【参加者管理①】ペーパークイズの成績を管理する

数十人以上の参加者を集めるような大会においては、各参加者の成績や勝ち残り状況を管理する必要があります。
そこで今回からは、参加者を管理するための方法を解説します。

参加者管理用のExcelシートとして、「名簿&ペーパー成績入力シート」と「成績管理シート」の2つを作ります。
「名簿&ペーパー成績入力シート」には、大会前にエントリーしてきた参加者たちの名前の一覧とペーパークイズ用の成績入力欄・コース別参加希望入力欄を作っておきます。
「成績管理シート」では、「名簿&ペーパー成績入力シート」で入力したペーパークイズの成績および、各ラウンド・各組の得点表示シートに残った試合記録をもとに、各参加者の勝ち残り状況などを記録します。これにより、次のラウンドの対戦カードを組むことができるようになります。

今回は、まず「名簿&ペーパー成績入力シート」を作ります。
このステップまでの完成品はこちらです。

【作り方】

＜準備編＞

①各列の見出しを作り、1～2行目を固定する

1行目を空け、2行目のA列から順に以下の文字列をそれぞれ入力し、適当な幅に調節します。
なぜ1行目を空けるかというと、手順④で近似値問題の答えを記入するからです。

・No.
・名前
・得点
・得点順位
・近似値(問題数の分だけ用意する)
・コース別希望(実施するコースの分だけ用意する)
・近似値誤差(問題数の分だけ用意する)
・近似値順位(問題数の分だけ用意する)
・総合順位

続いてカーソルをA3に合わせたあと、「表示」タブ内の「ウィンドウ枠の固定」ボタンから「ウィンドウ枠の固定」を選択します。
これで、いくら下にスクロールしても1～2行目だけは常に画面の上部に表示されるようになりました。
ウィンドウ枠の固定については基本①でも解説しております。

②エントリー番号を入力する

先ほど一番上の行に「No.」と記入したA列には、上から1,2,…という番号を入力しておきます。
A2セルに「1」、A3セルに「2」と入力したら、参加人数より少し多い行までオートフィルします。

なぜエントリー番号を用意しておくかというと、大抵のペーパークイズの順位判定方法として、点数の高さ→近似値問題の誤差の小ささ(or1問目からの連答数)→エントリーの早い順という基準が主に使われるため、エントリー番号という情報が必要なのです。

※この記事では、参加者は80名、近似値問題を2問用意した場合のシートを作っていく。

③エントリー順に名前を入力する

B列の2行目以降に、解答者名をエントリーの早い順に記入していきます。

近年では大会参加者を集める方法として、Webフォームサービスが使われることが多くなってきています。その場合、参加者一覧表の画面から参加者名をコピペしてExcelに貼り付けることが可能だと思われます。

④近似値問題の答えを入力し、「誤差」のセルに数式を入力する

先ほど作った空白の1行目を近似値問題の解答欄として使います。
「誤差」の列(「近似値」の列でも構いません)の1行目を入力欄として使います。
ここでは、下の画像のオレンジで塗ったセルを答え入力欄としました。

次に、「誤差」の列のセルに近似値の誤差が表示されるように数式を入力します。
今回はK～L列を誤差表示に使用していますので、K3セルに以下の数式を入力し、L列の最後(今回はL82セル)までオートフィルします。

=IF(E3="*",0,1/(1+ABS(E3-G$1)))

ここでポイントとなるのが、誤差の扱い方です。
近似値クイズでは、ふざけて大きな数字を書く人や何も答えない人というのがしばしば現れます。
無解答の場合は問答無用で解答した人たちよりも順位が低くなるようにしなければなりません。いわば「誤差無限大」というべき扱いになります。
しかし、Excelには無限大を返す関数はありません。どうすればよいでしょうか？

