Excelの#DIV/0!エラーの原因と消し方|0や空白で割るときのIF・IFERROR

スポンサーリンク

Excelで達成率や前年比を計算したら、セルに #DIV/0! がずらりと並んでしまった。そんな経験はありませんか。

表の見た目が悪くなるだけではありません。その列を平均しようとすると、結果まで #DIV/0! になって手が止まりますよね。

結論から言うと、#DIV/0! は分母が0か空白だから出るエラーです。消すなら =IF(C2=0,"",B2/C2) のように、IFで分母を判定する書き方が基本。

IFERRORでも消せますが、#DIV/0! 以外のエラーまで隠してしまう点に注意が必要です。#DIV/0! が出る5つの場面を早見表で整理し、達成率・前年比・単価の表で直し方を見ていきましょう。

対応バージョン: Microsoft 365・Excel 2024/2021/2019/2016・Web版で共通の内容です。IFERRORはExcel 2007以降で使えます。

Excelで#DIV/0!エラーが出る5つの場面(症状別早見表)

#DIV/0! は、Excelが「0で割る計算」をしようとしたときに返すエラーです。0で割った答えは決まらないため、計算結果の代わりにこの記号が表示されます。

Microsoftの公式ヘルプも、0や空白のセルで割ったときにこのエラーが出ると説明しています。つまり空白セルも、割り算の分母では0と同じ扱い。

次の達成率の表では、D2に =B2/C2 を入れてD6までコピーしています。

行A(担当)B(実績・万円)C(目標・万円)D(達成率 =B2/C2)
2佐藤120100120%
3鈴木8510085%
4高橋300#DIV/0!
5田中45(空白)#DIV/0!
6(未入力)(空白)(空白)#DIV/0!

自分の表がどの場面に当てはまるか、次の早見表で探してください。

場面見分け方直し方
分母が0分母のセルに0が入っているIFで分母が0なら空白を返す
分母が空白セル目標などがまだ入っていない分母が0のときと同じ数式で消える
分母が "" やスペース見た目は空白なのにIFで消えない分母の中身を確認して判定を足す
AVERAGE・AVERAGEIFの対象0件割り算を書いていないのに出る件数をIFで判定してから平均する
未入力の行へ数式をコピーデータがない行にだけ出るIFで分母を判定してからコピーする

分母が0のとき

表の4行目(高橋さん)は目標が0なので、=B4/C4 は「30÷0」の計算になります。答えが決まらないため、結果は #DIV/0!。

返品で数量が0になった行のように、0が正しい値として入っている場合もあります。そのときはデータを直すのではなく、数式の側で0の行を避けるのが現実的です。割り算の書き方そのものはExcelの四則演算のやり方も参考にどうぞ。

分母が空白セルのとき

表の5行目(田中さん)は、目標がまだ入っていない状態です。空白セルは分母では0と同じ扱いなので、「45÷空白」も #DIV/0! になります。

目標を後から入れる運用の表だと、入力するまでずっとエラーが見えたまま。IFで「空白なら計算しない」と書いておくと解消できます。

なお、空白セルを =C5 のように参照して0が表示されるのは、別の仕組みによる症状です。そちらは空白セルを参照すると0が表示される原因にまとめています。

分母が “” や見た目だけ空白のとき

分母のセルが空っぽに見えても、中身は数式の返す ""(空文字列)やスペースかもしれません。この場合は #DIV/0! ではなく、#VALUE! など別のエラーになることがあります。

さらに、分母が0かどうかの判定だけでは捕まえきれない場合も。「IFを入れたのに消えない」ときの対処は、後半の確認ポイントの節で紹介します。

AVERAGE・AVERAGEIF の対象が0件のとき

割り算を書いていなくても、#DIV/0! は出ます。平均は「合計÷個数」で求めるので、個数が0だと0で割ることになるためです。

AVERAGE関数は、範囲内の空白セル・文字列・論理値を無視し、0は計算に含めます。そのため範囲に数値が1件もなければ、結果は #DIV/0!。

