[Excel]VLOOKUPの結果を0にしない

Excelでデータを検索して表示させるのにはVLOOKUP(ブイルックアップ)関数が便利ですが、結果が空欄のときに0が表示されてしまいます。

その解決方法を調べます。

スマートな書き方で結果を空白にしたい

VLOOKUPで検索した結果、引用元が空欄だと0になってしまったりして、冗長(じょうちょう。同じことが複数あること。ここでは同じ関数を何度も書くこと。)な書き方で対応しがちです。

もっとスマートな書き方があったなと思いつつもド忘れしてしまいました。

調べたら良さそうなのがあったので、次に忘れてド忘れした時用にメモしておきます。

Excelへの記述と、その記述の説明を色で対応させて書いてみます。

× やりがちな書き方

=IF(VLOOKUP(A2,元データ!$A$2:$B$1001,2,FALSE)="","",VLOOKUP(A2,元データ!$A$2:$B$1001,2,FALSE))
  • VLOOKUP…垂直方向(Vertical)に、調べていきます(Look up)。
  • A2に入っている値を使って、シート「元データ」の、セル「$A$2」~「$B$1001」の1列目から調べていきます。見つかった場合、2列目(B列)から値を取得します。
  • 見つかった値が空欄だったら、空欄を返し、そうで無かった場合は、
  • 【A2に入っている値を使って、シート「元データ」の、セル「$A$2」~「$B$1001」の1列目から調べていきます。見つかった場合、2列目(B列)から値を取得します。】をもう一度実行して、値を取得します。
  • 探して」、「見つかったら」、「探して」をもう一度やる、と二度手間な感じです。件数が多くなってくると、計算時間も伸びてきます。

○ すっきりした書き方

いきなり正解(?)の書き方です。

=VLOOKUP(A2,元データ!$A$2:$B$1001,2,FALSE) & ""
  • VLOOKUPで探した場合、見つからないと、結果は「空(から)」となり「0」に読み替えられてしまいます。
  • 明示的に0文字の文字列をくっつけることで、結果は「0文字の空白」という事になり、「0」として扱われないようです。
  • ただし、文字列として扱われてしまうので、引用する内容が文字列の時だけにしましょう。

0が表示されて困るのは製品名や氏名、住所など文字列のことが多いので、この記述ですっきりすることが多いのでは無いでしょうか。

「数値」ではなく「数字」として表示されればいいのであれば、「右寄せ」を組み合わせることで数値っぽく表示させることができます。

× やりがちな書き方

該当が無い時に結果が「#N/A」(Not Applicable)にならないようにする時の書き方です。

=IF(ISERROR(VLOOKUP(A2,元データ!$A$2:$B$1001,2,FALSE)),"",VLOOKUP(A2,元データ!$A$2:$B$1001,2,FALSE)&"")
  • VLOOKUPで調べた結果エラーだったら空白、エラーじゃ無い場合、VLOOKUPをもう一度検索する

VLOOKUPで探して、エラーだったらもう一度VLOOKUPで探した内容を表示させます。

VLOOKUPを覚えたての頃には納得し易い記述ですが、同じ検索を2回やるので件数が多くなってくると再計算に時間がかかりますし、スマートではありません。

○ VLOOKUPを2つ書くより再計算時間が速い書き方

COUNTIFを組み合わせた方法です。

=IF(COUNTIF(元データ!$A$2:$A$1001,A2)=0,"",VLOOKUP(A2,元データ!A2:$B$1001,2,FALSE)&"")
  • 検索値が存在する回数をカウント。
  • 0だったら、存在しないので、空白
  • 存在している場合、VLOOKUPで検索結果を持ってくる。

VLOOKUPは文字列を取得してくるので、COUNTIFの方が早いそうです。

ただし、件数が何万件とかになってくるとCOUNTIFの方が遅くなる実感があります。

VLOOKUPは1件ヒットしたら検索をやめますが、COUNTIFは1件ヒットしても検索を続けるからだと思います。

○ もっと速そうな書き方

エラー表示もさせないで、結果も0を返さない記述です。

=IFERROR(VLOOKUP(A2,元データ!A2:$B$1001,2,FALSE)&””,””)
  • VLOOKUPで検索式の結果がエラーじゃ無い時は式の結果を採用(0文字を付け足すことで数値にしない)エラーの場合は空欄にする

結果が文字列なら、「&””」を付けるだけで解決するのは良いです。

△ 冗長な書き方だけど…?

VLOOKUPで検索した結果が数値で「0」の時は「0」、空欄の時は空欄にしたい場合はなかなか難しい感じです。

=IFERROR(IF(VLOOKUP(A2,元データ!A2:$B$1001,2,FALSE)=””,””,VLOOKUP(A2,元データ!A2:$B$1001,2,FALSE)),””)

とするしか無い気がします。

  • VLOOKUPした結果が空欄だったら空欄にする。空欄じゃなかったらVLOOKUPした結果を表示する

一応、「0」の時も空欄になってしまって良いなら書式設定を「#」にするという合わせ技もある様です。

(「セルの書式設定」→「表示形式」→「分類:ユーザー定義」で種類のところを「#」にする。)

後は引用元のデータに、空白にしたい結果セルに「’」(半角シングルクォート。アポストロフィ。SHIFT+[7]キーで入力。)を入れておくと良いのですが、突合先のデータにシングルクォートを入れておくのは難しい場合もあります。

× 更にやりがちな冗長な書き方

=IF(ISERROR(VLOOKUP(A2,元データ!A2:$B$1001,2,FALSE)),””,IF(VLOOKUP(A2,元データ!A2:$B$1001,2,FALSE)=””,””,VLOOKUP(A2,元データ!A2:$B$1001,2,FALSE)))
  • VLOOKUPで検索した結果がエラーだったら空欄を表示、エラーでは無くてVLOOKUPで検索した結果が空欄だったら空白を表示、空欄では無かったらVLOOKUPで検索した結果を表示

データを表示させるためにVLOOKUPで3回検索することになっています。

「やりがちじゃない」ですか…?

ついやってしまうのです。

つい!

<参考サイト>

Microsoft OfficeのTips(ちっぷす。豆知識。)が色々載っていて参考になるサイトです。今回見たページはずばりVLOOKUPで0を返さない。

エクセルで使う関数の解説があります。VLOOKUPやHLOOKUP、エラーが出たときの対処なども書いてあります。

<今回直接関係ないけど、面白かったサイト>

「VLOOKUPの限界」ということで、VLOOKUPを色んな視点で見ています。VLOOKUPよりも汎用性(はんようせい)の高いINDEX関数とMATCH関数を組み合わせた方法なんかも書いてあります。

広告