众所周知,当一列数据中存在错误值时,是没有办法对这列数据进行计算的。如果因为种种原因,必须对存在错误值的列求和、求平均值,而错误值出现的位置每次不固定,那怎么实现呢?
之前我曾经写过如何忽略错误值求和,具体请参见Excel – 存在错误值,也能正常求和。该案例中的所有数值都是正数,所以用文中的公式可以满足需求。如果既有正数,又有负数,那上文中的公式就不适用了,需要用我们今天教的方法。
案例:
下图是某公司销售名下的净增客户数,即新增减去流失客户的总数,其中有单元格因系统数据延迟存在错误值,如何忽略错误值求净增客户总数和平均数?
解决方案:
1. 总数:在 B16 单元格输入以下公式,按 Ctrl+Shift+Enter 即可:
=SUM((IF(ISERROR(B2:B15),"",B2:B15)))
公式释义:
ISERROR 函数作用:用来确定一个数字或表达式是否错误。如果参数的值错误,则返回 True;否则返回 False。
IF(ISERROR(B2:B15),"",B2:B15):依次判断 B2 至 B15 单元格是否为错误值;如果是错误值,则将它转换为空值,不是错误值,就保留该数值本身
最后用 sum 公式对数组求和
数组公式必须按 Ctrl+Shift+Enter 结束才能生效
以下就是求和结果。公式外面有一对花括号,表示这是个数组公式。
2. 平均数:在 B17 单元格输入以下公式,按 Ctrl+Shift+Enter 即可:
=AVERAGE((IF(ISERROR(B2:B15),"",B2:B15)))
公式释义:
跟上述求和公式原理一样,先用 IF 和 ISERROR,将指定范围内的非错误提取出来,再用 AVERAGE 函数对其求平均值
最后按 Ctrl+Shift+Enter 结束,生成数组公式
以下就是平均值的结果。
Excel学习世界
转发、在看也是爱!
领取专属 10元无门槛券
私享最新 技术干货