设置主页 | 收藏本站 Excel学习网-公益型的EXCEL在线学习网站,助您轻松办公!

Excel统计总分前30名的各科平均成绩

2020年09月21日 18:40 发布来源:Excel学习网

 问题情景如下表格:

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

 

共18个班级,七百多名学生,要求统计每个班级 总分前30名的同学各科平均成绩,如下图:

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

 

注意,并不是各科前30名,而是总分前30名。假设某同学,语文满分,但是总分不在班级前30内,他的语文成绩也不能统计在内。

公式实现

1、计算总分

这一步最容易,公式:=SUM(F2:L2),如下图:

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

 

2、计算班级排名

在D2单元格输入公式:=SUMPRODUCT(($A$2:$A$710=A2)*($E$2:$E$710>E2))+1,确定,并向下填充,得到所有同学在本班内的名次,如下图:

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

 

该公式的结果:如果总会相同,名次则相同。

公式解释参考: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名的同学各科平均成绩,如下图:

Excel | 统计总分前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名平均总分。

原创文章如转载请注明:转载自Excel学习网 [ http://www.excelcn.com/]
需要保留本文链接地址:http://www.excelcn.com/jiqiao/12839.html
分享到:
网站地图 | XML地图 | 免责声明 | 关于我们 | Excel学习网:优秀的EXCEL在线学习和资源分享网站。
版权所有: CopyRight © 2013-2018 www.excelcn.com All Rights Reserved.
豫ICP备12002644号