わえなび ワード&エクセル問題集

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】0で割る計算をすると「#DIV/0!」のエラーになる理由とエラーを無くす方法

Excelで、ゼロで割る計算をすると「#DIV/0!」と表示されます。割り算で空白セルを参照すると数値の0とみなされるため、このエラーが表示されます。これを「ゼロ除算」といいます。「#DIV/0!」のエラーを無くすには、IFERROR関数等を用いて空白またはハイフン等を表示するのが正しいです。

しかし、「#DIV/0!」の代わりに「0」を表示してはいけません。絶対に「0」にしてはいけません!!!ゼロ除算を「0」と混同している時点で、ゼロ除算の意味を理解していないと言っても過言ではありません。

そこで、今回は、ゼロ除算がなぜエラーなのか、なぜ0にしてはいけないのか、ゼロ除算のエラーを無くす方法について出題します。

目次

1.#DIV/0!エラーとは何か

問題

次の図で、昨年度と今年度の各店舗の売上について対昨年度比を求めなさい。ただし、長野は昨年度末で閉店し、名古屋と那覇については今年度新たに開店した店舗であり、ハワイ支店は来年度以降開店予定である。

f:id:waenavi:20201208100500j:plain

 

解説

対昨年度比は、昨年度から今年度にかけて売上が上昇したか下降したかを見る比率であり、今年度を昨年度で割って求めます(参考:割合の計算が分からない原因は、かけ算による変化を理解していないこと)。

  • =C2/B2

f:id:waenavi:20201208100918j:plain

 

昨年度と今年度で営業していればその比率を求めることができますが、開店していない場合又は閉店してしまった場合が問題となります。

f:id:waenavi:20201208102940j:plain

 

まず、昨年度末で閉店してしまった場合は、売上を0とみなして、昨年度比を0倍=0%としても問題ありません。

f:id:waenavi:20201209195053j:plain

 

Excelでも、0%と表示されます。しかし、昨年度営業していない店については「#DIV/0!」のエラーとなります。

f:id:waenavi:20201209200216j:plain

 

昨年度に「0」と入力しても同じです。

f:id:waenavi:20201209200314j:plain

 

「#DIV/0!」とは、ゼロ除算divide by zero)のエラーであり、0で割ることができないことを表します。

f:id:waenavi:20201209202449j:plain

 

0で割る計算をするとExcelだけでなく、電卓でもエラーとなります。

f:id:waenavi:20201209222104j:plain

 

2.ゼロ除算が数学的にダメな理由

(1)0÷X

問題

0÷2=0のように、0を、0以外の数で割ると答えが0になる理由を述べなさい。

f:id:waenavi:20201209222650j:plain

 

解説

0個のお菓子を何人で分けても0個になるのは当たり前のことですが・・・

割り算はもともと掛け算の反対となる計算です。例えば、6を2で割ったらいくつになるかという計算は、2を何倍したら6になるかという計算をしているのと同じです。

f:id:waenavi:20201211110348j:plain

 

2を「3倍」すると6になるので、6÷2=3なのです。

f:id:waenavi:20201211114815j:plain

 

0を2で割ったらいくつになるかという計算は、2を何倍したら0になるかという計算をしているのと同じです。2を「0倍」すると0になるので、0÷2=0なのです。

f:id:waenavi:20201211115034j:plain

 

上記の昨年度比の例では、閉店して売り上げが無くなった場合は「0倍」なので、0%と表示するのは正しいです。

f:id:waenavi:20201209195053j:plain

 

(2)X÷0、0÷0

問題

6÷0、0÷0のように、0で割る計算をしてはいけない理由を述べなさい。

f:id:waenavi:20201211115251j:plain

 

解説

前述のように、掛け算が成り立たなければ、割り算はできないのです。6÷0は、0を何倍すると6になるかということであり、0を何倍しても6にはならないので答えが無いのです(解なし)。

f:id:waenavi:20201211115625j:plain

 

 

また、0÷0は、0を何倍すると0になるかということであり、0を何倍しても0にはなるので答えが1つに決まらないのです。

f:id:waenavi:20201211115858j:plain

 

いずれにしても0で割る計算は数学的にあり得ない計算です。Excelでは、「#DIV/0!」のエラーを表示することによって数式の修正を促しています。

f:id:waenavi:20201211120159j:plain

 

(3)強制的に答えを0としてよいか

問題

「#DIV/0」のエラーが出た店舗について、強制的に0%と表示してもよいか。 

f:id:waenavi:20201211120416j:plain

 

解説

エラーが出た店舗については、IFERROR関数またはIF関数を用いることによって別の数値を表示することができます。しかし、このように無理やり「0」を表示するのは間違いです。

  • =IFERROR(C1/B1,0)
  • =IF(B1,C1/B1,0)

f:id:waenavi:20201211120519j:plain

 

現実的に考えても、全く店舗が無かったところから1年間頑張って他の店舗と同じくらいの売り上げを作ったにもかかわらず、閉店した店舗と同じ「0」と評価されるのは理不尽と言うべきです。それどころか、既存の店舗以上の上昇率です。

f:id:waenavi:20201211121406j:plain

 

比率が0であるというのは、「ある変化によって、変化後の数値が0になった」ことを表します。つまり、対昨年度比が0になるのは今年度が0だった場合(閉店した場合)に限られます。

f:id:waenavi:20201211120817j:plain

 

例えば、名古屋支店の昨年度が4180だった場合、10倍なので1000%となります。

f:id:waenavi:20201211121002j:plain

 

昨年度が418だったら、100倍なので10000%となります。昨年度の値をどんどん小さくしていくと倍率はどんどん大きくなります。

f:id:waenavi:20201211121028j:plain

 

0に近づけていくと倍率は最終的に「無限大」になります。

