[[20180831131820]]　『演算誤差について』（T18）　ページの最後に飛ぶ

『演算誤差について』（T18）

お世話になります。

VBAのIF判定で、「同じ数値が同じでない」という結果が出ます。
試しにセル（H13,H14）に出力させた両数値（24.7）を
「=IF(H13-H14=0,"O","X")」で判定すると「X」になります。
但し、「=IF(H13=H14,"O","X")」は「O」、「=IF(H13>H14,"O","X")」や
「=IF(H13<H14,"O","X")」は「X」と正常です。

いろいろ調べて演算誤差とわかり、ROUND関数で数値を丸めて対処できたのですが、
ふとそれぞれの数値の「真の値」を知りたく思いました。
可能であれば、その方法を教えていただけないでしょうか？

内部的には少数点16桁以降は「0」になるとのことですが、それでは
15桁まで同じであれば「同値」となるのではないでしょうか？　
この点も理解できません。

※セルに出力した件の数値は、「表示桁数を増やす」ボタンを押し続けると
　どこまでも（100桁ぐらい？）同じ値24.700000000.....が表示されます

よろしくお願いします。

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

＞（100桁ぐらい？）同じ値24.700000000.....が表示されます
＞内部的には少数点16桁以降は「0」になるとのことですが
違う。整数部分の桁数＋小数点以下の桁数の合計が15桁までで、それ以下は0になります。
確かに表示桁数はず～～っと増やせるけど、有効な使用方法があるのかは謎です。

演算誤差とは引き算の結果です。
確認したければ引き算の結果をセルに表示すると確認できると思いますよ。
=H13-H14　にして、表示桁数を15桁くらいに増やしてみましょう。

数値が完全に同一かどうか判定したいときは素直にH13=H14と書きましょう

（名無し） 2018/08/31(金) 13:42

私の
＞整数部分の桁数＋小数点以下の桁数の合計が15桁までで、それ以下は0になります。
の表現も不適切でした。

いわゆる有効数字桁数が15桁なので、整数部分や小数点以下の上の位が全て0なら含めません
たとえば
0.00000000123456123456123456
は
1.23456123456123E-09
として記憶されます。
（名無し） 2018/08/31(金) 13:45

名無しさん、表示桁数の件は承知しました。

「=H13-H14」にしてみましたが、50桁ぐらい「0.00....0」表示されます。
で、「=IF(H13-H14=0,"O","X")」は「X」です。

