Excel で数式を入力していると以下のようなエラーが表示されることがあります。
・ #N/A
・ #REF!
・ #DIV/0!
・ #VALUE!
エラーが表示されると「数式が間違っているのでは？」と思うかもしれません。しかしエラーの種類によって原因は異なり、数式自体に問題がなくても、入力データや参照先の状態によってエラーが発生することも少なくありません。
この記事では、Excel でよく発生するエラーの種類や意味、対処方法を解説するとともに、IFERROR を使ってエラーを分かりやすく表示する方法についても紹介します。
Excel でよくあるエラーの種類
数式を書いているときに表示されるエラーの意味と対処方法をまとめてみました。
数式自体が間違ってことも多いですが、数式ではなく参照しているセルや範囲、セルの値などに問題がある場合もあるので、エラーの意味と対処方法を知っておくと便利です。
エラーを紹介している順番は、発生頻度の高い順で並べています。
#N/A
意味
Not Available ( 該当するデータがない ) の略で、主に検索関数で検索した値が見つからない場合に表示されます。
#N/A が発生する代表的な関数
・ VLOOKUP
・ XLOOKUP
・ MATCH
・ INDEX + MATCH などの複合パターンもあり
主な原因
・ 検索値が検索範囲に存在しない
・ 検索値や検索範囲の指定が間違っている
・ 半角と全角が混在しており、完全一致する値が存在しない
・ 不要なスペースが含まれている
・ 検索値が文字列、検索範囲が数値 ( またはその逆 ) になっている
よくあるミス
見た目が似ていても、半角の 1 と全角の １ のような違いや、ー ( 長音符 ： カタカナの伸ばし棒 ) と － ( 全角ハイフン・マイナス ) のような別の文字として扱われる文字もあります。
また、入力したセルの形式により意図しない結果となる場合もあります。
#N/A になる比較
12345 ( 半角数字 )
１2345 ( 全角数字 )
メール ( 長音符 )
メール ( 全角ハイフン )
メ–ル ( 半角ハイフン )
12345 ( 数字 )
‘12345 ( 文字 ) ※ セルの書式設定で文字列にした場合も同様
対処方法
・ 検索値や検索範囲が正しいか確認する
・ 全角・半角や不要なスペースが含まれていないか確認する
・ 必要に応じて IFERROR で「該当なし」などを表示する
#REF!
意味
Reference ( 参照 ) の略で、数式で参照しているセルや範囲が無効になった場合に表示されます。
主に存在しないセルや列を参照した場合や、参照先を削除した場合に発生します。
REF! が発生する代表的な関数
・ VLOOKUP
・ INDEX
・ OFFSET
・ INDIRECT
・・・ その他、セル参照を使用する数式全般
主な原因
・ 数式で参照している行や列を削除した
・ 数式の参照先を誤って変更してしまった
・ 存在しないセルや範囲を参照している
・ VLOOKUP などの参照指定が参照できる範囲を超えている
よくあるミス
「 = B2 + C2 」のような数式があるシートで 2 行目を削除、または C 列を削除すると発生します。
また一例として、VLOOKUP の検索範囲が 2 列しか選択していない状態で 3 列目を取得しようとすると #REF! が表示されます。
例 )
= VLOOKUP ( A2, D:E, 3, FALSE )
★ D:E の 2 列指定で 3 列目を取得
対処方法
・ 行削除、列削除をした場合は復元する
・ 数式の参照先を正しく設定する
・ VLOOKUP などの参照指定を参照範囲から設定する
#DIV/0!
意味
Division by Zero ( 0 除算 ) の略で、数値を 0 または空白のセルで割ろうとした場合に表示されます。
割り算は 0 で割ることができないため、Excel では #DIV/0! エラーとなります。
#DIV/0! が発生する代表的な関数
・ / ( 割り算 ： 関数ではないが一番多い )
・ MOD
・ AVERAGE
主な原因
・ 分母が 0
・ 分母が空白
・ 数値が入力されていない ( 全角文字になっている )
・ セルの書式が文字列になっている
・ 計算結果が 0 になったセルを分母としている
よくあるミス
割り算の分母が 0、または空白の場合に発生するため、セルの値を削除 ( クリア ) したときによく発生します。
また、１２３ などの全角数字や、セルの書式が文字列形式になっている場合でも発生しますので原因特定が難しい場合もあります。
特に、数式の結果を別の数式で参照している場合など、何が原因で発生したかよくわからない状態になることがあるので、普段からどのような数式が使われているかを確認しておくことも必要です。
対処方法
・ 分母が 0 または空白になっていないか確認する
・ 入力漏れがないか確認する
・ IF で 0 または空白を判定し、0 または空白の場合に計算しないようにする
・ IFERROR で 0 または空白を表示する ※ 初期値が 0 でもエラー表示しないようにする
#VALUE!
意味
Value ( 値 ) の略で、数式や関数で使用している値やデータ型が正しくない場合に表示されます。
主に、数値として計算できない文字列が含まれている場合や、関数の引数に誤った値を指定した場合に発生します。
#VALUE! が発生する代表的な関数
・ 四則演算 ( +、-、*、/ ）
・ SUM
・ AVERAGE
・ その他、数値を扱う関数全般
主な原因
・ 数値として扱えない文字列を計算している
・ 数値と文字列が混在している
・ 全角数字や不要な文字が含まれている
よくあるミス
「 = 123 + “ABC” 」のような数字と文字列を計算させようとすると発生するため、１２３ などの全角文字が計算対象に入るとよく発生します。
また、数値の後ろに「円」や「個」などの単位が入っていると数式では計算できずに #VALUE! が表示されることがあります。計算式で使用する場合は「数字と単位を別セルに分ける」、「セルの書式設定で単位を表示する」などの工夫が必要です。
対処方法
・ 数値として計算するセルに文字列が入力されていないか確認する
・ 数値に全角文字が混在していないか確認する
・ セルの表示形式を利用し、「円」や「個」などの単位を表示形式で設定する
その他
その他にも #NAME?、#NUM!、#NULL!、#SPILL!、#CALC! などのエラーもありますが、実務で見かける機会は比較的少ないため、簡単に説明します。
発生していなければ読み飛ばしてください。
#NAME?
セル参照や関数の書き間違い
例
= A1 + B
= VLOKUP ( A2, D:E, 2, FALSE )
#NUM!
数値として計算できない値や計算結果が範囲外
例
= SQRT ( -1 )
※ SQRT は平方根 ( √ ： ルート ) を計算する関数でマイナス計算はできない。
余談
= SQRT ( 9 ) の結果は 3
#NULL!
範囲指定を誤った場合
例
= SUM ( A1:A5 C1:C5 )
※ SUM ( A1:A5, C1:C5 ) が正しいが、「A1:A5 C1:C5」と書くと交差する場所を取得する意味で使用されるため、A1:A5 C1:C5 では交差する場所がないと判定され #NULL! エラーとなる。
余談
= A1:C3 B2:D4 と記載すると結果は B2:C3 ( 交差する位置 ) になる。
#SPILL! ( Microsoft 365以降 )
出力結果が複数行、または複数列になる関数で結果表示行にすでに値がある場合
例
= SEQUENCE ( 5 )
※ 5 行連番を表示する関数ですが、結果を出力する行 ( 数式の下 4行 ) に値が設定されていると #SPILL! 表示になる
= XLOOKUP ( A2, D2:D10, E2:G10 )
※ 出力結果が複数列 ( E から G の 3列 ) になるため、数式の右列 ( 2 列 ) に値が設定されていると #SPILL! 表示になる
#CALC! ( Microsoft 365以降 )
動的配列関数などで計算できない場合に表示されます。
例
= FILTER ( A2:A10, B2:B10=”東京” )
※ FILTER は条件一致するデータを一覧表示する関数で、第三引数 ( 該当しない場合 ) を省略すると #CALC! が表示されます。
IFERROR とは
IFERROR は、数式がエラーになった場合だけ指定した値を表示する関数です。
正しい数式を入力していても検索した値が見つからない場合や、0 で割ってしまった場合など、参照する値によってエラーが表示されてしまうことがあります。
IFERROR を使用すると、このようなエラーの表示を「計算できません」や「該当なし」などの任意の文言に置き換えることができます。
なお、エラーが発生しない場合には数式の結果がそのまま表示されます。
IFERROR の構文
IFERROR の構文と各引数の説明です。
値 ( 数式 ) とエラーの場合の値 ( 文言 ) だけなので比較的簡単に覚えられます。
構文
= IFERROR ( 値, エラーの場合の値 )
※ 最初から値がエラーの場合は少ないため、数式が入ることが多いです。
例)
= IFERROR ( A1 / B1, "計算できません" )
= IFERROR ( VLOOKUP ( "1", A1:B10, 2, FALSE ), "該当なし" )
= IFERROR ( A1 / B1, 0 )
= IFERROR ( A1 / B1, "" )
※ エラー発生時の結果を 0 や空白にすることもできます
| 引数 | 内容 | 必須 |
|---|---|---|
| 値 | 実行する数式 | ○ |
| エラーの場合の値 | エラー時に表示する値 | ○ |
IFERROR の基本的な使い方
エラーが発生する可能性のある数式に対してエラー発生時の文言を用意します。
発生するエラー内容に合わせて表示する文言を正しく指定します。
以下の例では、「人数が 0 の場合」と「人数が 5 の場合」で同じ数式にしてありますが 0 で割ることはできないためエラー発生時の文言が表示されます。
= IFERROR ( A2 / B2, "計算できません" )
IFERROR を使う際の注意点
IFERROR は、すべてのエラーをまとめて処理します。
値の入力ミスや、数式の構文間違いまですべて隠してしまう場合がありますので、まずは IFERROR を除いた数式を作成し、その後で IFERROR を追加することをおすすめします。
ちなみに IFNA という #N/A だけを判定する関数もありますが、発生するエラーが何となく把握できていれば IFERROR だけで十分です。
なお、数式の検証方法は以下の記事で紹介していますので気になる方はご確認ください。
IFERROR を書いたままでも検証可能です。
まとめ
Excel のエラーにはそれぞれ意味があり、原因を理解することで適切に対処できるようになります。
IFERROR を使用することで、エラー時の表示を「該当なし」や「計算できません」などの任意の文言に置き換えられるため、見やすく分かりやすい資料を作成できます。
ただし、IFERROR はエラーの原因まで隠してしまうため、数式が正しく動作していることを確認した上で利用するようにしましょう。