「上座クイズ」の得点表示の作り方(基本形)

上座クイズの得点表示の作り方を解説します。
今回は最も基本的な機能のみを作ります。

上座クイズとは、「解答席に序列がついており、正解すると上位の席に、誤答すると下位の席に移動する」というクイズです。
例えば、上位から順にABCDEFGHの8人が並んでおり、正解すると2段上がるというルールの場合、Eが正解すればCのいる席に移動し、ABECDFGHという順番になります。その後2番席のBが正解すれば1段上昇でBAECDFGH、1番席のBが正解すれば座席の配置は特に変わりません。

代表例を挙げますと、テレビでは「なるほど!ザ・ワールド」や「Qさま!!」、オープン大会では「STU」の旧準決勝「Head of the Stage」です。

この要素さえ含まれていれば「上座クイズ」と呼ばれるので、ルールのバリエーションは豊富です。
ポイントの概念がない場合は、「○問ごとに下位○人が脱落」といったルールや「1番席の人が正解すれば勝ち抜け+同時に最下位席の人が脱落」などが考えられます。
ポイントの概念をつけるのであれば、例えば「1番席で正解すると+4p、2番席で正解すると+2p、3番席で正解すると+1p。10p先取」という具合です。

ひとまず今回は、正解・誤答により座席を移動する機能だけを作ります。

それでは基本⑤(終):「終わった問題」の行と「今の問題」の行を色分け表示するの続きから作っていきます。

完成品はこちらです。
[2020/8/25追記:ver1.1に更新しました]

【作り方】

①上昇数設定セル・下降数設定セルを作る

正解時に上昇する席の数・誤答時に下降する席の数を入力するセルを作ります。

今回はそれぞれR4・S4セルに作ります。
そして、ここではとりあえず上昇:2段、下降:2段で設定しておきます。

②順位加減テーブル・順位更新テーブル・解答者配置テーブル・参加者名入力セルを作る

今回、処理に使うテーブルは3つです。

N~U列:順位加減テーブル…その問題で答えた人の正誤に応じて、座席の配置を変えるための処理を行う。
W~AD列:順位更新テーブル…順位加減テーブルの表の結果、8人の新たな順位を記録する。
AF~AM列:解答者配置テーブル…各問題ごとの出題後における8人の解答者の配置を記録する。

各テーブルで使用する列の7行目から56列目までに格子を作ります。

あと、ついでにN5~U5セルに、それぞれ1,2,3,4,5,6,7,8という数字を入力しておきます。

また、今回はD~K列3行目に表示される解答者の名前が目まぐるしく変わるため、そこに直接名前を入力するのではなく、別の場所(今回はW~AD列の3行目)に解答者らの名前を入力しておき、D~K列には解答者配置テーブルの情報をもとに解答者名を表示させる数式を入力します。





全体的な処理の流れは、以下の通りです。

*誰かが正解/誤答する

順位加減テーブルには、あらかじめ左から順に1~8の数字が入力されている。
正解した場合は答えた人の位置のセルの数値から(設定した上昇量+0.1)を引く。
誤答した場合は答えた人の位置のセルの数値から(設定した下降量+0.1)を足す。

順位加減テーブルに表示された数値がそれぞれ何番目かを、RANK.EQテーブルを用いて順位更新テーブルに記録していく。

順位更新テーブルに記録された順位をもとに、8人の解答者の配置を、解答者配置テーブルに記録する。

*に戻る

③解答者配置テーブル(1問目の行)を作る

ここには、1問1問ごとの各解答者の配置を記録していきます。
このテーブルにおいては、プレイヤーは1~8の番号で表記します。

まず、AF5~AM5(0問目の行)にそれぞれ1~8の数字を順番に入力します。
これが初期配置というわけです。

④順位加減テーブルを作る

ここには、入力された正誤記録をもとに、解答者の配置を変更するための順位加減を行います。

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

=N$6-IF(D7=1,$R$4+0.1,IF(D7="x",-$S$4-0.1,0))

