Excelのエラー表示の意味

セルに出た表示の頭の数文字を見れば、エラーの種類はほぼ絞れます。#N/A は「探したものが見つからない」、#REF! は「参照先が消えた」、#DIV/0! は「0で割った」。種類が分かれば、次に疑う場所も決まります。

引き継いだ表にエラーが並ぶと、IFERROR で包んで消したくなります。ただ、消してよいエラーと、消すと合計が合わなくなるエラーがあります。ここでは、10種類の表示の意味と直し方を表にしたうえで、空白にしてよい場面とよくない場面を分けます。

先頭の記号で、10種類を見分ける

まず、早見表で種類を見分けます。「最初に疑うこと」の列を見れば、どこから調べるかが決まります。

#スピル! は、英語の画面では #SPILL! です。Microsoft の日本語のページでは「#スピル!」と書かれています。この表は図にもまとめました。

直す前に、写しを取って記録を残す

引き継いだ表は、作った人にしか分からない決め事が数式に入っています。いきなり直すと、元の状態に戻せなくなることがあります。

• ファイルを別名で保存し、写しを作ってから直す(「請求一覧_直す前」など)。
• 直したセルと、直し方を一覧にしておく。シートの端に「セル・直す前の数式・直した後の数式・日付」を書く程度で足ります。

#N/A・#VALUE!・#REF! は、事務の表でいちばん出会う

#N/A 探したものが、一覧にない

Microsoft のページでは、#N/A は数式が検索対象を見つけられないことを示す、と説明しています。経理や営業事務の表では、請求一覧の商品コードを単価表から引く VLOOKUP で出ます。

原因は、次の4つがほとんどです。

四つ目は見落としやすい点です。Microsoft のページによると、並べ替えていない一覧を TRUE(近似一致)で探すと、#N/A のほかに、誤った結果も返ることがあります。エラーが出ずに別の商品の単価が入っているほうが、気づきにくく、危険です。

なお、#N/A はグラフに描かれません。データのない月を0にして線を落とさないよう、あえて #N/A を入れる使い方もあります。引き継いだ表で NA 関数を見つけても、すぐには消さないでください。

#VALUE! 数値のつもりが、文字列

Microsoft のページは、#VALUE! を「入力した数式に問題があります。または、参照しているセルに問題があります」という意味、と説明しています。原因が幅広く、関数ごとの説明ページがあります。事務の表では、次の3つで多くが片づきます。

• 数値が文字列になっている: システムから出力した CSV を開いたときに多い。文字化けや先頭の0が消える問題とあわせて、CSVをExcelで開くと文字化け・0が消えるときの直し方 取り込みと保存の手順で説明しています。
• 日付が文字列になっている: 左揃えで表示されているのが目印。先頭にスペースが入っていることもある。
• 見えないスペースが入っている: [ホーム] の [検索と選択] から [置換] を開き、スペース1つを空に置き換える。

文字列かどうかは、=ISTEXT(E2) で調べられます。TRUE なら文字列です。

また、+ や * で足し算や掛け算をしていると、文字列が1つ混ざるだけで #VALUE! になります。Microsoft のページには、=A2+B2+C2 を =SUM(A2:C2) にすると、文字列を無視して計算できる場合がある、と書かれています。ただし、これは文字列が混ざった異常を見えなくする直し方でもあります。文字列になっている理由は、別に確かめます。

#REF! 参照先を、消してしまった

#REF! は、数式が無効なセルを参照しているときのエラーです。参照しているセルを削除したり、その上に貼り付けたりすると出ます。

