#N/Aを消す！VLOOKUPのエラー非表示テクニック【Excel】

By /

#N/Aを消す！VLOOKUPのエラー非表示テクニック【Excel】 詳細解説

はじめに：VLOOKUPと切っても切れない「#N/A」エラー

Excelで最も強力で頻繁に使われる関数の一つに、VLOOKUP関数があります。大量のデータの中から、特定の条件に合致する情報を見つけ出す、まさに「表引き」の役割を担ってくれるこの関数は、多くのビジネスシーンで必須のスキルと言えるでしょう。

しかし、VLOOKUP関数を使っていると、誰もが一度は目にするであろう困った表示があります。それが「#N/A」というエラーメッセージです。

#N/Aというエラーは、「Not Available」や「No Answer」の略とも言われ、「利用できません」とか「該当する値が見つかりません」という意味を持ちます。VLOOKUP関数においては、「検索したい値を、指定した範囲の中で見つけることができませんでした」ということを示しています。

このエラーが表示されること自体は、Excelが「見つからなかったよ」と正直に教えてくれているだけであり、関数が間違っているわけではありません。しかし、作成した表やレポートにこの#N/Aエラーがたくさん表示されていると、見た目が悪く、何よりデータが見つけられなかった箇所と、これからデータを入力する必要がある箇所などが区別しにくくなり、非常に分かりづらくなってしまいます。場合によっては、エラー表示のせいで、その後の計算がおかしくなってしまうこともあります。

そのため、VLOOKUPを使ってデータを参照する際には、この#N/Aエラーを適切に処理し、非表示にしたり、別の分かりやすい表示（例：「該当なし」「未入力」「0」など）に置き換えたりするテクニックが非常に重要になります。

この記事では、VLOOKUPで発生する#N/Aエラーが発生する原因を改めて確認した上で、そのエラーを様々な方法で非表示にする、あるいは別の表示に置き換えるためのテクニックを、Excelの初心者の方でも理解できるように、一つ一つ丁寧に解説していきます。約5000語にわたる詳細な解説を通じて、あなたのExcelスキルをさらに向上させ、より見やすく、より使いやすい表を作成できるようになることを目指しましょう。

VLOOKUP関数のおさらいと「#N/A」エラーが発生する主な原因

エラー処理のテクニックに入る前に、まずはVLOOKUP関数のおさらいと、なぜ#N/Aエラーが発生するのかを具体的に見ていきましょう。

VLOOKUP関数の基本的な構文

VLOOKUP関数は、以下の4つの引数で構成されます。

=VLOOKUP(検索値, 範囲, 列番号, 検索方法)

  1. 検索値: 参照したい値。この値を基にして、範囲内を検索します。（例: 商品コード、社員番号など）
  2. 範囲: 検索値が含まれている表の範囲全体を指定します。検索値は、この範囲の一番左の列に存在している必要があります。通常、この範囲は絶対参照（$A$1:$B$10のように$を付けて固定）にすることが多いです。
  3. 列番号: 範囲の左端を1列目として数え、参照したいデータがある列が何列目にあるかを指定します。
  4. 検索方法:
    • TRUE または 1 （近似一致）：検索値と完全に一致する値が見つからない場合、検索値以下の最も大きい値が使用されます。検索対象の範囲の左端の列は昇順に並んでいる必要があります。
    • FALSE または 0 （完全一致）：検索値と完全に一致する値のみを探します。通常、VLOOKUPを使う場合はこの「完全一致」を指定することが圧倒的に多いです。検索対象の範囲の左端の列が並べ替えられている必要はありませんが、一致する値が見つからない場合は#N/Aエラーが返されます。

この記事で扱う#N/Aエラーの処理は、主に完全一致 (FALSE) を指定した場合に発生する「検索値が見つからなかった」ケースを想定しています。

#N/Aエラーが発生する主な原因

完全一致を指定した場合に#N/Aエラーが発生するのは、基本的に「検索値が指定した範囲の一番左の列に見つからなかった」からです。ただし、この「見つからなかった」の背景には、いくつかの具体的な原因が考えられます。

  1. 単純に検索値が参照範囲に存在しない:
    • これが最も基本的な原因です。例えば、商品コード「A001」を探しているのに、参照先のリストに「A001」というコードが存在しない場合です。
  2. 検索値や参照範囲に余計な空白（スペース）が含まれている:
    • 見た目には同じ文字列でも、片方に不要なスペースが入っていると、Excelは別の値と認識します。例えば、「商品A 」と「商品A」は異なります。これは手入力による間違いや、他のシステムからデータをインポートした際に発生しやすい問題です。
  3. 検索値と参照範囲の値のデータ型が一致しない:
    • これも非常に多い原因です。例えば、片方が「数値」として入力されているのに、もう片方が「文字列」として入力されている場合です。見た目は同じ「123」でも、データ型が違うと一致とみなされないことがあります。特に、CSVファイルからのインポートなどで発生しやすいです。数値が文字列として認識されている（先頭にアポストロフィが付いているなど）ケースも含まれます。
  4. 検索値の入力ミスや表記ゆれ:
    • 「株式会社A」と「(株)A」、「有限会社B」と「(有)B」のように、同じものを指していても表記が異なると一致しません。手入力による誤字脱字も当然エラーの原因になります。
  5. 参照範囲が間違っている、または途中で参照範囲が変わってしまった:
    • VLOOKUPの範囲引数で指定したセル範囲が、意図したものと異なっている場合。特に、数式をコピーした際に、範囲を絶対参照（$で固定）にしていないと、参照範囲がずれてしまい、検索値が見つからなくなることがあります。
  6. 参照先のデータが削除された:
    • VLOOKUP関数を設定した後に、参照していた元のデータ行が削除されてしまった場合も、当然検索値が見つからなくなり#N/Aエラーとなります。

