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

質問

二つの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.16

chayamati

浮動小数点表現をキーワード検索しました
https://ja.wikipedia.org/wiki/浮動小数点数
各種ある中の代表的な「IEEE方式（IEEE 754 形式）」の抜粋です
-----------------------------------------------------------------
半精度浮動小数点数：符号部 1 ビット ・ 指数部 5 ビット ・ 仮数部 10 ビット
単精度浮動小数点数；符号部 1 ビット ・ 指数部 8 ビット ・ 仮数部 23 ビット
倍精度浮動小数点数：符号部 1 ビット ・ 指数部 11 ビット ・ 仮数部 52 ビット
四倍精度浮動小数点数：符号部 1 ビット ・ 指数部 15 ビット ・ 仮数部 112 ビット
で表現されている。各部は次のように定義されている。
符号部は、 0 を正、1 を負とする
仮数部は、整数部分が 1 であるような2進小数の小数部分（ケチ表現）を表す
指数部は、符号なし2進整数とし、
・半精度では 15
・単精度では 127
・倍精度では 1023
・四倍精度では 16383
のゲタを履かせたゲタ履き表現で表す
-----------------------------------------------------------------------
これより考察すると、仮数部と指数部は等しく、符号部のbitが原因かも
ただ =False()+False()が0になり、=True()+True()が2となるところです

お礼

ありが問うございます。

回答No.15

HohoPapa

>Function myDif(LRng As Range, RRng As Range) As Double
>　　myDif = LRng.Value - RRng.Value
>End Function
>でも差異が現れます。
>もちろん、0.669をセルに直接入力すれば差異は出ません。
>この目に見えない違いをどうすべきかなのです。

このスレッドでemaxemaxさんが聞きたいことがうまく読み取れないです。

単に

>>=B3-C3=0　と入れるとFALSEが返ります。
>という点と
>>Range("B3").Value - Range("C3").Value =　
>>1.11022302462516E-16
が
=B3-C3=0　と入れるとTrueが返り
Range("B3").Value - Range("C3").Value = 0

になればいいだけであれば
一方、または双方を
たとえば、=ROUND(B3,10)　といった計算式で
emaxemaxさんの期待する有効桁数(例えば小数点以下10桁)で
丸め処理をすれば済むことです。

他方、B3、C3のどこがどのように違うのかを理解するには
まず、浮動小数点の仕組みを理解する必要があります。

いままでの検証内容からいえることは
エクセルはB3、C3を二進数で記憶しており

B3=
0.10101011010000111001010110000001000001100010010011101
C3=
0.10101011010000111001010110000001000001100010010011100

または
B3=
0.10101011010000111001010110000001000001100010010011111
C3=
0.10101011010000111001010110000001000001100010010011110

つまり、
仮数部の末尾1ビットがB3は1、C3が0と思います。

お礼

ありがとうございます。

回答No.13

chayamati

＞このD3は、=B3-C3=0　が入ってるセルですか？ならば0です。
★Falseになるセル（B3-C3）の値を知りたかったのです。
　 =(B3-C3)*2^32、=(B3-C3)*2^64　の値です。
　計算のステップを分けずに、直接上記の式で試してください。
　　※Windowsに32bit版と64bit蕃があり2進数の精度にの差があるのかと
＞D3に＝1/3を入れたのであれば、1431655765　です。
？1/3の値は、0.3333333333333…ですね
　小数点以下に3が何個並ぶか確認して頂きたかったのですが
　こちらでは16個です。

お礼

ありがとうございます。
> =(B3-C3)*2^32、=(B3-C3)*2^64　の値です。
それぞれ
0.000000476837158203125000
2048.000000000000000000000
です。
＞小数点以下に3が何個並ぶか
15個でした。エクセルの有効桁数通りです。
32ビット版エクセル2016です。

回答No.12

chayamati

今晩は
＞D3セルに　=B3-C3=0　と入れるとFALSEが返ります。
★セルに=1/3と入力して表示形式をユーザ定義で
「.####################」と　#を20くらい並べて下さい
　結果3は16個並びこれ以下の値は表示されないようですね
　ご存知と思いますが数値の 元は2進数ですね
　そこで、D3に2の32乗または2の64乗を掛けて下さい
　　E3=D3*2^32
　　F3=D3*2^64
　何か有効数値が表示されますか

お礼

ありがとうございます。
＞D3に2の32乗または2の64乗を掛けて下さい
このD3は、=B3-C3=0　が入ってるセルですか？ならば0です。
D3に＝1/3を入れたのであれば、1431655765　です。

回答No.11

HohoPapa

>1.11022302462516E-16
これは何者か？

添付画像のように計算させると、
53回目（53ビット目）にこの値が登場しますので、
この値は、1/(2^53)です。
他方、倍精度浮動小数点の仮数部は52ビットですので、
C3とD3の差は、どうやら（おそらく）、

https://support.microsoft.com/ja-jp/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel
で説明している
>IEEE 754 に準拠していない部分
>•非正規化数: 非正規化数は指数 0 で示されます。
>--途中省略--
>非正規化数は基本的に、通常の下限値より小さい数値を格納できるようにするための代替手段です。
>これは規格のオプション部分で、マイクロソフトはこれを実装していません。

この箇所に絡んでいそうに思います。
私はIEEE754を深追いしたことが無く、勉強不測のため
準拠していないから起きるのか、これが仕様なのか、想定外なのか、
これ以上のツッコミは不本意ながらできません。　以上です。

お礼

ありがとうございます。

回答No.10

HohoPapa

