Google表格是否比Microsoft Excel准确或更小? [英] Is Google Sheets less or more accurate than Microsoft Excel?

问题描述

我知道带有浮点数的数字不准确的普遍问题，但是我希望Excel和Google表格的行为相同.不幸的是，他们没有-请参见以下示例:

I am aware of the general problem of numerical inaccuracies with floating point numbers, but I would expect Excel and Google Sheets to behave the same. Unfortunately, they don't - see the following example:

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

那么明显的问题是什么?

So what are the obvious problems?

• A3:Google表格表示差异非零的数字相等( shudder )
• A4:Excel显示行为不同的数字(C1，C2等)之间的零差
• B2/D2:都向上舍入 一个比舍入后的数字小0.005的数字

• A3: Google Sheets says that numbers which have a non-zero difference are equal (shudder)
• A4: Excel shows a zero difference between numbers that behave differently (C1, C2, etc.)
• B2/D2: both round up a number which is more than 0.005 smaller than the rounded number

我的问题是:

• 我应该期望两者的行为有所不同吗?
• 差异是不同实现方式(可能是数字格式)的无意副产品还是有意?
• 此文件记录在某处吗?

推荐答案

观察

以下观察是在Google Sheets(网络版本为2020-07-02，macOS 10.14.6上具有Safari 12.1.2)，Microsoft Excel 2008 for Mac 12.3.6和Numbers 10.0上进行的.在Excel中，偏好设置…>计算>设置显示的精度"被禁用.

Observations

The following observations were made on Google Sheets (web version on 2020-07-02 with Safari 12.1.2 on macOS 10.14.6), Microsoft Excel 2008 for Mac 12.3.6, and Numbers 10.0. In Excel, Preferences… > Calculation > "Set precision as displayed" was disabled.

已设置单元格:

• 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).

• 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:在所有三个电子表格中均为"TRUE".
• A4:在所有三个电子表格中均为"0".
• A5:Google表格中的"0"，Excel中的"-1.77636E-15"，数字中的"0".
• A6:在Google表格中为"-1.776356839"，在Excel中为"0"，在数字中为"0".
• A7:在Google表格中为"-1.776356839"，在Excel中为"-1.776356839"，在数字中为"0".
• A8:在Google表格中为"0"，在Excel中为"0"，在数字中为"-1".

如果单元格格式从自动"更改为科学"，则Google表格结果会更改:

If the cell formats are changed from Automatic to Scientific, then the Google Sheets results change:

• A4:-1.78E-15".
• A5:-1.78E-15".

否则，除外观更改外，其他单元格或电子表格中没有其他更改，例如显示"0.00E + 00"而不是"0".

Otherwise, there are no changes in other cells or spreadsheets except cosmetic changes, such as showing "0.00E+00" instead of "0".

以上观察与以下观点一致:

The above observations are consistent with:

• Google表格使用IEEE-754 64位二进制格式，并且不会影响舍入.(最初显示为"0"是由于自动选择了定点或类似格式.)
• Excel使用IEEE-754 64位二进制格式，并根据所使用的公式对舍入进行模糊处理.也就是说，四舍五入不仅仅取决于要四舍五入的数字.它取决于上下文.此外，舍入不仅用于显示；还可以用于显示.它将应用于单元格值，因此进一步的计算将使用四舍五入后的结果.
• 数字使用十进制浮点格式，其中有效数字为34位.没有观察到错误的舍入.

在Google Sheets和Excel中观察到的−1.776356839•10 ⁻¹⁵ 值是IEEE-754 64位二进制格式的特征，因此似乎很有可能他们正在使用该格式.在Numbers中观察到的−10 ^-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中对接近零的数字进行四舍五入的情况，后舍入后的值用于进一步计算.另一个实验是构造一个不是一个但显示为一个简单公式的数字，然后使用该单元格进行进一步的计算以查看是否使用了四舍五入前或四舍五入后的值.

The above notes rounding in Excel for numbers near zero, with the post-rounded value used in further calculations. Another experiment would be to construct a number that is not one but that displays as one yet has a simple formula and then use that cell in further calculations to see if the pre-rounding or post-rounding value is used.

已设置单元格:

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

49的原因是，当使用IEEE-754 binary64进行评估时， 1/49 * 49 会产生一个略低于1的数字.1/3 * 3 ，因为尽管除法中有一个舍入，但乘法中也有一个舍入进行补偿，从而产生正好为1的结果.49是不会发生此结果的最小整数.)

49 was chosen because, when evaluated with IEEE-754 binary64, 1/49*49 yields a number slightly under 1. (This does not occur with smaller denominators, such as with 1/3*3 because, although there is a rounding in the division, there is also a rounding in the multiplication that compensates, producing a result of exactly 1. 49 is the smallest integer for which this does not occur.)

具有科学格式的结果:

• B1:在Google表格中为"-1.11E-16"，在Excel中为"0.00E + 00"，在数字中为"-2E-34".
• B2:在Google表格中为"1.00E + 00"，在Excel中为"1.00E + 00"，在数字中为"1E + 00".
• B3:在Google表格中为"-1.11E-16"，在Excel中为"0E + 00"，在数字中为"0.00E + 00".

同样，Google表格似乎使用的是IEEE-754，没有任何欺骗性.Excel似乎对结果进行了四舍五入.但是，如果添加括号，将B1设置为 =(1/49 * 49-1)或将B3设置为 =(B2-1)，Excel将显示"-1.11E-16".这与在第一组观察结果中使用括号时禁用舍入是一致的.

Again, Google Sheets seems to use IEEE-754 with no shenanigans. Excel appears to have rounded the results. However, if parentheses are added, setting B1 to =(1/49*49-1) or B3 to =(B2-1), Excel displays "-1.11E-16". This is consistent with rounding being disabled when parentheses were used in the first set of observations.

但是，现在我们第一次在数字中看到一些恶作剧.我们希望B1和B3显示相同的结果，但事实并非如此.假设:数字在公式中使用的精度更高，但在单元格的最终结果中使用的精度更低?

However, now we see some shenanigans in Numbers for the first time. We would expect B1 and B3 to show the same result, but they do not. Hypothesis: Numbers uses more precision within a formula but less for the final result of a cell?

B1结果与34个十进制数字一致.对于34个有效十进制数字，1/49是02040816326530612244897959183673469.然后是49倍，即.99999999999999999999999999999999981.四舍五入到34个有效数字会得出.99999999999999999999999999999999999，然后减1得出−2•10 ⁻³⁴ .

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个十进制数字，并将其用于显示和进一步计算.

Further experimentation, not detailed here, suggests Numbers calculates internally with 34 decimal digits but rounds the final cell value to 15 decimal digits and uses that for display and for further calculation.

这篇关于Google表格是否比Microsoft Excel准确或更小?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！