【参加者管理⑤】各Rの勝者を集計する

前回：【参加者管理④】各試合の参加者を表示させる

続いては、このシートに各Rの成績が集計されるように作っていきます。
各シートにおいて、それぞれの試合で入力した正誤記録は得点や順位という形で出力されます。
そして、各試合の勝者を参加者管理シートに記録していきます。
こうすることで、次のラウンドの参加者を各コース・各試合に振り分けることができるのです。すなわち、2Rの得点表示シート→参加者管理シート→3Rの得点表示シート→準決勝の参加者管理シート→…というやりとりを行うのです。

今回の記事で2Rまでは完成です。
この段階までの完成品はこちらです。

【作り方】

①得点・誤答記録テーブルを作る

現状の2Rの得点表示は、得点や勝ち抜け・失格状態を表示するのみで、得点履歴は残りません。
アップダウンやSquare20などの形式では、得点処理に必要なため得点記録テーブルを作りましたが、シンプルなm○n×形式の場合はそれを作る必要がないので今まで作ってきませんでした。
しかし、勝者を記録するにはやはり必要となってきますので、ここで作っておきましょう。

まずは例によって格子を描きます。
R～AC列を得点記録テーブル、AE～AP列を誤答記録テーブルとします。
ここで、今回は40問限定というルールのため、格子はそれぞれ12列×40行の範囲にのみ描きます。

続いて、数式を入力します。

得点記録テーブル

R8セルに以下の数式を入力し、AC47セルまでオートフィルします。

=SUM(D$8:D8)+R$7

なぜ7行目のセルを足すのかは次のステップで説明します。

誤答記録テーブル

AE8セルに以下の数式を入力し、AP47セルまでオートフィルします。

=COUNTIF(D$8:D8,"x")

②アドバンテージ入力セルを作り、アドバンテージを得点に反映させる

さて、忘れるところでしたが、今回想定している大会ではペーパークイズ上位者にはアドバンテージを付与するのでした。

上位から順に2,2,1,1,1,1,0,0,0,0,0,0というポイントで試合を開始します。

このアドバンテージは、得点記録テーブルの上の1行(R7:AC7)に入力することにします。

得点記録テーブルにはキチンと反映されています。さきほど入力した式で7行目のセルを足したのはこれをやりたかったからです。

しかし、まだ得点表示セル(D3:O3)がほったらかしです。こちらの修正を忘れてはなりません。

D3セルを以下のように書き換え、O3セルまでオートフィルします。

=IF(SUM(D8:D57)>=$R$5,"WIN",IF(COUNTIF(D8:D57,"x")>=$S$5,"LOSE",SUM(D8:D57)))

↓

=IF(SUM(D8:D57)+R$7>=$R$5,"WIN",IF(COUNTIF(D8:D57,"x")>=$S$5,"LOSE",SUM(D8:D57)+R$7))

③勝者記録テーブルを作る

続いて、勝ち抜け者が出るたびにそのプレイヤーが記録されていくテーブルを作ります。
勝者記録テーブルは3列で構成されており、それぞれ「勝ち抜け人数」「勝ち抜け順位」「勝ち抜けたプレイヤー(1R順位で表示)」を表示します。

ポイント記録テーブルの左に3列(うち1列は勝者記録テーブルと得点記録テーブルの境界線)挿入し、以下の数式を入力します。

勝ち抜け者数(R列)

R8セルに以下の数式を入力し、R47セルまでオートフィルします。

=SUMPRODUCT(($V8:$AF8>=$V$5)*($V$6:$AF$6=""))

条件に合致するセルの個数を調べる場合、COUNTIF関数かSUMPRODUCT関数を使う方法がありますが、今回の"条件"は、シート内のセルの数値に左右されるものです。この場合はCOUNTIFは使えません。

COUNTIFが使えるのは、例えば

=COUNTIF($V8:$AF8,">=4")

というように、固定された数との大小比較をする場合のみに限定されます。

SUMPRODUCTの使い方は10Points Aheadの回でも取り上げていますので、こちらもご覧ください。

勝ち抜け順位(S列)

S8セルに以下の数式を入力し、S47セルまでオートフィルします。

=IF(R8-R7>0,R8,"")

勝ち抜け人数が増加した場合、増加後の人数をそのまま表示します。
増加していない場合は何も表示されません。

