Excel | 总分前30名的同学各科平均成绩如何统计

问题情景

如下表格：

共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：

所有同学的名次与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

------------------------------------------------

以上内容由优质教程资源合作伙伴 “鲸鱼办公” 整理编辑，如果对您有帮助欢迎转发分享！

你可能对这些文章感兴趣：

• Excel表格十大函数(Excel表格函数公式出现错误怎么办?)
• 新入手的iPhone 13，记得提前打开这5个功能，让手机更好用
• iphone应用图标大小要怎么调整？(iPhone应用图标大小)
• 有办法了！批量删除多个Word页眉页脚
• RANKIF？Excel中有这个函数吗？
• 手机与和U盘的连接及文件操作(手机直接连接u盘为什么不能互传文件)
• 人像摆拍姿势和抓拍技巧，学会“安排”照片，摄影新手也能拍美照
• 调整干部开启，如何找准自己的位置？以下几点值得借鉴
• Excel表格办公—-公式与结果共同显示的方法
• 人人都该掌握的电脑小技巧，建议收藏