FILTER函數(shù)在Excel或WPS表格中是一個(gè)超級(jí)智能篩子，下面我們通過(guò)一個(gè)簡(jiǎn)單的案例理解它的強(qiáng)大之處。

微軟Office LTSC 2021專業(yè)增強(qiáng)版 簡(jiǎn)體中文批量許可版 2024年09月更新

• 類型：辦公軟件
• 大?。?/span>2.2GB
• 語(yǔ)言：簡(jiǎn)體中文
• 時(shí)間：2024-09-12

查看詳情

如下圖所示：

A1:D10區(qū)域是各部門員工的簽單金額表，另外每一單有一個(gè)狀態(tài)，提示這一單是否完成客戶金額付款。

我們要做的就是：將符合曙光部且每單金額大于3000的，且排除訂單已退款狀態(tài)下的部門總金額統(tǒng)計(jì)出來(lái)。也就是要符合3個(gè)條件匯總總金額，即多條件求和。

FILTER函數(shù)的定義與功能：

FILTER函數(shù)是Excel和WPS表格工具中的一種動(dòng)態(tài)數(shù)組函數(shù)，核心功能是根據(jù)指定條件從數(shù)據(jù)區(qū)域中篩選出符合條件的記錄。

FILTER函數(shù)語(yǔ)法為：

=FILTER(數(shù)組, 條件, [無(wú)結(jié)果時(shí)的返回值])

• 數(shù)組：需要篩選的數(shù)據(jù)區(qū)域。
• 條件：邏輯表達(dá)式。

無(wú)結(jié)果時(shí)的返回值（可選）：當(dāng)無(wú)匹配數(shù)據(jù)時(shí)顯示的內(nèi)容。

第一步：增加第一個(gè)條件，篩選部門“曙光部”

輸入函數(shù)公式：

=FILTER(C2:C10,A2:A10=F2)

FILTER函數(shù)篩選C2:C10區(qū)域的金額數(shù)據(jù)。那么我們篩選C2:C10區(qū)域內(nèi)的哪些金額數(shù)據(jù)呢？我們這樣執(zhí)行篩選，當(dāng)條件區(qū)域A2:A10單元格區(qū)域內(nèi)的部門與F2單元格部門相同時(shí)，我們才會(huì)篩選C2:C10區(qū)域內(nèi)與之對(duì)應(yīng)的行數(shù)據(jù)，數(shù)組溢出返回結(jié)果：

{7300;3500;6870;2470}

第二步：增加第二個(gè)條件，在上一步基礎(chǔ)上篩選金額大于3000的

輸入函數(shù)公式：

=FILTER(C2:C10,(A2:A10=F2)*(C2:C10>3000))

在條件1(A2:A10=F2)的基礎(chǔ)上用乘號(hào)連接條件2(C2:C10>3000)，即當(dāng)C2:C10區(qū)域內(nèi)的金額大于3000時(shí)，再上一步的篩選基礎(chǔ)上再次執(zhí)行篩選C2:C10區(qū)域內(nèi)與之對(duì)應(yīng)的行數(shù)據(jù)，數(shù)組溢出返回結(jié)果：

{7300;3500;6870}

第三步：增加第三個(gè)條件，在上兩步基礎(chǔ)上篩選排除已退款狀態(tài)的

我們輸入函數(shù)公式：

=FILTER(C2:C10,(A2:A10=F2)*(C2:C10>3000)*(D2:D10<>"已退款"))

在條件1(A2:A10=F2)與條件2(C2:C10>3000)的基礎(chǔ)上用乘號(hào)連接條件3(D2:D10<>"已退款")，即當(dāng)D2:D10區(qū)域內(nèi)標(biāo)注的文字不等于“已退款”時(shí)，再在上兩步的篩選基礎(chǔ)上執(zhí)行篩選C2:C10區(qū)域內(nèi)與之對(duì)應(yīng)的行數(shù)據(jù)，數(shù)組溢出結(jié)果：

{7300;6870}

第四步：求和

最后用SUM函數(shù)進(jìn)行篩選結(jié)果求和就行了：

=SUM(FILTER(C2:C10,(A2:A10=F2)*(C2:C10>3000)*(D2:D10<>"已退款")))

由于SUM函數(shù)天然支持?jǐn)?shù)組參數(shù)，無(wú)論參數(shù)是靜態(tài)數(shù)組（如 {1,2,3}）、單元格區(qū)域引用，還是動(dòng)態(tài)數(shù)組公式生成的溢出結(jié)果，都可以進(jìn)行求和。

比如今天這個(gè)例子中就是FILTER函數(shù)返回的數(shù)組溢出結(jié)果{7300;6870}，SUM函數(shù)對(duì)其進(jìn)行求和的。

但是SUMIF函數(shù)就無(wú)能為力了：

=SUMIF(D2:D10,"<>已退款"，F(xiàn)ILTER(C2:C10,(A2:A10=F2)*(C2:C10>3000)*(D2:D10<>"已退款")))

比如我們?cè)谇皟蓚€(gè)條件的篩選基礎(chǔ)上，用SUMIF函數(shù)嵌套第3個(gè)條件。原則上SUMIF函數(shù)的第一參數(shù)D2:D10為第3個(gè)條件區(qū)域，第二參數(shù)增加對(duì)條件區(qū)域滿足"<>已退款"的第3個(gè)條件，然后執(zhí)行對(duì)FILTER篩選結(jié)果的條件求和。但是回車結(jié)束公式時(shí)提示公式錯(cuò)誤，無(wú)法完成。

這就是因?yàn)镾UMIF函數(shù)的第3個(gè)參數(shù)是實(shí)際進(jìn)行求和的單元格區(qū)域或引用，它不支持直接使用數(shù)組。每次調(diào)用SUMIF函數(shù)時(shí)，第3參數(shù)求和區(qū)域只能指向一個(gè)具體的單元格區(qū)域或引用，而不能是一個(gè)數(shù)組。如果需要處理數(shù)組溢出的結(jié)果，比如本例中的FILTER函數(shù)數(shù)組溢出結(jié)果，就必須使用其他函數(shù)或方法來(lái)實(shí)現(xiàn)了。

推薦閱讀：

excel中怎么使用filter函數(shù) Excel函數(shù)FILTER的三種實(shí)用技巧

90%的人不知道的偷懶公式! VLOOKUP+FILTER數(shù)據(jù)篩選實(shí)現(xiàn)雙殺

最新評(píng)論