((A+B)*(A+B)-(A*A))-2*(A+B/2)*B＝0,Excel計算結果卻不完全等於0

[huai127]

計算式：
((A+B)*(A+B)-(A*A))-2*(A+B/2)*B＝0
但Excel計算結果卻不完全等於0，如下表.
為什麼?

A B ((A+B)*(A+B)-(A*A))-2*(A+B/2)*B
10.100000000000000000 0.200000000000000000 -0.000000000000015987212
78.182600000000000000 0.571300000000000000 -0.000000000000994759830
90.139400000000000000 0.442500000000000000 -0.000000000001548983164
94.319700000000000000 0.273800000000000000 -0.000000000000120792265
72.047500000000000000 0.720000000000000000 -0.000000000000568434189
10.100000000000000000 0.250000000000000000 0.000000000000000000000
115.230000000000000000 3.330000000000000000 0.000000000000909494702
76.553689543863600000 7.419798566104940000 0.000000000000000000000
44.448017904417100000 0.545413571360671000 -0.000000000000142108547
58.108291714161200000 2.153265742579050000 0.000000000000000000000
99.895078614669700000 5.484724262901020000 0.000000000001818989404
79.704155161824400000 7.956699664743710000 0.000000000000000000000
12.417591492261200000 2.574623018655460000 0.000000000000000000000
52.536849222658800000 1.839927735985860000 -0.000000000000341060513
52.601664153447400000 5.784453809934160000 0.000000000000000000000
28.138652896793500000 9.288368476958180000 0.000000000000000000000
84.308832638385800000 9.316935351103770000 0.000000000000000000000
8.320404706547470000 0.629362873727723000 0.000000000000000000000
99.294559967722100000 9.887741960227840000 0.000000000000000000000
93.852748068438700000 8.749465165267880000 0.000000000000000000000
49.084522600088200000 0.644206905596711000 0.000000000000106581410
99.647965864794000000 6.181011559115420000 0.000000000000000000000
40.667471986314300000 7.858053234652560000 0.000000000000000000000
82.962111052139900000 2.312051669460650000 -0.000000000000795807864
42.172316281028000000 5.386366173409470000 0.000000000000000000000
53.883025577904000000 0.207812547214942000 0.000000000000074606987
19.192310144051200000 3.673045515510860000 0.000000000000000000000
57.139391588826800000 8.208601398563230000 0.000000000000000000000
89.206567851165200000 7.837121650374730000 0.000000000000000000000
71.689039252041000000 1.033064843053240000 0.000000000000000000000
79.207933327344400000 1.812471758632590000 0.000000000001648459147
47.484204141801400000 4.731643569574090000 0.000000000000000000000
43.197875149986600000 5.942444996414960000 0.000000000000000000000
53.060608235202500000 3.053440926420400000 0.000000000000000000000

[Tony]

§Ú¥ÎExcel 2003­pºâ1/3¡A
µ²ªG¬O 0.333333333333333000000000000000¡AŠA§â¥Š­Œ¥H 3¡A
­pºâªºµ²ªG¬O 1.000000000000000000000000000000¡CŠü¥GExcel €]°µ€F€@šÇ¯S§O³B²z¡C
§Ú€]Ž¿š£¹L€@šÇ€@¯ëªº­pŒÆŸ÷¡AÅã¥ÜŸ¹€ºŠsªº€pŒÆŠì€ñÅã¥Üªº€pŒÆŠìžûŠh¡A
1/3*3 ªº­pºâµ²ªG¬O1 ¡C
©Ò¥H§Ú«Øij¥Îžû€Ö€pŒÆŠìÅã¥Ü((A+B)*(A+B)-(A*A))-2*(A+B/2)*B ªºµ²ªG¡C

