こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

二つの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つで試しましたが同じ結果でした。
こんなことってあるんでしょうか?
困惑しています。

投稿日時 - 2019-05-21 22:16:12

QNo.9618785

困ってます

質問者が選んだベストアンサー

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ビットの変化を伴う時には
その影響が顕著となります。

添付した画像は
いくつかの小数の計算を行った結果です。
2つのセルの差はいずれも、
仮数部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)

 /以上

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

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

投稿日時 - 2019-05-25 10:33:48

お礼

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

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

投稿日時 - 2019-05-25 11:50:41

ANo.14

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

-広告-
-広告-

回答(16)

ANo.16

浮動小数点表現をキーワード検索しました
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となるところです

投稿日時 - 2019-05-25 16:12:02

お礼

ありが問うございます。

投稿日時 - 2019-05-27 09:58:57

ANo.15

>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と思います。

投稿日時 - 2019-05-25 14:47:24

お礼

ありがとうございます。

投稿日時 - 2019-05-27 09:59:14

ANo.13

>この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個です。
 
 

投稿日時 - 2019-05-25 08:30:09

お礼

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

投稿日時 - 2019-05-25 09:47:06

ANo.12

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

投稿日時 - 2019-05-24 21:30:10

お礼

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

投稿日時 - 2019-05-24 22:29:54

ANo.11

>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を深追いしたことが無く、勉強不測のため
準拠していないから起きるのか、これが仕様なのか、想定外なのか、
これ以上のツッコミは不本意ながらできません。 以上です。

投稿日時 - 2019-05-24 11:03:30

お礼

ありがとうございます。

投稿日時 - 2019-07-21 13:08:18

ANo.10

>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
といった値は記憶できないのです。

投稿日時 - 2019-05-24 06:37:16

お礼

ありがとうございます。

投稿日時 - 2019-07-21 13:08:41

ANo.9

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

◇=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桁』
これ未満の端数処理を
各計算の過程で利用者が明示的に正しく行っていなかったから
と言わざるを得ません。

投稿日時 - 2019-05-23 23:45:34

お礼

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

投稿日時 - 2019-05-24 21:52:46

ANo.8

[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 としたのとチャイますか?!

投稿日時 - 2019-05-23 14:00:54

補足

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

投稿日時 - 2019-05-23 14:54:20

お礼

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

投稿日時 - 2019-05-23 14:47:38

ANo.7

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

投稿日時 - 2019-05-22 22:42:51

お礼

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

投稿日時 - 2019-05-23 08:59:06

ANo.6

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

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

ところで、私の 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

投稿日時 - 2019-05-22 22:38:44

お礼

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

投稿日時 - 2019-05-23 08:57:16

ANo.5

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

私の事例の場合は

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



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

投稿日時 - 2019-05-22 10:39:44

お礼

ありがとうございます。

投稿日時 - 2019-05-23 08:55:19

ANo.4

周知とは思いますが、まず、
>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>

投稿日時 - 2019-05-22 10:20:46

お礼

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

投稿日時 - 2019-05-23 08:54:36

ANo.3

> 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にできない)という事ではないでしょうか。

投稿日時 - 2019-05-22 08:28:43

お礼

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

投稿日時 - 2019-05-23 08:50:06

ANo.2

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

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

2進数
0.1010101101000011100101011000000100000110001001010
10進数
0.6690000000000005
(0.669000000000000483169060316868126392364501953125)

投稿日時 - 2019-05-22 03:54:45

お礼

ありがとうございます。

投稿日時 - 2019-05-23 08:50:51

ANo.1

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

投稿日時 - 2019-05-21 22:42:57

お礼

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

投稿日時 - 2019-05-21 23:12:40

-広告-
-広告-

あなたにオススメの質問

-広告-
-広告-