WPS数表单维多维重构练习题

我的城市你来过

问题Q：数据定位查找？

=LET(a,DROP(WRAPROWS(REDUCE(0,$C$1:$R$10,LAMBDA(x,y,VSTACK(x,TEXTJOIN(",",,UNIQUE(IF((y<>"")*(ROW($A$1:$A$10)-1)=$S$1:$S$10,y,"")))))),4),-1,1),b,BYROW(1*ISNUMBER(FIND(A15,a*1)),SUM),HSTACK(FILTER(a,0<b),FILTER(INT(SEQUENCE(COUNTA(a)/3,,1)/4),0<b)))


或者=LET(a,HSTACK(VSTACK($C$1:$E$10,$G$1:$I$10,$K$1:$M$10,$O$1:$Q$10),REPTARRAY($S$1:$S$10,4)),FILTER(a,BYROW(1*ISNUMBER(FIND(A15,a)),SUM)>0))

我的城市你来过

=IFERROR(BYCOL(IFNA(DROP(REDUCE("",LET(a,REGEXP(A1&1,"(\d+大?#)+\d?"),TOCOL(TEXTSPLIT(LEFT(REPT(a&",",RIGHT(a,1)),LEN(REPT(a&",",RIGHT(a,1)))-1),,","),3,FALSE)),LAMBDA(x,y,VSTACK(x,DROP(TEXTSPLIT(y,"#",,,,","),,-1)))),1),"")&"#",CONCAT),"")

我的城市你来过

问题Q：表格整合？

=LET(a,REDUCE(A1:D1,A2:A19,LAMBDA(x,y,VSTACK(x,IF(OFFSET(y,,1)={"王宝鼎";"何联贸";"李荣利"},OFFSET(y,,,,4),"")))),b,VSTACK(TAKE(a,1),SORT(DROP(a,1),1)),HSTACK(TEXT(TAKE(b,,1),"yyyy-mm-dd"),DROP(b,,1)))