VLOOKUPで0を表示しない方法とは？発生原因と対処法を解説

【関連記事】VLOOKUPとは？使い方や活用方法をわかりやすく解説

【関連記事】Excel（エクセル）の関数30選！よく使う基本の関数を一覧で紹介｜CANVAS

VLOOKUPで0を表示しない方法とは

ExcelのVLOOKUPは、検索値に応じたデータを取得する関数で、大量のデータから商品名や住所の検索を行うなど、さまざまなデータ管理やデータ操作で活用できます。

しかし、引数の指定を間違えると意図した取得結果を得ることができません。中でも、VLOOKUPを使った結果、0が表示されて困っている方もいるのではないでしょうか。

この記事では、VLOOKUP関数を使用する際に0が表示される原因と、その対処法について解説します。また、他の意図しない検索結果の表示についても説明します。

VLOOKUPとは？使い方や活用方法をわかりやすく解説

VLOOKUPで0が表示される原因は？

ここでは、VLOOKUPを使った際に0が表示される原因が空白のセルであることを説明します。また、空白のセルが発生する理由と、空白のセルの探し方も確認します。

VLOOKUPで0が表示される原因は、検索して取得する対象のセルが空白であることです。

VLOOKUPの構文は「=VLOOKUP(検索値,範囲,列番号,検索方法)」であり、検索値を指定して、範囲内の指定した列番号の対応するセルから値を取得します。

検索した結果、検索値に紐づくセルの内容が空白だと、この数式は0を返します。

例えば、上記の図でD3セルの社員コードを検索値としてE3セルに社員名を表示する場合、取得する対象のセルが空白だと、VLOOKUP関数はこの空白を0として返します。

そのため、検索値「S0004」で検索すると、取得対象のB5セルは社員名が入力されていない空白のセルであるため、結果として0が表示されるのです。

セルが空白になるケースとして、色々な理由が考えられます。これらのどの理由で空白になっていても、VLOOKUPの結果はすべて0です。

そのため、VLOOKUPが0であったとしても、その取得元のセルが空白である理由までは分かりません。空白のセルが発生するケースとはどのような時か知って、整理しておきましょう。

まず、取得する対象のデータがもともと存在しない場合です。例えば、社員コードに対応する社員が存在せず、社員名が空白になっている場合が考えられます。この場合でも、VLOOKUPの結果は0が表示されます。

また、参照データが作成途中で未入力のセルがある場合も0が表示されます。後から入力する場合でも、まだ入力されていないのでVLOOKUPの結果は0になります。

さらに、誤操作によってデータが削除された場合も0が表示されます。誤操作で削除されたことに気づかずに検索結果だけを見て、元々0だったと誤認してしまう恐れがあります。

このように、空白のセルが発生する理由はさまざまですが、VLOOKUPで値を取得するとすべて0で表現されます。その0がどのような意味であるかは、取得元のセルを見て確認しましょう。

VLOOKUPでデータを取得する場合、検索値に紐づいてデータを取得する対象の列に空白のセルがあれば、0が表示されることになります。そのため、あらかじめ空白のセルがないかを確認しておくと良いでしょう。

空白のセルがあれば、その結果としてVLOOKUPで0が表示されても良いかどうか判断しましょう。

空白のセルの探し方としてまず挙げられるのが、Excelのショートカットキーを使用する方法です。 Ctrlキーと矢印キーを同時に押すと空白セルの手前まで移動するため、空白セルの場所が分かります。再度矢印キーを押すことで、空白セルを飛び越えて次のデータがあるセルまで移動できます。

この方法は簡単で、空白のセルを1つずつ調べるのに適していますが、多くの空白セルを探す場合は手間がかかります。

もう1つの方法として、オートフィルターで空白セルだけを抽出して確認することもできます。オートフィルタを使うと同一の列内の空白のセルをまとめて確認でき、必要であれば、そのままデータ入力が可能です。

