Excel関数でN/Aだった時の対処法をお探しですね。
広告
ExcelでVLOOKUPを使ったら「#N/A」が出た!意味と直し方を分かりやすく解説
ExcelでVLOOKUP関数やXLOOKUP関数を使ったとき、セルに「#N/A」って表示されて「あれ?壊れた?」と焦ったことはありませんか?実はこれ、数式が壊れたわけじゃなくて、Excelが「探している値が見つからないよ」って教えてくれているサインなんです。
この記事では、#N/Aエラーがどういう意味なのか、なぜ出るのか、どうやって直せばいいのかを、初心者の方にも分かりやすく説明していきます。
#N/Aエラーって何?どういう意味?
Excelで出てくる「#N/A」は、「Not Available(利用できません)」の略で、「該当するデータが見つかりませんでした」という意味です。
特にVLOOKUP関数やMATCH関数、XLOOKUP関数みたいに、何かを検索する関数でよく見かけます。
例えば、商品コードから商品名を探す表を作っていて、入力した商品コードが一覧表にない場合、Excelは「どの商品名を表示すればいいか分からない」ので#N/Aを表示します。
ここで大事なのは、**#N/Aが出たからといって、必ずしも数式が間違っているわけじゃない**ということ。
数式自体は正しくても、
– 探している値が表の中にない
– データの形式が違う(数字と文字列とか)
– 余計なスペースが入っている
といった「データ側の問題」で出ることも多いんです。
また、グラフを作るときなんかは、わざとNA関数を使って「この値は表示しない」って指定することもあります。
つまり#N/Aは、単なる「エラー」というより、Excelからの「今の条件では答えを返せません」っていうメッセージだと思ってください。
N/Aは、足し算や引き算みたいな簡単な計算よりも、「検索して値を持ってくる」系の関数で出やすいです。
特にVLOOKUPでは、
– 探したい値が表の左端にない
– 検索する範囲がずれている
– 完全一致と近似一致の設定を間違えている
といった理由でよく表示されます。
エラーを消すことばかり考えてしまうと、データの不備を見逃してしまうかもしれません。
まずは「なぜ見つからないのか」をちゃんと確認して、原因を直してから、最後に見た目を整える、という順番で進めるのがおすすめです。
VLOOKUPで#N/Aが出る理由トップ3
1. そもそもデータがない
一番多いのは、**探している値が表の中に存在しないパターン**です。
例えば、A列に商品コード一覧があって、別のシートで同じコードを検索しているつもりでも、実際にはその商品コードが登録されていなければ#N/Aになります。
これはExcelが正しく動いている証拠で、「ないものは返せません」って教えてくれているんです。
入力し忘れや、マスターデータの更新漏れがないかチェックしてみましょう。
2. 見た目は同じでも中身が違う
次に多いのが、**見た目は同じなのに、Excel内部では別物として扱われているケース**です。
例えば:
– 「00123」という商品コードが、片方では文字列、もう片方では数値の「123」になっている
– セルの前後に半角スペースや全角スペースが入っている
– 全角と半角が混ざっている
こういう場合、人間の目には同じに見えても、Excelは「別の値」として判断します。
特にCSVファイルから取り込んだデータや、他のシステムからコピーしたデータには、こうした見えない違いが混ざりやすいので注意が必要です。
3. VLOOKUPの設定ミス
VLOOKUPならではの原因として、**検索範囲の指定ミス**もよくあります。
VLOOKUPは、指定した範囲の**一番左の列**から検索値を探します。
だから、探したいキーが範囲の左端に入っていないと、正しく検索できません。
また、最後の引数(4つ目の部分)を省略すると「近似一致」として処理されるので、意図しない結果になったり#N/Aが出たりします。
商品コードや社員番号みたいなものを検索するときは、**完全一致を意味する「FALSE」か「0」を必ず入れる**のが基本です。
—
**よくある原因まとめ:**
– 検索値が表に存在しない、登録漏れがある
– 数値と文字列の違い、全角・半角、余分なスペースで一致していない
– VLOOKUPの範囲指定、列番号、完全一致の設定が間違っている
– 参照先のシート名や範囲が変わっているのに、数式が古いまま
確認するときは、検索値をコピーして表の中で実際に検索してみたり、COUNTIF関数で「その値が何個あるか」を数えてみたり、TRIM関数で余分なスペースを削除したりすると原因が見つかりやすいです。
N/Aは見た目を隠すだけなら簡単ですが、原因を放っておくと集計結果が狂ったりレポートが正確じゃなくなったりするので、まずはデータがちゃんと一致しているかを確認しましょう。
#N/Aエラーを直す方法・回避する方法
N/Aを直す方法は、大きく分けて2つあります。
1. **原因そのものを直す方法**
2. **エラーが出たときの表示を変える方法**
原因を直す方法
まずは、**検索値と参照先のデータの形式を揃える**のが基本です。
例えば、商品コードや社員番号みたいに「先頭のゼロに意味がある」データは、数値じゃなくて文字列として統一すると安全です。
余分なスペースが疑われるときは、**TRIM関数**で前後のスペースを削除すると改善することがあります。
外部データから取り込んだ変な文字が混ざっている場合は、**CLEAN関数**も併用してみましょう。
VLOOKUPで完全一致検索をするなら、基本の書き方はこうです:
“`
=VLOOKUP(A2, 商品マスター!A:C, 3, FALSE)
“`
– A2の値を
– 商品マスターシートのA列からC列の範囲で探して
– 3列目の値を返す
– FALSE = 完全一致で検索
最後の「FALSE」を入れることで、検索値と完全に一致するものだけを探します。
これを省略すると近似一致になって、予想外の結果になるので、IDやコードを検索するときは省略しないようにしましょう。
エラー時の表示を変える方法
エラーが出たときに別の文字を表示したいなら、**IFNA関数**が便利です。
IFNAは、数式の結果が#N/Aだった場合だけ、指定した値に置き換えてくれます。
“`
=IFNA(VLOOKUP(A2, 商品マスター!A:C, 3, FALSE), “該当なし”)
“`
こうすれば、検索対象が見つからないときに#N/Aじゃなくて「該当なし」と表示できます。
VALUE!や#REF!みたいな、#N/A以外のエラーもまとめて処理したいなら**IFERROR関数**を使えますが、原因の違うエラーまで隠してしまうので、「検索で見つからなかった」だけを扱いたいときはIFNAのほうがおすすめです。
XLOOKUP関数を使う方法
新しいバージョンのExcelが使える環境なら、**XLOOKUP関数**も選択肢に入ります。
XLOOKUPは、VLOOKUPと違って「検索列が左端じゃなきゃダメ」っていう制約がありません。
しかも、見つからなかったときの表示を関数の中で指定できるので、式が読みやすくなります。
“`
=XLOOKUP(A2, 商品マスター!A:A, 商品マスター!C:C, “該当なし”)
“`
– A2の値を
– 商品マスターのA列から探して
– C列の値を返す
– 見つからなければ「該当なし」と表示
—
**使い分けのポイント:**
– #N/Aだけを置き換えたい → **IFNA関数**
– すべてのエラーをまとめて置き換えたい → **IFERROR関数**
– 見つからない場合の表示まで簡潔に書きたい → **XLOOKUP関数**
– そもそも一致しない原因を探したい → **COUNTIF関数**や検索機能で確認
ただし、エラーを置き換える数式は便利な反面、**データの不備を見えにくくする**面もあります。
例えば、本来登録されているはずの商品コードがマスターにないのに、空白表示にしてしまうと、登録漏れに気づきにくくなります。
仕事で使う表では、確認用の列を作って「該当なし」って明示したり、最終提出用のシートだけ空白表示にしたりと、**作業段階と見せ方を分ける**とミスを防ぎやすくなります。
#N/Aを非表示にする設定と注意点
N/Aを画面上で見せたくないときは、IFNAやIFERRORを使って**空白にする方法**がよく使われます。
“`
=IFNA(VLOOKUP(A2, 商品マスター!A:C, 3, FALSE), “”)
“`
こうすると、該当データがないときは何も表示されません。
見た目がスッキリするので、提出資料や印刷用の一覧では便利です。
ただし、空白にすると「本当に空欄なのか」「検索に失敗して空欄なのか」が分かりにくくなります。
作業中の管理表では「該当なし」とか「未登録」って表示したほうが、原因を追いやすくなります。
条件付き書式で目立たなくする方法
**条件付き書式**を使って、#N/Aを目立たなくする方法もあります。
例えば、エラーが出たセルの文字色を白にすれば、画面上は見えなくなります。
ただし、この方法はセルの値自体を変えるわけじゃないので、別の数式で参照すると#N/Aの影響が残ります。
集計や他のシートと連携するデータでは、見た目だけを消すんじゃなくて、IFNAなどで必要な値に置き換えるか、原因となるデータを修正するほうが安全です。
印刷用と作業用を分ける
印刷するときだけ#N/Aを見せたくない場合は、**印刷用のシートを別に作る**方法が実務的です。
– 元データのシートでは#N/Aや「該当なし」を確認できる状態にしておく
– 提出用シートではIFNAで空白にした結果だけを表示する
こうやって役割を分けると、作業する人はエラーを把握できて、見る人には不要なエラー表示を見せずに済みます。
特に複数人で更新するExcelファイルでは、エラーを完全に隠すより、どこで何が一致していないのか分かる設計にしておくことが大切です。
グラフでは#N/Aが役に立つことも
実は、#N/Aはグラフで特別な意味を持つことがあります。
折れ線グラフなんかでは、空白やゼロを入れると線がつながったり、0として表示されたりしますが、**NA関数で#N/Aを返すと、その点をグラフ上で表示しない**処理に使えることがあります。
つまり、#N/Aは常に悪いものじゃなくて、「データがないことを明示するための値」として役立つ場面もあるんです。
非表示にする前に、その#N/Aがエラーなのか、わざと入れた値なのかを確認しましょう。
まとめ:エラーを消す前に、原因を確認しよう
Excel #N/Aエラーへの対応で一番大切なのは、**表示を消すこと**と**原因を解決すること**を分けて考えることです。
VLOOKUPなどで#N/Aが出たら:
1. **まず原因を確認**
– 検索値が本当に存在するか
– データ形式は揃っているか
– 余分なスペースが入っていないか
– 検索範囲は正しいか
– 完全一致の指定はできているか
2. **そのうえで見せ方を整える**
– IFNAやXLOOKUPで「該当なし」や空白に置き換える
– 作業用と提出用でシートを分ける
この順番で対応すれば、見た目を整えながら、データの正確性も保ちやすくなります。
N/Aが出たからって焦らなくて大丈夫。
Excelが「ここ、ちょっと確認してね」って教えてくれているだけです。
落ち着いて原因を探して、適切に対応していきましょう!
広告
