エクセルで成績管理|点数を自動で転記・集計・判定まで~VLOOKUP

エクセルで成績管理|点数を自動で転記・集計・判定まで~VLOOKUP

エクセルで成績管理をするにあたって

  • 各模試の点数データを自動転記したい
  • 生徒別に1枚のシートにまとめたい
  • 点数を自動判定したい

この記事ではエクセルでの成績管理をVLOOKUPを用いて効率化する方法を解説します。
VLOOKUPのメリットや注意点もあわせて参考にしてください。

本文中の解説図はクリックで拡大表示します。別途ページではPDFファイルも参照可能です。
≫ PDFファイル参照ページはこちら

お急ぎの方は、下記から該当箇所へ移動してください。

【各模試の点数データを結果一覧に自動転記する】

解説内容詳細へ
仕組みを理解したい2-1.
数式を作りたい2-2.

【結果一覧から生徒別成績表へ自動集計する】

解説内容詳細へ
仕組みを理解したい3-1.
数式を作りたい3-2.

【独自の判定表で点数を自動判定する】

解説内容詳細へ
仕組みを理解したい4-1.
数式を作りたい4-2.

この記事を読めば、成績管理の単純入力作業を大幅に削減できます。模試の数が増えても大丈夫です。

生徒別の成績分析がしやすくなり、よりきめ細かな指導をできるようになります。時短にもつながり、残業を減らして早く帰れることでしょう。

VLOOKUPを基礎から応用まで完璧にマスターしたい方は、次の記事も参照ください。
≫ 超図解!VLOOKUP|基礎から応用まで完璧マスター&便利技20選

マイクロソフト公式サイトの解説を参照したい方はこちら
≫ Microsoft|Officeのサポート~VLOOKUP関数

1. VLOOKUPの基礎知識と処理のイメージ

VLOOKUPとは、指定した範囲から特定の値を探し出し、紐づくデータを返す関数です。

処理の流れをイメージすると下図のようになります。

エクセルVLOOKUPの使い方【成績管理編】VLOOKUPの処理のイメージ
VLOOKUPの処理の流れのイメージ(画像はクリックで拡大)

≫ 解説図のPDFファイル

【VLOOKUPの処理のイメージ】
検索値を所定のセルに入力
検索値と一致するものを、検索範囲の最左列を上から順に探す
検索値と一致する値を発見!(近似値の場合もあり)
その行の左から○列目を返す(○は式で指定)
検索結果として表示

VLOOKUPの数式は次のとおりです。

=VLOOKUP( 検索値 , 検索範囲 , 列番号 , 検索方法 )

項目内容入力例
検索値データを探し出す手掛かりとなる値A1(セルを指定)
検索範囲探したいデータが入っている表A:D (列全体で指定)
A1:D4(長方形で指定)
列番号左から何列目を返すか2(数値で指定)
検索方法一致した場合のみ返すか、近似値でも返すかFALSE(完全一致)
TRUE(近似値)

各項目の基礎知識をさらに詳しく知りたい方はこちら
≫ VLOOKUPの使い方|3.VLOOKUPはどうやって使うの?|各項目の解説

マイクロソフト公式サイトの解説を参照したい方はこちら
≫ Microsoft|Officeのサポート~VLOOKUP関数

2. エクセル形式の点数データをラクに転記するVLOOKUPの使い方

この章では、エクセル形式の点数データを、VLOOKUPを使って自動転記する方法を解説します。受験番号が特定できれば一発で転記できます。

2-1. 受験番号を手掛かりに点数が自動転記される仕組み

受験番号を手掛かりに点数が自動転記される仕組みは、次のとおりです。

エクセルVLOOKUPの使い方【成績管理編】点数データから結果一覧にが自動転記される仕組み
点数データから結果一覧に自動転記される仕組み(画像はクリックで拡大)

≫ 解説図のPDFファイル

【VLOOKUPを用いた自動転記の流れ】
受験番号を所定の欄に入力する
各模試の点数データから一致する受験番号を探す
一致する受験番号を見つけたら対応する点数を返す
所定の欄に模試結果が自動転記される

結果一覧の一番上のセル「D3」の数式は次のとおりです。

【 セル「D3」の数式 】
=VLOOKUP($C3,X塾模試!$A:$F,2,FALSE)

$C3受験番号を入力するセル
X塾模試!$A:$F模試結果の範囲(※1)
22列目のデータを返せ
FALSEコードが一致する場合のみ返せ(※2)

