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

ワードとエクセルの練習問題の動画を「Youtube」で無料公開しています

Excelの表示形式で「3+3=7」になるのは誤差か?操作ミスか?それとも教育の問題か?

Excelは高性能な表計算ソフトなので、入力している数値や数式が正しければ、原則として正しい計算をしますが、セルに表示される計算結果が矛盾することがあります。例えば、極端な話ですが、「3+3」の計算結果が「5」と表示されたり「7」と表示されたりするのは、Excelでは当然ありえることです。
Excelを使い慣れている人であれば簡単に理解できる話です。

 

※これはイメージです。

f:id:waenavi:20180929133923j:plain

 

しかしながら、残念なことに、Excelの計算結果を電卓で点検するというとんでもないことをする人がいるようです。電卓とExcelを見比べて計算が合わないと悩んでいても仕方がありません。

計算が合わない原因はいろいろありますが、もし、セルの書式(表示形式)が原因で誤差が発生しているのであれば、それはExcelの問題ではなく、ユーザの問題です。

では、どのようなことに気をつけてExcelを使えばいいのでしょうか?
できるだけ多くの事例を自分の力で考えて、Excelを正しく使うようにしましょう。

 

目次 

 

「3x2=7」になる事例を考えてみましょう

問題

セルA1に「3」と表示されている。セルA2に、これを2倍する計算式を入力したところ「7」になった。考えられる原因を述べなさい。

f:id:waenavi:20180929202123j:plain


解説

例えば、3.4と入力します。

f:id:waenavi:20180929202636j:plain


小数点以下の桁数の表示を変えることができます。(表示形式の基本について、詳しくはファンダメンタルExcel Program2-4 の動画参照)

f:id:waenavi:20180929202638j:plain


このとき四捨五入になって、3になります。

f:id:waenavi:20180929202640j:plain

 

セルはデータと書式の2つの情報を持っています。データは3.4ですが、整数表示の書式によって画面上、3になります。しかし、計算は元のデータで計算されます。

f:id:waenavi:20180929203048j:plain

 

したがってこのセルを2倍すると6.8です。画面上、3を2倍して6.8になります。

f:id:waenavi:20180929203420j:plain

 

さらに、これを整数の表示にすると7になります。

f:id:waenavi:20180929203423j:plain

  

答えが6.8である場合、整数の表示形式にするか、通貨の表示形式にするか、列の幅を極端に狭くすると「7」になります。

f:id:waenavi:20180929203054j:plain

 

列幅が十分にあるのに整数の表示になっている場合は、表示形式を標準に戻せば「6.8」に戻ります。

f:id:waenavi:20180929202642j:plain

 

誤差と言うべきか?操作ミスと言うべきか?

小数の計算で四捨五入などによって誤差が発生することを丸め誤差ということがありますが、さきほどの例では、Excelは、3.4x2=6.8と正しい計算をしていますので、丸め誤差ではありません

Excelが勝手に「3x2=7」としているのではなく、ユーザの操作によって誤差が発生しているのです。ユーザの操作ミスというべきです。小数の表示になっているセルを、意味もなく表示形式によって整数表示にしてはいけません。また、小数点以下の桁数がボタンで簡単に変えられるからと言って、何も考えずに、桁数を変えてはいけません。

次の図のように、小数の表示にしてはいけない場合(人数、個数など)であっても、原則として表示形式を用いて整数の形式にしてはいけません。

f:id:waenavi:20180929221507j:plain

また、やむを得ず小数点以下の桁数を変える場合は、そのセルを計算式で使用してはいけません。参照して計算すると更に誤差が大きくなるからです。小数点以下の桁数を変えるときは、丸める方法(切り上げ、切り捨て、四捨五入、五捨六入など)やルールを決めて、ROUNDなどの関数で丸めるようにします。

 

小数になったときのルールを事前に決めておくこと

問題

「20+20+20=61」となる具体的な事例を考えなさい。

解説

20+20+20で61になっています。

f:id:waenavi:20180929222645j:plain 


これは小数の部分が隠れていて、それを合わせて切り上げになったものと推測できます。

f:id:waenavi:20180929222648j:plain

 

例えば、20.1,20.1,20.7と入力すれば、答えは60.9となり、整数表示にすれば正しい計算です。

f:id:waenavi:20180929222650j:plain

f:id:waenavi:20180929222653j:plain


しかし、20.4にすると、答えは60.6となり、1だけずれます。

f:id:waenavi:20180929222657j:plain

 

たとえば、3つの商品の単価が29円で、30%引き、つまり0.7倍をしました。

f:id:waenavi:20180929222700j:plain

 

実際には20.3円ですが、四捨五入で20円になります。合計すると、20円が3つで、61円になります。このように、割合や分数、パーセンテージの計算をするときには、小数になったときのルールを事前に決めておく必要があります。

f:id:waenavi:20180929222702j:plain

 

