Excelのエラー値徹底解説

2017.09.13基礎・入門関数その他

Excelでは計算式に矛盾があったり、計算できなかったりする場合に#で始まるエラーを出します。
エラーというとなんだかコンピュータが止まってしまいそうな、そんな悪いイメージもあるのですが、Excelのエラーは計算式の1つの答えと考えればよいでしょう。
計算式の作成方法や考え方が間違っているだけで、修正できるのであれば修正した方がいいというものです。
修正する場合、エラーの内容を読み解くことで修正がしやすくなります。
Excelが答えとして出すエラーをよくある順に解説します。

######エラー

#######のような#が連続して表示される場合は、そのセルに入力している値やセルの幅に収まりきらなかった時に表示します。

######

 

初期設定のセルの書式設定では、Excelが幅に合わせた表示の方法で表示したり、多い文字列をはみ出させたりして表示するので、よほどセルの幅を縮めない限りこのエラーにはなりません。
セルの書式設定で、表示形式を数値にして、多くの桁数の数値を入力した場合によくこの状態になります。
厳密にいうとエラーではなく、セルの幅を広げればきちんと表示します。
文字の大きさが変わってもいいのであれば、Ctrl+1のショートカットキーで[セルの書式設定]を表示し、[配置]タブの[縮小して全体を表示する]のチェックを入れることでも回避できます。
「2017年9月13日(水)」のような長い日時表示や、エラー値を出力したセルの幅が狭い場合もこのエラーになります。

#DIV/0!エラー

#DIV/0!の表示は、0で割ったというエラーです。

#DIV/0!

 

Excelをはじめとするコンピュータはすべて、0で割るということができません。
学校で習う数学でも0で割ってはいけないというルールがありますね。
どんな数字でも、0で割るということは、無限に小さいもので割ることを意味しています。
特にコンピュータでは、無限に小さい数字で割る処理を永遠と繰り返してしまうのです。
Excelは割り算する場合、あらかじめ0で割るかどうかをチェックしていて、もし0で割るような計算式になっている場合は、#DIV/0!と表示するのです。
=1/0 」は当然#DIV/0!エラーになります。「 =A1/0 」でも同様です。
意図せず#DIV/0!になるケースは、「 =100/(10-A1) 」のような計算の場合です。セルA1がもし10であれば割られる数字が0になるので#DIV/0!エラーとなります。
=0/1 」のような、割られる数字が0の場合はエラーとなりません。あくまで割る数字が0の場合のみです。
回避方法はいくつかありますが、「 =A1/B1 」のケースであれば、もしもセルB1が0ならば、というIF関数を組み合わせ、「 =IF(B1=0,"",A1/B1) 」という式にすればエラーにならなくなります。
0で割った場合の答えは0と考えることが多いので、空白の場合を0とすることで答えを0にすることもできます。
=A1/(B1+0.0001) 」のようにとても小さい値が必ず入るようにする方法もありますが、セルB1が-0.0001の場合は割られる数字が0となるので#DIV/0!エラーになります。
=100/(10-A1) 」の場合は、もしもセルA1が10ならば、というIF関数を組み合わせ、「 =IF(A1=10,"",100/(10-A1)) 」という式にすればエラーにならなくなります。

#N/Aエラー

#N/Aの表示は、値が見つからないというエラーです。

#N/A

 

