広告
進路指導の先生がエクセルで生徒の成績管理をする際に、
- 各模試の点数データを自動転記したい
- 生徒別に1枚のシートにまとめたい
- 点数を自動判定したい
この記事ではエクセルでの成績管理をVLOOKUPを用いて効率化する方法を解説します。
VLOOKUPのメリットや注意点もあわせて参考にしてください。
本文中の解説図はクリックで拡大表示します。別途ページではPDFファイルもダウンロード可能です。
≫ PDFファイル参照ページはこちら
お急ぎの方は、下記から該当箇所へ移動してください。
この記事を読めば、成績管理の単純入力作業を大幅に削減できます。模試の数が増えても大丈夫。
生徒別の成績分析がしやすくなり、よりきめ細かな指導をできるようになります。時短にもつながり、残業を減らして早く帰れることでしょう。
VLOOKUPを基礎から応用まで完璧にマスターしたい方は、次の記事も参照ください。
≫ 超図解！VLOOKUP｜基礎から応用まで完璧マスター＆便利技20選
マイクロソフト公式サイトの解説を参照したい方はこちら
≫ Microsoft｜Officeのサポート～VLOOKUP関数
1. VLOOKUPの基礎知識と処理のイメージ
VLOOKUPとは、指定した範囲から特定の値を探し出し、紐づくデータを返す関数です。
処理の流れをイメージすると下図のようになります。
【VLOOKUPの処理のイメージ】
❶ 検索値を所定のセルに入力
❷ 検索値と一致するものを、検索範囲の最左列を上から順に探す
❸ 検索値と一致する値を発見！（近似値の場合もあり）
❹ その行の左から○列目を返す（○は式で指定）
❺ 検索結果として表示
VLOOKUPの数式は次のとおりです。
=VLOOKUP( 検索値 , 検索範囲 , 列番号 , 検索方法 )
| 項目 | 内容 | 入力例 |
| 検索値 | データを探し出す手掛かりとなる値 | A1（セルを指定） |
| 検索範囲 | 探したいデータが入っている表 | A:D （列全体で指定） A1:D4（長方形で指定） |
| 列番号 | 左から何列目を返すか | 2（数値で指定） |
| 検索方法 | 一致した場合のみ返すか、近似値でも返すか | FALSE（完全一致） TRUE（近似値） |
各項目の基礎知識をさらに詳しく知りたい方はこちら
≫ VLOOKUPの使い方｜3.VLOOKUPはどうやって使うの？｜各項目の解説
マイクロソフト公式サイトの解説を参照したい方はこちら
≫ Microsoft｜Officeのサポート～VLOOKUP関数
2. エクセル形式の点数データを自動転記するVLOOKUPの使い方
この章では、エクセル形式の点数データを、VLOOKUPを使って自動転記する方法を解説します。受験番号が特定できれば一発で転記できます。
2-1. 受験番号を手掛かりに点数が自動転記される仕組み
受験番号を手掛かりに点数が自動転記される仕組みは、次のとおりです。
【VLOOKUPを用いた自動転記の流れ】
❶ 受験番号を所定の欄に入力する
❷ 各模試の点数データから一致する受験番号を探す
❸ 一致する受験番号を見つけたら対応する点数を特定する
❹ 特定した点数を所定のセルに自動転記する
結果一覧の一番上のセル「D3」の数式は次のとおりです。
【 セル「D3」の数式 】
=VLOOKUP($C3,X塾模試!$A:$F,2,FALSE)
| $C3 | 受験番号を入力するセル |
| X塾模試!$A:$F | 模試結果の範囲（※1） |
| 2 | 2列目のデータを返せ |
| FALSE | コードが一致する場合のみ返せ（※2） |
(※1) A～F列の最下行まで検索範囲に含まれます。「$」をつけることで数式をコピーしても変化しません。
(※2) 「TRUE」にしてしまうと一致するコードがなくても、近い値を見つけて返してしまいます。他の生徒の点数の転記間違いを防ぐためにも、「FALSE」を選びましょう。
≫ 「FALSE」「TRUE」の選び方について詳しく知りたい方はこちら
2-2. 受験番号を手掛かりに点数を自動転記する数式の作り方
この章では、受験番号を手掛かりに点数データを自動転記する数式の作り方を解説します。
完成形の数式は下記のとおりです。
集計先の一番左上のセル「D2」の数式は下記のとおりです。
集計先の一番左上のセル「D2」の数式
=VLOOKUP($C3,X塾模試!$A:$F,2,FALSE)
(※)転記元の点数データは「X塾模試」というシートにあります。
次に、ステップ順に解説します。
(1) 関連するシートを一つのファイルに集める（可能な限り）
関連するシートはできるだけ一つのファイルに集めましょう。複数のファイルにまたがると、つながりが切れたりして転記ミスが起きやすいからです。
どうしても分かれる場合でも、同じフォルダ内に置くなどして管理しやすいようにしましょう。
(2) 数式を作成するセルを選択し関数作成用ツールを開く
数式を作成するセルを選択し、メニュー欄にある「fx」をクリックします。
(3) VLOOKUP関数を検索して呼び出す
「関数の挿入」ダイアログが開くので、「関数の検索」の欄に「VLOOKUP」と入力し、検索して呼び出します。
(4) 受験番号を入力する場所を指定する
「関数の引数」ダイアログが開くので、「検索値」の欄をクリックします。
転記先の受験番号のセルを選択します。
「検索値」の欄に「C3」と表示されたら、Cの手前に「$」を付けてください。
「$C3」となればOKです。
Cに「$」を付けたのは、数式をコピーしていっても常にC列（受験番号の列）を指定して欲しいからです。
一方、数字には「$」を付けません。数式を下にコピーした際に、連動して変化して欲しいからです。
(5) 点数データの範囲を指定する
「関数の引数」ダイアログの「範囲」の欄をクリックします。
点数データの列の番地A～Fをドラッグで範囲指定します。
「範囲」の欄に「X塾模試!A:F」と表示されたら、AとFの手前に「$」を付けてください。
「X塾模試!$A:$F」となればOKです。
(6) 「列番号」と「検索方法」を直接入力する
「列番号」と「検索方法」を下記のとおりに、直接入力します。
| 列番号 | 2 |
| 検索方法 | FALSE |
(7) 数式を右へコピーして列番号を整える
「D3」の数式を右へコピーします。
列番号の「2」は変化しないので、科目に応じた列番号に手で書き換えます。
| 英語 | 数学 | 国語 | 理科 | 社会 |
| 2 | 3 | 4 | 5 | 6 |
列番号の書き換えが面倒な方は、COLUMN関数で指定することもできます。
詳しくは下記を参照ください。
≫ VLOOKUPの使い方｜6-20.COLUMNで列番号指定
変化する項目/しない項目
VLOOKUPでは数式を移動/コピーした際に、変化する項目としない項目があります。項目ごとに確認しながら進めましょう。
| 項目 | 移動/コピーしたときの変化 |
| 検索値 | 変化する（ただし「$」がある項目は変化しない） |
| 検索範囲 | 変化する（ただし「$」がある項目は変化しない） |
| 列番号 | 変化しない |
| 検索方法 | 変化しない |
(8) 数式を最下行までコピーする
英語～社会までのセルをまとめて最下行までコピーします。
このとき行番号の指定はコピーにあわせて変化するので、手で書き換える必要はありません。
以上ができたら、他の模試の点数データの数式も同様に作成します。
「$」は数式を移動/コピーしたときにできるだけ手を加えなくて済むようにするためのものです。「$」の有無は計算結果には直接影響しません。
「$」をうまく使えば変更箇所が少なくなり、ミスも減り、省力化できます。膨大な数をコピーする際に、「$」は必須アイテムです。
3. テスト結果一覧から生徒別成績表を自動作成する方法
この章では、エクセルで集計されたテスト結果一覧から、VLOOKUPをつかって生徒別成績表を自動作成する方法を解説します。出席番号を手掛かりに、一人ずつのシートを作成できます。
3-1. 出席番号を手掛かりに生徒別成績表を自動作成する仕組み
出席番号を手掛かりに生徒別成績表を作成する仕組みは、次のとおりです。
【VLOOKUPを用いた自動作成の流れ】
❶ 出席番号を所定の欄に入力する
❷ 結果一覧から一致する出席番号を探す
❸ 一致する出席番号を見つけたら対応する点数を特定する
❹ 特定した点数を所定のセルに自動転記する
結果一覧の生徒名を表示させるセル「B2」と、点数の一番左上のセル「E2」の数式は次のとおりです。
【 セル「B2」の数式 】
=VLOOKUP($A$2,結果一覧!$A:$T,2,FALSE)
【 セル「E2」の数式 】
=VLOOKUP($A$2,結果一覧!$A:$T,4,FALSE)
| $A$2 | 出席番号を入力するセル |
| 結果一覧!$A:$T | 結果一覧の範囲（※1） |
| 2 | 2列目のデータを返せ |
| FALSE | コードが一致する場合のみ返せ（※2） |
(※1) A～T列の最下行まで検索範囲に含まれます。「$」をつけることで数式をコピーしても変化しません。
(※2) 「TRUE」にしてしまうと一致するコードがなくても、近い値を見つけて返してしまいます。他の生徒の点数の転記間違いを防ぐためにも、「FALSE」を選びましょう。
≫ 「FALSE」「TRUE」の選び方について詳しく知りたい方はこちら
3-2. 出席番号を手掛かりに生徒別成績表を自動作成する数式の作り方
この章では、結果一覧から生徒別成績表を自動作成する数式の作り方を解説します。
完成形の数式は下記のとおりです。
【 セル「B2」の数式 】
=VLOOKUP($A$2,結果一覧!$A:$T,2,FALSE)
【 セル「E2」の数式 】
=VLOOKUP($A$2,結果一覧!$A:$T,4,FALSE)
(1) 関連するシートを一つのファイルに集める（可能な限り）
関連するシートはできるだけ一つのファイルに集めましょう。複数のファイルにまたがると、つながりが切れたりする転記ミスが起きやすいからです。
どうしても分かれる場合でも、同じフォルダ内に置くなどして管理しやすいようにしましょう。
(2) 数式を作成するセルを選択し関数作成用ツールを開く
数式を作成するセルを選択し、メニュー欄にある「fx」をクリックします。
(3) VLOOKUP関数を検索して呼び出す
「関数の挿入」ダイアログが開くので、「関数の検索」の欄に「VLOOKUP」と入力し、検索して呼び出します。
(4) 出席番号を入力する場所を指定する
「関数の引数」ダイアログが開くので、「検索値」の欄をクリックします。
転記先の出席番号のセルを選択します。
「検索値」の欄に「A2」と表示されたら、「$」を付けて「$A$2」としてください。
このシートのすべてのVLOOKUPはこの出席番号を手掛かりに検索するからです。
行と列の両方に「$」をつけることで、シート上のどこに数式をコピーしても常に検索値を出席番号にすることができます。
(5) 点数データの範囲を指定する
「関数の引数」ダイアログの「範囲」の欄をクリックします。
結果一覧の列の番地A～Tをドラッグで範囲指定します。
「範囲」の欄に「結果一覧!A:T」と表示されたら、AとTの手前に「$」を付けてください。
「結果一覧!$A:$T」となればOKです。
(6) 「列番号」と「検索方法」を直接入力する
「列番号」と「検索方法」を下記のとおりに、直接入力します。
| 列番号 | 2 |
| 検索方法 | FALSE |
(7) 数式を点数のセル「E2～I4」にコピーする
セル「B2」に生徒名が正しく表示されていることを確認してください。OKなら「B2」の数式をコピーして、点数のセル「E2～I4」すべてに貼り付けます。
(※)貼り付け直後は、すべてのセルに生徒名が表示されますが、現時点ではOKです。
生徒名と点数の数式は、できるだけ共通にしましょう。具体的には、列番号以外は同じにするのが好ましいです。
生徒名だけ違う処理にすると、生徒名と点数が不一致になる事故を起こしかねないからです。
(8) 列番号を手で書き換える
列番号がすべて同じになっているので、科目と模試にあわせて手で書き換えます。
具体的には、下記になります。
| 【列番号】 | 英語 | 数学 | 国語 | 理科 | 社会 |
|---|---|---|---|---|---|
| X塾模試 | 4 | 5 | 6 | 7 | 8 |
| Y塾模試 | 10 | 11 | 12 | 13 | 14 |
| Z塾模試 | 16 | 17 | 18 | 19 | 20 |
列番号の書き換えが面倒な方は、COLUMN関数で指定することもできます。
詳しくは下記を参照ください。
≫ VLOOKUPの使い方｜6-20.COLUMNで列番号指定
COLUMN関数は横方向のコピーにのみ有効です。縦方向へのコピーにはrow関数が有効ですが、今回のケースでは複雑になります。列番号は手入力をおすすめします。
4. 判定表をもとに点数を自動判定する方法
この章では、判定表にそって点数を自動的に判定するVLOOKUPの式を解説します。近似値検索を使いますので、これまでの完全一致検索との違いを理解することが重要です。
4-1. 判定表をもとに点数を自動判定する仕組み
【VLOOKUPを用いた自動転記の流れ】
❶ 判定したい点数が表示される
❷ 判定表から一致する点数を探す
❸ 一致する点数があった場合は、対応する判定結果を特定する
❹ 一致する点数が無かった場合は、検索値を超えない最も近い判定結果を特定する
❺ 特定した判定結果を所定のセルに自動転記する
判定結果の一番上のセル「L2」の数式は次のとおりです。
【 セル「L2」の数式 】
=VLOOKUP(K2,判定表!$A:$B,2,TRUE)
| K2 | 判定したい点数が表示されるセル |
| 判定表!$A:$B | 判定表の範囲（※1） |
| 2 | 2列目のデータを返せ |
| TRUE | 検索値を超えない最も近い値を返せ（※2） |
(※1) A～B列の最下行まで検索範囲に含まれます。「$」をつけることで数式をコピーしても変化しません。
(※2) 「FALSE」にしてしまうと、一致する点数が見つからない場合エラーを返してしまいます。一致する点数が無い場合に近似値を見つけて返すよう、「TRUE」を選びましょう。
検索方法：TRUEとFALSEの違い
「TRUE」と「FALSE」の結果の違いを整理しました。使い分ける際の参考にしてください。
≫ 「FALSE」「TRUE」の選び方について詳しく知りたい方はこちら
4-2. 判定表をもとに点数を自動判定する数式の作り方
この章では、判定表をもとに点数を自動判定する数式の作り方を解説します。
完成形の数式は下記のとおりです。
判定結果の一番上のセル「L2」の数式
=VLOOKUP(K2,判定表!$A:$B,2,TRUE)
(※)判定表は「判定表」というシートにあります。
次に、ステップ順に解説します。
(1) 関連するシートを一つのファイルに集める（可能な限り）
関連するシートはできるだけ一つのファイルに集めましょう。複数のファイルにまたがると、つながりが切れたりする転記ミスが起きやすいからです。
どうしても分かれる場合でも、同じフォルダ内に置くなどして管理しやすいようにしましょう。
(2) 数式を作成するセルを選択し関数作成用ツールを開く
数式を作成するセルを選択し、メニュー欄にある「fx」をクリックします。
(3) VLOOKUP関数を検索して呼び出す
「関数の挿入」ダイアログが開くので、「関数の検索」の欄に「VLOOKUP」と入力し、検索して呼び出します。
(4) 受験番号を入力する場所を指定する
「関数の引数」ダイアログが開くので、「検索値」の欄をクリックします。
判定したい点数のセルを選択します。
「検索値」の欄に「K2」と表示されたらOKです。
(5) 判定表の範囲を指定する
「関数の引数」ダイアログの「範囲」の欄をクリックします。
判定表の列の番地A～Bをドラッグで範囲指定します。
「範囲」の欄に「判定表!A:B」と表示されたら、AとFの手前に「$」を付けてください。
「判定表!$A:$B」となればOKです。
「$」を付けたのは、数式をコピーしていっても常に同じ列を指定して欲しいからです。
(6) 「列番号」と「検索方法」を直接入力する
「列番号」と「検索方法」を下記のとおりに、直接入力します。
| 列番号 | 2 |
| 検索方法 | TURE |
点数判定におけるIFとTRUE検索の数式の比較
もしIFを使って同様の判定式を作ると上記のようになります。点数の刻みを増やすと、その分数式も長くなっていきます。
一方、TRUE検索であれば、点数の刻みを増やしたい場合、判定表の行数を増やすだけで対応できます。数式は変更不要です。
（注意）点数の刻みを増やした場合、判定表の点数の列は昇順に並べる必要があります。
VLOOKUPを基礎から応用まで完璧にマスターしたい方は、次の記事もあわせて参照ください。
≫ 超図解！VLOOKUPの使い方｜基礎から応用まで完璧マスター＆スゴ技20選
5. エクセルの成績管理の作成でVLOOKUPを使うメリット
この章では、エクセルの成績管理の作成でVLOOKUPを使うメリットを解説します。
- 数字の転記をミスなく効率化できる
- 元データの変更がすぐに反映される
- 独自のテーブルで成績判定できる
5-1. 数字の転記をミスなく効率化できる
数式が正しければ、生徒数や点数データの数に関係なく、自動で転記できます。
行数は100万行あっても検索できるので、全国模試などがあっても問題なく抽出できます。
列数も1万超でも検索可能です。たくさんの模試を受けていても一元管理できます。
VLOOKUPの検索可能数については下記の記事で詳しく解説しています。
≫ エクセルの社員名簿作成を自動入力で効率化｜検索＆転記もVLOOKUPで一発処理～6-1. 社員名簿が膨大な人数でも簡単に検索可能
5-2. 元データの変更がすぐに反映される
何らかの事情で元データを変更しても、集計結果に即時に反映されます。平均点や判定結果も同様に更新されます。
手計算であれば電卓を叩き直す必要があるところ、エクセルではそういったことは不要です。
5-3. 独自のテーブルで成績判定できる
判定表を変更すれば、判定結果も連動して変わります。独自の刻み幅で判定結果を出したい場合、独自の判定表を作成して組み込めばOKです。
点数だけでなく偏差値などの数値を判定することもできます。
6. エクセルの成績管理の作成でVLOOKUPを使う際の注意点
エクセルの成績管理でVLOOKUPを使う際、幾つか注意点があります。
- 点数データに空白がある場合対策が必要
- 点数データのフォーマットに一定のルールが必要
- データが壊れるとすべての結果に影響する
6-1. 点数データに空白がある場合対策が必要
点数データに空白がある場合、注意が必要です。VLOOKUPは検索対象のセルが空欄の場合、検索結果に０が表示されるからです。
受験して0点だった場合との見分けがつきません。そのまま集計すると平均点を下げてしまいます。
対策として、IFと組み合わせる方法をご紹介します。
【 本来の数式 】
=VLOOKUP($C3,W塾模試!$A:$F,2,FALSE)
【 空白対策をした数式 】
=IF(VLOOKUP($C3,W塾模試!$A:$F,2,FALSE)=””,”－”, VLOOKUP($C3,W塾模試!$A:$F,2,FALSE))
(※)点数データが空欄の場合、「－」が表示されます。
6-2. 点数データや判定表のフォーマットに一定のルールが必要
点数データや判定表などの検索対象となる表のフォーマットには、一定のルールが求められます。
主なルールは次のとおりです。
- 検索値は一番左の列に縦に並べること
- 検索値は昇順に並んでいること
- 検索値に重複が無いこと
- 一行ごとに一組のデータであること
- セルに結合がないこと
(※)検索値とは受験番号や出席番号など、検索の際に手掛かりになる値のこと
VLOOKUPの検索範囲の表の作り方について、基本的なルールを知りたい方はこちら
≫ VLOOKUPの使い方｜4-1.検索範囲となる表の作成上の基本ルール
6-3. 元データが壊れるとすべての結果に影響する
元データが壊れると、転記先のすべての結果に影響します。ファイルの破損だけでなく、うっかり削除したりしてしまうこともあるので注意が必要です。
関連するデータはできるだけ一つのファイルにまとめましょう。バックアップをとるときも一つのファイルをコピーしておけばいいからです。もし複数のファイルにまたがっていると、片方をコピーしたときに参照先が移動してしまうおそれがあるので注意してください。
まとめ｜VLOOKUPは転記・集計・成績判定で威力を発揮！
エクセルでの成績管理について、VLOOKUPを使えば劇的に効率化できることを解説しました。
- 各模試の点数データを自動転記できる
- 生徒別成績を1枚のシートにまとめられる
- 点数を自動判定できる
あわせてVLOOKUPのメリットや注意点も解説しました。
再確認したい方は、下記から該当箇所を辿ってください。
本文中の解説図はクリックで拡大表示します。別途ページではPDFファイルもダウンロード可能です。
≫ PDFファイル参照ページはこちら
この記事を読めば、成績管理の単純入力作業を大幅に削減できます。模試の数が増えても大丈夫です。
生徒別の成績分析がしやすくなり、よりきめ細かな指導をできるようになります。時短にもつながり、残業を減らして早く帰れることでしょう。
VLOOKUPを基礎から応用まで完璧にマスターしたい方は、次の記事も参照ください。
≫ 超図解！VLOOKUP｜基礎から応用まで完璧マスター＆便利技20選
エクセルでお困りのことがありましたら、『エクセルの窓口』で検索してください。
ブックマークやお気に入りに登録していただけたら幸いです。