小数をif関数、vlookup関数に使うときは演算誤差に注意

2016年2月19日

エクセルで小数を使うときには注意が必要です。

エクセルのバグ？

例えば、次の図を見てみてください。

A2セル、A3セルともに「0.105」が入っているので、

A4セル～A6セルに入っている式は、
いずれも条件を満たしているので
「一致」と表示してほしいのに、
下２行は「不一致」と表示されてしまっています。

• A2＝A3　→　一致
• A2－A3＝0　→　不一致
• A2－A3－0＝0　→　不一致

なぜ、こんなことが起きてしまうのでしょうか？

エクセルは小数の正確な計算はできない

実は、
エクセルは、
小数については、正確な計算をすることができず、
概算での計算しかできません。

もっとも、
概算といっても、
小数10桁ちょっとくらいまでは、正確なので、
実用上は、問題がないレベルでの計算はできます。

でも、非常に細かいところで、
数値が正しくない場合があるのです。

これを、
「演算誤差」と呼んでいます。

ただ、
その演算誤差は、ごくごく小さいので、
普通に使っている分には、問題はありません。

小数を条件式として使うと問題が生じる

ところが、
冒頭の図のように、
小数を条件式として使おうとすると、
問題が生じるケースがあります。

今回のケースだと、
A2セル、A3セルは、
両方とも「0.105」と表示されているのですが、

演算誤差の関係で、
A2セル、A3セルの数値は
「ぴったり0.105」ではなく
「0.105に限りなく近い数字」になっています。

※たちが悪いことに、
　セルの書式設定を「標準」にしても、
　「0.105」にしか見えないので、
　演算誤差が生じていることは、見た目ではわかりません。

しかも、
A2セルに入っている「0.105に限りなく近い数値」と
A3セルに入っている「0.105に限りなく近い数値」は、
全く同じ数字ではありません。

例えていうならば、
A2セルが0.10500000000000000100
A3セルが0.10499999999999999962
が入っているようなイメージです。
※あくまでイメージなので、正確な数字ではありませんが

どちらも、
ほとんど0.105なのですが、
実際には、微妙に違う数字なんですね。

ですから、
A2とA3が等しいかどうかを調べると、
原理的には「不一致」となるほうが正しいのです。

実際、
下記の２つは不一致となっています。

ところが、
A4セルも、原理的には不一致となるはずなのに、
逆に「一致」してしまっています。

なぜなのでしょうか？

エクセルは演算誤差を自動補正している

実は、
エクセルは、
演算誤差が生じていそうだ、と判断した場合には、
自動で演算誤差を補正する、
という機能が組み込まれているようです。

その機能が発動する条件は定かではないのですが、
先ほどの例での、

については、その補正機能が発動しているようです。

同様に、
次の図では、
A4セルで、補正機能が発動しているようです。
逆にA5セルは、補正機能が発動せず、誤差が生じてしまっています。

vlookupでは誤差補正は働かないので注意

先ほどの補正機能は、
どうやら、vlookup関数を使うときには、
一切働かないようです。

そのため、
vlookup関数を使う場合に、
小数をキーにするのは止めておいたほうがいいでしょう。

もし、
小数をキーにしてしまうと、
次のようなことが起こります。

演算誤差によるロジックの誤りは、
非常に見つけにくく、トラブルの元です。

ですから、
なんとか工夫して、
条件式には小数を使わない、
と割り切ってしまったほうがいいと思います。