excel数据表怎么统计成绩

excel数据表怎么统计成绩?问题情境

如下表格:

共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名平均总分。

阅读已结束,喜欢的话就点个赞吧
注册坚果云网盘
还有其他问题,可以咨询小坚果咨询小坚果
赞(0)
网站部分内容和图片来源于网络如有侵权请联系我们删除:坚果芸 » excel数据表怎么统计成绩
分享到: 更多 (0)
excel数据表怎么统计成绩

本文链接:excel数据表怎么统计成绩https://content.officeapi.cn/23877.html

坚果云 便捷、安全的网盘产品

免费试用下载客户端