勝ち抜けたプレイヤー(T列)

=IF(S8<>"",INDEX($D$2:$O$2,MATCH(1,$D8:$O8,0)),"")

勝ち抜け者が発生した場合は、そのときに正解していた人が勝ち抜けた人なので、INDEX関数とMATCH関数の合わせ技で、2行目から正解した人(の1R順位)を表示させます。


さて、これで勝ち抜けが出るたびに勝ち抜けた人が表示されるようになりました。

④全問題を出題した際の勝ち抜け判定を行う

勝ち抜けが出るたびに勝ち抜け者が記録されるテーブルを作りました。
しかし、もし40問出し尽くした場合のことも考えなくてはなりません。

そこで、勝者記録テーブルのお尻に行を追加します。
追加する行数は、そのRで規定された勝ち抜け人数と同じ数です。全問題終了時に誰も勝ち抜けていないというケースがありえますからね。

※わかりやすくするため、40問目と追加分の境界には太い罫線を引いてあります。

次に、各列に数式を入力していきます。

勝ち抜け者数(R列)

R48セルに以下の数式を入力し、R52セルまでオートフィルします。

=R47+1

シンプルですね。要するに、R48:R52の範囲には、(問題全出題までに勝ち抜けた人数)+1から始まり、1ずつ増えていくような数列が並ぶわけです。

勝ち抜け順位(S列)

ここは40問目までの式と同じものを使います。
したがって、S47セルからS52にかけてオートフィルすればオッケーです。

すなわち、S48セルにはこのような数式が入力されます。

=IF(R48-R47>0,R48,"")

勝ち抜けたプレイヤー(T列)

T48セルに以下の数式を入力し、T52セルまでオートフィルします。

=IF(COUNTIF($D48:$O48,"W")=1,INDEX($D$2:$O$2,MATCH("W",$D48:$O48,0)),"")

問題終了後は、ルールで決められた順位判定法に従って各プレイヤーの順位を決定し、上位のプレイヤーから、正誤入力セルにそのプレイヤーの列に"W"を入力すると、勝ち抜けとして扱われる仕組みです。

…そうです、全問題終了後の勝者は手動で入力するのです。

全問題終了後の順位判定は大会によって異なります。よって、得点表示の汎用性を考えて、ここはあえて自動化しないことにします。

順位判定の最後ににペーパークイズの成績を用いることもあれば、サドンデスの1○1×で決めることもあります。むやみやたらに自動化しないことも大事です。

使用例を挙げます。

たとえば、40問終了後、このようになったとします。

残る勝ち抜け枠は2人ですね。

ここでの順位判定法は、「正解数の多さ→誤答の少なさ→ペーパークイズの順位の高さ」でした。

したがって、まず3○のプレイヤーをチェックします。13位、28位、60位のプレイヤーが該当しますね。この時点でまだ3人です。
次に誤答数を比較すると、13位のプレイヤーのみ0×なので、この人が4位です。一方、28位と60位は1×で並んでおります。
そこで最後に、ペーパークイズの順位で比較すると、28位のプレイヤーが5位となり、最後の通過者となります。

よって、正誤入力セルの41問目の行には13位の列(D列)に、42問目の行には28位の列(G列)に"W"を入力します。

すると、勝者記録テーブルにはきちんと4位・5位通過者として1R順位が表示されました。

⑤判定勝ちになったプレイヤーも勝ち抜け表示にする

勝者記録テーブルには判定勝ちの勝者が記録されるようになりましたが、ここの部分は観客には見えない部分です。
観客が見るのは得点表示セルなので、判定勝ちした場合はこちらも赤くならなければなりません。
というわけで、条件付き書式の設定を変えます。

得点表示セル全体(D5:O5)を選択し、「条件付き書式」ボタンから「ルールの管理」を選びます。

失格表示(黒く塗りつぶし字を白くする)と勝ち抜け表示(赤く塗りつぶし字を白くする)の書式設定が表示されています。
そのうち、勝ち抜け表示の書式設定を選択し、「ルールの編集」を押して、条件式を以下のように書き換えます。

=D5="WIN"
↓
=OR(D5="WIN",COUNTIF($T:$T,D2)>0)

T列、すなわち勝者記録テーブルの「勝ち抜けた人のペーパー順位が表示される行」に数字があった場合でも、勝ち抜け表示になるという設定です。