これらの原因を踏まえると、エラー処理の前に、そもそもエラーが出ないようにデータを整備することが、最も根本的で理想的な解決策であるとも言えます。しかし、様々な要因でデータが見つからない状況が発生するのは避けられない場合も多く、その際に#N/Aエラーを分かりやすく処理することが求められます。

#N/Aエラーを非表示にする、または置き換えるテクニック

さて、ここからが本題です。VLOOKUPで発生した#N/Aエラーを、どうすれば見た目にも機能的にも優れた形で処理できるのか、具体的なテクニックをいくつかご紹介します。

主なテクニックは以下の通りです。

  1. IFERROR関数を使う方法: エラーであれば指定した値を表示する（Excel 2007以降）。
  2. IFNA関数を使う方法: #N/Aエラーであれば指定した値を表示する（Excel 2013以降）。
  3. ISNA関数とIF関数を組み合わせる方法: #N/Aエラーかどうかを判定し、条件分岐させる（古いバージョンでも使える）。
  4. 条件付き書式を使う方法: エラーが表示されているセルの文字色を背景色と同じにするなどで、見た目を非表示にする。
  5. VLOOKUPを使う前に元のデータを整備する方法: エラーの発生自体を極力防ぐ（根本的解決）。

これらのテクニックは、それぞれに特徴があり、使えるExcelのバージョンや、どのような表示にしたいかによって適切な方法が異なります。それぞれの方法について、具体的な数式の書き方、手順、メリット・デメリットを詳しく見ていきましょう。

テクニック1：IFERROR関数を使う方法

最も手軽で、多くのケースで推奨される方法が、IFERROR関数を使う方法です。この関数は、Excel 2007以降で利用可能です。

IFERROR関数とは？

IFERROR関数は、指定した数式を評価し、その結果が何らかのエラー（#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL! など） であった場合に、あらかじめ指定しておいた別の値を表示する関数です。エラーでなかった場合は、元の数式の結果がそのまま表示されます。

構文は非常にシンプルです。

=IFERROR(値, エラーの場合の値)

  • 値: エラーかどうかを判定したい数式やセル参照です。ここにVLOOKUP関数をそのまま入れます。
  • エラーの場合の値: 値がエラーだった場合に表示したい値を指定します。例えば、エラー表示を完全に消したい場合は、何も文字がない状態を示す空白文字列（""）を指定します。特定の文字列（例: "データなし"）や数値（例: 0）を指定することも可能です。

VLOOKUPとIFERRORを組み合わせる方法

VLOOKUP関数で発生した#N/AエラーをIFERROR関数で処理するには、VLOOKUP関数全体をIFERROR関数の値引数として囲みます。

具体的な数式は以下のようになります。

=IFERROR(VLOOKUP(検索値, 範囲, 列番号, 検索方法), エラーの場合に表示したい値)

ステップバイステップの説明

例として、商品コードを使って商品名を参照し、商品コードが見つからなかった場合は何も表示しない（空白にする）ケースを考えます。

データ例：
* 検索元リスト:
| 商品コード |
| :——— |
| A001 |
| A002 |
| A003 |
| A005 | <= 検索したい商品コード
* 参照先商品マスタ:
| 商品コード | 商品名 |
| :——— | :——- |
| A001 | りんご |
| A002 | バナナ |
| A003 | みかん |
| A004 | ぶどう | <= ここにA005はありません

  1. まず、通常通りVLOOKUP関数を作成します。
    例えば、検索元リストのB列に商品名を表示させたいとして、セルB2に商品コードA001に対する数式を作成します。検索元リストのA列に商品コードがあり、参照先商品マスタがD列からE列にあるとします。

    =VLOOKUP(A2,$D$2:$E$5,2,FALSE)

    この数式をB列にコピーすると、A001, A002, A003は正しく商品名が表示されますが、A005の行では参照先に見つからないため#N/Aエラーが表示されます。

  2. #N/Aエラーを非表示にするために、このVLOOKUP関数全体をIFERROR関数で囲みます。エラーの場合に空白を表示したいので、""を指定します。

    =IFERROR(VLOOKUP(A2,$D$2:$E$5,2,FALSE),"")

  3. この数式をB列の必要な範囲にコピーします。

    結果：
    | 商品コード | 商品名 |
    | :——— | :——- |
    | A001 | りんご |
    | A002 | バナナ |
    | A003 | みかん |
    | A005 | | <= #N/Aエラーが消えて空白になった

