Excel の #N/A・#REF! が出たら｜最初に見る5か所と、エラーが出ないのに合計が合わないとき

月末の集計表を開いたら、#N/A や #REF! がずらりと並んでいた。前任の方が作った表で、どこから手をつければいいかわからない。そんな経験はありませんか？

この記事では、Excel のエラーが出たときに最初に確かめる5か所を、よくある原因の順に紹介します。最後に、いちばん気づきにくい「エラーは出ないのに、合計が合わない」ときの確かめ方も紹介します。

まず、エラーは「元」をたどる

集計のシートにエラーが出ていても、原因は集計のシートにあるとは限りません。

たとえば、売上の明細に #N/A が1つあるだけで、その明細を合計している月別の集計も #N/A になります。集計の式をいくら見直しても直りません。

エラーのセルを選んで、式がどのセルを見ているかをたどり、いちばん元のエラーから直すのが近道です。元のエラーを直すと、集計のエラーも連鎖して消えます。

1. #N/A：探している値が、表と「少しだけ」違う

VLOOKUP や XLOOKUP で #N/A が出るのは、探している値が表の中に見つからないときです。見た目は同じなのに見つからない場合は、次の3つを疑います。

・値の後ろに空白が入っている（「A-101 」と「A-101」は別の値として扱われます）

・数字と文字の違い（数字の 101 と、文字として入った "101" は一致しません）

・全角と半角の違い（「Ａ-101」と「A-101」）

ほかのシステムからコピーして貼り付けたデータでよく起きます。空白が原因なら、探す値を TRIM で整えてから探す式にすると防げます。

=IFNA(VLOOKUP(TRIM(B2),商品!$A$2:$C$200,3,FALSE),"未登録")

見つからなかったときに「未登録」と表示しておくと、エラーが集計まで伝わらず、どの行を確認すればいいかもすぐにわかります。

2. #VALUE!：数字の欄に文字が入っている

数量の欄に「5個」と入力すると、金額の式（数量×単価）は #VALUE! になります。空白だけが入ったセルを掛け算に使った場合も同じです。

数量や金額の欄は、数字だけを入力するように決めておきます。Excel の「データの入力規則」で、その列に整数しか入力できないように設定しておくと、入力した時点で止められます。

3. #REF!：参照していた行や列が消えた

#REF! は、式が見ていた行や列、シートが削除されたときに出ます。式の中に「#REF!」という文字がそのまま残るので、式を見ればすぐにわかります。

いったん #REF! になった式は、元に戻すことができないので、正しい範囲で作り直します。列や行を消す前に、「数式」タブの「参照先のトレース」で、そのセルを使っている式がないかを確かめておくと防げます。

4. #DIV/0!：0や空欄で割っている

達成率（売上÷目標）のような割り算で、目標がまだ入っていない月があると #DIV/0! になります。

=IF(N(C3)=0,"目標未設定",B3/C3)

のように、割る数が0か空欄のときは別の表示にしておくと、表がエラーだらけになりません。

5. 合計の範囲：あとから足した行が入っていない

最初に作った集計の式が「21行目まで」のように範囲を決めていると、あとから22行目に足したデータは合計に入りません。エラーは出ないので、気づかないまま数か月たつこともあります。

合計する範囲は、データが増えても入るように広めにとるか、表を「テーブル」に変換して自動で広がるようにします。

エラーが出ないのに合計が合わないとき

いちばん気づきにくいのが、数字が「文字」として入っているケースです。この PC の Excel で確かめると、文字として入った「100」と数字の「200」を

・SUM で合計すると 200（文字の100は無視される）

・「＋」で足すと 300

と、答えが変わりました。SUM で合計している表では、エラーも出ずに金額が少なくなります。

確かめ方は簡単です。合計している範囲を選ぶと、画面の右下（ステータスバー）に「データの個数」が表示されます。右クリックで「数値の個数」も表示させ、2つの数が違えば、数字ではない値が混ざっています。

自分で直すのが難しいときは

エラーの原因は、ここで紹介した5つでほとんど見つかります。ただ、前任の方が作った複雑な表や、シートをまたいだ式が何十個もある表では、どこが元なのかをたどるだけで時間がかかります。

ココナラで、Excel・Googleスプレッドシートの数式エラーを直すサービスを出品しています。直したセルに色を付けてお返しし、どこを・なぜ・どう直したかと、次に起きないための注意をメモでお渡しします。