EXCELの単純な計算に誤差があるようです。12.3-12=0.3000000000000010000となり0.3にならず困っています。もし、どのEXCELも同じ現象なら大きな問題です。

7.3-7=0.3 になりますが、8.3-8=0.3になりません。以下はその単純な計算の結果をEXCELからコピーしたものです。

 A1セル               B1セル              A1-B1の結果

12.300000000000000000000000 12.000000000000000000000000 0.30000000000000100000000

この結果、0.3のセルを検索しても出て来ず業務に支障を来たしてます。対策はROUND関数を使うことですが、こんな事はあり得ないと思います。また、MOD関数(あまり計算)でも誤差が発生し使い物になりません。

 

質問情報


最終更新日 2019年9月21日 表示 38,934 適用先:

* 小さ目のページ番号でお試しください。

* 番号のみをご入力ください。

* 小さ目のページ番号でお試しください。

* 番号のみをご入力ください。

Excelに限らず、コンピュータすべてに当てはまる話です。

ただ、Excelはこの辺りを結構サービスしてごまかしてるので、気が付かないケースもあります。

理由はコンピュータはすべて二進数で処理するわけですが、小数点以下の数字は

2^-n の和で表現されるため、ほとんどの数値が循環小数になり正確には表現できません。

例えば、0.1なんかもだめです。

ですから、Excelに限らず、浮動小数点の計算結果どうしの比較とかを=でやると一致しないってことがざらにあります。

ですから、結果が0かどうかの判別なんかは、微小数以下になったら0とみなす等の工夫が必要になります。

参考まで。

この回答は 18 人の役に立ちました

·

この回答で問題は解決しましたか?

役に立ちませんでした。

回答としてマークしていただきありがとうございます。

この返信の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。今後のサイト改善に役立てて参ります。

この回答の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。

解説、ありがとうございます。

0.1、0.2、0.3、0.4、0.7、0.8が誤差を発生します。

通常の計算なら誤差で片づけられるオーダーの数値ですから今まで全く気が付きませんでした。

2進数の処理では当たり前に発生する誤差、と言う事ですがこれではエクセルで計算したり統計取ったりするにも十分な注意が必要、と言う事ですね?

今作業をしている統計表ではそのセル(単純引き算した結果)の値が0.1か0.3か0.7か0.9によってカウントするようにしています。それでこの誤差に気付きROUND関数で処理しています。

回答戴いた方の「微小数点以下になったら0とみなす」と言うのは具体的に教えて戴けませんか?

この回答は 6 人の役に立ちました

·

この回答で問題は解決しましたか?

役に立ちませんでした。

回答としてマークしていただきありがとうございます。

この返信の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。今後のサイト改善に役立てて参ります。

この回答の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。

例えば、計算結果がA1に入っていてこれが、0.1かどうかチェックするとしたら、

=ABS(A1-0.1)<1E-10

とかで判別します。

ただ、ここで注意を要するのは、Excelの浮動小数点の有効数字は15ケタ程であるということです。

どういう意味かというと、二つの数字の小数点を同じ位置に置き、両方の数字の頭としっぽの間が15ケタを超えると足し算とか引き算とかが正しくできなくなることです。

ですから、↑の例では計算結果が0かどうか判定するのに、1E-10くらいで十分と言う考え方に基づいています。

仮に1E-30なんてやると実質的に0になり、それより小さいなんてことは発生しなくなっちゃいますから0.1ではないと判定されてしまうでしょう。

また、逆に計算結果が1E-5と言った元々小さい数値になるような場合、

=ABS(A1-1E-5)<1E-15

と言ったように”微小”のレベルを事前にちゃんと考えておかなければなりません。

ご自分でもおっしゃっておられるように、計算結果の小数点以下の桁数が3ケタとかいうことが分かっている場合は5ケタ目くらいでRoundしてしまえばよろしいかと思います。

基本さえ分かっていれば方法はなんでも良いわけです。

本来こういう誤差は事実上すべての計算で発生しているのですが、Excelはある程度システムでそれを調整してくれているらしく、大抵の場合こんなことを意識しないでもセーフになってしまい、手に負えなくなると質問されたような現象が表にでてくるので、精密な数値計算をやる場合には却って厄介かもしれません。

この回答は 9 人の役に立ちました

·

この回答で問題は解決しましたか?

役に立ちませんでした。

回答としてマークしていただきありがとうございます。

この返信の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。今後のサイト改善に役立てて参ります。

この回答の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。

丸め誤差が発生する原因や対処法については以下のページが参考になると思います。

Excel で浮動小数点演算の結果が正しくない場合がある

http://support.microsoft.com/kb/78113/ja

いろいろな誤差対策―― 微小値とROUND関数を使いこなす

http://pc.nikkeibp.co.jp/pc21/special/gosa/eg3.shtml

ROUNDUP関数の結果について/EXCEL

