VLOOKUPの「0」の原因|非表示や他文字へ置換&空白セルの探し方

VLOOKUPの「0」の原因|非表示や他文字へ置換&空白セルの探し方
  • VLOOKUPで「0」になる原因が知りたい
  • 「0」を非表示にしたい
  • 「0」を他の文字に置き換えたい

VLOOKUPは、参照先の該当セルが空白の場合に0を返します
エラーではありませんが、意図しない「0」が表示されると困るケースもあることでしょう。

この記事では、VLOOKUPの結果が「0」になる原因対策を解説します。
また「0」を非表示にしたり、他の文字の置き換える方法も解説しまので参考にしてください。

お急ぎの方は、下記から該当箇所へ移動できます。

◆空白セルを探すには

内容解説へ
Ctrl + 矢印(↓→↑←)で空白セルの前後まで移動する2-1.
空白セルまでジャンプする2-2.
オートフィルタで空白セルのある行を抽出する2-3.
条件付き書式で空白セルに色を付ける2-4.

◆「0」を非表示にしたり、他の文字へ置き換えるには

内容解説へ
オプション設定で「0」を非表示にする3-1.
書式設定で「0」を非表示にする3-2.
条件付き書式で「0」の文字色を白色にする3-3.
「&””」と組み合わせて「0」を非表示にする3-4.
「&””」「IFERROR」「VALUE」と組み合わせて「0」を非表示にする
あるいは他の文字へ置き換える
3-5.
「IF」と組み合わせて「0」を非表示にする
他の文字へ置き換える
3-6.

本文中の解説図はクリックで拡大表示します。別途ページでPDFファイルも参照可能です。
≫ PDFファイル参照ページ

VLOOKUP を基礎から応用まで完璧にマスターしたい方は、こちらの記事も参照ください。
≫ VLOOKUPの使い方|基礎から完璧マスター&便利技20選を超図解!

マイクロソフト公式サイトの解説を参照したい方はこちら
≫ Microsoft|Officeのサポート~VLOOKUP関数

1. VLOOKUPで「0」になる原因と問題点

エクセルVLOOKUPの使い方【エラー解決編】検索結果が0になるケース
検索結果が「0」になるケース(画像はクリックで拡大)

≫ 解説図のPDFファイル

VLOOKUPで「0」になる原因は、参照先の該当セルが空白であることが考えられます。
空白になるケースは、主に次のとおりです。

  • セルにもともとデータがない
  • 誤操作でセルのデータを削除してしまった
  • データの入力途中(いずれ入力する)

1-1. もともとデータがないケース

もともとデータが存在しないのであれば、VLOOKUPが0を返して当然です。
しかし、その結果を計算に組み込んでいる場合、誤った答えを出す恐れがあります。

例えば試験の結果をVLOOKUPで集計している場合を考えてみましょう。
試験を受けていない科目があれば、そのセルは空白です。しかしVLOOKUPは0を返します。つまり0点扱いになります。すると平均点に影響するわけです。

1-2. 誤操作でセルのデータを削除してしまったケース

誤操作でデータを削除してしまった場合も、VLOOKUPは0を返します。うっかりDeleteキーに触れてしまった場合などです。

この場合、速やかに戻さなければなりません。しかし、0が表示されてしまうと、誤操作で削除されたことに気付きません。もともと0だったかも、と誤認してしまうため、本来あるべき状態に戻せなくなります。

1-3. データの入力途中(いずれ入力する)のケース

参照データが作成途中で未入力のセルがある場合も、VLOOKUPは0を返します。転記先に0が並ぶわけです。

この場合、状況を把握しているので混乱は少ないでしょう。とはいえ無意味に0が並んでいると煩わしいです。データが未完成なのか、実は0点が続いているのか判断しづらいです。できれば見分けられる方が、見た目もスッキリすることでしょう。

スキルに自信がついたら新しい世界に踏み出そう!|転職サイト登録なび
転職活動で成功するGmailの作り方(1人で複数アドレス作成もOK)|転職サイト登録なび

2. VLOOKUPで「0」の原因となる空白セルを探す方法

VLOOKUPで「0」となる原因は、参照先に空白セルにあることです。データに空白セルがあることが好ましくない場合は、速やかに空白セルを探す必要があります

この章では、空白セルを探す方法を4つ解説します

