【Excel決定版】VBAで作る成績一覧表 〜ピボットテーブルに立ちはだだかる「三つの壁」を完全攻略〜

多くの教育現場や研修の場面で作成される「成績一覧表」。手元にある全学生の成績データから、縦軸に「学生名」、横軸に「科目名」を配置した見やすい表を作りたい。Excelに慣れた方なら、誰もがまず「ピボットテーブル」を思い浮かべるでしょう。

しかし、そこで多くの人が最初の「壁」にぶつかります。そして、その壁の向こうには、さらに厄介な問題が待ち構えているのです。

この記事では、Excelでの成績表作成において多くの人が直面する三つの大きな課題を乗り越え、実務で本当に使える一覧表を自動作成するための手法を、初心者にも分かりやすく紹介します。

第一の壁：なぜ評価（A, B, C）はピボットテーブルで集計できないのか？

まず、手元には以下のような、いたってシンプルな成績のローデータ（元データ）があるとします。

これを元に、あたりまえのようにピボットテーブルで操作を始めます。行に「氏名」、列に「科目名」を配置し、最後に値フィールドへ「評価」をドラッグ＆ドロップ。しかし、画面に現れたのは期待とは全く違う結果でした。

値フィールドには「1」という無機質な数字が並んでしまいます。これは、ピボットテーブルの値フィールドが、基本的に数値を「集計（合計、平均、個数など）」するために設計されているからです。「A」や「B」といったテキストデータを、そのまま表示する機能は標準では備わっていません。これが、多くの人が最初に直面する「ピボットテーブルの壁」です。

第二の壁：さらに厄介な「再履修」という問題

そこで、関数などを使って「第一の壁」を乗り越える方法を考え始めたところで、さらに根深く、厄介な問題に気づきます。それは、「再履修」の存在です。

実際のデータは、もっと複雑です。一度単位を落としてしまった学生が、同じ科目を再度受講するケースが必ず発生します。

このデータから一覧表を作りたいとき、佐藤さんの「微分積分学」には最終評価である「B」を、高橋さんの「線形代数学」には、まだ合格していない現状を示す「D」を、それぞれ正しく表示させなければなりません。

これは単に重複を削除すれば良い、という話ではありません。単純に「D」の行を削除すると、まだ合格していない高橋さんの履修情報そのものが消えてしまいます。各学生・各科目の組み合わせにおける「最終的で最も価値のある評価」を一つだけ選び出すという、高度な判断が求められます。これが「第二の壁」です。

第三の壁：実務要件としての「科目選択」

これら二つの壁を乗り越える方法の目途がついたとしても、実務の現場からはさらなる要望が聞こえてきます。「全科目ではなく、特定の科目群だけを抜き出した表が欲しい」。例えば、「必修科目だけの成績一覧」や「専門科目だけの達成度チェック」といった使い方です。

この「動的にデータを絞り込む」という要件も、都度元データを手作業でフィルタリングしていては、せっかくの自動化が台無しです。これもまた、柔軟なレポート作成を阻む三つ目の壁と言えます。

この記事では、これら三つの課題をすべて解決することを目標に、具体的なアプローチを紹介していきます。

解決策1：Excel関数で三つの課題を解決する

ピボットテーブルでは解決できないこれらの壁も、Excelの標準機能と、最新バージョン（Microsoft 365、Excel2021以降）に搭載された関数を組み合わせれば、VBAなしで見事に解決できます。アプローチは「①元データから『最終評価』だけにクリーニングされたリストを作り、②それを元に一覧表を組み立てる」という2段階の考え方が鍵となります。

実現するまでの4ステップ

ステップ1：データの並べ替え（評価順に整列）

まず、元データから「再履修」問題を解決するための下準備として、データを並べ替えます。目的は、同じ学生・同じ科目のレコードが隣り合い、その中で最も良い評価のレコードが一番上に来るようにすることです。

1. 元データの範囲（見出しを含む）を選択します。
2. 「データ」タブの「並べ替え」をクリックします。
3. 「並べ替え」ダイアログボックスで、以下の通り3つのレベルを設定します。
   • 最優先されるキー： 学生番号 、 順序：昇順（小さい順）
   • 次に優先されるキー： 科目名、 順序：昇順
   • 次に優先されるキー： 評価、 順序：昇順
4. 「OK」をクリックします。

これで、「佐藤太郎」の「微分積分学」のレコードは、評価「B」の行が「D」の行より上に来るように並び替えられます。

ステップ2：重複レコードの削除（第二の壁を解決）

次に、並べ替えたデータから不要なレコードを削除し、『クリーニング済データ』を完成させます。

1. 並べ替えた状態のまま、データ範囲が選択されていることを確認します。
2. 「データ」タブ > 「データツール」グループの「重複の削除」をクリックします。
3. 「重複の削除」ダイアログボックスで、一度「すべての選択を解除」をクリックしてから、重複を判定するキーとして「学生番号」と「科目名」の2つの列にだけチェックを入れます。
4. 「OK」をクリックします。

