在 Excel 中浮点运算可能会给出不准确的结果.docx

1、在 Excel 中浮点运算可能会给出不准确的结果编号: 78113 - 最后修改: 2011年9月18日 - 修订: 2.0在 Excel 中浮点运算可能会给出不准确的结果查看本文应用于的产品本页展开全部 | 关闭全部概要本文讨论 Microsoft Excel 如何存储和计算浮点数。这可能会因为舍入和/或数据截断而影响某些数.更多信息最大/最小限制所有计算机均能处理的最大数字和最小数字。因为用于存储数字的内存位数是有限的，所以，可以存储的最大数或最小数也是有穷的。对于 Excel，可以存储的最大数是 1.79769313486232E+308，而可以存储的最小正数是 2.2250738585

2、072E-308。我们遵守 IEEE 754 的情况下溢：当产生了一个因太小而无法表示的数字时，会发生下溢。在 IEEE 和 Excel 中，结果是 0（不同的是 IEEE 有 -0 的概念，而 Excel 没有）。溢出：当数字因太大而无法表示时，会发生溢出。Excel 使用它自己的特殊表示方法来表示此情况 (#NUM!)。 我们不遵守 IEEE 754 的情况非规范化数：非规范化数由等于 0 的指数表示。在这种情况下，整个数字存储在尾数中，而且尾数没有隐式的前导 1。因此，会丢失精度，而且数字越小，精度丢失越多。在此范围较小一端的数字只有一位精度。示例： 规范化数有隐式的前导 1。例如，如果

3、尾数表示 0011001，则规范化数会变成 10011001（因为有隐式的前导 1）。非规范化数没有隐式的前导 1，因此，在 0011001 这个示例中，非规范化数保持不变。在这种情况下，规范化数有八位有效数字 (10011001)，而非规范化数有五位有效数字 (11001)（前导 0 不是有效数字）。 非规范化数主要用作一种替代方法，以允许数字小于要存储的正常下限。Microsoft 未实施此规范的这一可选部分，原因是，非规范化数就本质而言具有数量可变的有效数字。这使得计算中可能出现重大误差。正/负无穷大：被 0 除时会出现无穷大。Excel 不支持无穷大，在此类情况下它会给出 #DIV/0

4、! 错误。非数字 (NaN)：NaN 用于表示无效的运算（例如无穷大除无穷大，无穷大减无穷大，或者 -1 的平方根）。NaN 允许程序略过无效的运算而继续执行。而 Excel 会立即生成错误（例如 #NUM! 或 #DIV/0!）。回到顶端精度浮点数以二进制存储，并分为三个部分，总长度为 65 位：符号、指数和尾数。 1 个符号位11 位指数1 个隐含位52 位尾数 符号存储数字的符号（正或负），指数存储使数字增大或减小到的 2 的幂（最大/最小的 2 的幂是 +1,023 和 -1,022），而尾数存储实际的数字。尾数的有限存储区域限制了两个相近的浮点数能够接近的程度（也即精度）。 尾数和指

5、数均作为独立的成份存储。因此，可能的精度值会因所处理的数字（尾数）的大小而异。对于 Excel，虽然它可以存储从 1.79769313486232E308 到 2.2250738585072E-308 的数字，但它只能在 15 位精度之内这样做。此限制是严格遵循 IEEE 754 规范的直接结果，并且不是 Excel 的限制。其他电子表格程序也具有此精度。 浮点数用以下形式表示，其中指数是二进制指数：X = 分数 * 2(指数 - 偏差) 分数是数字的规范化分数部分，规范化的原因是指数经过调整，使得前导位始终为 1。这样就不必存储它，而且您额外获得了一位精度。这就是为什么存在隐含位的原因。这与

6、科学记数法类似，在此方法中，您操作指数，以便小数点的左侧有一位数字；除了是二进制以外，您总是可以操作指数，使得第一位为 1（因为只能是 1 和 0）。 偏差是用于避免必须存储负指数的偏差值。单、双精度数字的偏差分别是 127 和 1,023（十进制）。Excel 使用双精度存储数字。回到顶端使用超大数字的示例将下列内容输入新工作簿： A1：1.2E+200B1：1E+100C1：=A1+B1单元格 C1 中的结果值将为 1.2E+200，与单元格 A1 的值相同。实际上，如果使用 IF 函数比较单元格 A1 和 C1（例如 IF(A1=C1)），则结果将为 TRUE。这是由只存储 15 位有效

7、精度数字的 IEEE 规范造成的。若要能够存储上述计算，Excel 将需要至少 100 位精度。回到顶端使用超小数字的示例将下列内容输入至新工作簿： A1： 0.000123456789012345B1： 1C1：=A1+B1单元格 C1 中的结果值将为 1.00012345678901，而不是 1.000123456789012345。这是由 IEEE 规范只存储 15 位精度而导致的。若要能够存储上述计算，Excel 将需要至少 19 位精度。回到顶端校正精度错误Excel 提供两种基本方法来弥补舍入误差：ROUND 函数，以及“以显示精度为准”或“将精度设为所显示的精度”工作簿选项。方法

8、 1：ROUND 函数以下示例使用上面的数据，并使用 ROUND 函数强制将数字舍为五位数。这可让您成功地将结果与另一个值进行比较。 A1:1.2E+200B1:1E+100C1:=ROUND(A1+B1,5)结果为 1.2E+200。D1：=IF(C1=1.2E+200, TRUE, FALSE)结果值为 TRUE。方法 2：以显示精度为准在某些情况下，您可以使用“以显示精度为准”选项来防止四舍五入错误影响您的工作。此选项会强制将工作表中每个数字的值成为显示的值。要打开此选项，请按照下列步骤操作： 1.在 Excel 2003 和更早的版本中，在“工具”菜单上单击“选项”。2.在“重新计算”

9、选项卡上，单击“以显示精度为准”复选框以将其选中。1.在 Excel 2007 中，单击“Office 按钮”，单击“Excel 选项”，然后单击“高级”类别。2.在“计算此工作簿时”部分中，选择所需的工作簿，然后选中“将精度设为所显示的精度”复选框。 例如，如果选择显示两位小数的数字格式，然后打开“以显示精度为准”选项，则在您保存工作簿时，所有超出两位小数的精度均将会丢失。此选项影响活动的工作簿（包括所有工作表）。您无法撤消此选项和恢复丢失的数据。建议您在启用此选项之前保存工作簿。回到顶端结果接近零的重复二进制数字和计算以二进制存储浮点数时，另一个引起混乱的问题是，某些 10 进制的有穷非循

10、环数在二进制下是无穷的循环数。这方面最常见的示例是值 0.1 及其变体。虽然这些数字在以 10 为底的情况下可以完美地得到表示，但二进制格式下的相同数字在尾数中存储时就变成了以下二进制循环数字：000110011001100110011（等类似数字）IEEE 754 规范并未对任何数字作特殊的规定；它在尾数中存储能够容纳的部分，并截断其余部分。这导致在存储数字时产生大约 -2.8E-17 或 0.000000000000000028 的误差。 在二进制下，即使是常见的十进制分数（例如十进制的 0.0001）也无法得到准确表示。（0.0001 是一个二进制循环分数，以 104 位为一个周期）。这

11、类似于为什么分数 1/3 在十进制下无法得到准确的表示（循环小数 0.33333333333333333333）。 这说明了为什么 Microsoft Visual Basic for Applications 中的一个简单示例Sub Main()MySum = 0For I% = 1 To 10000MySum = MySum + 0.0001Next I%Debug.Print MySumEnd Sub 将输出 0.999999999999996。在二进制下表示 0.0001 的微小误差传播到总和中。回到顶端加负数的示例1.在新的工作簿中输入以下内容：A1:=(43.1-43.2)+12.

12、右键单击单元格 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.2252.右键单击单元格 A1，然后单击“设置单元格格式”。在“数字”选项卡上，单击“类别”下的“科学记数”。将“小数位数”设置为 15。 Excel 95

13、将显示 -2.22044604925031E-16，而不是显示 0。 但是，Excel 97 引入了优化功能，以尝试纠正此问题。如果加或减运算会导致值为零或非常接近零，Excel 97 和更高版本将会弥补因为将操作数转换为二进制和转换二进制操作数而产生的任何误差。当在 Excel 97 和更高的版本中执行上例时，将正确显示 0（或科学记数法下的 0.000000000000000E+00）。 有关更多信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章： 172911 按非常大/非常小的幂乘 10 时返回不正确的结果214373 XL 2000：按非常大/非常小的幂自乘

14、10 时返回不正确的结果 有关浮点数和 IEEE 754 规范的更多信息，请访问以下万维网网站：http:/www.ieee.org回到顶端参考 有关如何纠正这些误差的更多信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章： 214118 如何纠正浮点运算中的舍入误差回到顶端-这篇文章中的信息适用于:回到顶端关键字： kbinfo KB78113回到顶端Microsoft和/或其各供应商对于为任何目的而在本服务器上发布的文件及有关图形所含信息的适用性，不作任何声明。 所有该等文件及有关图形均依样提供，而不带任何性质的保证。Microsoft和/或其各供应商特此声明，对所

15、有与该等信息有关的保证和条件不负任何责任，该等保证和条件包括关于适销性、符合特定用途、所有权和非侵权的所有默示保证和条件。在任何情况下，在由于使用或运行本服务器上的信息所引起的或与该等使用或运行有关的诉讼中，Microsoft和/或其各供应商就因丧失使用、数据或利润所导致的任何特别的、间接的、衍生性的损害或任何因使用而丧失所导致的之损害、数据或利润不负任何责任。 此信息是否有用？是 否 有一点 对于自己而言，应用此篇文章要付出多大的努力？非常低 低 中 高 非常高 请告知原因并帮助我们了解应如何完善该信息他资源其他帮助资源社区或论坛马上获得帮助 文章翻译( (相关支持中心 Excel 2010ExcelExcel 2007Excel 2002Excel 2003 需要更多帮助?请通过电子邮件、联机或电话与支持工程师联系 2012 Microsoft服务协议联系微软保留所有权利商标隐私权声明