Excel中按不同Order ID统计ID的累计出现次数问题

原公式的问题 #

你用的=COUNTIFS(A$2:A2,A2,B$2:B2,"<>"&B2)+1逻辑有误：它统计的是到当前行为止，同一ID下与当前Order ID不同的记录数再加1，这会把所有不同的Order ID都累加，导致同一个Order ID重复出现时计数错误（比如同一ID下的同一个Order ID第二次出现时，公式会错误地增加计数）。

正确解决方案 #

方案1：新版Excel（支持动态数组函数，如Excel 365/2021） #

使用以下公式（直接回车即可，无需数组输入）：

=MATCH(B2,UNIQUE(FILTER(B$2:B2,A$2:A2=A2)),0)

逻辑说明：

• FILTER(B$2:B2,A$2:A2=A2)：筛选出从第2行到当前行中，与当前行ID一致的所有Order ID
• UNIQUE(...)：提取这些Order ID中的唯一值，自动去重
• MATCH(B2,...0)：找到当前Order ID在唯一值列表中的位置，这个位置就是该ID下当前Order ID的累计出现序号（同一Order ID重复出现时，序号保持不变）

方案2：旧版Excel（不支持动态数组，需按Ctrl+Shift+Enter作为数组公式输入） #

使用以下数组公式：

=SUM(IF(A$2:A2=A2,1/COUNTIFS(A$2:A2,A2,B$2:B2,B$2:B2),0))

逻辑说明：

• COUNTIFS(A$2:A2,A2,B$2:B2,B$2:B2)：对每行统计同一ID下同一Order ID的出现次数
• 1/...：将同一ID同一Order ID的所有记录转换为1/出现次数，这样同一组记录的总和为1（比如某Order ID出现2次，每行就是0.5，总和为1）
• SUM(IF(...))：累加这些值，得到当前ID到当前行为止的唯一Order ID数量，也就是目标的累计计数

示例验证 #

假设数据如下：

两种方案都能得到符合预期的结果。

内容的提问来源于stack exchange，提问作者CodeBob