AVERAGEIF関数は、条件に一致するセルがないと #DIV/0! を返すと公式ヘルプが明記しています。A列に「大阪支店」の行が1つもなければ、次の数式の結果は #DIV/0! です。

=AVERAGEIF(A2:A10,"大阪支店",C2:C10)

複数条件のAVERAGEIFSでも、条件に合う行がなければ同じように #DIV/0! になることがあります。各関数の基本はAVERAGE関数・AVERAGEIF関数・AVERAGEIFS関数の記事を参考にしてください。

未入力の行へ数式をコピーしたとき

表の6行目のように、データを入れる前の行まで数式をコピーすると「空白÷空白」の計算になります。分母が空白なので、結果は #DIV/0!。

月次の表を年度初めに12行分作っておくと、まだ来ていない月がエラーで埋まります。数式自体は間違っていないので、IFで「分母が入るまでは空白」にしておきましょう。

IFで分母を判定して#DIV/0!を消す(達成率の表で実演)

IFで分母を調べ、0なら割り算をしない形にすると #DIV/0! は出なくなります。この方法の利点は、分母が0の行だけを狙って処理できること。

数式の打ち間違いなど別の原因のエラーは、そのまま表示されて気づけます。IF関数そのものの書き方はIF関数の使い方を参考にしてください。

基本形 =IF(C2=0,””,B2/C2)

達成率の表のD2に次の数式を入れ、D6までコピーします。

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

意味は「C2が0なら空白("")を表示し、それ以外はB2÷C2を計算する」です。空白セルも0と同じ扱いになるので、目標が未入力の行もまとめて空白になります。

行担当実績目標修正前 =B2/C2修正後 =IF(C2=0,"",B2/C2)
2佐藤120100120%120%
3鈴木8510085%85%
4高橋300#DIV/0!(空白)
5田中45(空白)#DIV/0!(空白)
6(未入力)(空白)(空白)#DIV/0!(空白)

空白ではなく、理由を文字で表示したい場合はこちら。

=IF(C2=0,"目標未設定",B2/C2)

Microsoftの公式ヘルプには =IF(A3,A2/A3,0) という短い例も載っています。分母のセルをそのまま条件にすると、0や空白はFALSEとみなされ、0が返る仕組みです。達成率の表に当てはめると次の形。

=IF(C2,B2/C2,0)

この形だと高橋さん・田中さん・6行目は0%と表示されます。空白と0%のどちらにすべきかは、後半の判断表で選んでください。

空白と0の両方を判定する書き方

分母が別の数式の結果で、"" が返ってくる表もあります。その場合に備えて、ORで「0または ""」をまとめて判定する書き方です。

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

C2="" は、空白セルに対してもTRUEになります。空白セル・0・数式の返す "" のどれでも空白を返せるのが、この形の強み。

前年比の表で試してみましょう。前年比は「今年÷前年−1」で求めるので、D2の数式は次のようになります。

=IF(OR(C2=0,C2=""),"",B2/C2-1)
行A(店舗)B(今年・万円)C(前年・万円)D(前年比)
2本店13212010%
3駅前店95100-5%
4新店80(空白)(空白)

今年オープンした新店は前年の実績がないため、前年比は空白のまま。本店は 132÷120−1=0.1 で10%、駅前店は 95÷100−1=−0.05 で−5%です。

AVERAGE の対象0件をIFで避ける

平均の対象が0件になりうる場合は、先に数値の個数を数えておくのがコツ。COUNT関数は範囲内の数値の個数を返すので、0件なら平均を計算しない形にできます。

=IF(COUNT(D2:D6)=0,"",AVERAGE(D2:D6))

AVERAGEIFの場合は、COUNTIFで条件に合う件数を先に調べる形です。

=IF(COUNTIF(A2:A10,"大阪支店")=0,"該当なし",AVERAGEIF(A2:A10,"大阪支店",C2:C10))

