一、背景说明
家里有个做老师的对象。属于那种工作很认真,但是电脑很菜的主。今年又双叒叕做了班主任,所以一些数据统计的工作自然而然地落到了我的身上。
这不是学期又结束了嘛。因为政策原因,学校是不可以给孩子排名次的,并且学生成绩只能以“优”、“良”,“及格”等进行表示。但是作为一班之主任,基本的及格率、良好率和优秀率总得做到心中有数吧。本文记录了如何通过 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
,并累加它们的统计结果。
参考资料:
微软:https://support.microsoft.com/zh-cn/office/countifs-%E5%87%BD%E6%95%B0-dda3dc6e-f74e-4aee-88bc-aa8c2a866842