これにより、先ほどの例において判定勝ちした2名の得点表示セルも赤くなりました。

⑥得点表示シートに記録された各Rの勝者を参加者管理シートに参照させる

さて、いよいよ2Rの勝ち抜け者を参加者管理シートに表示させます。

参加者管理シートに戻りましょう。

AD4セルに以下の数式を入力し、AH4セルまでオートフィルします。

=INDEX('2R'!$T$8:$T$52,MATCH(参加者管理!AD$3,'2R'!$S$8:$S$52,0))

おなじみのINDEXとMATCHのコンビです。
INDEX関数の参照元は、得点表示シートの勝者記録テーブルの勝ち抜けプレイヤー表示列(T列)です。そのうち何番目を参照するかを出力するMATCH関数では、「○位の数字が、勝者記録テーブルの順位表示列(S列)の何番目にあるか」をチェックしてくれます。

5人の勝者の1R順位が2R通過順にきちんと表示されていることを確認してください。

⑦2Rの得点表示シートを実施試合数分コピーし、参加者管理シートの勝者テーブルに残りの組を表示させる

これで2Rのシートは完成です。
全部で4組行いますので、4試合分コピーしていきましょう。

シートのコピーは、タブを右クリックして「移動またはコピー」でしたね。
(Round Trip Quizの回でも取り上げております)
シートの名前もそれぞれ「2R-○」に変えておきます。シートの名前変更は、タブをダブルクリックするか右クリックで出るメニューから「名前の変更」を選択すれば出来ます。

あとは、H1セルの組番号もそれぞれ変えれば…

きちんと各試合の参加者が表示されています。


続いて参加者管理シートの勝者テーブルも、残りの組の分の式を入力します。
少々手間がかかりますが、まず1組目の勝者テーブルの(AD4)から4組目までオートフィルします。この時点ではまだ1組目のシートを参照している状態です。
そして、2組目以降は数式内のシート名の部分をその組ごとに書き換えていきます。

=INDEX('2R-1'!$T$8:$T$52,MATCH(参加者管理!AD$3,'2R-1'!$S$8:$S$52,0))
↓
=INDEX('2R-○'!$T$8:$T$52,MATCH(参加者管理!AD$3,'2R-○'!$S$8:$S$52,0))

○の部分にそれぞれの組の数字を入れます。

※なお、実施する試合数が多く、いちいちシート名を書くのがめんどくさいという場合は、INDIRECT関数を使うのも手かもしれません。これについては今回は解説しません。使い方はこちらを参考にしてみてください。
https://www.nishi2002.com/4818.html

4組ともできたら、5位の列(AH列)まで横にオートフィルしましょう。


4組とも適当に試合結果を入れると、きちんと順位が表示されることを確認してください。

⑧参加者リストに通過状況を出力する

ペーパークイズ通過者のリストに戻ります。

このI列、すなわち「2Rの通過順位」をこちらに反映させます。

I15セルに以下の数式を入力し、I62セルまでオートフィルします。

=IF(COUNTIF($AD$4:$AH$7,$A15)>0,MATCH($A15,OFFSET($AD$3:$AH$3,$H15,0),0),"")

最初にIF関数でチェックするのは、「この人が2Rを勝ち抜けたかどうか」です。
これは、先ほどの勝者テーブル(AD4:AH7)の中に、その人の1R順位(A列)があるかどうかをCOUNTIF関数でチェックすればいいですね。
もし勝ち抜けていれば、今度はその人が何位かを表示します。ここではMATCH関数を利用します。
探す対象はもちろんA列の1R順位ナンバー。探す範囲は、その人の参加した組の行の勝者テーブル。ここで、OFFSET関数を使って、参加した組だけ探す範囲が変化するようにします。参加した組はペーパークイズ通過者リストのH列に入力されていますから、まず勝者テーブルの3行目(AD3:AH3)からスタートし、そこから下に向かって参加した組の番号(H列に入力されている数)分、下に探す範囲を移動させます。列方向には移動する必要はないので、OFFSET関数の第3引数は0にしておきます。

I列にきちんと2Rの通過順位が表示されていることを確認してください。

次回は、1Rシード+2R通過者の32名を3Rの4つのコースに振り分けます。
各参加者の参加希望を上位順に処理します。

(次回に続く)