1/3=
0.3333333
*3
0.9999999
ŒÆŸÇ€W 1/3*3 = 1 ¡AŠý¬O§A¬°Šó¯à±µšü­pºâŸ÷·|ÅÜŠš 0.9999999 ¡HŠ]¬°§AªŸ¹D³o¬O­pºâŸ÷ªº¯à€O­­šî¡C
ŠPŒËªº²z¥Ñ¡A³o¬O¹qž£­pºâªº³W®æ­­šî¡C
¯S§OªºŒÆŸÇ³nÅéšÒŠp MatLAB, Mathmatica €]­n³]©w¬ÛÃö¿ï¶µ€~·|¯S§O³B²z³oºØ­pºâ»~®t°ÝÃD¡C
--
œÐ²ŸÂàŠÜ·L³nµ{Š¡³]­p¡Bšt²ÎºÞ²zšÏ¥Î·s§Þ³Nœ×ŸÂ°Qœ×°Ï¡AŽ£šÑºô€ÍŠ^ÂЫá§Y®É²°T¡B¹q€l¶l¥ó³qªŸ¡G
µ{Š¡¶}µo³]­p¡Ghttp://forums.microsoft.com/msdn-cht/default.aspx?siteid=14

­pºâŠ¡¡G
((A+B)*(A+B)-(A*A))-2*(A+B/2)*B¡×0
ŠýExcel­pºâµ²ªG«o€£§¹¥þµ¥©ó0¡AŠp€Uªí.
¬°€°»ò?
A B ((A+B)*(A+B)-(A*A))-2*(A+B/2)*B
10.100000000000000000 0.200000000000000000 -0.000000000000015987212
78.182600000000000000 0.571300000000000000 -0.000000000000994759830
90.139400000000000000 0.442500000000000000 -0.000000000001548983164
94.319700000000000000 0.273800000000000000 -0.000000000000120792265
72.047500000000000000 0.720000000000000000 -0.000000000000568434189
10.100000000000000000 0.250000000000000000 0.000000000000000000000
115.230000000000000000 3.330000000000000000 0.000000000000909494702
76.553689543863600000 7.419798566104940000 0.000000000000000000000
44.448017904417100000 0.545413571360671000 -0.000000000000142108547
58.108291714161200000 2.153265742579050000 0.000000000000000000000
99.895078614669700000 5.484724262901020000 0.000000000001818989404
79.704155161824400000 7.956699664743710000 0.000000000000000000000
12.417591492261200000 2.574623018655460000 0.000000000000000000000
52.536849222658800000 1.839927735985860000 -0.000000000000341060513
52.601664153447400000 5.784453809934160000 0.000000000000000000000
28.138652896793500000 9.288368476958180000 0.000000000000000000000
84.308832638385800000 9.316935351103770000 0.000000000000000000000
8.320404706547470000 0.629362873727723000 0.000000000000000000000
99.294559967722100000 9.887741960227840000 0.000000000000000000000
93.852748068438700000 8.749465165267880000 0.000000000000000000000
49.084522600088200000 0.644206905596711000 0.000000000000106581410
99.647965864794000000 6.181011559115420000 0.000000000000000000000
40.667471986314300000 7.858053234652560000 0.000000000000000000000
82.962111052139900000 2.312051669460650000 -0.000000000000795807864
42.172316281028000000 5.386366173409470000 0.000000000000000000000
53.883025577904000000 0.207812547214942000 0.000000000000074606987
19.192310144051200000 3.673045515510860000 0.000000000000000000000
57.139391588826800000 8.208601398563230000 0.000000000000000000000
89.206567851165200000 7.837121650374730000 0.000000000000000000000
71.689039252041000000 1.033064843053240000 0.000000000000000000000
79.207933327344400000 1.812471758632590000 0.000000000001648459147
47.484204141801400000 4.731643569574090000 0.000000000000000000000
43.197875149986600000 5.942444996414960000 0.000000000000000000000
53.060608235202500000 3.053440926420400000 0.000000000000000000000

[huai127]

我了解這是浮點數格式造成數值計算誤差，是電腦計算的規格限制。
但是為什麼有些會等於零？而且等於零的較多(第8組數據起是以亂數產生)，若是浮點數格式造成數值計算的誤差，等於零的應該較少才合理。
就像Excel計算1/3*3的結果為1而不是0.999999999999999 ，顯然Excel 有某種修正機制，我比較好奇的是：Excel
的修正機制何種情況下才有作用？。

"??" 來函：

這邊只是拿一般計算機來舉例何謂計算誤差。
電腦用的浮點數依據 IEEE 754 的定義是用 2^n 來表示，若為小數時，n 為負數，像小學教的二分逼近法。所以電腦這邊的計算誤差不好形容，改用一般計算機的計算誤差來說明。
--
請移轉至微軟程式設計、系統管理使用新技術論壇討論區，提供網友回覆後即時簡訊、電子郵件通知：
程式開發設計：http://forums.microsoft.com/msdn-cht/default.aspx?siteid=14
操作管理技術：http://forums.microsoft.com/technet-cht/default.aspx?siteid=23

