下载掌阅APP，畅读海量书库
立即打开
在编写公式时，因为某些原因，运算结果可能会产生错误值。产生错误值并不可怕，可怕的是不会修正错误。在某些特定情况下，我们还会故意构造错误值来应用到公式中，所以凡事都有两面性，我们要充分利用好Excel函数中的每一个优点与缺点。
表3-1列出了常见的错误值、错误原因和示例。便于了解公式错误的原因，能更快、更有目的性地修正公式。当错误值不可避免时，如何去识别错误值、处理错误值、利用错误值呢？
表3-1
如何判断公式的运算结果是否为错误值呢?Excel为我们提供了3个可以判断错误值的信息函数，如表3-2所示。
表3-2
实例31 计算采购清单中的商品金额
问题描述： 图3-12所示的“监控设备采购清单”表中列出了“商品名称”“单价”“数量”，现在要求计算出每种商品的采购金额，单价和数量“未定”或“暂无”的考虑为0。
解决方法： 本实例比较麻烦的是单价和数量中都有汉字，汉字与数字相乘会得到错误值，但可以利用判断错误的信息函数ISERROR或ISERR解决。当相乘结果有错误时返回0，否则返回相乘的结果值。
操作步骤： 如图3-13所示，首先将鼠标光标定位在D3单元格，输入公式=IF(ISERR(B3*C3),0,B3*C3)，按Enter键，再向下填充公式，完成计算。
图3-12
图3-13
步骤1： 直接将单价乘以数量，公式为B3*C3，由于B3单元格的文字与C3单元格的数字相乘会出错，所以计算结果为#VALUE!。
步骤2： 使用ISERR函数去判断步骤1的结果是否有错误值，公式为ISERR(#VALUE!)，判断结果为TRUE。
步骤3： 使用IF函数，如果步骤2的结果为TRUE，则返回0，否则返回单价乘以数量的金额，公式为IF(TRUE,0,B3*C3)，判断结果为0。
通过图3-13能看出，可以将判断错误值的信息函数与IF函数结合应用，我们也可以用IFERROR函数将它们合二为一，达到同样的目的。注意，IFERROR是Excel 2010版本中新增的函数。
函数语法说明：
IFERROR(value,value_if_error)
● value：必需参数。检查是否存在错误的参数。
● value_if_error：必需参数。公式的计算结果错误时返回的值。能识别到的错误类型有：＃N/A、＃VALUE！、＃REF！、＃DIV/0！、＃NUM！、＃NAME？或＃NULL！。
如果value或value_if_error是空单元格，则IFERROR将其视为空字符串值 （〝〝）；如果value是数组公式，则IFERROR为value中指定区域的每个单元格返回一个结果数组。
我们来优化一下实例31的公式。如图3-14所示，首先将鼠标光标定位在D3单元格，输入公式=IFERROR(B3*C3,0)，按Enter键，再向下填充公式，完成计算。
简单解释一下公式，当B3*C3的值有错误时，返回第2参数中的0。如果没有错误则返回B3*C3运算得到的值。公式变得简单了，逻辑也变得简单了。
图3-14
如果公式返回错误值#N/A，则结果返回指定的值，否则返回公式的结果，可以用IFNA函数。此函数是Excel 2013版的新增函数，且只能识别#N/A错误。语法结构与IFERROR函数一样。
函数语法说明：
IFNA(value,value_if_na)
● value：必需参数。用于检查错误值＃N/A的参数。
● value_if_na：必需参数。当公式计算结果为错误值＃N/A 时要返回的值。
错误值经常在公式中不期而遇，但有时我们也会主动构造错误值，让它如期而至，利用这些错误值完成数据统计。
实例32 统计因故没有考试和考试不及格的人数
问题描述： 图3-15所示的表格中，统计“分数”列中“缺考”和“请假”以及小于60的单元格个数，也就是统计因故没有考试和考试不及格的人数。
图3-15
解决方法： 先将“分数”列中的文本变成错误值，再将小于60的变成错误值，这样不满足条件的都变成错误值了。最后计算错误值的个数即可。
操作步骤： 如图3-16所示，首先将鼠标光标定位在D3单元格，输入数组公式=SUM(IFERROR (0^(B2:B7-59),1))，然后按Ctrl+Shift+Enter组合键，完成计算。
图3-16
步骤1： 计算(B2:B7-59)部分，结果为{0;#VALUE!;1;#VALUE!;15;-5}，这样小于60的数字变成0或者负数，文本字符变成了错误值。
步骤2： 0作为底数，步骤1的结果作为指数，做乘方运算，公式为0^{0;#VALUE!;1;#VALUE!;15;-5}，0的正数次方为0，0的0次方和负数次方均为错误值，计算结果为{#NUM!;#VALUE!;0;#VALUE!;0;#DIV/0!}，所以此步骤的目的是将步骤1中小于或等于0的数字转化为错误值，相当于是将不及格的分数都转换成错误值。
步骤3： 使用IFERROR函数来处理步骤2的结果，公式为IFERROR({#NUM!;#VALUE!;0;#VALUE!;0;#DIV/0!},1)。如果是错误值则返回1，否则返回原值，实际上是将文本和分数不及格的全部转换为1，0则是大于59的分数。处理结果为{1;1;0;1;0;1}。
步骤4： 对步骤3的结果进行求和，公式为SUM({1;1;0;1;0;1})，结果为4。
实例33 求考试及格的人数
问题描述： 图3-17所示的表格中，“分数”列的数字如果大于或等于60，则为及格，其他“缺考”或“请假”做不及格处理，现在计算考试及格的人数。
解决方法： 先将“分数”列中的文本值变成错误值，再将小于60的变成错误值，这样不满足条件的都变成了错误值，最后计算出非错误值的个数，即及格的人数。
图3-17
操作步骤： 如图3-18所示，首先将鼠标光标定位在D3单元格，输入数组公式=COUNT (0^(B2:B7-59))，然后按Ctrl+Shift+Enter组合键，完成计算。
图3-18
步骤1： 计算B2:B7-59，将“缺考”和“请假”处理成错误值，并且将小于60的数字变成小于1的值，结果为{0;#VALUE!;1;#VALUE!;15;-5}。
步骤2： 0^（B2：B7-59）相当于 0^（{0；＃VALUE！；1；＃VALUE！；15；-5}），结果为{＃NUM！；＃VALUE！；0；＃VALUE！；0；＃DIV/0！}，将小于1的值变成错误值。
步骤3： 计算数字的个数，公式为COUNT(0^(B2:B7-59))，相当于COUNT({#NUM!;#VALUE!;0;#VALUE!;0;#DIV/0!})，计算结果为2。