IFERROR函數(shù)怎么剔除不需要的值

IFERROR函數(shù)怎么剔除不需要的值

IFERROR函數(shù)怎么剔除不需要的值？在使用公式時，我們經(jīng)常遇到將某個值從結(jié)果數(shù)組中剔除，然后將該數(shù)組傳遞給另一個函數(shù)的情形。

例如，要獲取單元格區(qū)域中除0以外的最小值，可以使用數(shù)組公式：

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

或者對于Excel 2010及以后的版本，使用AGGREGATE函數(shù)：

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

（注意，這里必須指定第1個參數(shù)的值為15（SMALL），因為如果指定其值為5（MIN）的話，AGGREGATE函數(shù)不接受除實際的工作表單元格區(qū)域外的任何值。然而，如果指定該參數(shù)的值為14-19，那么可以先操作任何單元格區(qū)域，也可以使用來源于AGGREGATE函數(shù)里的其他函數(shù)生成的數(shù)組、或者常量數(shù)組，這些都不是指定其值為1-13所能夠處理的。）

然而，有時包含0的數(shù)組不是一個簡單的工作表單元格區(qū)域而是由函數(shù)通過計算生成的數(shù)組。在這種情形下，特別是公式相當(dāng)長時，重復(fù)的子句將使公式更長，這使得公式看起來很“笨重”，并且還會使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″)))),””))

簡單講解一下這個公式的運作原理。

根據(jù)上文得出的結(jié)果，上面的公式可以轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

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

可以看到，Excel將1/#DIV/0!的結(jié)果仍返回為#DIV/0!。轉(zhuǎn)換為：

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

結(jié)果為：

4

因此，可以使用這項技術(shù)來避免重復(fù)非常長的公式子句的情形。

也可以使用這項技術(shù)處理在公式中包含重復(fù)的單元格路徑引用的情形。例如：

=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以外的最小值，可以使用數(shù)組公式：

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

也可以使用公式：

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

還有一個示例：

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

與下面的公式結(jié)果相同：

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

返回單元格A1:A10中除負數(shù)以外的值中的最小值。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。