Excel 計算結果誤差

質問

以下の様に計算させると小数３桁目の丸め方で差がでますなぜでしょうか？

例
(手入力)
　A1　　　　B1　　　　C1　　　　
1.235　　　1.000　　IF(MOD((A1-B1)*1000,10)=5,A1-B1,ROUND(A1-B1,2)) 求める解は0.235ですが0.240と表示されます

通常の4捨5入の処理をされてしまうのですがなぜでしょう教えてください。

回答No.5

Wendy02 · ベストアンサー

こんにちは。

前の回答で、一応、こちらの数式もチェックしていただきたかったですね。

FLOOR(A1,0.005)+(MOD(A1*1000,10)>5)*0.005-B1

MOD((A1-B1)*1000,10) = 5
で、浮動小数点丸め誤差が発生しているから、小数点第14位目が、#2 の方のご指摘のとおりに、9になっていて、　「5」と等しくはなっていないわけです。だから、Excelの場合は、求める桁より外の値は、切り捨てるというのが一般的です。たとえば、時間計算なら、小数点第7位までとか。

整数変換法　
　INT(MOD((A1-B1)*1000,10))=5

小数点固定法
　MOD(FIXED(A1-B1,3)*1000,10)=5

理屈は、両方とも同じです。

私は、なぜ、Microsoft は、このような仕様にしなければならなかったのか、疑問が残っています。今は、比較対象が限られていますから、単にExcelがどうこうという話しか出来ませんが、たぶん、このプログラム的な解決方法は、Lotus,Quadra,IBMなど各会社出してはあったし、かつては、Microsoft も対処していたのですが、Win-Excelになって、それを使えなかったことが原因だと思います。1991～2年頃に、LotusとMicrosoft共同で、この問題については発表がなされています。そうしないと、銀行など経理的な計算では大変な問題が発生する可能性があるからです。しかし、結局、Microsoft の一人勝ちのための、そのまま放置されたとも言えます。

ずいぶん前に、PC-VAN やTex で有名な三重大の奥村先生がこの問題を指摘していらっしゃるのですが、原因は、Microsoft が、「#IEEE 754 の double 型」をそのまま使っているということです。正直なところ、芳坂氏の説明は、丁寧に読みすすめましたが、あの説明では、私には良く分かりません。一応、元は雑誌の記事の内容ですから仕方がないと思います。表計算の場合は、ユーザーに対しては、その表計算上での対処法を提供すれば済むのではないか、と思っています。(maron-5さん、水を差してすみません。気を悪くなされないでください。）

お礼

遅くなりましてすみません。
解決いたしました。
問題の背景も含めご丁寧な回答ありがとう御座います。

回答No.4

maron--5

◆的外れな回答で失礼しました
★これはやはり「演算誤差」でしょうか
◆ならば、下のＵＲＬを一度ご覧ください
「“達人”芳坂和行氏に学ぶ、エクセル(Excel)「演算誤差」対策講座」

参考URL：

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

お礼

遅くなりましてすみません。
Excelを使っていくうえで大変参考になりした。
ありがとう御座います。

回答No.3

maron--5

>求める解は0.235ですが0.240と表示されます
◆でも式が、ROUND(A1-B1,2)　であれば、小数点第3位四捨五入小数点２桁表示のはずですので、0.235なら0.24は正動作だと思いますが？

回答No.2

gen_ma

Excelというよりコンピュータは、小数点以下の計算が苦手だそうです。
これを演算誤差とこのサイトで読んだことがあります。

=MOD((A1-B1)*1000,10)だけの結果ですが
実際excelに入れてみると5.00000000000009になりました。

これが原因では無いでしょうか。

お礼

遅くなりましてすみません。
Excelは計算が苦手とは・・意外した。
Excelを使っていくうえで大変参考になりした。
ありがとう御座います。

回答No.1

zap35

A1、B1の数値を手入力した場合は期待する結果になるのではありませんか？

もしかしてA1、B1の値は、計算式で求めていて、その結果をセルの書式表示で「小数点以下３桁まで表示」にしていないでしょうか。その場合小数点以下４桁目で四捨五入された結果が表示されています。
（例えば1.234999999が1.235と表示されている。だからMOD関数の結果が５にならない）

A1、B1の式を
　=ROUND(元の式,3)
にすれば正しい結果にならないでしょうか

お礼

遅くなりましてすみません。
解決致しました。
ありがとう御座います。