IFERROR函数，从结果中剔除不需要的值

Excel教程 2023-05-29 22:26:17

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

例如，要获取单元格区域中除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],””)

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

在单元格H2中的公式为：

=MIN(sum ifS(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})

结果为：

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

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

=if(VLOOKUP(A1,’C:documents andSettingsLong_Filepath_Name1Long_Filepath_Name2Long_Filepath_Name3[External_Workbook_with_Ridiculously_Long_Name.xlsx]Sheet1′!$A$1:$B$10,2,0)=0,””,VLOOKUP(A1,’C:documentsandSettingsLong_Filepath_Name1Long_Filepath_Name2Long_Filepath_Name3[External_Workbook_with_Ridiculously_Long_Name.xlsx]Sheet1′!$A$1:$B$10,2,0))

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

=ifERROR(1/(1/VLOOKUP(A1,’C:documents andSettingsLong_Filepath_Name1Long_Filepath_Name2Long_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中除负数以外的值中的最小值。

标签： Excel函数 excel函数公式 excel表格制作 Excel教程函数剔除 rank函数怎么用 rank函数的使用方法 rank函数的用法 rank排名