(※1) A~F列の最下行まで検索範囲に含まれます。「$」をつけることで数式をコピーしても変化しません。

(※2) 「TRUE」にしてしまうと一致するコードがなくても、近い値を見つけて返してしまいます。他の生徒の点数の転記間違いを防ぐためにも、「FALSE」を選びましょう。
≫ 「FALSE」「TRUE」の選び方について詳しく知りたい方はこちら

2-2. 受験番号を手掛かりに点数を自動転記する数式の作り方

エクセルVLOOKUPの使い方【成績管理編】点数データから結果一覧にが自動転記される数式の作り方
点数データを自動転記する数式の作り方(画像はクリックで拡大)

≫ 解説図のPDFファイル

この章では、受験番号を手掛かりに点数データを自動転記する数式の作り方を解説します。
完成形の数式は下記のとおりです。

数式の作り方

集計先の一番左上のセル「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」は変化しないので、科目に応じた列番号に手で書き換えます。

英語数学国語理科社会
23456

列番号の書き換えが面倒な方は、COLUMN関数で指定することもできます。
詳しくは下記を参照ください。
≫ VLOOKUPの使い方|6-20.COLUMNで列番号指定

変化する項目/しない項目

VLOOKUPでは数式を移動/コピーした際に、変化する項目としない項目があります。項目ごとに確認しながら進めましょう。

項目移動/コピーしたときの変化
検索値変化する(ただし「$」がある項目は変化しない)
検索範囲変化する(ただし「$」がある項目は変化しない)
列番号変化しない
検索方法変化しない

(8) 数式を最下行までコピーする

数式の作り方

英語~社会までのセルをまとめて最下行までコピーします。
このとき行番号の指定はコピーにあわせて変化するので、手で書き換える必要はありません。

以上ができたら、他の模試の点数データの数式も同様に作成します。

「$」は数式を移動/コピーしたときにできるだけ手を加えなくて済むようにするためのものです。「$」の有無は計算結果には直接影響しません。
「$」をうまく使えば変更箇所が少なくなり、ミスも減り、省力化できます。膨大な数をコピーする際に、「$」は必須アイテムです。

スキルに自信がついたら新しい世界に踏み出そう!|転職サイト登録なび
転職活動で成功するGmailの作り方(1人で複数アドレス作成もOK)|転職サイト登録なび

3. テスト結果一覧から生徒別成績表を自動作成する方法

この章では、エクセルで集計されたテスト結果一覧から、VLOOKUPをつかって生徒別成績表を自動作成する方法を解説します。出席番号を手掛かりに、一人ずつのシートを作成できます。

3-1. 出席番号を手掛かりに生徒別成績表を自動作成する仕組み

出席番号を手掛かりに生徒別成績表を作成する仕組みは、次のとおりです。

エクセルVLOOKUPの使い方【成績管理編】結果一覧から生徒別成績表を自動作成する仕組み
結果一覧から生徒別成績表を自動作成する仕組み(画像はクリックで拡大)

≫ 解説図のPDFファイル

【VLOOKUPを用いた自動作成の流れ】
出席番号を所定の欄に入力する
結果一覧から一致する出席番号を探す
一致する出席番号を見つけたら対応する点数を返す
所定の欄に点数が自動転記される

結果一覧の生徒名を表示させるセル「B2」と、点数の一番左上のセル「E2」の数式は次のとおりです。

【 セル「B2」の数式 】
=VLOOKUP($A$2,結果一覧!$A:$T,2,FALSE)

【 セル「E2」の数式 】
=VLOOKUP($A$2,結果一覧!$A:$T,4,FALSE)

$A$2出席番号を入力するセル
結果一覧!$A:$T結果一覧の範囲(※1)
22列目のデータを返せ
FALSEコードが一致する場合のみ返せ(※2)

(※1) A~T列の最下行まで検索範囲に含まれます。「$」をつけることで数式をコピーしても変化しません。

(※2) 「TRUE」にしてしまうと一致するコードがなくても、近い値を見つけて返してしまいます。他の生徒の点数の転記間違いを防ぐためにも、「FALSE」を選びましょう。
≫ 「FALSE」「TRUE」の選び方について詳しく知りたい方はこちら

3-2. 出席番号を手掛かりに生徒別成績表を自動作成する数式の作り方

