在 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。
我们遵循 IEEE 754 的案例
- 下溢：当生成的数字太小，无法表示时会发生下溢。 在 IEEE 和 Excel 中，结果为 0（除了 IEEE 有 -0 的概念，而 Excel 没有）。
- 溢出：当数字太大无法表示时会发生溢出。 Excel 针对这种情况使用自己的特殊表示形式 (#NUM!)。
我们未遵循 IEEE 754 的案例
非规范化数字：非规范化数字由指数 0 表示。 在这种情况下，整个数字存储在尾数中，而尾数没有隐式前导 1。 如此一来，你损失了精度，而数字越小，损失的精度就越多。 此范围小端的数字精度只有一位数。
示例：规范化数字具有隐式前导 1。 例如，如果尾数表示 0011001，则由于隐式前导 1，规范化数字将变为 10011001。 非规范化数字没有隐式前导数字，因此在 0011001 示例中，非规范化数字保持不变。 在这种情况下，规范化数有八个有效数字 (10011001)，而非规范化数有五个有效数字 (11001)，前导 0 无效。
非规范化数基本上是一种允许存储小于正常下限的数字的解决方法。 Microsoft 不实现规范的这一可选部分，因为非规范化数字本质上具有可变的有效位数。 这可能导致计算中出现重大错误。
正/负无穷大：除以 0 时出现无穷大。 Excel 不支持无穷大，而是显示 #DIV/0! 这些案例中的错误。
非数字 (NaN) ：NaN 用于表示无效运算（如无穷大/无穷大、无穷大-无穷大或 -1 的平方根）。 NaN 允许程序跳过无效运算继续运行。 相反，Excel 会立即生成错误，例如 #NUM! 或者 #DIV/0!。
精度
浮点数以二进制形式存储在 65 位范围内的三个部分：符号、指数和尾数。
| 符号 | 指数 | 尾数 |
|---|---|---|
| 1 个符号位 | 11 位指数 | 1 个隐式位 + 52 位小数 |
符号存储数字（正或负）的符号，指数存储数字的 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 采用双精度存储数字。
使用非常大数的示例
将以下内容输入到新工作簿中：
A1: 1.2E+200
B1: 1E+100
C1: =A1+B1
单元格 C1 的结果值为 1.2E+200，与单元格 A1 的值相同。 事实上，如果使用 IF 函数比较单元格 A1 和 C1，例如 IF(A1=C1)，则结果将为 TRUE。 这是由 IEEE 规范仅存储 15 个有效精度数字导致的。 为了能够存储上述计算，Excel 的精度至少需要达到 100 位数。
使用非常小数的示例
将以下内容输入到新工作簿中：
A1: 0.000123456789012345
B1: 1
C1: =A1+B1
单元格 C1 的结果值为 1.00012345678901，而不是 1.000123456789012345。 这是由 IEEE 规范仅存储 15 个有效精度数字导致的。 为了能够存储上述计算，Excel 的精度至少需要达到 19 位数。
更正精度错误
Excel 提供两种补偿舍入误差的基本方法：ROUND 函数和以显示精度为准或将精度设为所显示的精度工作簿选项。
方法 1：ROUND 函数
以下示例使用上一数据，使用 ROUND 函数强制将数字分为五位数。 这样，就能够成功将结果与另一个值进行比较。
A1: 1.2E+200
B1: 1E+100
C1: =ROUND(A1+B1,5)
结果是 1.2E+200。
D1: =IF(C1=1.2E+200, TRUE, FALSE)
其结果值为 TRUE。
方法 2：以显示精度为准
在某些情况下，可以使用“以显示精度为准”选项防止舍入误差影响你的工作。 此选项强制工作表中每个数字的值为显示值。 要打开此选项，请按照下列步骤操作：
- 在“文件”菜单上，单击“选项”，然后单击“高级”类别。
- 在“计算此工作簿时”部分，选择需要的工作簿，然后选中“将精度设为所显示的精度”复选框。
例如，如果选择显示两位小数位的数字格式，然后打开“以显示精度为准”选项，则保存工作簿时，所有超出两位小数位的精度将丢失。 此选项会影响活动工作簿，包括所有工作表。 无法撤消此选项并恢复丢失的数据。 建议在启用此选项之前保存工作簿。
重复结果接近于零的二进制数和计算
影响以二进制格式存储浮点数的另一个令人困惑的问题是，在十进制基数 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 中的以下简单示例：
Sub Main()
MySum = 0
For I% = 1 To 10000
MySum = MySum + 0.0001
Next I%
Debug.Print MySum
End Sub
这将打印 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 规范详细信息，请参阅以下万维网网站：
反馈
即将发布：在整个 2024 年，我们将逐步淘汰作为内容反馈机制的“GitHub 问题”，并将其取代为新的反馈系统。 有关详细信息，请参阅：https://aka.ms/ContentUserFeedback。
提交和查看相关反馈