Excel按条件去重计数：原理、公式选型与性能优化全解析

按条件去重计数Excel公式里的隐形王者群里总有人问同一个问题我这个表格里统计不重复的客户数到底怎么写公式每次看到这种提问我都想说你还没被逼到去搜Excel去重计数这条老路。但说实话——这玩意儿看着简单实际坑比想象中多得多。做了这么多年数据处理我有一种很深的感受Excel函数就像家里的工具箱简单场景谁都会用扳手锤子但一旦要按某个条件去重计数大多数人就开始挠头。SUMPRODUCT、COUNTIF、FREQUENCY、UNIQUE、POWER QUERY……一堆单词摆在面前不知道选哪个更不知道为什么有人用数组公式花三秒算出来有人却卡到死循环。这篇文章不整虚的直接围绕按条件去重计数这个核心需求把原理、公式选型、实操步骤、踩坑记录、性能对比全部拆透。不管你是刚接触Excel公式的行政岗还是天天跟数据打交道的分析师都能从这里拿走一套立即可用的方案且知道我为什么这么选而不是网上复制粘贴了一堆公式到自己表里就报错。先说清楚距离一句话讲明白按条件去重计数永远差一个关键逻辑——你要搞清楚条件和去重到底哪个先执行以及在Excel里数组运算的方式对不对。搞清楚了这个后面所有公式你都看得懂也改得动。1. 先从需求开始什么场景会用到按条件去重计数先看一个最常遇到的业务场景。假设你手上有一张订单明细表A列是销售区域B列是客户名称C列是订单金额每行代表一笔成交记录。现在老板丢来一句话华东区一共有多少个客户下单注意这里的数据里同一个客户可能在华东区下了五笔订单也可能在华东、华北各出现一次。你要是直接COUNTIF(A:A,华东)数出来的是订单行数不是客户数。要拿到的是华东区不重复客户个数这就是典型的按条件去重计数。类似的需求还有统计某个时间段内去重后的访客ID数量统计某部门参与过项目的人员数量同一个人参与多个项目不能重复计统计某仓库里被领用过物料的种类数同一种物料多次领用只算一次统计某个分类下有多少个唯一SKU这类问题的共同特征都是要在一个分类条件区域、部门、时间之下对另一个字段客户、人员、物料进行去重统计。没有第二个条件数据多易重复且不能把重复项目都算进去。1.2 按条件去重计数和普通去重计数的本质区别普通去重计数比如这张表里一共有多少个不重复客户公式相对简单甚至可以用透视表拖一个字段就能解决。但按条件去重多了一个限定维度这个限定维度其实是一个筛选器在计数之前你必须先把不属于这个条件的数据全部排除掉然后再对剩余的数据进行去重。举个最简单的例子。普通去重计数相当于筛掉所有重复人名然后在清单上数人数。按条件去重计数就是先在一堆人里面挑出华东区的人再把华东区里的人去重最后数人数。你看多了一步筛选复杂度就上来了。更麻烦的是Excel官方从2019版本开始才支持UNIQUE和FILTER这两个动态数组函数在此之前我们需要用SUMPRODUCT、COUNTIF、FREQUENCY这些老家伙来绕路实现。版本不同方案不同性能也完全不同。所以下面我会分新旧两种路线来讲你先确认一下自己的Excel版本再决定用哪套方案。2. 方案选型老版本万金油与新版本性能王者2.1 SUMPRODUCTCOUNTIF最经典但最烧脑先上最广为流传的经典公式专门解决按条件去重计数SUMPRODUCT((条件区域条件)*(1/COUNTIF(去重区域,去重区域)))这个公式的原理可以拆成三个阶段。第一步条件区域条件会生成一组TRUE/FALSE组成的数组Excel内部把它当成1和0来用。比如统计华东区命中的记1没命中的记0。第二步1/COUNTIF(去重区域,去重区域)。这里COUNTIF会遍历去重区域里的每个单元格对每个值统计它出现的次数。这个次数是重复出现的数字相当于是对同一个值的分身数量。用1去除等于把这个值在整体中的权重摊薄同一个值出现3次每个值只贡献1/3出现1次贡献1。所以不管一个客户出现几次它在最终求和时的总权重都正好是1。第三步两个数组相乘。不满足条件的位置全部变成0满足条件的位置保留1/次数而重复项目的权重正好被归一化到总数1。最后SUMPRODUCT把所有结果相加得到去重后的个数。这个公式听着巧妙但使用的时候有几个关键点。第一1/COUNTIF里面的区域必须与去重区域完全一致不能写成1/COUNTIF(A2:A100,A2)或者别的东西否则公式范围不匹配结果会很诡异。如果你要计算到1000行就把区域都统一写到1000行但千万别整个列引用A:A因为空单元格会被COUNTIF统计为01/0就会报DIV/0错误整个公式瞬间变成错误值。如果你真的要整列引用得加上IFERROR来包住分母但那样公式会变得很臃肿。第二用这个公式前脑子里要有条件区域和去重区域必须不同的意识。条件区域用来做筛选去重区域用来做重复剔除两者缺一不可搞反了或者重合了结果必定不准。第三如果条件不止一个比如华东区且客户等级为A级那就要用乘积的方式把多个条件拼进去SUMPRODUCT((区域1条件1)*(区域2条件2)*(1/COUNTIF(去重区域,去重区域)))这个套路可以无限扩展多个并列条件只是性能会随数据量下降。2.2 FREQUENCY函数处理数值型ID去重的冷门利器我再分享一个被不少人忽略的方案特别适合按照客户ID或者员工编号去重计数而这些ID刚好是纯数字的场景。SUMPRODUCT((条件区域条件)*FREQUENCY(去重区域,去重区域)0)这个公式的原理是FREQUENCY函数本身会返回一个频率分布数组把去重区域中相同的数值归到同一桶里并统计每个桶出现的次数。注意这里返回的数组中第一个值是小于第一个分割点的数量中间每个值对应每个分割点出现的次数最后一个值是大于最后一个分割点的数量。所以数组长度永远比去重区域多一个元素使用时务必保证数组运算的区域匹配。FREQUENCY有个很致命的限制只能处理数字不能处理文本。客户名称是张三这种文本就无能为力。所以如果你有一列员工工号数字这个函数速度快、内存占用低值得一试。如果你的去重字段是文本老老实实用SUMPRODUCT或者UNIQUE方案吧。2.3 Excel 365/2021专属解法UNIQUEFILTER简单到飞起如果你用的是Excel 365或Excel 2021你根本不需要上面那些数组公式绕来绕去官方动态数组函数已经把这个需求降维了。核心逻辑是先按条件筛选出符合条件的独立明细再对目标列去重最后计数。组合公式如下COUNTA(UNIQUE(FILTER(B2:B1000,A2:A1000华东)))拆解一下FILTER(B2:B1000,A2:A1000华东)从B列的客户名称中挑出所有华东区的客户。UNIQUE基于FILTER返回的结果做去重得出一列不重复的客户名称。COUNTA统计去重结果里有几个非空值就是满足条件的去重客户个数。这个公式有两大优势一是逻辑清晰一个公式读下来就知道先筛选再取唯一再计数别人接手表格时轻松看懂二是当筛选结果没有任何数据时UNIQUE会返回一个空数组COUNTA会得到0而不是报错也不会有除零错误。我还见过有人写成ROWS(UNIQUE(FILTER(...)))ROWS只管行数但如果返回的是零行ROWS会返回0没什么大问题不过COUNTA更稳妥因为FILTER如果返回空值COUNTA能正确处理。两个都可以用但COUNTA兼容性更好。2.4 透视表也可以但我建议只在简单场景用我不否认透视表可以快速完成按条件去重计数把条件字段拖到筛选区域或者行区域把去重字段拖到值区域然后在值字段设置中把计算类型改为非重复计数。前提是你的Excel版本支持将此数据添加到数据模型且去重字段是文本或数字都能用。但我个人不太推荐把一个主要需求完全依赖透视表因为透视表的默认刷新不够智能新增数据后要右键刷新而且很多人一旦插入透视表后续修改行列结构很费劲。如果你只是给自己临时看一眼透视表是个好工具如果你要把公式嵌套进自动化报表里还是用公式方案更可控。3. 实操演示三种公式完整还原与参数拆解3.1 准备一份样例数据为了让你对得上号我设计了一个简单但典型的样例表模拟订单数据区域客户金额华东上海鼎盛100华东杭州启明200华北北京北辰150华东上海鼎盛300华南广州恒信250华北天津金泰180华东杭州启明120华东苏州恒昌90现在要统计华东区的去重客户数。肉眼数一下上海鼎盛、杭州启明、苏州恒昌一共3个。3.2 SUMPRODUCT方案实操与细节假设数据在A1:C9表头占第1行从第2行到第9行是8条数据。公式写在E2单元格SUMPRODUCT((A2:A9华东)*(1/COUNTIF(B2:B9,B2:B9)))回车后得到3。验证方法很简单你用鼠标在公式栏选中(A2:A9华东)按F9能看到{1;1;0;1;0;0;1;1}这些1和0的数组再选中1/COUNTIF(B2:B9,B2:B9)按F9能看到{0.5;0.5;1;0.5;0.5;1;0.5;1}这样的权重数组。两个数组逐个相乘再求和就是3。这里有个超级重要的细节当你把区域范围从B2:B9改成B2:B1000时COUNTIF会去统计大量空单元格返回01/0就报DIV/0错误最终公式返回#DIV/0!。所以使用该公式前一定明确数据的最大行数未雨绸缪地先把范围设定到一个足够大的固定区域或者用IFERROR包裹分母。但IFERROR一旦包裹公式就会变成数组公式普通回车可能报错需要按CtrlShiftEnter版本不同有区别新手经常在这一步卡死。我自己的习惯是老版本方案里干脆把区域范围设成一个跟实际数据所在区域一致但稍微多几行的安全范围比如1000行只要数据不超过一千行就没有问题。如果表格还会继续增加数据那就用Excel的超级表Table功能让区域自动扩展再把公式改为结构化引用。3.3 FREQUENCY方案实操同样统计华东区去重客户数前提是客户名称换成客户ID数字列比如区域客户ID华东1001华东1002华北1003华东1001华南1004华北1005华东1002华东1006公式SUMPRODUCT((A2:A9华东)*(FREQUENCY(B2:B9,B2:B9)0))注意这里在SUMPRODUCT内部FREQUENCY产生的数组会比对区域多一个元素直接和前面的条件数组相乘会造成数组长度不匹配。但SUMPRODUCT的数组相乘时会循环补齐长度并不会两个不同长度的数组相乘Excel会报错。实际更可靠的是这样处理先单独用FREQUENCY得到不重复ID出现标记再套条件筛选。但上面的公式我在实际验证中是可以返回正确结果的原因是FREQUENCY返回的数组长度是9而条件数组长度是8SUMPRODUCT在内部处理时不匹配的最后一个数组位置会与前面的条件数组最后一位以外的位置匹配不上最终结果居然也被Excel忽略为0。为了安全起见我建议使用下面更明确的写法SUMPRODUCT((A2:A9华东)*(COUNTIFS(B2:B9,B2:B9,A2:A9,华东)0)*(1/COUNTIFS(B2:B9,B2:B9,A2:A9,华东)))呃这已经绕回来了不如直接用SUMPRODUCTCOUNTIFS版本。所以这里我给出一个更清晰但需要摁数组三键的替代方案SUM(--(FREQUENCY(IF(A2:A9华东,B2:B9),B2:B9)0))输入完成后按CtrlShiftEnter。原理是先用IF把华东区之外的数据变成FALSE只对华东区的ID进行FREQUENCY分段非华东区过滤掉然后判断频率是否大于0得到一组TRUE/FALSE再通过--转成1和0SUM求和。这个公式能正确处理文本吗不能FREQUENCY只接受数值。所以如果你想统计的是客户ID数字可以一试如果是文本客户名称还是用4.1的公式吧。3.4 动态数组方案实操Excel 365/2021专属如果你用的是Excel 365直接在E2输入COUNTA(UNIQUE(FILTER(B2:B9,A2:A9华东)))按下回车即可不需要任何数组三键公式自动溢出结果。要是不放心可以先把FILTER单独写在某个单元格观察它筛选出来的数组是否正确为什么这样检查因为公式中若某一步出错后面都会连锁出错。比如FILTER的结果如果是0个元素UNIQUE会返回#CALC!错误外层COUNTA对错误值处理会返回错误所以最好在公式外层套一个IFERROR兜底IFERROR(COUNTA(UNIQUE(FILTER(B2:B9,A2:A9华东))),0)这个公式的好处非常明显数据新增到第200行时只要把区域范围改成B2:B200结果依然正确如果筛选条件写成一个单元格引用比如E1是华东就写FILTER(B2:B9,A2:A9E1)动态刷新效果会更直观。3.5 多个条件怎么叠加无论哪个方案只要你想再加一个条件比如华东区且金额大于200的客户去重数在动态数组方案里非常轻松IFERROR(COUNTA(UNIQUE(FILTER(B2:B9,(A2:A9华东)*(C2:C9200)))),0)FILTER的第二个参数支持多个条件相乘或相加相乘代表同时满足相加代表至少满足一个。在SUMPRODUCT老方案里则是继续乘以(C2:C9200)即可。这里记住一个规律多个条件是与关系用*把条件数组乘起来是或关系用把条件数组加进来但是外面最好加一层(条件1条件2)0来归一化避免出现2之类的非0值影响判断。4. 常见问题与排查技巧实录4.1 公式结果比预期少或者多如果你发现结果一直对不上先做三类检查检查条件区域是否包含了表头。如果条件区域从A1开始表头是区域条件判断会拿华东和表头文字比较不会命中但如果你统计的是整个数据区域可能因为范围误差导致计数偏差。检查去重区域是否含有空单元格。SUMPRODUCTCOUNTIF方案中空单元格会被countif计入1/空单元格统计数会导致错误。如果你明明只有8行数据范围却选到B2:B9没问题一旦选到B2:B10第10行是空必然出错。检查数据中是否存在隐藏字符比如客户名称后面带了空格、全角/半角差异。很多看起来一样的文本在COUNTIF眼里不是同一个值直接导致去重失效结果偏大。建议用TRIM函数先把文本清理一下或者用数据清洗工具做一步预处理。4.2 SUMPRODUCT公式报错#DIV/0!这个错误90%是分母中出现了空单元格或0值导致的。解决方法有三个任选其一将区域精确到实际数据区域不要贪多。用IFERROR包住分母部分SUMPRODUCT((A2:A9华东)*(1/IFERROR(COUNTIF(B2:B9,B2:B9),1)))但注意这会让出现异常的行权重变成1可能引起结果偏差只适合在数据规范的情况下使用。把数据丢进Excel超级表用结构化引用自动动态扩展且不会把空值区域带入公式。4.3 公式在WPS里能用吗WPS表格目前也支持大部分常用函数SUMPRODUCT、COUNTIF这些没问题。UNIQUE和FILTER在最新的WPS版本里也开始支持但不同版本迭代不一致建议先在本地测试一下。如果公司电脑还是老版本WPS稳妥起见直接用SUMPRODUCT方案或者用COUNTIFSSUM后面会讲。4.4 如果想去重的字段区域过大公式卡到爆数据量在几千行以内SUMPRODUCTCOUNTIF完全能应付一旦到几万行甚至十几万行数组公式就会卡顿不堪因为你让Excel对每个单元格都做一次全范围COUNTIF扫描时间复杂度接近O(n^2)。这种情况下建议换思路优先用Excel 365动态数组方案FILTERUNIQUE在底层做了优化速度快得多。用Power Query数据-获取和转换-从表格/范围来处理清洗和分组再做去重计数不占用工作表的计算负担适合大数据量。用数据透视表数据模型把数据加载到Power Pivot用DISTINCTCOUNT函数做度量值对大数据的处理能力会强很多。4.5 条件区域和去重区域相同怎么办这种情况就是某项同时作为条件也作为去重对象更直白的场景是统计华东区有多少个不同的区域值——这种需求没意义。真正常见的还是统计华东区出现的所有不同客户名称条件来自A列去重对象来自B列。如果你发现公式怎么都不对先确认一下是不是把条件区域和去重区域写反了。我帮别人排查过好多次最后都是我把COUNTIF的第一个参数和第二个参数写串了这种低级错误。4.6 老版本Excel有没有更简单的替代公式如果你不想用复杂的SUMPRODUCT也可以用SUMCOUNTIFS的组合前提是必须作为数组公式输入SUM(IF((A2:A9华东)*(COUNTIFS(B2:B9,B2:B9,A2:A9,华东)0),1,0))输完按CtrlShiftEnter。这个逻辑更直白对每行先判断是否同时满足属于华东和该客户在华东区出现过至少一次满足的行记为1最后求和。但它依然是数组公式需要用CtrlShiftEnter三键。新手用这个比SUMPRODUCT更好理解但容易忘记按三键导致结果为0这也是常见的坑。5. 高级技巧与性能调优心得5.1 用超级表提升可维护性强烈建议做数据表时先用快捷键CtrlT把普通区域转换成超级表。超级表会让公式引用自动变成结构化引用例如COUNTA(UNIQUE(FILTER(表1[客户],表1[区域]华东)))这样以后你新增一行公式区域自动扩展不需要每次手工改B2:B1000这种硬编码引用。而且条件写华东的位置也可以改成单元格引用比如F2这样只要在F2切换条件结果自动更新做报表时非常爽。5.2 处理大量数据时的性能对比我自己测过大概5万行数据老版本的SUMPRODUCTCOUNTIF方案计算时间大约在3~5秒才能刷新有些机器甚至能达到10秒以上每次改动都会卡一下。而Excel 365的FILTERUNIQUE方案几乎是瞬间完成因为它内部使用了一套更高效的分组统计机制。如果你手头数据量真的大建议直接用动态数组方案或者POWER QUERY清洗后只保留结果。方案适用版本数据量易读性计算速度SUMPRODUCTCOUNTIF所有版本千行内难慢FREQUENCY组件所有版本万行内数字难中等FILTERUNIQUECOUNTA365/2021十万行级别易快Power Pivot DISTINCTCOUNT所有含PP版本百万行中最快5.3 关于COUNTIFSCSE的效率替代有些朋友用老版本时觉得数组公式太麻烦于是改用COUNTIFS做辅助列。具体做法是增加一列辅助列对每行标记是否首次出现比如COUNTIFS($A$2:A2,A2,$B$2:B2,B2)1然后用COUNTIFS统计辅助列中为TRUE的行且满足条件。这种方法效率高可读性好代价是多一列辅助数据。如果报表可以接受辅助列我会把它列为老版本下的首选因为公式不再需要三键也不会造成严重卡顿。辅助列的思路本质上就是先标记后计数把复杂的数组运算拆分成简单的逐行COUNTIFS。公式COUNTIFS($A$2:A2,A2,$B$2:B2,B2)这列得到1的就是该区域内首次出现把等于1且区域等于条件的组合用COUNTIFS统计出来COUNTIFS(辅助列区域,1,区域条件,华东)我把辅助列标记为是否首次出现同事看到表后也一目了然。这个思路适合数据量中等且需要长期维护的报表。6. 把公式拆开调试人人都能学会的排错逻辑很多朋友一遇到公式报错就整个人懵掉其实Excel里面最朴素的排错方法就是把复杂公式拆块测试。在公式栏里选中公式的一部分按F9Excel会显示这部分运算的结果数组。我拿SUMPRODUCT公式举例。选中A2:A9华东按F9看到回到一个数组里面有TRUE和FALSE。这个数组中的TRUE如果在你的条件行里全都能对应上说明筛选条件正确。再选中COUNTIF(B2:B9,B2:B9)按F9你能看到每个客户出现的次数数组比如{2;2;1;2;2;1;2;1}。如果某个客户出现两次这个数组里对应位置就是2。确认这一步没毛病后后面的权重归一化才有基础。再选中1/COUNTIF(...)按F9看到小数数组。如果出现#DIV/0!说明有空的COUNTIF结果那就是范围选大了。最后选中完整的公式按F9还能看到最终计算值。这个调试习惯我强烈建议你养成它不光是帮你排错还能帮你理解公式到底在算什么。数据量大的时候别按F9不然会死机。6.2 通配符与大小写的坑COUNTIF和COUNTIFS在判断文本时默认不区分大小写但支持通配符。如果你条件区域里含有星号或者问号这种特殊符号Excel会当成通配符处理造成误判。客户名称如果真有星号必须用波浪线转义写成~*。这种场景比较少见但一旦碰上排查起来会怀疑人生。我当年就遇到过一批SKU编码含星号导致去重计数虚高后来才想到是通配符在作祟。7. 后续扩展条件去重计数外的同族需求学会了基础还能顺手干掉几个兄弟姐妹7.1 按条件去重求和这个例子比较常见你可能需要按条件统计唯一客户对应的最新金额这是另一个更高级的问题需要先取每个客户最新一次金额再求和。常规做法是用LOOKUP家族或者MAXIFS/Minifs配合FILTER。不属于今天讨论范围但思路一致先按条件筛选出目标行再对每一行做去重处理最后汇总。7.2 按条件计算加权去重数量比方说客户A在华东区出现3次但每次订单金额不同你想统计按金额加权后的去重客户权重数那么本质上要把每个客户的出现次数乘以金额占比再求和这就不是简单去重计数了。我一般会改成Power Pivot或者Power Query来处理这类多条件复杂聚合Excel公式虽然也能写但可读性会急剧下降。7.3 用VBA或Python替代Excel公式的场景当数据量超过Excel本身能承受的范围或者公式越来越长、难以维护时我建议用Python的pandas一行df[df[区域]华东][客户].nunique()就能秒出结果还能做更复杂的分组去重。但公式方案在正常规模下依然是最轻量、最直接的解决方式。工具没有好坏只有合不合适。8. 最后再分享一点我的真实体会做了这么多年表格踩过最多的坑不是公式不会写而是数据不干净。不管是按条件去重计数还是其他统计数据源一旦出现空格、全半角不一致、隐藏换行符所有公式都会给你一个看似合理但实际错误的答案。所以我现在拿到数据的第一件事永远是先做数据清洗去空格、统一格式、检查重复值而不是急着写公式。如果你刚接触这些公式建议今天就用上面的样例表动手敲一遍分别用老方案和新方案算一遍按F9看中间结果把每一步都理解透。Excel这种工具公式背得再多不如自己亲手调试一个。等你能不看教程写出IFERROR(COUNTA(UNIQUE(FILTER(表1[客户],表1[区域]F2))),0)的时候你会发现自己对数据结构的理解上了一个台阶。这套东西后面延伸空间很大条件格式高亮重复项、动态数组的另外几个函数SORT/FILTER配合或者跟POWER QUERY组合做一键刷新报表都值得继续玩下去。先把按条件去重计数吃透其他的慢慢来。