学校の先生のためのエクセル使い方教室
成績処理でのvlookup関数の使いどころ
成績処理でvlookup関数を使用するときのポイントを３つ紹介します。
紹介するのは次のような成績処理です。
- シートごと（1回目、2回目、3回目）に入力されている得点データを1つのシート（総合）に統合して表示
- 統合したシートで平均点を算出し、点数に応じて5段階評価（*1）をつける
- *1…90点以上は５、80点以上は４のようなもの
いずれもVlookup関数を使用して表示、算出することができます。
点数に応じて5段階評価をつけるときに平均点を算出していますが、総合点、合計点のようなものを算出して評価を算出する場合も、ココに紹介した方法で可能です。
vlookup用のキーとなるIDを作成する
vlookup関数を使うにはキーとなるデータが必要です。キーは他のデータと重複がないものを指定することが必要で、名前にしてしまうと同姓同名がいた場合に正しく表示されません。
組と番号のデータを使って、下記のようにID列をつくることができます。
ID＝組の列の値×１００＋番号の列の値
- これで３ケタ目が組、２ケタで番号を表す数値となります。
組が「A」「B」のように数値でない場合は、SWITCH関数を使うことで変換することができます。
【SWITCH関数】
- =SWITCH(判定式,値1,結果1,値2,結果2…）
- 「判定式」の数値が「値1」なら「結果1」を表示、「値2」なら「結果2」を表示…
- 上の例ではA2セルが「A」なら「1」、「B」なら「2」、「C」なら「3」としている
ID列はvlookup関数用に使用するものですので、表示させたくない（印刷時に出てほしくない）場合は列ごと非表示にしておくことができます。
各シートにわかれている成績を一覧表示する
各シートにID列が準備出来れば、まとめてひとつのシートに表示することができるようになります。
【VLOOKUP関数】
- =VLOOKUP(検索値,範囲,列番号,検索方法）
- 検索値…キーとなるセルを指定
- 範囲…検索範囲を指定（範囲が別シートの場合はシート名をつける）
- 列番号…指定した範囲の中で何列目の値を結果として表示するかを指定
- 検索方法…指定したキーと完全に一致するものにするため「FALSE」を指定
VLOOKUP関数の入力方法
VLOOKUP関数の引数（検索値、範囲など）を入力するには、「=vlookup(」とセルに入力した後で、指定したいセルをクリックすると自動的に「D2」のようにセル値が入力されます。範囲で別シートのセルを指定する場合にも、この方法で入力できます。ただし「,」は手入力する必要があります。
総合得点から評定を算出する
総合得点、平均点などから●●点以上なら「５」のようにVLOOKUP関数で算出することができます。
【VLOOKUP関数】
- =VLOOKUP(検索値,範囲,列番号,検索方法）
- 検索値…キーとなるセルを指定（判定対象となる点数）
- 範囲…検索範囲を指定（昇順になるように表を用意しておく）
- 列番号…指定した範囲の中で何列目の値を結果として表示するかを指定
- 検索方法…近似一致とするため「TRUE」を指定
検索方法を近似一致（TRUE）とする
VLOOKUP関数に指定する検索方法を近似一致（TRUE）とすることで●●点以上なら「５」のように評定を算出することができます。ただし、範囲で指定する領域は昇順（小さい値→大きい値）に作成しておく必要があります。
