目次
EXCELで正しく計算したいなら気をつけるべきこと
僕も無茶苦茶使います。
でも、コンピュータソフトである限り「正しい計算結果」を得るには、ちょっとだけ気をつけるべきことがあるのですが、忘れがちです。
意図したのと違う結果がでることがあるのは仕様です
コンピュータは「２進数」がベースです。
コンピュータは、回路への電圧で数字を記憶する仕組みがベースであり、かつ昔の技術だと微妙な電力差で判定すると間違うリスクが高いので「電力がかかっていれば１、かかってなければ０」とした関係上、１つの回路が記憶できるのは０か１の２つだけになっているからです。
なので、人間が扱う10進数の数字も、内部的には２進数に変換して計算されます。
ところが、少数を「2進数」にきれいに変換できない場合があり、同じパターンや数字が延々と続いてしまったりするので、そういう時はどっかで丸め込んでやらないといけないので、そこで計算誤差がでるのは仕方ない・・そういうお約束の上で動いてます。
EXCELも例外ではありません。
何も対策せずに計算したら、以下の例のようなおかしな結果になります。
A列からB列の数字を引いた答えは全部「0.3」なのに、IF文で「A-B」の列が0.3と等しければOKという条件で「NG」で表示されている箇所があるのは、人間から見ると明らかにおかしいですよね。
でも、コンピュータの暗黙のお約束ごとなので、仕方ないのです。
EXCEL2019だと小数点以下15桁目で誤差がでるみたい
この現象の理由は、書式設定で小数点以下15桁目まで表示させてみるとわかります。
下の2行（NG）だけ「A-B」の結果が「0.300000000000001」になってます。
これと、0.3・・つまり「0.300000000000000」と比較したら当然不一致です。
では、この最後の「1」はどこからでてきたか・・ですが。
これが前に書いたように「2進数」に変換するときに、きれいに変換できないので、どこまで丸めて調整するという約束事によって発生した誤差というわけです。
これは「不具合」ではありません。
EXCELでは、小数点以下15桁目にあたる部分にその誤差を反映していますよという単なる仕様の問題ですし、IEEE 754という標準で定められている通りに計算したら、そういう結果になりましたということでしかありません。
つまり「そういうもんだ」と理解するしかないわけです。
仕組み上の誤差の影響をうけないようにする工夫
このことは、EXCELに限らず、どんなプログラム言語でも同じです。
少数は、通常、floatとかdoubleなどの浮動小数点数として扱うので、何も工夫しないで計算してしまうと、同じようにまるめ誤差がでてしまいます。
もちろん、実務上では、金額計算などで誤差がでてしまっては困るので、プログラミング言語側の工夫で、誤差のでない特別な型を用意していたりするのですが、EXCELのセルの計算式はそうではないよというわけです。
他のプログラミング言語でも、浮動小数点とかを使う時には予測される誤差を回避するための工夫をします。
よくあるやり方が、仕組み上のまるめ誤差がでるより前の桁・たとえば、小数点以下の4桁目・・で切り捨てるとか切り上げるみたいなルールを決めて、その誤差が実際の処理に影響しないようにすることです。
それは、EXCELで計算式を使うときでも同じなのです。
EXCELのROUND関数を計算式には必ず使うべき
EXCELのまるめは「ROUND」関数を使います。
- ROUND(四捨五入）
- ROUNDUP（常に切り上げ）
- ROUNDDOWN（常に切り下げ）
です。
計算式を書いたら、自分の決めたルールに従ってROUND系関数で囲む。
これを徹底するだけで、誤差の影響はほぼ気にしなくてよくなります。
先ほどの例でも。
=A9-B9
のように、ただ計算式を書いている部分を
=ROUND(A9-B9,1)
とすれば、正しい計算結果になります。
上記で小数点以下を「1桁」にしているのは、たまたまIF文が「0.3」だからそれにあわせただけで特に意味はありませんけど、書式設定で見せる桁数にあわせておくと経験的にはいい感じだと思ってます。
重要なのは「計算式すべて、同じROUND関数でくくる」こと。
単純な計算結果で誤差が気になるケースって、そうないですけど、IFやlookup系の検索関数などで意図せず不一致になるなどのアクシデントを予防するには、必要です。
僕もプログラムのテストで痛い目にあいました
ROUND関数でくくるだけ・・といっても、徹底するのは面倒です（笑）。
しなくても、ほぼほぼはうまくいきますから。
でも、一度だけ、こっぴどい目にあいました。
仕事で開発したシステムのテスト検証作業をしていて、ケースが複雑なので、EXCELで正解データを作って、それと比較することでＯＫ、ＮＧを判断するようにしてました。
それで全部ＯＫとなって、資料上も問題ないので、リリースしたわけですが。
バグ報告がきました。
それで調査してたら、なんとＥＸＣＥＬで作った正解データの一部が間違っていて、それがプログラムでも同じ計算誤差がでるバグだったのでＯＫになっていたというのがわかって、大反省でした。
全体で２か所、ＲＯＵＮＤが漏れていただけだったんですけどね(´;ω;｀)。
テストの正解データを作るシートなんて、徹底的にチェックしないといけないのに、まさに油断で・・大反省してます。
気を付けましょう・・ということで。
