本帖最后由 edwin11891 于 2024-4-17 12:02 编辑
区别2楼的方法,将辅助列方法简单合并一下,公式实在有些臃长:
- =LET(ma,SCAN(0,A2:A12,LAMBDA(x,y,IF(y=OFFSET(y,-1,),x,x+1))),ms,LAMBDA(mm,SCAN(0,mm,LAMBDA(x,y,IF((OFFSET(y,,-1)=OFFSET(y,-1,-1))*(y=OFFSET(y,-1,)),x,IF(OFFSET(y,,-1)=OFFSET(y,-1,-1),x+1,IF(y=OFFSET(y,-1,),x,1)))))),mt,REDUCE("",B2:E2,LAMBDA(y,x,(HSTACK(y,ms(OFFSET(x,,,11)))))),mb,DROP(mt,,1),md,HSTACK(ma,mb),n,ROWS(md),MAP(SEQUENCE(n),LAMBDA(x,TEXTJOIN(".",,DROP(TAKE(md,x),x-1)))))
复制代码
最后一步MAP循环还可以用BYROW函数替代:
- =LET(ma,SCAN(0,A2:A12,LAMBDA(x,y,IF(y=OFFSET(y,-1,),x,x+1))),ms,LAMBDA(mm,SCAN(0,mm,LAMBDA(x,y,IF((OFFSET(y,,-1)=OFFSET(y,-1,-1))*(y=OFFSET(y,-1,)),x,IF(OFFSET(y,,-1)=OFFSET(y,-1,-1),x+1,IF(y=OFFSET(y,-1,),x,1)))))),mt,REDUCE("",B2:E2,LAMBDA(y,x,(HSTACK(y,ms(OFFSET(x,,,11)))))),mb,DROP(mt,,1),md,HSTACK(ma,mb),BYROW(md,LAMBDA(x,TEXTJOIN(".",,x))))
复制代码
|