Microsoft Excel中的数字精度

与其他电子表格一样，MicrosoftExcel只在有限的精度下工作，因为它只保留了一定数量的数字来描述数字（它的精度有限）。除了一些关于错误值、无限值和非正常化数字的例外情况，Excel以IEEE754规范中的双精度浮点格式进行计算（除了数字，Excel还使用其他一些数据类型）。虽然Excel可以显示小数点后30位，但对于一个指定的数字，其精度仅限于15位有效数字，而且由于以下五个问题，计算的精度可能更低：四舍五入、截断和二进制存储、计算中操作数偏差的累积，以及最糟糕的情况：减法时的取消，以及减去相似大小的值时的”灾难性取消”。

在上图中显示了Excel中的分数1/9000。虽然这个数字的十进制表示法是一串无限的1，但Excel只显示前15位数字。在第二行中，数字1被添加到分数中，同样Excel只显示15个数字。在第三行中，用Excel将1从总和中减去。因为小数点后的和只有11个1，所以减去’1’后的真正差值是3个0，然后是一串11个1。然而，Excel报告的差值是三个0，后面是一串15位的13个1和两个额外的错误数字。因此，Excel计算出来的数字并不是它所显示的数字。此外，Excel答案中的误差并不是简单的四舍五入，它是浮点计算中的一种效应，称为”取消”。Excel计算中的不准确比15位有效数字的精度造成的误差更复杂。Excel以二进制格式存储数字也会影响其准确性。为了说明问题，下图将几个x值的简单加法1+x-1制成表格，所有的x值都是从小数点后15位开始的，所以Excel必须将它们考虑在内。在计算和1+x之前，Excel首先将x近似为一个二进制数。如果这个二进制版本的x是2的简单幂，那么x的15位十进制近似值就会被储存在和中，图中最上面的两个例子表示恢复x没有错误。在第三个例子中，x是一个更复杂的二进制数，x=1.110111⋯111×2-49（共15比特）。这里15位数字产生的’IEEE754双值’是3.330560653658221E-15，在’用户界面’上，Excel将其四舍五入为15位3.33056065365822E-15，然后用30位小数显示，再加上一个’假零’，因此样本中的’二进制’和’十进制’值仅在显示上是相同的，与单元格相关的值是不同的（1.11011111110000000000000000×2-49与1.11011111101111111101×2-49）。与其他电子表格类似，处理不同数量的小数位可以准确地存储在”双数”的53位尾数中（例如，在1和8之间有16位，但在0,5和1之间以及8和10之间只有15位）是有些困难的，并解决了”次优”。在第四个例子中，x是一个不等同于简单二进制的十进制数（尽管它与第三个例子中的二进制数显示的精度一致）。十进制输入被二进制近似，然后使用该十进制。图中的这两个中间的例子表明，引入了一些误差。

最后两个例子说明了如果x是一个相当小的数字会发生什么。在倒数第二个例子中，x=1.110111⋯111×2-50；总共有15位。二进制被非常粗略地替换成2的单次方（在这个例子中，2-49），并使用其十进制等价物。在下面的例子中，一个与上面的二进制相同的十进制的精度，但与二进制的近似方式不同，被截断到15位有效数字，对1+x-1没有贡献，导致x=0。对于不是2的简单幂的x′，即使x很大，也会出现1+x-1的明显误差。例如，如果x=1/1000，那么1+x-1=9.9999999999989×10-4，这是一个13位有效数字的误差。在这种情况下，如果Excel只是简单地对十进制数字进行加减，避免转换为二进制再转换为十进制，就不会出现四舍五入的错误，精度实际上会更好。Excel有一个选项是设置显示的精度。有了这个选项，根据情况，精度可能会变好或变差，但你会清楚知道Excel在做什么。(只有选定的精度被保留，而且不能通过反转这个选项来恢复额外的数字）。一些类似的例子可以在这个链接中找到。