值班名单分列转置

ph378

请大侠来个公式，值班名单分列并转置（wps2019版以下 ）

良良

楼主下次应上传附件

D2=IFERROR(INDEX(A:A,SMALL(IF(LEN(B$2:B$5)-LEN(SUBSTITUTE(B$2:B$5,"、",))+1>=COLUMN(A:AE),ROW($2:$5)),ROW(A1))),"")，三键。
E2=IFERROR(TRIM(MID(SUBSTITUTE(VLOOKUP(D2,A:B,2,),"、",REPT(" ",50)),50*COUNTIF(D$2:D2,D2)-49,50)),"")

cnmlgb9998

1. =REDUCE(A1:B1,B2:B5,LAMBDA(m,n,VSTACK(m,DROP(LET(_a,TEXTSPLIT(n,"、",,TRUE),REDUCE("",_a,LAMBDA(x,y,VSTACK(x,HSTACK(OFFSET(n,,-1),y))))),1))))

复制代码

无关岁月

1. =LET(_a,A2:A5,_b,B2:B5,TEXTSPLIT(TEXTJOIN("/",,_a&"、"&SUBSTITUTE(_b,"、","/"&_a&"、")),"、","/"))

复制代码

hugeinfo

=DROP(REDUCE(A1:B1,A2:A5,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(y,TEXTSPLIT(OFFSET(y,,1),,"、")),y)))),1)

博爱风雨同舟

=REDUCE(A1:B1,B2:B5,LAMBDA(X,Y,IFNA(VSTACK(X,HSTACK(Y,TEXTSPLIT(OFFSET(Y,,1),,"、"))),Y)))

逐渐陌生