Categories: 教師のEXCEL講座

【教師のEXCEL講座】VLOOKUP関数~評定の計算~

こんにちは。福田泰裕です。

VLOOKUP関数はとても便利な関数です。
VLOOKUP関数なんて分からない!という方は、こちらをご覧ください👇

今回はVLOOKUP関数の使い方の例として、評定の計算に使う方法をご紹介します。

1年間のテストの点数や提出物、平常点などを合計し、最後に『評定』を算出します。
その評定を算出する際、EXCELをどのように使っているでしょうか?

実はこの評定の計算に、VLOOKUP関数が利用できるのです!

最後まで読んでいただけると嬉しいです。

目次

広告

VLOOKUP関数を使わずに評定を計算する方法

実際のところ評定を計算するには、VLOOKUP関数を知っている必要はありません。
様々な方法で評定を計算することができます。

IF関数で評定を計算する方法

例えばあなたの勤務校では、評定は「80点以上なら”5″、65点以上なら”4″、40点以上なら”3″、30点以上なら”2″、29点以下なら”1″」で付けるとします。

あなたなら、セルF2にどのような式を入力しますか?

「岩鬼君は32点だから”2″、殿馬君は71点だから”4″、・・・」と一人ずつ確認しながら手入力していたら日が暮れてしまいます。

まず思いつくのは、IF関数を駆使する方法です。

このようにIF関数のネストを繰り返して処理することができます。
しかし、この方法では式がとても長くなります。

IFS関数で評定を計算する方法

そこで、IF関数のネストより少しだけ便利な方法があります。

EXCEL2016から導入された IFS 関数です。
この IFS 関数を知っていれば、上のようなIF関数のネストを記述する必要がなくなります。

しかし、式の長さはそれほど変わっていません。

VLOOKUP関数で評定を計算しよう!

上の2種類の方法では、どちらをとっても手間はあまり変わりません。
どちらもそれなりに面倒くさいです。
また、 「やっぱり63点以上を評定4にしよう。」などと変更があった場合、すべてのセルを書き直す必要があります。

そこで登場するのがVLOOKUP関数です。

VLOOKUP関数は、言わずと知れた有能な関数です。
しかし、この有能な関数は目的の値を検索するだけではありません!

まず、右隅に下のような作業用の表を作ります。

そして、セルF2に式を入力します。

なんと、たったこれだけで完成です!
あとはオートフィルで下へコピーすれば終了です。
IFS 関数のときの式の長さと比べると、とてもスッキリしています。

「一体何が起きているんだ・・・?」

と思われる方も多いでしょう。簡単に仕組みを説明します。

VLOOKUP関数は、『範囲』の左端の列から『検索値』を見つける…のですが、探し方の特徴を思い出してください。

VLOOKUP関数の検索値の探し方の特徴

① 上から下に探す。
② 検索値と一致すればその値を行を取得する。
一致しない場合は、その検索値未満の最大値を探す

評定の計算には、この③の特徴をうまく利用しています。

例えば岩鬼くんの32点の場合、J列から『32未満の最大値』=『30』を見つけてきます。
殿馬くんの71点ならば、『71未満の最大値』=『65』を見つけることになります。

ブロック塀に例えると、岩鬼くんの32点は…

40点の壁を越えられないので1つ前の「評定 2 」となります。

殿馬くんの72点は…

80点の壁を越えられないので1つ前の「評定 4 」となります。

このようにして、評定が計算されています。

注意点は、右側の表の左端のJ列を上から昇順に並べておく必要があることです。
逆にすると、思い通りに表示されません。

まとめ:VLOOKUP関数で評定を計算できる

いかがでしたでしょうか。

今回はVLOOKUP関数の使い方の1つ、評定の計算をご紹介しまし。

もう IF 関数のネストや IFS 関数とはサヨナラして、VLOOKUP関数を使ってスッキリ評定を求めていきましょう!

最後まで読んでいただき、ありがとうございました!

質問やご意見、ご感想などがあればコメント欄にお願いします👇

福田 泰裕

33歳、2児の父。 山口県の高校教師で、担当は数学と情報。 毎日定時ダッシュするために、働き方改革を実施中。 数学教育・情報教育・教師の働き方・教師のEXCEL講座などを記事にしています!