Excel 中的浮点运算可能提供不准确的结果

Summary

本文讨论 Microsoft Excel 如何存储和计算浮点数字。 由于四舍五入或数据截断,这可能会影响某些数字或公式的结果。

概述

Microsoft Excel 围绕 IEEE 754 规范进行了设计,可确定它存储和计算浮点数的方式。 IEEE 是电气和电子工程师协会,它是一种国际主体,用于确定计算机软件和硬件的标准。 754规范是一种非常广泛采用的规范,描述了如何在二进制计算机中存储浮点数字。 它很常见,因为它允许将浮点数存储在合理的空间量和计算中,以相对快的速度进行计算。 754标准在浮点单位和数字数据处理器中使用,几乎所有基于 PC 的所有基于 PC 的微处理器都实现浮点数学,包括 Intel、Motorola、Sun 和 MIPS 处理器。

存储数字时,相应的二进制数可以表示每个数字或分数。 例如,分数1/10 可以用十进制数系统表示为0.1。 但是,与二进制格式相同的数字将成为以下重复的二进制十进制数:

0001100110011100110011(等等)

这可以无限重复。 此数字不能用有限的空间量来表示。 因此,在存储时,此数字会按约-2.8 E-17 向下舍入。

但是,存在以下三个一般类别的 IEEE 754 规范的一些限制:

  • 最大/最小限制
  • 精密
  • 重复的二进制数

详细信息

最大/最小限制

所有计算机都具有可处理的最大和最小数字。 由于存储数字的内存位数是有限的,因此,可以存储的最大或最小数量也是有限的。 对于 Excel,可存储的最大值是 1.79769313486232 E + 308,可存储的最小正数为 2.2250738585072 E-308。

