在 Excel 中，浮点运算得到的结果可能不准确

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

Microsoft Excel 围绕 IEEE 754 规范进行设计，以确定它如何存储和计算浮点数。 IEEE 是电气与电子工程师协会，一个确定计算机软件和硬件标准的国际机构。 754 规范是一种非常普遍采用的规范，它介绍了如何在二进制计算机中存储浮点数。 它很受欢迎，因为它允许以合理的空间存储浮点数，并使计算能够相对快速地进行。 754 标准用于当今几乎所有实现浮点数学的基于 PC 的微处理器的浮点运算单元和数字数据处理器，包括 Intel、Motorola、Sun 和 MIPS 处理器。

存储数字时，对应的二进制数可以表示每个数字或小数。 例如，分数 1/10 在十进制记数制中可以表示为 0.1。 但是，以二进制格式的相同数字将成为以下重复的二进制小数：

0001100110011100110011（等）

此操作可以无限重复。 此数字不能在限定（有限）的空间里表示。 因此，存储此数字时，会向下舍入大约 -2.8E-17。

但是，IEEE 754 规范有一些限制，可分为三大类：

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

非规范化数字：非规范化数字由指数 0 表示。 在这种情况下，整个数字存储在尾数中，而尾数没有隐式前导 1。 如此一来，你损失了精度，而数字越小，损失的精度就越多。 此范围小端的数字精度只有一位数。

示例：规范化数字具有隐式前导 1。 例如，如果尾数表示 0011001，则由于隐式前导 1，规范化数字将变为 10011001。 非规范化数字没有隐式前导数字，因此在 0011001 示例中，非规范化数字保持不变。 在这种情况下，规范化数有八个有效数字 (10011001)，而非规范化数有五个有效数字 (11001)，前导 0 无效。

非规范化数基本上是一种允许存储小于正常下限的数字的解决方法。 Microsoft 不实现规范的这一可选部分，因为非规范化数字本质上具有可变的有效位数。 这可能导致计算中出现重大错误。

正/负无穷大：除以 0 时出现无穷大。 Excel 不支持无穷大，而是显示 #DIV/0! 这些案例中的错误。

非数字 (NaN) ：NaN 用于表示无效运算（如无穷大/无穷大、无穷大-无穷大或 -1 的平方根）。 NaN 允许程序跳过无效运算继续运行。 相反，Excel 会立即生成错误，例如 #NUM! 或者 #DIV/0!。

浮点数以二进制形式存储在 65 位范围内的三个部分：符号、指数和尾数。

符号存储数字（正或负）的符号，指数存储数字的 2 次升幂或降幂（2 的最大/最小幂分别为 +1,023 和 -1,022) ，而尾数存储实际数字。 尾数的有限存储区域限制两个相邻浮点数的接近程度（即精度）。

尾数和指数都存储为单独的组件。 因此，精度可能会有所不同，具体取决于所操作的数字（尾数）的大小。 就 Excel 而言，尽管它可以存储从 1.79769313486232E308 到 2.2250738585072E-308 之间的数字，但存储精度只能达到 15 位数。 此限制是严格遵循 IEEE 754 规范的直接结果，并非 Excel 的限制。 在其他电子表格程序中也可以找到此精度级别。

浮点数以以下形式表示，其中指数是二进制指数：

X = 小数 * 2^（指数 - 偏量）

小数是数字的规范化小数部分，由于对指数进行调整以便前导位始终为 1，因此进行了规范化。 这样一来，就不必存储它，并且还可以提升精度。 这就是存在隐式位的原因。 这类似于科学记数法，其中操作指数使小数点左侧有一位数；除二进制外，始终可以操作指数，使第一位为 1，因为只有 1 和 0。

偏量是用于避免必须存储负指数的偏量值。 单精度数字的偏量为 127，双精度数字的偏量为 1,023（十进制）。 Excel 采用双精度存储数字。

将以下内容输入到新工作簿中：

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

将以下内容输入到新工作簿中：

单元格 C1 的结果值为 1.00012345678901，而不是 1.000123456789012345。 这是由 IEEE 规范仅存储 15 个有效精度数字导致的。 为了能够存储上述计算，Excel 的精度至少需要达到 19 位数。

Excel 提供两种补偿舍入误差的基本方法：ROUND 函数和以显示精度为准或将精度设为所显示的精度工作簿选项。

以下示例使用上一数据，使用 ROUND 函数强制将数字分为五位数。 这样，就能够成功将结果与另一个值进行比较。

结果是 1.2E+200。

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

其结果值为 TRUE。

在某些情况下，可以使用“以显示精度为准”选项防止舍入误差影响你的工作。 此选项强制工作表中每个数字的值为显示值。 要打开此选项，请按照下列步骤操作：

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

影响以二进制格式存储浮点数的另一个令人困惑的问题是，在十进制基数 10 中为有限、非重复数字的一些数字在二进制中是无限、重复数字。 最常见的示例是值 0.1 及其变体。 尽管这些数字可以完美地以 10 为基数表示，但以二进制格式的相同数字存储在尾数中时，将变为以下重复的二进制数：

000110011001100110011（等）

IEEE 754 规范对任意数字都没有任何特殊限制。 它将可存储的内容存储在尾数中，并截断其余部分。 这将导致存储时出现大约 -2.8E-17 或 0.000000000000000028 的误差。

即使是常见的十进制小数（如十进制 0.0001）也不能完全以二进制形式表示。 （0.0001 是重复的二进制小数，周期为 104 位）。 这类似于为什么分数 1/3 不能以十进制精确表示（重复的 0.33333333333333333333）。

例如，考虑 Microsoft Visual Basic for Applications 中的以下简单示例：

这将打印 0.999999999999996 作为输出。 以二进制表示 0.0001 的小误差将传播到总和。

将以下内容输入到新工作簿中：

A1: =(43.1-43.2)+1

右键单击“单元格 A1”，然后单击“设置单元格格式”。 在“数字”选项卡上，单击“类别”下的“科学记数”。 将“小数位数”设置为 15。

Excel 不显示 0.9，而是显示 0.899999999999999。 由于先计算 (43.1-43.2)，因此将临时存储 -0.1，并且存储 -0.1 带来的误差将引入计算。

在 Excel 95 或更早版本中，将以下内容输入到新建工作簿中：

A1: =1.333+1.225-1.333-1.225

右键单击“单元格 A1”，然后单击“设置单元格格式”。 在“数字”选项卡上，单击“类别”下的“科学记数”。 将“小数位数”设置为 15。

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

然而，Excel 97 推出了一种尝试更正此问题的优化措施。 如果加减运算的结果等于或非常接近零，Excel 97 及更高版本将补偿因操作数与二进制相互转换而带来的任何误差。 以上在 Excel 97 及更高版本中执行的示例以科学记数法正确显示 0 或 0.000000000000000E+00。

有关浮点数和 IEEE 754 规范详细信息，请参阅以下万维网网站：