某サークルのオーディション採点を効率化するスプレッドシートとコードを書いてみた。

こんばんは、りんしょうです。
5月がスタートし、僕の所属するアカペラサークルでは学内のステージオーディションが始まってくる季節になりました。
うちのサークルでは、ステージ出演枠を争って学内で全サークル員を審査員とするオーディションが行われます。
従来ではサークルを運営する幹部がサークル員全員の紙媒体の採点シートを集計した結果、得点がもっとも高かった数バンドがその出演枠を勝ち取ります。
僕も４年前に幹部として集計に携わったのですが、この作業がとてつもなく非効率的で、

・100枚近い採点シートを手作業で電卓を使って計算する
・計算ミスがないように３グループに分けて全ての採点シートを検算する
・もし間違っていても最終的な得点の合計値で判断していたため、どこの計算が間違っていたのかわからず、また１から計算し直す
→ 集計に２,３時間がかかる、だるい。

という問題点がありました。
２年前、サークルライブを運営していた時にExcelを使ったシートを僕が作り、時間を短縮できましたが、いくつか改善点があって、

・合計値から照合をとってはいたが、目視で間違いを追っているためまだ非効率
・せっかくExcelを使ってデータ化したのにそれをうまく活用できていない

という改善点があったので、Pythonを使って、データ照合と得点の可視化をしようと思い、コードを書いてみました。

まずはスプレッドシートを作る

2年前にも作りましたが、改めてExcelを使って表計算をさせてみました。
弊サークルの現状の採点方式は

・0-20点の範囲で採点者１人が全バンドに対して点数をつける
・自分の属するバンドには採点ができない
・バンドに加算される得点は
（付けた点数-その人の採点シートの最小点）で計算される
・全採点者の加点を最終的な採点者の数で割った値の高いバンドから順位が決まる

という採点方式をとっています。
これを表計算するようなスプレッドシートを作りました。

行に各採点者の全てのバンドに対しての得点（0-20）と自分の属するバンドには"a"を入力するようにしています。
列はバンド毎の得点が入るようになっており、１行目には採点者のインデックスがわかるように番号を振ってあります。

列の右端には各採点者の合計点と最小点、そしてオーディションを行ったバンド数がカウントされるように計算しています。ここのカウントの方法は１行目にバンドの名前が入っているか、無記入かで判断するようになっています。

表の下端にはバンド毎の点数の合計値とバンドメンバーの数（"a"の数）、調整点、実際に加点する合計得点と順位が自動でランク付けされるように計算しています。
得られた合計得点に対して実際に採点した人（採点人数-バンドメンバーの数）で割ることで最終的な得点が得られます。

※調整点について
本来は１人の採点者の採点表に対して＋何点かをまず記入して、そこからバンド毎に加点の和を電卓で計算していたのですが、計算式としてはその
（バンドに対して与えられた全採点者の点数の和）-（全採点者の最小点数の和）と同じなのですが、それだとそのバンドに属する人の最小点まで余分に引かれてしまうため、"a"が入った採点者の最小点を足すことで正しく計算しています。

前回作ったスプレッドシートはMax100人くらいまでしか対応できていなかったので、Max150人、バンドの数も27バンドまで対応できるように拡張しました。
また前回だと最終的な得点や、１採点者の合計値を見比べて誤りがないかを確認していたのですが、せっかくデータ化したのならプログラミングで照合判定させましょうってことでPythonでコードを書いてみました。

またこのコードを使用するにあたっての前提条件として

・無効票（0-20以外の点や、点数記入漏れ等）が事前に取り除かれていること
・有効票について正しくインデックスが付けられていること
・シートに書かれたバンドの順番が正しく揃っていること（２年前はあらかじめ採点シートにバンドの名前を記入された採点シートを配布していました）

が満たされていることとします。

打ち込みの流れとして、
・当日の出席者と採点シートの数が一致するかを確認
・無効な採点シートを取り除く
・採点シートに番号を振る（１…）
・PCを3台用意し、それぞれスプレッドシートを用意する
・表のインデックスと採点シートの番号が一致するように流れ作業のように順番に入力をする

ここまで終えた段階で照合判定を行います。

値の照合判定を行うコード

使った言語はPython3です。研究でも使っている僕の大好きな言語です。 まず入力を終えたExcelファイルをcsv形式でエクスポートします。 名前をつけて保存から保存形式をcsvに指定します。 

3人によって入力されたそれぞれの採点シート1~3を集め同ディレクトリ内に保存します。

次にターミナルを開きこのフォルダに移動します。

Pythonのバージョンは3.6.6です。
numpyやmatplotlibなどを使っていますが、エラーが出たら随時バージョンを確認してください。
このディレクトリ内にあるcheck.pyを走らせると照合ができるようになっています。
採点シート１と２、２と３、３と１をそれぞれ照合するようになっています。

全てが一致する場合は

このように表示されるようになっています。

何箇所か採点シートを変更した場合

採点シート１のcsvファイルを

このように変更して、先ほどのcheck.pyを走らせてみると

シート２、３は一致し、１とそれぞれの変更箇所が表示されるようにしてみました。
確かに7人目、12人目、13人目のそれぞれのバンドの部分がピックアップされていますね。
これで入力ミスに関する照合を一瞬で見つけることができました。
間違っていたら、該当する番号の採点シートからお互いの値を確認すれば解決ですね。

データを可視化する

せっかく得点をデータ化したので、各バンド毎に全採点者の加点のヒストグラムを可視化してみようと思い、実装してみました。 先ほどの3つのcsvファイルが全て一致した場合に、全採点者のヒストグラムと各ベンド別の得点ヒストグラムを出力します。

全ての採点の点数のヒストグラム

実際の得点に換算したヒストグラム

バンド別の得点のヒストグラム

こんな感じで全採点者の得点の分布はどうだったのか、自分のバンドに付けられた得点の分布がどうだったのか、
出演枠のバンドとの点数の差はどれくらいで、どの得点層のコメントを重視して改善していくべきなのかが可視化によってわかると思います。
なので、僕個人としてはバンドの順位も得点も公表にすべきなんじゃないかなと思ってます。（現状は通ったバンドの名前しか公表されず、自バンドの順位と点数のみ知ることができる。）

採点シートに得点を記入する

最後に、バンド毎に採点シート（採点者のコメントがついている）を切り分けて各バンドに渡すので、それぞれの得点を記入しなければならないので、そこも出力します。いちいち計算するのは非効率なのでね。

これも3つ全てのスプレッドシートが同じであれば出力される仕様になっていて、
score.txtに吐き出されます。

あとはこのテキストファイルをもとに、実際の採点シートに＋○点を書き込めばOK。

まとめ

GW中に祖父母の家の田植えを手伝っている時にパッと思いついたので、コーディングしてみました。 これからオーディションが何度かあると思うので、今後の学内オーディションやサークルライブで採点方式に従来のものを使うのであれば、役立つんじゃないかなと思います。興味があればコードとスプレッドシート、あと使い方も教えるのでご一報を。自分で作ってもいいと思うよ。 もし誰かが使ってくれるのであればコードを綺麗に書いてないので、今後の拡張や可読性のために暇があればリファクタしていこうかなと思ってます。 僕自身面倒だな〜と思っていた作業なので後輩ちゃんたちはこういう作業をプログラミングだったり、表計算ソフトだったりITを駆使したイマドキなサークルになってほしいなと思います。

以上です。