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

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

エクセルのバグ?

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

syousuugosa_1

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

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

  • 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が等しいかどうかを調べると、
原理的には「不一致」となるほうが正しいのです。

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

A5セル:=IF(A2-A3=0,”一致”,”不一致”)
A6セル:=IF(A2-A3-0=0,”一致”,”不一致”)

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

なぜなのでしょうか?

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

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

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

A4セル:=IF(A2=A3,”一致”,”不一致”)

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

syousuugosa_1_2

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

syousuugosa_1_3

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

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

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

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

syousuugosa_1_4

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

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

エクセル基礎講座 「無料」動画マニュアル

「経理事務のためのエクセル基礎講座(初級編)」(動画マニュアル 総収録時間162分)を無料プレゼント中です!

このマニュアルで解説していることを一通り学べば、経理事務を行う上で最低限必要となる知識が得られます。

ご登録者の方には、合わせて、公認会計士が実体験を通して身に付けたエクセルを使う技をメールにてお伝えしていきます!

無料動画講座 登録フォーム

※ご登録頂いたメールアドレスに、エクセルを使いこなすための情報を配信するメールセミナー「エクセル倍速講座」も合わせて配信させていただきます。