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

エクセルの数式やマクロ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)

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

B列の書式設定を通貨にしている

実例1のパターンで、上の図のように書式設定を通貨などに変えると、あたかも0であるかのような表示になるが、小数点第2位までは0が並んでいるというだけです。

セルC7に「セルB7が0であるかどうか」を判定する式(セルD7参照)を入れると、0でないという結果が返ってきます。

セルB7で表示する小数点以下の桁数を増やすと次のようになりました。

回避方法は実例と同じです。

この記事が気に入ったら
フォローしてね!

よかったらシェアしてね!

この記事を書いた人

子育てに奮闘しながらも、再びガッツリ走り込める日を夢見るフルタイム会社員。

コメント

コメントする

CAPTCHA


目次
閉じる