円周率を3として計算するのは誤差か?ゆとりか?

問題

「10x10x3=314」となる具体的な事例を考えなさい。

解説

10と10と3をかけて、ちょうど300になるべきところ、314になっています。

f:id:waenavi:20180929223331j:plain


これは、3.14の切り捨てで3と表示されているからです。この場合も、整数の表示になっているセル(A2)を、数式で参照している(=A1*A1*A2)のが間違いです。

f:id:waenavi:20180929223334j:plain

 

例えば、半径10cmの円の面積を、円周率を3.14で計算すれば314cm2になります。
円周率を3とみなして計算するのに、表示形式を変えても答えは変わりません。

f:id:waenavi:20180929223336j:plain


半径10.4cmならさらに誤差は大きくなります。足し算よりも掛け算のほうが誤差が大きくなるので注意が必要です。

f:id:waenavi:20180929223339j:plain

 f:id:waenavi:20180929223342j:plain

 

構成比率と合計が合わない?

問題

「33%+33%+33%=100%」となる具体的な事例を考えなさい。

解説

33%を3つたして100パーセントになっています。パーセンテージも小数ですから誤差は発生します。 

f:id:waenavi:20180929223758j:plain


3分の1(=1/3)を入力すると、33.33%ですが、合計すると99.99%で切り上げで100%になります。

f:id:waenavi:20180929223801j:plain

 

例えば、301人、302人、303人の構成比率を求めると、それぞれ33.2%、33.3%、33.4%になりますが、合計は100%です。小数第2位以下が隠れているため、合計があっていません。

f:id:waenavi:20180929223803j:plain

f:id:waenavi:20180929223805j:plain

 

構成比率とそれを利用して按分をする計算は、合計が合わないことがあります。合計が合わなければどうするかというルールを決めなければいけません。例えば、「丸め誤差によって合計値が合わないことがある」という注意書きを入れるなどの対応が必要です。

 

カンマ桁区切りでお金が合わない?

問題

4,000円と3,000円の商品を200個ずつ買った時の代金が140万円にならない具体的な事例を考えなさい。

解説

4,000円と3,000円の商品を200セット購入して、140万円より多くなっています。

f:id:waenavi:20180929223808j:plain

 

このように通貨の表示やカンマ桁区切りの表示にすると、四捨五入のため誤差が発生します。

f:id:waenavi:20180929224124j:plain

 

例えば、1ドル123.45円の為替レートで、32.4ドルと24.3ドルの商品をそれぞれ200個購入したとします。

f:id:waenavi:20180929224318j:plain


掛け算でドルを円にします。

f:id:waenavi:20180929224321j:plain


表示形式によって、4,000円と3,000円になりますが、小数同士の掛け算なので、本当は小数点以下もあります。

f:id:waenavi:20180929224323j:plain


200倍すると、やはり細かい数字になり、合計すると140万円より少なくなります。

f:id:waenavi:20180929224325j:plain

 

日本円は、計算上小数にすることはあっても、実際に支払うときには小数を使いません。カンマ区切りで整数表示にすることによって計算が合わないことがあります。お金が合わないのは大問題ですから、ROUNDなどの関数を使うようにします。

 

まとめ

Excelを使うユーザは、表示上の誤差が発生するかもしれないということを常に注意しながら操作しなければなりません。誤差の原因はユーザの不注意です。

  • 何も考えずに整数の表示(表示桁数の変更)をしてはいけないこと
  • 表示形式は原則として列単位または行単位で設定し、1つのセルだけ意味もなく書式を変えるのは控えること
  • 小数点以下の桁数の表示を変えるときはルールを設けること
  • 表示形式を設定しているセルを計算式で参照するときは、さらに誤差が大きくなる可能性がないか十分に検討すること
  • 関数の利用を検討すること(特にお金の計算の時にはかならず関数を使用する)

初心者向けのExcelの指導書のなかには、「小数点以下の桁数の変更はボタン1つで簡単にできる!」などとメリットだけ強調して、誤差が発生する危険性をしっかりと記述していないものが見受けられますが、初心者が困らないようにぜひとも改善してほしいところです。

 


解説は以上です。

*補足意見*
Excelのオプションで、表示されている値のまま計算して誤差をなくすことは可能ですが、それは根本的な問題を隠蔽しているだけです。誤差が発生するのは何らかの原因がありますから、ユーザがルールを決めて正しく計算するべきです。 


動画版はこちら(無料)

この記事は、わえなび実力養成講座「Excel新演習3数式・計算式集中講義」Program3-4 の動画の内容を書き起こし、加筆修正したものです。

www.youtube.com

 


Youtube わえなび公式チャンネル
http://www.youtube.com/c/waenavijp

チャンネル登録(無料)をすると最新の動画をチェックできます。
初心者向けの動画が大量にあります。

 

 

 

 

 

Copyright(C)2018 waenavi, All rights reserved. [www.waenavi.com 定礎 平成30年8月]