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

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

《在 Excel 中浮点运算可能会给出不准确的结果.docx》由会员分享，可在线阅读，更多相关《在 Excel 中浮点运算可能会给出不准确的结果.docx（10页珍藏版）》请在冰豆网上搜索。

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

编号:

78113-最后修改:

2011年9月18日-修订:

2.0

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

查看本文应用于的产品

本页

展开全部|关闭全部

概要

本文讨论MicrosoftExcel如何存储和计算浮点数。

这可能会因为舍入和/或数据截断而影响某些数...

更多信息

最大/最小限制

所有计算机均能处理的最大数字和最小数字。

因为用于存储数字的内存位数是有限的，所以，可以存储的最大数或最小数也是有穷的。

对于Excel，可以存储的最大数是1.79769313486232E+308，而可以存储的最小正数是2.2250738585072E-308。

我们遵守IEEE754的情况

•下溢：

当产生了一个因太小而无法表示的数字时，会发生下溢。

在IEEE和Excel中，结果是0（不同的是IEEE有-0的概念，而Excel没有）。

•溢出：

当数字因太大而无法表示时，会发生溢出。

Excel使用它自己的特殊表示方法来表示此情况（#NUM!

）。

我们不遵守IEEE754的情况

•非规范化数：

非规范化数由等于0的指数表示。

在这种情况下，整个数字存储在尾数中，而且尾数没有隐式的前导1。

因此，会丢失精度，而且数字越小，精度丢失越多。

在此范围较小一端的数字只有一位精度。

示例：

规范化数有隐式的前导1。

例如，如果尾数表示0011001，则规范化数会变成10011001（因为有隐式的前导1）。

非规范化数没有隐式的前导1，因此，在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位精度之内这样做。

此限制是严格遵循IEEE754规范的直接结果，并且不是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。

这是由只存储15位有效精度数字的IEEE规范造成的。

若要能够存储上述计算，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.在Excel2003和更早的版本中，在“工具”菜单上单击“选项”。

2.在“重新计算”选项卡上，单击“以显示精度为准”复选框以将其选中。

1.在Excel2007中，单击“Office按钮”，单击“Excel选项”，然后单击“高级”类别。

2.在“计算此工作簿时”部分中，选择所需的工作簿，然后选中“将精度设为所显示的精度”复选框。

例如，如果选择显示两位小数的数字格式，然后打开“以显示精度为准”选项，则在您保存工作簿时，所有超出两位小数的精度均将会丢失。

此选项影响活动的工作簿（包括所有工作表）。

您无法撤消此选项和恢复丢失的数据。

建议您在启用此选项之前保存工作簿。

回到顶端

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

以二进制存储浮点数时，另一个引起混乱的问题是，某些10进制的有穷非循环数在二进制下是无穷的循环数。

这方面最常见的示例是值0.1及其变体。

虽然这些数字在以10为底的情况下可以完美地得到表示，但二进制格式下的相同数字在尾数中存储时就变成了以下二进制循环数字：

000110011001100110011（等类似数字）

IEEE754规范并未对任何数字作特殊的规定；它在尾数中存储能够容纳的部分，并截断其余部分。

这导致在存储数字时产生大约-2.8E-17或0.000000000000000028的误差。

在二进制下，即使是常见的十进制分数（例如十进制的0.0001）也无法得到准确表示。

（0.0001是一个二进制循环分数，以104位为一个周期）。

这类似于为什么分数1/3在十进制下无法得到准确的表示（循环小数0.33333333333333333333）。

这说明了为什么MicrosoftVisualBasicforApplications中的一个简单示例

SubMain（）

MySum=0

ForI%=1To10000

MySum=MySum+0.0001

NextI%

Debug.PrintMySum

EndSub将输出0.999999999999996。

在二进制下表示0.0001的微小误差传播到总和中。

回到顶端

加负数的示例

1.在新的工作簿中输入以下内容：

A1:

=（43.1-43.2）+1

2.右键单击单元格A1，然后单击“设置单元格格式”。

在“数字”选项卡的“分类”下单击“科学记数”。

将“小数位数”设置为15。

Excel将显示0.899999999999999，而不是0.9。

由于先计算（43.1-43.2），因此临时存储-0.1，而存储-0.1时产生的误差带入到计算中。

回到顶端

值为零时的示例

1.在Excel95或更早的版本中，在新的工作簿中输入以下内容：

A1:

=1.333+1.225-1.333-1.225

2.右键单击单元格A1，然后单击“设置单元格格式”。

在“数字”选项卡上，单击“类别”下的“科学记数”。

将“小数位数”设置为15。

Excel95将显示-2.22044604925031E-16，而不是显示0。

但是，Excel97引入了优化功能，以尝试纠正此问题。

如果加或减运算会导致值为零或非常接近零，Excel97和更高版本将会弥补因为将操作数转换为二进制和转换二进制操作数而产生的任何误差。

当在Excel97和更高的版本中执行上例时，将正确显示0（或科学记数法下的0.000000000000000E+00）。

有关更多信息，请单击下面的文章编号，以查看Microsoft知识库中相应的文章：

172911按非常大/非常小的幂乘10时返回不正确的结果

214373XL2000：

按非常大/非常小的幂自乘10时返回不正确的结果

有关浮点数和IEEE754规范的更多信息，请访问以下万维网网站：

http:

//www.ieee.org

回到顶端

参考

有关如何纠正这些误差的更多信息，请单击下面的文章编号，以查看Microsoft知识库中相应的文章：

214118如何纠正浮点运算中的舍入误差

回到顶端

--------------------------------------------------------------------------------

这篇文章中的信息适用于:

•

•

•

•

•

•

•

•

•

•

•

•

回到顶端

关键字：

kbinfoKB78113

回到顶端

Microsoft和/或其各供应商对于为任何目的而在本服务器上发布的文件及有关图形所含信息的适用性，不作任何声明。

所有该等文件及有关图形均"依样"提供，而不带任何性质的保证。

Microsoft和/或其各供应商特此声明，对所有与该等信息有关的保证和条件不负任何责任，该等保证和条件包括关于适销性、符合特定用途、所有权和非侵权的所有默示保证和条件。

在任何情况下，在由于使用或运行本服务器上的信息所引起的或与该等使用或运行有关的诉讼中，Microsoft和/或其各供应商就因丧失使用、数据或利润所导致的任何特别的、间接的、衍生性的损害或任何因使用而丧失所导致的之损害、数据或利润不负任何责任。

此信息是否有用？

是

否

有一点

对于自己而言，应用此篇文章要付出多大的努力？

非常低

低

中

高

非常高

请告知原因并帮助我们了解应如何完善该信息

他资源

其他帮助资源

•

•

•

•

•

社区或论坛

•

•

•

•

马上获得帮助

文章翻译

（ישראל（עברית

相关支持中心

•Excel2010

•Excel

•Excel2007

•Excel2002

•Excel2003

需要更多帮助?

请通过电子邮件、联机或电话与支持工程师联系

©2012Microsoft

服务协议

联系微软

保留所有权利

商标

隐私权声明