Excel中MAX函数返回0问题及多列逻辑赋值需求解决

需求规则 #

• 若A+B组合唯一，C列赋值为1；
• 若A+B组合重复，C列与该组合之前的赋值相同；
• 若A列值相同但B列值不同，C列在同A列的最大赋值基础上+1；
• 全新的A+B组合，C列重新赋值为1。

数据示例 #

A               B   C   D       
BK-8811436091   57  1   Unique      
BK-8811436091   57  1   Duplicate       
BK-8811436091   57  1   Duplicate       
BK-8811436091   58  1   Unique      
BK-8811436091   57  1   Duplicate       
BK-8811436091   59  1   Unique      
BK-8811436091   57  1   Duplicate       
BK-8811436091   58  1   Duplicate       
BK-8811436092   54  1   Unique      
BK-8811436092   56  1   Unique      
BK-8811436092   58  1   Unique      
BK-8811436092   57  1   Unique      
BK-8811436091   57  1   Duplicate       
BK-8811436091   58  1   Duplicate       
BK-8811436092   57  1   Duplicate       

原公式错误原因 #

1. 数组运算未正确执行：公式中MAX(IF($A$2:A2=A2;$C$2:C2))属于数组逻辑，但未按数组公式要求（旧版Excel需按Ctrl+Shift+Enter输入）操作，导致MAX无法正确统计符合条件的C列最大值，返回0。
2. 逻辑分支重复且语法错误：重复判断D2="unique"的条件，且第三分支中MAX(IF($A$2:A2=A2;$C$2:C2)+1)的语法错误，应该将+1放在MAX函数外部，否则会对每个IF结果加1后再取最大值，违背需求逻辑。
3. 依赖相邻行判断不可靠：用A2&B2<>A1&B1判断新组合，当同一组合非连续出现时（如示例中BK-8811436091+57被其他组合打断），无法正确识别重复组合。

解决方案 #

方法1：兼容所有Excel版本（需数组输入） #

在C2单元格输入以下公式，按Ctrl+Shift+Enter完成输入后下拉填充：

=IF(COUNTIF($A$2:A2&$B$2:B2,A2&B2)>1,LOOKUP(2,1/($A$1:A1&$B$1:B1=A2&B2),$C$1:C1),MAX(IF($A$1:A1=A2,$C$1:C1,0))+1)

逻辑说明：

• 先通过COUNTIF判断当前A+B组合是否已出现，若重复则用LOOKUP反向查找该组合上一次的C值复用；
• 若为新组合，计算当前A列已出现行的C列最大值并加1，首次出现的A列会返回0+1=1。

方法2：Excel 365/2021专属（无需数组输入） #

利用动态数组函数简化公式，直接在C2输入后下拉：

=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)>1,XLOOKUP(A2&B2,$A$1:A1&$B$1:B1,$C$1:C1,"",0,1),MAX(FILTER($C$1:C1,$A$1:A1=A2,"0"))+1)

逻辑说明：

• 用COUNTIFS精准判断A+B组合的重复情况，避免字符串拼接的潜在冲突；
• 重复组合时用XLOOKUP快速定位最近一次的C值；
• 新组合时用FILTER筛选同A列的C值取最大值，再加1得到新序号。

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