Excel中基于层级列的依赖式层级编号实现方案求助

场景说明 #

根据B列的层级数值（1、2、3...），在A列生成对应层级的依赖式编号，例如：

• 层级1：1、2、3...
• 层级2：1.1、1.2、2.1...
• 层级3：1.1.1、1.1.2、2.1.1...

方案1：Excel 365/2021（支持动态数组） #

直接在A2单元格输入以下动态数组公式，公式会自动填充整列：

=SCAN("",B2:B10,LAMBDA(prev,cur,
    IF(cur=1,
        COUNTIF(B2:INDEX(B:B,ROW(cur)),1),
        TEXTJOIN(".",,TEXTBEFORE(prev,".",cur-1),COUNTIFS(B2:INDEX(B:B,ROW(cur)),cur,B1:INDEX(B:B,ROW(cur)-1),cur-1))
    )
))

公式逻辑： #

• 用SCAN遍历B列每一行的层级值
• 当层级为1时，统计当前行及以上所有层级1的数量，作为一级编号
• 当层级大于1时：
  1. 用TEXTBEFORE提取上一行编号中前cur-1级的内容
  2. 用COUNTIFS统计当前层级中，属于上一级下的序号
  3. 用TEXTJOIN拼接两部分得到完整层级编号

方案2：旧版Excel（无动态数组支持） #

在A2单元格输入以下公式，然后下拉填充至所有行：

=IF(B2=1,COUNTIF(B$2:B2,1),LEFT(A1,FIND("^",SUBSTITUTE(A1,".","^",B2-1))-1)&"."&COUNTIFS(B$2:B2,B2,B$1:B1,B2-1))

公式逻辑： #

• 层级为1时，统计当前行及以上层级1的数量
• 层级大于1时：
  1. 用SUBSTITUTE将上一行编号中第B2-1个.替换为特殊符号^
  2. 用FIND定位该符号位置，提取前半部分（即上一级的完整编号）
  3. 用COUNTIFS统计当前层级在对应上级下的序号，拼接后得到完整编号

注意事项 #

• 确保B列的层级是连续依赖的：层级2必须紧跟在层级1的行之后，层级3必须紧跟层级2的行之后，否则编号会出现逻辑错误
• 若B列存在空白行，需调整公式中的单元格范围（例如将B$2:B2改为B$2:INDEX(B:B,ROW())来排除空白行）

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