Excelで, クラス毎(学年, 習熟度別など)に成績データを管理する

学年毎に成績データを管理し, かつ習熟度別クラスの成績データも管理したい.

2つに別けるのは, 二度手間かつ容量の無駄なので, これらを一括で管理したいと考えました.

ここで言う成績データは, 一度の試験で配点などの情報も残しておくものです.
(数学の試験の場合は, それぞれの問題の理解度も測りたいので, このようなものを作っていました.)

具体的には, 次のような1学年のクラス名簿と配点データがあったとします.

(ちなみに名前は, ランダム名前ジェネレータ から作成したものです.)

このように1学年でまとめて成績データを管理したとしましょう.

(学年単位の共通な試験に対して有効です.

定期試験であれば, 大問別の平均点を出す方が良いですが,

理解度チェックの試験であれば, 正答者数のみで良いと考えています.)

まずは, 試験結果などが入力された状態を見て下さい.

正答者数は,

=COUNTIF(範囲,条件)
=COUNTIF(D6:D25,D3)

を用いることで, 表示できます.
(後はオートフィル機能で簡単に.)

受験者数は, 生徒数から欠席者数を引いて与えます.
欠席者数は, 正答者数の応用で,

=COUNTIF(範囲,条件)
=COUNTIF(K6:K25,"欠席")

を用いれば良いです.

平均点も合計から受験者数で割ることで取得しておきます.

同様にして, 内訳(クラス1)と内訳(クラス2)までは得ることができます.

ここまでは簡単です.

次に, 習熟度別のデータを摘出したいのですが, 人数が多いときは, 整理するのが大変です.
そのために, 補助番号を振ります.
(これは習熟度別の出席番号を参考にすれば問題なく振れるはずです.)

この補助番号を使って, 並び替えます.
並び替え用のシートを新たに作成し, そちらにデータをコピーします.

ここで, 次のようにして補助番号を参考に, 並び替えを実行します.

すると次のようになります.

この補助番号を昇順にしたことで, 習熟度別のクラス別名簿を作ることができます.
まずコピペとオートフィル機能を使って, 新たにシートを作成します.
(Aクラスは5名でしたが, 余分な10名分作っておきましょう. (B, Cクラスにも適用が簡単になります.))

受験者数は, クラス名簿(一覧)から取得しておきましょう.

=COUNTIF(範囲,条件)
=COUNTIF(クラス名簿!A6:A25,"A")

これで, Aクラスの総生徒数が簡単に取得できるので, 後は欠席者数を引けばよいです.

あとは, 先ほど並び替えた生徒情報から引き出します.

=VLOOKUP(検索値,範囲,列番号,[検索条件])
=VLOOKUP($A4,並び替え!$A$2:$J$21,COLUMN()-1,0)

最初の検索値を方縛りし, 範囲は全縛り, そして列番号は, COLUMN()で指定することで, オートフィルを使えるようになります.
これで, 10名分オートフィルすると,

106以降は参照できないので, エラーが出ますが, 他のクラスも作るために今は放置しておきます.
正答者数もクラス名簿一覧のときと同様に設定をします.

(Bクラスにコピーしたものです.)
受験者数の設定も"B"に変えることで, 全部設定ができたので, エラー部分を全部削除しておきます.
(もちろん, いろいろ組めばこれらも回避できますが, 今は手作業で...)

これで, クラス名簿一覧へそれぞれのクラスデータを参照することで, まとめて管理できます.

だたし, 同じ大問, 小問構成でない限り, これらの作業を繰り返す必要があります.
(同じ構成なら, 名簿一覧の成績データのみ変えれば, 全部変わります.)

あまり定期的に行わないのであれば, 苦労はしませんが, 大体理解度確認をしたい状況を想定すると, 頻繁に行う可能性が高いです.

VBA, マクロを組むことで, これらのことは簡素化できるはずです.
(これができたらまた書きたいと思います.)