これで、検索値が見つからなかったセルの#N/Aエラー表示が消え、空白になります。

IFERROR関数の利点と注意点

利点:

  • 簡単: 構文がシンプルで、既存のVLOOKUP関数の前後にIFERROR(と, "")（または別の表示）を追加するだけで済みます。
  • 様々なエラーに対応: #N/Aだけでなく、#VALUE!, #REF!など、VLOOKUP関数やその計算過程で発生しうる他のエラーもまとめて処理できます。例えば、参照範囲が削除されて#REF!エラーになった場合でも、IFERRORを使っていれば指定した値（空白など）が表示されます。これは、特に複数の関数を組み合わせている複雑な数式で威力を発揮します。
  • 分かりやすい表示に置き換え可能: 空白だけでなく、「データなし」といった文字列や、数値の「0」など、エラー時に表示したい値を自由に設定できます。

注意点:

  • すべてのエラーをまとめて処理してしまう: #N/A以外のエラー（例: 引数の型が間違っていることによる#VALUE!エラー、参照先がなくなったことによる#REF!エラーなど）も同じように処理されてしまいます。本当に見つからなかったことによる#N/Aエラーだけを区別して処理したい場合は、後述のIFNA関数の方が適しています。IFERRORを使う場合、本来なら修正すべき数式や参照の問題によるエラーも非表示にしてしまう可能性があることを理解しておく必要があります。
  • Excel 2003以前のバージョンでは使えない: 古いバージョンのExcelを使用しているユーザーとファイルを共有する場合、IFERROR関数が使われているとエラーになる可能性があります。その場合は、次に説明するIF+ISNAの方法を検討する必要があります。

IFERROR関数は、手軽さから最も一般的に使われる方法ですが、「#N/Aだけを特別扱いしたい」「Excel 2003以前との互換性が必要」といった場合には、別の方法を検討する余地があります。

テクニック2：IFNA関数を使う方法

IFERROR関数が様々なエラーに対応するのに対し、IFNA関数は#N/Aエラーだけを対象に処理を行います。この関数はExcel 2013以降で利用可能です。

IFNA関数とは？

IFNA関数は、指定した数式を評価し、その結果が#N/Aエラーであった場合に限り、あらかじめ指定しておいた別の値を表示する関数です。結果が#N/Aエラーでなかった場合は、元の数式の結果がそのまま表示されます。他の種類のエラー（#VALUE!, #REF!など）が発生した場合は、IFNA関数は何も処理せず、そのエラーをそのまま返します。

構文はIFERROR関数とほぼ同じです。