ある範囲から値を探す関数、VLOOKUP関数、HLOOKUP関数、MATCH関数で検索値が見つからない時にこの表示になります。
検索値が検索範囲に見つからない代表例は、検索値が未入力の場合です。未入力は空白なので、一般的に一覧表内には明示してありませんので見つからず#N/Aエラーになります。
よくある対処法は、IF関数と組み合わせ、「 =IF(A1="","",VLOOKUP(A1,C1:E10,3,0)) 」のように、もしも検索値が空白なら空白、そうでなければ関数の答えのようにします。
また、#N/AエラーはISNA関数で検索値が見つからない時はTRUE、そうでない場合はFALSEの値を出力する形でチェックすることができます。これを応用し、「 =IF(ISNA(VLOOKUP(A1,C1:E10,3,0)),"",VLOOKUP(A1,C1:E10,3,0)) 」のように、VLOOKUP関数の答えが#N/Aならば空白、そうでなければVLOOKUP関数の答えとすることができます。
ただし、この計算式は同じVLOOKUP関数を2回使いますので無駄なようにも思えます。Excel2013(Mac版はExcel2011)からはIFNA関数というIF関数とISNA関数が一度に指定できる関数が用意されており、「 =IFNA(VLOOKUP(A1,C1:E10,3,0),"") 」と表記することで、2回VLOOKUP関数を入力する必要がありません。Excel2013以前のバージョンでは対応していないので注意が必要です。
もしもそれ以前のバージョンで同じVLOOKUP関数を2回繰り返したくない場合は、COUNTIF関数を組み合わせると良いでしょう。「 =IF(ISNA(COUNTIF(C1:C10,A1)<1),"", VLOOKUP(A1,C1:E10,3,0)) 」となります。検索値が一覧表範囲の最左列の中に1つもなければ空白、そうでなければVLOOKUP関数の答えとなります。
#N/Aエラーが発生するケースを考えると、検索値が入力されていない場合、検索値が誤って入力されたため見つからない場合の2つが考えられます。この2つのケースに対応するエラーメッセージを出力する最も親切な計算式は「 =IF(A1="","検索値を入力してください",IF(COUNTIF(C1:C10,A1)<1,"正しい値を入力してください",VLOOKUP(A1,C1:E10,3,0))) 」となります。

#VALUE!エラー

#VLAUE!の表示は、数値を求める計算式の中に文字が含まれる場合です。

#VALUE!

 

数値を求める演算子は「+」の加算、「-」の減算、「*」の乗算、「/」の除算、「^」のべき乗です。これらで計算した場合に数値のつもりが文字データで演算されていると#VALUE!エラーとなります。「 =A1+1 」の計算式において、セルA1に「1.2」と小数点で入力したつもりが「1,2」とカンマ区切りで入力してしまった時、「1,2」は文字データですので、計算できないので#VALUE!エラーになります。
この場合は、数値の個数のみを数えるCOUNT関数が応用できます。計算式は「 =IF(COUNT(A1)=0,"文字データが入力されています",A1+1) 」となり、もしもA1の数値の個数が0なら空白、そうではなければ計算式となります。
また、統計関数としてSUM関数やAVERAGE関数がありますが、この場合、文字は無視して計算しますのでエラーにはなりません。そこで一つ気を付けたいのは、「1」「2」「3a」「4」「5」を関数で集計した時、「3a」が無視された値で計算されます。エラーにはなりませんので、答えが間違っていることに気付きにくいかもしれません。
この場合もCOUNT関数が応用できるでしょう。計算式は「 =IF(COUNTA(A1:A5)>COUNT(A1:A5),"文字データが入力されています",SUM(A1:A5)) 」で、データの個数を数えるCOUNTA関数と数値データのみを数えるCOUNT関数の結果が違えばエラーメッセージ、そうでなければ関数の答えとすることができます。
必ず数値を出力する統計の種類以外の、SIN関数やVALUE関数、財務関数であるFV関数などの入力値に文字データがあっても#VALUE!エラーとなります。
#VALUE!エラーは意図せず文字列が入力された場合に起こるので、そもそも文字データが入力されないよう、入力規則で制限することも#VALUE!エラー防止には有効な手段です。

 

#NAME?エラー

#NAME?の表示は、Excelが認識できない文字が入力された場合に起きます。

#NAME?

 

主なケースは3つあり、関数名の入力間違いか、名前機能で設定した名前の入力間違い、または指定する範囲の入力間違いです。
SUM関数を入力しようとして、「 =SAM(A1:C1) 」というスペルミスをした場合、名前機能で一覧表に「商品リスト」と設定していて「 =VLOOKUP(A1,商品,2,0) 」と名前を勘違いして入力した場合、セルZZ1を指定しようとして「 =ZZZ1+1 」と一文字多く入力した例などが考えられます。
関数名のスペルミスは関数の挿入ボタンや、関数の予測変換機能を使うことで防ぐことができます。

予測変換と関数ウィザード

 

名前の勘違いはなかなか気づきにくいかも知れません。名前を使う場合は、[数式]タブの[名前の管理]できちんと管理しましょう。名前入力時にF3キーで名前を呼び出せば間違いは少ないです。

名前の管理と名前の挿入

 

