一表可以用公式变成表二的样式吗？

杨杨1991315

一表可以用公式变成表二的样式吗？

mykitami

=LET(a,TOCOL(C6:C30,1),b,SCAN(0,a,LAMBDA(x,y,IF(LEFT(y,2)="姓名",x+1,x))),REDUCE(F6:H6,UNIQUE(b),LAMBDA(x,y,LET(c,FILTER(a,b=y),VSTACK(x,IF(SEQUENCE(,3)=1,TEXTAFTER(FILTER(a,(b=y)*(LEFT(a,2)="计划")),":"),HSTACK("",TEXTAFTER(INDEX(c,1,1),":"),INDEX(c,2,1))))))))

祝洪忠-

求教.rar (11.02 KB, 下载次数: 4)

2024-6-17 21:11 上传

点击文件名下载附件

静听溪流

1. =SUBSTITUTE(SUBSTITUTE(UNIQUE(LET(a,TRANSPOSE(SCAN(,TRANSPOSE(HSTACK(C3:C27,IF(LEFT(C3:C27,2)="姓名",C3:C27,""),IF(LEFT(C3:C27,2)="姓名",C4:C28,""))),LAMBDA(x,y,IF(y="",x,y)))),FILTER(a,LEFT(CHOOSECOLS(a,1),4)="计划门店"))),"计划门店:",""),"姓名:","")

复制代码

leepoison

1. =VSTACK({"计划门店","姓名","电话"},SUBSTITUTE(TEXTSPLIT(CONCAT(DROP(REDUCE("",TEXTSPLIT(CONCAT(C6:C30),,"姓名",TRUE),LAMBDA(x,y,VSTACK(x,TRANSPOSE(REGEXP(y,"(?<=计划门店:)[一-龟]+"))&"+"&REGEXP(y,"[一-龟]+(?=\d+)")&"+"&REGEXP(y,"\d+")&"-"))),1)),"+","-",TRUE),"计划门店",""))

复制代码

boyuanoffice

1. =LET(tab,C6:C30,n,IF(tab="","",SCAN(0,tab,LAMBDA(x,y,IF(LEFT(y)="姓",x+1,x)))),DROP(REDUCE("",SEQUENCE(MAX(n)),LAMBDA(x,y,LET(a,TEXTAFTER(FILTER(tab,n=y),":",-1,,1),VSTACK(x,CHOOSE({1,2,3},DROP(a,2),INDEX(a,1),INDEX(a,2)))))),1))

复制代码

你是那块小饼干

1. =LET(T,SCAN(0,C6:C30,LAMBDA(X,Y,IF(ISNUMBER(FIND("姓名",Y)),X+1,X))),
   
2. _T,UNIQUE(T),
   
3. REDUCE({"计划门店","姓名","电话"},_T,LAMBDA(_X,_Y,LET(_M,FILTER(C6:C30,_Y=T,""),
   
4. _N,TEXTAFTER(TAKE(_M,1),":"),
   
5. _R,DROP(TAKE(_M,2),1),
   
6. _C,TEXTAFTER(DROP(_M,2),":"),
   
7. VSTACK(_X,IFNA(HSTACK(IFNA(HSTACK(_C,_N),_N),_R),_R)
   
8. )))))
   

复制代码