提出リストの氏名を、名簿から探して、組を表示したい。ところが、同じ子の名前なのに、「#N/A」と出てしまった。教員の仕事では、名簿を照合するときに、この表示に困る場面があります。
「#N/A」は、探している値が見つからないというエラーです。この記事では、教員の仕事でよく出る原因を例にして、直し方を説明します。
教員の仕事で#N/Aが出る2つの場面
場面1|氏名のスペースがそろっていない
名簿には「青木 花子」(半角スペース)、提出リストには「青木 花子」(全角スペース)と入っていると、別の文字として扱われ、見つかりません。スペースをそろえると、見つかります(エクセルでスペースを削除する方法)。
場面2|検索方法の指定(FALSE)を忘れている
VLOOKUP関数の最後の指定を、FALSE(完全一致)にしていないと、名簿の並びによっては、別の子の情報が出たり、「#N/A」が出たりします。
=VLOOKUP(A2,名簿!A:B,2,FALSE)
ここからは、「#N/A」が出る原因を、順に見ていきます。
#N/Aとは|「見つからない」という意味
#N/A は、「該当する値がない(Not Available)」ことを表すエラーです。VLOOKUPでは、検索値が範囲の左端の列から見つからなかったときに表示されます。
見た目では同じに見えても、中身が違っていれば見つかりません。次の5つを順に確認してください。
原因1:数値と文字列の違い
見た目は同じ「101」でも、片方が数値、もう片方が文字列だと一致しません。セルが左寄せになっていたり、左上に緑の三角が出ていたりしたら、文字列の可能性があります。

直し方は、どちらかの種類をそろえることです。文字列を数値に直すには、VALUE関数を使うか、セルの表示形式を「標準」にして入力し直します。
=VLOOKUP(VALUE(D2),A2:B4,2,FALSE)
原因2:余分なスペースが入っている
セルの前後に、見えないスペースが入っていると一致しません。コピーしたデータや、他のシステムから取り込んだデータに多い原因です。TRIM関数で、前後のスペースを取り除いて検索します。
=VLOOKUP(TRIM(D2),A2:B4,2,FALSE)
一覧側のデータにスペースがあるときは、TRIMで整えた列を別に作って参照します。
原因3:検索方法がTRUE(近似一致)になっている
4つ目の引数を省略したり、TRUEにしたりすると、近似一致で探します。並び順によっては、正しい値が見つからずに#N/Aになります。通常の検索では、検索方法を必ず FALSE にします。
原因4:範囲の左端に検索値の列がない
VLOOKUPは、範囲の一番左の列から検索値を探します。範囲の左端が、検索値を含む列になっているか確認してください。列を挿入したあとにずれていることがあります。
原因5:コピーで範囲がずれている
数式を下の行へコピーしたとき、範囲が1行ずつずれていることがあります。範囲を「$A$2:$B$4」のように絶対参照にして、固定します。
=VLOOKUP(D2,$A$2:$B$4,2,FALSE)
| 原因 | 見分け方 | 直し方 |
|---|---|---|
| 数値と文字列の違い | 左寄せ・緑の三角が出る | VALUE関数か、表示形式を直す |
| 余分なスペース | 文字数をLENで数えると差がある | TRIM関数で取り除く |
| 近似一致になっている | 4つ目の引数がTRUEか省略 | FALSEにする |
| 左端の列がずれている | 範囲の左端に検索値の列がない | 範囲を選び直す |
| 範囲がずれている | コピー後に範囲が動いている | $で絶対参照にする |
エラーを表示させない方法
原因を直しても、一覧にない値が入力されることはあります。そのときは、IFERROR関数で表示を置き換えます。
=IFERROR(VLOOKUP(D2,$A$2:$B$4,2,FALSE),"未登録")
ただし、IFERRORはあらゆるエラーを隠します。原因を確認してから使うのがおすすめです。
もう一歩進める|原因を見つける調べ方
#N/Aの原因が分からないときは、式を一度に疑うのではなく、順番に切り分けていくのが近道です。
MATCH関数で見つかるか確かめる
VLOOKUPが探している値が、そもそも範囲の左端の列にあるのかを、MATCH関数で確認できます。
=MATCH(A2,商品表の左端の列,0)
数字(行の位置)が返れば、その値は見つかっています。#N/Aが返れば、左端の列に同じ値がありません。見た目は同じでも、スペースや全角半角の違いがないか、検索値のセルを見直しましょう。
余分な文字を取り除く
余分なスペースや、目に見えない文字が原因のときは、TRIM関数やCLEAN関数で整えてから検索します。
=VLOOKUP(TRIM(A2),商品表,2,FALSE)
見つからないセルを色で目立たせる
条件付き書式に、「=ISNA(B2)」のような数式を設定すると、#N/Aが出たセルだけに色をつけられます。大量のデータでも、どこで見つからなかったのかがひと目で分かります。
数値と文字列の違いが原因のときは、元のセルの表示形式をそろえたうえで、「データ」タブの「区切り位置」を使って一括で変換する方法も便利です。
エラーを減らす表の作り方
#N/Aは、検索する値を入力するときの表記ゆれが原因になることが多くあります。入力の段階で防ぐ工夫をしておくと、あとの手間が減ります。
- 検索値の列にプルダウンを設定して、一覧から選べるようにする
- 商品コードなどは、数値と文字列のどちらで管理するかをあらかじめ決めておく
- コピーして貼り付けるときは、「値」として貼り付けて、余分なスペースを持ち込まない
検索の元になる表のほうに、見出しや空白の行が入っていないかも、定期的に確認しておきましょう。
#N/Aが出たときの確認の順番
- 検索する値と、名簿の値が、見た目では同じでも、スペースや全角・半角で違っていないか。
- 検索の範囲が、ずれていないか。
- 最後の指定が、FALSEになっているか。


コメント