Google 表格比 Microsoft Excel 更准确还是更准确？

我知道浮点数数值不准确的普遍问题，但我希望 Excel 和 Google Sheets 的行为相同。不幸的是，他们没有 - 请参阅以下示例：

A1: 15.525         our reference
A2: =3*5.175       should equal 15.525
A3: =A2=A1         shows TRUE, as expected, in both
A4: =A2-A1         but A2 is actually smaller in Google Sheets, by -1.78E-15 [Excel shows 0.00E+00]

B1: =ROUND(A1,2)   shows 15.53, correct, in both
B2: =ROUND(A2,2)   shows 15.53, correct, in both - surprisingly in Google Sheets, looking at A4
B3: =B2=B1         shows TRUE, as expected, in both

C1: =B1-A1         shows 0.00499999999999901, in both
C2: =B2-A2         shows 0.00500000000000078, in both
C3: =C2=C1         shows FALSE, as expected, in both

D1: =C1<0.005      consistent with C1, in both
D2: =C2>0.005      consistent with C2, but inconsistent with correct rounding in B2, in both

那么有哪些明显的问题呢？

• A3：Google Sheets 表示差异非零的数字是相等的（shudder)
• A4：Excel 显示表现不同的数字（C1、C2 等）之间的零差异
• B2/D2：两轮up比四舍五入后的数字小 0.005 以上的数字

我的问题是：

• 我应该期望两者的行为有所不同吗？
• 差异是不同实现（可能是数字格式）的无意副产品，还是有意为之？
• 这有记录在某处吗？

观察结果

以下观察结果是在 Google Sheets（2020 年 7 月 2 日的网页版，macOS 10.14.6 上使用 Safari 12.1.2）、Microsoft Excel 2008 for Mac 12.3.6 和 Numbers 10.0 上进行的。在 Excel 中，首选项... > 计算 >“设置显示精度”已禁用。

单元格设置：

• A1: 15.525.
• A2: =3*5.175.
• A3: =A2=A1.
• A4: =A2-A1.
• A5: =(A2-A1).
• A6: =A4*1000000000000000.
• A7: =A5*1000000000000000.
• A8: =(1/3*3-1)*POWER(10,34).

观察到的结果是：

• A3：所有三个电子表格中的“正确”。
• A4：所有三个电子表格中的“0”。
• A5：Google 表格中的“0”，Excel 中的“-1.77636E-15”，Numbers 中的“0”。
• A6：Google 表格中为“-1.776356839”，Excel 中为“0”，Numbers 中为“0”。
• A7：Google 表格中为“-1.776356839”，Excel 中为“-1.776356839”，Numbers 中为“0”。
• A8：Google 表格中为“0”，Excel 中为“0”，Numbers 中为“-1”。

如果单元格格式从“自动”更改为“科学”，则 Google 表格结果会发生变化：

• A4：“-1.78E-15”。
• A5：“-1.78E-15”。

否则，除了外观更改（例如显示“0.00E+00”而不是“0”）之外，其他单元格或电子表格中没有任何更改。

假设

上述观察结果与：

• Google Sheets 使用 IEEE-754 64 位二进制格式，并且不会捏造舍入。 （最初显示“0”是由于自动选择定点或类似格式。）
• Excel 使用 IEEE-754 64 位二进制格式，并根据所使用的公式进行舍入。也就是说，舍入不纯粹是被舍入的数字的函数；它是依赖于上下文的。此外，四舍五入不仅仅是为了显示；也是为了显示。它应用于单元格值，因此进一步的计算使用舍入后的结果。
• Numbers 使用十进制浮点格式，尾数为 34 位。没有观察到捏造的舍入。

进一步的调查

The −1.776356839•10⁻¹⁵ value observed in Google Sheets and Excel is characteristic of the IEEE-754 64-bit binary format, so it seems quite likely they are using that format. The −10⁻³⁴ value observed in Numbers is less conclusive, so the hypothesis it is using a decimal format should be investigated further.

上述注释在 Excel 中对接近零的数字进行四舍五入，四舍五入后的值用于进一步计算。另一个实验是构造一个不是 1 但显示为 1 且具有简单公式的数字，然后在进一步计算中使用该单元格来查看是否使用了舍入前或舍入后的值。

更多观察

单元格设置：

• B1: =1/49*49-1.
• B2: =1/49*49.
• B3: =B2-1.

选择 49 是因为，当使用 IEEE-754 二进制 64 进行评估时，1/49*49产生一个略低于 1 的数字。（对于较小的分母，例如分母，不会发生这种情况1/3*3因为，虽然除法中有舍入，但乘法也有舍入进行补偿，产生的结果恰好为 1。49 是不会发生这种情况的最小整数。）

结果，科学格式：

• B1：Google 表格中的“-1.11E-16”，Excel 中的“0.00E+00”，Numbers 中的“-2E-34”。
• B2：Google 表格中的“1.00E+00”，Excel 中的“1.00E+00”，Numbers 中的“1E+00”。
• B3：Google 表格中的“-1.11E-16”，Excel 中的“0E+00”，Numbers 中的“0.00E+00”。

同样，Google Sheets 似乎毫无恶意地使用了 IEEE-754。 Excel 似乎对结果进行了四舍五入。但是，如果添加括号，则将 B1 设置为=(1/49*49-1)或 B3 至=(B2-1)，Excel 显示“-1.11E-16”。这与在第一组观察中使用括号时禁用舍入是一致的。

然而，现在我们第一次在《民数记》中看到一些恶作剧。我们期望 B1 和 B3 显示相同的结果，但事实并非如此。假设：数字在公式中使用更高的精度，但在单元格的最终结果中使用更少的精度？

The B1 result is consistent with 34 decimal digits. To 34 significant decimal digits, 1/49 is .02040816326530612244897959183673469. Then 49 times that is .99999999999999999999999999999999981. Rounding to 34 significant digits gives .9999999999999999999999999999999998, and then subtracting 1 gives −2•10⁻³⁴.

进一步的实验（此处未详细说明）表明 Numbers 在内部使用 34 位十进制数字进行计算，但将最终单元格值四舍五入为 15 位十进制数字，并将其用于显示和进一步计算。