>1.11022302462516E-16　との話がありますが、
>倍精度浮動小数点数の仮数部が52ビットですから、
>ここまで細かな差は保持していないはずです。

これについて、よりわかりやすい説明を試みます。

仮に
C3=0.669000000000000000000000000000
としたときに
D3=0.669000000000000111022302462516
と主張しているように読み取れます。

整数部が0で小数点第一位が0以外の時は、
倍精度浮動小数点数の仮数部が52ビットですから
可能な精度（記憶可能な最小値、最小単位）は、
1/(2^52)=1/4,503,599,627,370,500です。

つまり、どう頑張っても
D3=0.669000000000000111022302462516
といった値は記憶できないのです。

お礼

ありがとうございます。

回答No.9

HohoPapa

ここで話題にしている問題は何か？
というところから改めて整理してみました。

◇=B3-C3=0がFalseなのはなぜ？

B3≠C3だから

◇両者の差は？

1.11022302462516E-16　との話がありますが、
倍精度浮動小数点数の仮数部が52ビットですから、
ここまで細かな差は保持していないはずです。
1.11022302462516E-16の大半は
単なる2進／10進変換でおきる誤差と思います。

内部的な2進数の値を拾い出して比較したいものの
VBAにはその術がないので深追いができません。

◇両者の差をエクセル上で可視化できるか？

差が小さすぎるため可視化できない。
ただし、
B3-0.6　C3-0.6　といった計算を行えば
浮動小数点の位置が右に1桁移動するので
炙り出せるかもしれません。
しかし、
炙り出せたとしても、信頼できる値か？といえば疑わしいです。

繰り返しますが、
倍精度浮動小数点数の仮数部は52ビット
つまり、2^52=4,503,599,627,370,500ですから
15桁を超える値（16桁目以降）は信頼できません。

これは先に案内した
https://support.microsoft.com/ja-jp/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel
にも、
>15 桁の精度の範囲内でのみ表現できます。
とあるとおりです。

◇=B3=C3がTrueになるのはなぜ？

私はエクセルの忖度と推察します。
これをFalseと判断するような計算を求めるのであれば
エクセルやVBAではなく、別なプロ用の言語を使う必要がありましょう。

◇なぜ今回のような差が生まれたのか？

B3、C3を計算する過程で、
『利用者側が規定する有効桁数：例えば小数点以下10桁』
これ未満の端数処理を
各計算の過程で利用者が明示的に正しく行っていなかったから
と言わざるを得ません。

お礼

ありがとうございます。
> 問題は何か？
そうでした。
わたしの仕事は、データを比較して同一かそうでないかを上司に報告することでした。
その場合、この元データの浮動小数点演算誤差と思われる差異をどうするかなのです。
違うと報告しても、数式バーで見て全く同じ数値、しかも=B3=C3　と入れるとTRUEが返るものを、どう違うと納得させられるかなのです。

回答No.8

msMike

[No.7お礼]へのコメント、
》 差額の0.000000000000000111022302462516 が…
何それ？
お断りしておきますが、私マクロ音痴なので質問文の貴方の添付図しか見ていません。
=LEN(B3) も =LEN(C3) も 5 を呈しているなら、
B3、C3 は何れも 0.669 でしょ？
=B3-C3=0.669-0.669 は小数点を含む数値の演算だけど
=B3*10000-C3*10000=6690-6690 は整数の演算だから演算誤差は発生する訳ゃ無い！
貴方、まさか、=B3*10000-C3*10000 でなく、カッコ付けて =(B3-C3)*10000 としたのとチャイますか？！

お礼

差額の0.000000000000000111022302462516 は、マクロで出した差額
1.11022302462516E-16　という指数表示を数値化したものです。
この指数を、コピーしてセルに値貼り付けして、書式を数値にし、桁表示を増やしたらこうなります。つまり両方のセルの値にはこれだけ差があるのです。目には見えませんが。
ご指摘の =(B3-C3)*10000 とはしていません。
=B3*10000-C3*10000 でもFalseなのです。
ありがとうございました。

補足

ご指摘の「かっこ」で思いついて、
=(B5*10000-C5*10000)*10000000000000　を計算してみました。
目出度く、9.094947017729 が返りました。
やはり、エクセルは有効桁数15桁をはみ出た部分は表示されない、しかし差はあるのです。

回答No.7

msMike

[No.6]誤謬訂正
D5: =B5-C5=0 ⇒ =B3*10000-C3*10000=0
は、
D5: =B5-C5=0 ⇒ =B5*10000-C5*10000=0
の間違いでした。
済みませんでした。

お礼

はい、気づいてました。ありがとうございます。
結局、差額の0.000000000000000111022302462516 が、有効桁数を超えてるから表示できないってことなんでしょうね。

回答No.6

msMike

諄いようですが、確認させてください。

添付図に書かれていることは間違いないことを確認済みなのですね？

ところで、私の Excel 2013 ではセル D3、D5 の末尾に示す各式とも FALSE でなく、TRUE を呈します。貴方の場合は、それらの式を各矢印(⇒)の右側の式に変更しても同じく FALSE のままかどうか試してみてくれませんか？
D3: =B3-C3=0 ⇒ =B3*10000-C3*10000=0
D5: =B5-C5=0 ⇒ =B3*10000-C3*10000=0

お礼

>添付図に書かれていることは間違いないことを確認済みなのですね？
はい、間違いなく。
D3: =B3-C3=0 ⇒ =B3*10000-C3*10000=0
D5: =B5-C5=0 ⇒ =B5*10000-C5*10000=0
これもFALSEでした。
ありがとうございます。