基本③：勝ち抜け・失格等のルール設定を変更できるようにする

基本シリーズ第3回です。
前回…基本②：勝利・失格時に色が変わるようにする
次回…基本④：タイトルバーを作り、問題数やラウンド名などを表示する

基本①では、勝ち抜け条件を「7問正解」、失格条件を「3回誤答」としました。
「by」の得点表示の作り方では、勝ち抜け条件を「100点以上到達」、失格条件を「10回誤答」としました。
しかし、場合によっては5○2×とか3○1×とか10○10×とか、色々なルールに変えたいこともあるでしょう。
その都度、正解欄・誤答欄の数式の中の数字を変えるのは、少し手間がかかります。
そこで、普段は表示しない場所に「ルール設定セル」を設け、そこに勝ち抜け条件や失格条件といった数値を入力することで、簡単にルール設定を変えられるようにします。

今回は、 基本② で作成したものがすでにできている状態から作り方を記します。

完成品はこちらからダウンロードできます。

【使用するExcelの機能】

・絶対参照

【作り方】

①右側の場所に、ルール設定セルを設ける

N3セルに「勝ち抜け」、O3セルに「失格」と記入します。
N4セルを勝ち抜け条件、O4セルを失格条件の記入欄に使うわけです。
わかりやすくするために、N4,O4セルに色をつけておきます。

編集中は表示倍率80%ですが、本番では表示倍率100%で使用するため、L列より右の範囲は画面に表示されません。
このように、「本番では見えない」「でも右にスクロールすればすぐ変更可能」という場所に設定セルを配置します。

ここではとりあえず、N4セルに「10」、O4セルに「4」とでも入れておきます。

②ポイント表示セルの数式が、ルール設定セルを参照できるように変更する

D4セルの数式を以下のように変更し、K4セルまでオートフィルします。

下線部の2箇所が変更点です。

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

先ほど説明したとおり、N4セルは勝ち抜け条件の値、O4セルは失格条件の値を入力するセルです。いままでは下線部のところはそれぞれ「7」「3」となっていましたが、それをセルの名前にしたわけです。

これにより、先ほどまで「WIN」「LOSE」だった表示がなくなりました。勝ち抜け・失格条件が変更されたためです。

ここで、黄色の部分を見ると、「N4」ではなく「$N$4」、「O4」ではなく「$O$4」となっています。しかし、範囲をあらわす「D7:D56」では$マークはついていません。この違いは何でしょうか？

セル名に$マークをつけると、絶対参照を行うことができます。これは、オートフィルによってセルの数式をコピーする際、「行や列が変わっても参照するセルを変えたくない」という場合に使用するものです。

一方、$マークをつけない場合は相対参照となり、オートフィルによって数式をコピーすると、オートフィルされたセルの位置が1つ隣に移るにつれて、数式の中で指定されるセルも隣に移ります。

具体例を挙げましょう。D4セルとF4セルをそれぞれ見て下さい。

＜D4＞

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

＜F4＞

=IF(SUM(F7:F56)>=$N$4,"WIN",IF(COUNTIF(F7:F56,"x")>=$O$4,"LOSE",SUM(F7:F56)))

D列のプレイヤーもF列のプレイヤーも、同じ試合で戦っていますから、勝ち抜け条件・失格条件は同じです。そのため、この2つの値は両者とも同じセルを参照しなければなりません。だから絶対参照を使います。
一方、正誤記録は、D列のプレイヤーならD7:D56の範囲に、F列のプレイヤーならF7:F56の範囲に入力されています。そのため、参照すべき正誤記録は、列によってズレていなければなりません。だから相対参照を使います。

数式入力が完了したら、正誤記録セルに「1」や「x」を入力し、設定したとおりの条件で「WIN」や「LOSE」が表示されることを確認しましょう。

絶対参照と相対参照を使い分けないと、思わぬ場所のセルを参照してしまい、思った通りの処理が行われないことがあります。

画面上部の数式ボックスをクリックして入力可能な状態にすると、そのセルの数式がどこのセルを参照しているのかがわかります。数式内のセル名の色と同じ枠で囲まれています。もし間違った場所を参照していた場合は、数式を手打ちで直すか、選択範囲の枠をドラッグで正しい位置に移動します。