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

我的城市你来过

20240722练习-05
=FILTER($A$1:$A$14,COLUMN(A1)=SCAN(0,$A$1:$A$14,LAMBDA(x,y,IF(y<>"订单",x,x+1))))

hugeinfo

我的城市你来过 发表于 2024-7-22 15:06
20240722练习-05
=FILTER($A$1:$A$14,COLUMN(A1)=SCAN(0,$A$1:$A$14,LAMBDA(x,y,IF(y"订单",x,x+1))))

=FILTER($A$1:$A$14,COUNTIF(OFFSET($A$1,,,ROW($1:$14)),"订单")=COLUMN(A1))

我的城市你来过

20240723练习1
=REDUCE($A$1:$B$1,UNIQUE($A$2:$A$11),LAMBDA(x,y,VSTACK(x,HSTACK(y,SUM(FILTER($B$2:$F$11,$A$2:$A$11=y))))))

我的城市你来过

20240723练习2
=TEXTJOIN("、",,LET(m,FILTER($A$1:$B$23,ROW($A1)-1=INT((ROW($A$1:$A$23)-1)/6)),REDUCE({"",""},UNIQUE(TAKE(m,,1)),LAMBDA(x,y,VSTACK(x,HSTACK(y,SUM(FILTER(TAKE(m,,-1),TAKE(m,,1)=y))))))))

我的城市你来过

20240723练习3
=LOOKUP(ROW(A1)-1,VSTACK(0,SCAN(0,$B$2:$B$11,LAMBDA(x,y,x+y))),$A$2:$A$11)

=REDUCE(A1,B2:B11,LAMBDA(x,y,VSTACK(x,REPTARRAY(OFFSET(y,,-1),y))))

Holiday2022

我的城市你来过 发表于 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) ...

您好，choose（{1,2,3}在这里起什么作用呢？

我的城市你来过

Holiday2022 发表于 2024-7-24 10:57
您好，choose（{1,2,3}在这里起什么作用呢？

值与所需要的区域选择对应

我的城市你来过

20240724练习1
=LET(m,HSTACK(MAP(SCAN(,$A$3:$A$38,LAMBDA(x,y,IF(y<>"",y,x))),LAMBDA(x,REGEXP(SUBSTITUTE(x,CHAR(10),""),"(?=\[).+(?<=\])",2,""))),$B$3:$C$38),n,FILTER(m,CHOOSECOLS(m,2)="小计"),REDUCE({"名称","工程量"},UNIQUE(TAKE(n,,1)),LAMBDA(x,y,VSTACK(x,HSTACK(y,SUM(1*FILTER(TAKE(n,,-1),TAKE(n,,1)=y)))))))

我的城市你来过

20240725练习1
=LET(n,HSTACK($A$1:$A$23,REDUCE(TEXT($C$1:$G$1,"m月d日"),$A$2:$A$23,LAMBDA(x,y,VSTACK(x,IF(y<>"",BYCOL(OFFSET(y,,2,2,5),SUM),"#N/A"))))),FILTER(n,TAKE(n,,1)>0))

cnmlgb9998

我的城市你来过 发表于 2024-7-22 15:06
20240722练习-05
=FILTER($A$1:$A$14,COLUMN(A1)=SCAN(0,$A$1:$A$14,LAMBDA(x,y,IF(y"订单",x,x+1))))

练一下公式。