これにより、Excelは「学生番号」と「科目名」の組み合わせが同じレコード群の中から、一番上の行（ステップ1で並べ替えたことにより、最も評価が良い行）だけを残し、それ以外をすべて削除します。これで第二の壁「再履修」問題が解決され、最終評価のみ記録された『クリーニング済データ』が完成です。

ステップ3：一覧表の骨格を作成（第三の壁を解決）

新しいシートを用意し、最終的なレポートの骨格を作成します。

• 縦軸（学生リスト）の作成: A列に、評価を出したい学生のリストを用意します。ステップ2で作成した『クリーニング済データ』から、=UNIQUE(クリーニング済データの学生番号と氏名の列範囲) のような関数を使うと、学生一覧を簡単に抽出できます。
• 横軸（科目リスト）の作成: 1行目に、表示したい科目名を横方向に入力します。これは手入力しても良いですし、別の科目リストシートにあらかじめ用意した一覧を参照しても構いません。これが第三の壁「科目選択」を解決するベースとなります。

【ヒント】 もしデータに含まれる全ての科目を表示したい場合は、=TRANSPOSE(UNIQUE(クリーニング済データの科目名列の範囲)) という数式で科目名を自動で抽出することもできます。

ステップ4：評価の抽出と完成（第一の壁を解決）

最後に、表の中身を埋めていきます。各セルに、「特定の学生」の「特定の科目」の評価を『クリーニング済データ』から探し出す数式を入力します。

XLOOKUP関数を使った配列数式が強力です。以下の数式を表の左上のセル（例：C2セル）に一度入力するだけで、スピル機能により表全体が自動で完成します。

=XLOOKUP(学生リストの範囲 & 横軸の科目リスト範囲, クリーニング済データの学生列 & クリーニング済データの科目列, クリーニング済データの評価列, "未履修")

これで、元データや科目リストが変更されても自動で更新される、インタラクティブな成績一覧表が完成します。

この方法の評価

• メリット： VBAが不要で、比較的簡単に作成できる。元データとの連動性が高い。
• デメリット： UNIQUEやXLOOKUPといった新しい関数が使えない古いバージョンのExcelでは実現が煩雑になる。ステップ2の数式がやや複雑になる。

XLOOKUP関数については以下の記事も参考にしてください。

関連記事

【Excel業務が変わる！】Office 2016/2019 サポート終了間近！Microsoft 365へ移行してXLOOKUP関数を使いこなそう

「え、まだOffice 2016/2019を使ってますけど、なにか？」――そんな声が聞こえてきそうです。実は、これらのバージョンのOfficeは、まもなくサポートが終了する予定です。セキュリティリスクや機能面を考えると、この機会に最新の […]

解決策2：VBAで全ての課題をスマートに自動化する

Excelのバージョンを問わず、誰でも使える安定したツールを目指すなら、VBAが最適解です。関数と考え方は似ていますが、一連のステップをすべて裏側で自動実行してくれるのがVBAの強みです。

一覧表作成ツールの動作

VBAの核心に触れる前に、このツールが完成後にどのように動作するのか紹介します。

ステップ1：Excelファイルの準備

1. 成績データシート: 成績の元データが入力されたシートを用意します。
2. 科目リストシート: 科目リストという名前の新しいシートを作成し、A列に処理したい科目名の一覧を入力します（タイトル行は不要）。

ステップ2：マクロの実行

1. 成績データが入力されたシートをアクティブ（表示した状態）にします。
2. Alt + F8キーを押し、「マクロ」ダイアログボックスを開きます。
3. CreateGradeReport_Final_Completeを選択し、「実行」ボタンをクリックします。より便利にするには、シート上にボタンを配置し、このマクロを登録することをお勧めします。

ステップ3：ツールとの対話

1. マクロを実行すると、メッセージボックスが表示され、続いて元データの「学生番号」「氏名」「科目名」「評価」がどの列にあるかを選択するよう、4回にわたってダイアログボックスが表示されます。
2. 指示に従い、該当する列のヘッダー（例: A）をクリックして列全体を選択し、「OK」を押してください。

ステップ4：完成

すべての選択が終わると、数秒で新しいシートが作成され、そこに完成した成績一覧表が出力されます。

VBAで一覧表作成を高速に処理するための3つのポイント

VBAでこの処理を自動化するにあたり、処理速度と正確性を両立させるための重要なポイントが3つあります。

ポイント1：元データをメモリ上で高速処理する「配列」

数千行に及ぶデータを扱う場合、Excelのセルを一つひとつ読み書きするコードを書くと非常に時間がかかります。そこで、最初に元データ全体を「配列」というVBA内の変数に一括で読み込み、処理はすべてメモリ上で完結させます。