内容解説へ
Ctrl + 矢印(↓→↑←)で空白セルの前後まで移動する2-1.
空白セルまでジャンプする2-2.
オートフィルタで空白セルのある行を抽出する2-3.
条件付き書式で空白セルに色を付ける2-4.

2-1. Ctrl + 矢印(↓→↑←)で移動する

エクセルVLOOKUPの使い方【エラー解決編】空白セルの探し方(Ctrl+矢印)
空白セルの探し方(Ctrl+矢印)(画像はクリックで拡大)

≫ 解説図のPDFファイル

Ctrl + 矢印(↓→↑←)で空白セルの手前まで移動できます。さらに押すと空白セルをまたいでデータの入っているセルに移動します。さらに押すとこれを繰り返します。

【メリット・得意なシーン】
■一番簡単
■特定の行や列だけなら素早く調べられる

【デメリット・苦手なシーン】
■どこを探せばいいかわからない場合に時間がかかる
■たくさんあると何度も矢印キーを押す必要がある

100万行の果てへ

データの端まで来てあともう一度押すと、シートの果てまで飛んでしまいます。行では100万、列では1万3千列の果てになります。
その場合あわててマウスのスクロールで戻ろうとすると時間がかかります。再度Ctrlと反対向きの矢印を押せば元の場所に戻れます。

2-2. ジャンプ機能を使う

エクセルVLOOKUPの使い方【エラー解決編】空白セルの探し方(ジャンプ)
空白セルの探し方(ジャンプ)(画像はクリックで拡大)

≫ 解説図のPDFファイル

ジャンプ機能を使えば、簡単に空白セルに探せます。縮小表示すれば空白セルが一目瞭然で、一覧性が高いのが特長です。

ただ、特定のセルで何か操作をすると結果表示が消えてしまいます。再度同じ操作をする必要があります。

【メリット・得意なシーン】
■やや簡単
■全体でどのくらいあるかが把握しやすい

【デメリット・苦手なシーン】
■特定のセルを編集すると結果表示がリセットされる
■編集をともなう場合は何度も処理が必要になり面倒

2-3. オートフィルタで空白セルのある行を抽出する

エクセルVLOOKUPの使い方【エラー解決編】空白セルの探し方(オートフィルタ)
空白セルの探し方(オートフィルタ)(画像はクリックで拡大)

≫ 解説図のPDFファイル

オートフィルタで空白セルを抽出することができます。抽出した空白セルにデータを一気に貼り付けることができるので、編集を伴う場合に便利です。

ただ、準備に手間がかかります。列ごとにしか調べることができず、全体の把握もしにくいです。

【メリット・得意なシーン】
■特定の列を調べるのには簡単
■抽出した結果に対してまとめて貼り付けたりできる
(※)ただし「値貼り付け」にすると隠れているセルにまで貼り付けられてしまうので注意してください。

【デメリット・苦手なシーン】
■準備に手間がかかる
■列ごとにしか調べられない

フィルタのクリアを忘れずに

隣の列を調べる時は、今かけているフィルタをクリアしてからにしましょう。フィルタをかけたまま隣のフィルタを設定してしまうと、両方の列に空白がある行しか抽出されません
空白セルを見落とす恐れがあるので注意しましょう。

2-4. 条件付き書式で空白セルに色を付ける

エクセルVLOOKUPの使い方【エラー解決編】空白セルの探し方(条件付き書式)
空白セルの探し方(条件付き書式)(画像はクリックで拡大)

≫ 解説図のPDFファイル

条件付き書式で空白セルに色を付けることで、空白セルを探すことができます。

【メリット・得意なシーン】
■全体でどのくらいあるか把握しやすい
■色を自由に設定でき、視認性に優れいている
■入力すると色が消えていき、進捗がわかりやすい

【デメリット・苦手なシーン】
■設定に手間がかかる
■場所がわかるだけなので、編集は1つずつ必要
■設定解除を忘れやすい

条件付き書式の解除を忘れずに

空白セルが真っ赤になっている間は1つずつ丁寧に処理できるのでわかりやすいです。しかしすべて入力し終わると、赤色セルが無くなります。すると条件付き書式を設定しているのを忘れてしまいます
条件付き書式はエクセルの動作が重くなる原因の一つです。作業が済んだら解除するのを忘れないようにしましょう。

