有错误值没法查找、求和?这2个公式,包你一看就会
我们经常会遇到错误值,比如#N/A,这种会导致很多麻烦,查找、求和等都会出问题。遇到这种情况,你知道怎么处理吗?
继续从学员的实际案例来看问题。
1.在反馈列获取:前三列的只要有一列有正确值,就反馈正确值,如果三列都没有正确值的,直接反馈错误值。
估计很多人的第一想法就是用IF+ISERROR逐一判断,偷偷的告诉你,卢子的第一眼也是,不过第二眼就改变了想法。
LOOKUP在查找的时候,忽略错误值,"座"是一个接近最大的问题,查找的时候返回小于或等于这个值的最后一个单元格,因此可以找到对应的反馈值。
=LOOKUP("座",A2:C2)
如果反馈值都是数字,可以用9E+307代替"座",9E+307是接近最大的数字。
=LOOKUP(9E+307,A2:C2)
LOOKUP这种用法平常用得比较少,一般都用经典查找模式,再举一个小例子巩固下。
查找余额,也就是E列最后一个数字,利用所有数字小于9E+307的特点,返回最后一个值。
=LOOKUP(9E+307,E:E)
2.当金额有错误值的时候,直接用SUM求和得到的是错误值,怎么处理?
依然是利用所有数字小于9E+307的特点,现在是按条件求和,那就用SUMIF。
=SUMIF(B2:B8,"<9E+307",B2:B8)
当条件区域和求和区域相同是,可以省略求和区域,对公式进一步简化。
=SUMIF(B2:B8,"<9E+307")
顺便提下,在数据源跟透视表让错误值显示空白的方法。
01 数据源嵌套IFERROR
=IFERROR(B2,"")
02 透视表,右键,透视表选项,勾选对于错误值,显示。
推荐:写了10多年公式,第一次遇到这种奇葩错误,你能遇到算我输!
你平常遇到错误值,是怎么处理的?
作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)
赞 (0)