VLOOKUP関数で0を表示しない手順

ここでは、VLOOKUPで0を表示しないようにする方法を解説します。0を表示しない方法はさまざまですが、それぞれメリットやデメリットもあります。データ管理を行う上で、最も適切な方法を選択することが大切です。

まず、VLOOKUPに「&””」を組み合わせることで、検索結果が0の場合に空白を表示させる方法です。

VLOOKUPで取得した値の後ろに、空の文字列「""」を&でつなぐことにより、セルの値を文字列として扱い、0を表示しないようにできます。

例えば、「=VLOOKUP(D3,A2:B6,2,FALSE)&""」と入力すると、VLOOKUP関数の結果が0であった場合、その値を空白として表示することができます。

ただし、この方法を使用するとすべての検索結果が文字列として扱われるため、計算に使用できなくなる点に注意が必要です。

IF関数を組み合わせることで、VLOOKUPの結果の0を表示しないようにすることができます。

例えば、「=IF(VLOOKUP(D3,A2:B6,2,FALSE)=0,"",VLOOKUP(D3,A2:B6,2,FALSE))」とすることで、検索結果が0であった場合に空白を表示することができます。

この数式を簡単に説明すると、VLOOKUPの結果が0の場合は「""」を、そうでない場合は取得するセルの内容をそのまま表示させるという意味です。

これにより、数値の0を非表示にし、空白として表示させることが可能です。

この方法を使うと数式が長くなってしまいますが、オプション設定や書式設定を変更することなく、簡単に0を非表示にできます。

また、検索結果が数値であれば計算も可能であり、空白以外の文字列にも置き換えることができて便利です。

0を非表示にするために、ユーザー定義書式を設定する方法もあります。

具体的には、0を非表示にしたいセルを選択して右クリックし、「セルの書式設定」で表示形式を「ユーザー定義」に変更し、「種類」欄に「0;-0;;@」と入力します。

これにより、0が表示される場合にそのセルが空白として表示されます。この設定はセル単位で適用でき、数式を変更せずに設定できます。

ただし、他の表示形式設定を使用している場合、この方法が適用できないことがあります。

また、元々0が表示されて間違いないセルにまでユーザー定義書式を適用してしまうと、正しい0の表示も非表示となるため注意が必要です。

【参考】：Microsoft|サポート　ゼロ値を表示する、または非表示にする

条件付き書式で0の文字色を白にすることで、VLOOKUPの結果として表示された0を視覚的に非表示にすることができます。

具体的には、条件付き書式で0の文字色を背景色と同じに設定することで、セルの内容が見えなくなります。方法は、対象のセル範囲を選択して「条件付き書式」を選び、「セルの強調表示ルール」をクリックします。

「指定の値に等しい」をクリックし、「次の値に等しいセルを書式設定」に0を入力します。

「書式」のボックス、「ユーザー設定の書式」の順にクリックし、「フォント」タブで文字色を背景色と同じ色である白に設定します。また、「塗りつぶし」タブで背景色を色なしにします。

これにより、0が表示されても白色の文字となり、背景に溶け込んで見えなくなります。この方法を使うと数式を記述することなく0を表示しないようにできます。

しかし、この条件付き書式を設定した範囲では、本来表示されるべき正しい0も見えなくなるため、注意が必要です。また、条件付き書式の設定が多くなると、Excelの動作が重くなることもあります。

Excelのオプション設定で0を表示しないようにすることができます。 Excel上部の「ファイル」メニューから「オプション」を選択し、「詳細設定」に進みます。

次に、「次のシートで作業する時の表示設定」内の「ゼロ値のセルにゼロを表示する」のチェックを外します。これにより、選択したシート内の0が非表示になります。

この方法では、数式を変更せずに簡単に設定でき、シート全体に対して一括で適用できるため、広範囲のデータに対して有効です。

しかし、本来表示されるべき正しい0も非表示となり、セル単位での設定ができないため特定のセルだけ非表示にすることはできません。

VLOOKUPで0が表示される以外の意図しない表示とは

0が表示される以外で、VLOOKUPで意図しない結果が表示されるケースについても知っておきましょう。

日付が1900/1/0と表示される場合、#N/Aエラーが表示される場合などがあります。ここでは、それぞれの原因と対処方法を解説します。

ここまで説明してきたように、VLOOKUPで検索値に紐づくセルが空白の場合は0が表示されます。 しかし、対象のセルが空白であるにもかかわらず、0ではなく「1900/1/0」と表示されることがあります。

これは、取得する値の表示形式が「日付」であるときに発生します。Excelの仕様によるもので、日付をシリアル値として扱うために起こる現象です。

「1900/1/1」を基準日として日付を数値で管理しているため、0を日付として解釈した「1900/1/0」と表示されるのです。

この問題に対処するためには、IF関数を組み合わせて使うことが効果的です。「=IF(VLOOKUP(F3, A2:C6, 3, FALSE)="","",VLOOKUP(F3, A2:C6, 3, FALSE))」という数式を使います。

この数式では、VLOOKUP関数の結果が空白の場合に空白を返し、そうでない場合にVLOOKUP関数の結果を返します。この方法を用いることで、「1900/1/0」と表示されることを回避することができます。

ExcelのVLOOKUP関数を使用している際に、「#N/Aエラー」が表示されることがあります。#N/Aエラーは検索値が見つからないことを意味します。

#N/Aエラーの主な原因は、検索対象の値が参照範囲内に存在しないことです。たとえば、検索値にスペルミスがあったり、余分なスペースが含まれてたりする場合に発生します。

また、参照範囲が正しく設定されていない場合もエラーが発生します。検索値が最初の列にない場合や、列番号で範囲外を指定している場合も原因となります。

#N/Aエラーを表示しないようにするには、まず、検索値・範囲・列番号の設定を確認し、正確に指定されていることを確認します。また、検索値に余分なスペースが含まれていないかもチェックしましょう。

さらに、エラーが発生した場合に代わりの値を表示する方法として、IFERROR関数を使う方法があります。

例えば、「=IFERROR(VLOOKUP(D3, A2:B6, 2, FALSE), "見つかりませんでした")」のようにIFERROR関数を組み合わせることで、エラーが発生した時に指定した値を返すことができます。

この数式を使用すると、検索値が見つからない場合に「見つかりませんでした」と表示されます。

VLOOKUP関数でエラーが出た？#REF!エラーなどの解決法を詳しく解説

0を表示しないようにしてシートを見やすくしよう

ここまで、VLOOKUP関数で0やその他の意図しない結果が表示される原因と、表示しないようにする方法を解説してきました。さまざまな方法で表示しないようにできることが分かりました。

意図しない取得結果を表示しないように工夫することで、データの見やすさを向上させることができます。

しかし、方法によってメリット・デメリットがあります。それぞれの方法の特徴を理解して適切な方法を使用し、業務効率アップに役立てましょう。

その他VLOOKUP関連記事

その他、VLOOKUPに関連する内容は、下記の記事でもご紹介しています。ぜひ参考にご覧ください。

VLOOKUP関数で複数条件を指定する？簡単にできる方法を図解

VLOOKUPで別シートを参照するには？記述方法や実践例を解説

VLOOKUP関数でエラーが出た？#REF!エラーなどの解決法を詳しく解説

VLOOKUP関数がうまく反映されない？その原因と対策を図解

VLOOKUPとIFの組み合わせで何ができる？便利な使い方を解説

VLOOKUPで部分一致検索するには？ワイルドカード使用法を解説

VLOOKUPとXLOOKUPの違いとは？使い方を図解で解説

VLOOKUPで0を表示しない方法とは？発生原因と対処法を解説