Microsoft Excel(エクセル)で数式や関数を使うと、エラー値が表示されることがあります。
エクセルの数式のエラー値には、「#DIV/0!」や「#N/A」、「#REF!」など８種類のエラーが存在します。
これらの数式エラーが表示された場合、エラー値の意味が分からないと原因を探すのに苦労してしまいます。
この記事では、事例を使いながら、Excelの８つのエラー値の意味と、どういった場合にエラーが発生するか、発生原因と解決策について紹介していきます。
この機会に、Excelの数式エラーの修正方法について学んでください。
エクセルのエラーコードの種類は８つ
エクセルの数式や関数で発生するエラーコードは以下の８種類です。
エクセルのエラー値
「#N/A」「#VALUE!」「#DIV/0!」「#NULL!」「#REF!」「#NAME?」「#NUM!」「#スピル!」
この後の見出しで詳しく紹介しますが、エラー値の内容と発生する原因は以下の通りです。
この中でも、「#DIV/0!」、「#N/A」、「#REF!」の３つのエラーは関数を取り扱う際に特によく見かけるエラーかと思います。
| 種類 | 内容と 原因 |
| #DIV/0! | 『0』で割り算している。割り算をしている参照セルが未入力など |
| #NAME? | 「＝SAM」と入力するなど関数名を誤っている |
| #N/A | 条件に指定するセル範囲やセル番号を誤っている |
| #VALUE! | 数値を指定すべき引数に文字列を指定している |
| #REF! | 関数で参照しているセルや行、列を削除した際に発生する |
| #NUM! | 数式または関数に無効な数値が含まれている場合に表示 |
| #NULL! | 参照するセル範囲の指定方法が誤っている |
| #スピル! | スピルにより入力された範囲に、余計なデータが入っている |
この８つのエラー以外にも、「#######」と表示される場合がありますが、それは数式によるエラーではなく、セルの幅が狭すぎるための表示エラーです。
それでは、具体的にこれらのエラーコードの修正方法、直し方を紹介していきます。
エラー値１：「#DIV/0!」の原因と解決策
エラー値「#DIV/0!」の読み方は「ディブゼロエラー」です。
このエラーの「DIV」は「割る」を意味するdivisionの略で、「/0」は「0で割る」を意味しています。
そのため、「#DIV/0!」が発生する原因は、数式や関数で『数値を0で割っている』ことによるものです。
例えば、下の図のように、「10÷0」や「10÷空白セル」の答えは無いので、エラーコードが表示されます。
解決策は、計算式を見直して0で割らないように注意するか、下の図ように、対象の数式をIFERROR関数を使って囲うことでエラーを非表示にすることです。
=IFERROR(B3/C3,"")
エラー値２：「#NAME?」の原因と解決策
エラー値「#NAME?」の読み方は「ネイムエラー」です。
「NAME」は「名前」を意味する英語で、「?」の疑問符が付いていることからも想像できるように、このエラーは「関数名を誤っていることで発生するエラー」です。
例えばE列にSUM関数を挿入するべきところを、「=SAM(B3:D3)」「=DAM(B4:D4)」と関数の名前を間違って使用すると、「#NAME?」エラーが発生します。
エラーは解決するためには、以下のように関数名を正しく入力し直します。
関数名を正しく入力しているにもかかわらず、エラーが消えない場合は、対象の関数が利用できないExcelバージョンの可能性があります。
エラー値３：「#N/A」の原因と解決策
エラー値「#N/A」の読み方は「エヌエーエラー」です。
このエラーの「N」は「Not」、「N」は「Applicable」の略で、「該当なし」や「利用できない」という意味となります。
そのため、「#N/A」エラーは、関数の引数で指定するセル範囲やセル番号を誤っている場合に表示されます。
例えば、下のG3セルには、表から値を抽出することができるVLOOKUP関数「=VLOOKUP(F3,B3:D7,3,FALSE)」が挿入されています。
VLOOKUP関数の第１引数で指定しているF3セルの商品番号『40』がB列に存在しないために、VLOOKUP関数が対象のセルを検索することができないので「#N/A」が表示されています。
エラーを解決する方法は、VLOOKUP関数の第１引数で指定している「F3」セルの値を「25」に修正すれば、B列にも同じ値があるので、「#N/A」のエラーは解消されます。
仮に、VLOOKUP関数で商品番号「40」が見当たらないことを確認したい場合は、以下のように、IFERROR関数を用いて、「#N/A」のエラーを回避することも出来ます。
=IFERROR(VLOOKUP(F3,B3:D7,3,FALSE),"該当なし")
エラー値４：「#VALUE!」の原因と解決策
エラー値「#VALUE!」の読み方は「バリューエラー」です。
「VALUE」が「文字」を意味しているので、このエラーは「数式に不適切な値(文字列)が含まれている」と発生します。
具体的には、「文字列に対して数式を使っている」、「数式がエラー「#VALUE!」のセルを参照している」ことが主な発生原因となります。
例えば、下の図のB3セルは「10個」と入力されているため文字列として取り扱われるので、「=B3＋C3」の結果が「#VALUE!」と表示されます。
さらに、D6セルのSUM関数は、「#VALUE!」のD3セルを参照しているため、その結果も「#VALUE!」となります。
このエラーコードを直すためには、エラーの元となっているセルを数値で入力し直せば、エラーは解決します。
エラー値５：「#REF!」の原因と解決策
エラー値「#REF!」の読み方は「リファレンスエラー」です。
英語の「reference」の頭文字が使われていて、「参照」を意味しているため、このエラーは「数式や関数が無効なセルを参照している場合」に発生します。
特にこの「#REF!」が一番発生するケースは、数式が参照しているセルや行もしくは列、ワークシートを削除したときに発生します。
たとえば、下のG3セルにはVLOOKUP関数が挿入されていて、B～D列から商品の値段が検索されています。次に、この表のB列～D列を削除してみます。
そうすると、VLOOKUP関数が参照していたセル範囲(B列～D列)が消されてしまったことで、VLOOKUP関数の書式が以下のように変わり、「#REF!」エラーが発生します。
=VLOOKUP(C3,#REF!,3,FALSE)
このエラーは参照セルが削除されたことにより発生するので、数式を作り直すだけでなく、元のセルを復元する必要があるので、元に戻すのが大変です。
そのため解決策は、「元に戻す」ボタンを使って、エラーが発生する直前の状態まで戻してください。
仮に、ワークシートを削除したことで、数式が崩れてこのエラーが発生した場合は、ファイルを上書き保存せずデータを一度閉じて開き直してください。
エラー値６：「#NUM!」の原因と解決策
エラー値「#NUM!」の読み方は「ナンバーエラー」です。「NUM」は「number」の略で「数値」という意味です。
「#NUM!」は、数式または関数で取り扱うことができない無効な数値が含まれている場合に表示されます。
たとえば、下の図のように、LARGE関数で６番目に大きい値を表示しようとしても、指定したセル範囲がそれを下回る４つしかなければ、「#NUM!」が発生します。
「#NUM!」を解決するには、数式の引数を修正する必要があります。
「=LARGE(C3:C6,6)」を「=LARGE(C3:C6,3)」と修正することで、エラーは消え、セル範囲C3:C6の中から３番目に大きい数値が表示されます。
エラー値７：「#NULL!」の原因と解決策
エラー値「#NULL!」の読み方は「ヌルエラー」です。英語の「NULL」は「無効の」という意味です。
そのため、「#NULL!」は、関数で参照するセル範囲の指定方法が誤っているときに発生します。
例えば、下の事例のように、AVERAGE関数でB列～D列の平均値を算出するとき、「=AVERAGE(B3 C3 D3)」「=AVERAGE(B4:C4 D4)」といったように、セル範囲を指定する際、「半角スペース」を使ったりすると「#NULL!」が発生します。
解決するには、AVERAGE関数で指定しているセル範囲の指定方法を見直し、「＝AVERAGE(B3,C3,D3)」「＝AVERAGE(B4:C4,D4)」と修正すれば、エラーは解消します。
エラー値８：「#スピル!」の原因と解決策
エラー値「#スピル!」の読み方は「スピルエラー」です。「#SPILL!」と表示されることもあります。
「SPILL」は英語で、「あふれる」「こぼれる」という意味です。
このエラー値「#スピル!」は、「スピル機能を使った際、数式が自動拡張されるセルに値が入力されている場合」に発生します。
スピル機能を知らない方が多いと思うので、基本的な使い方を紹介します。例えば、E3セルに、「=C3:C6」と入力して「Enter」キーを押して確定します。
そうすると、数式はE3セルにしか入力していませんが、数式を挿入していないE4 ～E6セルにもC列の値が自動で入力されました。これがスピルと呼ばれるエクセルの機能です。
下の図のように、スピル機能で値が自動で入力される対象セルに値が入力されていると、数式や関数を確定させた際、「#スピル!」のエラー値が発生してしまいます。
この「#スピル!」エラーの解決策は、スピルで数式の結果が自動拡張される対象のセル範囲を空白にすることで、エラーを回避することができます。
セルに「#######」エラーが表示される原因と対策
下の図のB2セルには「1234567890」と数値を入力していますが、「#######」とエラーが表示されています。
このエラー値が発生するのは、数値が入力されているセルの列幅が狭すぎることが原因です。
下の図のように、セルの列幅を広げれば数値が正しく表示され、エラーを解消されます。
数式や関数のエラー値をIFERROR関数で非表示にする方法
この記事で紹介したように、数式や関数でエラー値が表示された場合、数式を見直すことも大切ですが、エラー値を非表示にすることもできます。
IFERROR関数を使うことでエラー値を非表示にしたり、別の値に変換することができます。以下の記事で詳しく紹介していますので、参考にしてください。
(関連記事)循環参照エラーについて
今回は８つの数式のエラーコードの内容について紹介しました。
これ以外にもExcelファイルを開くと、「１つ以上の循環参照が発生しています。」という警告メッセージが表示されることがあります。
この循環参照エラーは、数式がセルを正しく参照出来ていない数式セルに発生しているエラーです。
以下の記事で、「循環参照とは何のか？」「循環参照エラーの解決方法」を紹介しているので、参考にしてください。
