Excel公式技巧05： IFERROR函数，从结果中剔除不需要的值

学习Excel技术，关注微信公众号：

excelperfect

在使用公式时，我们经常遇到将某个值从结果数组中剔除，然后将该数组传递给另一个函数的情形。

例如，要获取单元格区域中除0以外的最小值，可以使用数组公式：

=MIN(IF(A1:A10<>0,A1:A10))

或者对于Excel 2010及以后的版本，使用AGGREGATE函数：

=AGGREGATE(15,6,A1:A10/(A1:A10<>0),1)

（注意，这里必须指定第1个参数的值为15（SMALL），因为如果指定其值为5（MIN）的话，AGGREGATE函数不接受除实际的工作表单元格区域外的任何值。然而，如果指定该参数的值为14-19，那么可以先操作任何单元格区域，也可以使用来源于AGGREGATE函数里的其他函数生成的数组、或者常量数组，这些都不是指定其值为1-13所能够处理的。）

然而，有时包含0的数组不是一个简单的工作表单元格区域而是由函数通过计算生成的数组。在这种情形下，特别是公式相当长时，重复的子句将使公式更长，这使得公式看起来很“笨重”，并且还会使Excel进行一些不必要的计算，例如：

=MIN(IF([a_very_long_formula]<>0,[a_very_long_formula],"")

下面用一个例子来说明，如下图1所示：

图1

在单元格H2中的公式为：

=MIN(SUMIFS(F2:F13,A2:A13,{"Mike","John","Alison"},B2:B13,"A",C2:C13,"B",D2:D13,"C",E2:E13,">="&DATEVALUE("2019/8/27"),E2:E13,"<="& DATEVALUE("2019/8/29")))

用来计算Mike、John和Alison在满足条件时的销售量中的最小值。公式的中间结果为：

=MIN({5,0,4})

结果为：

0

然而，如果想要得到除0以外的最小值，一般会使用下面的公式：

=MIN(IF(SUMIFS(F2:F13,A2:A13,{"Mike","John","Alison"},B2:B13,"A",C2:C13,"B",D2:D13,"C",E2:E13,">="&DATEVALUE("2019/8/27"),E2:E13,"<="& DATEVALUE("2019/8/29"))<>0,SUMIFS(F2:F13,A2:A13,{"Mike","John","Alison"},B2:B13,"A",C2:C13,"B",D2:D13,"C",E2:E13,">="&DATEVALUE("2019/8/27"),E2:E13,"<="& DATEVALUE("2019/8/29"))))

可以得到结果：

4

但是，正如前面所说的，看上去很笨拙。

并且，这样的公式对于很小的数据量来说可能算不了什么，但是如果数据量很大且有很多相同的公式，将公式的主要部分计算两次将占用双倍的资源。如果有一个与IFERROR函数类似的IFZERO函数就好了！

其实可以使用含有IFERROR函数的公式来简化：

=MIN(IFERROR(1/(1/(SUMIFS(F2:F13,A2:A13,{"Mike","John","Alison"},B2:B13,"A",C2:C13,"B",D2:D13,"C",E2:E13,">="&DATEVALUE("2019/8/27"),E2:E13,"<="& DATEVALUE("2019/8/29")))),""))

简单讲解一下这个公式的运作原理。

根据上文得出的结果，上面的公式可以转换为：

=MIN(IFERROR(1/(1/({5,0,4})),""))

转换为：

=MIN(IFERROR(1/({0.2,#DIV/0!,0.25}),""))

转换为：

=MIN(IFERROR({5,#DIV/0!,4},""))

可以看到，Excel将1/#DIV/0!的结果仍返回为#DIV/0!。转换为：

=MIN({5,””,4})

结果为：

4

因此，可以使用这项技术来避免重复非常长的公式子句的情形。

也可以使用这项技术处理在公式中包含重复的单元格路径引用的情形。例如：

=IF(VLOOKUP(A1,'C:\Documents andSettings\Long_Filepath_Name1\Long_Filepath_Name2\Long_Filepath_Name3\[External_Workbook_with_Ridiculously_Long_Name.xlsx]Sheet1'!$A$1:$B$10,2,0)=0,"",VLOOKUP(A1,'C:\DocumentsandSettings\Long_Filepath_Name1\Long_Filepath_Name2\Long_Filepath_Name3\[External_Workbook_with_Ridiculously_Long_Name.xlsx]Sheet1'!$A$1:$B$10,2,0))

可以使用下面的公式替代：

=IFERROR(1/(1/VLOOKUP(A1,'C:\Documents andSettings\Long_Filepath_Name1\Long_Filepath_Name2\Long_Filepath_Name3\[External_Workbook_with_Ridiculously_Long_Name.xlsx]Sheet1'!$A$1:$B$10,2,0)),"")

除了排除零以外，我们还可以在很多情形下使用此方法。我们需要做的就是操控想要排除值的公式，将其解析为0后再放置在IFERROR(1/(1/...后。例如，要获取单元格A1:A10中除3以外的最小值，可以使用数组公式：

=MIN(IF(A1:A10<>3,A1:A10))

也可以使用公式：

=MIN(IFERROR(1/1/(A1:A10-3))+3,””))

还有一个示例：

=MIN(IFERROR(POWER(SQRT(A1:A10),2),""))

与下面的公式结果相同：

=MIN(IF(A1:A10>=0,A1:A10))

返回单元格A1:A10中除负数以外的值中的最小值。

注：本技巧整理自excelxor.com，有兴趣的朋友可以研阅原文。