两集合容斥原理公式在excel中如何计算及格率和优秀率及统计各分数段人数

2020年9月29日发(作者：金龙节)
在excel中如何计算及格率和优秀率

在excel中如何计算及格率和优秀率
A3=85
A4=23
A5=80
=COUNTIF(A2:A5,
统计的学生某科成绩，>=80优（合格）的条件。
完整表达%
=COUNTIF(A2:A5,
但有一个问题，如果学生缺考该怎样计分母，因为如果未输入分数 （含
0），结果将不一样，建议COUNT(A2:A5)可直接输入实际人数符合学校评
估要 求！
其实有一数组FREQUENCY更方便！
假设范围是A1：A5
优秀率:
CountIF(A1:A5,
及格率:
CountIF(A1:A5,
方法二:
在excel中，怎样统计学生的优秀率和及格率。
如何用EXCEL计算优秀率和及格率
我们在教学工作中经常要用EXCEL统计学生成绩，其中要统计学生的
优秀人数，及格人 数以及优秀率，及格率，这些功能如果用菜单来操作比
较繁琐，其实完全可以直接在表格中输入函数，轻 松搞定。
数据表——

1
参考人数
优秀人数
优秀率
公式编辑——
C19单元格中输入公式
=COUNTA（C2：C16）——计算“参考人数”；
C20中单元格中输入公式
=COUNTIF（C2：C16，）——计算“优秀人数”，设计90及其
以上为“优秀”；
C21单元格中输入公式
=C20C19——计算“优秀率”，单元格设置为百分比格式,自动保留
2位小数；
其实，可以把上述三个步骤综合成一个直接计算“优秀率”的公式
=COUNTIF（C2：C16，）COUNTA（C2：C16）
这样做起来是不是轻松多了 ？比在工具和菜单中操作方便多了吧。同
样的道理也可以用来计算及格率。这里就不重复了。
1、COUNTIF函数
函数名称：COUNTIF
主要功能：统计某个单元格区域中符合指定条件的单元格数目。
使用格式：COUNTIF(Range,Criteria)
参数说明：Range代表要统计的单元格区域；Criteria表示指定的条
件表达式。
应用举例：在C17单元格中输入公式：=COUNTIF(B1:B13,，确
认后，即可统计出B1 至B13单元格区域中，数值大于等于80的单元格数
目。
特别提醒：允许引用的单元格区域中有空白单元格出现。
2、AVERAGE函数
函数名称：AVERAGE

2
主要功能：求出所有参数的算术平均值。
使用格式：AVERAGE(number1,number2,……)
参数说明： number1,number2,……：需要求平均值的数值或引用单元
格（区域），参数不超过30 个。
应用举例：在B8单元格中输入公式：=AVERAGE(B7:D7,F7:H7,7,8 )，
确认后，即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。
特 别提醒：如果引用区域中包含“0”值单元格，则计算在内；如果引
用区域中包含空白或字符单元格，则 不计算在内
Excel统计学生成绩用excel统计各分数段人数统计学生的优秀率和及
格率
Excel统计学生成绩时的四个难题及解决

一、前言
对于教师而 言，经常需要用Excel进行学生成绩统计，会被一些常见
问题难住。碰到的难题主要有：如何统计不 同分数段人数、如何在保持学
号不变前提下进行排名、如何将百分制转换成不同分数段与如何用红色显< br>示不及格的分数等，本文着重对这些问题的解决方法与技巧加以分析和讨
论。
本文 假设读者已对Excel的基本操作已经有一定基础，已经掌握如何
进行求和、求平均和如何使用自动填 充柄进行复制公式等操作，本文对这
些一般性操作不做详细介绍，仅对一些难度较大的操作技巧进行讨论 。

3
二、Excel统计学生成绩时的四个难题
假设在统计学生成绩时，我们需要统计出如图1所示的相关结果。
在

图1 学生成绩统计所需要的结果图
这里，假设学号、姓名、成绩等列及行15都已经事先输好，需要让
Excel统计其他的相关数据结果。这时，成绩统计中主要难解决的问题及
它们在图中的位置 如下：
问题1：如何统计不同分数段的学生人数?(图中A16~E16)
问题2：如何在保持学号顺序不变的前提下进行学生成绩名次排定?(图
中F2~F13)
问题3：如何将百分制转换成不同的等级分?(图中“等级1”与“等级
2”列)
问题4：如何使不及格的分数以红色显示?(图中红色显示部分，即第
12行)

三、解决统计学生成绩时的四个难题的方法

4
下面，针对上面提出的四个难题分别讨论解决的方法与技巧。
1、统计不同分数段的学生人数
统计不同分数段的学生人数是非常常见的需求，其所需结果如图1中
A16~E16所示。 这里，假设需要统计90~100、80~89、70~79、60~69及低
于60分五个不同分数段 的人数。
通常，统计不同分数段最好的方法是利用COUNTIF(X，Y)函数。其中
有两个参数，第一个参数X为统计的范围，一般最好用绝对引用;第二个参
数Y为统计条件，要加引号。
对于小于60分的人数只要用一个COUNTIF( )函数，如在E16单元格
中输入公式：
=COUNTIF($$C$$2:$$C$$13,
对于其他在两个分数之间的分数段的人数统计，需要用两个COUNTIF( )
函数相减。如在A16单元格中输入公式：
=COUNTIF($$C$$2:$$C$$13,
即用小于等于100的人数减去小于90的人数。
如果要统计80~89、70~79与60~69分数段的人数，只要利用自动填充
柄将该 公式复制到右边三个单元格，再把与作相应的修改，
就可以得到正确的结果。

5
2.保持学号顺序不变的前提下进行成绩排名
学生成绩排定在学生成绩统计中经常用 到。特别要强调的是，这里所
谈的方法不是一般的排序，因为那样会使学生的学号顺序发生变化。这里< br>所需要的是在保持学号顺序不变的情况下进行学生成绩名次排定的功能，
其所需结果如图1中F2 ~F13所示。
要进行保持学号顺序不变的情况下进行学生成绩名次的排定，最好使
用R ANK(X，Y，Z)函数。其中有三个参数，第一个参数X为某个学生的成
绩所在单元格;第二个参数 Y为整个班级成绩所在的区域;第三个参数Z是
可选的，表示统计方式，若省写或写0，则成绩高的名次 靠前，一般都使
用这种方式，如果写1，则成绩高的名次靠后，这种情况一般较少用。
为了在保持学号顺序不变的前提下进行学生成绩名次排定，可以在F2
单元格中输入公式：
=RANK(C2,$$C$$2:$$C$$13,0)
然后，利用自动填充柄将其复制到下方的几个单元格 。注意，这里
$$C$$2:$$C$$13用的是绝对地址，是为了保证公式在复制时此处不变，因为作
为第二个参数，这里都是指整个班级成绩所在的区域，这个区域是相同的。
3、将百分制转换成不同的等级分
将百分制转换成不同的等级分有多种不同的划分方法，其所需结 果如
图1中“等级1”与“等级2”列所示。这里，“等级1”列是将百分制的

6
分数转换成A(90~100)、B(80~89)、C(70~79)、D(60~69)与E(低于 60)五
个等级;“等级2”列是将百分制的分数转换成优(90~100)、良(75~89)、中(60~74)与不及格(低于60)四个等级。具体使用哪种等级划分方法可根
据实际情况自己 确定。
在百分制转换成不同的等级分时，一般使用IF(X，Y，Z)函数。其中
有三个 参数，第一个参数X为条件，不能加引号;第二个参数为条件成立时
的结果，如果是显示某个值，则要加 引号;第三个参数为条件不成立时的结
果，如果是显示某个值，同样要加引号。该函数可以嵌套，即在第 二个或
第三个参数处可以再写一个IF函数。
为了得到“等级1”列所要的等级结果，可以在D2单元格中输入公式：
=IF(C2>=90,

然后，利用自动填充柄将其复制到下方的几个单元格。
为了得到“等级2”列所要的等级结果，可以在E2单元格中输入公式：
=IF(C2>=90,优良中不及格
然后，利用自动填充柄将其复制到下方的几个单元格。
4、使不及格的分数以红色显示

7
统计学生成绩时经常需要将不及格的分数用红色显示，其结果如图1
中红色显示部分(如第12行)。
使不及格的分数以红色显示需要使用“格式”菜单中的“条件格式”
命令。该命令会弹出一 个对话框，其中要求确认条件与相应的格式。
对于“成绩”列，可先选中C2：C13，然后使用 “格式”菜单中的“条
件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中
间选“小于”，右边填写60，然后单击右边的“格式”按钮，从中选择红
色，最后单击两次“确定”按 钮。
对于“等级1”列，可先选中D2：D13，然后使用“格式”菜单中的“条
件格式 ”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中
间选“等于”，右边填写E，然后单击 右边的“格式”按钮，从中选择红
色，最后单击两次“确定”按钮。“等级2”列类似。
对于其他的一些统计计算要求，如怎样计算各分数段的百分比、如何
计算机优良率与合格率等功能，应该 比较简单，本文此处不赘述。


excel - Rank函数



8
返回一个数字在数字列表中的排位。数字的排位是其大小与列表中 其他值
的比值（如果列表已排过序，则数字的排位就是它当前的位置）。
语法
RANK(number,ref,order)
Number 为需要找到排位的数字。
Ref 为数字列表数组或对数字列表的引用。Ref 中的非数值型参数
将被忽略。
Order 为一数字，指明排位的方式。
如果 order 为 0（零）或省略，Microsoft Excel 对数字的排位是基于 ref
为按照降序排列的列表。
如果 order 不为零，Microsoft Excel 对数字的排位是基于 ref 为按照
升序排列的列表。

计各分数段人数需要用到-------------- FREQUENCY函
数
请老师们先来了解-------------、FREQUENCY函数
函数名称：FREQUENCY
主要功能：以一列垂直数组返回某个区域中数据的频率分布。

9
使用格式：FREQUENCY(data_array,bins_array)
参数说明： Data_array表示用来计算频率的一组数据或单元格区域；
Bins_array表示为前面数 组进行分隔一列数值。
应用举例：如图2所示，同时选中B32至B36单元格区域，输入 公式：
=FREQUENCY(B2:B31,D2:D36)，输入完成后按下“Ctrl+Shif t+Enter”组合
键进行确认，即可求出B2至B31区域中，按D2至D36区域进行分隔的各< br>段数值的出现频率数目（相当于统计各分数段人数）。



特别提醒：上述输入的是一个数组公式，输入完成后，需要通过按
“Ctrl+Shift+Enter ”组合键进行确认，确认后公式两端出现一对大括号
（{}），此大括号不能直接输入。

在excel中，怎样统计学生的优秀率和及格率。
A2=89
A3=85
A4=23
A5=80

10
=COUNTIF(A2:A5,
统计的学生某科成绩，>=80优（合格）的条件。
完整表达%
=COUNTIF(A2:A5,
但有一个问题，如果学生缺考该怎样 计分母，因为如果未输入分数（含0），
结果将不一样，建议COUNT(A2:A5)可直接输入实际 人数符合学校评估要求！
其实有一数组FREQUENCY更方便！
假设范围是A1：A5
优秀率:
CountIF(A1:A5,
及格率:
CountIF(A1:A5,

方法二:
在excel中，怎样统计学生的优秀率和及格率。
如何用EXCEL计算优秀率和及格率

我们在教学工作中经常要用EXCEL 统计学生成绩，其中要统计学生的优
秀人数，及格人数以及优秀率，及格率，这些功能如果用菜单来操作 比较

11
繁琐，其实完全可以直接在表格中输入函数，轻松搞定。
举例如下：



数据表——

参考人数
优秀人数
优秀率
公式编辑——
C19单元格中输入公式
=COUNTA（C2：C16）——计算“参考人数”；
C20中单元格中输入公式
=COUNTIF（C2：C16，）——计算“优秀人数”，设计90及其以上
为“优秀”；
C21单元格中输入公式
=C20C19——计算“优秀率”，单元格设置为百分比格式,自动保留2位
小数；
其实，可以把上述三个步骤综合成一个直接计算“优秀率”的公式
=COUNTIF（C2：C16，）COUNTA（C2：C16）

12 这样做起来是不是轻松多了？比在工具和菜单中操作方便多了吧。同样的
道理也可以用来计算及格率 。这里就不重复了。



6、COUNTIF函数
函数名称：COUNTIF
主要功能：统计某个单元格区域中符合指定条件的单元格数目。
使用格式：COUNTIF(Range,Criteria)
参数说明：Range代表要统计的单元格区域；Criteria表示指定的条
件表达式。
应用举例：在C17单元格中输入公式：=COUNTIF(B1:B13,，确
认后，即 可统计出B1至B13单元格区域中，数值大于等于80的单元格数
目。
特别提醒：允许引用的单元格区域中有空白单元格出现。



13
3、AVERAGE函数
函数名称：AVERAGE
主要功能：求出所有参数的算术平均值。
使用格式：AVERAGE(number1,number2,……)
参数说明：numbe r1,number2,……：需要求平均值的数值或引用单元
格（区域），参数不超过30个。
应用举例：在B8单元格中输入公式：=AVERAGE(B7:D7,F7:H7,7,8)，< br>确认后，即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。
特别提 醒：如果引用区域中包含“0”值单元格，则计算在内；如果引
用区域中包含空白或字符单元格，则不计 算在内。


14