ここでは、誤差をある関数で換算することにしてみます。
「誤差0(=ジャスト)の場合に1、その後誤差が増えるたびに単調減少し、極限0となるような関数」を用いて、誤差を換算します。「誤差無限大」の代わりに「換算値0」とし、何かしら記入していれば0よりもわずかに大きい値となるわけです。例えば、1/{1+(誤差)}という関数ならばこれを満たします。
そして、この値をRANK.EQ関数で順位化します。

入力した数式に立ち返ってみましょう。
まず、誤差入力セルに「*」が入力されたら、即座に誤差換算値が0になります。これは近似値問題が無解答の場合、近似値解答セルに「*」と入力するよう決めておくのです。
そして、無解答でない場合は、さきほどの関数を用いて誤差を換算します。

なお、Excelにおいて、数式によって処理できる数値の範囲はは、-1.7976931348623158e+308から1.7976931348623158e+308までとなっております。
※https://support.office.com/ja-jp/article/Excel-%E3%81%AE%E4%BB%95%E6%A7%98%E3%81%8A%E3%82%88%E3%81%B3%E5%88%B6%E9%99%90-1672b34d-7043-467e-8e27-269d656771c3による

しかし、問題によっては解答者が思考を放棄し、この範囲外の値を解答する恐れがあります(実際、筆者がスタッフを務めた大会で、「10の1000乗」といった数値を答えた方がいらっしゃいました)。そういった解答者でも、無解答の参加者よりは上位にしないといけず、処理に困ってしまいます。

このような事態になるのを防ぎたい場合は、「あらかじめ解答として認められる数値の範囲を定めておき、その範囲外の値を解答した場合は、無解答と同様に扱う」などのように対処しておきましょう。

⑤「得点順位」「近似値順位」「評価値」「総合順位」のセルに数式を入力する

先ほども述べましたが、ペーパークイズの順位判定方法は「点数」「近似値問題の誤差」「エントリー順」といった基準を用いて判定を行います。

得点順位

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

=RANK.EQ(C3,C$3:C$82)

なお、得点入力セル(C列)に何も数字が書かれていない場合はRANK.EQ関数がうまく機能せず、「#N/A!!」と表示されてしまいますが、C列に何らかの数値を入力すればきちんと順位が表示されますのでご安心ください。

近似値順位1・2

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

=RANK.EQ(K3,K$3:K$82)

評価値

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

=D3+0.01*M3+0.0001*N3+0.000001*A3

総合順位

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

=RANK.EQ(O3,O$3:O$82,1)

評価値は順位の数字を使って足し算した値なので、「評価値が小さいほど上位」ということになります。よって、RANK.EQ関数の第3引数に1を指定してやることで、「値が小さいほど上位」という扱いで処理を行います。

ここでは、まず得点だけで評価した順位と近似値だけで評価した順位をRANK.EQ関数で計算します。
そこから、(参加者が99人以下の場合)「得点順位+近似値順位1/100+近似値順位2/10000+エントリー番号/1000000」という値を「評価値」とし、この値をあらためてRANK.EQ関数で順位づけすることで最終順位を決定します。
各順位を２桁ずつずらして足し算することで、決められた優先順位で各選手の順位を評価することができるのです。

100人以上の場合の評価値は得点順位+近似値順位1/1000+近似値順位2/1000000-+エントリー番号/1000000000となります。

＜本番編＞

⑥ペーパークイズの得点・近似値解答を入力する

採点担当スタッフから送られてきた解答用紙を見て、得点と近似値解答を入力していきます。

この際、解答用紙が誰のものかをチェックしてから、Excelのどの行にその人がいるかをすぐにチェックできるよう、少し工夫しておくとよいでしょう。
例えば、解答用紙にあらかじめエントリー番号と名前を印刷しておく、フィルター機能を使って名前を五十音順に並べておく、などです。

オレンジ色のセルが、使用時に入力すべきセルです。
(上の画像では「コース別希望」の部分は入力しておりませんが、使用時はこちらも入力してください)
それ以外は処理に使うのでいじらないでください。
セルのロックをしておくのもいいかもしれません。

これで、一番右のL列にペーパー順位が出力されるようになりました。

(次回に続く)