見えない浮動小数点演算誤差？

質問

二つのBOOKにある表のデータの数値をVBAで比較していました。
単に各セルのValueとValueが等価かどうか＝で比較しただけです。
すると見た目（表示）も数式バー上の値もまったく同じなのに相違があると判定されました。
不思議に思い、二つのBOOKにある表のデータの数値のうち違いがあると出た2つのセルを、そのまま別シートにコピー貼り付けして比較したのが添付の図です。

たとえば、BOOK-AからコピペしたB3セルの値は0.669です。
BOOK-BからコピペしたC3セルの値も0.669です。
両方とも数式バーでみましたが、間違いなく0.669です。
A3セルに　=B3=C3　と入れるとTRUEが返ります。
ところが、D3セルに　=B3-C3=0　と入れるとFALSEが返ります。
では、0でないなら差額はいくら？と、B11セルに=B3-C3と入れると、0が返ります。
これまで浮動小数点演算誤差で、見た目がおなじでも小数点以下かなり下の方で違いケースは経験していましたが、その場合でも小数点以下の表示を20位くらいまで表示させると違いが表れました。ところが今回は誤差が見えません。
差額確認のためVBAで
Sub test01()
Debug.Print Range("B3").Value = Range("C3").Value
Debug.Print Range("B3").Value - Range("C3").Value
Debug.Print Range("B5").Value = Range("C5").Value
Debug.Print Range("B5").Value - Range("C5").Value
End Sub
としてみると、
False
1.11022302462516E-16
False
1.11022302462516E-16
が返りました。
エクセル2010と2016の2つで試しましたが同じ結果でした。
こんなことってあるんでしょうか？
困惑しています。

回答No.14

HohoPapa · ベストアンサー

1.11022302462516E-16
この部分を深追いしてみました。

根拠などは後ほどとして
次のような動作と推測できます。

>=B3=C3　と入れるとTRUEが返ります
これは、IEEE 754の仕様に従い
15桁の精度の範囲内で比較しているから。

他方、
>=B3-C3=0　と入れるとFALSEが返ります。
という点と
>Range("B3").Value - Range("C3").Value =　
>1.11022302462516E-16
となるのは
倍精度浮動小数点の値
（2進数:
　　符号 1 ビット
　　指数 11 ビット
　　暗黙 1 ビット
　　仮数 52 ビット
　で記憶している値）
これをそのまま使い計算し
10進数で結果を表示しているから。

なぜ15桁を超えたところで差が起きるのかは
議論の余地なく
10進の計算を2進数で計算しているから。
特に、浮動小数点が移動する時の計算
つまり、指数部11ビットの変化を伴う時には
その影響が顕著となります。

添付した画像は
いくつかの小数の計算を行った結果です。
２つのセルの差はいずれも、
仮数部50ビット目から53ビット目までのいくつかの値を
合計することで求めることができますので
B3、C3の内部的な値は「仮数部」の末尾近くで違いがあることを
裏付ける結果です。

2.22045E-16 = 1/(2^52)
1.66533E-16 = 1/(2^50) + 1/(2^51) + 1/(2^52) + 1/(2^53)
5.55112E-17 = 1/(2^51) + 1/(2^53)
1.11022E-16 = 1/(2^53)

　／以上

追記１
先に私の発言にあった
>>各計算の過程で利用者が明示的に正しく行っていなかったから
>>と言わざるを得ません。
は謝罪し撤回させてください。<m(__)m>

追記２
２つのセルの値が等しいかどうかの判定に
=B3-C3=0　は使わないほうが賢明ということと思います。

お礼

ありがとうございます。
浮動小数点演算誤差が発生する過程が添付の図でよくわかりました。
ただ、わたしの課題は与えられたデータの差異を調べることなのです。
そして与えられたデータは、見た目も、数式バーで見ても0.669でまったく同一。
しかし、ご提示いただいた
Function myDif(LRng As Range, RRng As Range) As Double
　　myDif = LRng.Value - RRng.Value
End Function
でも差異が現れます。
もちろん、0.669をセルに直接入力すれば差異は出ません。
この目に見えない違いをどうすべきかなのです。

・・・と言って、これは自分でありのままを説明するしかないですね、やっぱり。（理解してもらえるか自信がありませんが）

回答No.5

HohoPapa

No4です。
言い忘れがありました。

私の事例の場合は

>Excel で浮動小数点演算の結果が正しくない場合がある
>https://support.microsoft.com/ja-jp/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel

の

>[表示桁数で計算する] オプションを使用して、
>丸め誤差による影響を回避できる場合があります。
をすれば、忖度範囲が広くなるので
=C5-C2=0　でTrueが返ります。

