概要
本文讨论如何Microsoft Excel 存储和计算浮点数。 这可能会影响某些数字或公式的结果，因为舍入或数据截断。
概述
Microsoft Excel 围绕 IEEE 754 规范进行设计，以确定它如何存储和计算浮点数。 IEEE 是电气和电子工程师研究所，一个国际组织，除其他方面，确定计算机软件和硬件的标准。 754 规范是一种广为采用的规范，用于描述如何在二进制计算机中存储浮点数。 它很受欢迎，因为它允许浮点数存储在合理的空间和计算速度相对较快。 754 标准用于几乎所有当今基于电脑的微控制器的浮点单元和数值数据处理者，这些处理器实现浮点数学，包括 Intel、摩托罗拉、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 表示。 在这种情况下，整个数字存储在 mantissa 中，而 mantissa 没有隐式前导 1。 因此，你将失去精度，而数字越小，精度就越小。 此范围小端的数字只有一位数的精度。
示例：规范化数字具有隐式前导 1。 例如，如果 mantissa 表示0011001，则规范化数由于隐式前导 1 而变为10011001。 非规范化数字没有隐式前导数字，因此在我们的0011001示例中，非规范化数字保持不变。 在这种情况下，规范化数字有八个有效数字（10011001），而非规范化数字有五个有效数字（11001），前导零微不足道。
非规范化数字基本上是一种解决方法，允许存储小于正常下限的数字。 Microsoft不实现此规范的可选部分，因为非规范化数字具有可变数量的有效数字。 这可能导致计算中出现重大错误。
正/负无穷大：当除以 0 时会产生正或负无穷大。 Excel 不支持无穷大，而是显示 #DIV/0！ 在这些情况下出错。
Not-a-Number （NaN）：NaN 用于表示无效的操作（如无穷大/无穷大、无穷大-无穷大或 -1的平方根）。 NaN 允许程序在无效操作后继续运行。 Excel 会立即生成错误，例如
#NUM!或#DIV/0!。
精准率
浮点数以二进制形式存储在 65 位范围内的三个部分：符号、指数和 mantissa。
| 标志 | 指数 | mantissa |
|---|---|---|
| 1 个符号位 | 11 位指数 | 1 隐含位 + 52 位分数 |
该符号存储数字（正或负数）的符号，指数存储该数字向上或降低的 2 的幂（最大/最小值为 +1,023 和 -1,022），而 mantissa 存储实际数字。 mantissa 的有限存储区域限制两个相邻浮点数的接近程度（即精度）。
mantissa 和指数都存储为单独的组件。 因此，可能的精度量可能会因所纵的数字（mantissa）的大小而异。 在 Excel 中，尽管 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 函数（例如 IF（A1=C1）比较单元格 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 函数强制数字为 5 位数字。 这样，便可以将结果成功与另一个值进行比较。
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 为底表示，但二进制格式的相同数字在存储在 mantissa 中时会变为以下重复二进制数：
000110011001100110011（等等）
IEEE 754 规范没有针对任何数值的特殊规定。 它将它存储在 mantissa 中，并截断其余的。 这会导致存储时出现大约 -2.8E-17 或 0.0000000000000028 的错误。
即使是常见的十进制分数（如十进制 0.0001）也不能完全以二进制形式表示。 （0.0001 是重复的二进制分数，其时间段为 104 位）。 这类似于为什么分数 1/3 不能完全用小数表示（重复 0.33333333333333333333333333333333） 的原因。
例如，请考虑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.9999999999999996 作为输出。 以二进制表示的 0.0001 存在的小错误会传播到总和。
示例：添加负数
在新的工作簿中输入以下内容：
A1： =（43.1-43.2）+1
右键单击单元格 A1，然后单击“ 设置单元格格式”。 在“数字”选项卡上，在“类别”中选择“科学”。 将 小数位数 设置为 15。
Excel 不显示 0.9，而是显示 0.89999999999999999。 由于首先计算了 （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.0000000000000000E+00 的科学表示法。
有关浮点数和 IEEE 754 规范的详细信息，请参阅以下万维网网站：