Excelで学生のテスト結果を集計してみた

tetsutalow.hateblo.jp

この記事を読んでつらつら考えたこと。

問題

某大学では定期試験でマークシートを使い、学生のテスト結果として次のようなCSVファイルが生成されます。

	問1	問2	問3	問4	問5	問6
学生1	A	C	A	D	B	B
学生2	B	C	A	D	D	A
学生3	A	B	A	C	A	D

この試験の正解と配点は次の表になります。

問題番号	正解	配点
問1	A	5
問2	B	10
問3	A	20
問4	C	15
問5	A	30
問6	D	20

これらのデータをもとに採点作業をしなさい、というのが教員側に課せられた問題。

上原さんのブログ記事では、問題の解き方として次の3つが示されています。

長いIF文
一度各問題についての得点を得ておいて、それをSUMで合計する方法（作業セル方式）
配列数式

それで、配列数式は便利だけどちょっと不便というのがこの記事の趣旨。でも、もっとかんたんで美しい答えがあると私は考えるのです。

私の解き方

私の考えた問題の解き方は次のとおり。

ノーマルな表に、CSVファイルのデータを変換
集計関数を使って集計

少し詳しく説明します。

ノーマルな表への変換

ノーマル（normal）というのは、リレーショナルデータベース（RDB）の世界でお馴染みの「正規化された」の意味です。「正規」「正規化された」というより「ノーマル」の方が今時の日本語としてもわかりやすいし、元の英語もイメージしやすいのでこの言葉を使っています。で、Excelでも原則的に表はノーマルにすべし、というのが私の基本的な考え。

Excelで表をノーマルにする第一歩は、表を「リスト形式表」にすること。リスト形式表とは、「各列に見出しがあり、それぞれの列見出しの下に同じ形式のデータが並んだ表」（Excel豆知識20-4:エクセル:リスト形式とは）のこと。一方で、列方向にも同じ形式のデータが並ぶような表は「クロス集計表」と呼ばれています（例:【エクセル時短】クロス集計表をリスト形式に戻す方法。元データがなくてもピボットテーブルウィザードで解決！ | できるネット）。今回の学生のテスト結果はクロス集計表になります。

クロス集計表からリスト形式表への変換は感覚的には、横に並んでいるデータに名前を付けて縦に並べ替える作業になります。で、列名を「学生」「問題番号」「学生解答」として学生のテスト結果をリスト形式表にしたのがこちら。

学生	問題番号	学生解答
学生1	問1	A
学生1	問2	C
学生1	問3	A
学生1	問4	D
学生1	問5	B
学生1	問6	B
学生2	問1	B
学生2	問2	C
学生2	問3	A
学生2	問4	D
学生2	問5	D
学生2	問6	A
学生3	問1	A
学生3	問2	B
学生3	問3	A
学生3	問4	C
学生3	問5	A
学生3	問6	D

クロス集計表からリスト形式表への変換を、データを1つずつコピーするなどして手作業でやろうとすると非常に大変です。でも、幸いなことにExcelでは割とかんたんにそうした作業ができます。手順は次のとおり。

「学生」と「問題番号」のすべての組み合わせの表（直積表）を作成する
OFFSET関数を使って、クロス集計表から「学生解答」のデータを入力する

直積表の作成

「学生」と「問題番号」のすべての組み合わせを表にすると、次のようになります。

学生	問題番号
学生1	問1
学生1	問2
学生1	問3
学生1	問4
学生1	問5
学生1	問6
学生2	問1
学生2	問2
学生2	問3
学生2	問4
学生2	問5
学生2	問6
学生3	問1
学生3	問2
学生3	問3
学生3	問4
学生3	問5
学生3	問6

行数は、学生数の3と問題数の6を掛けた18になります。数が多くなるとこうした表も工夫なしで作るのはたいへんでしょう。私は次のように作っています。ほかにいい方法があれば教えてください。

見出し行を作成
「学生」列に、「学生1」〜「学生3」を入力
「学生1」〜「学生3」を問題数-1である5回コピー
表を「学生」列の昇順で並べ替え
「学生」列が「学生1」の行に「問1」〜「問6」をフィル機能などを使って入力
「問1」〜「問6」を学生数-1である2回分コピー

クロス集計表からの「学生解答」のデータ入力

学生と問題番号の直積表ができたら、「学生解答」列を追加してクロス集計表から「学生解答」のデータを入力します。こうした入力で私が好きなのは、OFFSET関数を使う方法。

