班级学科两个条件，筛选出各等级的姓名成绩，并按照成绩排序

欢乐马20221029

班级学科两个条件，筛选出各等级的姓名成绩，并按照成绩排序

欢乐马20221029

为了方便可以把15%，30%，30%，25%单列一行

leepoison

50个人15%是7.5，那是取7个还是8个

欢乐马20221029

leepoison 发表于 2024-12-17 21:16
50个人15%是7.5，那是取7个还是8个

有小数递补到上一等级

loirol

=TAKE(SORT(FILTER(OFFSET(B2:B200,,MATCH(O1,C1:I1,)),A2:A200=M1),,-1),ROUNDUP(COUNTIF(A2:A200,M1)*0.15,0))

hugeinfo

=LET(a,TOCOL(($A$2:$A$200=$M$1)*($C$1:$I$1=$O$1)*$C$2:$I$200),b,FILTER(a,a<>0),TAKE(SORT(b,1,-1),CEILING(ROWS(b)*0.15,1)))

zwzc

=LET(a,SORT(FILTER(HSTACK(B2:B200,FILTER(C2:I200,C1:I1=O1)),A2:A200=M1),2,-1),c,TAKE(DROP(REDUCE("",L2:O2,LAMBDA(x,y,LET(b,SEQUENCE(ROUNDUP(TEXTAFTER(TEXTBEFORE(y,")"),"(")*ROWS(a),0)),VSTACK(x,HSTACK(b,EXPAND(LEFT(y,1),b,,LEFT(y,1))))))),1),ROWS(a)),d,PIVOTBY(TAKE(c,,1),TAKE(c,,-1),a,HSTACK(T,N),,0,,0),DROP(d,2,1))

你是那块小饼干

1. =SORT(CHOOSECOLS(FILTER(B2:I200,A2:A200=M1),1,MATCH(O1,B1:I1,0)),2,1)

复制代码