エクセル・同じ数字なのに違う数字とみなされる現象を回避する（浮動小数点演算）

エクセルの数式やマクロVBAで、計算結果が同じなのに、同じでないと判断されてしまう(不一致)時の回避方法を紹介します。

IF関数を使って、計算結果Aと定数Bが同じなら「OK」、違うなら「NG」と表示させたい。

どう見てもA=Bだし、A-B=0なのに、「NG」となってしまうことがあります。

AとBが同じなのに、違うとエクセルが言ってくるんです。
セル範囲を指定して右下に合計値を表示させても一緒だし、他のセルに値を張り付けても数字は一緒なんです。

途方に暮れています。

その計算は小数点以下の数字が含まれていますか。

今作っている資料の通貨がUSドルなので、小数点以下のもがほとんどです。

実は計算元に小数点以下が多く含まれる場合、ごく微小な誤差がでてしまいます。
これはコンピュータの性質上、不可避なことのようです。

･･･困るし、意味がわからないです。。。

普段私たちが使っている数字は10進法ですが、コンピュータは2進法を使っています。
その過程で起こることのようです。

詳しくはMicrosoftのこちらのページへ↓

同じ数字なのに違う数字とみなされる実例1

残高から値を引いていきます。

セルB3には「=B2-A3」という式が入っています。その下のセルも同様の式が入っています。

値の合計(A列の合計)は残高のスタート(97,123.05)と等しいが、最後の残高(セルB7)が「0」になっていません。

回避方法

ドル通貨などのように、小数点以下の数字があるものをいくつも計算していくと、微小な誤差が出ます。

これを回避するためには、考えられる値よりも小さくなったら0とみなすような式を組みます。

ドル通貨であれば、少数第3位以下になれば0とみなす式を組むことで、この微小な誤差を許容でます。

この誤差では、比べる値のうちどちらが大きくなるかわからないので、絶対値を得る「ABS関数」を使います。

回避する数式　ABS関数

同様に残高から値を引いていきます。

セルB3には「=IF(ABS(B2-A3)<0.01,0,B2-A3)」という式が入っています。その下のセルも同様。
1.　ABS(B2-A3)：B2-A3の絶対値が
2.　1.<0.01：0.01(小数点第2位)よりも小さかったら
3.　True→0とする。False→B2-A3の計算結果

最後の残高(セルB7)が「0」になっている。

考えられる値が小さい場合はべき乗を使うと式がすっきりします。
ex.=IF(ABS(B2-A3)<10^-2,0,B2-A3)