Excelを使っているときに、「#N/A！」エラーが出てきて戸惑ったことはないでしょうか？
N/Aエラーとは、「参照したデータが見つからないとき」に出るエラーで、Excelでは非常によく発生するエラーの一つです。
原因はシンプルな入力ミスから、検索関数の設定ミスまでさまざま。放置すると正しい集計や分析ができなくなり、実務で困ることも少なくありません。
ただ原因の探し方であったり、どう対応したらいいの？と迷われる方もいらっしゃると思います。
私自身、Excel初心者のときは
N/Aエラーって何だ？？どうしたらいいの？？？
と、エラーが出てきて悩んだとを思い出します。
エラーが出ると、不安になりますよね。しかも原因がよく分からないし、どうしたらいいんだろう？と悩んだ経験を数多くしてきました。
本記事では、初心者向けの基本対処法と、実務者向けの応用対処法をまとめて解説します。
同じような境遇の方もいらっしゃると思いますので、少しでも参考になれば幸いです。
- #N/A!エラーが発生する原因
- #N/A!エラーの基本的な対処法
- 実務で役立つエラー対策
#N/A!エラーとは？
「#N/A!」エラーとは、Excelでよく発生するエラーの一つで、「該当なし」（Not Available）を意味します。つまり、検索してもデータが存在しないときに発生するエラーになります。
ポイントは数式が壊れたとかではなく、「一致するデータがないですよ」と教えてくれているということです。
N/Aエラーのよくある原因
では、どういうときにN/Aエラーが発生するのでしょうか。
ここでは、N/Aエラーが発生する主な原因を紹介していきます。
検索値が存在しない
N/Aエラーが発生する代表的な理由が、検索値が存在しないことです。
例えば下図のように、vlookup関数で社員番号から部署を抽出したい場合、検索する社員番号が存在しないと、当然検索しても出てこないですよね。この場合は該当無しということでN/Aエラーとなります。
データの不一致
同じように見えていても、実はデータの種類が異なることによりエラーが発生することがあります。
よくあるのが、文字列と数値の相違です。
下図では分かりやすいよう色をつけていますが、社員番号「2001」が文字列となっています。この場合、社員番号「2001」を数値で検索しても該当無しと判定されてしまうため、エラーとなってしまいます。
また、「〇」と「◯」のような、見た目は同じように見えるけど、実は違う記号ということで、不一致エラーが起きることもあります。
参照範囲がズレている
数式での参照範囲がズレてしまうことで、エラーが発生することもあります。検索値がないフィールドを参照範囲としてしまっている場合ですね。
下図のように、vlookupで指定する検査値はB列にあるのに、A列を指定しまっているなどのケースです。定期的に更新するもので、列の追加削除変更があった場合などに発生しやすいです。
余分な空白がある
これもありがちな要素です。
入力値に余分な空白があることで、不一致エラーが発生します。手入力のデータや、テキストデータの一部をコピペしたときによく発生します。
また下図のように、人の名前にある半角/全角スペースの相違によるエラーもよく発生します。
エラー発生防止のポイント
ここまでは、N/Aエラーが発生するよくある原因を紹介してきました。
では、このN/Aエラーが起きないようにするにはどうしたらいいか、エラー発生の防止ポイントを説明していきます。
マスタ管理を徹底する
【該当する原因】：検索値が存在しない
基本的なことになりますが、参照元となるマスタをしっかり管理することで、Excelエラーを回避することができます。特に複数人で更新することが多い場合、ヒューマンエラーが発生する可能性も比較的高くなってしまいます。
「○/〇」のように入力する値が微妙に異なっていたり、入力不要な部分を誤って更新してしまったりすることありますよね。そのため、ヒューマンエラーが起きない仕組みづくりをすることが大事です。
その仕組みづくりには「データの入力規則」や「シートの保護」が役に立ちます。
データをきれいに整える
【該当する要因】：データの不一致、余分な空白
Excelに限らず、データをきれいに整えることは非常に重要となります。データクレンジングとも言ったりしますね。データがきれいに整っていないと、データ突合がうまくいかず、N/Aエラーが発生する原因となってしまいます。
主なものでいうと、「文字列/数値」の違い、「全角/半角」の違い、「余分な空白が入っている」などが原因として考えられます。
データを整えるには、「セルの値を文字列/数値いずれかに統一」することや、「余分な空白を削除」する、あるいは「相違しているワードを一括で修正」する必要があります。
こちらもどのようにしたらよいか、具体策を後述しています。
参照範囲を固定する
【該当する要因】：参照範囲がずれている
数式をコピーすると、参照範囲がずれてしまうことがあります。
これは相対参照といい、数式をコピーしたときに参照範囲も一緒にズレる仕組みです。しかし状況によっては、範囲を固定して数式をコピーしたい時も多いですよね。
そんなときは、絶対参照を使用することで、参照範囲を固定することができます。絶対参照にすることにより、数式をコピーしたとしても、参照範囲がずれることを防ぐことができます。
絶対参照・相対参照に関しては、こちらの記事で詳しく解説しています。
ぜひチェックしてみてください。
実務者が知っておくと便利な対処方法
ここまでは、エラー発生の原因と防止ポイントを説明してきましたが、「では具体的にどうしたらよいか？」と思われた方も多いと思います。
ここからは、エラー発生を防止する、もしくはエラー発生時の対応方法を説明していきます。図解で分かりやすく解説していきますので、エラーが発生して困っている場合、ぜひ活用してみてください。
入力できるワードを制限する（データの入力規則）
「データの入力規則」を活用することで、想定外の入力を防ぐことができます。
あらかじめ用意したリストから選択したり、入力ルールから外れるとエラーメッセージが出るようにしていると、ミスは減りそうですよね。エラー対策には重要な点です。
データの入力規則は、「データ」タブより選択できます。
データの入力規則では、「すべての値」以外で以下7種類から選択することができます。
| 入力値の種類 | 説明 |
|---|---|
| 整数 | 指定した範囲の小数点なしの整数を入力できる（0やマイナス値も可能） |
| 小数点数 | 指定した範囲の小数点を含んだ数を入力できる（整数も入力可能） |
| リスト | 指定した文字列をプルダウンから選択できる（文字列の指定は、セル参照でも可能） |
| 日付 | 指定した範囲の日付を入力できる |
| 時刻 | 指定した範囲の時刻を入力できる |
| 文字列（長さ指定） | 指定した文字数を入力できる |
| ユーザー設定 | ユーザーが設定した条件で入力規則をかけることができる |
例えば、「整数」で範囲を「1～50」までで設定した場合、51を入力するとエラーメッセージが表示され、入力ができないようになっています。
このように入力制限を書けることができるので、入力ミスを減らすことができます。
必要な部分以外は入力できないようにする（シートの保護）
「シートの保護」を使用することで、対象範囲を編集できないようにすることができます。
編集できなければ、誤って入力することはないですよね。こちらも重要な点です。
シートの保護の基本的な設定方法は、以下のとおりとなります。
１．選択できるようにしたい範囲を選択し、右クリックから「セルの書式設定」を選択
→「保護」タブにある「ロック」のチェックを外し、「OK」を選択
２．「校閲」タブより「シートの保護」を選択
→「ロックされたセル範囲の選択」のチェックを外し、「OK」を選択
これで、選択した部分のみ編集できるようになりました。
マスタ管理は重要なので、不用意に編集してしまわないよう、必要な部分以外は保護をかけてしまうことをおすすめします。
シートの保護を活用する内容については、以下の記事でも解説しています。
こちらもぜひチェックしてみてください。
セルの値を文字列/数値いずれかに統一する（CONCAT/VALUE関数）
数値を扱う場合は、文字列/数値いずれかに統一することで、N/Aエラーを防ぐことができます。
・文字列化した数を数値化する場合 → VALUE関数
・数値を文字列化する場合 → CONCAT関数
を使用すると、文字列/数値に寄せることができます。それぞれ説明していきます。
■CONCAT関数
CONCAT関数は、複数の範囲や文字列を結合する数式です。
書式は以下のとおりです。
=CONCAT(テキスト1,テキスト2...)CONCAT関数を使うことで、数値から文字列に変わっているのが確認できます。SUM関数では文字列の計算はできないため、合計が0になっています。
■VALUE関数
VALUE関数は、数値を表す文字列を数値に変換する数式です。
書式はシンプルで、対象文字列を指定するだけです。
=VALUE(文字列)VALUE関数を使うことで、文字列から数値に変わっているのが確認できます。
合計もちゃんと計算されています。
余分な空白を削除する（TRIM関数）
例えば他のデータからコピペしたり、コメントなど長い文字列から選択してコピーしたりすると、意図せず空白（スペース）が入ってしまうことがあります。
そんなときは、「TRIM」関数が便利です。
TRIM関数は、余分がスペースをすべて削除する数式です。
書式はシンプルで、対象文字列を指定するだけです。
=TRIM(文字列)「じゃあどういう場合のスペースを削除するの？」と疑問を持たれた方もいらっしゃると思いますので、パターン別のTRIM関数結果をまとめてみました。
基本的には、文字と文字の間にある1つ分のスペース以外は削除されるようです。（3つ以上のスペースで試しても結果は一緒でした）
文字列の表記を合わせるには便利な関数ですので、余分なスペースが入って困っている場合はぜひ活用してみてください。
相違しているワードを一括で修正する（置換/SUBSTITUTE関数）
■置換で一括変換する
「置換」機能を使うことで、対象文字列を一気に変更することができます。
数式を使う必要もなくて簡単なので、データを整える作業においては大活躍する機能です。私もよく使っています。置換方法は以下のとおりです。
1.置換したい範囲を選択し、「Ctrl + H」キーを押す
2.「検索文字列」と「置換文字列」を入力し、「置換」を実行
これで検索した文字列を一気に変換することができます。
■SUBSTITUTE関数で一括変換する
基本的には「置換」機能でデータを整えるほうが簡単で早いと思います。
しかし、指定した文字列の2番目だけ変換したい、といった細かい変換をしたいときもあると思います。置換だと、全部一気に変換されてしまいますからね。
そんなときは、「SUBSTITUTE」関数を活用することをおすすめします。
SUBSTITUTE関数は、指定するセルの中にある特定の文字列を、別の文字列に置換する数式です。
書式は以下の通りです。
=SUBSTITUTE(文字列,検索文字列,置換文字列,[置換対象])それぞれの引数と説明は以下の通りです。
ここでのポイントは、「置換対象」を設定することです。
| 項目 | 説明 |
|---|---|
| 文字列 | 文字を置き換えるテキストを含むセルを指定 |
| 検索文字列 | 置換する文字列を指定 |
| 置換文字列 | 検索した文字列を置き換える文字列を指定 |
| 置換対象 ※省略可 | どの文字列を置換するか指定 （省略した場合、すべての文字列が置換される） |
※具体例をここで出す（考え中）電話番号の1つ目0を消す
どういうことかというと、例えば国際通話時の番号を表記する場合、
・先頭に+81をつける
・市外局番の前にある0を省略
する必要がありますよね。
この場合に0を無しに置換すると、他にも0がある場合はそちらも省略されてしまいますので、困ってしまいます。
その場合、SUBSTITUTE関数で最初の0を指定することで、市外局番の前にある0のみを省略できます。
置換では対応が難しい場合は、この関数を活用してみてください。
エラーを分かりやすい表記に置き換える（IFERROR関数）
根本的な解決策ではないですが、一時的にエラー値の表記を「不一致」など分かりやすい文言に変えることもできます。
その場合は、「IFERROR」関数を使用します。書式は以下の通りです。
=IFERROR(値,エラーの場合の値)※IFERROR関数については、「REFエラー」記事でも解説しています。
ぜひこちらの記事もチェックしてみてください。
まとめ：エラーは起きるもの。ただし原因を突き止めれば必ず解決できる！
エラーが発生した場合、まずはなぜエラーが発生しているかを確認し、その原因を突き止めることが重要です。エラー値が出た場合は焦らず、まずはこの記事を参照して、原因を解消しましょう。
またエラーを発生させないための対策も重要です。しっかりデータ管理を行うことで、エラーが起きにくい環境を作っていきましょう。
この記事が役に立ったと思った場合、ブックマークやシェアしてもらえると嬉しいです。また、質問やこんな方法を紹介してほしい！などありましたら、お気軽にコメントしてください。
最後まで読んでいただき、ありがとうございました。
コメント