层级处理

春日秋水

想要达到的效果，以"."为依据,如只有一个点，如果"."为1个则按序号+1处理，如果"."数量超过1，则继承上面的值，并在其后面加"."，值按序号+1处理，

山菊花

C3=INDEX(C$1:C2,LOOKUP(9^9,IF(MID(A$2:A2,LEN(A3)-LEN(SUBSTITUTE(A3,".","")),1)<>".",ROW(A$2:A2),"")))&"."&COUNTIF(A$2:A3,A3)
数组公式，按Ctrl+Shift+Enter结束。
结果与原效果不一致，可能理解规则有误。

象山海鲜

1. =LOOKUP("a",REPT(C$1:C1&".",ROW($1:1)>1)&1/FREQUENCY(IF(A$1:A2=A2,-ROW($1:2)),(LEN(A$1:A1)=LEN(A2)-1)*-ROW($1:1))^-1)

复制代码

xlllw203

1. =TEXTJOIN(".",,1,TEXT(COUNTIF(A$2:A2,REPT(".",COLUMN(A:J)-1)&"*")-COUNTIF(A$2:A2,REPT(".",COLUMN(A:J))&"*"),"0;;;"))

复制代码

累了。。。用这个函数的话，得先做降序排序，仅供参考

lchengjun

c2=IF(ISERROR(FIND(".",A2)),A2,INDEX($E$1:E1,XMATCH(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))-1,LEN($A$2:A2)-LEN(SUBSTITUTE($A$2:A2,".","")),,-1)+1)&"."&COUNTIF(INDEX($A$1:A1,XMATCH(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))-1,LEN($A$2:A2)-LEN(SUBSTITUTE($A$2:A2,".","")),,-1)+1):A2,A2))

1007890405

这题有点竞赛题的味道啊

moqixinfan

BOM表层次展开？

bbm000000

=IF(C1="效果",1,TEXT(,"[$"&SUBSTITUTE(C1,".","-",SUBSTITUTE(A2,".",))&"]")&"."&INDEX(EVALUATE("{"&SUBSTITUTE(C1,".",";")&";0}"),SUBSTITUTE(A2,".",)+1)+1)不知道为什么14行时1.3.2到15行就变成了1.3.3了

wintroop

模拟结果标蓝的部分有误吧？

C2=IF(ISERROR(FIND(".",A2)),A2&"",
IF(LEN(A2)>LEN(A1),C1&".1",
IF(LEN(A2)=LEN(A1),LEFT(C1,LEN(C1)-1)&RIGHT(C1,1)+1,
LEFT( XLOOKUP(A2,$A$1:A1,$C$1:C1,,,-1),LEN( XLOOKUP(A2,$A$1:A1,$C$1:C1,,,-1) )-1)&RIGHT( XLOOKUP(A2,$A$1:A1,$C$1:C1,,,-1),1)+1  )))

wintroop

为避免出现2位及以上的数字时出错，在10楼基础上再完善一下

C2=IF(ISERROR(FIND(".",A2)),A2&"",
IF(LEN(A2)>LEN(A1),C1&".1",
IF(LEN(A2)=LEN(A1),LEFT(C1,LEN(C1)-(LEN(C1)-FIND("@",SUBSTITUTE(C1,".","@",LEN(C1)-LEN(SUBSTITUTE(C1,".",))))))&RIGHT(C1,(LEN(C1)-FIND("@",SUBSTITUTE(C1,".","@",LEN(C1)-LEN(SUBSTITUTE(C1,".",))))))+1,
LEFT( XLOOKUP(A2,$A$1:A1,$C$1:C1,,,-1),LEN( XLOOKUP(A2,$A$1:A1,$C$1:C1,,,-1) )-(LEN(C1)-FIND("@",SUBSTITUTE(C1,".","@",LEN(C1)-LEN(SUBSTITUTE(C1,".",))))))&RIGHT( XLOOKUP(A2,$A$1:A1,$C$1:C1,,,-1),(LEN(C1)-FIND("@",SUBSTITUTE(C1,".","@",LEN(C1)-LEN(SUBSTITUTE(C1,".",))))))+1  )))