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)))
自定义格式的学习帖子:
http://club.excelhome.net/thread-1201604-1-1.html?jdfwkey=gjemq1
标签：prevWBS,WBS,ISERROR,OFFSET,VALUE,生成,编号,FIND,SUBSTITUTE From： https://www.cnblogs.com/cloudhan/p/17020126.html