处理Excel报表时，你是不是也遇到过这种情况：同一份表格里，同一个客户名出现了好几次，但你需要按“第几次出现”给它们编个号，好让后续的统计能分清第一笔订单、第二笔订单、第三笔订单。或者你在做库存流水，同一个物料编码反复入库出库，你想把同编码的进出记录按时间顺序排个序号。这时候，Excel函数里那个最不起眼的COUNTIF就能派上大用场。
今天要聊的就是这样一个场景：名称相同，序号自动递增，也就是按重复值分组排序。这不是什么复杂的数组公式，也不涉及VBA宏，就是一个简单的函数嵌套用法，但它的应用范围极广——订单号补全、重复记录标记、一对多查询的数据准备、重复签到计数、批次分组……只要是“同一分类下的出现次数”需求，都能用它解决。适合刚接触Excel函数不久、想搞懂引用方式的新手，也适合天天处理表格但要提升效率的办公族。
我自己在项目里用这个套路给一个将近三万行的客户交易流水做过编号处理，整个过程从理解公式到跑完数据，顺手得跟喝口水一样。接下来我就把这个思路、公式原理、实操步骤、踩坑经验全部拆开讲。
1. 整体思路与方案选型：为什么一定要用COUNTIF
1.1 需求到底在说什么：从人工编号到自动编号
先还原一下真实业务。假设你手上有一张销售明细表，A列是客户名称，B列是成交日期，C列是金额。领导要你统计“每个客户的第1笔单、第2笔单、第3笔单分别贡献了多少”，或者要把“每位客户的第N次交易”提取出来做对比。如果没有一个“序号”字段，你根本没法定位“第2笔”是哪一行。
这个问题本质上可以拆成两个子问题：
- 第一个：怎么识别哪些行属于同一个客户？
- 第二个：怎么在同一个客户内部，根据出现的先后顺序，把编号自动排成1、2、3……
人工做法是排序、筛选、手填序号，但数据一旦上千行就废了，而且下次数据一更新又得重来。所以我们需要一个公式，能在新增数据后自动重新计算，把“第几次出现”的编号算出来。
顺着这个思路往下想，可行的方案其实有好几个，比如用COUNTIF、用SUMPRODUCT、用Power Query分组添加索引列。我最终选了COUNTIF，原因后面讲。
1.2 为什么COUNTIF是这个场景的最优解
COUNTIF这个函数的名字拆开来看就是COUNT（计数）+ IF（条件），它的作用是在一个区域里，数一数满足某个条件的单元格有多少个。它的官方语法是这样：
COUNTIF(条件区域, 实际条件)大多数人平时拿它来统计“学历一列里有几个本科”，或者“成绩大于90分的有几个”。但COUNTIF有一个非常特殊的用法，也是Excel函数进阶里极具价值的一招：用混合引用构建一个“逐渐扩大”的计数区域，然后向下填充公式。
比如公式：
=COUNTIF($A$2:A2, A2)按下回车之后，公式得到的数字是1，因为$A$2:A2这个区域里只有A2这一个单元格。但当你把公式往下拖到第3行时，公式会变成=COUNTIF($A$2:A3, A3)，区域扩大为A2到A3两个格子，于是如果A3和A2相同，计数结果就是2，如果不同就是1。这就是“相同名称序号自动递增”的核心原理。
相比其他方案，COUNTIF方案有几个明显优势：
- 公式短小，一个函数搞定，不需要辅助列，不依赖数组公式（不用按Ctrl+Shift+Enter）。
- 计算逻辑直观，引用方式弄清楚之后，自己就能灵活变形。
- 对Excel版本没有要求，无论是Excel 2010还是Microsoft 365，WPS Office同样适用。
- 数据源区域变化时，只需要调整下拉填充范围，维护成本低。
SUMPRODUCT也能实现类似效果，但公式写起来更复杂；Power Query虽然更适合做正式的数据清洗流程，但是如果只是临时加个编号列，显得“杀鸡用牛刀”，而且Power Query入门门槛略高，普通办公场景里没必要绕那么大一圈。
1.3 这个方案的适用边界，先跟你说清楚
COUNTIF方案的定位很清楚：解决“轻量级、一次性、快速处理”的编号问题。它的适用条件是：
- 数据列中“同名”的排列是有意义的，即同名的行是先后顺序排列的，或者你希望严格按照当前表格里的出现顺序来编号。
- 需要编号的“名称列”本身是文本或数字，且格式统一。
- 数据量在几万行以内，COUNTIF的计算性能足够应对；如果百万行级别的数据，就不太建议用这种逐行扩大的COUNTIF写法了，性能明显下降，那是Power Query或者数据库该上场的时候。
所以，这篇博文讲的方法更偏向日常办公中“快速、稳、准”的实用套路，而不是企业级大数据清洗方案。
2. 核心公式原理与关键细节：混合引用是最值得你吃透的点
2.1 绝对引用、相对引用、混合引用，三者的区别一次说清
很多用户用COUNTIF做序号自动递增时，公式写完拖下去发现结果全变成1，或者全变成同一个数字，问题基本都出在引用方式上。这里我把引用规则掰开揉碎讲一遍。
- 相对引用：
A2。当你把公式往下拖一行，这个引用会自动变成A3；往右拖一列，会变成B2。 - 绝对引用：
$A$2。不管公式拖到哪里，它永远指向A2这个单元格。 - 混合引用：
$A2表示列固定、行可变；A$2表示行固定、列可变。
在我们的公式=COUNTIF($A$2:A2, A2)里，$A$2是行和列都固定的起点，A2是终点，同时它也是实际条件值。往下填充时，起点永远锁在A2，终点跟着当前行往下走，于是区域范围从1个格变成2个格、3个格……不断累积，最终实现“到现在为止出现了多少次”的计数。
这个“起点固定、终点跟随”的设计是整个公式的灵魂。我习惯把它叫作“台阶式计数区域”，就好像你在爬楼梯，每一层台阶都会把之前的台阶包括进来，而起点永远是大门口。
2.2 公式拆解：为什么条件就是A2自己
再讲一个容易混淆的点：COUNTIF的第二个参数是条件，这里的条件写的是A2，意思是“以此单元格的内容为条件”。它等价于在条件区里找“和A2值一样的单元格有多少个”，而不是像很多人潜意识里以为的“等于纯文本A2”。
所以当公式下拉到第5行时，条件自动变成A5，区域变成$A$2:A5，找的是“从第2行到第5行之间，和A5值相同的单元格有多少个”。因为区域是逐渐扩大的，所以如果第5行是某个名称的第四次出现，前面三行都是同一个名称，那计数结果就是4。
这个逻辑非常顺：它不是在整列里数总数，而是在“截至当前行”这个不断放大的窗口里数累计次数。理解到这一层，你就相当于掌握了整个技巧的核心。
2.3 多条件怎么处理：从COUNTIF升级到COUNTIFS
如果你的表格里光靠名称还不够，还需要在“名称相同”之外再叠加一个条件，比如同一客户同一年度内的第N笔订单，或者同一仓库同一物料的第N次进出，那么COUNTIF就不够用了，要用COUNTIFS。
COUNTIFS的公式结构是这样的：
=COUNTIFS($A$2:A2, A2, $B$2:B2, B2)意思是在“截至当前行”的A区域里数A列相同的个数，同时要求在“截至当前行”的B区域里B列也相同，两边同时成立才计数。用法上仍然是起点绝对引用、终点相对引用，往下拖即可。
我自己在一次库存台账整理中就用过这个变体：A列是物料编码，B列是仓库号，需要给“同一物料在同一仓库中的出入库记录”排流水号。对，就是上面这个公式，两百多行轻轻松松搞定。
2.4 关于COUNTIF的数据类型和通配符问题，提前扎个预防针
COUNTIF在匹配时不是单纯的一比一字符串匹配，它有几个隐藏规则会坑到初学者：
- 文本型数字和数值型数字可能被认为不同。比如A2单元格是文本格式的“1001”，而A5虽然是看起来一模一样的“1001”但它是数值格式，COUNTIF默认区分文本和数值。
- 空单元格不会被计数，但如果你条件里写的是
""，那就不一样了。 - 还有一个特别关键的：COUNTIF支持通配符。条件里如果出现
*或?，会被当成通配符处理，而不是普通字符。比如你要统计的名称是“A*B”，直接写公式会统计出所有“以A开头、以B结尾”的值，这时候就偏了。
关于通配符的坑，我在后面“常见问题”那一节会专门讲怎么绕过。
3. 实操过程与核心环节实现：从空白表格到完整编号
3.1 基础示例：三个步骤完成同名自动递增序号
无论你手里的数据是什么，操作节奏基本一样。我以一张客户订单表为例，A列是客户名称，现在要给每个客户的出现次数编号，编号放在E列。
第一步：确定公式起始单元格。假设数据从第2行开始，客户名称在A2，那么在E2单元格输入公式：
=COUNTIF($A$2:A2, A2)输入完之后，按回车，E2会显示1。
第二步：下拉填充公式。把鼠标移到E2单元格右下角，出现黑色实心十字光标后，双击——如果你的A列下方有连续数据，Excel会自动把公式填充到数据最后一行。如果双击无效，就手动拖拽到最后一行。
第三步：核对结果。看E列的分布情况。比如A2到A5都是“客户甲”，那E2到E5的顺序应该是1、2、3、4；如果A6换成了“客户乙”，E6应该重新从1开始。这个结果验证正确，说明公式已经生效。
这就是最基础的重复值排序自动编号。整个过程不到一分钟。
3.2 衍生玩法：给重复名称追加序号后缀
这种计数结果出来之后，最常见的用途之一就是生成“带序号的唯一标识”。比如A列是人名“张三”，但张三出现了多次，你需要一个不重复的字符串作为关联字段，拼接一下就好：
=A2 & "-" & COUNTIF($A$2:A2, A2)出来的结果会是这样：张三-1、张三-2、张三-3……这样的编号直接可以当作辅助列，用于后续的VLOOKUP精确匹配，或者用于Power Pivot建立关系。
我做合同台账时经常这样处理：同一家公司签了多份合同，用“公司名-序号”作为合同档案表的关联主键，比直接用公司名清爽得多，也不会导致VLOOKUP因为重复值而返回错误结果。
3.3 配合排序使用：先排序再编号，达到“按重复值分组排序”的效果
标题里提到的“重复值排序”，在实际操作中通常有两种理解。
一种理解是当前数据已经有序，只需要加编号区分第几次出现。上面说的步骤已经解决了。
另一种理解是，数据是杂乱排列的，比如同一客户名的行分散在不同位置，但你要先按客户名分组，再在每个组内按出现顺序排号。这时候的操作顺序是：先把整个表格按A列排序，可以再加关键字按日期排序，然后再用上面的公式添加序号。这样编号结果就变成了“按客户分组、组内按排序后的出现次数递增”的效果。
需要注意的是，COUNTIF方法编的是“当前表格排列下的出现序号”，如果同一客户名的行是分散的，结果会是1、1、2、2、1这种跳动的状态，而不是一组内整齐递增。避免这类问题的方法，就是在编号前先排一次序，保证每个名称的行是连续聚集的。
3.4 真实案例：用COUNTIF批量处理Excel中的采购批次编号
我之前处理过一个采购数据项目，数据有一万多行，分为物料编码、采购日期、供应商三列。当时的业务要求是：同一物料在同一供应商下的每一次采购，要有一个批次号，批次号从1开始递增。
如果只在物料编码维度上计数，显然不够，因为同一个物料可能从不同供应商采购。这时候就用COUNTIFS了，公式写成：
=COUNTIFS($A$2:A2, A2, $C$2:C2, C2)A列是物料编码，C列是供应商名称。在辅助列下拉填充后，很快每一条采购记录就有了“同一物料+同一供应商”维度下的批次序号。再配合TEXT函数把批次号格式化一下，比如：
=A2 & "-" & TEXT(COUNTIFS($A$2:A2,A2,$C$2:C2,C2), "000")出来的编号就是“MX1001-001”这种规整的样式，直接作为采购单号的一部分用。整个过程没有排序原始数据，直接公式算数，速度也很快。
这就是COUNTIF/COUNTIFS在处理场景里的典型价值：把“人眼数数”变成“公式自动数数”，准确率和速度都提升几个量级。
3.5 关于使用辅助列与直接公式的取舍经验
可能有人会问：既然要序号，能不能不搞辅助列，直接在某个单元格里一次性嵌套完成？
能做到，但我不建议。比如你要根据序号去匹配数据，公式会越写越长，嵌套越来越多，出错后排查的时间成本远高于拖一个辅助列的成本。辅助列不占什么空间，用完之后隐藏即可，但价值非常大：它把复杂逻辑拆成了简单步骤，每一步的结果都可以单独检查，这是我在实际项目里一直坚持的习惯。
尤其是当你需要在一个公式里同时做编号、拼接、查找时，如果没有辅助列，公式动辄几十个字符，维护起来相当痛苦。所以这里给个经验建议：编号阶段一定单独占一列，后续再基于这一列做二次加工，千万别把编号函数直接嵌套进VLOOKUP之类的函数里。
4. 常见问题与排查技巧实录：这些坑我帮你踩过了
4.1 为什么COUNTIF下拉后序号全部是1
这是新人最常见的问题。出现这种状况，几乎可以肯定是公式里的起点引用没有被锁定。比如你写的是：
=COUNTIF(A2:A2, A2)区域起点没有加$，下拉时变成了A3:A3、A4:A4，每个单元格都只统计自己一行，当然全是1。
解决办法就是改成$A$2:A2，确保起点固定。检查公式时，点开任意一个下拉后的单元格，看公式栏里的引用区域是不是逐渐变大的，这是最快定位问题的方法。
4.2 为什么相同名称中间隔了几行，序号不连续
我前面解释过，COUNTIF这种递增编号，本质依赖“当前表格的行顺序”。如果数据没有预先按名称分组，比如客户甲出现在第2行、第10行、第15行，中间第10行前插着客户乙，那么第15行计数时，从第2行到第15行这个区域里包含两次客户乙，但这不影响客户甲自己累计，因为条件是找和A15相同的值，客户乙不会干扰计数。倒不如说，COUNTIF的计数结果仍然能正确统计“截止到当前行，总共有多少次出现”，只是如果同一名称的行是分散的，序号看起来不是连续的1、2、3，而可能是1、1、2、1等跳动状态。
如果业务上要求组内连续递增，唯一的办法是先排序让相同名称聚在一起。注意，排序这个操作建议用“自定义排序”或者把整个数据区域选中再排，避免只排一列导致数据错位。
4.3 为什么统计结果把A1表头也算进去了
初学者很容易把表头行也纳入公式。比如表头在A1，数据从A2开始，但你在E1输入公式时写成了=COUNTIF($A$1:A1, A1)，这时候区域包含表头文字，计数结果就会多出一次。
解决办法就是让公式起始行和数据起始行保持一致。数据从第2行开始，公式起点就从$A$2开始，没有例外。
4.4 数字和文本格式混杂导致的计数错误
如果你要计数的列是一列“看起来像是数字的文本”，比如物料编码“01234”，有些单元格存的是文本格式带前导零，有些被Excel自动转成了数值型1234，COUNTIF在匹配时就会把这两类区分开，导致相同“码值”的编码被当成不同的值。
排查思路是：选中这一列，看Excel的“格式”设置，或者使用ISTEXT函数逐个检查。遇到这种问题，最好统一整个列的数据格式，全部设置成文本，再重新录入或粘贴一遍。
4.5 名称里带星号或问号时，COUNTIF统计异常
COUNTIF的通配符支持是很多人的盲区。假如某个客户名称是“ABC*”，你直接写：
=COUNTIF($A$2:A2, A2)当A2的值带*时，COUNTIF不会把它当作普通字符，而是会匹配所有以“ABC”开头的内容，统计结果远超预期。
解决办法是给条件拼接~转义符，把公式改成：
=COUNTIF($A$2:A2, SUBSTITUTE(A2, "*", "~*"))SUBSTITUTE函数先把名称里的星号替换成“~*”，让COUNTIF按普通字符匹配。问号的道理一样。如果名称里既可能带星号又带问号，就嵌套两个SUBSTITUTE。这个坑不常见，但一旦遇到，排查会非常费时，所以我专门列出来。
4.6 大数据量卡顿怎么办
COUNTIF这种逐行扩大的写法，在几千行时毫无压力，但如果达到几万行以上，下拉填充时会明显感觉卡。因为每一行都要重新扫描前面所有行，本质是O(n²)的计算复杂度。
如果你面临的表格有几万行，我的建议是换一条路，用Power Query的添加索引列功能，或者直接使用数据透视表的“重复项目标签”配合自定义列。与此相关的是Power Query的Table.AddRankColumn函数（在Excel中需要M公式编辑器），它可以把按姓名分组的行添加一个从1开始的排序编号，处理速度和稳定性都优于大型COUNTIF公式。
但对于绝大多数日常场景，COUNTIF仍然是最方便的方案，够用就好。
4.7 序号不跟随数据更新自动刷新
有些用户发现，自己改了前边的名称，后面的序号却没有变，怀疑公式出错了。这个情况基本可以断定是你不小心把公式粘贴成了值，或者工作表开启了手动计算模式。
检查Excel的“公式”选项卡里“计算选项”是不是“手动”，如果是，改为“自动”。粘贴时要注意，如果复制过来的“序号”在编辑栏里看不到公式，只看到数字，说明是纯值，这种情况只能重新生成公式。
5. 延伸思路：从COUNTIF到更高阶的“重复值排序”玩法
5.1 用COUNTIF配合IF实现同组内的首行标记
在很多重复值应用场景里，我们不一定要给所有行编号，而是只标记“这个名称的第一次出现”。这个需求在数据清洗中也特别频繁。实现起来非常巧妙：
=IF(COUNTIF($A$2:A2, A2) = 1, "是", "")这样只有在名称第一次出现的那一行返回“是”，其他重复行返回空白。这个标记可以直接用来做“按名称去重后的唯一清单”的筛选条件，比Excel自带的删除重复值功能多一个好处：它不删除数据，只是做标记。
5.2 用VLOOKUP或XLOOKUP反查第N次出现的记录
有了COUNTIF生成的序号，就可以做一件很多人想实现的操作：找出每个名称的第N次记录的内容。
具体做法是在另一个表里，把“名称+序号”拼接作为查找键，然后配合VLOOKUP的近似匹配思路；或者用XLOOKUP直接匹配拼接值。如果你用的是Excel 365，XLOOKUP的方式非常简单：
=XLOOKUP(A2 & "-" & 2, E:E, C:C)这条公式的意思是，从E列的“名称-序号”匹配条件值，取对应C列的数据。本质上，COUNTIF生成了辅助键，XLOOKUP负责查询，两者配合起来就能做一对多查询，效果堪比Index+Match组合。
就我个人的体验来说，这种“先COUNTIF编号再XLOOKUP取值”组合，比传统的INDEX+MATCH数组公式更容易理解，特别是数据量大的时候，排查逻辑也更清晰。
5.3 用Power Query替代COUNTIF做正式的数据清洗
如果你的工作流要求“每次拿到原始数据后自动完成编号并加载到表格”，那COUNTIF这种需要手动下拉填充的方式就不太合适了，因为每次数据更新都要重新拖公式。
Power Query的解法是：在“添加列”选项卡选择“自定义列”，用M函数或者Table.AddRankColumn。不过M函数写起来对初学者有一定门槛。Option 1是在查询编辑器中，先用“分组依据”按名称分组，再在每组内添加一个从1开始的索引列，最后展开，即可得到完整的分组编号。
也因此，Power Query适合“建立一套可重复使用的查询流程”；而COUNTIF适合“立刻、马上、给我一个编号”。两条技术路线不冲突，我通常先按数据量和工作频率做事前判断：偶尔一次，COUNTIF；天天更新，Power Query。
5.4 在Python和SQL里，同样思路的不同写法
可能有人会问：Excel之外，这种需求在其他工具有没有对应写法？有。
- 在SQL里，这个需求对应的是窗口函数
ROW_NUMBER() OVER (PARTITION BY 客户名称 ORDER BY 日期)，一句SQL就出来了，概念上和COUNTIF逐行累计是完全一致的。 - 在Python的Pandas里，对应的是
df.groupby('客户名称').cumcount() + 1。
理解了COUNTIF序号递增的本质是“分组内累计计数”，你会发现这套逻辑在数据库和数据分析工具里是通用的。Excel的COUNTIF是这套思想在表格里的最直观体现，也是很多人第一次接触“窗口函数”这个概念时的启蒙老师。
正因为这样，我觉得这个Excel技巧不仅能帮你在表格里处理数据，更能帮你建立一种跨工具的数据思维。你以后用SQL、用Pandas，遇到同类需求时，脑海里会立刻浮现出这个公式的运行逻辑，学习曲线会平滑很多。
6. 关于序号自动递增的二三心得
最后分享一点个人体会。很多人觉得Excel函数难，不是因为函数语法记不住，而是没理解“引用关系”这条暗线。COUNTIF做重复值排序的关键，不在于COUNTIF这个函数本身有多强大，而在于$A$2:A2这种“起点固定、终点移动”的区间构造方式。你掌握了这个，不光是COUNTIF，SUMIF、COUNTIFS、SUMPRODUCT里复制粘贴公式时，都能做出类似的分组累计效果。
比如你想对相同名称的金额做累计求和，公式就是：
=SUMIF($A$2:A2, A2, $C$2:C2)和COUNTIF的套路一模一样，只是第三个参数加了一个求和列。这种“一个思路通吃计数和求和”的能力，才是真正值得花时间去练的。
我在实际项目里踩过不少坑，总结下来最想提醒大家的还是两件事：第一，编号之前先看数据是否有序、格式是否统一，别等到编号生成了一大半才发现区域引错了；第二，公式里能加辅助列就加辅助列，别为了省一列空间搞出几百个字符的嵌套公式，省下来的时间和精力远比那一列宽度值钱。
这个技巧还可以往后延伸：用条件格式把同名的第1次出现标黄、第2次标蓝；或者用数据验证做一个下拉菜单，输入某个名称就能看到它每一次出现的明细位置。这些都是基于同一个公式框架去扩展的创意玩法，等你熟悉了底层逻辑，可以任意发挥。