お礼

ありがとうございます。

回答No.4

HohoPapa

周知とは思いますが、まず、
>Excel で浮動小数点演算の結果が正しくない場合がある
>https://support.microsoft.com/ja-jp/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel
の理解が必要です。

また、
B3、C3の値は、内部的には、
http://www.altima.jp/column/fpga_edison/bit_number_float.html
に説明がありますとおり、
「符号」、「仮数」、「指数」を2進数で保持しています。
より厳密には
符号 1 ビット 指数 11 ビット 暗黙 1 ビット 仮数 52 ビット

おそらく、
B3、C3の内部的な値は「仮数部」の末尾近くで違いがあるものの
その差が小さいため、
表示形式と表示桁数を設定しても表面化できないものと思います。
（表示形式と表示桁数を設定しても表示桁数に限界がありますから）

このような状況下で、
=B3=C3　の評価は、
前述の表面化できない差を無視（補正？）して
（エクセル固有の忖度かも）
行っているためTrueが返ってくるものと思います。

他方、
=B3-C3=0　の評価は、
浮動小数点のまま計算(忖度しないで評価)しているため
前述の差が表面化するものと思います。

πを使って再現してみましたので参考にしてみてください。
D列はC列に埋まっている計算式です。

VBAでは「符号」、「仮数」、「指数」に埋まっている
それぞれのデータを取得する術がないので、
これ以上の深追いができません。　<m(__)m>

お礼

>その差が小さいため、
>表示形式と表示桁数を設定しても表面化できない
そういうことなんでしょうね。ありがとうございます。
VBAで求めた差額の1.11022302462516E-16って、数値に直せば
0.000000000000000111022302462516 で、エクセルの有効桁数を超えてますもんね。

回答No.3

kkkkkm

> BOOK-AとBOOK-Bのデータは手入力したものではなく、他の資料からの値貼り付けのはずです

たとえば元の計算式が分数等をもとにしたのもということはないでしょうか。
分数で1/3として(数式バーには0.333333333333333と表示されている)
で、この1/3のセルをコピーして値貼り付けすると
貼り付け先は(E1とします)
0.333333333333333
になります(数式バーも同じ)
手入力で(E2とします)
0.333333333333333
とした場合
=E1=E2はTRUE
=E1-E2=0はFALSE
になります。
ちなみに
E1に3を掛けると1になります。
このように、元の値が分数のような値(通常の数値)だけで処理できない場合にはなにがしかのデータが存在して、通常の比較ではその部分が無視され、計算ではそこまで対象になる(しないと3掛けて1にできない)という事ではないでしょうか。

お礼

ありがとうございます。
たしかに、＝1/3の結果を値貼り付けすると0.333333333333333
これの桁数をふやしても0.3333333333333330000000ですが、手入力した0.3333333333333330000000との差は0ではないですね。数式バー上では全く同じなのに。

回答No.2

kichi8000

計算誤差の最小値である「計算機イプシロン」
エクセルでは、数値の内部記憶は２進数です。
内部演算で2進数の小数部最小桁は、表示上で強制的に丸められます。１０進数表記に変換された時点で変換誤差により計算機イプシロンは消滅し、表示桁を増やしても確かめる事は出来ません。
有効桁を指定して計算すると計算機イプシロンは発生しません。
有効桁指定で計算、または計算後に数値を丸める（切り上げや四捨五入など）、１を足してから１を引く、などの操作で誤差をなくす必要があります。

以下の有効桁は例です。
2進数
0.1010101101000011100101011000000100000110001001001
10進数
0.669
(0.6689999999999987068122209166176617145538330078125)
0.0000000000000017763568394002504646778106689453125以下の誤差は表示上では切り上げされる

2進数
0.1010101101000011100101011000000100000110001001010
10進数
0.6690000000000005
(0.669000000000000483169060316868126392364501953125)

お礼

ありがとうございます。

回答No.1

kkkkkm

BOOK-AとBOOK-Bのデータは入力したやつですかそれとも何かの数式で出た値ですか。
2013だと「入力したもの」はTRUEですし、VBAで実行してもTrueに0です。

お礼

ありがとうございます。
BOOK-AとBOOK-Bのデータは手入力したものではなく、他の資料からの値貼り付けのはずです。おおもとのデータは数式で計算された結果だと思いますが、すでにBOOK-AとBOOK-Bの段階では、値です。数式バーでも確認しています。そのBOOK-AとBOOK-Bのセルを、テストのため、値貼り付けではなくそのままコピペしたのが今回提示した画像のエクセルシートです。