ベストアンサー

エクセル関数の計算結果が合いません

2019/12/25 20:35

10,012.3 - 9,977.2 = 35.1ですがこの式をTRUNC関数やROUNDDOWN関数で計算すると、答えが合いません。ちなみに画像は、分かりやすいように桁をずらしてあります。分かる方がいらっしゃいましたら、教えてください。

noname#241112

Excel（エクセル）
回答数4
ありがとう数2

みんなの回答 （4）
専門家の回答

質問者が選んだベストアンサー

ベストアンサー

nishi6
ベストアンサー率67% (869/1280)

2019/12/26 04:09 回答No.4

コンピュータ内部で、２進数で数値を表すと、２のべき乗の線形結合(足すとか足さないとか)になります。整数は限度までなら表せますが、小数は1/2、1/4、1/8…1/1024…の和になります。これも結合数の限度があり、はぼ循環小数になってしまいます。そう考えると２進数で考えている限り、コンピュータ内部の小数は正確に表現できている場合は非常にまれだと認識しておく必要があります。 Excelでは、IEEE754倍精度(64bit)仕様(+Microsoft仕様)のはずです。IEEE754倍精度で質問の数値を表現すると、以下になります。符号(1ビット)+指数部(11ビット)+仮数部(52ビット)の64ビットで表現しています。(/や_は区切りです) ________符号/指数部(11)___/仮数部(52) 10012.300//0/100_0000_1100/0011_1000_1110_0010_0110_0110_0110_0110_0110_0110_0110_0110_0110 _9977.200//0/100_0000_1100/0011_0111_1100_1001_1001_1001_1001_1001_1001_1001_1001_1001_1010 「10012.300」の一番右の4ビットは循環小数になっていて次の4ビットが「0110」なので、切り捨てされて、「0110」は「0110」のままです。「_9977.200」の一番右の4ビットも循環小数になっていて次の4ビットが「1001」なので、切り上げされて、「1010」が「1011」になっています。 65ビット目が「0なら切り捨て」、「1なら切り上げ」です。これは、「10012.300」は内部では真値より小さく保持され、「_9977.200」は真値より大きく保持されていることになります。従って、RoundDownが見た目とは違う「35.090」になるわけです。見た目で「35.1」になったとしても、内部では「≠35.1」の可能性があり、次の計算に使ったりすると、予期せぬことが起きたりします。(実務で経験あります) これに対応する対応（実務的な話です）Excelのワークシートでの計算、VBA(VB等)での計算は小数使用を制限(ほぼ禁止)していました。・例えば、30%を計算する場合は、「*0.3」ではなく、　「*30/100 として端数処理を行う」ように決めていた。・シェア(割合、占率)などについても、DBに0.31、0.18、0.51や　31%、18%、51%と持つのではなく、3100、1800、5100と持ち、　最後に端数処理を行っていました。 Excel で浮動小数点演算の結果が正しくない場合がある https://docs.microsoft.com/ja-jp/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result

質問者

お礼 2019/12/26 05:49

分かりやすい詳細な説明ありがとうございました。エクセルの小数計算の精度には限界があるなんて、恥ずかしいながら初めて知りました。また、一つ勉強になりました。

その他の回答 (3)

msMike
ベストアンサー率20% (364/1804)

2019/12/26 00:24 回答No.3

丸め誤差とか浮動小数点問題とか、難しいことは扨て置き、Excel に限らずＰＣの表計算アプリは小数点の計算に弱いと覚えておきましょう。(むしろ、電卓の方が賢い?) 私が採る常套手段は、小数点の有効桁数より"充分に"小さい桁数で四捨五入しろッ！です。お示しの場合は、計算に供される２個の数値は何れも小数点以下１桁なので、ま小数点以下３桁くらいが「充分に小さい桁数」と考えればOKです。だから、=ROUND(A1-A2,3) とするのです。言っておきますが、「充分に」であって、小さ過ぎても好けません。。小数点以下5桁でも構わないけど、15桁はお奨めできません) また、「四捨五入」が肝要であって、切捨ても切上げもお奨めできません。