使用Excel 统计及格率、优秀率
使用Excel 统计及格率、优秀率
本文通过具体的数据演示了如何通过Excel统计学生成绩的及格率和优良率。其实生活中类似的场景还有很多，比如企业统计员工的出勤率、工厂统计产品的良品率等。
一、背景说明
家里有个做老师的对象。属于那种工作很认真，但是电脑很菜的主。今年又双叒叕做了班主任，所以一些数据统计的工作自然而然地落到了我的身上。
这不是学期又结束了嘛。因为政策原因，学校是不可以给孩子排名次的，并且学生成绩只能以“优”、“良”，“及格”等进行表示。但是作为一班之主任，基本的及格率、良好率和优秀率总得做到心中有数吧。本文记录了如何通过Excel统计学生成绩的及格率和优良率。
二、问题和分析
2.1、问题描述
作为演示，我对原始的表格数据进行了简化和处理。一方面方便说明；另一方面也避免泄露他人的隐私。
演示案例中只涉及四门课程，分别是：道德、语文、数学和英语。
具体的演示数据如下：
2.2、解决思路
这个问题如果通过程序可能比较好解决，但是奈何我的Excel也是半桶水。不过既然是解决问题，虽然缺乏相关的知识，但是解决问题的思路至少是有的：
- 求优秀率。就是统计成绩是“优”的有几人，然后再统计一下总人数。最后代入公式：
优秀率 = 优秀的人数/总人数
。 - 找出统计成绩是“优”的方法
- 找出统计总人数的方法
- 良好率的求法类似。只是需要增加统计维度：同时统计
优秀
和
良好
的人数。
三、困难与解决
3.1、困难一：如何统计值为指定值的单元格的个数
以“道德”这门课的成绩为例，首先我想知道“道德”这门课，成绩为“优”的有几个人。Excel中是否有函数可以直接获取这类值？
通过查看Excel的函数列表，找到了能满足我的需求的函数：
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
函数参数说明：
- criteria_range1：条件作用的区域。如：
A2:A50
, 表示第1列的第二行到第50行。 - criteria：表示需满足的条件。如：
60
，
=优秀
等等 - [criteria_range2, criteria2]：表示可以追加的一组新条件。
这个函数至少需要提供一组条件。多个条件之间是
且的关系
。
现在回到我的需求：获取在“道德”这门课程中成绩优异的同学有几名？
如果用COUNTIFS函数实现，代码如下：
=COUNTIFS(C2:C21,"=优")
语法解析：
C2:C21
：表示作用的数据区域是第2行第三列 到 第21行第三列
"=优"
：是作用于数据区域的条件。意思是单元格值为“优”的数据
我们人工数一下，可以发现：道德为“优”的数据有14条。
3.2、困难二：如何统计有值的单元格的个数
图中的数据是经过处理过的。实际数据中是
存在空行
的，因为历史原因某些同学转学或休学了，所以有些学号是空的。例如现在51号同学转学了，但是不能让52号的同学顺位变为51号，因为这样所有历史的班级数据都需要调整。极端情况是1号同学转学了，那么他后面的所有同学的学号都需要调整，历史的考试成绩、学生活动数据都有可能受到影响，所以最好的方案是保留转学或休学同学的学号。
那么，针对上面所说的问题，我们如何去统计有值的单元格呢？
其实Excel针对这样的数据有其解决方法，即函数
COUNTA
。
COUNTA函数的语法规则如下：
COUNTA(value1,[value2],...)
语法解析：
value1
：待统计的数据区域。一般形如：
B2:B56
value2
：可选值。如果设置，表示同时统计
value1+value2
区域的非空单元格个数。
从语法规则可知：COUNTA函数既可以统计一个区域，也可以统计多个区域的非空单元格的个数。
回到我们的需求，“道德”课程，需要统计的区域只有一个，即
C2:C21
。所以所有同学成绩的个数可使用下面的函数获取：
=COUNTA(C2:C21)
至此，结合上面两个函数的结果，可以通过下面的函数获取道德这么课的优秀率：
=COUNTIFS(C2:C21,"=优")/COUNTA(C2:C21)
该函数得到的结果是：
70%
我们可以做一个人工验算：
优秀人数：
14
总学生数（不含无成绩的学生）：
20
优秀率 = 优秀人数/总学生数 = 14÷20 =0.7=70%
所以，该函数获得的结果是正确的。
那么接下来我们再计算良好率。是不是也可以一下子出来呢？？
在继续后面的工作前，有必要介绍下成绩值的组成。由于教育政策的要求，学生成绩单是不允许出现分数的，所以需要对学生的成绩进行转换。分数与成绩的转换规则如下：
分数 成绩 说明
< 60 待努力 不及格
60 ~ 74 及 及格
75 ~ 84 良 良好
85 ~ 100 优 优秀
接下来我们要统计的良好率，是既包含成绩优秀，又包含成绩良好的同学。前面我们介绍过：
COUNTIFS
函数可以追加多组条件，但是条件和条件之间是
且的关系
。
如何理解？
以取良好率的场景为例，如果我们使用COUNTIFS 作用多个条件：
=COUNTIFS(C2:C21,"=优",C2:C21,"=良")
那得到的结果是：取“道德”成绩既为
优秀
，又为
良好
的同学的人数。
一方面这两个条件显然是矛盾的；另一方面这个结果也不是我所需要的，因为条件之间是
且的关系
，而我需要的是
或的关系
：
道德成绩为优秀或良好的同学的人数
。因为该函数针对某个区域的值：
数值类型：可以取一段区间。见官方示例的示例2
字符类型：只能取指定的值。虽然可以使用通配符获取多个值，但是这些值都具有相关性
所以，COUNTIFS的条件无法实现如下功能：
道德成绩：为优秀，或者为良好的人数
而只能实现如下功能：
道德成绩为优秀；并且语文成绩为良好的人数
公式代码如下：
=COUNTIFS(C2:C21,"=优",D2:D21,"=良")
也曾尝试过如下公式：
=COUNTIFS(D2:D22,OR("=优","=良"))
目的是使用OR函数连接两个条件，但是函数预览时报红，如下：
所以，目前没有找到比较优雅的写法 😂
难道不优雅就意味着没有办法了吗？毕竟笨办法总好于没有办法😄
最后我只能以下面这样的笨方法去获取成绩优良的学生人数：
=COUNTIFS(C2:C21,"=优")+COUNTIFS(C2:C21,"=良")
下面直接给出良好率的公式：
=(COUNTIFS(C2:C21,"=优")+COUNTIFS(C2:C21,"=良"))/COUNTA(C2:C21)
及格率的公式和良好率相似：
=(COUNTIFS(C2:C21,"=优")+COUNTIFS(C2:C21,"=良")+COUNTIFS(C2:C21,"=及"))/COUNTA(C2:C21)
至于其他学科的优秀率、良好率和及格率，直接使用填充功能就可以了💖
最终的统计结果如下图：
四、总结
本文通过具体的数据演示了如何通过Excel统计学生成绩的及格率和优良率。其实生活中类似的场景还有很多，比如企业统计员工的出勤率、工厂统计产品的良品率等。
在解决问题的过程中，使用了Excel的两个函数：
COUNTIFS
和
COUNTA
。前一个函数可以统计符合指定条件的记录数；而后一个函数可用于统计非空单元格的个数。在本文的示例中，也可以不使用
COUNTA
，而是引入一个独立的单元格（可称之为
在籍数
），其值设置为固定的数值（比如文中可设为
20
）。
在此过程中，遇到的最大问题是：无法使用优雅的方式统计
成绩为优秀或是良好的同学的个数
。最后使用了比较笨的方法：多次使用
COUNTIFS
，并累加它们的统计结果。