运动会项目进行统计

ahwz2018

ovxyz 发表于 2024-10-17 09:18
=LET(a,B3:I26,b,WRAPROWS(TOCOL(a,,1),4),c,HSTACK(TOCOL(REPTARRAY(B1:I1,ROWS(a)/4),,1),b),d,VSTACK(CH ...

厉害！大牛！

你是那块小饼干

1. =DROP(REDUCE("",TRANSPOSE(UNIQUE(B1:I1,1)),LAMBDA(X,Y,LET(T,WRAPROWS(FILTER(B3:I26,(Y=B1:I1)*(B2:I2="男子"),""),4),VSTACK(X,IFNA(HSTACK(Y,SORT(REPTARRAY(CHOOSECOLS(T,1,2),2)),TOCOL(CHOOSECOLS(T,3,4))),Y))))),1)

复制代码

mykitami

第一问
男子

=LET(a,DROP(TRANSPOSE(A1:I26),1),b,DROP(REDUCE("",SEQUENCE(ROWS(a)),LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(INDEX(TAKE(a,,2),y,),WRAPROWS(INDEX(DROP(a,,2),y,),4)),INDEX(TAKE(a,,2),y,))))),1),bb,FILTER(b,INDEX(b,,2)="男子"),DROP(REDUCE("",SEQUENCE(ROWS(bb)),LAMBDA(x,y,VSTACK(x,IF(SEQUENCE(,5)=5,TOCOL(INDEX(TAKE(bb,,-2),y,)),INDEX(TAKE(bb,,4),y,))))),1))


第二问
=LET(a,DROP(TRANSPOSE(A1:I26),1),b,DROP(REDUCE("",SEQUENCE(ROWS(a)),LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(INDEX(TAKE(a,,2),y,),WRAPROWS(INDEX(DROP(a,,2),y,),4)),INDEX(TAKE(a,,2),y,))))),1),c,DROP(REDUCE("",SEQUENCE(ROWS(b)),LAMBDA(x,y,VSTACK(x,IF(SEQUENCE(,5)=5,TOCOL(INDEX(TAKE(b,,-2),y,)),INDEX(TAKE(b,,4),y,))))),1),FILTER(c,TAKE(c,,-1)=100))