=IFERROR(AVERAGEIF(…),"該当なし") と囲む書き方もあります。ただし関数名の打ち間違いなど、別のエラーまで「該当なし」に変わる点は覚えておきましょう(次の節で実演)。

IFERRORで隠す書き方と、IFとの使い分け

=IFERROR(B2/C2,””) の書き方

IFERRORは「数式がエラーなら、代わりの値を表示する」関数です。Excel 2007以降で使えます。

=IFERROR(B2/C2,"")

単価の表(売上金額÷数量)で使うと、数量が0の行だけが空白に。

行A(商品)B(売上金額)C(数量)D(単価 =IFERROR(B2/C2,""))
2ボールペン12,000100120
3ノート9,00060150
4クリップ00(空白)

分母のセルを指定しなくてよいぶん、IFより短く書けるのが利点です。構文の詳細やVLOOKUPとの組み合わせはIFERROR関数の使い方で解説しています。

#REF!・#NAME? まで消えてしまう実演

IFERRORが置き換えるのは #DIV/0! だけではありません。#N/A・#VALUE!・#REF!・#DIV/0!・#NUM!・#NAME?・#NULL! の7種すべてが対象です。

Microsoftの公式ヘルプも、IFERRORはすべてのエラーを隠す処理だと注意しています。エラー処理を付ける前に、数式が正しく動くかを確かめるよう勧めているほどです。

たとえば単価を切り捨てようとして、ROUNDDOWNを ROUNDOWN と打ち間違えたケースを考えてみましょう。

=IFERROR(ROUNDOWN(B2/C2,0),"")

存在しない関数名なので本来は #NAME? が出るはずです。ところがIFERRORが空白に置き換えるため、D列がすべて空白になり、ミスに気づけません。