これは演算誤差なんぞではなく、ひょっとして単なる「バグ」でしょうか？
（T18） 2018/08/31(金) 14:01

 すごく難しいテーマだと(個人的には）思いますので、
 具体例を一つ上げて貰って、それで考えた方が(少なくとも当面は）いいと思います。

 ＞数値の「真の値」を知りたく思いました。 
 上の具体例における「真の値」を示してください。

 あと、我々が確認できるのは、エクセルの表示メカニズムを通して表示されるものでしかないです。
 ※表示メカニズムがどんな仕組みになっているは、誰も知らされていない。

 表示メカニズムが人間の期待する通りに動いている保証はありませんので、
 それで確認した結果も(厳密には）100％信頼できるものではないと言うことも
 頭の片隅に入れて置く必要があると思っています。

（半平太） 2018/08/31(金) 14:05

というか私の環境Excel2016では、「24.7」で計算した時におかしな点は全くありませんよ。
T18さんの説明が間違っているような。

O =IF(H13-H14=0,"O","X")
O =IF(H13=H14,"O","X")
X =IF(H13>H14,"O","X")
X =IF(H13<H14,"O","X")
（名無し） 2018/08/31(金) 14:21

環境依存かも知れないので正確に書いておきますね。
Windows10 1803 Pro 64bit / Excel 2016 16.0.9226.2126 32bit
（名無し） 2018/08/31(金) 14:27

私もやってみましたけどおかしい結果はでませんね。

 O =IF(H13-H14=0,"O","X") 
 O =IF(H13=H14,"O","X") 
 X =IF(H13>H14,"O","X") 
 X =IF(H13<H14,"O","X")

Windows10 Excel2013 です。
（TAKA） 2018/08/31(金) 15:00

 H13セル、H14セルの値はどのようにして求めているのだろうか？
（ねむねむ） 2018/08/31(金) 15:05

 >「=IF(H13-H14=0,"O","X")」は「X」です。 
 >これは演算誤差なんぞではなく、ひょっとして単なる「バグ」でしょうか？

 これとの違いが気になってるんですか？
 >「=IF(H13=H14,"O","X")」は「O」

 これは下の式の場合は、EXCEL97以降から演算誤差を補正する機能が出来たからだそうです。

 但し、常に補正してくれる訳ではなく、上の式のような記述方法や、()で囲んだりすると
 補正機能が働かなくなるようです。

 どのパターンが補正機能が働いて、働かない時はと聞かれると、それほど詳しくないので分かりません。

 >15桁まで同じであれば「同値」となるのではないでしょうか？　 
 >この点も理解できません。 
 此方もロジックを公開されてないので推測でしかありませんが、
 表示できる桁では同じに見えると言うだけで中身は違うので、
 (1/3や円周率などと同じ無限数になってるんじゃないでしょうか)
 普段私たちが書式設定で表示桁を制限して表示させてるけど、
 実際の値は違うのと同じ原理と思ってます。

（sy） 2018/09/01(土) 10:45

皆様ありがとうございます、返信遅れましてすみません。

２つのブックからデータを転記した後にマクロでデータ処理（計算や加工）を
配列（Dictionary含む）で行っています。

（質問の対象となっている計算について）
・対象データ（Ｄ）は「13.1-12.7-...」などと「-」で数字が結合されています
・数字は、小数点以下一桁の半角数字で、個数は不定です（データは数千行）
・先ずSplit関数でＤを分解して数字のみ配列に格納しています
・Ｄの最右側から2個の数字を足し算します（Ａ）
　例）Ｄが「13.1-12.7-...-12.1-12.6」であれば、Ａは「24.7」

「If tg1>tg2 Then ...」（tg1,tg2はＡを格納している変数）と比較している時に、
tg1,tg2共に24.7でありながらtg1の方が大きいという結果が出ました。
（同値のもの全てがこのような結果になるわけではありません）

各変数をセル出力して確認した結果が質問内容になります。
（T18） 2018/09/01(土) 13:28

横からすみませんが、もう一段階具体的にできませんか？
24.7という同じ結果になるはずのものは、
どのような式で計算されたものですか？
その二通りの方法を具体的に示してもらうと、
皆さんで状況を再現できるんじゃないですか？

（γ） 2018/09/01(土) 14:30

＞（質問の対象となっている計算について）
＞　・・・
＞「If tg1>tg2 Then ...」（tg1,tg2はＡを格納している変数）
この部分のマクロそのものを提示されれば良いと思いますよ。

配列は何型なのか？
文字列数字を数値に変換している処理は？
Ａの値を tg1 , tg2 に格納する過程で他に何か介在していないのか？
等等

確実な検証＆回答をする上で、回答者側にとって
不明な点が多々あるように見受けられます。

マクロコードさえ提示されれば「12.1-12.6」のデータを与えて
直ぐに検証できるでしょう。
（AddinBox 角田） 2018/09/01(土) 14:38

 言い忘れましたが、
 この様な現象は、ワークシート上とVBA処理上とでは、挙動が異なることがあるので、
 (少なくとも当面は）VBAだけに絞った方が混乱がなくていいと思います。

 ※ワークシート関数の話を絡めると更にややこしくなります。

 12.1と12.6の組合せが別途あると仮定します。

 Sub test()
    Dim tg1 As Double
    Dim tg2 As Double

    tg1 = Val("12.3") + Val("12.4")
    tg2 = Val("12.1") + Val("12.6")

    Debug.Print tg1 - tg2         ' 3.5527136788005E-15
    Debug.Print tg1 - Val("24.7") ' 3.5527136788005E-15

    Debug.Print Val("24.7") * 3 - Val("74.1") ' 3.5527136788005E-15
 End Sub

 問題はこの認識が正しいか、と言うことです。(数学的には正しいですが、それは人間が頭で考えることです）
 　　　　↓
 ＞tg1,tg2共に24.7でありながらtg1の方が大きいという結果が出ました

 24.7であることを何で確かめましたか？、
 　ローカルウィンドウで両方とも24.7と出ているからですか？

   そう表示されていても 24.7とは限らないですよね？　
 　実際tg1は、24.7より 3.5527136788005E-15 だけ大きい値なんですから。

   でも、ローカルウィンドウで、そのわずかな差をどう表現すればいいと思いますか？

 　マイクロソフトもその方法は考えたでしょうが、
 　多分、その差を表現する適切な方法が見当たらなかったか、
   考えるのが面倒でイージーな処理にしたか、だと思います。

 次に、これを見てtg2は24.7と同じだと思いましたか？
 　　　　　↓
 ＞  Debug.Print tg1 - tg2         ' 3.5527136788005E-15
 ＞  Debug.Print tg1 - Val("24.7") ' 3.5527136788005E-15

 浮動小数点の話を聞かれたことがあると思いますが、
 tg2 と Val("24.7")　は同じですが、「真の24.7」とは違います。

 エクセルが「真の24.7に極限まで近いと認識する値」と言うしかないです。
 エクセルが真の値とは何かを認知できるなら、そもそも演算誤差の問題は生じないです。

 また、「真の24.7に極限まで近いと認識する値」を3倍しても、
 　　　「真の74.1に極限まで近いと認識する値」と同じになる保証もありません。

（半平太） 2018/09/01(土) 15:27

実ブックが手元にないため、マクロを提示できず申し訳ありません。

配列や変数は全てVariant型にしており、Splitで格納した数字の合計は「= 値1 *1 + 値2 *1」で
求めています。

内容的には半平太さんのコードで、

 Dim tg1 As Double
 Dim tg2 As Double
 Dim a, b, c, d

 a = "12.3"
 b = "12.4"
 c = "12.1"
 d = "12.6"

 tg1 = a * 1 + b * 1
 tg2 = c * 1 + d * 1

としているのと同じと解釈しています。

で、Debug.Printが全て「3.5527136788005E-15」となるカラクリは何なんでしょう？

 >ワークシート上とVBA処理上とでは、挙動が異なる
 >ワークシート関数の話を絡めると更にややこしくなります
 >「真の24.7に極限まで近いと認識する値」を3倍しても・・・と同じになる保証もありません

など、？？？状態です。
開けてはいけない扉を開けるような、私レベルはスルーした方がよさそうですかね…
（T18） 2018/09/01(土) 18:31

 ＞開けてはいけない扉を開けるような、私レベルはスルーした方がよさそうですかね…

 いや、私も含めて、ほとんどの人が大差ないレベルだと思います。

 ＞Debug.Printが全て「3.5527136788005E-15」となるカラクリは何なんでしょう？

 全て同じ差になったのは、たまたまです。

 私レベルでは、サンプルデータの差を自在に演出するのは出来ませんので、
 今回、実験で差が出るものを見付け、そのまま採用したに過ぎません。

 2進数形式でtg1とtg2を表示すると、以下になります。

 　tg1 　+11000.101100110011001100110011001100110011001100110100
 　tg2   +11000.101100110011001100110011001100110011001100110011
 　その差　　 0.000000000000000000000000000000000000000000000001

  差は小数点以下48個目に1ですから 10進数ですと「0.5の48乗」、つまり 3.5527136788005E-15 です。

 じゃ、何故「真の12.3に極限まで近い値」と
 　　　　　「真の12.4に極限まで近い値」の和である「tg1」が少し大きくなったのか？　ですよね？

 　「真の12.3に極限まで近い値」の2進数　+1100.0100110011001100110011001100110011001100110011010
 　「真の12.4に極限まで近い値」の2進数　+1100.0110011001100110011001100110011001100110011001101
 ----------------------------------------------------------------------------------------------
  (計)　小数点以下のビット数が同じなら +11000.1011001100110011001100110011001100110011001100111 となるハズが
 　　　　　 　　　　　　　　　　　　　 +11000.101100110011001100110011001100110011001100110100　
                                                                                       　 ~↑~
 　　　　　　　　　　　　　整数部分で１桁余分に使うことになったので、小数部分が1桁減り、丸めが起こった。
 　　　　　　　　　　　　　詳しい丸めのルールは私の能力(脳力？）では理解できておりません。
 　　　　　　　　　　　　　その理解が必要とも思っておりません。後述の心得を参照。

 同様に12.1と12.6について考えると
 　　　 +1100.0001100110011001100110011001100110011001100110011
 　　　　　　 ↓0.5大きい　
 　 　　+1100.1001100110011001100110011001100110011001100110011
 　　　--------------------------------------------------------
 (計)　+11000.1011001100110011001100110011001100110011001100110 となるハズが１桁減少で・・、
 　　　+11000.101100110011001100110011001100110011001100110011　あれっ、最終桁は0になる組合せだったので丸めは無影響かぁ。

 毎回、こんな分析やってられないですよねぇ。

 ・・で、心得として、
 (1)真の小数点値を把握するなんて無謀なことは考えない。
 (2)十分に桁が低い所でRound関数を使って事前処理しておけば、実務上差し支えない。(科学技術系の計算は別)

（半平太） 2018/09/01(土) 21:00

 心得が不十分でした。

 <追加>
 (3)小数が絡む計算をした場合で、異同大小を判定する処理が入る時は、
 　 小数演算誤差が発生しているとの前提で対策を考える必要がある。

 　 ※実際に発生しているかどうかなんて、いちいち調べない。

（半平太） 2018/09/01(土) 21:42

こんばんは。
既に解説をいただいていますので、私は参考サイトを追加で紹介します。
　
10進法の普通の計算では、0.1はきっちりとした数ですが、
2進法では、例えば、0.5 , 0.25 , 0.125 ,・・・・などが誤差のない「ちょっきり」の数字です。
例えば、10進数での 0.1と完全に一致するものを2進法では表しきれません。
一番近いもので代替しているということです。
このあたりのことを理解されることが出発点でしょうか。
　
既に解説頂いているところですが、
このあたりのことは下記のネット記事が参考になるかもしれません。
　
（日経PC21 という雑誌に載っていた「演算誤差」に関する記事です。
　雑誌のサイトは閉じられてしまったので、外国のアーカイブに保存されたものを紹介します。）
http://web.archive.org/web/20160413003500/http://pc.nikkeibp.co.jp/pc21/special/gosa
http://web.archive.org/web/20160815202704/http://pc.nikkeibp.co.jp/pc21/special/gosa/eg1.shtml
http://web.archive.org/web/20160807013422/http://pc.nikkeibp.co.jp/pc21/special/gosa/eg2.shtml
http://web.archive.org/web/20160726032410/http://pc.nikkeibp.co.jp/pc21/special/gosa/eg3.shtml
http://web.archive.org/web/20160808054439/http://pc.nikkeibp.co.jp/pc21/special/gosa/eg4.shtml
第4回で、そうした内部の管理の話が載っていますが、3回目までをざっとごらんになれば
感じはつかめると思います。

（γ） 2018/09/01(土) 22:04

IEEE 754における最後の桁の丸めに関する話は、
紹介した記事の第4回のところに説明があるようです。
　
「IEEE 754 では丸め方が 4 通りあって、ユーザーが選べるようになっているの。
　でも普通はデフォルトの最近偶数丸め(Round to Nearest-Even)という方法で丸めるんだ」
とあり、それを使う理屈も説明されているようです。
　
以下余談です。
とある処理系の紹介論文に面白い例が載っていたのでご紹介。
　
(i,j) 要素が 1/(1+i+j) である 15×15の二次元配列を作り、
各種の丸め方法でその逆行列の(1,1)要素をとると、
・RoundNearest 　154410.55589294434
・RoundUp -49499.606132507324
・RoundDown -841819.4371948242
・bigfloat precision(300)だと、

 -2.0939717925074627012828017421448951616270885770371495976323268904715350765882491054998376252e+03
となるんだそうな。
科学技術系の話は予想もつかないことになるんですなあ。
（この行列はHilbert-like matrixとあり、逆行列が不安定であることが知られているらしい）

（γ） 2018/09/02(日) 13:21

詳しい解説やサイトご紹介、誠にありがとうございます。

 >全て同じ差になったのは、たまたまです

・・・って、　本当にズッコケましたよ…

 >表示メカニズムが人間の期待する通りに動いている保証はありません
 >表示できる桁では同じに見えると言うだけで中身は違う
 >「IEEE 754」こそが演算誤差の原因・・（サイトにありましたよね）
 >科学技術系の話は予想もつかないことになる・・

こういう知識って真っ先に学習すべきかと思うのですが、これをトップに
持ってきているサイトって知らないです、私は。
（エクセル使いには常識すぎるから？？？）

今回はたまたま結果の異常に気付きましたが、この手の処理は頻繁に行っているので
全てのマクロを見直す必要がありそうです。（涙）

皆さん、貴重なお時間ありがとうございました。
これからもよろしくお願いします…
（T18） 2018/09/02(日) 16:25

コメント返信：

[ 一覧(最新更新順) ]

[[20180831131820]] 『演算誤差について』（T18） ページの最後に飛ぶ

[[20180831131820]]　『演算誤差について』（T18）　ページの最後に飛ぶ