「10Points Ahead」の得点表示の作り方
STUの3R・コース別の一つ、10Points Aheadのルールを説明します。
以前紹介した10 hits combo(初代)同様、「5人対戦。M連答で+Mポイント、N回目の誤答でN回休み」というルールなのですが、勝ち抜け条件が違います。
それは、(3位の点数+10)ポイントです。すなわち、試合が進むにつれてゴールがどんどんと遠ざかっていくというわけです。
3位というのは、いわば5人の中でちょうど真ん中の順位(統計用語で「メジアン」といいます)。例えば、5人の得点がそれぞれ8,5,5,3,2の場合は、勝ち抜けポイントは15点となり、7,7,7,2,1の場合は17点になります。
また、誰か1人が勝ち抜けた場合、その人は今後クイズには参加しないためポイントは増えませんが、以降はずっと1位扱いとなります。そのため、「3位」はこれ以降、「残っている4人の中での2位」を意味します(この形式において失格はありません)。
この形式、上位2人だけならまだしも、3人以上が点数を伸ばすと、どんどんとゴールが遠ざかっていき、なかなか勝ち抜け者が現れません。
現にこの形式は、ほとんどの場合、試合時間の15分がフルに使われ、判定に持ち込まれるケースです。
スタミナが要求される形式と言えるでしょう。
それでは、「10 hits combo」シリーズの得点表示の作り方の続き(「初代」タブ)から作っていきます。
完成品はこちらです。
・関数「MATCH」
・関数「SUMPRODUCT」
当ブログではこれまで形式にかかわらず8人用として作ってきましたが、今回に限り、5人に削減します。
見出しをドラッグしてI～K列を選択し、右クリックメニューから「削除」を選択します。
すると、それに伴い、右側の「連答状態記録テーブル」「加算ポイント記録テーブル」「休み数記録テーブル」の6～8枠に相当するセルが全て「#REF!」と表示されてしまいます。セル内の数式で参照していたセルがなくなってしまったからですね。
これらのテーブルも、5枠に削減しましょう。
A56:AA57(49・50問目の、正誤スルー入力セル・連答状態記録テーブル・加算ポイント記録テーブル・休み数記録テーブル)を選択し、87行目(もしくはそれ以上)までオートフィルします。
また、それに伴い、誤答数表示セル(D6:H6)および出題数カウントセル(M4)の数式を変更しなくてはなりません。現状では、いずれも57行目までしか参照しないようになっております。こちらも変更しておきます。
=COUNTIF(D8:D57,"x")&"×"
↓
=COUNTIF(D8:D87,"x")&"×"
↓
=SUM($B$8:$B$87)
[既設]答状態記録テーブル(5列；K～O列)
[新設]勝ち抜け者テーブル(1列；P列)
[新設]勝ち抜け者数記録テーブル(1列；Q列)
[新設]得点履歴テーブル(5列；R～V列) ※「加点ポイント記録テーブル」を改造
[新設]勝ち抜けノルマテーブル(1列；W列)
[既設]休み数記録テーブル(5列；X～AB列)
[新設]プレイヤー別順位テーブル(5列；AD～AH列)
[新設]順位別得点テーブル(5列；AJ～AN列)
こう列記しただけでもめまいがしそうですが、落ち着いて、まずはとりあえず格子だけでも書いておきます(1列だけのテーブルは格子ではなく縦線だけにしておきます)
すなわち、R～V列には1問1問における各プレイヤーの得点履歴を記録してもらい、それを得点表示セルにてOFFSET関数で参照してもらう形にします。
その前に下準備をします。
AJ6:AN6の範囲に、それぞれ1,2,3,4,5と入力しておいて下さい。
次に、R7:V7の範囲全てに0を入力しておいて下さい。
そして、R7セルに以下の数式を記入し、V87セルまでオートフィルします。
=IF(COUNTIF($P$7:$P7,AJ$6)>=1,999,R7+(K7+1)*IF(D8=1,1,0))
ここでまずチェックするのは、P列です。ここには後(手順⑧)で「勝ち抜け者テーブル」を作り、勝ち抜け者が出た場合にそのプレイヤーの番号(左から1,2,3,4,5)を表示してもらいますが、ここに表示された数字がその列のプレイヤーの数字と同じであった場合―――すなわち「その人が勝ち抜けた場合」―――得点を999にします。
そうでない場合は、普通に同様の処理を行います。
ここで登場するのが「RANK.EQ関数」です。
この関数では、ある数がある配列内の中で何番目にあたるかを返します。もし複数の同じ数字がある場合は、その中の最も小さな数を返します。すなわち、100,80,80,50,30という配列があった場合は、それぞれ順位は1,2,2,4,5ということになるわけです。
AD7セルに以下の数式を入力し、AH87セルまでオートフィルします。
=RANK.EQ(R7,$R7:$V7)
単純ですね。得点履歴テーブルを参照し、その問題のその人の点数が、プレイヤー全体で見て何位かを返す式です。
さきほどのテーブルをもとに、各プレイヤーの得点を順位順に並べ替えて表示します。
AJ7セルに以下の数式を入力し、AN87セルまでオートフィルします。
=IF(COUNTIF($AD7:$AH7,AJ$6)=0,AI7,INDEX($R7:$V7,MATCH(AJ$6,$AD7:$AH7,0)))
まず、順位テーブルのその行の中に、ある順位の数字がないかどうかをチェックします。AJ列なら1位が、AK列なら2位が、…、AN列なら5位がいるかどうかをチェックするわけですね。
いないならば、同順位の人というわけですから、1つ上の順位と同じ数字(左隣のセル)を参照します。
一方、その順位の人がいるならば、順位テーブルと得点履歴テーブルから、点数を表示するようにします。
さてここで、INDEX関数とMATCH関数を組み合わせた方法を紹介しましょう。
INDEX関数は以前アップダウンで紹介いたしました。配列と数字を指定すると、その配列のその番号の要素を返すのでしたね。
一方、MATCH関数は、INDEXとは逆に、配列と配列の中に含まれる数字・文字列を指定すると、その数字・文字列が配列の何番目にあるかを返してくれます。なお、MATCH関数は引数が3つありますが、第1引数が「探したい数値・文字列」、第2引数が「探す配列」、そして第3引数が「照合の型」といいまして、ここに0と入力する必要があります。詳しい説明はこちらを見ていただきたいのですが、この引数はデフォルト値が1であるため、忘れずに0を入力して下さい。そうしないと、思ったとおりの数値・文字列が表示されないことがあります。
さて、INDEXとMATCHを組み合わせた部分だけもう一度よく見てみましょう。
INDEX($R7:$V7,MATCH(AJ$6,$AD7:$AH7,0))
INDEX関数は、まず最初に探したい数値・文字列の含まれた配列を指定します。ここでは、「その問題での5人の得点」ですね。
次に、その配列の何番目を参照したいかを指定します。ここでMATCH関数を使います。ここでは、「各プレイヤーの順位」の中から、その行に表示させたい順位を探し、それが何番目にあるか。MATCH関数はそれを返すわけですね。
④～⑥をまとめるとこうなります。
例えば、R～V列(得点)がそれぞれ8,0,7,11,3の場合、
AD～AH列(順位)は2,5,3,1,4となり、
AJ～AN列(ソート後得点)には、それぞれAD～AH列の4,1,3,5,2番目を参照し、11,8,7,3,0
と表示されます。
とはいっても、先ほどの順位別点数テーブルを作った後では大したことではないのですが。
W7セルに以下の数式を入力し、W87セルまでオートフィルします。
=AL+10
AL列に表示されるのは3位の点数です。それに10を足すだけですね。
ここには、各時点における、「勝ち抜けたプレイヤーの人数」を記録していきます。この情報は次の手順⑨で作るP列の勝ち抜け者テーブルを動かすために必要です。
Q8セルに以下の数式を入力し、
=SUMPRODUCT((AJ8:AN8>=W8)*TRUE)
ここで新しい関数「SUMPRODUCT」が出てきました。
SUMPRODUCT関数にはいくつか用途があるのですが、これを使うと、複数の配列について条件を指定し、それを全て満たすセルはいくつあるかを計算することができます。複数の条件式は( ) と * を使って連結していきます。ここで、各配列は同じサイズでなければなりません。また、条件式は2つ以上連結しなければなりません。
詳しい使い方はこちらをご覧ください。
ここでは、まず最初にAJ～AN列(5人の得点)を参照し、そのうち勝ち抜けノルマ(W列)以上であるかどうかをチェックします。ここでは、内部的に×××○×というような感じの結果が出るわけですね。
次に、TRUEという関数を連結しています。この関数は、問答無用でTRUEを返す引数なしの関数です。これを連結しても、さきほどの条件式の真偽がそのままの状態で通過します。何が何でも条件式が2つ以上必要のため、仕方なくこうしているのです。
ここにプレイヤー番号が表示されると、次の問題以降はその人の点数が999になるように設定したのでしたね。
P8セルに以下の数式を入力し、P87セルまでオートフィルします。
=IF(Q8-Q7>=1,MATCH(Q8,$AD8:$AH8,0),"")
Q列の今の問題の行とその1つ前の問題の行を比べ、勝ち抜け人数が変わった場合、勝ち抜け者を表示するようにします。そうでない場合は何も表示させません。
勝ち抜け者表示にはMATCH関数を使います。
MATCH関数では、AD～AH列(それぞれの順位)の中から、1人目の勝ち抜け者の場合は1、2人目の勝ち抜け者の場合は2であるセルの位置を調べます。
Q列には、勝ち抜けた人数が表示されていますから、1人目の勝ち抜けが出た場合は1と表示され、2人目が出たら2と表示されます。
D4の数式を以下のように書き換え、H4までオートフィルします。
=IF(SUM(R8:R57)>=$K$4,"WIN",IF(COUNTIF(D8:D57,"x")>=$L$4,"LOSE",SUM(R8:R57)))
↓
=IF(COUNTIF(OFFSET($P$7,0,0,$M$4+1),AJ$6)>=1,"WIN",OFFSET(R$7,$M$4,0))
書き換えるのは、IF関数の条件と、FALSE時に返す値です。
条件そのものは「勝ち抜けたかどうか」で一致しているのですが、変更前はSUM関数でK4に指定したノルマ以上の得点に達したかどうかをチェックしていたのに対し、変更後は、P列を7行目からスタートして(出題問題数+1)行の範囲までの中に、そのプレイヤーの番号(AJ6～AN6から参照)があるかをチェックします。OFFSET関数は、参照するセルをずらすだけでなく、拡張することも可能です。
FALSE時に返す値については、変更前は失格条件を満たすかどうかのIF関数を記述していましたが、このルールにおいては失格は起こらないので、OFFSET関数で得点記録テーブルを参照するだけでOKです。
そのため、3位の点数がどれなのかは、常にわかるようにしておくと良いでしょう。
現にSTUでは、3位の点数は緑色で表示されるようになっていますので、それを踏襲することにします。
D4:H4を選択し、条件付き書式を選択、以下の数式を入力します。
=D4=OFFSET($AL$7,$M$4,0)
ポイント表示セルの値と、順位別得点テーブルの3位の列の値(OFFSET関数を用いて出題済み問題数分だけ下にさがる)が同じ場合に、緑色に表示されるようにします。
書式は、「塗りつぶし」で下の画像の色を選択します。
連答時の条件つき書式は削除し、代わりにリーチ時(その状態で正解すると勝ち抜ける場合)にポイント表示セルのみが黄色くなる設定に変えます。
まずはポイント表示セルと連答数・休み数表示セル全体(D4:H5)を選択し、「条件付き書式」から「ルールの管理」を選択します。
上から、
・3位の点数を緑色にする
・休み中の場合に灰色にする
・勝ち抜けたら赤色にする
・失格になったら黒にする(現在は意味がない)
・連答中の場合に黄色にする
という条件付き書式が設定されています。
このうち一番下の書式を変更します。
(ついでに4番目の失格時の書式は削除してもかまいません)
まず「適用先」を「=$D$4:$H$5」から「=$D$4:$H$4」に変更し、「ルールの編集」ボタンを押します。
そして、次の数式を入力します。
=D4+OFFSET(K$7,$M$4,0)+1>=OFFSET($W$7,$M$4,0)
(現在のその人の点数)+(現在のその人の連答数+1)が、(現在の勝ち抜けノルマ)以上ならば、リーチということになるわけですね。
現在の状態ですと、3位の人が休み中の場合、下の画像のように、文字だけ白くなってしまいます。
これでは、スクリーンに表示した場合、3位の人の得点が見づらくなってしまいます。
これはいったいどういうことでしょうか？
先ほどの条件付き書式の一覧をもう一度見て下さい。
これは上から優先順位の高い書式ということになっておりまして、一番上の「3位は緑色」設定では、塗りつぶしだけを指定して、文字の色はデフォルトの「自動」のままになっていたのでした。
一方、次に優先順位の高い「休み中は灰色&白文字」設定では、文字の色の設定を白と定義しておりますので、これも適用された場合、文字色が白になってしまうわけです。
いろいろな方法が考えられますが、ここではいっそのこと、休み中にポイント表示セルを灰色にはせず、連答数・休み数セルのみ灰色にすることにします。
やり方は至って簡単です。
2番目の書式の「適用先」を「=$D$4:$H$5」から「=$D$5:$H$5」に変えるだけです。
これでようやく完成です。
画面に合わせて各プレイヤーの幅を変えて下さい。
以前紹介した10 hits combo(初代)同様、「5人対戦。M連答で+Mポイント、N回目の誤答でN回休み」というルールなのですが、勝ち抜け条件が違います。
それは、(3位の点数+10)ポイントです。すなわち、試合が進むにつれてゴールがどんどんと遠ざかっていくというわけです。
3位というのは、いわば5人の中でちょうど真ん中の順位(統計用語で「メジアン」といいます)。例えば、5人の得点がそれぞれ8,5,5,3,2の場合は、勝ち抜けポイントは15点となり、7,7,7,2,1の場合は17点になります。
また、誰か1人が勝ち抜けた場合、その人は今後クイズには参加しないためポイントは増えませんが、以降はずっと1位扱いとなります。そのため、「3位」はこれ以降、「残っている4人の中での2位」を意味します(この形式において失格はありません)。
この形式、上位2人だけならまだしも、3人以上が点数を伸ばすと、どんどんとゴールが遠ざかっていき、なかなか勝ち抜け者が現れません。
現にこの形式は、ほとんどの場合、試合時間の15分がフルに使われ、判定に持ち込まれるケースです。
スタミナが要求される形式と言えるでしょう。
それでは、「10 hits combo」シリーズの得点表示の作り方の続き(「初代」タブ)から作っていきます。
完成品はこちらです。
【新しく登場するExcelの機能】
・関数「RANK.EQ」・関数「MATCH」
・関数「SUMPRODUCT」
【作り方】
①プレイヤー数を5つに減らす
この形式は、「全体の3位」という概念がある以上、5人専用形式となります。当ブログではこれまで形式にかかわらず8人用として作ってきましたが、今回に限り、5人に削減します。
見出しをドラッグしてI～K列を選択し、右クリックメニューから「削除」を選択します。
すると、それに伴い、右側の「連答状態記録テーブル」「加算ポイント記録テーブル」「休み数記録テーブル」の6～8枠に相当するセルが全て「#REF!」と表示されてしまいます。セル内の数式で参照していたセルがなくなってしまったからですね。
これらのテーブルも、5枠に削減しましょう。
②正誤スルー入力セル等を増設し、誤答数表示セル・出題数カウントセルの参照範囲を変更する
これまで当ブログで作ってきた得点表示は50問限定という前提で作ってきましたが、この形式は15分間フルで戦う事が多いため、50問では絶対に足りなくなります。せめて80問程度用意しておきましょう。A56:AA57(49・50問目の、正誤スルー入力セル・連答状態記録テーブル・加算ポイント記録テーブル・休み数記録テーブル)を選択し、87行目(もしくはそれ以上)までオートフィルします。
また、それに伴い、誤答数表示セル(D6:H6)および出題数カウントセル(M4)の数式を変更しなくてはなりません。現状では、いずれも57行目までしか参照しないようになっております。こちらも変更しておきます。
【誤答数表示セル】
D6セルを以下のように書き換え、H6セルまでオートフィルします。=COUNTIF(D8:D57,"x")&"×"
↓
=COUNTIF(D8:D87,"x")&"×"
【出題数カウントセル】
=SUM($B$8:$B$57)↓
=SUM($B$8:$B$87)
③勝ち抜け条件を変更する
現在、勝ち抜け条件(K4セル)は「10」となっていますが、今回は勝ち抜け条件が時々刻々と変化するため、このセルとは別の場所で勝利条件を設定します。
とりあえず、K4セルには「99」とでも入力しておきます。
(最終的にはこのセルは全く使わないので、何を入力しておいても変わりはないのですが、編集中に得点表示セルが「WIN」と表示されるのは煩わしいため、得点表示セルの数式を編集するまでは、このように設定しておくことで「WIN」と表示されないようにしておきます)
④各種テーブルを設置する
この形式はこれまでのものと比べると処理が複雑で、以下に示す数多くのテーブルを用意する必要があります。[既設]答状態記録テーブル(5列；K～O列)
[新設]勝ち抜け者テーブル(1列；P列)
[新設]勝ち抜け者数記録テーブル(1列；Q列)
[新設]得点履歴テーブル(5列；R～V列) ※「加点ポイント記録テーブル」を改造
[新設]勝ち抜けノルマテーブル(1列；W列)
[既設]休み数記録テーブル(5列；X～AB列)
[新設]プレイヤー別順位テーブル(5列；AD～AH列)
[新設]順位別得点テーブル(5列；AJ～AN列)
こう列記しただけでもめまいがしそうですが、落ち着いて、まずはとりあえず格子だけでも書いておきます(1列だけのテーブルは格子ではなく縦線だけにしておきます)
④加点ポイント記録テーブルを得点履歴テーブルに作り変える
これまでは、R～V列に正解時の加算ポイントを記録し、それをSUM関数で集計することで得点表示セル(D4:H4)に得点を出す方法でしたが、これを変えます。すなわち、R～V列には1問1問における各プレイヤーの得点履歴を記録してもらい、それを得点表示セルにてOFFSET関数で参照してもらう形にします。
その前に下準備をします。
AJ6:AN6の範囲に、それぞれ1,2,3,4,5と入力しておいて下さい。
次に、R7:V7の範囲全てに0を入力しておいて下さい。
そして、R7セルに以下の数式を記入し、V87セルまでオートフィルします。
=IF(COUNTIF($P$7:$P7,AJ$6)>=1,999,R7+(K7+1)*IF(D8=1,1,0))
ここでまずチェックするのは、P列です。ここには後(手順⑧)で「勝ち抜け者テーブル」を作り、勝ち抜け者が出た場合にそのプレイヤーの番号(左から1,2,3,4,5)を表示してもらいますが、ここに表示された数字がその列のプレイヤーの数字と同じであった場合―――すなわち「その人が勝ち抜けた場合」―――得点を999にします。
そうでない場合は、普通に同様の処理を行います。
⑤プレイヤー別順位テーブルに数式を入力する
1問ごとの各プレイヤーの順位を記録していきます。ここで登場するのが「RANK.EQ関数」です。
この関数では、ある数がある配列内の中で何番目にあたるかを返します。もし複数の同じ数字がある場合は、その中の最も小さな数を返します。すなわち、100,80,80,50,30という配列があった場合は、それぞれ順位は1,2,2,4,5ということになるわけです。
AD7セルに以下の数式を入力し、AH87セルまでオートフィルします。
=RANK.EQ(R7,$R7:$V7)
単純ですね。得点履歴テーブルを参照し、その問題のその人の点数が、プレイヤー全体で見て何位かを返す式です。
⑥順位別得点テーブルに数式を入力する
続いてAJ～AN列ですさきほどのテーブルをもとに、各プレイヤーの得点を順位順に並べ替えて表示します。
AJ7セルに以下の数式を入力し、AN87セルまでオートフィルします。
=IF(COUNTIF($AD7:$AH7,AJ$6)=0,AI7,INDEX($R7:$V7,MATCH(AJ$6,$AD7:$AH7,0)))
まず、順位テーブルのその行の中に、ある順位の数字がないかどうかをチェックします。AJ列なら1位が、AK列なら2位が、…、AN列なら5位がいるかどうかをチェックするわけですね。
いないならば、同順位の人というわけですから、1つ上の順位と同じ数字(左隣のセル)を参照します。
一方、その順位の人がいるならば、順位テーブルと得点履歴テーブルから、点数を表示するようにします。
さてここで、INDEX関数とMATCH関数を組み合わせた方法を紹介しましょう。
INDEX関数は以前アップダウンで紹介いたしました。配列と数字を指定すると、その配列のその番号の要素を返すのでしたね。
一方、MATCH関数は、INDEXとは逆に、配列と配列の中に含まれる数字・文字列を指定すると、その数字・文字列が配列の何番目にあるかを返してくれます。なお、MATCH関数は引数が3つありますが、第1引数が「探したい数値・文字列」、第2引数が「探す配列」、そして第3引数が「照合の型」といいまして、ここに0と入力する必要があります。詳しい説明はこちらを見ていただきたいのですが、この引数はデフォルト値が1であるため、忘れずに0を入力して下さい。そうしないと、思ったとおりの数値・文字列が表示されないことがあります。
さて、INDEXとMATCHを組み合わせた部分だけもう一度よく見てみましょう。
INDEX($R7:$V7,MATCH(AJ$6,$AD7:$AH7,0))
INDEX関数は、まず最初に探したい数値・文字列の含まれた配列を指定します。ここでは、「その問題での5人の得点」ですね。
次に、その配列の何番目を参照したいかを指定します。ここでMATCH関数を使います。ここでは、「各プレイヤーの順位」の中から、その行に表示させたい順位を探し、それが何番目にあるか。MATCH関数はそれを返すわけですね。
④～⑥をまとめるとこうなります。
例えば、R～V列(得点)がそれぞれ8,0,7,11,3の場合、
AD～AH列(順位)は2,5,3,1,4となり、
AJ～AN列(ソート後得点)には、それぞれAD～AH列の4,1,3,5,2番目を参照し、11,8,7,3,0
と表示されます。
⑦勝ち抜けノルマテーブルを作る
この形式は、勝ち抜けノルマが(3位の人の点数+10)点となっている通り、時々刻々と変化します。そのため、その値を1問1問記録していかなければなりません。とはいっても、先ほどの順位別点数テーブルを作った後では大したことではないのですが。
W7セルに以下の数式を入力し、W87セルまでオートフィルします。
=AL+10
AL列に表示されるのは3位の点数です。それに10を足すだけですね。
⑧勝ち抜け者数記録テーブルを作る
続いてQ列です。ここには、各時点における、「勝ち抜けたプレイヤーの人数」を記録していきます。この情報は次の手順⑨で作るP列の勝ち抜け者テーブルを動かすために必要です。
Q8セルに以下の数式を入力し、
=SUMPRODUCT((AJ8:AN8>=W8)*TRUE)
ここで新しい関数「SUMPRODUCT」が出てきました。
SUMPRODUCT関数にはいくつか用途があるのですが、これを使うと、複数の配列について条件を指定し、それを全て満たすセルはいくつあるかを計算することができます。複数の条件式は( ) と * を使って連結していきます。ここで、各配列は同じサイズでなければなりません。また、条件式は2つ以上連結しなければなりません。
詳しい使い方はこちらをご覧ください。
ここでは、まず最初にAJ～AN列(5人の得点)を参照し、そのうち勝ち抜けノルマ(W列)以上であるかどうかをチェックします。ここでは、内部的に×××○×というような感じの結果が出るわけですね。
次に、TRUEという関数を連結しています。この関数は、問答無用でTRUEを返す引数なしの関数です。これを連結しても、さきほどの条件式の真偽がそのままの状態で通過します。何が何でも条件式が2つ以上必要のため、仕方なくこうしているのです。
⑨勝ち抜け者テーブルを作る
先ほどのW列に表示させた数字以上の点数に達したプレイヤーがいたら、P列にそのプレイヤーの番号を表示させるようにします。ここにプレイヤー番号が表示されると、次の問題以降はその人の点数が999になるように設定したのでしたね。
P8セルに以下の数式を入力し、P87セルまでオートフィルします。
=IF(Q8-Q7>=1,MATCH(Q8,$AD8:$AH8,0),"")
勝ち抜け者表示にはMATCH関数を使います。
MATCH関数では、AD～AH列(それぞれの順位)の中から、1人目の勝ち抜け者の場合は1、2人目の勝ち抜け者の場合は2であるセルの位置を調べます。
Q列には、勝ち抜けた人数が表示されていますから、1人目の勝ち抜けが出た場合は1と表示され、2人目が出たら2と表示されます。
⑩ポイント表示セルの数式を書き換える
現時点では、ポイント表示は「SUM関数を使って加点分を合計する」という方法をとっていますが、手順④でテーブルを書き換えたので、ポイント表示セルの数式も書き換えなければなりません。D4の数式を以下のように書き換え、H4までオートフィルします。
=IF(SUM(R8:R57)>=$K$4,"WIN",IF(COUNTIF(D8:D57,"x")>=$L$4,"LOSE",SUM(R8:R57)))
↓
=IF(COUNTIF(OFFSET($P$7,0,0,$M$4+1),AJ$6)>=1,"WIN",OFFSET(R$7,$M$4,0))
書き換えるのは、IF関数の条件と、FALSE時に返す値です。
条件そのものは「勝ち抜けたかどうか」で一致しているのですが、変更前はSUM関数でK4に指定したノルマ以上の得点に達したかどうかをチェックしていたのに対し、変更後は、P列を7行目からスタートして(出題問題数+1)行の範囲までの中に、そのプレイヤーの番号(AJ6～AN6から参照)があるかをチェックします。OFFSET関数は、参照するセルをずらすだけでなく、拡張することも可能です。
FALSE時に返す値については、変更前は失格条件を満たすかどうかのIF関数を記述していましたが、このルールにおいては失格は起こらないので、OFFSET関数で得点記録テーブルを参照するだけでOKです。
⑪3位の場合にポイント表示セルが緑色になるように条件付き書式を設定する
何度も書きますが、この形式においては、3位の人の点数が勝ち抜けノルマを決定します。そのため、3位の点数がどれなのかは、常にわかるようにしておくと良いでしょう。
現にSTUでは、3位の点数は緑色で表示されるようになっていますので、それを踏襲することにします。
D4:H4を選択し、条件付き書式を選択、以下の数式を入力します。
=D4=OFFSET($AL$7,$M$4,0)
ポイント表示セルの値と、順位別得点テーブルの3位の列の値(OFFSET関数を用いて出題済み問題数分だけ下にさがる)が同じ場合に、緑色に表示されるようにします。
書式は、「塗りつぶし」で下の画像の色を選択します。
⑫ポイント表示セルが黄色になる条件を、連答状態からリーチに変更する
現状では、連答状態の場合にポイント表示セル(4行目)および連答数・休み数表示セル(5行目)が黄色に塗りつぶされる設定となっていますが、これも少し変更します。連答時の条件つき書式は削除し、代わりにリーチ時(その状態で正解すると勝ち抜ける場合)にポイント表示セルのみが黄色くなる設定に変えます。
まずはポイント表示セルと連答数・休み数表示セル全体(D4:H5)を選択し、「条件付き書式」から「ルールの管理」を選択します。
上から、
・3位の点数を緑色にする
・休み中の場合に灰色にする
・勝ち抜けたら赤色にする
・失格になったら黒にする(現在は意味がない)
・連答中の場合に黄色にする
という条件付き書式が設定されています。
このうち一番下の書式を変更します。
(ついでに4番目の失格時の書式は削除してもかまいません)
まず「適用先」を「=$D$4:$H$5」から「=$D$4:$H$4」に変更し、「ルールの編集」ボタンを押します。
そして、次の数式を入力します。
=D4+OFFSET(K$7,$M$4,0)+1>=OFFSET($W$7,$M$4,0)
(現在のその人の点数)+(現在のその人の連答数+1)が、(現在の勝ち抜けノルマ)以上ならば、リーチということになるわけですね。
⑬休み中の書式をポイント表示セルから除外する
もうこれで完成、と言ってもいいのですが、最後にひとつだけ小さな厄介事を解決しておきましょう。現在の状態ですと、3位の人が休み中の場合、下の画像のように、文字だけ白くなってしまいます。
これでは、スクリーンに表示した場合、3位の人の得点が見づらくなってしまいます。
これはいったいどういうことでしょうか？
先ほどの条件付き書式の一覧をもう一度見て下さい。
これは上から優先順位の高い書式ということになっておりまして、一番上の「3位は緑色」設定では、塗りつぶしだけを指定して、文字の色はデフォルトの「自動」のままになっていたのでした。
一方、次に優先順位の高い「休み中は灰色&白文字」設定では、文字の色の設定を白と定義しておりますので、これも適用された場合、文字色が白になってしまうわけです。
いろいろな方法が考えられますが、ここではいっそのこと、休み中にポイント表示セルを灰色にはせず、連答数・休み数セルのみ灰色にすることにします。
やり方は至って簡単です。
2番目の書式の「適用先」を「=$D$4:$H$5」から「=$D$5:$H$5」に変えるだけです。
これでようやく完成です。
画面に合わせて各プレイヤーの幅を変えて下さい。
コメント
コメントを投稿