VLOOKUPなどのExcel数式で#N/Aエラーを解決する方法
Microsoft Excel では、データの入力時や数式の実行時に、特に要求されている内容を Excel が理解できない場合に、奇妙なエラーが発生することがあります。#N/A エラーは、VLOOKUP、HLOOKUP、MATCH などの関数で参照を行うときによく発生します。通常、参照しているデータが正しくないか、データが存在しないときに表示されます。このエラーはデータ分析やレポートに支障をきたすため、非常にイライラすることがあります。また、エラー メッセージではなく、単に空白を表示したい場合もあります。#N/A エラーが表示される場合は、スペルミス、間違ったデータ型の使用、または参照値がテーブルに存在しない可能性があります。少し煩わしいエラーですが、エラーの原因をトラブルシューティングしたい場合でも、エラーを非表示にしたい場合でも、目的に応じて適切に修正する方法があります。
問題は、これらのエラーは、余分なスペースやタイプミスなど、注意深く見過ごしてしまうような小さなミスが原因で発生する可能性があることです。Excelは、書式設定の問題や、誤ってデータ範囲外のセルを参照してしまったことなどにより、必要な情報を見つけられない場合があります。これはよくあるシナリオであり、何が問題なのかを把握すれば、トラブルシューティングはそれほど難しくありません。そこで、入力内容の確認、よくある問題の修正、そしてシートを台無しにすることなくエラーをきちんと捕捉する方法など、修正方法の概要をご紹介します。
Excelで#N/Aエラーを修正する方法
修正1: 参照値とテーブル範囲を再確認する
#N/A エラーが発生した場合、まず最初に、ルックアップ値がテーブル内の値と完全に一致していることを確認してください。多くの場合、これはごく小さなタイプミスやスペースが原因です。Excel はこうしたミスを非常に厳しく検出します。データがクリーンであることを確認するには、ルックアップ列でTRIM関数を使用して、先頭または末尾のスペースを削除してみてください。例えば、ルックアップリストが列 A にある場合は、新しいヘルパー列を作成し、=TRIM(A2)そのクリーンアップされたリストを VLOOKUP で参照します。また、table_arrayとcol_index_num をチェックして、正しい範囲を指していることを確認してください。範囲を のように指定したA2:B7のに実際にはデータが含まれていA2:B8たり、範囲をドル記号で固定し忘れていたりすることがあります。これらの問題を見つけるのは簡単ではありませんが、後で頭を悩ませる手間を大幅に省くことができます。
個人的には、検索値が正しいように見えても#N/Aが返される場合は、データ型の不一致が原因かもしれません。数値とテキストの不一致はよくあるケースです。検索値がテキストとして書式設定されているのに、テーブルの列に数値が含まれている、あるいはその逆のケースがないか確認してください。セルをクリックしてリボンの書式設定を確認することで確認できます。場合によっては、テキストを数値（またはその逆）に変換するか、VALUE関数を使用してデータ型を揃える必要があるかもしれません。
修正2: IFERRORまたはIFNAでエラートラップを使用する
ちょっとした裏技があります。ソースデータを修正するだけでは不十分だったり、見苦しい#N/Aエラーにうんざりしていたりする場合は、これらのエラーを綺麗に隠すことができます。IFERROR関数はまさにうってつけです。数式内のエラーを検出し、代わりに空白、カスタムメッセージ、デフォルト値などを表示するようExcelに指示してくれます。例えば、VLOOKUP関数を次のように囲みます。
=IFERROR(VLOOKUP(E4, B2:C7, 2, FALSE), "Not found")Excelが一致するものが見つからない場合、#N/Aではなく「見つかりません」と表示されます。これは、ダッシュボードやレポートで見苦しいエラーメッセージを表示したくない場合に非常に便利です。IFNA関数も同様に機能しますが、#N/Aエラーのみを捕捉するため、「見つかりません」と他の数式エラーを区別したい場合に適しています。ただし、これらの関数を使用するとシートが多少はきれいになりますが、根本的な原因は解決されないため、データも確認することをお勧めします。
修正3: ISERROR/IFの組み合わせを使用してすべてのエラーをキャッチする
もっと細かく制御したい場合は、ISERRORとIFを組み合わせることで、エラー処理をさらにカスタマイズできます。例えば、次のようになります。
=IF(ISERROR(VLOOKUP(E4, B2:C7, 2, FALSE)), "Nope", VLOOKUP(E4, B2:C7, 2, FALSE))基本的に、エラーが発生したかどうかを確認し、カスタムメッセージを表示します。ここではほぼ何でもできます。おまけに、#N/Aだけでなく、すべてのエラータイプで動作します。理由はよく分かりませんが、特に複雑な数式では、ネストされたIFERRORよりも信頼性が高いと感じることがあります。
修正4: データ型が一貫していることを確認する
これは#N/Aの最も分かりにくい原因の一つです。Excelは、検索値がテキストとして保存された数値か、実際に数値であるかを厳密に判断します。確認するには、セルを選択して書式を確認してください。不一致を示す小さな緑色の三角形のアイコンが表示される場合があります。「テキストを数値に変換」またはを使用して、テキストを数値に変換し、=VALUE()データが揃っていることを確認してください。不一致の場合、VLOOKUPなどの関数は一致する値を見つけられず、#N/Aが表示されます。
一部の設定では、セルが同一に見えるため、一致させようとしない限り、すぐには分かりません。データ型を修正することで、回避可能なエラーを多数解決できる場合が多くあります。
マクロの#N/Aエラーを修正する
マクロを実行中に#N/Aが表示される場合は、VBAコードが存在しないデータを参照しているか、検索範囲が正しくないことが原因と考えられます。Rangeオブジェクトを確認し、ワークシート名が正しいことを確認し、検索関数（ など）に渡されるパラメータを確認してください。マクロ内にチェックを追加して、検索を実行する前にセルまたは範囲が空でないことを確認すると、多くの手間を省くことができます。WorksheetFunction. VLookup
#N/A エラーは修正されましたが、まだ修正されていないものもあります
全体的に見て、#N/A エラーの修正はそれほど難しいことではありませんが、細部にまで注意を払う必要があります。データのクリーニング、範囲の正確性の確認、IFERRORやIFNAなどの関数を使ったエラーの検出は、大きな効果をもたらします。エラーの原因が書式の不一致やタイプミスにある場合もあり、それらの修正が鍵となります。また、データが全く存在しない場合もあり、その場合はデータを追加するか、数式を調整するかを判断する必要があります。
まとめ
- ルックアップ値とテーブル範囲を再確認し、タイプミスに注意してください
- TRIMを使ってスペースを整理する
- データ型が一貫していることを確認してください（数値とテキスト）
- エラーを非表示にするには、数式をIFERRORまたはIFNAで囲みます。
- 特にマクロでは、範囲と参照を確認してください。
まとめ
#N/A エラーの修正は、多くの場合、タイプミスやスペースなどの小さなミスを検知し、エラー処理関数を使って数式をよりスマートにすることに尽きます。必要なのは、少しの忍耐と、入念なデータクリーニングだけです。この方法で、このエラーのトラブルシューティングに取り組んでいる人の時間を数時間短縮できれば幸いです。ただし、データの品質が最優先であることを覚えておいてください。すべてが整えば、これらのエラーは通常消えてしまいます。
関連記事
この記事は役に立ちましたか？