同じように、数量のC列を誤って削除すると、数式は =IFERROR(B2/#REF!,"") に変わります。これも空白になるだけで、参照が壊れたことは画面に出てきません。

3つの書き方で、起きたことごとの表示を比べると次のとおり。

起きたこと=B2/C2=IFERROR(B2/C2,"")=IF(C2=0,"",B2/C2)
数量が0#DIV/0!空白空白
関数名の打ち間違い#NAME?空白#NAME?
数量の列を削除#REF!空白#REF!

IFで分母を判定する形なら、0の行だけを空白にしつつ、それ以外のミスは表示されたまま残ります。#NAME? そのものの直し方は#NAME?エラーの原因と直し方で扱っています。#REF! については#REF!エラーの原因と直し方をどうぞ。

空白にするか0にするかの判断表

#DIV/0! を何に置き換えるかは、表をどう使うかで決めます。

表の使い方返す値数式例
見せる・印刷するだけ空白=IF(C2=0,"",B2/C2)
列の平均を出す(対象外の行は除く)空白=IF(C2=0,"",B2/C2)
目標未設定の行も0%として平均に含めたい0=IF(C2,B2/C2,0)
未設定の行に気づきたい文字=IF(C2=0,"目標未設定",B2/C2)
他の人に渡す・後で数式を直す空白(IFで判定)IFERRORは避ける

迷ったら、IFで分母を判定して空白を返す形から始めると安全です。関数の使い分け全般はIF・IFS・IFERROR・IFNAの違いと使い分けで比較しています。

合計・平均・グラフに使う表の注意点

範囲に #DIV/0! が1つでも残っていると、その範囲をSUMやAVERAGEで集計した結果もエラーになります。修正前の達成率の表で =AVERAGE(D2:D6) を計算すると、結果は #DIV/0!。

空白にするか0にするかでも、平均は大きく変わります。AVERAGEは文字列を無視し、0は計算に含めるためです。

D列の数式D4〜D6の表示=AVERAGE(D2:D6) の結果
=B2/C2#DIV/0!#DIV/0!
=IF(C2=0,"",B2/C2)空白102.5%
=IF(C2,B2/C2,0)0%41.0%

空白の場合は佐藤さんと鈴木さんの2人だけが対象で、(120%+85%)÷2=102.5%。0の場合は未入力の6行目まで0%として数えられ、(120%+85%+0%+0%+0%)÷5=41.0%に下がります。

グラフの元データに使う場合も、空白("")と0のどちらを返すかで折れ線や棒の見え方が変わることがあります。使っている環境で一度グラフを描いて確かめてから、返す値を決めてください。

#DIV/0!が消えないときの確認ポイント

分母が文字列の “” になっている

=IF(C2=0,"",B2/C2) にしたのに、エラーが残る行があるケース。分母のセルを選んで、数式バーを見てください。

=IF(…,"") のような数式が入っていれば、分母は "" という文字列かもしれません。C2=0 だけでは捕まえきれないことがあるので、C2="" も判定する形に置き換えます。

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

スペースなど見えない文字が入っている

分母のセルにスペースだけが入っていると、見た目は空白でも中身は文字です。スペースは "" とも0とも一致しないので、ORで判定する形もすり抜けてしまいます。

この場合は割り算でエラーが出たままになり、#VALUE! などが表示されることがあります。対処は、該当セルを選んでDeleteキーで中身を消すこと。

範囲が広いときは、置換機能でスペースをまとめて削除できます。スペース以外の見えない文字の探し方は#VALUE!エラーの直し方にまとめました。

コピー元の参照がずれている

全員共通の目標をF1に1つだけ入れ、D2に =B2/F1 と書いて下へコピーしたとします。D3は =B3/F2、D4は =B4/F3 と参照が1行ずつずれ、空白のF2・F3で割ることになります。

その結果、2行目以外がすべて #DIV/0!。分母を固定したいときは、$を付けた絶対参照にしましょう。

=B2/$F$1

F1が100、B2が120なら、D2は120%です。下へコピーしても分母はF1のまま変わりません。

よくある質問(FAQ)

#DIV/0! は放置してもいい?

自分だけが使う作業途中の表なら、すぐに困らないこともあります。ただ、その列を平均・合計すると結果も #DIV/0! になります。

人に渡す表や集計に使う表では、IFで分母を判定して空白にしておきましょう。

IFERRORは古いExcelでも使える?

IFERRORはExcel 2007以降で使えます。それより前のExcelでは、IFとISERRORを組み合わせて同じ処理を書けます。

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

この書き方も、IFERRORと同じくすべてのエラーを空白に置き換える点に注意してください。

#DIV/0! だけを見分ける方法はある?

ERROR.TYPE関数を使うと、エラーの種類を番号で確認できます。#DIV/0! なら2が返り、エラーでないセルに使うと #N/A が返ります。

=ERROR.TYPE(D4)

ただ、数式の中で #DIV/0! だけを消したいなら、分母をIFで判定するほうがシンプルです。

AVERAGEAに変えれば #DIV/0! にならない?

AVERAGEAは文字列や "" を0として数え、空白セルは無視します。そのため "" だけの範囲なら、#DIV/0! ではなく結果は0。

一方で、"" が0として平均に入るぶん、平均値は下がります。エラーを消す目的で関数を変えるのはおすすめしません。

まとめ

Excelの #DIV/0! は、分母が0か空白のときに出るエラーです。押さえておきたいポイントを整理します。

  • 分母が0・空白セル・未入力の行は、=IF(C2=0,"",B2/C2) で空白にできる
  • 分母が "" になる表は、=IF(OR(C2=0,C2=""),"",B2/C2) を使う
  • AVERAGE・AVERAGEIFの対象0件は、COUNT・COUNTIFで件数を判定してから平均する
  • IFERRORは短く書けるが、#REF!・#NAME? などのミスまで空白にしてしまう
  • 平均に使う列は空白を返すと、未入力の行が平均から外れる

まずは自分の表の分母を確認し、IFで判定する形に書き換えてみてください。#DIV/0! 以外のエラーが出たときは、Excelのエラー値一覧から原因と対処を探せます。

タイトルとURLをコピーしました