【Excel】VLOOKUPとISNAはセットで覚えるべき

最近、

「ここの赤で塗ってある空白セルに数値を入力してください。全て入れれば赤が消えますので。」

とのExcelでの依頼業務が降ってきた。丁寧に条件付き書式で空欄の部分(と品目)は赤で塗りつぶされており、いくつかの空白セルに全て入力すれば赤い塗りつぶし部分も消える仕組みである。つまり、赤塗りつぶしが無くなるように全て入力してください。という意味である。

f:id:nmzfish:20210125003252p:plain

気の利いた条件付き書式(義務教育に組み込むべき内容)

f:id:nmzfish:20210125003417p:plain

納めるべき姿

なんのこっちゃない。マスターファイルのようなものがあれば、VLOOKUPで参照すればよい。

f:id:nmzfish:20210125004008p:plain

VLOOKUPでマスターから持ってくればよい

例えば、みかんの値段(D5セルの値)は、

=VLOOKUP($C3,$I$4:$L$6,2,FALSE)

で求めることが出来る。これも義務教育で習得済である。

f:id:nmzfish:20210125005203p:plain

素晴らしい!赤塗りつぶしは消えた!

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(検索値,範囲,列番号,検索方法))

f:id:nmzfish:20210125011400p:plain

ISNAとVLOOKUPを組み合わせた結果

そもそもマスターに不備があったことも分かるようになった*2

ちなみに、実際に行った業務では、赤塗りつぶし部がほぼマスターにない項目だった*3。 

そもそも依頼内容をきちんと理解しましょうとか、ちゃんとしたマスター使いましょうとか、様々あると思うが、VLOOKUPへフールプルーフとして覚えておいて損はないだろう。

Excel関数逆引き辞典パーフェクト 第3版

Excel関数逆引き辞典パーフェクト 第3版

  • 作者:きたみ あきこ
  • 発売日: 2016/07/20
  • メディア: 単行本(ソフトカバー)
 

 

*1:この例では、条件付き書式によって赤に塗りつぶされる仕組みがあるので、何かしらの文字を入れるのが良い

*2:条件付き書式にも手を加えているが

*3:でなければわざわざ頼んでこないのだが...。