问题情景
如下表格：
共18个班级，七百多名学生，要求统计每个班级 总分前30名的同学各科平均成绩 ， 如下图：
注意，并不是各科前30名，而是总分前30名 。假设某同学，语文满分，但是总分不在班级前30内 ， 他的语文成绩也不能统计在内 。
公式实现
1、计算总分这一步最容易，公式：=SUM(F2:L2)，如下图：
2、计算班级排名
在D2单元格输入公式：=SUMPRODUCT(($A$2:$A$710=A2)*($E$2:$E$710>E2)) 1 ， 确定，并向下填充，得到所有同学在本班内的名次，如下图：
该公式的结果：如果总会相同，名次则相同 。
公式解释参考：Excel | SUMPRODUCT函数实现两种特殊但又常用的排名
3、计算总分前30名各科平均分
在P2单元格输入公式：=AVERAGE(LARGE(($D$2:$D$710<=30)*($A$2:$A$710=$N3)*E$2:E$710,ROW($A$1:$A$30))) ， 以“Ctrl Shift Enter”三键组合结束公式输入，得1班前30名总分平均分 ， 公式向右、向下填充，得到每个班级 总分前30名的同学各科平均成绩，如下图：
公式解析
$D$2:$D$710<=30：【Excel | 统计总分前30名的各科平均成绩】所有同学的名次与30比较，如果在30以内则返回TRUE，否则返回FALSE，本部分返回709个TURE与FALSE组成的数组 。
$A$2:$A$710=$N3：
将班级与N3班级比较，如果相同，则返回TRUE，否则返回FALSE ， 本部分返回709个TURE与FALSE组成的数组 。
($D$2:$D$710<=30)*($A$2:$A$710=$N3)*E$2:E$710：
将前两步得到的两个数组与所有的总分相乘，如果满足名次在30名以内且班级与N3相同，则返回部分 ， 否则返回0 。本部分返回值是N3班级的前30名部分与0组成的709个数值的数组 。
LARGE(($D$2:$D$710<=30)*($A$2:$A$710=$N3)*E$2:E$710,ROW($A$1:$A$30)):
取上一步得到的数组内的前30名 。
=AVERAGE(LARGE(($D$2:$D$710<=30)*($A$2:$A$710=$N3)*E$2:E$710,ROW($A$1:$A$30)))，以“Ctrl Shift Enter”三键组合结束公式输入，计算出N3班级的前30名平均总分 。
--------------------------------------------------
练习素材下载
链接：https://pan.baidu.com/s/1WOY1M5lH_sWBQJK3C5U98g
提取码：0jdd
------------------------------------------------