エクセルVLOOKUPの使い方【成績管理編】結果一覧から生徒別成績表を自動作成する数式の作り方
結果一覧から生徒別成績表を自動作成する数式の作り方(画像はクリックで拡大)

≫ 解説図のPDFファイル

この章では、結果一覧から生徒別成績表を自動作成する数式の作り方を解説します。
完成形の数式は下記のとおりです。

数式の作り方

【 セル「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塾模試45678
Y塾模試1011121314
Z塾模試1617181920
模試の種類と科目に対応した列番号一覧

列番号の書き換えが面倒な方は、COLUMN関数で指定することもできます。
詳しくは下記を参照ください。
≫ VLOOKUPの使い方|6-20.COLUMNで列番号指定

COLUMN関数は横方向のコピーにのみ有効です。縦方向へのコピーにはrow関数が有効ですが、今回のケースでは複雑になります。列番号は手入力をおすすめします。

あなたを待っている会社がある
|転職サイト登録なび
転職活動で成功するGmailの作り方|1人で複数アドレス作成もOK

4. 判定表をもとに点数を自動判定する方法

この章では、判定表にそって点数を自動的に判定するVLOOKUPの式を解説します。近似値検索を使いますので、これまでの完全一致検索との違いを理解することが重要です。

4-1. 判定表をもとに点数を自動判定する仕組み

エクセルVLOOKUPの使い方【成績管理編】判定表をもとに点数を自動判定する仕組み
判定表をもとに点数を自動判定する仕組み(画像はクリックで拡大)

≫ 解説図のPDFファイル

【VLOOKUPを用いた自動転記の流れ】
判定したい点数が表示される
判定表から一致する点数を探す
一致する点数があった場合は、対応する判定結果を返す
一致する点数が無かった場合は、検索値を超えない最も近い判定結果を返す
所定の欄に判定結果が自動転記される

判定結果の一番上のセル「L2」の数式は次のとおりです。

【 セル「L2」の数式 】
=VLOOKUP(K2,判定表!$A:$B,2,TRUE)

K2判定したい点数が表示されるセル
判定表!$A:$B判定表の範囲(※1)
22列目のデータを返せ
TRUE検索値を超えない最も近い値を返せ(※2)

(※1) A~B列の最下行まで検索範囲に含まれます。「$」をつけることで数式をコピーしても変化しません。

(※2) 「FALSE」にしてしまうと、一致する点数が見つからない場合エラーを返してしまいます。一致する点数が無い場合に近似値を見つけて返すよう、「TRUE」を選びましょう。

検索方法:TRUEとFALSEの違い

検索方法:TRUEとFALSEの違い(画像はクリックで拡大)

≫ 解説図のPDFファイル

「TRUE」と「FALSE」の結果の違いを整理しました。使い分ける際の参考にしてください。


≫ 「FALSE」「TRUE」の選び方について詳しく知りたい方はこちら

4-2. 判定表をもとに点数を自動判定する数式の作り方

エクセルVLOOKUPの使い方【成績管理編】判定表をもとに点数を自動判定する数式の作り方
判定表をもとに点数を自動判定する数式の作り方(画像はクリックで拡大)

≫ 解説図のPDFファイル

この章では、判定表をもとに点数を自動判定する数式の作り方を解説します。
完成形の数式は下記のとおりです。

数式の作り方

判定結果の一番上のセル「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検索の数式の比較(画像はクリックで拡大)

≫ 解説図のPDFファイル

もしIFを使って同様の判定式を作ると上記のようになります。点数の刻みを増やすと、その分数式も長くなっていきます。
一方、TRUE検索であれば、点数の刻みを増やしたい場合、判定表の行数を増やすだけで対応できます。数式は変更不要です。

(注意)点数の刻みを増やした場合、判定表の点数の列は昇順に並べる必要があります

VLOOKUPを基礎から応用まで完璧にマスターしたい方は、次の記事もあわせて参照ください。
≫ 超図解!VLOOKUPの使い方|基礎から応用まで完璧マスター&スゴ技20選

転職して、私、成長したかな?
|転職サイト登録なび
転職活動で成功するGmailの作り方|1人で複数アドレス作成もOK

5. エクセルの成績管理の作成でVLOOKUPを使うメリット

この章では、エクセルの成績管理の作成でVLOOKUPを使うメリットを解説します。

  1. 数字の転記をミスなく効率化できる
  2. 元データの変更がすぐに反映される
  3. 独自のテーブルで成績判定できる

