Excel得点表示道

【2019年1月13日に追記した点については赤文字で表記しています】 ※完成品はDL可能ですが、作り方は現在準備中です。 今年8月に開催される「STU XX」で新たに導入された、SemiFinalのルールの得点表示の作り方を説明します。 ルールは以下の通りです。 ・3セット制の早押しクイズ。各セットの制限時間はそれぞれ12分、10分、8分。 ・各セット、全員「0☆, 0pt.」の状態でスタートする。 ・正解で「☆」を1つ獲得。 ・☆を3つ獲得したプレイヤーが1人発生した時点で、各プレイヤーの得た☆はポイントに交換される。交換レートは以下の通り。 　1☆…1pt. ／ 2☆…2pts. ／ 3☆…4pts. ・ 6pts.以上を持った状態で☆を3つ集めれば勝ち抜けとなり、そのセットは終了する。 ・誤答は、第1セット…+1×&4回休み ／ 第2セット…+1×&3回休み ／ 第3セット…+1×&2回休み。 　休み状態はセットをまたがず、セット終了に伴い解除される。 ・3×でそのセットのみ解答権剥奪となる。 ・制限時間によりセット終了となった時点でまだ誰も勝ち抜けていなかった場合、各プレイヤーが持っていた☆を先述のレートに従ってポイントに交換し、ポイントの最も高いプレイヤーが勝ち抜けとなる。同点の場合は1○1×のサドンデスを行う。 「3☆1人抜け」の短期戦が何度も何度も繰り返される形式です。 ポイントを稼ぐだけならば3☆を取る必要はありませんが、勝ち抜けるには、どうしても最後に1回「3☆1抜け」を成功させなければなりません。 それでは、 「m○n休クイズ(固定型)」の得点表示の作り方 の続きから作っていきます。 完成品は こちら です。 「STU XXI」において、以下のルール変更が行われました。 ・セット終了時、ポイント最下位の者は失格となる。 ・勝ち抜け条件：6pts.持った状態で3★獲得→(第1セット:6pts.、第2セット以降:セット開始時の最高得点+6)pts.持った状態で3★獲得 こちらのルールのものも作成いたしました。 さらに、解答者が9枠、シートが3セット分あるので、本番と同じようにプレイすることができます。 完成品は こちら です。 【使い方】 通...

2022/07/24追記：プログラムにバグがあったため、作り方の修正および完成品ファイルの差し替えを実施しました。作り方の修正箇所は赤字で示しています。 前回は、上座クイズの基本である解答者の上下の処理のみを行える段階まで作りました。 ここから、さらにルールを付け加えていきます。 今回は、かつてSTUの準決勝で行われていた「Head of the Stage」の得点表示の作り方を解説します。 このクイズは9人で行います。まず、客席から見て左側(下手)から前Rの成績順に並びます。 左に行くほど「上位の席」となっており、早押しクイズで正解すると2つ上位の席に上がることができます。 また、このクイズではポイントを稼ぐ必要があり、1位席で正解すると+4p、2位席で正解すると+2p、3位席で正解すると+1pとなります。それ以外の席で正解してもポイントは増えないため、勝ち抜けるためにはまず上位席に登らなければなりません。 10p以上に到達すれば勝ち抜けとなります。 ただし、誤答はいかなる位置でも即座に最下位の席に落とされてしまいます。また、誤答数はカウントされており、通算5×で失格となります。 (最下位席で誤答した場合は2×が加算) それでは、 「上座クイズ」の得点表示の作り方(基本形) の続きから作っていきます。 完成品は こちら です。 【作り方】 ①ルール設定をする デフォルト設定は以下の通りです。 勝ち抜け：10 失格：5 上昇：2 下降：8 「下降」は(プレイヤー数-1)以上の数ならば、誤答時に最下位席降格と同じことになります。 ②勝ち抜け者数記録テーブル・失格者数記録テーブル・ポイント数記録テーブル・×数記録テーブルを作る 上座クイズの得点表示では既に3つのテーブルを作りましたが、ここではさらに4つのテーブルを増設します。 また、プレイヤーの人数も9人に増やす必要があります。 [既設]解答者配置テーブル(O～W列) [新設]勝ち抜け者数記録テーブル( X列 ) [新設]失格者数記録テーブル(Y列) [既設]順位加減テーブル(Z～AH列) [既設]順位更新テーブル(AJ～AR列) [新設] ポイント数記録テーブル( AT～BB列) [新設] ×数記録テーブル( BD～BL列 ) 最...

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」シリーズの得点表示の作り方 の続き( 「初代」タブ )から作っていきます。 完成品は こちら です。 【新しく登場するExcelの機能】 ・関数「RANK.EQ」 ・関数「MATCH」 ・関数「SUMPRODUCT」 【作り方】 ①プレイヤー数を5つに減らす この形式は、「全体の3位」という概念がある以上、5人専用形式となります。 当ブログではこれまで形式にかかわらず8人用として作ってきましたが、今回に限り、5人に削減します。 見出しをドラッグしてI～K列を選択し、右クリックメニューから「削除」を選択します。 すると、それに伴い、右側の「連答状態記録テーブル」「加算ポイント記録テーブル」「休み数記録テーブル」の6～8枠に相当するセルが全て「#REF!」と表示されてしまいます。セル内の数式で参照していたセルがなくなってしまったからですね。 これらのテーブルも、5枠に削減しましょう。...