=IFNA(値, #N/Aの場合の値)

  • 値: #N/Aエラーかどうかを判定したい数式やセル参照です。ここにVLOOKUP関数をそのまま入れます。
  • #N/Aの場合の値: 値が#N/Aエラーだった場合に表示したい値を指定します。IFERRORと同様、空白文字列（""）、特定の文字列、数値などを指定できます。

VLOOKUPとIFNAを組み合わせる方法

VLOOKUP関数で発生した#N/AエラーだけをIFNA関数で処理するには、IFNA関数の値引数としてVLOOKUP関数全体を囲みます。

具体的な数式は以下のようになります。

=IFNA(VLOOKUP(検索値, 範囲, 列番号, 検索方法), #N/Aの場合に表示したい値)

ステップバイステップの説明

IFERROR関数の例と同じデータを使って説明します。商品コードが見つからなかった場合に空白を表示したいケースです。

  1. 通常通りVLOOKUP関数を作成します。（IFERRORのステップ1と同じ）

    =VLOOKUP(A2,$D$2:$E$5,2,FALSE)

  2. #N/Aエラーだけを非表示にするために、このVLOOKUP関数全体をIFNA関数で囲みます。エラーの場合に空白を表示したいので、""を指定します。

    =IFNA(VLOOKUP(A2,$D$2:$E$5,2,FALSE),"")

  3. この数式を必要な範囲にコピーします。

    結果：
    | 商品コード | 商品名 |
    | :——— | :——- |
    | A001 | りんご |
    | A002 | バナナ |
    | A003 | みかん |
    | A005 | | <= #N/Aエラーが消えて空白になった

IFERRORを使った場合と同じ結果が得られます。

IFNA関数の利点と注意点

利点:

  • #N/Aエラーのみを処理: これが最大の利点です。#N/A以外のエラー（数式の間違いや参照の問題など、本来修正すべきエラー）が発生した場合、IFNA関数はそれを隠さずに表示します。これにより、数式や参照範囲に問題があることに気づきやすくなります。
  • IFERRORと同様の使いやすさ: 構文や使い方はIFERRORとほぼ同じなので、導入が容易です。
  • 分かりやすい表示に置き換え可能: IFERRORと同様に、エラー時に表示したい値を自由に設定できます。

注意点:

  • Excel 2013以前のバージョンでは使えない: Excel 2013よりも古いバージョンのExcelではIFNA関数は存在しません。古いバージョンとの互換性が必要な場合は、IFERROR（2007以降）かIF+ISNA（すべてのバージョン）を使う必要があります。

#N/Aエラーだけを特別扱いしたい、他の種類のエラーは検出したい、という場合には、IFNA関数が最適な選択肢となります。特に、複雑な数式の一部としてVLOOKUPを使っている場合や、数式のデバッグを容易にしたい場合に有効です。

テクニック3：ISNA関数とIF関数を組み合わせる方法

IFERROR関数やIFNA関数が利用できない、あるいは特定の理由で使いたくない場合（例: Excel 2003以前との互換性が必要）、ISNA関数とIF関数を組み合わせて#N/Aエラーを処理する方法があります。この方法は、IFERRORやIFNAが登場する前から使われていました。

ISNA関数とIF関数とは？

  • ISNA関数: 指定したセルや数式の結果が#N/Aエラーであるかどうかを判定する関数です。結果は論理値（TRUEまたはFALSE）で返されます。

    • 構文: =ISNA(値)
    • 値が#N/Aエラーの場合: TRUE を返す
    • 値が#N/A以外のエラーまたはエラーでない場合: FALSE を返す

  • IF関数: 条件が真（TRUE）の場合と偽（FALSE）の場合で、実行する処理や表示する値を切り替える関数です。

    • 構文: =IF(論理式, 真の場合の値, 偽の場合の値)
    • 論理式がTRUEの場合: 真の場合の値 を表示
    • 論理式がFALSEの場合: 偽の場合の値 を表示

ISNAとIFとVLOOKUPを組み合わせる方法

ISNA関数を使ってVLOOKUP関数の結果が#N/Aエラーかどうかを判定し、その結果（TRUEまたはFALSE）をIF関数の論理式とします。

  • ISNAの結果がTRUE（つまりVLOOKUPの結果が#N/Aエラー）の場合に表示したい値をIF関数の真の場合の値に指定します。
  • ISNAの結果がFALSE（つまりVLOOKUPの結果が#N/Aエラーではない）の場合に、元のVLOOKUPの結果を表示したいので、IF関数の偽の場合の値に、もう一度同じVLOOKUP関数を指定します。

具体的な数式は以下のようになります。

=IF(ISNA(VLOOKUP(検索値, 範囲, 列番号, 検索方法)), #N/Aの場合に表示したい値, VLOOKUP(検索値, 範囲, 列番号, 検索方法))

ステップバイステップの説明

IFERROR/IFNA関数の例と同じデータを使って説明します。商品コードが見つからなかった場合に空白を表示したいケースです。

  1. 通常通りVLOOKUP関数を作成します。（IFERRORのステップ1と同じ）

    =VLOOKUP(A2,$D$2:$E$5,2,FALSE)

  2. このVLOOKUP関数が#N/Aかどうかを判定するために、ISNA関数で囲みます。

    =ISNA(VLOOKUP(A2,$D$2:$E$5,2,FALSE))

    この数式の結果は、A001〜A003の行ではFALSE、A005の行ではTRUEとなります。

  3. このISNA関数の結果をIF関数の論理式とします。

    • 論理式: ISNA(VLOOKUP(A2,$D$2:$E$5,2,FALSE))
    • 真の場合の値 (ISNAがTRUE、つまり#N/Aの場合): "" (空白を表示したい)
    • 偽の場合の値 (ISNAがFALSE、つまり#N/Aではない場合): VLOOKUP(A2,$D$2:$E$5,2,FALSE) (元のVLOOKUPの結果を表示)

    これらを組み合わせて最終的な数式を作成します。

    =IF(ISNA(VLOOKUP(A2,$D$2:$E$5,2,FALSE)), "", VLOOKUP(A2,$D$2:$E$5,2,FALSE))

  4. この数式を必要な範囲にコピーします。

    結果：
    | 商品コード | 商品名 |
    | :——— | :——- |
    | A001 | りんご |
    | A002 | バナナ |
    | A003 | みかん |
    | A005 | | <= #N/Aエラーが消えて空白になった

IFERROR/IFNAを使った場合と同じ結果が得られます。

ISNAとIFを組み合わせる方法の利点と注意点

利点:

  • 高い互換性: IF関数もISNA関数も、Excelの非常に古いバージョンから存在するため、ほぼすべてのExcelバージョンで利用可能です。ファイルを様々なバージョンのユーザーと共有する必要がある場合に有効です。
  • #N/Aエラーのみを処理: ISNA関数は#N/Aエラーのみを判定するため、IFNA関数と同様に、他の種類のエラーはそのまま表示されます。これにより、数式や参照の問題に気づきやすくなります。

注意点:

  • 数式が長くなる: 同じVLOOKUP関数を数式中に二度書く必要があるため、IFERRORやIFNAを使う場合に比べて数式が長くなり、編集しにくく、視認性も悪くなります。
  • 処理効率が若干低下する可能性がある: 同じVLOOKUP関数を二度評価しているため、大量のデータに対してこの数式を使用した場合、IFERRORやIFNAを使う場合に比べて計算に時間がかかる可能性があります（ただし、一般的なデータ量であれば体感できるほどの差はほとんどないことが多いです）。

IFERRORやIFNAが使えない環境である場合や、#N/Aエラーのみを確実に区別して処理したいがIFNAが使えない、といった場合に有効な方法です。

テクニック4：条件付き書式を使う方法

これまでのテクニックは、数式の結果として表示される「値」そのものを変更する方法でした。一方、条件付き書式を使う方法は、値自体は#N/Aエラーのままにしておき、そのセルの「見た目」（書式）を変更することで、エラー表示を非表示にする方法です。

条件付き書式とは？

条件付き書式は、セルの値や数式の結果に基づいて、セルの書式（文字色、背景色、罫線など）を自動的に変更する機能です。「特定の値より大きい場合はセルを赤くする」「日付が今日の場合はセルを黄色くする」といった設定が可能です。

この機能の条件設定で「エラー」を指定し、エラーが表示されているセルの文字色を背景色と同じ色（例: 白い背景なら文字色も白）にすることで、見た目上はエラー表示が消えたように見せることができます。

エラー値に対して書式を適用する方法

例として、VLOOKUP関数を入力したセル範囲（例えばB2:B100）に対して、#N/Aエラーが表示されている場合に文字色を白にする手順を説明します。

  1. VLOOKUP関数を通常通り入力します。エラー処理は行いません。

    =VLOOKUP(A2,$D$2:$E$5,2,FALSE)

    この数式をコピーすると、エラーが発生するセルには#N/Aが表示されます。

  2. 条件付き書式を設定したいセル範囲を選択します。（例: B2:B100）

  3. Excelのリボンメニューから「ホーム」タブを選択し、「スタイル」グループにある「条件付き書式」をクリックします。

  4. ドロップダウンメニューから「新しいルール」を選択します。

  5. 「新しい書式ルール」ダイアログボックスが開きます。「ルールの種類を選択してください」の中から「数式を使用して、書式設定するセルを決定」を選択します。

  6. 「次の数式を満たす場合に値を書式設定」という入力欄が表示されます。ここに、選択したセルがエラーかどうかを判定する数式を入力します。

    • 選択範囲の先頭セル（例ではB2）がエラーかどうかを判定する数式を入力します。

    • エラーを判定する関数として、ISERROR関数（あらゆる種類のエラーを判定）またはISNA関数（#N/Aエラーのみを判定）を使用します。

    • #N/Aエラーのみを非表示にしたい場合は、ISNA関数を使います。
      =ISNA(B2) <= 選択範囲の先頭セル（B2）が#N/AならTRUEを返す

    • #N/Aを含む全てのエラーを非表示にしたい場合は、ISERROR関数を使います。
      =ISERROR(B2) <= 選択範囲の先頭セル（B2）がエラーならTRUEを返す

    • 今回はVLOOKUPの#N/Aエラーが対象なので、=ISNA(B2)を使うのがより正確ですが、通常VLOOKUPで発生するエラーは#N/Aがほとんどなので、=ISERROR(B2)でも結果は同じになることが多いです。どちらを使うかは状況に応じて判断してください。ここでは=ISNA(B2)とします。

  7. 入力欄の下にある「書式」ボタンをクリックします。

  8. 「セルの書式設定」ダイアログボックスが開きます。「フォント」タブを選択し、「色」のドロップダウンからセルの背景色と同じ色を選択します。例えば、背景が白であれば、文字色も白を選びます。

  9. 「OK」ボタンをクリックして「セルの書式設定」ダイアログボックスを閉じます。

  10. 「新しい書式ルール」ダイアログボックスに戻ります。設定した書式がプレビューに表示されていることを確認し、「OK」ボタンをクリックします。

これで、選択した範囲内で#N/Aエラーが表示されているセルの文字色が白になり、見た目にはエラーが消えたように見えます。

条件付き書式を使う方法の利点と注意点

利点:

  • 数式をシンプルに保てる: VLOOKUP関数自体にエラー処理の関数を追加する必要がないため、VLOOKUPの数式がシンプルで分かりやすくなります。
  • 値自体は残る: セルに入力されている値は#N/Aエラーのままです。これにより、後からエラーが発生している箇所を特定したり、デバッグしたりすることが容易になります。
  • 特定の書式設定が可能: 文字色を白にするだけでなく、背景色を変えたり、特定のフォントを使ったり、罫線を消したり、といった多様な書式設定でエラー箇所を目立たなくさせることが可能です。

注意点:

  • 値そのものは#N/Aエラー: セルの値はあくまで#N/Aです。このセルを他の計算（SUM関数など）で参照しようとすると、計算結果が#N/Aになってしまう可能性があります。表示を消すだけで、エラーを値として処理したわけではないことに注意が必要です。
  • 印刷時には表示される可能性がある: プリンターの設定やPDF出力の方法によっては、白い文字が薄く表示されてしまうなど、見た目上は非表示でも印刷時には見えてしまうことがあります。
  • セルの書式が適用されるだけ: 数式でエラー時に別の値を表示するのとは異なり、あくまで書式が変わるだけです。コピー＆ペーストで「値のみ」を貼り付けた場合、書式設定は引き継がれないため、貼り付け先でエラーが表示されてしまいます。

条件付き書式は、主に「見た目」の問題を解決したい場合に有効な方法です。「エラーの箇所を特定はしておきたいが、普段の表示では目立たなくしたい」といった場合に便利です。ただし、そのエラー値を他の計算に利用する可能性がある場合は、数式でエラー処理を行う方法（IFERROR, IFNA, IF+ISNA）を選択すべきです。

テクニック5：VLOOKUPを使う前に元のデータを整備する方法

ここまでご紹介したテクニックは、#N/Aエラーが「発生した後の処理」でした。しかし、そもそも#N/Aエラーの発生頻度を減らす、あるいはなくすことができれば、それが最も理想的です。VLOOKUP関数を使用する前に、検索値や参照範囲のデータを適切に整備することで、多くの#N/Aエラーを防ぐことができます。

これはエラーを「非表示」にするのではなく、エラーを「発生させない」ためのアプローチであり、根本的な解決策と言えます。

データの整備項目と方法

#N/Aエラーの主な原因を考慮し、以下の点を確認・修正します。

  1. 検索値と参照範囲の空白（スペース）の除去:

    • 原因: 「 商品A 」と「商品A」のように、不要なスペースが含まれていると一致しません。
    • 確認方法: セルを選択し、数式バーで値を確認します。値の先頭や末尾にカーソルを合わせてみて、不要なスペースがないか確認します。
    • 修正方法: TRIM関数を使います。TRIM関数は、文字列の先頭、末尾、および単語間の複数のスペースを1つのスペースに正規化する関数です。
      • 例: TRIM(A2)
      • 元の列の隣の列にTRIM関数を使った数式を入力し、それをコピーして値貼り付けすることで、空白を取り除くことができます。

  2. 検索値と参照範囲の値のデータ型の確認と統一:

    • 原因: 「123」（数値）と「123」（文字列）は一致しません。CSVインポートなどで発生しやすいです。数値に見えるのに左揃えになっている場合、文字列として認識されている可能性が高いです。
    • 確認方法:
      • セルの書式設定を確認します（ただし、表示形式を変えても値の型そのものが変わるわけではありません）。
      • ISTEXT関数やISNUMBER関数を使って、値のデータ型を判定します。
      • セルを選択し、数式バーを見ます。文字列として入力されている数値は、先頭にアポストロフィ（'）が付いていることがあります。
    • 修正方法:
      • 文字列を数値に変換:
        • 対象の列を選択し、「データ」タブ -> 「データツール」グループ -> 「区切り位置」を選択 -> 何もせず「完了」をクリック。（これで文字列として認識されていた数値が数値に変換されることがあります）
        • 空いているセルに数値の「1」を入力し、そのセルをコピーします。数値に変換したいセル範囲を選択し、「貼り付け」のドロップダウンから「形式を選択して貼り付け」を選び、「演算」で「乗算」を選択して「OK」をクリックします。（文字列としての数値に1を掛けることで、数値に変換できます）
        • VALUE関数を使う方法もあります。（例: VALUE(A2)）
      • 数値を文字列に変換:
        • 対象の列を選択し、セルの書式設定を「文字列」に変更します。その後、セルをダブルクリックしてEnterを押すか、前述の「区切り位置」機能を使うことで型が適用されることがあります。
        • TEXT関数を使う方法もあります。（例: TEXT(A2,"0") または A2&""）

  3. 検索値の入力ミスや表記ゆれの修正:

    • 原因: 「(株)」と「株式会社」、「半角カナ」と「全角カナ」など、表記が異なると一致しません。
    • 確認方法: 目視や、データの抽出・集計（ピボットテーブルなど）で重複しているように見えるが別々の項目として表示されているものを確認します。
    • 修正方法:
      • 手入力での修正。
      • SUBSTITUTE関数やREPLACE関数を使って、特定の文字列を置換します。
      • Excelの検索・置換機能を利用します。
      • PHONETIC関数を使ってフリガナを取得し、フリガナで比較・統一する方法もあります。
      • 全角・半角を統一する関数（JIS, ASC）や、大文字・小文字を統一する関数（UPPER, LOWER, PROPER）も役立ちます。

  4. 参照範囲の正確な指定と絶対参照の使用:

    • 原因: VLOOKUP関数の範囲引数が間違っている、または数式をコピーした際に範囲がずれてしまう。
    • 確認方法: 数式バーでVLOOKUP関数の範囲引数が、検索したいデータが全て含まれる範囲を正確に指定しているか確認します。
    • 修正方法: 範囲引数は、通常は絶対参照（例: $D$2:$E$10）にします。これにより、数式を他のセルにコピーしても参照範囲がずれることがありません。F4キーで簡単に絶対参照に切り替えられます。

  5. 参照先のデータが存在することを確認:

    • 原因: VLOOKUP関数を設定した後に、参照していた行が削除された場合。
    • 確認方法: 定期的に参照先のデータリストを確認します。
    • 修正方法: データは安易に削除せず、不要な行は非表示にする、または別のシートに移動するなど、参照元としては残しておくようにします。

データの整備アプローチの利点と注意点

利点:

  • 根本的な解決: エラーが発生しないようにするため、見た目の改善だけでなく、データとして信頼性が向上します。
  • 他の問題も同時に解決: データ型の不一致や空白の問題は、VLOOKUPだけでなく、COUNTIFやSUMIFなどの他の関数、さらにはピボットテーブルなどでも問題を引き起こす可能性があります。データを整備することで、これらの問題もまとめて解決できます。
  • 計算パフォーマンスの向上: エラー処理の関数（特にIF+ISNA）を追加した数式に比べて、シンプルなVLOOKUP関数の方が計算が速くなる可能性があります。

注意点:

  • 手間がかかる: データの量が多い場合や、複数のソースからデータが来る場合、データの整備にはかなりの時間と手間がかかることがあります。
  • 自動化が難しい場合も: 表記ゆれなどは、手作業で修正したり、複雑なルールを設定したりする必要があり、完全に自動化するのが難しい場合があります。
  • 常にデータが整備されているとは限らない: 新しいデータが追加されたり、他のユーザーがデータを変更したりする場合、常にデータがきれいな状態に保たれるとは限りません。このため、データ整備は一度行えば終わりではなく、継続的な作業になる場合があります。

データ整備は、可能であれば行うべき最も理想的なアプローチです。しかし、現実には常に完璧なデータを用意するのが難しい場合も多いため、データの整備とエラー処理のテクニックを組み合わせて使うのが、最も実用的と言えるでしょう。例えば、可能な限りデータを整備した上で、それでも見つからなかった場合に備えてIFERRORやIFNAでエラー表示を処理する、といった方法です。

各テクニックの比較と使い分け

ここまで5つのテクニックを見てきました。それぞれの特徴を踏まえ、どのような状況でどのテクニックを選択すべきか、まとめてみましょう。

テクニック 対応Excelバージョン 処理対象エラー 数式の簡潔さ データの値 利点 注意点 どんな時に使う？
1. IFERROR 2007以降 全てのエラー（#N/A含む） 簡単 指定値 導入が容易、多様なエラーをまとめて処理 #N/A以外のエラーも隠してしまう（問題に気づきにくい） 最も手軽にエラー表示を消したい場合。#N/A以外のエラーもまとめて処理して構わない場合。
2. IFNA 2013以降 #N/Aエラーのみ 簡単 指定値 #N/Aのみ処理するため、他のエラー原因に気づける 2013以前のバージョンでは使えない #N/Aエラーのみを区別して処理したい場合。数式のデバッグを重視する場合。
3. IF+ISNA 全バージョン #N/Aエラーのみ 長くなる 指定値 古いバージョンとの互換性が必要な場合でも使える。#N/Aのみ処理。 数式が長くなる、処理効率がやや劣る可能性 Excel 2013以前のバージョンで#N/Aエラーのみを処理したい場合。
4. 条件付き書式 全バージョン 設定による（ISNA/ISERROR） シンプル #N/A 数式を変更しない。エラー箇所は特定できる。見た目だけを非表示。 値自体はエラーのまま（他の計算に影響）。印刷で見える可能性。書式が引き継がれない場合も。 エラー値を他の計算に使わない。見た目だけを改善したい。エラー箇所は残しておきたい。
5. データ整備 全バージョン ほぼ全てのエラー VLOOKUPのみ 正しい値 根本的な解決。データの信頼性向上。他の問題も解決。計算も速い可能性。 手間がかかる。継続的な作業が必要。完全に自動化できない場合も。 可能であれば最優先で行うべきアプローチ。他の方法と組み合わせて使うことが多い。

使い分けの考え方:

  • 最も一般的で手軽な選択肢: IFERROR関数です。Excel 2007以降を使っているなら、多くの場合これで十分です。数式の簡潔さと処理対象エラーの幅広さが魅力です。
  • #N/Aエラーだけを特別扱いしたい場合: IFNA関数（Excel 2013以降）またはIF+ISNA関数（全バージョン対応）を選択します。数式や参照の問題によるエラーと、「データが見つからなかった」という意図した結果としての#N/Aを区別したい場合に適しています。新しいバージョンならIFNAがよりシンプルです。
  • 古いバージョンとの互換性が必須の場合: IF+ISNA関数が有力な選択肢となります。ただし、数式が長くなる点は覚悟が必要です。
  • 値はエラーのままで良いが、見た目だけを改善したい場合: 条件付き書式が有効です。ただし、そのエラー値が他の計算に影響しないか注意が必要です。
  • エラーの発生自体を減らしたい場合: VLOOKUP関数を使う前に、データの整備を徹底します。これは他のテクニックと併用することで、より効果を発揮します。

これらのテクニックを状況に合わせて使い分けることで、VLOOKUPを使った表をより分かりやすく、エラーに強いものにすることができます。

その他のVLOOKUP関連エラーについて

ここまで#N/Aエラーに焦点を当てて解説してきましたが、VLOOKUP関数を使っていると、他にもいくつかのエラーに遭遇することがあります。IFERROR関数はこれらのエラーもまとめて処理できますが、それぞれの意味を理解しておくことは重要です。

  • #REF!エラー:
    • 原因: 数式が参照しているセル、行、または列が削除された場合に発生します。VLOOKUPの範囲引数で指定した範囲が壊れたり、列番号で指定した列が削除されたりすると発生します。
    • 対策: 削除された参照範囲を修正するか、参照先のデータ自体を復旧させます。IFERROR関数を使っている場合はこのエラーも非表示になりますが、原因に気づきにくくなります。
  • #VALUE!エラー:
    • 原因: 数式の引数として不適切なデータ型が使用された場合に発生します。例えば、数値であるべき引数に文字列が入っている場合などです。VLOOKUPでは、列番号に数値以外の値が入っている場合などに発生することがあります。
    • 対策: 数式を確認し、引数に適切なデータ型が渡されているか確認します。
  • #NAME?エラー:
    • 原因: 数式中で使用されている関数名や定義された名前（セル範囲に付けた名前など）がExcelに認識されない場合に発生します。関数名のスペルミス（例: VLOKUPなど）や、存在しない名前を参照している場合などに発生します。
    • 対策: 関数名のスペルを確認したり、定義された名前が正しく設定されているか確認します。
  • #DIV/0!エラー:
    • 原因: 数式中で数値をゼロで割ろうとした場合に発生します。VLOOKUP関数単体でこのエラーが発生することは少ないですが、VLOOKUPの結果を使ってさらに割り算などの計算をしている場合に発生する可能性があります。
    • 対策: 割る値がゼロにならないようにデータや計算方法を見直します。

これらのエラーが発生した場合、#N/Aエラーと同様にIFERROR関数でまとめて非表示にすることが可能ですが、多くの場合、これらのエラーは数式自体や参照設定に根本的な問題があることを示しています。#N/Aエラーとは異なり、「見つからなかった」という意図した結果ではなく、「数式がおかしいですよ」という警告であることが多いです。

そのため、IFNA関数を使って#N/Aエラーだけを処理し、これらの他のエラーはそのまま表示させるようにすると、問題の早期発見に役立ちます。あるいは、IFERRORで非表示にするにしても、「データに問題があります」といった別のメッセージを表示させるようにするなどの工夫が考えられます。

まとめ：VLOOKUPのエラー処理は必須スキル

ExcelのVLOOKUP関数は非常に便利な機能ですが、データが見つからなかった場合に表示される#N/Aエラーは、多くのユーザーにとって悩みの種です。しかし、この記事で解説したようなテクニックを使えば、このエラーを適切に処理し、見やすく、使いやすい表を作成することができます。

  • 手軽さと汎用性ではIFERROR関数。
  • #N/Aのみを区別したいならIFNA関数（Excel 2013以降）。
  • 古いバージョンとの互換性が必要ならIF+ISNA関数。
  • 見た目だけを整えたいなら条件付き書式。
  • そして何より、エラーの発生自体を防ぐためのデータ整備。

これらのテクニックは、単にエラー表示を消すだけでなく、データの信頼性向上や数式の保守性向上にも繋がります。どのような状況でどのテクニックを使うべきかを理解し、自在に使い分けられるようになれば、あなたのExcelスキルは間違いなく一段レベルアップするでしょう。

今回ご紹介した方法は、VLOOKUP関数だけでなく、他の検索関数（HLOOKUP, INDEX+MATCHなど）や、エラーを返す可能性のある様々な関数にも応用できます。エラー処理は、実務でExcelを使う上で非常に重要なスキルの一つです。ぜひ、これらのテクニックを習得し、日々の業務に役立ててください。

最初は少し難しく感じるかもしれませんが、実際に手を動かして数式を入力し、結果を確認していくうちに、必ず理解が深まります。この記事が、あなたのExcel学習の一助となれば幸いです。

これで、ExcelのVLOOKUP関数で発生する「#N/A」エラーを非表示にするテクニックに関する約5000語の詳細な記事は終了です。

コメントする

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

上部へスクロール