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関数を組み合わせた方法なんかも書いてあります。
コメント