' --- 元データ範囲を配列に一括で格納 ---
Dim sourceData As Variant
sourceData = sourceSheet.UsedRange.Value

ポイント2：最終評価を高速で作成する「Dictionaryオブジェクト」

次に、メモリに読み込んだデータから、「どの科目を処理し、どの評価を採用するか」という、このツールで最も重要な判断を行います。ここでの最適な道具がDictionary（辞書）オブジェクトです。

ステップA：処理対象となる「科目リスト」を辞書に格納する

まず、ユーザーが科目リストシートに入力した処理対象の科目一覧を、一つ目の辞書（selectedSubjectsDic）に読み込みます。

' --- 「科目リスト」シートの内容をDictionaryに読み込む ---
Dim selectedSubjectsDic As Object
Set selectedSubjectsDic = CreateObject("Scripting.Dictionary")
lastRow = listSheet.Cells(listSheet.Rows.Count, "A").End(xlUp).Row
For i = 1 To lastRow
    subject = listSheet.Cells(i, "A").Value
    ' 重複を無視しつつ、科目名をキーとして辞書に追加
    If subject <> "" And Not selectedSubjectsDic.Exists(subject) Then
        selectedSubjectsDic.Add subject, Empty
    End If
Next i

辞書に科目名を入れておくことで、後の工程で「この科目は処理対象か？」という確認を瞬時に行うことができます。これが第三の壁「科目選択」を解決する鍵です。

ステップB：メインループで評価を厳選し、結果を別の辞書に格納する

次に、元データ（配列sourceData）を一行ずつループ処理し、二つ目の辞書（resultDic）に最終評価を格納していきます。

' --- メインの集計ループ ---
For i = 2 To UBound(sourceData, 1) ' 配列の2行目から最後まで
    subject = sourceData(i, subjectCol.Column)
    
    ' ★ステップAで作った辞書で、処理対象か瞬時に判定
    If selectedSubjectsDic.Exists(subject) Then
        ' (処理対象だった場合のみ、以下の処理に進む)
        studentId = sourceData(i, studentIdCol.Column)
        grade = sourceData(i, gradeCol.Column)
        key = studentId & "|" & subject ' 「学生|科目」というユニークなキーを作成
        
        ' ★第二の壁「再履修」を解決するロジック
        If Not resultDic.Exists(key) Then
            resultDic.Add key, grade ' 初めての科目なら無条件で追加
        Else
            ' 2回目以降なら、より良い評価の場合のみ上書き
            If grade < resultDic(key) Or resultDic(key) = "D" Then
                resultDic(key) = grade
            End If
        End If
    End If
Next i

このループが終わる頃には、resultDicの中に、選択された科目の、最終評価だけの完璧なデータセットが完成しています。

ポイント3：座標を特定し、シートへ一気に書き込む

最後に、完成したresultDicのデータを元に、VBA内で出力用の配列を組み立てます。そして、その配列を一回の操作でシートに書き出すことで、ピボットテーブルでは不可能だった**第一の壁「評価の表示」**を高速に実現します。

' --- 完成した配列をシートに一括で書き出し ---
newWs.Range("A1").Resize(UBound(outputData, 1), UBound(outputData, 2)).Value = outputData

あなたに最適な方法はどれ？

これまでのアプローチを比較し、あなたにとって最適な方法を選択しましょう。

結論として、今回の3つの要件をすべて満たすには、最新のExcel関数またはVBAが現実的な選択肢となります。特に、誰でも使えるツールとして配布・運用したい場合には、VBAに圧倒的な優位性があります。

まとめと完成版VBAコードのご案内

いかがでしたでしょうか。ここでご紹介したのは、実務要件をVBAで解決するための核となる考え方です。

しかし、実務で安心して使う「ツール」として完成させるには、もう少し工夫が必要です。実際の完成版コードには、

• 元データの列構成が変わっても対応できる、柔軟な列指定機能
• 「D評価」や「不可」など、特定の評価を自動で色付けする条件付き書式設定
• 科目リストシートが見つからない等のエラーを事前に防ぐ、堅牢なチェック機能

など、日々の業務で本当に役立つための様々な「気配り」が網羅されています。

この「かんたん成績一覧表 作成マクロ」の全コード、および各コードブロックのより詳細な解説を盛り込んだ完全版の記事は、以下のnoteで公開しています。

コピー＆ペーストしてすぐに使える完成品が欲しい方、そしてVBAの思考法をさらに一段階引き上げたい方は、ぜひご覧ください。

▼Excel VBAで作る成績一覧表！《科目選択・再履修対応版》全コードと徹底解説

https://note.com/horiken805/n/n34d1e9735d85

この記事が、あなたのデータ集計業務のヒントとなり、VBAという新たなスキルへの扉を開く一助となれば幸いです。