あなたを待っている会社がある
|転職サイト登録なび
転職活動で成功するGmailの作り方|1人で複数アドレス作成もOK

3. VLOOKUPの「0」を非表示、他の文字へ置き換える方法

VLOOKUPで「0」になることが避けられない場合は、非表示にするか、あるいは他の文字へ置き換える方法があります。

内容解説へ
オプション設定で「0」を非表示にする3-1.
書式設定で「0」を非表示にする3-2.
条件付き書式で「0」の文字色を白色にする3-3.
「&””」と組み合わせて「0」を非表示にする3-4.
「&””」「IFERROR」「VALUE」と組み合わせて「0」を非表示にする
あるいは他の文字へ置き換える
3-5.
「IF」と組み合わせて「0」を非表示にする
他の文字へ置き換える
3-6.

3-1. オプション設定で「0」を非表示にする

エクセルVLOOKUPの使い方【エラー解決編】オプションで0を非表示にする
オプションで「0」を非表示にする(画像はクリックで拡大)

≫ 解説図のPDFファイル

エクセルのオプション設定で「0」を非表示にすることができます。シート単位で設定できます。

ただし、表示しないだけで「0」は存在します。それを忘れて計算式を組むと、結果を誤る恐れがあります。またセル単位では設定できません。

【メリット・得意なシーン】
■数式はそのままで簡単に設定できる
■シート単位で設定できる

【デメリット・苦手なシーン】
■意味のある0まで見えなくなる
■特定のセルだけ設定することができない
■設定していることを忘れやすい

3-2. 書式設定で「0」を非表示にする

エクセルVLOOKUPの使い方【エラー解決編】書式設定で0を非表示にする
書式設定で「0」を非表示にする(画像はクリックで拡大)

≫ 解説図のPDFファイル

書式設定で「0」を非表示にできます。具体的には、表示形式のユーザー定義で「0;-0;;@と設定してください。セル単位で設定できます。

ただし、書式設定の表示形式で他の設定をしている場合は、できないケースがあります。その場合は条件付き書式で設定してください。
≫ 3-3. 条件付き書式で「0」の文字色を白色にする

【メリット・得意なシーン】
■数式はそのままで簡単に設定できる
■セル単位で設定できる

【デメリット・苦手なシーン】
■意味のある「0」まで見えなくなる
■他の書式設定をしていると、できないケースがある
■設定していることを忘れやすい

3-3. 条件付き書式で「0」の文字色を白色にする

エクセルVLOOKUPの使い方【エラー解決編】条件付き書式で0を非表示にする
条件付き書式で「0」を非表示にする(画像はクリックで拡大)

≫ 解説図のPDFファイル

条件付き書式で「0」を非表示にできます。具体的には、セルが「0」の場合に文字色を白色に設定します。セル単位で設定できます。

文字の色を白色にしただけなので、0は存在します。計算結果にも0が算入されます。

条件付き書式は設定画面を開かないと、設定していることに気付きにくいです。必要なくなったら忘れず解除しておきましょう。残しておいてもエクセルの動きが重たくなるだけです。

【メリット・得意なシーン】
■数式はそのままで簡単に設定できる
■セル単位で設定できる

【デメリット・苦手なシーン】
■意味のある0まで見えなくなる
■条件付き書式の設定画面を開かないと、設定していることに気付きにくい

3-4. &”” と組み合わせて「0」を非表示にする

エクセルVLOOKUPの使い方【エラー解決編】&””と組み合わせて0を非表示にする
「&””」と組み合わせて「0」を非表示にする(画像はクリックで拡大)

≫ 解説図のPDFファイル

=VLOOKUP($H2,$A:$F,2,FALSE)&””

VLOOKUPの式に「&””」を追加すると、検索結果が「0」の場合に空白になります。

ただし、VLOOKUPの検索結果がすべて文字列になり、計算に使えなくなるので注意してください。

【メリット・得意なシーン】
■オプションや書式などの設定を変えずに「0」を非表示にできる

【デメリット・苦手なシーン】
■検索結果がすべて文字列になる(数字でも計算できなくなる)

3-5. IFERROR、VALUE、&”” と組み合わせて「0」を非表示や他の文字へ置き換える

