【Excel関数】#N/A・#VALUE!・#REF!が出たら最初に確認

数式に #N/A・#VALUE!・#REF! などのエラー値が出ると、IFERRORで囲んで消したくなります。しかし、消してしまうと原因が分からなくなります。

この記事では、エラー値ごとに最初に確認する場所を整理します。原因はすべて自作のサンプルで再現し、結果を確かめました。検証環境はExcel 16.0（日本語環境）です。他のバージョンやMac版は未検証です。

想定読者
エラー値ごとの最初の確認ポイント
#N/A：探した値が見つからないとき
1. 末尾のスペースや見えない文字
2. 数値と文字列の違い
#VALUE!：計算に使えない値があるとき
#REF!：参照先がなくなったとき
IFERRORで消す前に：IFNAとERROR.TYPE
よくある失敗
応用例：エラーの種類を別の列に出す
まとめ

想定読者

数式のエラーの原因探しに時間がかかる方
他の人が作ったブックの数式を引き継いだ方
IFERRORで消してよいか迷っている方

エラー値ごとの最初の確認ポイント

エラー値	最初に確認する場所	確かめ方
#N/A	検索値と検索範囲の中身（空白・型）	LEN、ISNUMBER、ISTEXT
#VALUE!	計算元セルの中身	ISNUMBER、ISTEXT
#REF!	数式の中に #REF! が入っていないか	セルを選んで数式バーを見る

#N/A：探した値が見つからないとき

VLOOKUPやMATCHが検索値を見つけられないと出ます。まず、その値が検索範囲に本当にあるかを見ます。見た目が同じなのに見つからないときは、次の2つを疑います。

末尾のスペースや見えない文字

「A002」と「A002 」（末尾にスペース）は別の値です。検索値側でも、検索範囲側でも #N/A になりました。LENで文字数を数えると気づけます。

TRIMで除去できたのは、半角スペースと全角スペースでした。ウェブからコピーした値に混ざる改行なしスペース（文字コード160）は、TRIMでは除去できず、#N/A のままでした。この場合はSUBSTITUTEで取り除きます。

検索値の末尾に半角・全角・改行なしスペースが付いた場合のLENとVLOOKUPの結果の比較表。TRIMで直るのは半角と全角だけ — 再現用の最小コードの実行結果（検索値の末尾に付いた文字ごとの比較）

=VLOOKUP(SUBSTITUTE(D1,UNICHAR(160),""),A1:B3,2,FALSE)

文字コードは、UNICODE関数で確認できます。全角スペースは12288、改行なしスペースは160でした。

数値と文字列の違い

1001（数値）と「1001」（文字列）は一致しません。どちらの向きでも #N/A になりました。ISNUMBERとISTEXTで型を確認し、VALUEかTEXTでそろえます。

=VLOOKUP(VALUE(D1),A1:B2,2,FALSE)

日付が文字列になっている場合は、【Excel】日付が数字になる仕組みとVBA・PQでの扱いも参考になります。

VBAのDictionaryでマスタを引くときも、末尾のスペースや型の違いで見つからなくなります。VBA側の確認は、【VBA】マスタ参照の存在チェックをDictionaryに置き換えるにまとめています。

#VALUE!：計算に使えない値があるとき

足し算の相手に文字列があると出ます。

空欄は0として計算され、エラーになりません
「未入力」のような文字列は #VALUE! になります
="" が返す空文字も #VALUE! になります
「50円」のように、数字以外が混ざった文字列も #VALUE! になります

意外なのは、数字に見える文字列です。文字列の「50」は、ISNUMBERがFALSEでも、足し算はできました。末尾にスペースがある「50 」、全角の「５０」、カンマ付きの「1,000」も計算できました。一方、改行なしスペースが付いた「50」は #VALUE! です。TRIMでは直らず、SUBSTITUTEで取り除くと計算できました。

また、SUMは文字列を無視します。足し算が #VALUE! になる範囲でも、SUMはエラーにならず、文字列の分だけ少ない合計を返します。気づかないまま集計が合わなくなるので注意が必要です。

関数に渡す値の型が原因で #VALUE! になる例は、【Excel関数】TIMEVALUEが24時間以上で誤る原因と対処に書いています。

文字列が入った金額を五捨六入の式に渡したときの#VALUE!は、【Excel関数】五捨六入の式｜0円ガードの落とし穴で実測しています。

#REF!：参照先がなくなったとき

参照先のシート・列・行を削除すると出ます。数式を見ると、参照が #REF! に置き換わっています。

シートを削除すると、=#REF!A1 のような数式になります
列や行を削除すると、数式が =#REF! になります
範囲の一部だけを削除した場合は、範囲が縮むだけで #REF! になりません（SUM(B3:C3) が SUM(B3:B3) になりました）
範囲の全体を削除すると、SUM(#REF!) になります
切り取って貼り付けた場合は、参照先が追従しました（=B1 が =F1 に変わりました）

関数の引数でも出ます。VLOOKUPの列番号が範囲の列数より大きいときと、INDEXの行・列が範囲の外のときです。なお、VLOOKUPの列番号を0にすると #VALUE! でした。

IFERRORで囲んでいると、シートを削除しても空文字になるだけで、壊れたことに気づけません。

名前の参照先が削除されて#REF!になっている場合は、引き継いだブックの名前の管理で見つかることがあります。名前・外部リンク・クエリ接続をまとめて確認する手順は、【Excel】引き継ぎブックで先に見る外部リンク・名前・接続で整理しています。

IFERRORで消す前に：IFNAとERROR.TYPE

IFERRORは、どのエラー値も同じ代替値に置き換えます。IFNAは #N/A だけを置き換え、#REF!・#VALUE!・#DIV/0! はそのまま表示します。

=IFNA(VLOOKUP(A2,B:C,2,FALSE),"未登録")

見つからないことが正常なケースなら、IFNAで足ります。

適用開始日つきのマスタで基準日より前の行しかないときに、式ごとに返るエラー値が変わる例は、【Excel】適用開始日つきマスタから基準日時点の最新行を引く式で実測しました。

XLOOKUPなら、4つ目の引数に「見つからない場合」の値を指定できます。Microsoft 365で試すと、見つからないときは4つ目の引数の値が返り、4つ目を省くと #N/A になりました。XLOOKUPはExcel 2021とMicrosoft 365で使えます（2016・2019では使えません）。

エラー値の種類は、ERROR.TYPEで番号にできます。#VALUE! は3、#REF! は4、#N/A は7でした（一覧はMicrosoft Support）。

ISNAは #REF! に対してFALSEでした。ISERRは #N/A に対してFALSE、#REF! に対してTRUEでした。ISERRORは #N/A でもTRUEです。

IFERRORが複数のエラーを区別しない点は、【Excel関数】OR・ANDは短絡評価されない原因と対処でも触れています。空文字にした行が平均に入らない例は、【Excel関数】率の平均はAVERAGEでなくSUM/SUMに書いています。