如何将多列表转换为一维表

我的城市你来过

=LET(a,DROP(REDUCE("",A3:A6,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(OFFSET(y,,,,3),WRAPROWS(OFFSET(y,,3,,6),2,"")),OFFSET(y,,,,3))))),1),FILTER(a,TAKE(a,,-1)>0))

xvfi

ww87725244 发表于 2024-12-22 11:09

非常感谢！

mykitami

=DROP(REDUCE("",D3:D6,LAMBDA(x,y,VSTACK(x,IF(SEQUENCE(,5)>3,HSTACK(OFFSET(y,,-1,,-3),WRAPROWS(TOCOL(OFFSET(y,,,,6),1),2)),OFFSET(y,,-1,,-3))))),1)

秃头发师

=REDUCE(A2:E2,SEQUENCE(4),
       LAMBDA(x,y,
       VSTACK(x,HSTACK(IF(SEQUENCE(COUNT(CHOOSEROWS(D3:I6,y)))<=COUNT(CHOOSEROWS(D3:I6,y)),CHOOSEROWS(A3:C6,y),""),
                       WRAPROWS(TOROW(CHOOSEROWS(D3:I6,y),3),2)))))

waternew

=DROP(REDUCE("",ROW(A3:C6)-2,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(INDEX($A$3:$C$6,y,),WRAPROWS(TOCOL(OFFSET($D$2:$I$2,y,),3),2)),INDEX(A3:C6,y,))))),1)

你是那块小饼干

1. =REDUCE("",B3:B6,LAMBDA(X,Y,VSTACK(X,IFNA(HSTACK(OFFSET(Y,,-1,,3),WRAPROWS(TOROW(OFFSET(Y,,2,,6),3),2)),OFFSET(Y,,-1,,3)))))

复制代码

wpsghost

=LET(arr,SORT(HSTACK(REPTARRAY(A3:C6,3),VSTACK(D3:E6,F3:G6,H3:I6)),{2,3}),FILTER(arr,TAKE(arr,,-1)<>0))

hugeinfo

=DROP(REDUCE("",A3:A6,LAMBDA(x,y,LET(a,WRAPROWS(TOCOL(OFFSET(y,,3,,6),3),2),VSTACK(x,HSTACK(IF(SEQUENCE(ROWS(a)),OFFSET(y,,,,3)),a))))),1)

cnmlgb9998

用递归函数 解决。

忙到飞起

=LET(a,WRAPROWS(TOCOL(D3:I6,1),2),HSTACK(DROP(REDUCE(1,BYROW(a,CONCAT),LAMBDA(x,y,VSTACK(x,FILTER(A3:C6,ISNUMBER(FIND(y,BYROW(D3:I6,CONCAT))))))),1),a))