Excelの関数に関して、問題が発生しています。

Question

Excelのシートの中で、

　=abs(a1-a2)<=a3

という論理式を立てて、

小数点第1位を判定すると、

　a1=100 , a2=100.1 , a3=0.1　→　TRUE

　a1=100 , a2=100.2 , a3=0.2　→　FALSE

　a1=100 , a2=100.3 , a3=0.3　→　TRUE

　a1=100 , a2=100.4 , a3=0.4　→　FALSE

　a1=100 , a2=100.5 , a3=0.5　→　TRUE

　a1=100 , a2=100.6 , a3=0.6　→　TRUE

　a1=100 , a2=100.7 , a3=0.7　→　FALSE

　a1=100 , a2=100.8 , a3=0.8　→　TRUE

　a1=100 , a2=100.9 , a3=0.9　→　FALSE

という答えが返ってきます。

小数点第2位を判定すると、

　a1=100 , a2=100.01 , a3=0.01　→　FALSE

　a1=100 , a2=100.02 , a3=0.02　→　TRUE

　a1=100 , a2=100.03 , a3=0.03　→　FALSE

　a1=100 , a2=100.04 , a3=0.04　→　FALSE

　a1=100 , a2=100.05 , a3=0.05　→　TRUE

　a1=100 , a2=100.06 , a3=0.06　→　FALSE

　a1=100 , a2=100.07 , a3=0.07　→　TRUE

　a1=100 , a2=100.08 , a3=0.08　→　TRUE

　a1=100 , a2=100.09 , a3=0.09　→　FALSE

となりました。

さらに小数点第3位を判定すると、

　a1=100 , a2=100.001 , a3=0.001　→　FALSE

　a1=100 , a2=100.002 , a3=0.002　→　TRUE

　a1=100 , a2=100.003 , a3=0.003　→　FALSE

　a1=100 , a2=100.004 , a3=0.004　→　FALSE

　a1=100 , a2=100.005 , a3=0.005　→　TRUE

　a1=100 , a2=100.006 , a3=0.006　→　FALSE

　a1=100 , a2=100.007 , a3=0.007　→　FALSE

　a1=100 , a2=100.008 , a3=0.008　→　TRUE

　a1=100 , a2=100.009 , a3=0.009　→　FALSE

となりました。

FALSEが返ってくる頻度はa1の値によって変わり、例えばa1=10として小数点第1位を判定すると、0.3と0.8の時のみFALSEが返ってきました。

また、整数に対しては発生せず、小数の値に対してのみ発生するようです。

(絶対値を取るかどうかは関係ありませんでした。)

論理式に誤りがあると、気づかないまま誤った処理をしてしまう可能性があります。

確認いただければ幸いです。

Answer 1

> round関数を使う、表示桁数で計算オプションを選択する、といった「技」を用いなければならない

ということです。今までのことを悔いてもしょうがないので、今後はそういうことを念頭においてやりましょう。

Answer 2

回答ありがとうございました。

浮動小数点演算に起因するあるあるとのこと。そのことについては理解しました。

ただ、

=abs(a1-a2)<=a3

という式は、(a1=100 , a2=100.1 , a3=0.1)の場合も(a1=100 , a2=100.2 , a3=0.2)の場合もTRUEが返ってくることを期待して立てています。それを常に担保するためには、round関数を使う、表示桁数で計算オプションを選択する、といった「技」を用いなければならないということですか。

今までそんなことを考えたことがなかったので、誤った判定をしてしまうことがあったのではないかと、少しぞっとしています。

Answer 3

年に何度か見かける Excel あるあるです。

　添付図は小数第一位の判定の二番目の例ですが、数式バーで「A1-A2」の部分をマウスでドラッグして F9 を押した図（A1-A2 を評価した結果）です。これを見れば FALSE になるのが当然です。

　一番目の例でも A1-A2 は 0.1 ではなくそれより小さい値になるので TRUE になっています。

　詳しくは以下など。

浮動小数点演算が Excel で不正確な結果をもたらす可能性がある