Excelを使ってマークシートの選択式試験結果を集計するには配列数式が便利…だけど…

私はネ申Excelは大嫌いですが、Excelそのものは好きですよという話。

大学は定期試験シーズンを迎えました。私のような教員にとっては採点シーズンでもあります。私学は大人数クラスも多いので採点は大仕事です。なので、科目によっては大学の定期試験と言えどマークシートを使うこともあります。そういやセンター試験で記述式が導入されたときに、マーク式だと暗記ばかりになって思考力が問えないからとかいう人がいましたけど、それは工夫次第だと思いますよ…

さて、本学では試験答案に専用のマークシートが使えます。ですが、マークシート式の試験であっても、採点は各教員が自分でやることになっています。答案用紙をマークカードリーダにかけると、各解答に対するCSVファイルがこんな感じで生成されます↓（本物じゃないですよ、わかりやすさのために色々変えてあります）。

f:id:tetsutalow:20200126151254p:plain — 元データの例

これの採点作業は自分で行うことになります。つまり、各設問ごとに正答と配点を書いた行を作って、正答率や合計点の行や列を加えていくわけです。

f:id:tetsutalow:20200126153615p:plain — 正答と配点の行を加える

この際にいきなり合計点を求めようとすると、例えばH2のセルには

=IF(B2=B$5,B$6,0)+IF(C2=C$5,C$6,0)+IF(D2=D$5,D$6,0)+IF(E2=E$5,E$6,0)+IF(F2=F$5,F$6,0)+IF(G2=G$5,G$6,0)

とか書いてそれを縦にフィルすればいいのですが、問題の数が増えてくると式が横長になってミスがないか確認するのが大変ですよね。なので、よく↓のように、一度各問題についての得点を得ておいて、それをSUMで合計する方法で計算しますね。

f:id:tetsutalow:20200126154526p:plain — 部分点を得ておいて合計する手法

この場合はH2のセルは

=IF(B2=B$5,B$6,0)

としておいて縦横にフィルし、正答数はN2のセルに

=COUNTIF(B2:G2,">0")

としておいて縦にフィル、合計点はO2のセルに

=SUM(H2:M2)

としておいて縦にフィル、ですよね。

これでもいいのですが、どうにもH列からM列が無駄だなぁと思いませんか。特に、これは6問しかない問題だからいいんですが、50問とか出題すると表が横に長くなりすぎて、これ要らないのになぁと思うわけです。まぁ列を非表示にしちゃうとか、横幅を縮めて見えなくしちゃうとか、工夫はできなくはないのですが、どうにも計算機屋としては敗北感があるわけで。

どうしてもその式を一発で書きたい。そういう時には配列数式が便利です。

support.office.com

でまぁ、これを使うと要するに上記でいうH列からM列が要らないわけです。具体的にはこうやります。

f:id:tetsutalow:20200126184337p:plain — 配列数式を用いて、複数セルの比較と合計算出を1つの式で

正答数を求めるH2のセルには

=SUM(IF(B2:G2=B$5:G$5,1,0))

と書いて、数式を確定するときにCTRLとSHIFTを押しながらEnterを押します。すると式全体が{}で囲まれ、意味論的にはSUMの中の式が配列に展開されます。よって=SUM(IF(B2=B5,1,0),IF(C2=C5,1,0),…,IF(G2=G5,1,0))と同じ意味になります。こんな変な操作法を強制せず、手で{}を書かせてくれよと心から思うんですが、なぜか許してくれません…歴史的事情なのでしょうか。このためこの配列数式のことをCSE数式（CSE Fomula）とも言うようです。

H2のセルをもう少し単純に書くには

=SUM(INT(B2:G2=B$5:G$5))

も使えます（同じくCTRL+SHIFT+ENTERをお忘れなく）。こうすると、INTは()内がTRUEなら1、FALSEなら0にしてくれるので、結局同じ値が計算できます。これは鳥取環境大の齊藤明紀先生に教えて頂きました。感謝。

で、合計点は同様にI2のセルに

=SUM(IF(B2:G2=B$5:G$5,B$6:G$6,0))

と入力して、CTRL+SHIFT+ENTERで確定すればOKです。いずれも縦にフィルすれば望みのものが得られます。

そもそもExcelでは配列が使用できます。例えば{0,1,2}と書けば行方向に3要素の配列が作れます。セミコロンで区切ると列方向にも伸ばせるので、{0,1,2;3,4,5}なら、2行3列の配列（行列）を表すことができます。配列同士の計算も出来たりします。なお最近「スピル」と呼ばれる機能がついて、このような配列の結果を隣接するセルに反映できるようになりました。

support.office.com

このスピルをはじめ、最近Excelの機能追加は配列数式関係が多いように思います。

forest.watch.impress.co.jp

しかし、それにしてもこの配列数式は使いにくいです。そもそもCTRL+SHIFT+Enterという直感的に解りにくい操作を要求する時点でホントどうにかして欲しい。それに、配列として展開されたときにどういう意味論になるかは関数ごとに決まっているようで、どうにも直観に合わない感じの展開がされることがあります。そもそもどの関数でも使えるわけではないので、「あれ、こうやりたいときはどの関数使ってどうやったらいいんだっけ？」ってのがすぐに出てこないもどかしさもあります。結局イディオム的にしか使えないのですよね…

マイクロソフトもさすがにこのCSE数式はひどいと思っているらしくて、将来は非推奨にする方向のようです。でもそれならば、{}を使った配列表記やセル範囲の配列化などがもっと手軽に出来るようにして欲しいと思うのです*1が。まぁ最近は、面倒になったらPythonのopenpyxl使ってExcelのセルを直接いじりに行くし、pandas使って散々いじった後でExcelに書き戻すなんてこともやりますが、単純ループで済むようなことのためにPython使うのもまた面倒なので、出来るだけ配列数式で済ませたいと思っています。

おまけ： PythonでExcelいじりたい人にお勧めしてる本はこれ。