f:id:waenavi:20201211121143j:plain

 

高校数学ではこれを「+∞」と表記します。無理やり表示するのであれば0ではなく「+∞」とするべきです。

f:id:waenavi:20201211121733j:plain

 

ハワイ支店については変化が無かったのは事実なので「0」と表示しても必ずしも不適切とは言えません。なお、数学的には前述のとおり0/0は定義されない値なので誤りです。

f:id:waenavi:20201211121948j:plain

 

3.Excelでゼロ除算を回避する

問題

昨年度から今年度にかけて開店または閉店する支店がある場合、対昨年度比はどのように表示するべきか。また、グラフを作成する場合、どのように表示するべきか。

f:id:waenavi:20201211122856j:plain

 

解説

(1)元の数がある場合

昨年度の売上があって、閉店したため今年度の売上が無い場合、対昨年度の比率は0倍=0%と表示するのは正しいと言えます。

f:id:waenavi:20201209200216j:plain

 

前述のとおり、対昨年度比=0(伸び率はマイナス100%になる)は、昨年度の実績があるのに何らかの事情で今年度の実績がない場合を表します。このように、元の数が0でなければ、割り算の答えとして0と表示するのは正しいです。

f:id:waenavi:20201211120817j:plain

 

(2)分母が0の場合

対昨年度比とは昨年度と比較したときの倍率です。昨年度の実績が無いのに比較するのは不可能であり、無意味です。対昨年度比が数値として表示されるということは、昨年度の実績があることを意味しているので、昨年度の実績がない場合はいかなる数値を表示することも許されません

f:id:waenavi:20201211124449j:plain

 

この場合は、IFERROR関数またはIF関数を用いることによって、空白にしておくかまたは、数値ではないデータを表示するべきです。

  • =IFERROR(C2/B2,"-")
  • =IF(B2,C2/B2,"-")

f:id:waenavi:20201211124544j:plain

 

さらに、昨年度実績がない店舗は対昨年度比を表示することができない旨の注意書きを書いておくのとベターです。

f:id:waenavi:20201211125214j:plain

 

昨年度の実績のない店舗については、グラフ化する必要性が無いので、できるだけグラフ作成範囲から除外するべきです。

f:id:waenavi:20201211125424j:plain

 

4.関数でゼロ除算になることがある

問題

セル範囲B2:B6の平均値をAVERAGE関数で求めた。セル範囲B2:B6が全て空白の場合はどのように表示すればよいか。

f:id:waenavi:20201211125946j:plain

 

解説

AVERAGE関数を用いて、空白の範囲の平均値を求めると、「#DIV/0」のエラーとなります。

f:id:waenavi:20201211130022j:plain

 

平均を求める計算は「合計÷個数」をしているので、数値の個数が0個の場合はゼロ除算のエラーとなります(参考:【Excel】IFERROR関数を用いてエラーを回避する方法、ISERRORとの違いについて)。このように、関数自体に割り算が含まれる場合、ゼロ除算のエラーが生じることがあります。

f:id:waenavi:20201004111201j:plain

 

これを「0」と表示するのは間違いです。

f:id:waenavi:20201211130208j:plain

 

もともとAVERAGE関数は、セル範囲に空白セルがあればそれを除外して計算します。空白を「0」とみなして計算しているわけではありません。0と空白セルでは合計は同じでも数値の個数が異なるので、平均も異なります。

f:id:waenavi:20201211130618j:plain

 

すべてが空白の場合は、すべてのセルが計算対象から除外されるので計算できません(正確に言えば平均が定義できない)。それにもかかわらず、空白セルに0が入力されているものとみなして、「0」と表示するのは矛盾しているのです。

f:id:waenavi:20201211130840j:plain

 

いっぱんにExcel関数でゼロ除算のエラーが出る場合は計算不能の状態であることを表すため、そのエラーの代わりに「0」を表示するのは誤りです。AVERAGEで計算対象が無いことを表すには、空白を返すかまたは数値でないデータを表示するべきです。

  • =IF(COUNT(B2:B6),AVERAGE(B2:B6),"-")
  • =IFERROR(AVERAGE(B2:B6),"-")

f:id:waenavi:20201211130957j:plain

 

5.数値を表示するように指示されたらどうするか

残念ながら、無能な経営者または管理職から「エラーの代わりに何らかの数値を表示するべきだ」などと意味不明な指示を受けることがあるようです。気の毒としか言いようがありません。経営者または管理職がゼロ除算すら理解できていないというのは問題外です。数字が読めない以前の問題です。

ゼロ除算はエラーであって、エラー以外の何物でもありません。ゼロ除算と「0」は異なるものです。何らかの数値を表示することは絶対に不可能であり、断固として断るべきです。

 

6.補足

(1)NA関数で#N/Aのエラーを表示する

ゼロ除算の#DIV/0!のまま折れ線グラフを描くと0として表示されます。これは間違いです。

f:id:waenavi:20201211131253j:plain

 

ゼロ除算をIF関数で回避するときに、文字列ではなく、NA関数を用いると#N/Aのエラーを返すことができます。

  • =IF(B2,C2/B2,NA())

#N/Aのエラーにすると折れ線グラフには表示されなくなります。比率が定義できないものはグラフにも表示しないのが正しいです。

f:id:waenavi:20201211131409j:plain

 

(2)エラーを無視して合計などを求める

セルB2:B6の合計を求めるときに、その範囲内にゼロ除算等のエラー値があったら合計することができません。

f:id:waenavi:20201211131721j:plain

 

エラー値を除外して合計を求めるにはSUMではなくAGGREGATE関数を使います。

  • =AGGREGATE(9,6,B2:B6)

f:id:waenavi:20201211131829j:plain

 


解説は以上です。


 


Copyright(C)2018-2021 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