本文总共1147个字,阅读需4分钟,全文加载时间:3.600s,本站综合其他专栏收录该内容! 字体大小:

文章导读:问题情景 如下表格: 共18个班级,七百多名学生,要求统计每个班级 总分前30名的同学各科平均成绩,如下图: 注意,并不是各科前30名,而是总分前30名。假设某同学,语文满分,但是总分不在班级前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

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

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

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

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注