http://answers.microsoft.com/ja-jp/office/forum/office_2007-excel/roundup%e9%96%a2%e6%95%b0%e3%81%ae%e7%b5%90/eff3eec1-f12f-4d36-8062-93fc4b1c0d0f

このような誤差は、小数点以下の数値だけでなく、時間などをオートフィルで連続データを作成するときにも発生しますので、大小関係などを判定する場合には注意が必要です。

たとえばA列に「1:00」をオートフィルコピーして「2:00」「3:00」・・・のデータは、B列に手入したデータと異なることが「=A1-B1=0」のような数式を入力することによりわかります。

なお、この場合、「=A1=B1」の判定式なら、Excel2013ではすべて「TRUE」の判定になりますが(Excel2010では「2:00」が「FALSE」になるなど、バージョンによって結果が異なります)、「1:00」「2:00」の2つのセルをオートフィルして連続データを作成した場合は「5:00」が「FALSE」になります。

このように時間を使った表での大小関係を判定する場合は誤差が出ますので、計算値(オートフィル入力値)をTEXT関数で処理して対応するなどして、手入力データと計算値が一致するように工夫する必要があります。

この回答は 6 人の役に立ちました

·

この回答で問題は解決しましたか?

役に立ちませんでした。

回答としてマークしていただきありがとうございます。

この返信の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。今後のサイト改善に役立てて参ります。

この回答の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。

先ほど返信したのですがアップできていないようですので改めて返信します。

詳細な解説ありがとうございます。また、返信が遅れて申し訳ございません。

単純な計算でも中は複雑だと言う事、改めて感じました。こういう現象は理論的に改善できないものなのでしょうか?質問ついでで申し訳ないのですが、円周率を計算したら誤差は発生するのでしょうか?

永年EXCEL使ってますが、ちょっとEXCEL不信に陥りそうでしたが、理屈を解説して頂いて安心しました。

この回答で問題は解決しましたか?

役に立ちませんでした。

回答としてマークしていただきありがとうございます。

この返信の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。今後のサイト改善に役立てて参ります。

この回答の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。

円周率を計算したらとはどういう意味でしょうか?

円周率を多数桁求めたいということでしょうか?

もし、PI()がどこまで信用できるかということでしたら、

3.14159265358979

となるわけですが、正しい値は

3.141592653589793238462643383279・・・・・・

ですから、有効数字15ケタの範囲では十分信用できます。

この回答で問題は解決しましたか?

役に立ちませんでした。

回答としてマークしていただきありがとうございます。

この返信の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。今後のサイト改善に役立てて参ります。

この回答の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。

円周率の求め方を知らないので確かめようがないのですが、関数のPI()ならば15桁の数字がプログラムされていると言う事ですね?で、「正しい値」とはEXCELによる計算で求めたものですか?求め方があったかどうかも忘れてます。すいません。

この回答で問題は解決しましたか?

役に立ちませんでした。

回答としてマークしていただきありがとうございます。

この返信の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。今後のサイト改善に役立てて参ります。

この回答の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。

>関数のPI()ならば15桁の数字がプログラムされていると言う事ですね?

Excelの中でどう実装されているのかは分かりませんが、実質的にはそのとおりです。

ただ、逆に言えばπに限らずExcelの範囲では有効数字15ケタ以上の精度は望めません。

数値計算の式の組み立てによっては精度がそれより落ちる場合もあり得ます。

>、「正しい値」とはEXCELによる計算で求めたものですか

数年前、Excelというより、VBで長桁数計算のExcel用COMアドインを作成した時、サンプルとして私が計算したものですが、πの数千桁の値はインターネット上にいくらでも転がってますから、気になるなら探してください。

この回答で問題は解決しましたか?

役に立ちませんでした。

回答としてマークしていただきありがとうございます。

この返信の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。今後のサイト改善に役立てて参ります。

この回答の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。

この辺が参考になると思います。

上でも紹介されてますが、Excelの問題ではなく

2 進数の小数の表現方法の問題ですね。

詳細を知りたいのであればExcelではなく 2 進数やパソコンそのものの基礎をを勉強しましょう。

http://pc.nikkeibp.co.jp/pc21/special/gosa/eg4.shtml

この回答で問題は解決しましたか?

役に立ちませんでした。

回答としてマークしていただきありがとうございます。

この返信の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。今後のサイト改善に役立てて参ります。

この回答の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。

sakudaさん、nekoさん、いろいろとありがとうございました。

自分の知識不足はさておき、皆さんの豊富な知識に驚きです。2進数やパソコンの基礎、を今更勉強する気にはなれないので、そういうものだと言う事を理解してEXCELを利用します。

それにしてもΠのVBによる計算、Netで探してみます。

この度は本当にありがとうございました。

この回答で問題は解決しましたか?

役に立ちませんでした。

回答としてマークしていただきありがとうございます。

この返信の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。今後のサイト改善に役立てて参ります。

この回答の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。

* 小さ目のページ番号でお試しください。

* 番号のみをご入力ください。

* 小さ目のページ番号でお試しください。

* 番号のみをご入力ください。