基于Milestone/Task/Subtask的动态编号公式需求及报错解决

需求概述 #

需要构建公式驱动的动态编号列表，遵循以下层级规则：

• 里程碑（Milestone）：一级编号，如1、2
• 任务（Task）：二级编号，对应父里程碑，如1.1、1.2
• 子任务（Subtask）：三级编号，对应父任务，如1.1.1、1.1.2

基础示例 #

1     Milestone
1.1   Task
1.1.1 Subtask

复杂场景示例（需支持） #

1 Milestone
1.1  Task
1.2  Task
1.3  Task
1.3.1 Subtask
1.3.2 Subtask
1.3.3 Subtask
1.4   Task
2    Milestone

现有问题 #

尝试了两个适配编号超10场景的公式，但均触发#Value!错误：

公式1 #

=IF(B10="milestone",IF(A9<10,LEFT(A9)+1,LEFT(A9,2)+1),IF(B10="Task",IF(B9="Milestone",A9+0.1,IF(B9="subtask",IF(LEFT(A9,1)<=9,A9+0.1,LEFT(A9,4)+0.1),A9+0.1)),IF(B10="subtask",IF(B9="subTask",IF(NUMBERVALUE(LEFT(A9,1))<=9,LEFT(A9,4)&RIGHT(A9,1)+1,0),A9&"."&1))))

公式2 #

=IF(B39="milestone",IF(A38<10,LEFT(A38)+1,LEFT(A38,2)+1),IF(B39="Task",IF(B38="Milestone",A38+0.1,IF(B38="subtask",IF(LEFT(A38,1)<=9,A38+0.1,LEFT(A38,4)+0.1),A38+0.1)),IF(B39="subtask",IF(B38="Task",IF(A38<=9,LEFT(A38,4)&"."&1,LEFT(A38,5)&"."&1),LEFT(A38,5)&RIGHT(A38,1)+1))))

错误原因分析 #

1. 固定长度截取失效：依赖LEFT(Ax,N)固定截取字符，无法适配长度变化的编号（如10.1、100.2），导致数值转换错误
2. 类型混合运算错误：同时进行文本拼接（&）和数值运算（+），类型不匹配时触发#Value!
3. 硬编码格式依赖：预设编号长度规则，一旦编号层级或位数变化就会出错

解决方案：通用动态编号公式 #

以下公式支持任意位数的里程碑/任务/子任务编号，无#Value!错误，适配所有复杂场景：

公式（Excel适用，A列存编号，B列存类型，A1手动输入1、B1输入milestone） #

=IF(B2="milestone",
    COUNTIF($B$1:B2,"milestone"),
    IF(B2="task",
        TEXT(COUNTIF($B$1:B2,"milestone"),"0")&"."&(COUNTIF($B$1:B2,"task")-COUNTIF($B$1:INDEX(B:B,MATCH(TRUE,$B$1:B2="milestone",0)-1),"task")),
        IF(B2="subtask",
            LEFT(A1,FIND("*",SUBSTITUTE(A1,".","*",LEN(A1)-LEN(SUBSTITUTE(A1,".","")))))&(COUNTIF($B$1:B2,"subtask")-COUNTIF($B$1:INDEX(B:B,MATCH(TRUE,$B$1:B2="task",0)-1),"subtask")),
            ""
        )
    )
)

逻辑说明 #

• 里程碑：统计当前行及以上的里程碑总数，直接输出整数编号
• 任务：取当前里程碑编号，拼接当前里程碑下的任务序号（通过统计当前里程碑范围内的任务数量得到）
• 子任务：提取上一行的父任务/子任务前缀，拼接当前任务下的子任务序号

验证场景 #

• 里程碑超过10：如10 → 任务10.1 → 子任务10.1.1
• 多任务无 Subtask：1.1、1.2、1.3 连续生成
• 多 Subtask 后新增任务：1.3.3 后直接生成1.4

内容的提问来源于stack exchange，提问作者S.M.