浮点运算在运算中可能提供不准确Excel

备注

Office 365 ProPlus 正在重命名为 适用于企业的 Microsoft 365 应用。 有关此更改的详细信息, 请阅读此博客文章

摘要

本文讨论如何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 iEEE) 。
  • 溢出:当数字太大无法表示时发生溢出。 Excel对本例使用自己的特殊表示形式 (#NUM!) 。

我们未遵守 IEEE 754 的情况

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

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

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

  • 正/负 Infinities: Infinities occur when you divide by 0. Excel不支持无限次,而是提供一个#DIV/0! 错误。

  • 非数字 (NaN) :NaN 用于表示无效操作 (如无穷/无穷大、无穷大或 -1) 的平方根。 NA 允许程序继续通过无效操作。 Excel立即生成错误,如#NUM! 或 #DIV/0!。

精度

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

符号 指数 第三个
1 个符号位 11 位指数 1 个隐含位 + 52 位分数

该符号存储数字 (正负) 的符号,指数存储数字提升或降低的 2 的功率 (最大/最小功率 2 为 +1,023 和 -1,022) ,而头条存储实际数。 对数的有限存储区域限制两个相邻浮点数 (,即精度和) 。

第三个数和指数都存储为单独的组件。 因此,可能的精度值可能会有所不同,具体取决于所操作 (数) 的大小。 对于 Excel,尽管 Excel 可以存储从 1.79769313486232E308 到 2.2250738585072E-308 的号码,但它只能在 15 位数的精度内进行存储。 此限制是严格遵循 IEEE 754 规范的直接结果,并不是对 Excel。 在其他电子表格程序中也找到此精度级别。

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

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

Fraction 是数字的规范化分数部分,由于指数进行调整以便前导位始终为 1,因此进行了规范化。 这样一来,就无需存储它,并且您可以再获得一点精度。 这就是存在隐含位的原因。 这类似于科学表示法,其中你操作指数使小数点左侧有一个数字;除二进制外,你始终可以操作指数,以便第一位为 1,因为只有 1 和 0。

Bias 是用于避免必须存储负指数的偏置值。 对于双精度数字,单精度数字的偏 (127 和 1,023) 为 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:显示的精度

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

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

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

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

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

000110011001100110011 (等)

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

即使是常见的小数小数(如十进制 0.0001)也不能完全以二进制形式表示。 (0.0001 是重复的二进制分数,其周期为 104 位) 。 这类似于重复的 0.333333333333333333333) 小数部分 1/3 无法完全以十进制表示 (的原因。

例如,请考虑以下 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.99999999999996 作为输出。 表示二进制值 0.0001 的小错误将传播到和。

示例:添加负数

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

    A1: = (43.1-43.2) +1

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

显示 0.9,Excel 0.8999999999999。 由于 (43.1-43.2) ,因此将临时存储 -0.1,并且计算中会引入存储 -0.1 的错误。

值达到零时的示例

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

    A1: =1.333+1.225-1.333-1.225

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

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

Excel 97 引入了尝试更正此问题的优化。 如果加减运算导致值等于或非常接近零,则 Excel 97 和更高版本将补偿因将操作数转换为二进制值和从二进制数转换而引入的任何错误。 以上在 Excel 97 及更高版本中执行时的示例以科学表示法正确显示 0 或 0.0000000000000E+00。

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