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

我的城市你来过

20240726练习01
=REDUCE($A$1:$G$1,UNIQUE($A$2:$A$10&$B$2:$B$10),LAMBDA(x,y,VSTACK(x,HSTACK(TEXTSPLIT(y,"cm"),BYCOL(FILTER($C$2:$G$10,$A$2:$A$10&$B$2:$B$10=y),SUM)))))

我的城市你来过

20240726练习2
=REDUCE(数据库!$F$1:$K$1,UNIQUE(BYROW(数据库!$F$2:$I$38,ARRAYTOTEXT)),LAMBDA(x,y,VSTACK(x,TEXTSPLIT(TEXTJOIN(", ",,y,BYCOL(FILTER(数据库!$J$2:$K$38,BYROW(数据库!$F$2:$I$38,ARRAYTOTEXT)=y),SUM)),", "))))

我的城市你来过

20240726练习01
=LET(n,DROP(REDUCE("",$A$2:$A$6,LAMBDA(x,y,VSTACK(x,BYROW(CHOOSE({1,2},TEXTJOIN(", ",FALSE,OFFSET(y,,,,4),OFFSET(y,,5,,3)),TEXTSPLIT(OFFSET(y,,4),,",")),ARRAYTOTEXT)))),1),VSTACK($A$1:$H$1,CHOOSECOLS(WRAPROWS(TEXTSPLIT(TEXTJOIN(", ",,n),","),8),{1,2,3,4,8,5,6,7})))

=VSTACK($A$1:$H$1,CHOOSECOLS(DROP(TEXTSPLIT(REDUCE("",$A$2:$A$6,LAMBDA(x,y,VSTACK(x,HSTACK(TEXTSPLIT(OFFSET(y,,4),,","),OFFSET(y,,,,8)&","&TOCOL(REGEXP(OFFSET(y,,4),"[^,]+")))))),","),1),{2,3,4,5,1,7,8,9}))

我的城市你来过

20240727练习01
=DROP(REDUCE("",$C$1:$I$1,LAMBDA(x,y,HSTACK(x,ROW($A$1:$A$30),TAKE(SORT(HSTACK($A$2:$B$100,OFFSET(y,1,,101)),3,-1),30)))),,1)

=DROP(REDUCE("",$C$1:$I$1,LAMBDA(x,y,HSTACK(x,TAKE(SORT(HSTACK(RANK(OFFSET(y,1,,100),OFFSET(y,1,,100)),$A$2:$B$101,OFFSET(y,1,,100)),4,-1),30)))),,1)

我的城市你来过

20240728练习01
=LET(c,SCAN($A$1:$B$1,$A$2:$A$184,LAMBDA(x,y,IF(y<>"",y,x))),b,$C$2:$C$184,REDUCE(A1:B1,UNIQUE(c),LAMBDA(x,y,VSTACK(x,HSTACK(y,SUM(FILTER(b,c=y)))))))

最爱咥面

过来看看么

我的城市你来过

20240729练习1
=LET(a,UNIQUE(A2:A11),b,UNIQUE(B2:B9),c,HSTACK(b,COUNTIF(B2:B9,b)),REPT(a,COUNTIF(A2:A11,a)-IFNA(MAP(a,LAMBDA(x,VLOOKUP(x,c,2,0))),0)))

我的城市你来过

20240803练习1
=REDUCE("",UNIQUE(TOCOL(YEAR($B$1:$V$1)&$A$2:$A$3,3)),LAMBDA(x,y,VSTACK(x,HSTACK(y,SUM(IF(YEAR($B$1:$V$1)&$A$2:$A$3=y,$B$2:$V$3,0))))))

我的城市你来过

20240807练习1
=LET(b,DROP(SUBSTITUTE(REDUCE("",$F$2:$J$2,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(OFFSET(y,-1,)&","&B3:B13,$C$3:$D$13,y,OFFSET(y,ROW($A$1:$A$11),)),y)))),",",""),1),VSTACK({"科目","门店","名称","二级","金额"},HSTACK(SCAN("",TAKE(b,,1),LAMBDA(x,y,IF(y<>"",y,x))),DROP(b,,1))))

我的城市你来过

20240807练习2
=REDUCE({"房号","类型","个数"},UNIQUE($A$1:$A$15&$B$1:$B$15),LAMBDA(x,y,LET(a,FILTER($B$1:$B$15,$A$1:$A$15&$B$1:$B$15=y),VSTACK(x,HSTACK(LEFT(y,7),UNIQUE(a),COUNTA(a))))))