セル参照に余計な文字を入力してしまう「A100a」のような入力例で、Excelに存在しないセルを指定していれば#NAME?エラーになりますが、「MAX100」(MAX列の100行目)のように存在するセルの表記を誤って指定してしまうとエラーになりませんので、間違いに気付きにくいです。動作チェックをすることはもちろん、参照元のトレースなどの機能を活用しましょう。

参照先/参照元のトレース

 

#NAMEエラーは、作られた計算シートへの入力作業中に起きることは少なく、計算シートの仕組みを作成作業中に出ることが多いので、注意しましょう。

#REF!エラー

#REF!エラーは、存在しないセルを指定しているときに発生します。

#REF!

 

よくあるのは、セルA1を参照した「 =VLOOKUP(A1,C1:E10,2,0) 」のような計算式を作成後、セルA1を削除すると、参照できなくなるので計算式の答えが#REF!となります。
セルや行列を削除すると起きることが多いですが、シートを削除した際も気を付ける必要があります。
また、OFFSET関数で存在しないセルを指定した場合も同様で、「 =OFFSET(A1,-1,1) 」ではセルA1の上の行を指定することになるので、#REF!エラーになります。
VLOOKUP関数の列番号が一覧表範囲外でも起きます。「 =VLOOKUP(A1,C1:D10,3,0) 」では、一覧表が2列しかないにも関わらず3列目を指定しているので、#REF!エラーになります。ただし、この場合でも、A1の検索値が見つからない場合は#N/Aエラーになります。

#NUM!エラー

#NUM!エラーはExcelで扱える数値の大きさ以上の数値を扱った場合に表示されます。

#NUM!

 

Excelは308桁以上の数字は扱えません。308桁というと小さく感じますが、1の後ろに0が308個ついた数字のとても大きな数字で、億や兆のような文字での表現方法はありません。世の中の様々な業務や計算で308桁を使い切ることはほとんどありません。
例えば、500×500の計算をしようと「 =500*500 」を計算しようとしたときに、誤ってべき乗で「 =500^500 」と計算した場合は、#NUM!エラーになります。
また、セルE300に1億を乗算する「 =E300*1000000000 」という計算式を入力しようとして誤って「 =1E300*1000000000 」と入力した場合、「1E300」は1の後ろに0が300個あるとても大きな数字に1億の8桁の数字を掛け合わせるため、308桁となり、#NUM!エラーとなります。
=1E300*-1000000000 」の場合も、マイナス側に308桁となるので、エラーになります。
また、CHOOSE関数の第一引数やSMALL関数の第二引数など、何番目かの番号を指定する際にマイナスの値を入力しても#NUM!エラーになります。
#NUM!エラーも入力ミスが原因のことが多いので、入力規則で防ぐことができます。

#NULL!エラー

#NULL!エラーは、関数内で範囲指定時に「:」や「,」で区切らずにスペースで区切られた場合に表示されます。

#NULL!

 

実は関数内で範囲をスペースで区切るという書式はExcelでは「:」や「,」で区切るのと同じように範囲指定としての意味を持っています。
A1からA5の範囲にすべて1が入力されている場合、「 =SUM(A1 A1:A5) 」の答えは1となります。「 =SUM(A2:A4 A1:A5) 」の答えは3となります。「 =SUM(A2:A4 A3:A6) 」の答えは1となります。
つまり、空白で区切られた範囲で重複しているセルだけを対象範囲としているのです。
#NULL!エラーは、空白で区切られた範囲内でセルが重複していなければ表示されます。
A1からA5の範囲の合計を求める際、「 =SUM(A1 A5) 」と入力すれば、A1とA5は重複していないので#NULL!エラーとなります。
#NULL!エラーも計算式を作成している時に出ることが多いでしょう。

循環参照

#に続く表示ではないのですが、数式のエラーとして循環参照があります。

循環参照

 

正しく計算されないという意味ではエラーですので発生したら修正しましょう。
循環参照についての詳細はこちらです。

エラー値を含む演算

計算式の中に1つでもエラーがあると、エラー回避しない限り計算結果はエラーになります。
=SUM(A1:A5) 」でセルA3が#N/Aエラーの場合、答えはエラー値を無視せず、#N/Aになります。注意しましょう。

Excel業務をもっと「ラク」にするなら

Excel業務を自動化したい!
Excelをデータベース化したい!

 

Back to top