エクセルVLOOKUPの使い方【エラー解決編】IFERROR、VALUE、&””と組み合わせて0を非表示、他の文字に置き換える
「&””」「IFERROR」「VALUE」と組み合わせて「0」を非表示や他文字へ置き換える(画像はクリックで拡大)

≫ 解説図のPDFファイル

=IFERROR(VALUE(VLOOKUP($H2,$A:$F,2,FALSE)&””),””)

前項(3-4.)の欠点を補う方法です。「&””」を追加しただけでは数字がすべて文字列に扱いになってしまいます。そこで「VALUE」と「IFERROR」をさらに追加しました。
この方法であれば空白にするだけでなく、他の文字への置き換えも可能です。

ただし、検索先のセルに文字列が入っていても、空白になってしまいます。文字列をそのまま返すことができません。

【メリット・得意なシーン】
■オプションや書式などの設定を変えずに「0」を非表示にできる
■数字であれば計算できる
■空白以外の文字列への置き換えも可能

【デメリット・苦手なシーン】
■検索先のセルに文字列が入っている場合、返すことができない(空白になる)

3-6. IF と組み合わせて「0」を非表示、他の文字に置き換える

エクセルVLOOKUPの使い方【エラー解決編】IFと組み合わせて0を非表示や他文字へ置き換える
「IF」と組み合わせて「0」を非表示や他文字へ置き換える(画像はクリックで拡大)

≫ 解説図のPDFファイル

=IF(VLOOKUP($H2,$A:$F,2,FALSE)=””,””,VLOOKUP($H2,$A:$F,2,FALSE))

「IF」と組み合わせることで、「0」を非表示にできます。空白以外の場合は、VLOOKUPの式と全く同じ結果になります。

デメリットは、1つの数式の中にVLOOKUPを2回記述しなければないことです。数式が大変長くなり、メンテナンスも難しくなります。

【メリット・得意なシーン】
■オプションや書式などの設定を変えずに「0」を非表示にできる
■数字であれば計算できる
■空白以外の文字列への置き換えも可能
■検索先のセルに文字列が入っていても返すことができる

【デメリット・苦手なシーン】
■VLOOKUPの数式を2回記述する必要があり、数式全体が長くなる

マイクロソフト公式サイトの解説を参照したい方はこちら
≫ Microsoft|Officeのサポート~ゼロ値を表示する、または非表示にする

転職して、私、成長したかな?
|転職サイト登録なび
転職活動で成功するGmailの作り方|1人で複数アドレス作成もOK

まとめ|VLOOKUPの「0」はケースに応じて対処する

VLOOKUPで「0」になる場合、次の対応方法があります。

  • 原因となる空白セルを探し出して撲滅する
  • 「0」を想定内として非表示にしたり、他の文字に置き換える

それぞれの対応方法についてメリット・デメリットも踏まえて解説しました。お急ぎの方は下記のから該当場所へ移動してください。

◆空白セルを探すには

内容解説へ
Ctrl + 矢印(↓→↑←)で空白セルの前後まで移動する2-1.
空白セルまでジャンプする2-2.
オートフィルタで空白セルのある行を抽出する2-3.
条件付き書式で空白セルに色を付ける2-4.

◆「0」を非表示にしたり、他の文字へ置き換えるには

内容解説へ
オプション設定で「0」を非表示にする3-1.
書式設定で「0」を非表示にする3-2.
条件付き書式で「0」の文字色を白色にする3-3.
「&””」と組み合わせて「0」を非表示にする3-4.
「&””」「IFERROR」「VALUE」と組み合わせて「0」を非表示にする
あるいは他の文字へ置き換える
3-5.
「IF」と組み合わせて「0」を非表示にする
他の文字へ置き換える
3-6.

また、本文中の解説図はクリックで拡大表示します。別途ページでPDFファイルも参照可能です。
≫ PDFファイル参照ページはこちら

VLOOKUP を基礎から応用まで完璧にマスターしたい方は、次の記事もあわせて参照ください。
≫ VLOOKUPの使い方|基礎から完璧マスター&便利技20選を超図解!

エクセルでお困りのことがありましたら、『エクセルの窓口』で検索してください。

エクセルの窓口検索

ブックマークお気に入りに登録していただけたら幸いです。

スキルに自信がついたら新しい世界へ飛び出そう!
転職サイト登録なび
転職サイト登録なび