5-1. 数字の転記をミスなく効率化できる

数式が正しければ、生徒数や点数データの数に関係なく、自動で転記できます。
行数は100万行あっても検索できるので、全国模試などがあっても問題なく抽出できます。
列数も1万超でも検索可能です。たくさんの模試を受けていても一元管理できます。

VLOOKUPの検索可能数については下記の記事で詳しく解説しています。
≫ エクセルの社員名簿作成を自動入力で効率化|検索&転記もVLOOKUPで一発処理~6-1. 社員名簿が膨大な人数でも簡単に検索可能

5-2. 元データの変更がすぐに反映される

何らかの事情で元データを変更しても、集計結果に即時に反映されます。平均点や判定結果も同様に更新されます。
手計算であれば電卓を叩き直す必要があるところ、エクセルではそういったことは不要です。

5-3. 独自のテーブルで成績判定できる

判定表を変更すれば、判定結果も連動して変わります。独自の刻み幅で判定結果を出したい場合、独自の判定表を作成して組み込めばOKです。

点数だけでなく偏差値などの数値を判定することもできます

転職してやっと活躍できる場を見つけました
|転職サイト登録なび
転職活動で成功するGmailの作り方|1人で複数アドレス作成もOK

6. エクセルの成績管理の作成でVLOOKUPを使う際の注意点

エクセルの成績管理でVLOOKUPを使う際、幾つか注意点があります。

  1. 点数データに空白がある場合対策が必要
  2. 点数データのフォーマットに一定のルールが必要
  3. データが壊れるとすべての結果に影響する

6-1. 点数データに空白がある場合対策が必要

エクセルVLOOKUPの使い方【成績管理編】点数データに空白がある場合の対策
点数データに空白がある場合の対策(画像はクリックで拡大)

≫ 解説図のPDFファイル

点数データに空白がある場合、注意が必要です。VLOOKUPは検索対象のセルが空欄の場合、検索結果に0が表示されるからです。
受験して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. 元データが壊れるとすべての結果に影響する

元データが壊れると、転記先のすべての結果に影響します。ファイルの破損だけでなく、うっかり削除したりしてしまうこともあるので注意が必要です。

関連するデータはできるだけ一つのファイルにまとめましょう。バックアップをとるときも一つのファイルをコピーしておけばいいからです。もし複数のファイルにまたがっていると、片方をコピーしたときに参照先が移動してしまうおそれがあるので注意してください。

転職サイトは複数登録してベストな会社を見つけたい|転職サイト登録なび
転職活動で成功するGmailの作り方|1人で複数アドレス作成もOK

まとめ|VLOOKUPは転記・集計・成績判定で威力を発揮!

エクセルでの成績管理について、VLOOKUPを使えば劇的に効率化できることを解説しました。

  • 各模試の点数データを自動転記できる
  • 生徒別成績を1枚のシートにまとめられる
  • 点数を自動判定できる

あわせてVLOOKUPのメリットや注意点も解説しました。

再確認したい方は、下記から該当箇所を辿ってください。

【各模試の点数データを結果一覧に自動転記する】

解説内容詳細へ
仕組みを理解したい2-1.
数式を作りたい2-2.

【結果一覧から生徒別成績表へ自動集計する】

解説内容詳細へ
仕組みを理解したい3-1.
数式を作りたい3-2.

【独自の判定表で点数を自動判定する】

解説内容詳細へ
仕組みを理解したい4-1.
数式を作りたい4-2.

本文中の解説図はクリックで拡大表示します。別途ページではPDFファイルも参照可能です。
≫ PDFファイル参照ページはこちら

この記事を読めば、成績管理の単純入力作業を大幅に削減できます。模試の数が増えても大丈夫です。

生徒別の成績分析がしやすくなり、よりきめ細かな指導をできるようになります。時短にもつながり、残業を減らして早く帰れることでしょう。

VLOOKUPを基礎から応用まで完璧にマスターしたい方は、次の記事も参照ください。
≫ 超図解!VLOOKUP|基礎から応用まで完璧マスター&便利技20選

エクセルでお困りのことがありましたら、『エクセルの窓口』で検索してください。

エクセルの窓口検索

ブックマークお気に入りに登録していただけたら幸いです。

スキルに自信がついたら新しい世界へ飛び出そう!
転職サイト登録なび
転職サイト登録なび