連答機能をつける(等速ver.)

早押しクイズのルールではしばしば、「連答」という概念が登場します。

簡単に言うと、「ある問題で正解した後、その次の問題も同じ人が正解する」ということです。他の人に正解されるまでは、「連答状態」は続きます。
補足すると、「解答者Aが正解→別の解答者Bが誤答」の場合は、解答者Aの連答状態は失われません。スルーの場合も同様です。
また、「解答者Aが正解→解答者Aが誤答」の場合、Aの連答状態はOFFになります。

連答ありのルールの場合、連答が成功するとポイントが一度に2点入るなどの特典があります。abcの第2Rの連答ボーナスつき5○2×が代表例ですね(fourthまでは即勝ち抜けでした)。
場合によっては、「2連答で2点、3連答で3点、4連答で4点」…というように、連答回数で加算ポイントが加速していくものもあります(例：abcの"10 Hits Combo"シリーズ、STUの"10 Points Ahead")。この方式は、また別の機会に解説します。
※7/10：加速型の連答機能の作り方を公開しました。

今回は、連答ボーナスつき5○2×を作ってみましょう。何連答しても+2点という「等速型連答ボーナス」です。
基本⑤の続きから作っていきます。
単に「連答すれば+2点」という機能を作るだけでなく、「連答状態中(=次正解すればボーナス)であることを表示する」という仕様も実装します。

完成品はこちらです。

【使用するExcelの機能】

・関数「OR」
・条件付き書式

【作り方】

①連答状態記録テーブルを作る

「連答状態」の場合は1、そうでない場合は0を表示するテーブルです。

前回紹介したアップダウンと同様に、N7:U56の範囲のセルを使います。
格子状の罫線を引いておきましょう。
また、N6:U6の範囲全てに0を入力しておくことも同じです。

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

=IF(D7="x",0,IF(OR($L7<>"",COUNTIF($D7:$K7,"x")>=1),N6,D7))

2つの分岐があるので、1つずつ丁寧に見ていきましょう。

条件①：その人が誤答した？

まず、その問題で誤答した場合は、問答無用で連答状態がOFFになります。
そうでない場合は条件②をチェックします。

条件②：スルーが起きた？もしくは、自分以外の誰かが誤答した？

この場合のように、複数の条件式のうちいずれか1つが満たされているかどうかをチェックする場合は、OR関数を使います。括弧の中に、カンマ区切りで複数の条件式を記述するだけです。
スルーが起きたかどうかは、L列が空白のセルではないか(否定の演算子は、不等号を2つ並べた「<>」です)をチェックします。
自分以外の人が誤答したかどうかは、その行の正誤スルー入力セルにxが入力されたかどうかをCOUNTIF関数で調べればいいです。「これだと自分が誤答した場合も含まれるではないか」という心配はいりません。そのケースは既に条件①でハネています。

これが真の場合は、連答状態に変動はありません。そのため、連答状態記録セルの1つ上のセルと同じ値を表示させます。

一方、偽の場合はどうでしょうか。残されたケースは、「自分が正解」「自分以外の誰かが正解」のいずれしかありません。自分が正解ならば連答状態ON、自分以外が正解ならばOFFになりますね？一方、正誤スルー入力セルの中身は、自分が正解の場合は「1」、自分以外が正解の場合は空白です。

というわけで、正誤スルー入力セルを直接参照して、そのまま連答状態表示セルに表示させてしまえばいいのです。参照したセルが空白の場合は自動的に0として扱われます。

試しに入力してみましょう。

2枠○→5枠○→6枠○→4枠○→4枠○→スルー→6枠○→6枠×→4枠○
のとき、

4枠が3問連続で連答状態をキープしており、6枠が誤答により連答状態を失っていることが確認できます。

②加算ポイント記録テーブルを作る

その問題で何点が加算されたかを表示するテーブルです。
連答状態記録テーブルの更に右、W7:AD56の範囲のセルを使います。格子状の罫線を引いておきましょう。

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

=IF(D7="x",0,D7*(N6+1))

誤答の場合は当然加算ポイントは0です。
そうでない場合は、正誤スルー入力セル(上の式ではD7セル)には1が入力されているか、何も入力されていないかのどちらかです。
また、正解時には、1つ前の問題の連答状態(上の式ではN6セル)がON(=1)の場合は2点、OFF(=0)の場合は1点が入らなければなりません。
それらを満たす式が、D7*(N6+1)なのです。

連答すれば2点が入っていることが確認できます。

③加算ポイント記録テーブルの列を合計し、ポイントを表示する

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

=IF(SUM(W7:W56)>=$N$4,"WIN",IF(COUNTIF(D7:D56,"x")>=$O$4,"LOSE",SUM(W7:W56)))

下線部が変更点です。
すなわち、これまで得点は正誤スルー入力セルを参照していましたが、ここでは加算ポイント記録テーブルを参照するのです。

連答した4枠の人の得点がきちんと3点ではなく4点になっていますね。

④条件付き書式を用いて、連答状態であるかどうかを表示する

D4:K4を選択し、「条件付き書式」内の「新しいルール」から条件付き書式を定義します。

数式は以下の通りです。

=OFFSET(N$6,$P$4,0)>=1

連答状態記録テーブルを参照し、判定します。

OFFSET関数を使って、0問目の位置(6行目)から、既に出題された問題数だけ、下に移動します。
その値が1以上ならば、その人は今連答状態なので、ポイント表示セルに色をつける、というわけです。
(ここで、「1ちょうど(「=1」)」ではなく「1以上(「>=1」)」としたのは、今度紹介する加速型の連答ルールにも適用できるようにするためです)

書式設定は、「塗りつぶし」だけ変更します。

今回は、「その他の色」から、こちらの薄い黄色を選びます。

設定すると、きちんと「最後に正解した人」のポイント表示欄が黄色くなっているのが確認できます。

連答状態で誤答した場合に連答状態の表示が消えてしまうことも確認して下さい。