突然ですが、以下の2つの計算問題に答えてください。
- 9.8-9.7はいくつですか？
- 4.8-4.7はいくつですか？
これら二つに”0.1″と答えた人は、残念ながらExcelと意見が合がわないようです。Excelでこれらの計算をさせると、なんと”0.1″以外の数が答えとして返ってきます。
今回はそのカラクリを説明します。
Excelが計算を誤る瞬間!?
序文だけだと著者が嘘をついていると疑われかねませんので、実際にExcelで計算してみましょう。
上のように、2つの計算式を投入しています。答えが出ないように、”数式の表示”を行っています。
“数式の表示”を解くと、、
“0.1”と返ってきました。あれ？やっぱりExcel正しいじゃん、、
と見えますが、以下の赤線から小数点を表示させていってください。
そうすると、、
あら不思議、2つとも0.1より少しだけ差があります。
なぜExcelは計算を間違えた？
振り返ると、A1セルの数式は9.8-97、A2セルは4.8-4.7です。私たちが学校で習った算数の知識を用いれば、どう考えても”0.1″になるはずです。一体ここでは何が起きているんでしょうか？
その答えは、”数字の記し方“にあります。われわれ人間は、数を十進法で数えます。十進法では数を”0,1,2,3,4,5,6,7,8,9″という十個のアラビア数字で表します。われわれが日常使うのは基本的には十進法ですね。
でもこれ、どんな場合でも数を十進法で数えると思ったら大間違いなんです。世の中には多様な数の数え方があって、他の数え方であれば例えば二進法や十六進法があります。
二進法は0と1だけで数えるもので、十六進法では!”0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F”を使います。
そして、人間の計算結果とExcelの計算結果に齟齬が乗じたのは、Excelが二進法を使っているからなのです。
二進数と十進数の違い
二進数は、十進数の数字を2で割り続けて、あまりの数と最後に割り切れなくなった数字で表現できます。
例えば、13という数字であれば以下のようになります。
13/2 =6 余り1
6/2 = 3 余り0
3/2 = 1 余り1
割り切れなくなった数1
これらを下からつなげて、”1101”となります。
つまり、十進法の13を二進法の1101と読み替えることができるのです。読み替えができるからこそ、Excelが二進法を使って、人間が十進法を使っていても、基本的にはいつも正しい計算結果を得られます。人間が入力した十進法の計算式を二進法に変換して、Excelは二進法で処理をして、処理の結果を十進法に戻せばいいのです。
ところが、これがうまくいかなくなる可能性があるのが、小数です。
Excelは”0.1″が苦手!?
0.1が最もわかりやすい例なので、これを使って見ていきましょう。
小数点を二進数に変換するには、整数の時のように”2で割る”のではなく、”2をかける”作業を続けていき、かけた値が1を下回っている場合には0、上回った場合には1として、小数桁が0になるまで計算し、最後に二進数の最上位(1の位)を整数にします。
0.1であれば、以下のようになります。
0.1 x2 (0)
0.2 x2 (0)
0.4 x2 (0)
0.8 x2 (0)
1.6 x2 (1) (0.6として計算)
1.2 x2 (1) (0.2として計算)
0.4 x2 (0)
0.8 x2 (0)
1.6 x2 (1) (0.6として計算)
1.2 x2 (1) (0.2として計算)
0.4 x2 (0)
0.8 x2 (0)
endless
つまり、計算を終われないのです。
これらをつなげると000011001100…となり、二進法としては0.00011001100…となります。皆さんは1/3 = 0.33333…のように、十進数で表し切れない少数を持つ計算結果を知っていると思います。これらを無限小数と呼びますが、”0.1″に関しては、十進数としては有限小数なのに、なんと二進数だと無限小数になってしまうんです。
ですから、人間=>Excel =>人間とデータがやりとりされる中で、有限小数の十進数から無限小数の二進数に変わってしまい、それを再度十進数に直すプロセスで、齟齬が生じるのです。
Excelは人間をはるかに凌ぐすさまじい計算能力を有するわけですが、そんなExcelの苦手分野が”0.1″のような、二進数では無限小数になってしまう値なのです。
そんな時はどうしたらいい？
さて、原理が分かったところで、一体この現象が発生したらどうすればいいのでしょうか？
基本的には、どうにもできません。Excelも人間も間違っていないので、人間の意図通りの計算結果を表示させる方法はありません。
そんな中で対応策としては、ABS関数で絶対値を出すというのが一つの解決策です。
もしくは、そもそも誤差が小さすぎてほとんどの人は気にしないと思うので、小数の場合は人間の計算結果と違う値が返ってくることがあり得るということを織り込んでExcelの計算結果を見る、ということでも解決策になりえます。
コメント