做數(shù)據(jù)統(tǒng)計(jì)的人遲早都會(huì)撞上“按條件去重計(jì)數(shù)”這個(gè)需求。查訂單有多少個(gè)客戶、算某一個(gè)地區(qū)一共成交了幾家門店、統(tǒng)計(jì)某段時(shí)間內(nèi)出現(xiàn)過多少個(gè)產(chǎn)品型號這些事情聽起來跟 Excel 函數(shù)公式大全里那些花活差不多真上手卻容易翻車用 COUNTIF 能數(shù)出所有出現(xiàn)次數(shù)一加條件就不知道怎么寫用 SUMIFS 能求和卻不能去重有人把輔助列拉了一長串最后喊公式下拉失效、結(jié)果對不上。這篇內(nèi)容就是把“按條件去重計(jì)數(shù)”這件事徹底拆開從普通計(jì)數(shù)講到多條件去重計(jì)數(shù)從低版本 Excel 到 365 新函數(shù)配合可復(fù)制的公式和實(shí)戰(zhàn)案例給到能直接抄作業(yè)的寫法。適合每天跟訂單表、客戶表、庫存表打交道的業(yè)務(wù)人員也適合剛接手報(bào)表、被“重復(fù)記錄”折磨的數(shù)據(jù)處理新手。1. 先搞懂“去重計(jì)數(shù)”和“條件去重計(jì)數(shù)”很多朋友一上來就搜“excel多條件篩選”“excel函數(shù)公式大全”想找一個(gè)現(xiàn)成函數(shù)結(jié)果發(fā)現(xiàn)單個(gè)函數(shù)沒有直接支持的。真正的原因在于Excel 的常規(guī)函數(shù)都是“面向行”操作的要么統(tǒng)計(jì)行數(shù)要么按條件篩選行要么對行做計(jì)算。而“去重計(jì)數(shù)”是先看整列或者某個(gè)范圍內(nèi)有哪些不重復(fù)的項(xiàng)目再對不重復(fù)項(xiàng)目計(jì)數(shù)這個(gè)邏輯本身就需要“數(shù)組計(jì)算”或?qū)Ｓ眯潞瘮?shù)來支持。不先把概念掰開后面所有公式都只會(huì)背不會(huì)改。1.1 普通去重計(jì)數(shù)先把地基打牢普通去重計(jì)數(shù)典型例子是“客戶名單里一共有多少個(gè)不同的客戶”。假設(shè)A列是客戶名稱從A2到A100有數(shù)據(jù)想去重計(jì)數(shù)最經(jīng)典的低版本寫法是SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100))這句話讀起來稍微繞但原理非常直觀。COUNTIF(A2:A100, A2:A100) 是對范圍內(nèi)每一個(gè)單元格分別統(tǒng)計(jì)它出現(xiàn)的次數(shù)。比如“張三”出現(xiàn)了3次這個(gè)函數(shù)就會(huì)在一整段數(shù)組中給“張三”對應(yīng)的位置都返回3。再用1去除以這個(gè)次數(shù)每一個(gè)重復(fù)項(xiàng)就被拆成了1/3 1/3 1/3加起來正好等于1。SUMPRODUCT 再把所有“1”相加得到的就是不重復(fù)項(xiàng)的總數(shù)。這個(gè)公式是低版本環(huán)境里的萬金油能運(yùn)行但對新人不太友好看不懂就很難改。后來 Office 365 和 WPS 新版里有了 UNIQUE 函數(shù)直接可以寫COUNTA(UNIQUE(A2:A100))UNIQUE 負(fù)責(zé)把不重復(fù)的項(xiàng)目提取成一個(gè)數(shù)組COUNTA 再數(shù)一下有多少個(gè)含義清清楚楚。如果你的 Excel 版本支持我建議優(yōu)先用這個(gè)寫法可讀性高后續(xù)擴(kuò)展條件也方便。不會(huì)算的人先把普通去重計(jì)數(shù)弄明白條件去重只是在這基礎(chǔ)上加一道“閘門”。1.2 按條件去重計(jì)數(shù)為什么不能直接用 COUNTIF 或 SUMIFS按條件去重計(jì)數(shù)的典型需求是“上海地區(qū)一共成交了哪幾個(gè)客戶”或者“3月份有幾個(gè)產(chǎn)品型號產(chǎn)生了銷量”。這個(gè)時(shí)候很多人本能地寫 COUNTIFS卻忘了 COUNTIFS 的計(jì)數(shù)規(guī)則是“符合條件的行數(shù)”重復(fù)出現(xiàn)同一個(gè)客戶會(huì)被重復(fù)計(jì)算。比如上海區(qū)的一位客戶成交了5筆COUNTIFS 會(huì)給你記5可你想要的可能是記1。SUMIFS 也一樣它只能對滿足條件的數(shù)值求和根本不去重。換句話說條件計(jì)數(shù)、條件求和都是“按行”玩的去重計(jì)數(shù)是“按值”玩的。我們要自己設(shè)計(jì)公式邏輯讓公式先判斷行是否滿足條件再判斷同一個(gè)值是否已經(jīng)出現(xiàn)過。只有理解了這一步才能看懂 SUMPRODUCT 版公式里的乘法邏輯。有些朋友會(huì)問那為什么不用 Excel 自帶的“刪除重復(fù)值”功能因?yàn)閯h除重復(fù)值會(huì)物理刪除行破壞原始臺賬。我們做報(bào)表講究一個(gè)“不動(dòng)源數(shù)據(jù)”所以要用公式動(dòng)態(tài)計(jì)算源數(shù)據(jù)一更新結(jié)果也跟著更新。2. 三套主力公式從低配到高配圍繞按條件去重計(jì)數(shù)我給一個(gè)結(jié)論沒有一套公式能覆蓋所有 Excel 版本選公式之前先確認(rèn)自己用的是經(jīng)典版還是 365/Microsoft 365或者 WPS 新版。下面三套方案分別適合不同場景建議按需取用而不是只背一個(gè)。2.1 低配方案COUNTIF 輔助列適合任何版本低版本 Excel 沒有 UNIQUE 這類數(shù)組原生函數(shù)我推薦用輔助列。雖然看起來多占一列但勝在穩(wěn)定、可調(diào)試。假設(shè)A列是客戶B列是地區(qū)要統(tǒng)計(jì)“上?！边@個(gè)條件下的去重客戶數(shù)。第一步在C2寫入IF(B2上海, COUNTIFS($A$2:$A2, A2, $B$2:$B2, 上海), 0)這是關(guān)鍵寫法。注意 $A$2:$A2 這種“鎖定區(qū)域起點(diǎn)終點(diǎn)不鎖”的寫法就是讓范圍隨著公式下拉不斷擴(kuò)大的“累計(jì)區(qū)域”。當(dāng)公式拉到第10行它統(tǒng)計(jì)的是 A2:A10 中與當(dāng)前客戶相同且地區(qū)為“上海”的出現(xiàn)次數(shù)。如果這是上海區(qū)這個(gè)客戶的第1次出現(xiàn)COUNTIFS 會(huì)返回1當(dāng)這一行出現(xiàn)第2次、第3次時(shí)返回2、3只有第一次顯示為1其他都大于1。然后在輔助列再加一層判斷讓重復(fù)記錄只在第1次出現(xiàn)的位置保留目標(biāo)值IF(COUNTIFS($A$2:$A2, A2, $B$2:$B2, 上海)1, 1, 0)最后對C列求和得到的就是上海區(qū)的不重復(fù)客戶數(shù)。這個(gè)思路看著土但它邏輯簡單而且可以隨時(shí)點(diǎn)開C列看哪一行被算過、哪一行沒被算排查問題非常方便。還有一種變體是用 SUMPRODUCT 直接實(shí)現(xiàn)類似邏輯不用輔助列但那種寫法一旦條件復(fù)雜調(diào)試成本就高了。我個(gè)人建議如果你只是偶爾做一次統(tǒng)計(jì)輔助列不丟人反而能減少“公式算不清楚”的焦慮。2.2 中配方案SUMPRODUCT COUNTIF單公式搞定不需要輔助列的話可以用經(jīng)典數(shù)組組合。以“統(tǒng)計(jì)上海地區(qū)不重復(fù)客戶”為例公式如下SUMPRODUCT((B2:B100上海) / COUNTIFS(A2:A100, A2:A100, B2:B100, B2:B100))拆解一下第一部分 (B2:B100上海) 生成一組由 TRUE/FALSE 組成的數(shù)組TRUE 在 Excel 計(jì)算時(shí)等于1FALSE 等于0。第二部分 COUNTIFS(A2:A100, A2:A100, B2:B100, B2:B100)是對每一行數(shù)據(jù)分別統(tǒng)計(jì)“在A列同樣客戶、B列同樣地區(qū)”的組合出現(xiàn)了多少次。兩者相除只有滿足“上?！睏l件的行才會(huì)參與計(jì)數(shù)同一客戶在上海區(qū)每出現(xiàn)一次都會(huì)被拆成 1/總次數(shù)加起來還是1。這樣就把“滿足條件”和“去重”兩個(gè)動(dòng)作疊在了一起。這個(gè)公式寫起來比別人分享的 SUMPRODUCT((B2:B100上海)*(1/COUNTIF(A2:A100,A2:A100))) 更嚴(yán)謹(jǐn)一點(diǎn)因?yàn)榈谝粋€(gè)版本沒有把“地區(qū)條件”放進(jìn) COUNTIF 里去重當(dāng)同一個(gè)客戶出現(xiàn)在上海和北京時(shí)容易誤傷。用 COUNTIFS 雙條件統(tǒng)計(jì)是穩(wěn)妥的。不過如果數(shù)據(jù)量超過幾千行SUMPRODUCT 這種數(shù)組運(yùn)算會(huì)讓 Excel 變慢使用時(shí)要心里有數(shù)。2.3 高配方案UNIQUE FILTER365和WPS新版強(qiáng)烈推薦如果你用的是 Office 365、Excel 2021 或 WPS 較新版本我強(qiáng)烈建議直接用動(dòng)態(tài)數(shù)組函數(shù)。統(tǒng)計(jì)上海不重復(fù)客戶寫起來像人話COUNTA(UNIQUE(FILTER(A2:A100, B2:B100上海)))這個(gè)公式的順序是FILTER 先把 A 列中滿足“B列等于上海”的客戶名單篩出來UNIQUE 再把這份名單里的重復(fù)項(xiàng)去掉COUNTA 數(shù)一下名單里有多少個(gè)非空值。三個(gè)函數(shù)各管一件事直來直去不用求倒數(shù)不用除零兜底。更高級一點(diǎn)的寫法是可以讓結(jié)果是動(dòng)態(tài)數(shù)組直接在單元格里溢出顯示所有不重復(fù)客戶列表UNIQUE(FILTER(A2:A100, B2:B100上海))當(dāng)源數(shù)據(jù)變化結(jié)果區(qū)域自動(dòng)更新比舊版函數(shù)方便太多。唯一要注意的是如果你的 Excel 版本不支持動(dòng)態(tài)數(shù)組那這個(gè)公式會(huì)報(bào)錯(cuò)所以別在別人電腦上亂演示。3. 多條件去重計(jì)數(shù)與組合場景實(shí)際業(yè)務(wù)遠(yuǎn)沒有“上?！币粋€(gè)條件這么簡單。很多報(bào)表要按月份、地區(qū)、渠道、品類同時(shí)過濾這時(shí)候公式需要再升級。理解核心邏輯后寫起來其實(shí)不難就是往數(shù)組條件里“加乘法”。3.1 多條件版本從單條件推廣到多重篩選需求場景變成統(tǒng)計(jì) 3月份、華東地區(qū)、線上渠道 的不重復(fù)客戶數(shù)。SUMPRODUCT 版本可以寫成SUMPRODUCT((C2:C1003月) * (D2:D100華東) * (E2:E100線上) / COUNTIFS(A2:A100, A2:A100, C2:C100, C2:C100, D2:D100, D2:D100, E2:E100, E2:E100))我把原數(shù)據(jù)列位置假設(shè)一下A客戶C月份D地區(qū)E渠道。這個(gè)公式和單條件版本結(jié)構(gòu)一樣只不過條件部分從一組變成了三組。COUNTIFS 里要把“客戶”和所有“條件列”都作為去重維度目的是判斷“同一個(gè)人、同一個(gè)月、同一個(gè)地區(qū)、同一個(gè)渠道”這一組合一共出現(xiàn)了多少次。分母計(jì)算出的就是完整組合的次數(shù)分子上的條件判斷確保我們只計(jì)數(shù)符合條件的行。UNIQUEFILTER 的多條件版本更直白COUNTA(UNIQUE(FILTER(A2:A100, (C2:C1003月) * (D2:D100華東) * (E2:E100線上))))注意 FILTER 的條件區(qū)域之間要用乘號連接不能用逗號。用乘號等于執(zhí)行“邏輯與”三個(gè)條件同時(shí)滿足才返回對應(yīng)行。有的朋友剛學(xué)動(dòng)態(tài)數(shù)組習(xí)慣寫 FILTER(A2:A100, C2:C1003月, D2:D100華東)那是錯(cuò)誤用法FILTER 的第二個(gè)參數(shù)只有一個(gè)條件數(shù)組多條件必須合成一個(gè)。3.2 按關(guān)鍵詞匹配的條件去重計(jì)數(shù)有時(shí)候條件不是“等于”而是“包含某個(gè)關(guān)鍵詞”比如統(tǒng)計(jì)“客戶名稱里包含‘科技’二字的客戶在華北地區(qū)有多少個(gè)不重復(fù)”。這種需求本質(zhì)是把精確匹配改成模糊匹配核心函數(shù)換成 ISNUMBER SEARCH/FIND。SUMPRODUCT 版本SUMPRODUCT((ISNUMBER(SEARCH(科技, A2:A100)) * (B2:B100華北)) / COUNTIFS(A2:A100, A2:A100, B2:B100, B2:B100))SEARCH(科技, A2:A100) 會(huì)在每個(gè)客戶名稱中查找“科技”找得到就返回一個(gè)位置數(shù)字找不到返回錯(cuò)誤值。ISNUMBER 再把“是數(shù)字”的變成 TRUE這樣“包含關(guān)鍵詞”的條件就轉(zhuǎn)成了邏輯數(shù)組。這里我特意用 SEARCH 而不是 FIND因?yàn)?SEARCH 不區(qū)分大小寫且支持通配符對中文來說兩者差不多但 SEARCH 對英文大小寫更友好。UNIQUEFILTER 版本COUNTA(UNIQUE(FILTER(A2:A100, ISNUMBER(SEARCH(科技, A2:A100)) * (B2:B100華北))))FILTER 的條件參數(shù)支持?jǐn)?shù)組運(yùn)算ISNUMBER(SEARCH(...)) 在 FILTER 里能正常使用返回的也是邏輯值數(shù)組。我在實(shí)際處理客戶名、產(chǎn)品名時(shí)這類“包含匹配”需求非常多。唯一要注意的是 SEARCH 支持通配符如果客戶名里有星號、問號這些字符可能會(huì)誤匹配需要轉(zhuǎn)義。3.3 數(shù)值區(qū)間和日期區(qū)間條件訂單金額、銷售日期也是高頻條件。比如統(tǒng)計(jì)“3月份、訂單金額大于500元的不重復(fù)客戶數(shù)”。這里重點(diǎn)是日期條件的寫法。SUMPRODUCT 版本SUMPRODUCT((TEXT(C2:C100,YYYY-MM)2024-03) * (D2:D100500) / COUNTIFS(A2:A100, A2:A100, C2:C100, C2:C100))我把列再換一下A客戶C日期D金額。TEXT(C2:C100,YYYY-MM) 的作用是把日期統(tǒng)一轉(zhuǎn)成“年-月”格式然后和“2024-03”比較。有些朋友習(xí)慣用 MONTH 函數(shù)判斷月份但在跨年時(shí)會(huì)出問題比如 2024-03 和 2025-03 的 MONTH 都是3。用 TEXT 或者直接用 DATE 區(qū)間判斷更穩(wěn)妥。用 UNIQUEFILTER 時(shí)寫日期區(qū)間建議用“開始日期日期結(jié)束日期”的邏輯COUNTA(UNIQUE(FILTER(A2:A100, (C2:C100DATE(2024,3,1)) * (C2:C100DATE(2024,3,31)) * (D2:D100500))))日期不能用“2024/3/1”這種文本直接比較除非你確認(rèn)單元格是日期格式。用 DATE 函數(shù)生成標(biāo)準(zhǔn)日期值能避免很多隱性 bug。4. 新手常踩的坑和排查實(shí)錄好公式寫出來只是第一步真正讓人頭大的是公式明明看著對結(jié)果卻不對。這里我把多年處理 Excel 表格時(shí)踩過和見過的高頻問題整理一下。這些問題網(wǎng)上散落在各種帖子評論區(qū)里我自己也逐一試過解法。4.1 公式下拉失效、結(jié)果不正確多半是數(shù)組公式或動(dòng)態(tài)數(shù)組的問題有朋友反饋“office2019 excel 公式下拉失效”或者低版本里寫完 SUMPRODUCT 公式后向下填充發(fā)現(xiàn)每個(gè)格子都返回一樣的結(jié)果。這個(gè)現(xiàn)象通常是兩個(gè)原因一個(gè)是當(dāng)前表格手動(dòng)計(jì)算模式。Excel 如果設(shè)置了“手動(dòng)計(jì)算”公式下拉后不會(huì)自動(dòng)重算按 F9 強(qiáng)制重算或者切換成自動(dòng)計(jì)算就好了。很多人不知道這一點(diǎn)以為公式壞了先把“公式”選項(xiàng)卡里“計(jì)算選項(xiàng)”改成“自動(dòng)”再檢查問題。另一個(gè)原因更隱蔽低版本里的公式其實(shí)是數(shù)組公式需要按 CtrlShiftEnter 結(jié)束編輯不能直接回車。如果你直接在單元格里輸入 SUMPRODUCT(...) 然后回車Excel 老版本可能會(huì)把它當(dāng)普通公式對待結(jié)果就亂了。如果是 365 或者 WPS 新版動(dòng)態(tài)數(shù)組函數(shù)不需要專門按三鍵但舊版必須用數(shù)組形式錄入。這個(gè)差異經(jīng)常讓人崩潰建議到任意一臺機(jī)器上寫公式前先確認(rèn)版本。4.2 計(jì)算結(jié)果少算或多算要檢查隱藏字符、空白單元格和重復(fù)表頭有一類特別常見的問題公式結(jié)果比實(shí)際少了幾個(gè)。排查時(shí)要先看數(shù)據(jù)源里有沒有不可見字符。比如從系統(tǒng)導(dǎo)出的客戶名稱可能帶了空格、換行符或全角空格看起來都是“上海”實(shí)際上一個(gè)是“上?！币粋€(gè)是“上海 ”多了空格COUNTIFS 就會(huì)認(rèn)為它們是兩個(gè)不同的值。解決辦法是用 TRIM 函數(shù)先清洗比如在輔助列加 TRIM(A2)或者用查找替換把空格去掉?？瞻讍卧褚矔?huì)干擾分母。COUNTIFS 對空白單元格的統(tǒng)計(jì)結(jié)果不同如果條件區(qū)域里有空行分母可能返回0導(dǎo)致公式出現(xiàn) #DIV/0! 錯(cuò)誤。常見的兜底寫法是SUMPRODUCT((B2:B100上海) / IF(COUNTIFS(A2:A100, A2:A100, B2:B100, B2:B100)0, 1, COUNTIFS(A2:A100, A2:A100, B2:B100, B2:B100)))不過這種寫法有點(diǎn)丑。我更推薦把數(shù)據(jù)區(qū)域精確設(shè)置為“有數(shù)據(jù)的區(qū)域”或者用 UNIQUEFILTER 版本因?yàn)?FILTER 對空行會(huì)自然過濾。另一個(gè)很坑的是表格里出現(xiàn)“總計(jì)”行或合并單元格。合并單元格會(huì)把 COUNTIFS 的行數(shù)判斷搞亂建議做數(shù)據(jù)前先取消合并單元格否則去重計(jì)數(shù)的結(jié)果很難查。4.3 復(fù)制粘貼失效、公式不更新可能是格式或外部鏈接問題很多人在工作中遇到過“excel ctrl v 失效”或者“excel粘貼快捷鍵用不了”的情況。這個(gè)跟公式本身沒直接關(guān)系但特別影響操作效率。我遇到過的可能原因有Excel 打開了多個(gè)工作簿當(dāng)前工作簿被某個(gè)加載項(xiàng)拖慢或者表格里存在大量條件格式、數(shù)據(jù)驗(yàn)證導(dǎo)致粘貼時(shí)卡死還有可能是剪貼板里被別的程序占用。換個(gè)思路可以先試“開始”選項(xiàng)卡里的“選擇性粘貼”或者清除全部格式后粘貼純文本。公式不更新還經(jīng)常因?yàn)殒溄恿送獠抗ぷ鞑?。如果你的表里用了外部引用比?SUMPRODUCT(...) 的源數(shù)據(jù)來自另一個(gè)沒打開的文件Excel 會(huì)在不重算或拒絕更新時(shí)給出緩存結(jié)果。處理這類問題建議把源數(shù)據(jù)導(dǎo)入到當(dāng)前表或者打開源文件后再重算。還有人問“excel導(dǎo)入數(shù)據(jù)庫”“python寫入excel”其實(shí)都是數(shù)據(jù)在 Excel 和外部系統(tǒng)之間搬運(yùn)的問題務(wù)必要處理好日期、數(shù)字格式再導(dǎo)防止導(dǎo)入后條件匹配不上。4.4 大數(shù)據(jù)的卡頓問題SUMPRODUCT 跑不動(dòng)怎么辦如果數(shù)據(jù)有50萬行SUMPRODUCT 這種數(shù)組公式會(huì)卡到懷疑人生。我個(gè)人實(shí)測幾萬行內(nèi)還行幾十萬行基本不建議用。這時(shí)候有兩條路一是先把數(shù)據(jù)清洗成“透視表可處理”的結(jié)構(gòu)用透視表“值字段設(shè)置為非重復(fù)計(jì)數(shù)”來替代公式二是縮減公式作用范圍不要整列 A:A只選 A1:A50000能快不少。如果你是在做報(bào)表自動(dòng)化建議把明細(xì)數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫或?qū)I(yè)分析工具Excel 只負(fù)責(zé)展示最終結(jié)果。在 Excel 自帶的“插入透視表”里如果數(shù)據(jù)模型加載過就可以在值字段設(shè)置里選“非重復(fù)計(jì)數(shù)”這本質(zhì)上是一種軟件級去重計(jì)數(shù)不寫公式也能做到適合不想折騰數(shù)組公式的朋友。唯一要注意的是普通透視表默認(rèn)沒有“非重復(fù)計(jì)數(shù)”選項(xiàng)需要勾選“將此數(shù)據(jù)添加到數(shù)據(jù)模型”。5. 實(shí)戰(zhàn)案例月度訂單按客戶去重計(jì)數(shù)前面講了不少原理接下來走一遍完整案例。假設(shè)你是一家消費(fèi)品公司的銷售助理手里有一份3月份訂單明細(xì)表列結(jié)構(gòu)是訂單號、客戶名稱、所屬區(qū)域、訂單金額、訂單日期。領(lǐng)導(dǎo)要你統(tǒng)計(jì)“各區(qū)域在3月份分別有多少個(gè)不重復(fù)成交客戶”你需要在同一個(gè)報(bào)表里展示華東、華南、華北、西南這幾個(gè)區(qū)域的去重客戶數(shù)。5.1 數(shù)據(jù)整理與需求拆解先把源數(shù)據(jù)整理規(guī)范客戶名稱列不能有空格訂單日期最好是標(biāo)準(zhǔn)日期格式區(qū)域列必須統(tǒng)一名稱比如“華東”不要出現(xiàn)“華東部”“華東區(qū)”等別名。我習(xí)慣先把原始明細(xì)表復(fù)制一份到“數(shù)據(jù)清洗”工作表用 TRIM、TEXT、IFERROR 做一遍清洗然后在干凈數(shù)據(jù)上寫公式。這種“源數(shù)據(jù)不動(dòng)、副本處理”的思路能避免失誤后原始臺賬被破壞。需求拆解后不難發(fā)現(xiàn)這其實(shí)是一個(gè)“分組條件下的去重計(jì)數(shù)”本質(zhì)上不是單一條件而是“區(qū)域”字段的每個(gè)值分別作為條件。最偷懶的辦法有兩種一是用 Excel 透視表把“區(qū)域”拖到行區(qū)域把“客戶名稱”拖到值區(qū)域并設(shè)置為“非重復(fù)計(jì)數(shù)”二是寫動(dòng)態(tài)數(shù)組公式把區(qū)域列表和去重計(jì)數(shù)一次性全部算出來。5.2 動(dòng)態(tài)數(shù)組公式直接輸出各區(qū)域不重復(fù)客戶數(shù)如果你的 Excel 支持動(dòng)態(tài)數(shù)組可以用 UNIQUEFILTER 配合 BYROW 之類的數(shù)組函數(shù)甚至可以同時(shí)輸出區(qū)域名和計(jì)數(shù)。不過 BYROW 比較復(fù)雜我推薦更直觀的組合先用 UNIQUE 提取區(qū)域列表再用每個(gè)區(qū)域作為條件分別計(jì)數(shù)。比如在 H2 輸入U(xiǎn)NIQUE(B2:B100)這會(huì)自動(dòng)列出所有不重復(fù)區(qū)域。然后在 I2 輸入COUNTA(UNIQUE(FILTER($A$2:$A$100, $B$2:$B$100H2)))下拉填充或者讓 Excel 自動(dòng)擴(kuò)展區(qū)域新版支持 H2# 這種引用方式。這里的 A2:A100 是客戶名稱B2:B100 是區(qū)域。當(dāng) H2 是“華東”時(shí)公式先篩選出所有“華東”的客戶再去重計(jì)數(shù)。由于 H2 每種區(qū)域只有一個(gè)值這個(gè)公式拉下去就能得到每個(gè)區(qū)域的去重客戶數(shù)。如果你的 Excel 支持 VSTACK/HSTACK還可以把區(qū)域列表和計(jì)數(shù)結(jié)果合成一張自動(dòng)更新的報(bào)表不過普通用途沒必要上這么高的復(fù)雜度。低版本用戶就在前面說的輔助列方案上操作加一列“首次出現(xiàn)標(biāo)記”用 COUNTIFS 判斷某個(gè)客戶在對應(yīng)區(qū)域里是否第一次出現(xiàn)最后用 SUMIFS 按區(qū)域求和。SUMIFS 在這里不是做條件去重而是對“首次出現(xiàn)標(biāo)記”為1的行按區(qū)域求和所以它負(fù)責(zé)的是“區(qū)域分組匯總”去重邏輯由輔助列完成。這個(gè)思路邏輯鏈清晰也方便核對。5.3 結(jié)果解析與手動(dòng)核驗(yàn)公式算出來之后一定要做手動(dòng)核驗(yàn)我工作里吃過虧公式看著對結(jié)果錯(cuò)得離譜最后查了半天是區(qū)域名稱里有全角空格。核驗(yàn)方法很簡單選中華東區(qū)域的所有行復(fù)制客戶列到空白列用 Excel“數(shù)據(jù)—?jiǎng)h除重復(fù)值”功能查看剩余行數(shù)和公式結(jié)果對比。如果兩邊一致說明公式正確不一致就順著輔助列逐行檢查。這里分享一個(gè)“SUMIFS 和條件去重計(jì)數(shù)結(jié)合”的經(jīng)驗(yàn)如果報(bào)表還要帶上銷售額同一個(gè)公式里可以同時(shí)算出去重客戶數(shù)和總金額?？偨痤~直接用 SUMIFS 按區(qū)域和日期區(qū)間求和不需要去重去重客戶數(shù)用上面公式。兩個(gè)指標(biāo)放在同一行最終效果是一張“區(qū)域維度成交概覽表”這在實(shí)際工作中非常受歡迎。6. 我的幾點(diǎn)實(shí)操心得最后不寫教科書式的總結(jié)就分享幾個(gè)我在實(shí)際工作中反復(fù)被驗(yàn)證過的體會(huì)。這些經(jīng)驗(yàn)不少是踩坑換來的希望你能少走幾步。6.1 公式選型要根據(jù) Excel 版本走不要盲目追求高配我現(xiàn)在寫表格前一定會(huì)先確認(rèn)對方電腦里的 Excel 版本。如果對方是 WPS 2019 或 Office 2016就老老實(shí)實(shí)寫 SUMPRODUCT 或輔助列如果對方用的是 Microsoft 365 且開啟了動(dòng)態(tài)數(shù)組UNIQUEFILTER 的體驗(yàn)會(huì)遠(yuǎn)好于老公式。盲目教別人高配函數(shù)很可能在對方電腦上輸出 #NAME? 錯(cuò)誤。這一點(diǎn)說小是小說大能讓整張報(bào)表報(bào)廢。我的習(xí)慣是剛?cè)腴T的新公式先在自己電腦上驗(yàn)證再用最低兼容版本做一份“保底”公式兩邊都能跑才算完。6.2 輔助列不一定丑它是排查問題的好朋友很多教程把輔助列說得上不了臺面我反而覺得輔助列在業(yè)務(wù)報(bào)表中非常實(shí)用。它不僅讓公式邏輯透明還能在結(jié)果異常時(shí)快速定位輔助列哪一行不符合預(yù)期一眼就能看出來。我處理復(fù)雜需求時(shí)經(jīng)常先加兩列輔助列算清楚后再?zèng)Q定要不要把它們隱藏。有時(shí)候隱藏掉會(huì)讓報(bào)表美觀但打印時(shí)如果擔(dān)心領(lǐng)導(dǎo)看到亂七八糟的公式可以在“視圖”里取消勾選網(wǎng)格線輔助列放在報(bào)表右邊區(qū)域不打印出來就行。說到底干凈優(yōu)雅和實(shí)用穩(wěn)定并不矛盾業(yè)務(wù)處理講究“能跑明白”優(yōu)先。6.3 把去重邏輯封裝成模板一勞永逸如果你頻繁要做“按條件去重計(jì)數(shù)”建議做一張自己的模板表列好參數(shù)區(qū)、明細(xì)區(qū)、結(jié)果區(qū)公式提前寫好并預(yù)留一個(gè)“版本說明”。每次新數(shù)據(jù)進(jìn)來只需要把明細(xì)粘貼到指定區(qū)域結(jié)果區(qū)自動(dòng)更新。這個(gè)思路其實(shí)和我之前處理“excel處理框架”類需求很像把公式固定下來把變化留成參數(shù)而不是每次從零開始寫函數(shù)。時(shí)間一長你會(huì)發(fā)現(xiàn)去重計(jì)數(shù)只是一個(gè)小小的起點(diǎn)同樣的邏輯可以遷移到庫存盤點(diǎn)、會(huì)員統(tǒng)計(jì)、渠道分析等幾乎所有場景里。就我個(gè)人的使用習(xí)慣而言“按條件去重計(jì)數(shù)”并不是某個(gè)高深函數(shù)的炫技現(xiàn)場而是 Excel 里最考驗(yàn)邏輯拆解的統(tǒng)計(jì)場景之一。你只要想清楚“哪一列要被去重、哪一列是條件、重復(fù)計(jì)數(shù)按什么組合判斷”這三件事公式就只是順手的事。希望這篇內(nèi)容能幫你把表格里的“重復(fù)項(xiàng)”馴服別再被領(lǐng)導(dǎo)追問“這客戶數(shù)到底哪來的”時(shí)手忙腳亂。