我用Excel 2003計算1/3，
結果是 0.333333333333333000000000000000，再把它乘以 3，
計算的結果是 1.000000000000000000000000000000。似乎Excel 也做了一些特別處理。
我也曾見過一些一般的計數機，顯示器內存的小數位比顯示的小數位較多，
1/3*3 的計算結果是1 。
所以我建議用較少小數位顯示((A+B)*(A+B)-(A*A))-2*(A+B/2)*B 的結果。

計算機概論（電腦概論）會說到這部份，這部分屬於 IEEE 754 定義浮點數格式造成數值計算誤差，一般軟體都沒辦法避免，只有特別數學軟體才會處理這類問題。
舉個例子，拿出一般的計算機：
1/3=
0.3333333
*3
0.9999999
數學上 1/3*3 = 1 ，但是你為何能接受計算機會變成 0.9999999 ？因為你知道這是計算機的能力限制。
同樣的理由，這是電腦計算的規格限制。
特別的數學軟體例如 MatLAB, Mathmatica 也要設定相關選項才會特別處理這種計算誤差問題。
--
請移轉至微軟程式設計、系統管理使用新技術論壇討論區，提供網友回覆後即時簡訊、電子郵件通知：
程式開發設計：http://forums.microsoft.com/msdn-cht/default.aspx?siteid=14
操作管理技術：http://forums.microsoft.com/technet-cht/default.aspx?siteid=23

Post by huai127
計算式：
((A+B)*(A+B)-(A*A))-2*(A+B/2)*B＝0
但Excel計算結果卻不完全等於0，如下表.
為什麼?
A B ((A+B)*(A+B)-(A*A))-2*(A+B/2)*B
10.100000000000000000 0.200000000000000000 -0.000000000000015987212
78.182600000000000000 0.571300000000000000 -0.000000000000994759830
90.139400000000000000 0.442500000000000000 -0.000000000001548983164
94.319700000000000000 0.273800000000000000 -0.000000000000120792265
72.047500000000000000 0.720000000000000000 -0.000000000000568434189
10.100000000000000000 0.250000000000000000 0.000000000000000000000
115.230000000000000000 3.330000000000000000 0.000000000000909494702
76.553689543863600000 7.419798566104940000 0.000000000000000000000
44.448017904417100000 0.545413571360671000 -0.000000000000142108547
58.108291714161200000 2.153265742579050000 0.000000000000000000000
99.895078614669700000 5.484724262901020000 0.000000000001818989404
79.704155161824400000 7.956699664743710000 0.000000000000000000000
12.417591492261200000 2.574623018655460000 0.000000000000000000000
52.536849222658800000 1.839927735985860000 -0.000000000000341060513
52.601664153447400000 5.784453809934160000 0.000000000000000000000
28.138652896793500000 9.288368476958180000 0.000000000000000000000
84.308832638385800000 9.316935351103770000 0.000000000000000000000
8.320404706547470000 0.629362873727723000 0.000000000000000000000
99.294559967722100000 9.887741960227840000 0.000000000000000000000
93.852748068438700000 8.749465165267880000 0.000000000000000000000
49.084522600088200000 0.644206905596711000 0.000000000000106581410
99.647965864794000000 6.181011559115420000 0.000000000000000000000
40.667471986314300000 7.858053234652560000 0.000000000000000000000
82.962111052139900000 2.312051669460650000 -0.000000000000795807864
42.172316281028000000 5.386366173409470000 0.000000000000000000000
53.883025577904000000 0.207812547214942000 0.000000000000074606987
19.192310144051200000 3.673045515510860000 0.000000000000000000000
57.139391588826800000 8.208601398563230000 0.000000000000000000000
89.206567851165200000 7.837121650374730000 0.000000000000000000000
71.689039252041000000 1.033064843053240000 0.000000000000000000000
79.207933327344400000 1.812471758632590000 0.000000000001648459147
47.484204141801400000 4.731643569574090000 0.000000000000000000000
43.197875149986600000 5.942444996414960000 0.000000000000000000000
53.060608235202500000 3.053440926420400000 0.000000000000000000000