WBS自动生成编号

excel名称管理器中定义A列名称prevWBS,名称管理器中把A2单元引用改为=Sheet1!$A1；生成编号复制公式即可:

1 2 3...

=IF(ISERROR(VALUE(SUBSTITUTE(prevWBS,".",""))),"1",IF(ISERROR(FIND("`",SUBSTITUTE(prevWBS,".","`",1))),TEXT(VALUE(prevWBS)+1,"#"),TEXT(VALUE(LEFT(prevWBS,FIND("`",SUBSTITUTE(prevWBS,".","`",1))-1))+1,"#")))

1.1 ...

=IF(ISERROR(VALUE(SUBSTITUTE(prevWBS,".",""))),"0.1",IF(ISERROR(FIND("`",SUBSTITUTE(prevWBS,".","`",1))),prevWBS&".1",LEFT(prevWBS,FIND("`",SUBSTITUTE(prevWBS,".","`",1)))&IF(ISERROR(FIND("`",SUBSTITUTE(prevWBS,".","`",2))),VALUE(RIGHT(prevWBS,LEN(prevWBS)-FIND("`",SUBSTITUTE(prevWBS,".","`",1))))+1,VALUE(MID(prevWBS,FIND("`",SUBSTITUTE(prevWBS,".","`",1))+1,(FIND("`",SUBSTITUTE(prevWBS,".","`",2))-FIND("`",SUBSTITUTE(prevWBS,".","`",1))-1)))+1)))

1.1.1 ...

=IF(ISERROR(VALUE(SUBSTITUTE(prevWBS,".",""))),"0.0.1",IF(ISERROR(FIND("`",SUBSTITUTE(prevWBS,".","`",2))),prevWBS&".1",LEFT(prevWBS,FIND("`",SUBSTITUTE(prevWBS,".","`",2)))&IF(ISERROR(FIND("`",SUBSTITUTE(prevWBS,".","`",3))),VALUE(RIGHT(prevWBS,LEN(prevWBS)-FIND("`",SUBSTITUTE(prevWBS,".","`",2))))+1,VALUE(MID(prevWBS,FIND("`",SUBSTITUTE(prevWBS,".","`",2))+1,(FIND("`",SUBSTITUTE(prevWBS,".","`",3))-FIND("`",SUBSTITUTE(prevWBS,".","`",2))-1)))+1)))

1.1.1.1 ...

=IF(ISERROR(VALUE(SUBSTITUTE(prevWBS,".",""))),"0.0.0.1",IF(ISERROR(FIND("`",SUBSTITUTE(prevWBS,".","`",3))),prevWBS&".1",LEFT(prevWBS,FIND("`",SUBSTITUTE(prevWBS,".","`",3)))&IF(ISERROR(FIND("`",SUBSTITUTE(prevWBS,".","`",4))),VALUE(RIGHT(prevWBS,LEN(prevWBS)-FIND("`",SUBSTITUTE(prevWBS,".","`",3))))+1,VALUE(MID(prevWBS,FIND("`",SUBSTITUTE(prevWBS,".","`",3))+1,(FIND("`",SUBSTITUTE(prevWBS,".","`",4))-FIND("`",SUBSTITUTE(prevWBS,".","`",3))-1)))+1)))

这是另一个自动生成编号的公式，前一列写入编号级次，公式这一列可以生成正式的任务编号,级次可以无限；

级别	生成编号
1	1
2	1.1
3	1.1.1
4	1.1.1.1
1	2
2	2.1
...	...

=IF(A2="","",IF(A2>OFFSET(A2,-1,0,1,1),IF(OFFSET(B2,-1,0,1,1)="","1",OFFSET(B2,-1,0,1,1))&REPT(".1",A2-MAX(OFFSET(A2,-1,0,1,1),1)),IF(ISERROR(FIND(".",OFFSET(B2,-1,0,1,1))),REPT("1.",A2-1)&IFERROR(VALUE(OFFSET(B2,-1,0,1,1))+1,"1"),IF(A2=1,"",IFERROR(LEFT(OFFSET(B2,-1,0,1,1),FIND("^",SUBSTITUTE(OFFSET(B2,-1,0,1,1),".","^",A2-1))),""))&VALUE(TRIM(MID(SUBSTITUTE(OFFSET(B2,-1,0,1,1),".",REPT(" ",LEN(OFFSET(B2,-1,0,1,1)))),(A2-1)*LEN(OFFSET(B2,-1,0,1,1))+1,LEN(OFFSET(B2,-1,0,1,1)))))+1)))