完美Excel

这里是2007年9月1日收到CPearson.com的Excel新闻资讯内容。Excel的爱好者可以到CPearson.com中订阅CPearson.com Excel Newsletter，CPearson.com将每周为您发送一份关于Excel知识的技术文章。
(CPearson是Chip Pearson的简写，Chip Pearson是一位知名的Excel专家，在其网站上有很多实用的Excel技术文章)
这期主要内容如下：

介绍
本期来看看Excel可能会返回错误计算结果的环境。几乎所有情况下，错误可能基于下列两种情形：单元格中显示的值和单元格中实际的值不同、对存储在计算机中数值精度的限制。其中第二种限制并不只是在Excel或Microsoft软件中，它是在任何系统下任何软件产品中都存在。

实际值和显示值
Excel总是尽可能在最大精度存储和计算数字，而不管在输入和输出单元格中设置的显示格式。这意味着Excel使用15位数字，数字的总数包括小数点左右边的数字，即使在输入或输出单元格中仅显示两个小数位。例如，在单元格区域A1:A8中，输入公式=1/8，然后在单元格A9中输入公式=SUM(A1:A8)，对单元格区域A1:A9设置单元格格式为显示3位小数，则单元格区域A1:A8中显示0.125，单元格A9中显示1.000。结果是正确的并且我们也接受这个结果。现在，设置单元格区域A1:A9中的格式为两位小数，则单元格区域A1:A8中将显示的值为0.13，单元格A9中显示1.00，但是，0.13乘以8将是1.04而不是1.00。这是Excel出错了吗？这是一个Bug吗？
不，Excel没有错，也没有Bug。当Excel计算公式=SUM(A1:A8)时，它使用单元格区域A1:A8中的实际值进行计算而不是显示值。不管单元格中格式的设置如何，Excel都使用其潜在的值，即所有15位数字。在本例中，Excel使用值0.125而不是0.13来进行计算。
也可以强制Excel使用所显示的值而不是实际值进行计算，但并不推荐这样做。可以通过在“工具”菜单的“选项”对话框中的“计算”选项卡上的“将精度设置为所显示的精度”来控制计算的设置。可以在启用此项设置后，重新试验上面所讲的示例，并试着将单元格格式分别设置为显示三位小数、二位小数和一位小数，将得到的结果分别为1.000、1.04和0.8。即会得到不同的值，但所有这些实质上是相同的值1/8的求和，这三个结果值正确码？答案是肯定的。但这样会导致混乱和不正确的计算。
如果需要考虑舍入误差，则可以在数组公式中使用ROUND函数。ROUND函数将数字舍入为指定的小数数。例如，将公式=SUM(ROUND(A1:A8,2))作为数组公式输入(即输入完公式后按CTRL+SHIFT+ENTER组合键)，此时在单元格区域A1:A8中的值为两位小数且SUM也是这些舍入的数字之和，因此，如果A1:A8中包含=1/8，Excel会将这些数字(0.125)四舍五入为0.13，然后求和，结果为1.04而不是1.00。注意，在传递这些值到SUM函数之前就将每个值四舍五入。这意味着数组公式=SUM(ROUND(A1:A8,2))与公式=ROUND(SUM(A1:A8),2)不同，第一个公式在求和前对单元格区域A1:A8中的值进行四舍五入，而第二个公式中的值则求和后再对结果进行四舍五入。可以为四舍五入使用的函数包括：
•ROUNDUP
•ROUNDDOWN
•INT
•TRUNC
•MROUND
在Excel的在线帮助中有这些函数的文档说明。

数值精度的限制
发生明显错误的另一个原因是由于计算机中存储数值的内在限制。与几乎所有其它的软件程序一样，Excel使用称作双精度浮点型数据格式。这种格式是一种工业标准，并不限于Excel或其它Microsoft产品，允许15位数字精度。术语精度指可以精确呈现在小数点左右两边的数字数。如果小数点左边和右边的数字总数超过15，Excel将在15时舍入。例如，在单元格A1中输入数字123456789012345，然后在单元格A2中输入=A1+1，由于单元格A1中有15个数字，单元格A2中将准确地显示123456789012346。现在将A1改为1234567890123456，这个数值有16位，超过了Excel的15位的限制，因此，Excel将该数值舍入为1234567890123450，最后一个数字0是舍入的结果，单元格A2也被四舍五入，因此，A1+1为1234567890123450，这显然是错误的，因为该数值与A1中的数值相同。在数学上，A1+1显然不等于A1，从数学的角度讲是错的。但这不是一个Bug。这种舍入行为是计算机中的一种固有的限制，并且在处理大数值或带有许多小数位时需要考虑这种情形。
这种限制也决定了可以在小数点左边使用的位数。例如，在单元格B1中，输入123.456789012345，共有15位数字，小数左边有3位，右边有12位。在单元格B2中输入=B1+0.000000000001，即在小数点右边放置11个0和1个1，这仍然在15个数字的限制内，因此结果是正确的123.456789012346。现在，在单元格B3中输入=B1+0.0000000000001，小数点右边有12个0和1个1。在数学上讲，这应该是123.4567890123451，然而，这超过了Excel的15个数字的限制(它是16位，小数点左边是3位，右边是13位)，因此，Excel在小数点右边放置第12位的舍入结果。
如果需要存储大于15位的数字，但不需要对这些数字进行计算(例如电话号码或信用卡号)，可以告诉Excel不要将这些数字当作数字对待而是作为文本。此时，Excel显示像与输入的数字位数相同的数字。然而，如果试图使用这些数值进行计算则会进行四舍五入。要将输入的数值作为文本，可以在输入数字前格式化单元格为文本格式，或者可以在数值前输入一个单引号(‘)。这个单引号不会显示在单元格中，但会出现在公式栏中。
值得注意的是，即使Excel受15位数字精度的限制，但可以处理大至10^308(在小数点左边有308位数字)或小至10^-308(在小数点右边有308位)。然而，超过15位精度的都会被四舍五入。例如，Excel可以显示数值10^25，但(10^25)+1不会被正确地计算，因为它超过了15位。它将在10^15时被四舍五入。