EXCELの数値計算が間違ってる？

技術メモ

2019.02.28

●EXCELで数値計算していたら

こんな結果が出てびっくり。

どう計算しても、-0.6のはずなのに、小数点以下15桁目が「9」になっている。
何度繰り返してみても結果が同じ。

（EXCELを基本的なオプション設定で使っていると-0.6と表示されるので、この場合は小数点以下の表示桁数を15桁まで広げてみてください）

小数点以下の表示桁数を１桁にすると-0.6と表示される。

いったい、どうなっているのか？

●調べてみました

Microsoft サポートに以下の記事があります。

Excel で浮動小数点演算の結果が正しくない場合がある
https://support.microsoft.com/ja-jp/help/78113/

上記は専門的でわかりにくいので、簡単に解説します。

①パソコンは2進数で計算している

我々が普段使っている数値は10進数ですが、パソコンの内部は2進数なので、２進数で計算して、10進数に変換するということを行っています。

2進数では、多くの小数点以下の数値が循環小数になり誤差が発生します。これは、EXCELに限らずコンピューターを使って計算する限り避けがたいことです。

②15桁の精度で計算している

数値計算の精度を上げると計算速度が遅くなるので、実用的な15桁で計算を行っています。

詳細については、IEEE 754 という規格があります。この規格はコンピューター標準規格を策定している国際的な団体が定めたもので、広く採用されています。

③15桁目に誤差が生じることがある

上記の二つの理由から、数値計算において15桁目に誤差が生じることがあります。

EXCELは一般的な数値計算では誤差が見えないようにしているので、気がつかないで使っている人も多いです。

●対策は？

【一般向け】

こちらの方法で多くはカバーできます。

①表示桁数を設定する

15桁というのはかなり大きな数値を扱うので、表示桁数を設定することで誤差を見かけ上見えなくするということができます。
上記の例では、表示桁数を小数点以下１桁に設定すると、

とりあえず、一般的な使い方であれば問題なくなります。
ただし、数値としては-0.600000000000009となっていること、つまり計算誤差があることは心に留めておいた方が良いです。

【計算精度が必要な人向け】

2進数により発生する誤差が問題になってしまう場合は、計算精度を管理する必要があります。

①ROUND 関数を使って、計算桁数を決める

数値精度を管理することで計算誤差が生じるのを防ぐやり方。
厳密な数値計算をするときには、こちらを使う必要があります。

●参考

こちらも参考になります。

EXCELの単純な計算に誤差があるようです。
12.3-12=0.3000000000000010000となり0.3にならず困っています。もし、どのEXCELも同じ現象なら大きな問題です。
https://answers.microsoft.com/ja-jp/msoffice/forum/all/excel%E3%81%AE%E5%8D%98%E7%B4%94%E3%81%AA%E8%A8%88/e5421c34-8053-4132-a0a6-14729e505376?page=1

コンピューターの専門家向け解説はこちら。

上記にも書かれていますが、厳密な精度を求められる計算では、コンピューター内部で10進数で計算する方法もありますが、計算負荷が高い為一部でしか使われていません。