連誤答ディスアドバンテージ機能をつける

「連誤答」とは、「ある人が1度誤答した後、その人が次に正解する前に再び誤答してしまう」ということです。
以前「連答」の話をしましたが、連答が「参加者全体で見て連続して正解する」のに対し、連誤答は「各参加者それぞれについて連続で誤答する」というものです。
※ちなみに、各参加者それぞれについて連続で正解する、いわば「自己連答」を利用した形式も存在します。その形式についてはまた別の機会に解説します。

STUの2Rでは、「連誤答ディスアドバンテージ付き5○3×」という形式が行われています。
これは、「5問正解で勝ち抜け(連答によるボーナス加点はなし)」「1回目の誤答で起立し、次に正解するまでに2回目の誤答を犯すと2×が加算され、即失格」とというものです。2回目の誤答の前に1問正解すれば着席することができ、残り2回は誤答することができます。

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

【作り方】

①連誤答状態記録テーブル・加算×数記録テーブルを作る

N～U列を「連誤答状態記録テーブル」、W～AD列を「加算×数記録テーブル」として活用します。
いつものように、まずはN7:U56およびW7:AD56を選択し、格子を描きます。

つづいて、数式をそれぞれ入力します。

【連誤答状態記録テーブル】
N7セルに以下の数式を入力し、U56までオートフィルします。

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

このテーブルには、各解答者・各問題について、「連誤答状態かどうか」を1か0で出力します。
まず、「誤答したかどうか」をIF関数で判定し、真ならば1と表示します。偽ならば、さらに「正解したかどうか」を判定し、真ならば0と表示し、偽ならば1問前と同じ状態を維持します。

【加算×数記録テーブル】
W7セルに以下の数式を入力し、AD56までオートフィルします。

=IF(D7="x",IF(N6=1,2,1),0)

このテーブルには、各解答者・各問題について、「いくつの×が加算されたか」を出力します。
まず、「誤答したかどうか」を判定し、真ならば、さらに「1問前の時点で連誤答状態だったかどうか」を判定します。真ならば2、偽ならば1を表示します。一方、最初の判定が偽(=誤答していない)の場合は当然0と表示されます。

②誤答数表示セル・ポイント数表示セルの数式を書き換える

SUM関数で加算×数テーブルを参照し数値を合計することによって、×数を表示させます。

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

=SUM(W7:W56)&"×"

また、これに伴い、ポイント数表示セルも書き換える必要があります。
なぜなら、ポイント数表示セルも×数をカウントして「LOSE」と表示しているわけですから、正しく書き換えてあげないと、条件付き書式が作動せず、失格時に黒く表示されません。

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

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

③連誤答状態を表示するための条件付き書式を設定する

連答系形式の場合は、「連答状態」のときに、ポイント数表示セルが黄色くなるように設定しましたね。
連誤答の場合も同様に、「連誤答状態」のときに変色するよう設定します。

やり方は連答のときと同様、OFFSET関数を使用します。

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

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

設定は、ここでは文字の色を白に、塗りつぶしを紫系の色にします。

それでは、正誤記録を適当に入力してテストしてみましょう。

5枠の人、まずは1回目の誤答。

試合は進んでいき、5枠の人、正解のないまま2度目の誤答！

…おや、失格にはなりましたが、連誤答状態のカラーそのままですね。

条件付き書式の設定を思い出して下さい。
「連誤答状態」の条件は、「連誤答状態かどうか」でした。ここで、「連誤答状態は失格後も持続する」というのがミソです。
一方「失格」の条件は、「ポイント表示セルに「LOSE」と表示されるかどうか」でした。
この2つの条件は、なんと両立してしまいます。

このような場合、Excelはどちらを優先するのか？
その答えは、「条件付き書式のルールの管理」ウィンドウにあります。

「条件付き書式」ボタンから、「ルールの管理」を選択して下さい。
何も表示されない場合は、「書式ルールの表示」のプルダウンリストから「このワークシート」を選んで下さい。

これまでこのシートに定義してきた条件付き書式が一覧となって表示されます。
そして、上にいくほど優先順位の高い条件付き書式として機能します。

先ほどのミス表示は、すなわち、ポイント表示セルがLOSEかどうかをチェックする前に、連誤答状態かどうかをチェックしてしまったため、起きてしまったというわけです。

では、この問題を解決するにはどうすればよいか？
優先順位を入れ替えてあげればよいのです。

この画像では、今「連誤答状態」の条件付き書式が選択されている状態です。
選択されていない場合は、クリックして選択して下さい。
そして、「ルールの削除」ボタンの右側にある「▼」ボタンを使い、連誤答状態の条件付き書式を、失格の条件付き書式よりも下位の位置に移動します。

「OK」もしくは「適用」を押して、改めて画面を見てみると…

きちんと正しく表示されるようになりました。