我们遵循 IEEE 754 的情况

  • 下溢:生成的数字因过小而无法表示时出现下溢。 在 IEEE 和 Excel 中,结果为0(但 IEEE 具有-0 的概念除外,Excel 不会)。
  • 溢出:当数字太大而无法表示时,将发生溢出。 Excel 在这种情况下使用其自己的特殊表示形式(#NUM!)。

我们不遵循 IEEE 754 的情况

  • 非规范化数字:非规范化数字以0的指数表示。 在这种情况下,整个数字存储在尾数中,而尾数没有隐式前导1。 因此,将丢失精度,而数字越小,精度越小。 此范围的小型端的数字只有一位精度。

    示例:规范化的数字具有隐式前导1。 例如,如果尾数代表0011001,则规范化的数字将变为10011001,因为有暗示的前导1。 非规范化数字没有隐式前导1,因此在我们0011001的示例中,不规范的数字保持不变。 在这种情况下,规范化的数字有八个有效数字(10011001),而非规范化数有五个有效数字(11001),且前导零无关紧要。

    非规范化数字基本上是一种替代方法,允许小于要存储的正常下限的数字。 Microsoft 不实现此可选的规范部分,因为非规范化数字的本质上有可变的有效数字位数。 这可能会导致严重错误,因此无法在计算中进行输入。

  • 正/负无穷大:当被0除时,将发生无穷大。 Excel 不支持无穷大,而是为其提供 #DIV/0! 在这些情况下出错。

  • Not a Number (NaN): NaN 用于表示无效操作(如无限大/无穷大、无穷大无穷大或-1 的平方根)。 Nan 允许程序在无效操作之前继续进行。 Excel 会立即生成一个错误,如 #NUM! 或 #DIV/0!。

精密

浮点数以二进制形式存储在65位范围内的三个部分:符号、指数和尾数。 ||||| |---|---|---|---| |1个符号位|11位指数|1个隐含位|52位尾数|

符号存储数字的符号(正数或负数),指数存储2的幂的幂(最大/最小的2为 + 1023 和-1022),尾数存储实际的数字。 尾数的有限存储区域限制两个相邻的浮点数可以接近的程度(即精度)。

尾数和指数均存储为单独的组件。 因此,可能的精度取决于所操作的数字(尾数)的大小。 在 Excel 中,尽管 Excel 可以将 1.79769313486232 E 308 中的数字存储到 2.2250738585072 E-308 中,但它只能在15位精度内实现。 此限制是严格遵循 IEEE 754 规范的直接结果,不是 Excel 的限制。 在其他电子表格程序中也能找到这一级别的精度。

浮点数字以以下形式表示,其中指数是二进制指数:

X = 分数 * 2 ^ (指数-偏置)

分式是数字的规范化小数部分,已规范化,因为指数将进行调整,以便前导位始终为1。 这样一来,不必存储它,而是获得一个更多的精度。 这就是为什么有隐含位的原因所在。 这类似于科学记数法,在此操作中,将指数的一位设置为小数点左边的一位。除了二进制之外,您始终可以操作指数,使第一位为1,因为只有1和0。

偏置是用于避免必须存储负指数的偏移量值。 单精度数字的偏置为127和1023(十进制)用于双精度数。 Excel 使用双精度存储数字。

使用非常大的数字的示例

在新工作簿中输入以下内容:

A1: 1.2E+200
B1: 1E+100
C1: =A1+B1 

单元格 C1 中的结果值将为 1.2 E + 200,与单元格 A1 的值相同。 实际上,如果您使用 IF 函数比较单元格 A1 和 C1,例如,如果(A1 = C1),则结果将为 TRUE。 这是由仅存储15个有效数字精度的 IEEE 规范引起的。 若要能够存储上述计算,Excel 将需要至少100位精度。

使用非常小的数字的示例

在新工作簿中输入以下内容:

A1: 0.000123456789012345
B1: 1
C1: =A1+B1 

单元格 C1 中的结果值将为1.00012345678901 而不是1.000123456789012345。 这是由仅存储15个有效数字精度的 IEEE 规范引起的。 为了能够在上述情况下存储计算,Excel 将需要至少19位精度。

更正精度错误

Excel 提供了两种用于补偿舍入误差的基本方法: ROUND 函数和精度显示为 "显示工作簿" 选项或设定精度为显示工作簿选项。

方法1: ROUND 函数

使用以前的数据,下面的示例使用 ROUND 函数将一个数字强制为五个数字。 这使您可以成功地将结果与另一个值进行比较。

A1: 1.2E+200
B1: 1E+100
C1: =ROUND(A1+B1,5) 

这将导致1.2 e + 200

D1: = IF (C1 = 1.2 E + 200,TRUE,FALSE)

这将导致值为 TRUE。

方法2:显示的精度

在某些情况下,您可能可以通过使用 * * 精度显示 * * 选项来防止舍入误差影响工作。 此选项将工作表中每个数字的值强制为显示值。 若要启用此选项,请按照以下步骤操作。

  1. 在 "文件" 菜单上,单击 "选项",然后单击 "高级" 类别。
  2. 在 "计算此工作簿时" 部分,选择所需的工作簿,然后选中 " 将精度设定为显示" 复选框。

例如,如果选择显示两个小数位数的数字格式,然后打开 "  以显示精度为准" 选项,则在保存工作簿时,除两个小数位数之外的所有精度都将丢失。 此选项影响活动工作簿,包括所有工作表。 您无法撤消此选项并恢复丢失的数据。 建议您在启用此选项之前保存工作簿。

重复的二进制数字和具有接近零结果的计算

影响二进制格式的浮点数存储的另一个混乱问题是,以十进制数为单位的有限、非重复数字的一些数字是无限的、以二进制形式的重复数字。 最常见的示例是值0.1 及其变体。 虽然这些号码可以在 base 10 中完美表示,但在将其存储在尾数中时,相同的二进制格式的数字将成为以下重复的二进制数: 

000110011001100110011(等等)

IEEE 754 规范对任何号码不提供特殊补偿。 它在尾数中存储它可以的内容,并截断其余部分。 这会导致在存储大约-2.8 E-17 或0.000000000000000028 时出现错误。

即使是常用的小数小数(如十进制的0.0001)也不能完全用二进制表示。 (0.0001 是一个重复的二进制分数,其周期为104位)。 这类似于为什么分数1/3 无法精确表示为十进制(重复0.33333333333333333333)。

例如,请考虑 Microsoft Visual Basic for Applications 中的以下简单示例: 

   Sub Main()
      MySum = 0
      For I% = 1 To 10000
         MySum = MySum + 0.0001
      Next I%
      Debug.Print MySum
   End Sub

这会将0.999999999999996 作为输出打印。 在二进制中表示0.0001 的小错误将传播到 sum。 

示例:添加一个负数

  1. 在新工作簿中输入以下内容:

    A1: = (43.1-43.2) + 1

  2. 右键单击单元格 A1,然后单击 "设置单元格格式"。 在 "数字" 选项卡上,单击 "类别" 下的 "科学 将小数位数设置为15。

Excel 显示 "0.899999999999999",而不是显示 "0.9"。 由于先计算(43.1-43.2),因此将临时存储-0.1,并将存储-0.1 中的错误引入计算中。 

值达到零时的示例

  1. 在 Excel 95 或更早版本中,在新工作簿中输入以下内容:

    A1: = 1.333 + 1.225-1.333-1.225

  2. 右键 clickcell A1,然后单击 "设置单元格格式"。 在 "数字" 选项卡上,单击 "类别" 下的 "科学 将小数位数设置为15。

Excel 95 显示 2.22044604925031 E-16,而不是显示0。

但是,Excel 97 引入了一种优化,尝试纠正此问题。 如果加法或减法运算结果的值为或接近零,Excel 97 和更高版本将补偿因将操作数转换为二进制而引入的任何错误。 上面的示例在 Excel 97 和更高版本中执行时,在科学记数法中正确显示0或 0.000000000000000 E + 00。

有关浮点数字和 IEEE 754 规范的详细信息,请参阅以下万维网网站: