EXCELで計算式を書いたら、ROUND系関数で囲む。
計算誤差で困りたくなければ徹底したほうが良い。
これが僕がここで言いたい事です。
EXCELであっても計算誤差はでる
コンピュータは「２進数」がベースです。
人間が扱う10進数も、内部的には２進数に変換して計算されます。
10進数は、2進数に変換すると循環小数になるものがあります。
割り切れないのでどっかで丸めないといけません。
その丸めによって計算誤差がでます。
コンピュータは、そういうお約束の上で動いてます。
EXCELも例外ではありません。
例えば。
A列からB列の数字を引いた答えは全部「0.3」である表です。
ここに、IF文で「A-B」の列が0.3と等しければOKという条件式を書きました。
ここで「NG」で表示されている箇所があります。
人間から見ると明らかにおかしいです。
でも、EXCELで普通に計算したら、こういうことが起こりえます。
この現象の理由は、書式設定で小数点以下15桁目まで表示させてみるとわかります。
下の2行（NG）だけ「A-B」の結果が「0.300000000000001」になってます。
これと、0.3・・つまり「0.300000000000000」と比較したら当然不一致です。
この最後の「1」はどこからでてきたか・・ですが。
これが前に書いたように丸めて調整する約束事で発生した誤差です。
仕組み上の誤差の影響をうけないようにする工夫
これを回避する簡単なやり方。
それは「仕組み上のまるめ誤差がでるより前の桁で丸めるルールにする」です。
そうすれば、理屈上、その誤差は処理結果に影響しません。
EXCELの場合、ルールをきめて、「ROUND」関数を使います。
- ROUND(四捨五入）
- ROUNDUP（常に切り上げ）
- ROUNDDOWN（常に切り下げ）
です。
先ほどの例でも。
=A9-B9
のように、ただ計算式を書いている部分を
小数点以下一桁までとするルールを決めて
=ROUND(A9-B9,1)
とすれば、正しい計算結果になります。
小数点以下を「1桁」にしたのは、IF文が「0.3」だからです。
プログラムのテストで痛い目にあいました
僕は一度こっぴどい目にあいました。
仕事で開発したシステムのテスト検証作業をしたときです。
EXCELで正解データを作って、それと比較してＯＫ、ＮＧを判断してました。
それで全部ＯＫとなったので、リリースしたわけですが。
トラブルが発生し、バグ報告がきました。
調べると、計算式で２か所、ＲＯＵＮＤ関数でくくっていないところがありました。
正解データの反転が上記の誤差で正しくない値になっていました。
それで、プログラムのバグが見落とされてたのです。
なので・・気を付けましょう・・ということで。
