「ポイント換算クイズ」の得点表示の作り方

以前「ポイント換算クイズ」の作り方をリクエストして頂きましたが、ようやく完成したので投稿いたします。

ポイント換算クイズのルールは以下の通りです。
・各プレイヤー、「○」「ポイント」の2種類の変数を持っており、どちらも最初は0である。
・正解すると、○が1つ加算される。そのとき、○をポイントに交換するかどうかを選択する。
「交換しない」を選んだ場合はそのまま次の問題に移るが、「交換する」を選んだ場合は、今まで持っていた○数に応じてポイントが加算され、○数は0に戻り、次の問題に移る。
・交換によってもらえるポイント数の計算式は様々なバリエーションが考えられるが、ほとんどの場合は「交換する○の数が多いほど、もらえるポイントが加速度的に多くなる式」である。

(例)
○の数の2乗
1○=1p、2○=4p、3○=9p、4○=16p…
三角数
1○=1p、2○=3p(=1+2)、3○=6p(=1+2+3)、4○=10p(=1+2+3+4)…
※n○のときに交換されるポイントは、n(n+1)/2で求められる。

・誤答すると、そのときに持っていた○数を全て没収される。
それ以外にも「3回で失格」や「0○時の誤答はN回休み」などのルールを付け加えてもよい。
・ポイントが規定値に達したプレイヤーから勝ち抜けとなる。
・限定問題数に達して試合終了となった場合、残りのプレイヤーの順位はポイントの多さで判定する。
終了時点で持っていた○はポイントに交換されない。

○を貯めれば貯めるほど大逆転が狙えますが、１回の誤答で全てを失ってしまうという、なかなかスリリングなルールですね。

今回は、「交換→+(○の数の2乗)p」「誤答罰→3回で失格」「20p勝抜け」のルールで作っていきたいと思います。
基本⑤の続きから作っていきます。

完成品はこちらです。

【作り方】

①「○数表示行」を追加する

ポイントと×数の間に○数表示行を追加します。
図1において、各行の文字の大きさは次の通りです。

ポイント…84
○数…48
×数…62

②正誤スルー入力セルに「交換入力列」を追加する

通常は、１問ごとに入力する行は、プレイヤー数だけ用意された「正誤入力列」とその右の「スルー入力列」で構成されていましたが、このルールに限り、さらに「○をポイントと交換する列」を1つ追加します。

③「○数テーブル」「ポイント数テーブル」を作成する

各プレイヤーの○数とポイント数を1問1問管理するテーブルを作ります。
O～V列を○数テーブル、X～AE列をポイント数テーブルとします。

④各テーブルに数式を入力する

まず、③で作った2つのテーブルの「0問目」にあたる7行目に、全て0を入力します。
次に、8行目以降には数式を入れていきます。

○数テーブル(O8に入力してV57までオートフィル)

=IF(D8=1,IF($M8<>"",0,O7+1),IF(D8="x",0,O7))

D8(正誤入力セル)に1が入力された場合(=正解した場合)、次に「ポイント交換を行ったか」をチェックします。TRUEなら、○数は0になり、FALSEなら1追加されます。
一方、1が入力されなかった場合は、「誤答かどうか」をチェックします。TRUEなら、○数は0にリセットされ、そうでない場合(=何も答えてない場合)は、前の問題の○数をそのまま表示します。

ポイント数テーブル(X8に入力してAE57までオートフィル)

=IF(AND(D8=1,$M8<>""),X7+(O7+1)^2,X7)

「D8が1」かつ「M8に何か文字が入力されている」場合(=正解し、ポイント交換を宣言した場合)、これまでの○数に1足した値を2乗したものを持ちポイントに加算します。
そうでない場合は、前の問題のポイント数のままです。

上記のポイント数テーブルの数式内で黄色いマーカーを引いた部分は、採用するルールに応じて書き換えてください。
今回は、最初に書いたとおり、(○数の2乗)pを交換できるルールでやっていきます。


ここまでの工程と、画面上の加工する場所を示した画像が以下の図1です。

⑤ポイント表示セルと○数表示セルに数式を入力する

ポイント表示セル(D4:K4)と○数表示セル(D5:K5)に、それぞれ以下の数式を入力します。
問題数(Q4セル)を利用してテーブルを参照して表示する、おなじみのOFFSET関数を使った方法です。

ポイント表示セル(D4に入力してK4までオートフィル)

=IF(OFFSET(X$7,$Q$4,0)>=$O$4,"WIN",IF(COUNTIF(D8:D57,"x")>=$P$4,"LOSE",OFFSET(X$7,$Q$4,0)))

○数表示セル(D5に入力してK5までオートフィル)

=OFFSET(O$7,$Q$4,0)

【使い方】

通常どおり、正解したプレイヤーには「1」を、誤答したプレイヤーには「x」を入力します。

さらに、正解後「○とポイントを交換する」を選んだ場合には、正解した問題の行の一番右の「交換入力列」に何か文字を入れます。
(正解の「1」を入力した時点でその行はすでに灰色になっていますが気にしないでください)

これで、○が全てポイントに交換されました。