• 数式 =SUM(B2,C2,D2) のように、セルを1つずつ並べていると、C列を消した時点で =SUM(B2,#REF!,D2) になります。
• VLOOKUP の列番号が、検索範囲の列数を超えていても出ます。4列の範囲に「5」と指定した場合です。
• INDEX の行や列の番号が範囲を超えた場合、INDIRECT で閉じたブックを参照した場合も出ます。

直し方は、消した直後なら Ctrl+Z で元に戻すのが最も確実です。戻せないときは、数式の #REF! の部分を直します。今後のためには、=SUM(B2:D2) のように範囲で指定しておくと、範囲の途中の列を消しても、Excel が数式を自動で調整します。

0で割る・綴りの誤り・極端な数 4つの小さなエラー

#DIV/0! 割る数が0か空白

達成率のように、=B2/C2 で目標のセルが空のとき、Excel は #DIV/0! を表示します。まだ入力していない行にも出るので、新しい月の欄を作った直後の表では、ずらりと並びます。

直し方は、割る数を確認して入力することです。入力待ちの行だけ見せたくないなら、IF 関数で割る数を先に調べます。

=IF(C2=0,"",B2/C2)

割る数が0か空白のときは空白を表示し、それ以外は計算します。Microsoft のページにも、同じ形の例が載っています。

#NAME? Excel が知らない名前

関数名の打ち間違いが典型です。=SUMM(A1:A5) のように綴りを間違えると出ます。ほかの原因は次のとおりです。

• 定義していない名前、または綴りの違う名前を使っている
• 数式の中の文字列を、二重引用符(")で囲んでいない
• 範囲を指定するコロン(:)が抜けている
• 必要なアドインが無効になっている

使えない関数が入っている場合も、同じ見方で調べます。たとえば XLOOKUP は、Microsoft のページで Excel 2016 と 2019 では使えないとされています。自分の版で使えない関数が、引き継いだ表に入っていないかも確かめます。

#NUM! 数値として成り立たない

数式の中に、1,000円のような通貨記号やカンマ付きの数を打つと出ます。数式の中では、カンマが引数の区切りに、ドル記号が絶対参照になるためです。数式には 1000 と書式なしで入れ、見た目はセルの書式設定で整えます。

そのほか、IRR や RATE のように繰り返し計算で答えを出す関数が、答えにたどり着けないときと、結果が Excel の扱える範囲を超えたときにも出ます。

#NULL! 範囲の間に、スペースがある

範囲の間にスペースを入れると、2つの範囲が重なる部分を指す指定になります。重なる部分がないと #NULL! です。=SUM(C2:C3 E4:E6) は、範囲が交差しないのでエラーになります。足したいのなら、スペースをカンマに替えて =SUM(C2:C3,E4:E6) とします。連続した範囲はコロンで書きます。

Microsoft 365 の新しい表示 #スピル!・#CALC! と

#スピル! 結果を広げる先が、ふさがっている

スピルとは、1つの数式の結果が、下や右のセルへ自動的に広がる動きです。FILTER や UNIQUE のような関数で起きます。広がる先に何か入っていると、#スピル! になります。

直し方は、数式のセルを選ぶと見える破線の枠の中を調べることです。エラーチェックのボタンから [障害物セルの選択] を選ぶと、邪魔なセルに飛べます。そのセルの値を消すか、別の場所に移せば、数式は自動で広がります。

ほかの原因は次のとおりです。

• 結果の大きさが決まらない(乱数を作る関数で大きさを指定している)
• 列全体を指定した数式が、シートの端を超える
• Excel のテーブルの中に、スピルする数式を入れている
• 結合したセルにスピルしようとしている

#CALC! Excel が計算できない形

FILTER の条件に合うデータが1件もなく、結果が空になったときが、事務の表でありそうな例です。Microsoft のページでは、=FILTER(C3:D5,D3:D5<100,0) のように、見つからないときの値(if_empty)を指定する例を挙げています。LAMBDA を、値を渡さずにセルへ入れた場合などにも出ます。

#CALC! の適用先に載っているのは、Microsoft 365、2024、2021などです。古い版で作られた表で見かけないのは、そのためです。

##### 列幅が足りない

これは数式のエラーではなく、セルの中身を表示しきれないという意味です。列の境目をダブルクリックして、幅を広げます。

日付や時刻がマイナスのときも ##### になります。列を広げても消えないなら、日付の引き算の順序を確かめます。

原因は、3つの道具で順に絞る

種類が分かっても、数式が長いと、どこが悪いのか分かりません。次の3つを、上から順に使います。

「!」マークのメニュー

エラーのセルを選ぶと、隣に出る「!」マークからメニューが開きます。メニューに [計算ステップの表示] が出ているときは、そこから計算の手順をたどれます。[エラーを無視] を選ぶと、そのエラーは以後のチェックでも無視扱いになります。引き継いだ表で選ぶときは、記録に残してからにします。

エラーチェックのルールには、エラー値を含む数式、文字列として書式設定された数値、隣接する数式との不一致などがあります。

数式の検証

[数式] タブの [数式を評価する] で、下線の引かれた部分の計算結果を、1つずつ確認できます。「評価」を繰り返せば、どの段階でエラーになるかが分かります。Windows の Excel で使えます。

同時に調べられるのは1つのセルだけです。RAND や TODAY のように再計算で値が変わる関数が入っていると、評価のたびに結果が変わることがあります。

参照元のトレース

エラーのセルを選んで [参照元のトレース] を押すと、そのセルに値を渡しているセルから矢印が伸びます。青い矢印はエラーのないセル、赤い矢印はエラーの原因になっているセルです。もう一度押すと、さらに1つ前の参照元までさかのぼります。

#N/A や #REF! は、1つのセルのエラーが、それを参照する数式に次々と伝わります。表の最後の合計に出たエラーは、赤い矢印をたどって、いちばん上流のセルまでさかのぼります。直すのは、そのセルだけです。

なお、トレース矢印は、数式を変えたり、行や列を挿入・削除したりすると消えます。直す前の状態を残しておきたいときは、矢印を表示した画面を印刷しておくと便利です。

IFERROR と IFNA で空白にしてよいか

IFERROR は、数式がエラーになったときに、指定した値を返す関数です。=IFERROR(数式,"") と書けば、エラーのときだけ空白を表示します。対象は #N/A・#VALUE!・#REF!・#DIV/0!・#NUM!・#NAME?・#NULL! の7種類です。

ここで問題なのは、7種類をまとめて置き換える点です。Microsoft の #DIV/0! のページにも、IFERROR は #DIV/0! 以外のエラーも隠すので、数式が正しく動作するか確認してから使うよう、注意が書かれています。

空白にしてよいエラー

次の条件を、すべて満たすときに限ります。

• 入力前の行の #DIV/0!: 月の途中で、まだ目標が入っていない行の達成率。原因は「未入力」だと分かっていて、空白にしても合計には関係しない。
• まだ登録のない商品コードの #N/A: 新商品が単価表に載る前の、請求一覧の単価。空白でなく「未登録」と出して、見える形にする。

隠してはいけないエラー

#REF! は、数式が見ていたセルがなくなったという合図です。隠すと、本来の金額が抜けたまま、合計だけが合ったように見えます。

#N/A を0にした場合も同じです。次の例で見てみます。

A-109 は単価表に載っていません。0 に置き換えると、金額は0円と計算され、合計 29,500円は、エラーなしできれいに出ます。実際には、A-109 の8個分の金額が入っていません。

IFNA は #N/A だけを拾う

IFNA は、#N/A のときだけ指定した値を返す関数です。Microsoft のページによると、#N/A 以外のエラーは、そのまま表示されます。

=IFNA(VLOOKUP(A2,単価表!A:B,2,FALSE),"未登録")

検索に失敗した行だけ「未登録」と出て、打ち間違いによる #NAME? や、列を消したことによる #REF! は、隠れずに残ります。つまり、検索の失敗は想定内、ほかのエラーは想定外、と分けて扱えます。XLOOKUP を使える版なら、4つ目の引数「見つからない場合」に "未登録" と書く方法もあります。省略すると #N/A が返る、と Microsoft のページに書かれています。

ただし、金額の列を =B2*C2 のような掛け算にしていると、単価が「未登録」の文字では #VALUE! になります。金額の側で、数値かどうかを判定しておきます。

=IF(ISNUMBER(C2),B2*C2,"要確認")

こうしておけば、合計の SUM は文字列を無視するので、金額が入った行だけの合計が出ます。ただし、「未登録」の行が合計に入っていないことは、見落としがちです。表の端に =COUNTIF(D2:D6,"要確認") を置いて、要確認の件数を出しておけば、合計の横で気づけます。

引き継いだ表のエラーを片づける段取り

エラーの並んだ表を渡された日に、次の順で進めます。

最初は、種類ごとの件数を数えるのが近道です。#REF! が数件だけなら、削除した列が1つあるはずです。#N/A が大量にあるなら、検索値や一覧の形式がそろっていないのかもしれません。数が分かれば、同じ原因を一度に直せます。

直した後は、数字を直す前の写しと比べます。エラーが消えて合計が変わったときは、どの行の金額が増えたのかを、記録の欄に残します。

重複したデータが原因で合計が合わないときは、Excelの重複の削除 色付けで確かめてから消す手順と表記の揺れの直し方も参考になります。