N~Uの6行目には、それぞれ1~8という数字が表示されており、その行の正誤記録が入力されていない場合はその数字がそのまま返されます。
そこに、正解もしくは誤答の記録が入力されると、R4で指定した上昇量およびS4で指定した下降量にもとづき、順位の補正が行われます。
例えば、6位席の人が正解し、2段上昇する場合、6位だった人は3位と4位の間に入るわけです。そこで、3と4の間の数である3.9に数字を変えます。
そうして正誤による順位補正を行った後の数値をRANK.EQ関数を用いて改めて順位付けし、隣の順位更新テーブルに記録させるのです。
正解時には順位の数字を引きますが、誤答時には順位の数字を足します。



④順位更新テーブルを作る

順位更新テーブルでは、順位加減テーブルで補正された順位の数字を、RANK.EQ関数を用いて順位づけし、整数の値にします。

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

=RANK.EQ(N7,$N7:$U7,1)

ここで注意しておきたいのが、RANK.EQの第3引数です。ここはデフォルトでは0となっており、降順、すなわち大きい順に順位が振られます。
しかし、今回は最も小さい数を1、次に小さい数を2、…という法則で順位付けするため、ここには0以外の数値を入れて、昇順の設定にしておきます。

⑤解答者配置テーブル(2問目以降の行)を作る

順位加減テーブルと順位更新テーブルを作ったので、解答者配置テーブルの残りを作りましょう。

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

=INDEX($AF6:$AM6,MATCH(N$6,$W7:$AD7,0))

INDEX関数で、まず1問前の時点での各選手の配置(AF~AM列)を参照します。
次にMATCH関数で、その列の順位の席(N5~U5列に入力した数字を流用)の人が、順位更新テーブルのどこに行くのかを調べます。


⑥解答者表示セルに、順番通りに解答者の名前が表示されるようにする

最初にW3~AD3セルに登録した各選手の名前が、AF~AM列の解答者配置テーブルに従って並ぶように設定します。

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

=INDEX($W$3:$AD$3,OFFSET(AF$6,$P$4,0))

いつものようにINDEX関数を使いますが、ここではMATCH関数を使う必要はありません。OFFSET関数を使います。解答者配置テーブルの6行目(0問目の行)を起点とし、出題された問題数(P4セル)分だけ参照セルを下げます。

これで解答者が動くようになりました。正誤をいろいろと入力してみましょう。
解答者が動いていることを確認して下さい。
(ポイント数表示セル・誤答数表示セルは一旦消しました)



コメント

  1. 初めまして、てぃーと申します。
    いつも得点表示の参考に使わせて頂いております
    上座を実際に作って動かしてみたところ、最終問題に1を入力した際にエントリーナンバー及び名前に#VALUE!が表示されてしまいました。そこで各テーブルの最終行をもう1行コピーして作成したところエラーが解消されました。これは必須の対応でしょうか?
    もしご覧になればご返信よろしくお願いいたします。

    返信削除
  2. コメントありがとうございます。
    参加者名を表示する際、N~U列の「解答者配置テーブル」を参照しているのですが、このテーブルは「その問題が出される前の各回答者の配置」を表しているので、例えば50問目が終わった場合は51行目を見て名前を表示しています。なので、存在しない行を参照した結果エラーが表示され、行を継ぎ足すと正しく表示されるようになったということです。
    これから時間をかけて修正いたしますので、取り急ぎ行の継ぎ足しでご対応ください。申し訳ございませんが、よろしくお願いいたします。

    返信削除
  3. てぃー様
    記事の内容およびExcelファイルの修正が完了しました。(案外早く終わりました!)
    修正内容としては、
    ・解答者配置テーブルの仕様変更(その問題が出された"後”の解答者の配置を表示するようにした)
    ・テーブル3種の配置の変更
    となります。
    これで、継ぎ足しせずに、最終問題の正誤を入力しても解答者名が正しく表示されるかと思います。
    Excelファイルへのリンクを新しく張りましたので、改めてダウンロードしてください。
    どうぞよろしくお願いいたします。

    返信削除

コメントを投稿

このブログの人気の投稿

基本①:「m○n×クイズ」の得点表示の作り方

参加者の苗字と名前を分離する/選手名を均等な大きさで表示する

勝ち抜け時に順位を表示する