OFFSET関数は、セルまたはセル範囲から指定された行数と列数だけ移動した位置にあるセル範囲の参照を返します。今回のクロス集計表では、表のもっとも左上が学生1の問1の学生解答になっていて、学生番号の分だけ行、問題番号の分だけ列がそれぞれ移動します。

また、Excelでは指定した文字列の一部を置換するSUBSTITUTE関数を使うことで、「学生2」や「問題3」の文字列から数字だけを取り出すことができます。

こうしたOFFSET関数やSUBSTITUTE関数を使うことで、「学生解答」のデータを入力します。

=OFFSET(Sheet1!$B$2,SUBSTITUTE(A2,"学生","")-1,SUBSTITUTE(B2,"問","")-1)

Sheet1のもっとも左上に「学生解答」のクロス集計表データが入力されていて、Sheet3にリスト形式表を作成する場合のスクリーンショットは次のようになります。

「学生解答」が入力されたら、今度はその解答が正解かどうか自動判定するための列を追加していきます。D列を「正解」列を追加し、VLOOKUP関数で問題ごとの正解データを入力します。Sheet2のもっとも左上に試験の正解と配点の表がある場合、「正解」列の見出しのすぐ下の行（D2セル）に次の数式を入力し、ほかの行にコピーします。

=VLOOKUP(B2,Sheet2!$A$2:$C$7,2,FALSE)

「学生解答」列と「正解」列から、その問題が正解かどうか判定できます。E列を「正答？」列にし、判定結果は
を追加し、VLOOKUP関数で問題ごとの正解データを入力します。Sheet2のもっとも左上に試験の正解と配点の表がある場合、「正答？」列の見出しのすぐ下の行（E2セル）に次の数式を入力し、ほかの行にコピーします。

=C2=D2

「得点」は、「正答？」列で正解と判定した場合には問題ごとの「配点」で決められた点数、それ以外の場合は0点とします。F列を「得点」列とし、見出しのすぐ下のセル（F2）に次の数式を入力し、ほかの行にコピーします。

=IF(E2,VLOOKUP(B2,Sheet2!$A$2:$C$7,3,FALSE),0)

「得点」を求めるだけならば「得点」列の数式を少し複雑にすることで「正答？」列は不要です。ただし今回は正答数も集計するために「正答？」列を追加しています。

〔参考〕
=IF(C2=D2,VLOOKUP(B2,Sheet2!$A$2:$C$7,3,FALSE),0)

今回は「4択」「複数解答なし」「部分点なし」ということで、正解かどうかの判定はシンプルです。試験の実施方法によってはより複雑な判定が必要になるでしょう。

以上の作業で、ノーマルなリスト形式の表ができました。

集計

ノーマルなリスト形式の表ができれば、いよいよ集計作業本番。とはいえ、『できるExcel』などのExcel入門書にそのまま載せてもいいくらいかんたんな作業です。

COUNTIFS関数やSUMIFS関数を使って集計する場合の手順は次のとおり。

新しいシートを挿入
挿入したシートのA列を「学生」列にし、すべての学生（「学生1」〜「学生3」）を入力
挿入したシートのB列を「正答数」列にし、「学生1」〜「学生3」の正答数を関数を使って入力
挿入したシートのC列を「合計点」列にし、「学生1」〜「学生3」の合計点を関数を使って入力

正答数を求めるには、「正答数」列の見出しのすぐ下のセル（B2）に次の数式を入力し、ほかの行にコピーします。

=COUNTIFS(Sheet3!$E$2:$E$19,TRUE,Sheet3!$A$2:$A$19,A2)

合計点を求めるには、「正答数」列の見出しのすぐ下のセル（C2）に次の数式を入力し、ほかの行にコピーします。

=SUMIFS(Sheet3!$F$2:$F$19,Sheet3!$A$2:$A$19,A2)

これで、すべての学生の正答数と合計点が求められます。だいぶ説明が長くなりましたが、長いIF文や作業セル方式、配列数式などの方法に比べたらずっと楽ですし、途中の計算ミスも発生しにくいと考えます。

なお、ここまでに出てくる表をテーブルにしてから作業すれば、数式でデータを入力するときにコピーする手間が小さくなり、表が見やすくなります。また、集計作業はピボットテーブルを使って行うこともできます。

まとめ

集計作業を行うときは、クロス集計表よりもノーマルな表の方がやりやすいです。クロス集計表を集計する場合、そのまま集計の作業するよりも、Excelの機能を使ってクロス集計表をノーマルな表にしてから集計作業するほうがやりやすい場合が多いです。

この記事を見てExcelを使った作業を効率化できる人がいたらうれしいです。