|
|
疑惑中的问题，突然有人推荐了这贴，大家不妨阅读下！！！
在 Excel 中浮点运算可能会给出不准确的结果
查看本文应用于的产品
本页概要
概述
更多信息
最大/最小限制
我们遵守 IEEE 754 的情况
我们不遵守 IEEE 754 的情况
精度
使用非常大的数字的示例
使用非常小的数字的示例
更正精度误差
方法 1：ROUND 函数
方法 2：以显示精度为准
二进制循环数和结果接近零的计算
加负数的示例
值为零时的示例
参考
展开全部 | 关闭全部
概要本文讨论 Microsoft Excel 如何存储和计算浮点数。这可能会因为舍入和/或数据截断而影响某些数字或公式的结果。 概述Microsoft Excel...本文讨论 Microsoft Excel 如何存储和计算浮点数。这可能会因为舍入和/或数据截断而影响某些数字或公式的结果。
回到顶端
概述
Microsoft Excel 是按照与存储和计算浮点数有关的 IEEE 754 规范设计的。IEEE 的全称是 Institute of Electrical and Electronics Engineers（电气和电子工程师协会），此国际机构确定计算机软硬件的标准和其他许多标准。754 规范是一个广泛采用的规范，它描述了在二进制计算机中应如何存储浮点数。它之所以得到广泛采用，原因是它允许在合理的空间量中存储浮点数，以及相对快速地进行计算。如今，大多数执行浮点运算的基于 PC 的微处理器（包括 Intel、Motorola、Sun 和 MIPS 处理器）在浮点单元和数值数据处理器中均采用 754 标准。
在存储数字时，对应的二进制数字可以表示每一个数字或分数。例如，分数 1/10 在十进制中可以表示为 0.1。但是，同一个数字在二进制格式中变成了二进制循环小数
0001100110011100110011（等等）
并且可以无限循环。此数字无法用有限的空间量来表示。因此，此数字在存储时向下舍入大约 -2.8E-17。
但是，关于 IEEE 754 规范有一些限制，它们大致分为三类：
最大/最小限制
精度
二进制循环数字
回到顶端
更多信息最大/最小限制所有计算机均有能够处理的最大数字和最小数字。因为用于存储数字的内存位数是有限的，所以，可以存储的最大数或最小数也是有穷的。对于 Excel，可以存...最大/最小限制
所有计算机均有能够处理的最大数字和最小数字。因为用于存储数字的内存位数是有限的，所以，可以存储的最大数或最小数也是有穷的。对于 Excel，可以存储的最大数是 1.79769313486232E+308，而可以存储的最小正数是 2.2250738585072E-308。
我们遵守 IEEE 754 的情况
下溢：当产生了一个因太小而无法表示的数字时，会发生下溢。在 IEEE 和 Excel 中，结果是 0（不同的是 IEEE 有 -0 的概念，而 Excel 没有）。
溢出：当数字因太大而无法表示时，会发生溢出。Excel 使用它自己的特殊表示方法来表示此情况 (#NUM!)。
我们不遵守 IEEE 754 的情况
非规范化数：非规范化数由等于 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 位精度之内这样做。此限制是严格遵循 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。这是由只存储 15 位有效精度数字的 IEEE 规范造成的。若要能够存储上述计算，Excel 将需要至少 100 位精度。
回到顶端
使用非常小的数字的示例
在新的工作簿中输入以下内容：
A1： 0.000123456789012345
B1： 1
C1：=A1+B1
单元格 C1 中的结果值将为 1.00012345678901，而不是 1.000123456789012345。这是由只存储 15 位有效精度数字的 IEEE 规范造成的。若要能够存储上述计算，Excel 将需要至少 19 位精度。
回到顶端
更正精度误差
Excel 提供两种基本方法来弥补舍入误差：ROUND 函数，以及“以显示精度为准”或“将精度设为所显示的精度”工作簿选项。
方法 1：ROUND 函数
以下示例使用上面的数据，并使用 ROUND 函数强制将数字舍为五位数。这可让您成功地将结果与另一个值进行比较。
A1：1.2E+200
B1：1E+100
C1：=ROUND(A1+B1,5)
结果为 1.00012。
D1：=IF(C1=1.00012, TRUE, FALSE)
结果为值 TRUE。
方法 2：以显示精度为准
在某些情况下，您可能可以通过使用“以显示精度为准”选项防止舍入误差影响您的工作。此选项会强制将工作表中每个数字的值成为显示的值。要打开此选项，请按照下列步骤操作：
在 Excel 2003 和更早的版本中，在“工具”菜单上单击“选项”。
在“重新计算”选项卡上，单击“以显示精度为准”复选框以将其选中。
在 Excel 2007 中，单击“Office 按钮”，单击“Excel 选项”，然后单击“高级”类别。
在“计算此工作簿时”部分中，选择所需的工作簿，然后选中“将精度设为所显示的精度”复选框。
例如，如果选择显示两位小数的数字格式，然后打开“以显示精度为准”选项，则在您保存工作簿时，所有超出两位小数的精度均将会丢失。此选项影响活动的工作簿（包括所有工作表）。您无法撤消此选项和恢复丢失的数据。建议您在启用此选项之前保存工作簿。
回到顶端
二进制循环数和结果接近零的计算
以二进制存储浮点数时，另一个引起混乱的问题是，某些 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.899999999999999，而不是 0.9。由于先计算 (43.1-43.2)，因此临时存储 -0.1，而存储 -0.1 时产生的误差带入到计算中。
回到顶端
值为零时的示例
在 Excel 95 或更早的版本中，在新的工作簿中输入以下内容：
A1：=1.333+1.225-1.333-1.225
右键单击单元格 A1，然后单击“设置单元格格式”。在“数字”选项卡的“分类”下单击“科学记数”。将“小数位数”设置为 15。
Excel 95 将显示 -2.22044604925031E-16，而不是显示 0。
但是，Excel 97 引入了优化功能，以尝试纠正此问题。如果加或减运算会导致值为零或非常接近零，Excel 97 和更高版本将会弥补因为将操作数转换为二进制和转换二进制操作数而产生的任何误差。当在 Excel 97 和更高的版本中执行上例时，将正确显示 0（或科学记数法下的 0.000000000000000E+00）。 有关更多信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章：
172911 (http://support.microsoft.com/kb/172911/ ) 按非常大/非常小的幂自乘 10 时返回不正确的结果
214373 (http://support.microsoft.com/kb/214373/ ) XL 2000：按非常大/非常小的幂自乘 10 时返回不正确的结果
有关浮点数和 IEEE 754 规范的更多信息，请访问以下万维网网站：
http://www.ieee.org (http://www.ieee.org)
http://stevehollasch.com/cgindex/coding/ieeefloat.html (http://stevehollasch.com/cgindex/coding/ieeefloat.html)
参考有关如何纠正这些误差的更多信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章： 214118 (http://support.micr...有关如何纠正这些误差的更多信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章：
214118 (http://support.microsoft.com/kb/214118/ ) 如何纠正浮点运算中的舍入误差
--------------------------------------------------------------------------------
这篇文章中的信息适用于:
Microsoft Office Excel 2007
Microsoft Office Excel 2003
Microsoft Excel 2002 标准版
Microsoft Excel 2000 标准版
Microsoft Excel 97 标准版
Microsoft Excel 95 标准版
Microsoft Excel 2004 for Mac
Microsoft Excel X for Mac
Microsoft Excel 2001 for Mac
Microsoft Excel 98 for Macintosh
关键字： kbinfo KB78113
Microsoft和/或其各供应商对于为任何目的而在本服务器上发布的文件及有关图形所含信息的适用性，不作任何声明。 所有该等文件及有关图形均"依样"提供，而不带任何性质的保证。Microsoft和/或其各供应商特此声明，对所有与该等信息有关的保证和条件不负任何责任，该等保证和条件包括关于适销性、符合特定用途、所有权和非侵权的所有默示保证和条件。在任何情况下，在由于使用或运行本服务器上的信息所引起的或与该等使用或运行有关的诉讼中，Microsoft和/或其各供应商就因丧失使用、数据或利润所导致的任何特别的、间接的、衍生性的损害或任何因使用而丧失所导致的之损害、数据或利润不负任何责任。 |
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2009-7-6 16:09
