【Excel】VLOOKUPとISNAはセットで覚えるべき
最近、
「ここの赤で塗ってある空白セルに数値を入力してください。全て入れれば赤が消えますので。」
とのExcelでの依頼業務が降ってきた。丁寧に条件付き書式で空欄の部分(と品目)は赤で塗りつぶされており、いくつかの空白セルに全て入力すれば赤い塗りつぶし部分も消える仕組みである。つまり、赤塗りつぶしが無くなるように全て入力してください。という意味である。
なんのこっちゃない。マスターファイルのようなものがあれば、VLOOKUPで参照すればよい。
例えば、みかんの値段(D5セルの値)は、
=VLOOKUP($C3,$I$4:$L$6,2,FALSE)
で求めることが出来る。これも義務教育で習得済である。
VLOOKUPを用いれば2秒で終わる内容であった。
ここで問題が発生している。もしマスターファイルに不備があったとする。VLOOKUPの第4引数をFALSEの完全一致で検索した場合、参照範囲に検索値が無いとエラーの#N/Aが出る。
そして、条件付き書式の赤塗りつぶしを貫通する。ちなみに、この例だと、りんごの値段のみ#N/Aで他は空欄でも赤塗りつぶしは消える。確認用の赤塗りつぶしのみで見ると、完了したように見える。
そこで、ISNA関数を使う。ISNA関数とは、対象セルが#N/Aかどうかを判定する関数である。
=ISNA(対象セル)
対象セルが#N/Aであれば、TRUEを返す。完全一致検索でマスターに不備がある可能性がある場合、このISNA関数とVLOOKUPをIF関数で組み合わせた方が安心である。
もし#N/Aが出たなら何かしらの処理を行い*1、そうでないならVLOOKUPで参照するような関数を組む。
つまり、使用するべきVLOOKUP関数は以下のようになる。
=IF(ISNA(VLOOKUP(検索値,範囲,列番号,検索方法))=TRUE,#N/Aのときの処理,VLOOKUP(検索値,範囲,列番号,検索方法))
そもそもマスターに不備があったことも分かるようになった*2。
ちなみに、実際に行った業務では、赤塗りつぶし部がほぼマスターにない項目だった*3。
そもそも依頼内容をきちんと理解しましょうとか、ちゃんとしたマスター使いましょうとか、様々あると思うが、VLOOKUPへのフールプルーフとして覚えておいて損はないだろう。