【教師のEXCEL講座】VLOOKUP関数~空欄&エラーの処理~

教師のEXCEL講座

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

EXCELのVLOOKUP関数の使い方を解説します!
VLOOKUP関数なんて分からない!という方は、まずこちらの記事をご覧ください👇

VLOOKUP関数を使って、成績処理やテストの個人票を作成するときに困るのが、空白とエラーの処理です。
今回は、この空白とエラーを処理する方法について解説していきます。

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

VLOOKUP関数の罠:空欄は「0」が返される

VLOOKUP関数は表検索ができる超有能な関数なのですが、特徴があります。
それは、空欄だと「 0 」が返されるということです。

次の表は、5人のテストの結果です。
岩鬼くんは英語が0点、山田くんは英語を欠席しました。
ここから、下の表にVLOOKUP関数を使って集計してみましょう。

vlookup

セルB10には、=VLOOKUP(A10,$A$2:$E$6,2,FALSE)
と入力し、テストの点数も同様にVLOOKUP関数で表検索します。

vlookup

平均点は、=AVERAGE(C10:E10)
でいいですね。

vlookup

これをオートフィルを使って下へコピーすると…

vlookup

このように、山田くんの空欄が、0点として検索されてしまっています!
平均点も、90,100,0の平均で63.3点!
これは可哀相ですね……。

このように、VLOOKUP関数は空欄を「 0 」で返すという特徴があります。
慣れないうちはここでミスをするので、気を付けてください!

VLOOKUP関数の特徴

VLOOKUP関数は、空欄の場合は「 0 」を返す!
対策しないとミスにつながる!

VLOOKUP関数で、空欄を空欄のまま表示する方法

VLOOKUP関数を使うとき、この空欄対策は必ず必要になります。
(※条件付き書式で「 0 は非表示」にしても、平均点は大幅ダウンなので効果はありません…。)

その対策方法は、IF 関数と組み合わせることで実現可能です。
IF 関数を使い、「空欄ならば空欄、そうでなければそのまま表示」と指定します。

vlookup

かなり長くなりましたが、下のような意味になります。

vlookup

このように式をつくり、オートフィルでコピーすると…👇

vlookup

ちゃんと空欄は空欄としてコピーして、平均点も空欄を無視して計算し、95.0点となりました。

このように、空欄対策は IF 関数を使って行いましょう。

VLOOKUP関数で、エラーを空欄として表示する方法

次に、VLOOKUP関数でエラーの処理の方法をご紹介します。

例えば次の表のように、科目ごとの平均点を算出しようとした場合…👇

vlookup

平均を計算するAVERAGE関数は、1つでもエラーがあると計算できません。
番号6番は存在しないので、エラーではなく空欄にする必要があります。

そこで登場するのがIFERROR関数です!

iferror

IFERROR関数は、エラーの場合にどう処理するのかを指定できる関数です。
今回はエラーの場合は空欄にしたいので、

このようにすれば、エラーはすべて空欄となります。

まず、名前の欄は👇

iferror

=IFERROR(VLOOKUP(A10,$A$2:$E$6,2,FALSE),””)

次に得点の欄は👇

iferror

=IFERROR(IF(VLOOKUP(A10,$A$2:$E$6,3,FALSE)=””,””,VLOOKUP(A10,$A$2:$E$6,3,FALSE)),””)

どちらも先ほどの空欄対策の式を、IFERROR関数で囲った形です。

iferror

これで、エラーを空欄として表示することに成功しました!

まとめ:VLOOKUP関数はエラー対策をしっかりと

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

VLOOKUP関数は便利なのですが、実際に使ってみると上手くいかないことが多いです。
その原因は、空欄とエラーの処理を忘れていることが多いと思います。

空欄にするセルをDeleteで消してしまうと、翌年それに気づかずに大事件を引き起こすかもしれません。
セルを消すことなく、翌年以降も使えるシートをつくれると良いですね!

–>

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

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

コメント

タイトルとURLをコピーしました