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

wj65134

我的城市你来过 发表于 2024-7-22 10:34
20240722分列汇总转一维
=REDUCE($A$1:$C$1,$B$2:$B$4,LAMBDA(x,y,VSTACK(x,CHOOSE({1,2,3},OFFSET(y,,-1) ...

=REDUCE(A1:C1,A2:A4,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(y,TOCOL(REGEXP(OFFSET(y,,1),"\w+")),TOCOL(REGEXP(OFFSET(y,,2),"\d+"))),y))))

wj65134

我的城市你来过 发表于 2024-7-22 10:53
20240722练习3
=REDUCE($A$1:$B$1,UNIQUE($A$2:$A$30),LAMBDA(x,y,VSTACK(x,HSTACK(y,COUNTA(FILTER($B$2: ...

=REDUCE(A1:B1,UNIQUE(A2:A30),LAMBDA(x,y,VSTACK(x,HSTACK(y,COUNTIFS(A2:A30,y,B2:B30,"未*")/COUNTIF(A2:A30,y)))))

我的城市你来过

20240812练习
=REDUCE({"自连器","自管脚"},$A$2:$A$6,LAMBDA(x,y,IFNA(VSTACK(x,HSTACK(y,SEQUENCE(OFFSET(y,,2)-OFFSET(y,,1)+1,,OFFSET(y,,1)))),y)))

我的城市你来过

20240817记录
=REDUCE($A$1:$E$1,$A$2:$A$8,LAMBDA(x,y,IFNA(VSTACK(x,HSTACK(y,OFFSET(y,,1,,2),TEXT(WRAPROWS(TOCOL(OFFSET(y,,3,,6),3),2),"yyyy-mm-dd"))),HSTACK(y,OFFSET(y,,1,,2)))))

我的城市你来过

20240823练习1

=REDUCE($A$2:$B$2,$B$3:$B$12,LAMBDA(x,y,IFNA(VSTACK(x,HSTACK(OFFSET(y,,-1),IFS(y<=120,REPTARRAY(y,INT(y/30)),y<=510,REPTARRAY(y,INT(y/60)+1),y>510,REPTARRAY(y,INT(y/90)+1)))),OFFSET(y,,-1))))

我的城市你来过

20240823练习
=REDUCE($A$1:$D$1,$A$2:$A$6,LAMBDA(x,y,LET(a,1+LEN(OFFSET(y,,1))-LEN(SUBSTITUTE(OFFSET(y,,1),"、","")),VSTACK(x,HSTACK(REPTARRAY(y,a),TEXTSPLIT(OFFSET(y,,1),,"、"),REPTARRAY(OFFSET(y,,{2,3}),a))))))

我的城市你来过

20240827练习1
=REDUCE({"合骏","线号","数量"},A2:A25,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(y,TOCOL(FILTER($B$1:$D$1,OFFSET(y,,1,,3)>0),3),TOCOL(FILTER(OFFSET(y,,1,,3),OFFSET(y,,1,,3)>0),3)),y))))

我的城市你来过

=GROUPBY(LEFT($A$1:$A$29,1),HSTACK($B$1:$B$29,$C$1:$C$29),SUM,3)

我的城市你来过

=GROUPBY($A$1:$A$15,$C$1:$C$15,SUM,3,,,$B$1:$B$15<>0)

hugeinfo

我的城市你来过 发表于 2024-7-22 10:34
20240722分列汇总转一维
=REDUCE($A$1:$C$1,$B$2:$B$4,LAMBDA(x,y,VSTACK(x,CHOOSE({1,2,3},OFFSET(y,,-1) ...

=REDUCE(A1:C1,B2:B4,LAMBDA(x,y,VSTACK(x,CHOOSE({1,2,3},OFFSET(y,,-1),TEXTSPLIT(SUBSTITUTE(y,"\","+"),,"+"),TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(OFFSET(y,,1),"\","+"),"【",""),"】",""),,"+")))))