你是不是也遇到過這樣的場景面對(duì)一份密密麻麻的Excel數(shù)據(jù)表老板讓你“把A列大于100且B列是‘已完成’的數(shù)據(jù)篩出來”或者“找出所有不在這個(gè)名單里的人”你熟練地打開了篩選器卻發(fā)現(xiàn)“與”條件好說“或”條件怎么搞更別提“反向篩選”了——想找出所有“非A且非B”的數(shù)據(jù)難道要手動(dòng)一個(gè)個(gè)勾掉嗎很多人第一時(shí)間會(huì)想到SUMIFS、COUNTIFS或者高級(jí)篩選。沒錯(cuò)它們能解決大部分問題。但今天我要講的是一個(gè)被嚴(yán)重低估的“邪修”思路用最基礎(chǔ)的COUNTIF函數(shù)配合數(shù)組公式實(shí)現(xiàn)靈活的多條件“或”篩選和反向篩選。這聽起來有點(diǎn)反直覺。COUNTIF不是用來數(shù)數(shù)的嗎怎么還能篩選這正是“邪修”的精髓——跳出函數(shù)的常規(guī)用法利用其返回?cái)?shù)值0或非0的特性構(gòu)建出強(qiáng)大的邏輯判斷引擎。相比SUMIFS的“且”邏輯COUNTIF構(gòu)建的“或”邏輯和“非”邏輯在應(yīng)對(duì)不規(guī)則、動(dòng)態(tài)變化的條件組合時(shí)往往更加簡潔和直觀。本文將帶你徹底搞懂這個(gè)技巧。讀完你將掌握核心原理COUNTIF如何化身邏輯判斷工具。實(shí)戰(zhàn)三步法從單條件到多條件“或”篩選再到復(fù)雜的反向篩選。完整公式剖析結(jié)合FILTER、SUMPRODUCT等函數(shù)寫出既強(qiáng)大又易讀的公式。避坑指南處理文本、數(shù)字、空值時(shí)的關(guān)鍵細(xì)節(jié)。性能與替代方案何時(shí)該用何時(shí)不該用。我們從一個(gè)最真實(shí)的辦公痛點(diǎn)開始。1. 為什么需要 COUNTIF 來做“邪修”篩選在深入公式之前我們先明確兩個(gè)最常見的篩選困境這也是COUNTIF解法大顯身手的地方。困境一多條件“或”篩選的繁瑣假設(shè)你有一張銷售記錄表需要找出“產(chǎn)品是‘手機(jī)’或‘平板’”的所有記錄。使用常規(guī)篩選器你需要在“產(chǎn)品”列下拉菜單中手動(dòng)勾選“手機(jī)”和“平板”。如果條件有5個(gè)、10個(gè)呢勾到手酸。如果條件列表是動(dòng)態(tài)變化的比如來自另一個(gè)單元格區(qū)域常規(guī)篩選幾乎無法自動(dòng)完成。困境二反向篩選的“繞路”老板說“列出所有‘部門’不是‘銷售部’且‘狀態(tài)’不是‘已離職’的員工?！蹦愕牡谝环磻?yīng)可能是先篩選出“銷售部”的人再篩選出“已離職”的人然后手動(dòng)把這兩批人從總表里剔除或者用高級(jí)篩選寫條件區(qū)域但需要理解“”運(yùn)算符和條件區(qū)域布局規(guī)則對(duì)很多人來說門檻不低。COUNTIF的邪道解法恰恰能優(yōu)雅地解決這兩個(gè)問題。它的核心優(yōu)勢(shì)在于條件動(dòng)態(tài)化條件可以是一個(gè)單元格區(qū)域增刪條件只需修改這個(gè)區(qū)域公式自動(dòng)生效。邏輯直觀化“或”關(guān)系就是檢查目標(biāo)值是否出現(xiàn)在條件列表中“非”關(guān)系就是檢查結(jié)果是否為0。兼容性廣從古老的 Excel 2007 到最新的 Microsoft 365 都能使用數(shù)組公式部分版本需按 CtrlShiftEnter。接下來我們從COUNTIF的基礎(chǔ)講起重新認(rèn)識(shí)這個(gè)函數(shù)。2. COUNTIF 函數(shù)的核心不止于計(jì)數(shù)更是邏輯探測(cè)器COUNTIF函數(shù)語法非常簡單COUNTIF(range, criteria)range要計(jì)數(shù)的單元格區(qū)域。criteria計(jì)數(shù)的條件可以是數(shù)字、表達(dá)式、單元格引用或文本字符串如100,蘋果,A2。傳統(tǒng)認(rèn)知它在range里數(shù)一數(shù)有多少個(gè)單元格滿足criteria返回一個(gè)數(shù)字。邪修視角它返回的數(shù)字本身就是一個(gè)布爾值TRUE/FALSE的數(shù)值化形式。在Excel中TRUE相當(dāng)于1FALSE相當(dāng)于0。所以如果COUNTIF(A2, 蘋果)的結(jié)果是1意味著A2單元格等于“蘋果”邏輯為真。如果結(jié)果是0意味著A2單元格不等于“蘋果”邏輯為假。關(guān)鍵躍遷當(dāng)criteria參數(shù)是一個(gè)區(qū)域時(shí)COUNTIF會(huì)進(jìn)行一系列匹配檢查。COUNTIF(A2, $D$2:$D$5)這個(gè)公式的意思是檢查A2單元格的值是否出現(xiàn)在區(qū)域$D$2:$D$5中。如果出現(xiàn)返回1或匹配到的次數(shù)如果不出現(xiàn)返回0。這就是我們實(shí)現(xiàn)“或”篩選的基石。區(qū)域$D$2:$D$5就是我們的“條件列表”。A2只要匹配其中任意一個(gè)公式結(jié)果就大于0即邏輯為真。理解了這一點(diǎn)我們就可以開始構(gòu)建篩選體系了。3. 環(huán)境準(zhǔn)備理解絕對(duì)引用與數(shù)組公式在動(dòng)手前有兩個(gè)基礎(chǔ)概念必須牢固掌握否則公式會(huì)錯(cuò)亂。3.1 絕對(duì)引用 ($) 的重要性在構(gòu)建下拉填充的公式時(shí)引用方式?jīng)Q定成敗。$D$2:$D$5絕對(duì)引用。無論公式復(fù)制到哪條件區(qū)域始終鎖定在D2:D5。A2相對(duì)引用。當(dāng)公式向下填充時(shí)會(huì)自動(dòng)變成A3,A4... 從而逐行檢查。在本文的所有公式中條件列表區(qū)域務(wù)必使用絕對(duì)引用如$E$2:$E$10而待檢查的單元格使用相對(duì)引用。3.2 數(shù)組公式與動(dòng)態(tài)數(shù)組本文的公式分為兩類傳統(tǒng)數(shù)組公式適用于 Excel 2019 及更早版本。公式輸入后必須按Ctrl Shift Enter組合鍵結(jié)束Excel會(huì)在公式兩邊自動(dòng)加上大括號(hào){}。這類公式通常與SUMPRODUCT、INDEX等函數(shù)配合進(jìn)行多條件判斷和結(jié)果聚合。動(dòng)態(tài)數(shù)組公式適用于 Microsoft 365 和 Excel 2021。這是革命性的更新一個(gè)公式就能返回多個(gè)結(jié)果并自動(dòng)“溢出”到下方的單元格。FILTER函數(shù)就是動(dòng)態(tài)數(shù)組函數(shù)的代表。本文將同時(shí)給出兩種環(huán)境的解法但會(huì)以更現(xiàn)代、更強(qiáng)大的動(dòng)態(tài)數(shù)組公式FILTER作為主要講解對(duì)象。我們的示例數(shù)據(jù)如下姓名 (A)部門 (B)銷售額 (C)張三銷售部1500李四技術(shù)部800王五市場部1200趙六銷售部2000孫七技術(shù)部950目標(biāo)1或篩選篩選出“部門”為“銷售部”或“技術(shù)部”的員工。目標(biāo)2反向篩選篩選出“部門”不是“銷售部”且不是“技術(shù)部”的員工。下面我們進(jìn)入實(shí)戰(zhàn)。4. 核心流程拆解從單條件到多條件“或”篩選讓我們把復(fù)雜問題分解。首先實(shí)現(xiàn)“或”篩選。4.1 第一步構(gòu)建邏輯判斷列我們?cè)贒2單元格輸入以下公式并向下填充COUNTIF($B2, $F$2:$F$3) 0$B2相對(duì)引用檢查當(dāng)前行的部門。$F$2:$F$3絕對(duì)引用這是我們的條件列表區(qū)域假設(shè)我們?cè)贔2和F3分別輸入了“銷售部”和“技術(shù)部”。COUNTIF(...)判斷B2的值是否在{“銷售部” “技術(shù)部”}中。在則返回1不在則返回0。 0將數(shù)值結(jié)果轉(zhuǎn)化為TRUE/FALSE。10為TRUE00為FALSE。填充后D列會(huì)顯示一系列TRUE/FALSETRUE就代表該行滿足“部門是銷售部或技術(shù)部”的條件。4.2 第二步利用 FILTER 函數(shù)輸出結(jié)果動(dòng)態(tài)數(shù)組公式這是最簡潔的方法。在一個(gè)空白單元格如H2輸入FILTER(A2:C6, COUNTIF($B$2:$B$6, $F$2:$F$3)0)公式詳解A2:C6這是我們的源數(shù)據(jù)區(qū)域。COUNTIF($B$2:$B$6, $F$2:$F$3)0這是篩選條件。COUNTIF($B$2:$B$6, $F$2:$F$3)這里發(fā)生了一個(gè)數(shù)組運(yùn)算。$B$2:$B$6是一個(gè)5行1列的垂直數(shù)組$F$2:$F$3是一個(gè)2行1列的垂直數(shù)組。Excel會(huì)進(jìn)行“廣播”計(jì)算最終生成一個(gè)5行1列的中間數(shù)組。這個(gè)數(shù)組的每個(gè)元素表示對(duì)應(yīng)行的B列值在F2:F3中出現(xiàn)的次數(shù)。對(duì)于“張三”銷售部在{銷售部 技術(shù)部}中出現(xiàn)1次中間結(jié)果為1。對(duì)于“李四”技術(shù)部出現(xiàn)1次結(jié)果為1。對(duì)于“王五”市場部出現(xiàn)0次結(jié)果為0。以此類推。0將上述中間數(shù)組的每個(gè)元素與0比較10為TRUE00為FALSE。最終得到一個(gè)由TRUE/FALSE構(gòu)成的邏輯數(shù)組{TRUE; TRUE; FALSE; TRUE; TRUE}。FILTER函數(shù)根據(jù)這個(gè)邏輯數(shù)組從A2:C6中篩選出對(duì)應(yīng)為TRUE的行。按下回車H2:J5區(qū)域會(huì)自動(dòng)“溢出”顯示出篩選結(jié)果張三、李四、趙六、孫七的數(shù)據(jù)。這一切只需要一個(gè)公式4.3 第三步傳統(tǒng)數(shù)組公式方案兼容舊版如果你的Excel不支持動(dòng)態(tài)數(shù)組可以使用INDEXSMALLIF的經(jīng)典組合但這更復(fù)雜。更推薦使用SUMPRODUCT配合輔助列。 在輔助列D2輸入并下拉SUMPRODUCT(($B2$F$2:$F$3)*1)或者直接用--(COUNTIF($B2, $F$2:$F$3)0) // 雙負(fù)號(hào)將TRUE/FALSE轉(zhuǎn)為1/0然后對(duì)D列進(jìn)行篩選篩選值為1的行即可。雖然多了一步但邏輯清晰兼容性好。至此“或”篩選已經(jīng)完成。它的強(qiáng)大之處在于你只需要在F2:F3區(qū)域里增刪部門名篩選結(jié)果就會(huì)實(shí)時(shí)、動(dòng)態(tài)地更新無需修改公式。5. 反向篩選的完整實(shí)現(xiàn)找出“不屬于”任何條件的數(shù)據(jù)反向篩選即“非”篩選是“或”篩選的逆操作。我們的目標(biāo)是找出那些在B列的值完全沒有出現(xiàn)在條件列表中的行?；谥暗倪壿嬤@變得非常簡單COUNTIF(...)的結(jié)果如果等于0就說明該行數(shù)據(jù)是“反向”的。5.1 動(dòng)態(tài)數(shù)組公式實(shí)現(xiàn)FILTER在空白單元格輸入FILTER(A2:C6, COUNTIF($B$2:$B$6, $F$2:$F$3)0)與“或”篩選公式的唯一區(qū)別就是把0改成了0。COUNTIF(...)0生成邏輯數(shù)組只有那些在條件列表中一次都沒出現(xiàn)的部門才會(huì)是TRUE。在我們的例子中只有“王五”市場部不在{銷售部 技術(shù)部}中所以邏輯數(shù)組為{FALSE; FALSE; TRUE; FALSE; FALSE}。FILTER函數(shù)據(jù)此只返回TRUE對(duì)應(yīng)的那一行數(shù)據(jù)。按下回車結(jié)果區(qū)域?qū)⒅伙@示王五的記錄。5.2 處理多列反向篩選“且非”關(guān)系更復(fù)雜的需求來了篩選出“部門不是銷售部且銷售額不大于1000”的記錄。 這其實(shí)是兩個(gè)反向條件的“與”關(guān)系。我們需要構(gòu)建兩個(gè)邏輯判斷然后相乘。假設(shè)條件1部門不等于“銷售部”條件列表在F2。 條件2銷售額不大于1000即小于等于1000這是一個(gè)數(shù)值條件。公式如下FILTER(A2:C6, (COUNTIF($B$2:$B$6, $F$2)0) * ($C$2:$C$61000))公式詳解(COUNTIF($B$2:$B$6, $F$2)0)生成一個(gè)數(shù)組部門不是“銷售部”的為TRUE。($C$2:$C$61000)生成另一個(gè)數(shù)組銷售額小于等于1000的為TRUE。兩個(gè)邏輯數(shù)組相乘*在數(shù)組運(yùn)算中TRUE*TRUE1其他情況為0。只有兩個(gè)條件同時(shí)為TRUE的行結(jié)果才是1被視作TRUE。FILTER根據(jù)最終結(jié)果為1TRUE的行進(jìn)行篩選。這個(gè)公式會(huì)返回李四技術(shù)部800和孫七技術(shù)部950的數(shù)據(jù)。張三和趙六因?yàn)椴块T是銷售部被排除王五因?yàn)殇N售額12001000被排除。6. 進(jìn)階技巧與常見問題排查掌握了核心公式后我們來看一些實(shí)戰(zhàn)中必然會(huì)遇到的細(xì)節(jié)和坑。6.1 條件列表包含空單元格或公式返回空值如果條件區(qū)域$F$2:$F$10中有空單元格COUNTIF在匹配時(shí)會(huì)將空值也作為一個(gè)條件。這可能導(dǎo)致你意想不到的結(jié)果比如匹配到數(shù)據(jù)源中的空單元格。解決方案使用動(dòng)態(tài)范圍或清理數(shù)據(jù)源?？梢允褂肙FFSET或TABLE但更簡單的方法是確保條件區(qū)域是緊湊無空的?；蛘呤褂肍ILTER先清理?xiàng)l件列表LET( criteriaList, FILTER($F$2:$F$100, $F$2:$F$100), // 去除空值 FILTER(A2:C100, COUNTIF($B$2:$B$100, criteriaList)0) )LET函數(shù)可定義中間變量需 Microsoft 365 支持6.2 匹配文本時(shí)的大小寫與通配符COUNTIF默認(rèn)不區(qū)分大小寫?！癆pple”和“apple”會(huì)被視為相同。如果需要區(qū)分可以考慮使用EXACT函數(shù)結(jié)合數(shù)組公式但這會(huì)復(fù)雜很多。對(duì)于通配符*,?,~如果條件本身包含這些字符需要在criteria參數(shù)中將~放在它們前面進(jìn)行轉(zhuǎn)義例如~*來匹配星號(hào)本身。6.3 處理數(shù)字與文本混合列當(dāng)數(shù)據(jù)列中既有數(shù)字又有文本時(shí)COUNTIF的行為是可靠的。但要注意數(shù)字100和文本100在COUNTIF眼中是不同的。確保你的條件類型與數(shù)據(jù)列類型一致。如果不確定可以使用TEXT函數(shù)或VALUE函數(shù)進(jìn)行轉(zhuǎn)換。6.4 性能問題大數(shù)據(jù)量下的優(yōu)化COUNTIF配合數(shù)組運(yùn)算在數(shù)據(jù)量極大例如數(shù)十萬行時(shí)計(jì)算可能會(huì)變慢因?yàn)樗侵鹦羞M(jìn)行數(shù)組比較。優(yōu)化建議縮小范圍盡量精確限定COUNTIF的range參數(shù)不要引用整列如B:B而用實(shí)際范圍如$B$2:$B$10000。使用輔助列如果條件不常變化可以將COUNTIF(...)0或0的計(jì)算結(jié)果放在一個(gè)輔助列中然后直接基于這個(gè)邏輯列進(jìn)行篩選或FILTER。這相當(dāng)于把計(jì)算成本分?jǐn)偟綌?shù)據(jù)更新時(shí)而不是每次篩選時(shí)?？紤] Power Query對(duì)于極其復(fù)雜、頻繁的篩選需求使用 Power Query 進(jìn)行數(shù)據(jù)清洗和轉(zhuǎn)換是更專業(yè)、性能更好的選擇。6.5 常見錯(cuò)誤與排查問題現(xiàn)象可能原因排查方式解決方案#VALUE!錯(cuò)誤COUNTIF的range和criteria區(qū)域維度不匹配或criteria是錯(cuò)誤的數(shù)據(jù)類型。檢查COUNTIF內(nèi)部的兩個(gè)參數(shù)。確保criteria是單個(gè)值、單元格引用或一維區(qū)域。修正區(qū)域引用。對(duì)于復(fù)雜條件確保其格式正確如文本加引號(hào)。結(jié)果全為FALSE或篩選不出數(shù)據(jù)1. 絕對(duì)/相對(duì)引用用錯(cuò)導(dǎo)致條件區(qū)域偏移。2. 條件列表與實(shí)際數(shù)據(jù)不匹配如多余空格。3. 邏輯運(yùn)算符方向錯(cuò)誤該用0用了0。1. 按F2進(jìn)入單元格編輯狀態(tài)查看公式引用。2. 使用TRIM函數(shù)清理數(shù)據(jù)或直接用比較單元格。3. 復(fù)查業(yè)務(wù)邏輯。1. 鎖定條件區(qū)域的絕對(duì)引用$。2. 清洗數(shù)據(jù)確保可比性。3. 修正邏輯判斷部分。FILTER函數(shù)返回#CALC!錯(cuò)誤篩選條件最終所有結(jié)果都是FALSE沒有數(shù)據(jù)符合條件。檢查篩選條件邏輯是否過于嚴(yán)格或者數(shù)據(jù)本身是否為空。這是正常情況表示未找到匹配項(xiàng)?？梢允褂肐FERROR包裹FILTER顯示友好提示IFERROR(FILTER(...), 無匹配數(shù)據(jù))公式在舊版 Excel 中不工作使用了FILTER、LET等新函數(shù)。確認(rèn) Excel 版本?；赝说绞褂肧UMPRODUCT或輔助列自動(dòng)篩選的方案。7. 最佳實(shí)踐與工程化建議將“邪修”技巧用于實(shí)際工作流時(shí)遵循以下建議能讓你的表格更健壯、更易維護(hù)。命名區(qū)域讓公式更可讀不要使用$F$2:$F$10這樣的引用。選中條件區(qū)域在左上角名稱框輸入“部門條件列表”然后按回車。公式就可以寫成FILTER(數(shù)據(jù)表, COUNTIF(數(shù)據(jù)表[部門], 部門條件列表)0)清晰明了即使表格結(jié)構(gòu)變動(dòng)也只需更新名稱定義無需修改大量公式。將條件列表放在獨(dú)立的工作表專門創(chuàng)建一個(gè)名為“Config”或“參數(shù)”的工作表存放所有篩選條件列表。這樣主數(shù)據(jù)表看起來更干凈條件管理也更集中。使用表格對(duì)象CtrlT將你的源數(shù)據(jù)轉(zhuǎn)換為“表格”快捷鍵CtrlT。這樣做的好處是公式中可以使用結(jié)構(gòu)化引用如Table1[部門]自動(dòng)適應(yīng)數(shù)據(jù)行的增減。FILTER等動(dòng)態(tài)數(shù)組公式引用表格列時(shí)溢出范圍也會(huì)自動(dòng)調(diào)整。為反向篩選提供清晰的標(biāo)簽在輸出結(jié)果旁邊用公式自動(dòng)生成篩選條件的描述避免他人或未來的你迷惑。篩選條件部門不屬于 TEXTJOIN(, , TRUE, 部門條件列表)封裝復(fù)雜邏輯如果同一個(gè)復(fù)雜的反向篩選邏輯需要在多個(gè)地方使用考慮使用LAMBDA函數(shù)Microsoft 365將其定義為一個(gè)自定義函數(shù)。例如定義一個(gè)叫FilterNotIn的函數(shù)以后只需調(diào)用FilterNotIn(數(shù)據(jù)區(qū)域, 判斷列, 排除列表)即可。8. 總結(jié)何時(shí)該用何時(shí)該換用COUNTIF實(shí)現(xiàn)多條件“或”篩選和反向篩選是一個(gè)巧妙、靈活且兼容性強(qiáng)的技巧。它特別適合以下場景條件列表動(dòng)態(tài)變化條件經(jīng)常增刪改且來源可能是一個(gè)手工維護(hù)的區(qū)域。條件數(shù)量較多需要匹配的條件有十幾個(gè)甚至幾十個(gè)手動(dòng)勾選不現(xiàn)實(shí)。需要嵌套在復(fù)雜公式中作為中間邏輯判斷的一部分參與更復(fù)雜的計(jì)算。Excel版本較舊在沒有FILTER、XLOOKUP等新函數(shù)的環(huán)境下它是實(shí)現(xiàn)動(dòng)態(tài)“或”篩選的輕量級(jí)方案。然而它并非萬能。在以下情況可能有更好的選擇極高性能要求面對(duì)海量數(shù)據(jù)優(yōu)先考慮 Power Pivot 或 Power Query。條件邏輯極其復(fù)雜涉及多重嵌套的“與”、“或”、“非”組合使用SUMPRODUCT或FILTER直接構(gòu)建布爾表達(dá)式可能更直觀。需要返回匹配項(xiàng)的具體信息例如不僅要篩選還要知道每條數(shù)據(jù)具體匹配了條件列表中的哪一項(xiàng)這時(shí)XLOOKUP或INDEX/MATCH可能更合適。技術(shù)的價(jià)值在于解決問題。COUNTIF的這次“邪修”之旅核心不是記住幾個(gè)公式而是掌握一種思路深入理解每個(gè)基礎(chǔ)函數(shù)的核心輸出尤其是其數(shù)值/邏輯特性并敢于將它們以非常規(guī)的方式組合從而解決看似需要更高級(jí)工具才能處理的問題。這種“函數(shù)思維”的鍛煉遠(yuǎn)比死記硬背一百個(gè)函數(shù)語法更有價(jià)值。下次當(dāng)你在Excel中遇到棘手的多條件篩選時(shí)不妨先想一想COUNTIF能不能幫上忙也許一個(gè)看似簡單的函數(shù)就能撬動(dòng)讓你頭疼許久的難題。