当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。Excelの表示形式で「3+3=7」になるのは誤差か?操作ミスか?それとも教育の問題か?2018-09-29Excelの表示形式で「3+3=7」になるのは誤差か?操作ミスか?それとも教育の問題か?ExcelExcelは高性能な表計算ソフトなので、入力している数値や数式が正しければ、原則として正しい計算をしますが、セルに表示される計算結果が矛盾することがあります。極端な話ですが、「3+3」の計算結果が「5」と表示されたり「7」と表示されたりするのは、Excelでは当然ありえることです。Excelを使い慣れている人であれば簡単に理解できる話です。※これはイメージです。残念なことに、Excelの計算結果を電卓で点検するというとんでもないことをする人がいるようです。馬鹿としか言いようがありません。「Excelの計算結果を信用しない」という人もいるようですが、それは、正しい計算結果を表示できる知識を持っていないだけです。「ペットの性格は飼い主に似る」とよく言いますが、使用する人のレベルが低ければそれに合わせてExcelが動くのです。どのようなことに気をつけてExcelを使えばいいのでしょうか?できるだけ多くの事例を自分の力で考えて、Excelを正しく使うようにしましょう。まとめユーザー定義表示形式のサンプルの意味を全部解説します表示形式で単位を付ける、または単位を変えるときの注意点Excelで先頭にプラス+をつける、マイナスを三角△で表示する方法なぜ指数の表示形式「E」が表示されるのか、「E」の設定と解除の方法Excelの表示形式で「3+3=7」になるのは誤差か?操作ミスか?それとも教育の問題か?目次1.「3x2=7」になる事例を考えてみましょう2.誤差と言うべきか?操作ミスと言うべきか?3.小数になったときのルールを事前に決めておくこと4.円周率を3として計算するのは誤差か?ゆとりか?5.構成比率と合計が合わない?6.カンマ桁区切りでお金が合わない?7.まとめ8.動画版はこちら(無料)1.「3x2=7」になる事例を考えてみましょう問題セルA1に「3」と表示されている。セルA2に、これを2倍する計算式を入力したところ「7」になった。考えられる原因を述べなさい。小数点以下の桁数の表示を変えることができます。動画参照)このとき四捨五入になって、3になります。セルはデータと書式の2つの情報を持っています。データは3.4ですが、整数表示の書式によって画面上、3になります。計算は元のデータで計算されます。したがってこのセルを2倍すると6.8です。画面上、3を2倍して6.8になります。これを整数の表示にすると7になります。答えが6.8である場合、整数の表示形式にするか、通貨の表示形式にするか、列の幅を極端に狭くすると「7」になります。列幅が十分にあるのに整数の表示になっている場合は、表示形式を標準に戻せば「6.8」に戻ります。2.誤差と言うべきか?操作ミスと言うべきか?小数の計算で四捨五入などによって誤差が発生することを丸め誤差ということがありますが、さきほどの例では、Excelは、3.4x2=6.8と正しい計算をしていますので、丸め誤差ではありません。Excelが勝手に「3x2=7」としているのではなく、ユーザの操作によって誤差が発生しているのです。ユーザの操作ミスというべきです。小数の表示になっているセルを、意味もなく表示形式によって整数表示にしてはいけません。小数点以下の桁数がボタンで簡単に変えられるからと言って、何も考えずに、桁数を変えてはいけません。次の図のように、小数の表示にしてはいけない場合(人数、個数など)であっても、原則として表示形式を用いて整数の形式にしてはいけません。やむを得ず小数点以下の桁数を変える場合は、そのセルを計算式で使用してはいけません。参照して計算すると更に誤差が大きくなるからです。)やルールを決めて、ROUNDなどの関数で丸めるようにします。「20+20+20=61」となる具体的な事例を考えなさい。解説20+20+20で61になっています。これは小数の部分が隠れていて、それを合わせて切り上げになったものと推測できます。20.1,20.1,20.7と入力すれば、答えは60.9となり、整数表示にすれば正しい計算です。20.4にすると、答えは60.6となり、1だけずれます。3つの商品の単価が29円で、30%引き、つまり0.7倍をしました。実際には20.3円ですが、四捨五入で20円になります。合計すると、20円が3つで、61円になります。このように、割合や分数、パーセンテージの計算をするときには、小数になったときのルールを事前に決めておく必要があります。「10x10x3=314」となる具体的な事例を考えなさい。解説10と10と3をかけて、ちょうど300になるべきところ、314になっています。これは、3.14の切り捨てで3と表示されているからです。この場合も、整数の表示になっているセル(A2)を、数式で参照している(=A1*A1*A2)のが間違いです。半径10cmの円の面積を、円周率を3.14で計算すれば314cm2になります。円周率を3とみなして計算するのに、表示形式を変えても答えは変わりません。半径10.4cmならさらに誤差は大きくなります。足し算よりも掛け算のほうが誤差が大きくなるので注意が必要です。「33%+33%+33%=100%」となる具体的な事例を考えなさい。解説33%を3つたして100パーセントになっています。パーセンテージも小数ですから誤差は発生します。3分の1(=1/3)を入力すると、33.33%ですが、合計すると99.99%で切り上げで100%になります。301人、302人、303人の構成比率を求めると、それぞれ33.2%、33.3%、33.4%になりますが、合計は100%です。小数第2位以下が隠れているため、合計があっていません。構成比率とそれを利用して按分をする計算は、合計が合わないことがあります。合計が合わなければどうするかというルールを決めなければいけません。「丸め誤差によって合計値が合わないことがある」という注意書きを入れるなどの対応が必要です。6.カンマ桁区切りでお金が合わない?問題4,000円と3,000円の商品を200個ずつ買った時の代金が140万円にならない具体的な事例を考えなさい。解説4,000円と3,000円の商品を200セット購入して、140万円より多くなっています。このように通貨の表示やカンマ桁区切りの表示にすると、四捨五入のため誤差が発生します。1ドル123.45円の為替レートで、32.4ドルと24.3ドルの商品をそれぞれ200個購入したとします。掛け算でドルを円にします。表示形式によって、4,000円と3,000円になりますが、小数同士の掛け算なので、本当は小数点以下もあります。200倍すると、やはり細かい数字になり、合計すると140万円より少なくなります。日本円は、計算上小数にすることはあっても、実際に支払うときには小数を使いません。カンマ区切りで整数表示にすることによって計算が合わないことがあります。お金が合わないのは大問題ですから、ROUNDなどの関数を使うようにします。7.まとめExcelを使うユーザは、表示上の誤差が発生するかもしれないということを常に注意しながら操作しなければなりません。誤差の原因はユーザの不注意です。何も考えずに整数の表示(表示桁数の変更)をしてはいけないこと表示形式は原則として列単位または行単位で設定し、1つのセルだけ意味もなく書式を変えるのは控えること小数点以下の桁数の表示を変えるときはルールを設けること表示形式を設定しているセルを計算式で参照するときは、さらに誤差が大きくなる可能性がないか十分に検討すること関数の利用を検討すること(特にお金の計算の時にはかならず関数を使用する)初心者向けのExcelの指導書のなかには、「小数点以下の桁数の変更はボタン1つで簡単にできる!」などとメリットだけ強調して、誤差が発生する危険性をしっかりと記述していないものが見受けられますが、初心者が困らないようにぜひとも改善してほしいところです。*補足意見*Excelのオプションで、表示されている値のまま計算して誤差をなくすことは可能ですが、それは根本的な問題を隠蔽しているだけです。誤差が発生するのは何らかの原因がありますから、ユーザがルールを決めて正しく計算するべきです。動画の内容を書き起こし、加筆修正したものです。わえなびチャンネルhttp://www.youtube.com/c/waenavijpチャンネル登録をすると最新の動画をチェックできます。初心者向けの動画も大量にあります。平成三十年八月]スターをつけました引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。