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

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

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


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

しかしながら、残念なことに、Excelの計算結果を電卓で点検するというとんでもないことをする人がいるようです。馬鹿としか言いようがありません。

 

※これはイメージです。

f:id:waenavi:20180929133923j:plain

 

「Excelの計算結果を信用しない」という人もいるようですが、それは、正しい計算結果を表示できる知識を持っていないだけです。「ペットの性格は飼い主に似る」とよく言いますが、使用する人のレベルが低ければそれに合わせてExcelが動くのです。

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

 

「セルの書式設定」完全マスターシリーズ(この記事は第9回です)
  1. Excel「セルの書式設定」最低限練習すべき設定と解除の特訓問題50問
  2. 【Excel】均等割り付けの設定と解除の方法と、種類を見分ける練習
  3. 表示形式はセルの書式設定なのでセルの値は変わらない(表示形式の基本)
  4. セルの表示形式「ユーザー定義」書式記号完全総まとめ
  5. ユーザー定義表示形式のサンプルの意味を全部解説します
  6. 表示形式で単位を付ける、または単位を変えるときの注意点
  7. Excelで先頭にプラス+をつける、マイナスを三角△で表示する方法
  8. なぜ指数の表示形式「E」が表示されるのか、「E」の設定と解除の方法
  9. Excelの表示形式で「3+3=7」になるのは誤差か?操作ミスか?それとも教育の問題か?

 

目次

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

問題

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

f:id:waenavi:20180929202123j:plain

 

解説

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

f:id:waenavi:20180929202636j:plain

 

小数点以下の桁数の表示を変えることができます。(参考:【Excel】表示形式はセルの書式設定なのでセルの値は変わらない(表示形式の基本)

f:id:waenavi:20180929202638j:plain

 

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

f:id:waenavi:20180929202640j:plain

 

セルはデータと書式の2つの情報を持っています(参考:【Excel】セルにはデータと書式の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

 

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

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

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

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

f:id:waenavi:20180929221507j:plain

 

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

 

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

問題

「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

 

4.円周率を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

 

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

問題

「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

 

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

 

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

問題

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などの関数を使うようにします。

 

7.まとめ

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

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

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

 


解説は以上です。

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


8.動画版はこちら(無料)

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

 

「セルの書式設定」完全マスターシリーズ(この記事は第9回です)
  1. Excel「セルの書式設定」最低限練習すべき設定と解除の特訓問題50問
  2. 【Excel】均等割り付けの設定と解除の方法と、種類を見分ける練習
  3. 表示形式はセルの書式設定なのでセルの値は変わらない(表示形式の基本)
  4. セルの表示形式「ユーザー定義」書式記号完全総まとめ
  5. ユーザー定義表示形式のサンプルの意味を全部解説します
  6. 表示形式で単位を付ける、または単位を変えるときの注意点
  7. Excelで先頭にプラス+をつける、マイナスを三角△で表示する方法
  8. なぜ指数の表示形式「E」が表示されるのか、「E」の設定と解除の方法
  9. Excelの表示形式で「3+3=7」になるのは誤差か?操作ミスか?それとも教育の問題か?

 


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