STUの3R・コース別の1つ、10divide10のルールを説明します。 試合開始時、得点は全員10pです。正解すると10pが加算され、N回目の誤答でポイントがN分の1倍になります(小数点は切り捨て)。すなわち、1回目の誤答はお咎めなし、ということです。 100p以上に達すれば勝ち抜け、0pになると失格となります。 形式名こそ by の対極ですが、「誤答で一気に点数が下がる」という点では アップダウン に似ているかもしれません。 しかし、アップダウンでは「1回目の誤答でリセット、2回目の誤答で失格」なのに対し、divideは「1回目の誤答は無罪、2回目の誤答以降大減点される。点数さえ1p以上を維持していれば何回誤答してもいい」ということで、誤答罰はやや軽いです。しかしそれゆえに、プレイヤーへの誤答の抑止力は弱く、つい何度も誤答を重ねてしまい、点数が延びなくなってしまうこともあります。 それでは、 基本⑤ の続きから作っていきます。 完成品は こちら です。 【作り方】 ①ポイント記録テーブルを作る N7:U56を選択し、格子を描きます。 次に、N6:U6(0問目=初期状態)の全ての範囲に「10」を入力します。 そして、N7セルに以下の数式を入力し、U56までオートフィルします。 =IF(D7="x",ROUNDDOWN(N6/COUNTIF(D$7:D7,"x"),0),N6+D7*10) この形式は誤答時の計算が特殊なので、誤答したかどうかをまずチェックします。誤答した場合は、(持っていたポイント)÷(この問題を含めてのこれまでの誤答回数)を小数点以下で切り捨てる処理を行います。 切り捨て処理ならば、以前 Swedish の作り方にも登場したROUNDDOWN関数の出番です。第1引数に数値、第2引数に小数第何位までを残すか指定します。今回は小数点以下は全て切り捨てるため、0を指定します。 一方、誤答していない場合は、1問前の得点に正解した場合は10pを加え、そうでない場合は加えないようにします。ここでは、正解時には正誤スルー記録セルに「1」が入力されるため、この数式できちんと動作します。 ②ルール設定を変える 勝ち抜け条件(N4セル)および失格条件(O4セル)を変えま...

STUの3R・コース別の1つ、「Square20」のルールを説明します。 この形式では、「 by 」同様、各プレイヤーに2つの変数が与えられ、その積が得点となります。この2つの変数をα、βと呼ぶことにします。試合開始時、α・βはともに1です。 そして、奇数問目を正解するとαが1増加し、偶数問目を正解するとβが1増加します。α×βの値が20以上に達すれば勝ち抜けとなります。 一方、奇数問目を誤答するとαが、偶数問目を誤答するとβが 0にリセットされます。すなわち、その人のスコアは0になります。 また、通算3回の誤答で失格となります。 うまく問題が噛み合えば最低7問正解で勝ち抜けられる(5×4=20p)一方、タイミングが悪ければいくら正解を重ねても思うように点が伸ばせません。 また、誤答罰も厳しい。誤答直後は0に逆戻りとなり、しかもたった3回で失格となります。 それでは、 基本⑤ の続きから作っていきます。 完成品は こちら です。 【新しく出て来るExcelの機能】 ・関数「MOD」 【作り方】 ①ルール設定セルにルールを入力する 勝ち抜け条件を20p、失格条件を3にします。 ②α記録テーブル・β記録テーブル・ポイント記録テーブルを作る N～U列を「α記録テーブル」、W～AD列を「β記録テーブル」、AF～AM列を「ポイント記録テーブル」とします。 まずはそれぞれのテーブルについて格子を引きましょう。 次に、数式を入れていくのですが、そのまえに、初期値はα・βともに1でしたね。なので、1問目の1つ前である6行目の全てのプレイヤーに、両テーブルとも1を入力しておきます。 つづいて、式を入力します。 α記録テーブル N7セルに以下の数式を入力し、U56セルまでオートフィルします。 =IF(MOD($C7,2)=1,IF(D7="x",0,N6+D7),N6) β記録テーブル W7セルに以下の数式を入力し、AD56セルまでオートフィルします。 =IF(MOD($C7,2)=0 ,IF(D7="x",0,W6+D7),W6) ポイント記録テーブル AF6 セルに以下の数式を入力し、AM56セルまでオートフィルします。 =N6*W6 さてここで...

「連誤答」とは、「ある人が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問前の時点で連誤答状態だったか...