按班级总分的前90%计算各门学科的平均分

panda306

CheryBTL 发表于 2012-3-3 12:01
=AVERAGE(LARGE(IF($B$2:$B$2650=G2,$F$2:$F$2650),ROW(INDIRECT("1:"&INT(COUNT(0/($B$2:$B$2650=G2))*9 ...

这是求各科前90%学生的平均分，不是总分前90%学生的平均分。需增加一列辅助列，总分+(1/row())%%,以该列为条件之一进行计算
=AVERAGE(IF((汇总!$P$3:$P$500>=LARGE(IF(汇总!$A$3:$A$500=设置!A$5,汇总!$P$3:$P$500),ROUND(COUNTIF(汇总!$A$3:$A$500,设置!A$5)*0.9,0)))*(汇总!$A$3:$A$500=设置!A$5),汇总!$D$3:$D$500))
公式中汇总!$P$3:$P$500即为辅助列，汇总!$A$3:$A$500为班级所在的列，设置!A$5为某个班级，汇总!$D$3:$D$500为语文科成绩所在的列。

高个子

1. =AVERAGE(MOD(LARGE(IF($B$2:$B$2621=$G3,$F$2:$F$2621/1%%%+C$2:C$2621),ROW(INDIRECT("1:"&ROUND(COUNTIF($B:$B,$G3)*90%,)))),1000))

复制代码

不加辅助列可以这么做，记得三键

一杯绿茶来了

=AVERAGE(LARGE(IF($B$2:$B$2621=$B2,C$2:C$2621),ROW(INDIRECT("1:"&ROUND(COUNTIF($B:$B,$B2)*0.9,)))))

ericxzhou

=LET(a,DROP(REDUCE("",UNIQUE(B2:B2621),LAMBDA(x,y,VSTACK(x,LET(a,FILTER(C2:F2621,B2:B2621=y),TAKE(SORT(a,4,-1),ROWS(a)*0.9))))),1),VSTACK(C1:E1,HSTACK(AVERAGE(TAKE(a,,1)),AVERAGE(CHOOSECOLS(a,2)),AVERAGE(CHOOSECOLS(a,3)))))