又开学啦，有了这个方法，老师们再也不担心统计工作啦！ / 四六文摘

送人玫瑰，手有余香，请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径！

下面这张表是某学校期末考试成绩表。我们需要统计每个班级的平均分、前10名的平均分、后10名的平均分、合格率、优秀率以及缺考学生的人数。

按照以往的老经验，老师们会先汇总总成绩，然后在分别统计其余各项指标。对于缺考的学生人数，也是通过手动来核查的。

如今，在EXCEL函数的帮助下，我们只需要输入基础的学生各科成绩即可，缺考的学生成绩记为0。剩下的所有事情都可以通过函数来一次性完成！

先做一些准备工作。在合并单元格A67中创建数据验证，创建一个班级的下拉清单。

平均分

我们先来看看如何统计平均分。

在单元格E67中输入公式“=ROUND(SUMPRODUCT(($A$2:$A$65=--MID($A$67,3,1))*(E$2:E$65>0)*(E$2:E$65))/SUMPRODUCT((E$2:E$65>0)*($A$2:$A$65=--MID($A$67,3,1))),2)”，并向右拖曳即可。

思路：

SUMPRODUCT(($A$2:$A$65=--MID($A$67,3,1))*(E$2:E$65>0)*(E$2:E$65))部分，统计的是三（1）班所有没有缺考的学生的总成绩
SUMPRODUCT((E$2:E$65>0)*($A$2:$A$65=--MID($A$67,3,1)))部分，统计的是三（1）班所有参加考试的学生人数
公式比较简单，小伙伴们观察一下上面这两组公式有什么差别

前10名平均分

在单元格E68中输入公式“=SUMPRODUCT(LARGE(($A$2:$A$65=--MID($A$67,3,1))*(E$2:E$65),ROW($1:$10)))/10”，并向右拖曳即可。

思路：

($A$2:$A$65=--MID($A$67,3,1))*(E$2:E$65)部分，返回三（1）班所有的成绩清单
LARGE(($A$2:$A$65=--MID($A$67,3,1))*(E$2:E$65),ROW($1:$10))部分，提取其中前10大的成绩
后面的公式比较简单，不再详细介绍了

后10名平均分

在单元格E69中输入公式“=SUMPRODUCT(SMALL(IF(($A$2:$A$65=--MID($A$67,3,1))*(E$2:E$65>0),E$2:E$65),ROW($1:$10)))/10”，三键回车并向右拖曳即可。

思路：

本例中不能使用像求前10名平均分那样，直接使用SMALL(($A$2:$A$65=--MID($A$67,3,1))*(E$2:E$65>0), ROW($1:$10)))。原因是，($A$2:$A$65=--MID($A$67,3,1))*(E$2:E$65>0)返回的内存数组中有很多不符合条件的数据所返回的“0”，直接使用SMALL函数是抓不到后10名的成绩的
利用IF函数省略第三个参数，返回一组以三（1）班所有成绩和错误值构成的内存数组
再使用SMALL函数就可以提前到后10名的成绩了

合格率

我们假定成绩大于60分即为合格。所有成绩大于60分的人数除以所有参加考试的总人数就是合格率。