IFERROR函数，从结果中剔除不需要的值-英雄云拓展知识分享

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

举例来看，要获得单元格区域中除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⑴9，那末可以先操作任何单元格区域，也能够使用来源于AGGREGATE函数里的其他函数生成的数组、或常量数组，这些都不是指定其值为1⑴3所能够处理的。）

但是，有时包括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/27”),E2:E13,”<=>=”&DATEVALUE(“2019/8/27”),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,””))

还有一个示例：

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

与下面的公式结果相同：

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

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