rd计算公式EXCEL学生成绩统计的常用函数公式

2020年9月21日发(作者：慕湘)
1、统计不同分数段的学生人数
统计不同分数段的学生人数是非常常见的需求，其所需 结果如图1中A16~E16所示。这
里，假设需要统计90~100、80~89、70~79、60 ~69及低于60分五个不同分数段的人数。
通常，统计不同分数段最好的方法是利用COUNT IF(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分数段的人数，只要利用自动填充 柄将该公式复制
到右边三个单元格，再把与作相应的修改，就可以得到正确的结果。
2.保持学号顺序不变的前提下进行成绩排名
学生成绩排定在学生成绩统计中经常用到。特别要强 调的是，这里所谈的方法不是一般
的排序，因为那样会使学生的学号顺序发生变化。这里所需要的是在保 持学号顺序不变的情
况下进行学生成绩名次排定的功能，其所需结果如图1中F2~F13所示。
要进行保持学号顺序不变的情况下进行学生成绩名次的排定，最好使用RANK(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”列是将百分制的分数转换成A (90~100)、B(80~89)、C(70~79)、D(60~69)与E(低于60)五个等级;“等级2”列是将百分制的分数转换成优
(90~100)、良(75~89)、中(60~74)与不及格(低于60)四个等级。具体使用哪 种等级划分方
法可根据实际情况自己确定。
在百分制转换成不同的等级分时，一般使用I F(X，Y，Z)函数。其中有三个参数，第一
个参数X为条件，不能加引号;第二个参数为条件成立时 的结果，如果是显示某个值，则要
加引号;第三个参数为条件不成立时的结果，如果是显示某个值，同样 要加引号。该函数可
以嵌套，即在第二个或第三个参数处可以再写一个 IF函数。
为了得到“等级1”列所要的等级结果，可以在D2单元格中输入公式：=IF(C2>=
90,，然后，利用自动填充柄将
其复制到下方的几个单元格。
为了得到“等级 2”列所要的等级结果，可以在E2单元格中输入公式：=IF(C2>=90,
优良中不及格，然后， 利用自动填充柄将其复制到下
方的几个单元格。
4、使不及格的分数以红色显示
统计学生成绩时经常需要将不及格的分数用红色显示，其结果如图1中红色显示部分
(如第12行)。

使不及格的分数以红色显示需要使用“格式”菜单中的“条件格式”命令。该命令会弹
出一个对话框，其中要求确认条件与相应的格式。
对于“成绩”列，可先选中C2：C1 3，然后使用“格式”菜单中的“条件格式”命令，
在弹出的对话框中，左边使用默认的“单元格数值” ，中间选“小于”，右边填写60，然
后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定 ”按钮。
对于“等级1”列，可先选中D2：D13，然后使用“格式”菜单中的“条件格式”命 令，
在弹出的对话框中，左边使用默认的“单元格数值”，中间选“等于”，右边填写E，然后
单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。“等级2”列类似。
对于其他 的一些统计计算要求，如怎样计算各分数段的百分比、如何计算机优良率与合格率
等功能，应该比较简单 ，本文此处不赘述。
5、保留几位小数？
选取要更改的单元格（一般都是整行或者整列选取 ），右键-->设置单元格格式-->数字，在
里面选择就是了，上面都有这种格式的样板的。
比如你要的“数字变成3位有效数字，包括小数”，就选数值，把小数位数改成3就行了
那只能再把整数改回来，不过一般来说，整数后面也放同样位数的0，是正确的办法。
修改 的时候，按住Ctrl键，再逐个点整数的单元格，最后一次性调整小数位，这样会稍微
快点
6、求最高分和最低分
MAX函数 ：求出一组数中的最大值。
MIN函数：求出一组数中的最小值。
7、求和、条件求和
SUM 函数：计算所有参数数值的和。
SUMIF函数：计算符合指定条件的单元格区域内的数值和。（？）
8、区域求和
区域求和常用于对一张工作表中的所有数据求总计。此时你可以让单元格指针停留在存放结
果的单元格 ，然后在Excel编辑栏输入公式（），用鼠标在括号中间单击，最后拖过
需要求和的所有单元格。若 这些单元格是不连续的，可以按住Ctrl键分别拖过它们。对于
需要减去的单元格，则可以按住Ctr l键逐个选中它们，然后用手工在公式引用的单元格前
加上负号。当然你也可以用公式选项板完成上述工 作，不过对于SUM函数来说手工还是来的
快一些。比如，H13的公式还可以写成：=SUM(D3: D12,F3:F12)-SUM(G3:G12)
9、四舍五入
在实际工作的数学运算中 ，特别是财务计算中常常遇到四舍五入的问题。虽然，excel的单
元格格式中允许你定义小数位数， 但是在实际操作中，我们发现，其实数字本身并没有真正
的四舍五入，只是显示结果似乎四舍五入了。如 果采用这种四舍五入方法的话，在财务运算
中常常会出现几分钱的误差，而这是财务运算不允许的。那是 否有简单可行的方法来进行真
正的四舍五入呢？其实，Excel已经提供这方面的函数了，这就是RO UND函数，它可以返回
某个数字按指定位数舍入后的数字。
在Excel提供的数学与三角 函数中提供了一个名为ROUND(number,num_digits)的函数，
它的功能就是根据 指定的位数，将数字四舍五入。这个函数有两个参数，分别是number和
num_digits。其 中number就是将要进行四舍五入的数字；num_digits则是希望得到的数字
的小数点后的 位数。如图3所示：
单元格B2中为初始数据0.123456，B3的初始数据为0.234567 ，将要对它们进行四舍五入。
在单元格C2中输入，小数点后保留两位有效数字，得到0.12、0.2 3。在单
元格D2中输入，则小数点保留四位有效数字，得到0.1235、0.2346。
10、及格率、优秀率的计算
=COUNTIF(a1:a10, 计算优秀率
=COUNTIF(a1:a10,计算及格率
11、打印时添加大标题、顶端标题
文件页面设置页眉页脚自定义页眉页脚
如果要求打印时每一页都出现第一行：
文件页面设置工作表顶端标题栏内输入$$1:$$1
12、为工作表加密
文件另存为工具常规选项加密确定
学习小结：
EXCEL学生成绩统计的常用函数公式
1.总分公式：=sum(C2:C59)；
2.平均分公式： =AVERAGE(C2:C59)；
3.最高分公式： =MAX(C2:C59)；
4.及格率公式： =(COUNTIF(C2:C59,；
5.优秀率公式： =(COUNTIF(C2:C59,；
6.90分以上人数公式： =COUNTIF(C2:C59,；
7.80分~85分人数公式： =COUNTIF(C2:C59,；
8.60分以下人数公式： =COUNTIF(C2:C59,。
平均分只需保留一位小数：鼠标右键单击→设置单元格格式→数值→小数位数设置为1位。
不 及格的学科突出显示：选择全部成绩→格式→条件格式→条件格式对话框→把条件设为
小于60分的用红 色显示→点击格式按钮→把颜色设为红色→确定。
小数变百分比：鼠标右键单击→设置单元格格式→百分比→小数位数设置为1位→确定。
如何计算

[2.1] 计算考号1的总分
表格都是由行和列组成的，行由 数字表示，列用字母表示。当活动单元格在
F4位置时键入“＝”然后输入SUM(C4:E4)（表示 从C4单元格加到E4单元格），
再回车，即得总分。另一种方法是选择C4到E4这三个单元格后单击 工具栏上“自
动求和”按钮。




[2.2] 其他总分的得出
用鼠标选取已算出的学号1的总分，当出现“+”符号后往下拖动鼠标，所
有 其他的总评分会自动出现。
3.如何计算学生平均分
[3.1] 学生1的平均分： 活动单元格放在需要填写平均分的位置，输入“＝F43”（用总分除以科目
3）回车，即算出平均 分。
[3．2] 其他平均分的得出：
用鼠标选取已算出的学生1的平均分，当出现“+” 符号后往下拖动鼠标，
所有其他的平均分会自动出现。
4.如何排列名次
[4．1] 利用菜单排序：
排列名次，首先按总分排序。
从列标题那行一直选取 到最后（本表是从第3行到55行），单击菜单“数
据”，再单击“排序”，在主要关键字下拉列表中选 择“总分”，再选“降序”，
按下“确定”键，分数即由高至低排列。这时再用“自动填充”方法将名次 的号
（数字）填上。缺点是并列的名次没有显示出来。

然后再选全表，单击“数据 ”再单击“排序”，在下拉列表中选主要关键字
“考号”，再选“升序”，最后“确定”，即出现按考号 排列的名次格式。


[4．2] 利用RANK函数排序：
另外一种 方法是利用RANK函数进行排名，解决了以上统计方法无并列名次
的缺点。RANK(X，Y，Z)函 数，其中有三个参数，第一个参数X为某个学生的总
分所在单元格，第二个参数Y为整个班级总分所在的 区域，第三个参数Z是可选
的，表示统计方式，若省写或写0，则成绩高的名次靠前，一般都使用这种方 式，
如果写1，则成绩高的名次靠后，这种情况一般较少用。
为了在保持学号顺序不变的前提下进行学生成绩名次排定，可以在F4单元
格中输入公式： =RANK(F4,$$F$$4:$$F$$55,0)


然后，利用自动填充柄将其复制 到下方的单元格，统计出所有学生的名次。
注意，这里$$F$$4:$$F$$55用的是绝对地址，是为了保 证公式在复制时此处不变，
因为作为第二个参数，这里都是指整个班级总分所在的区域，这个区域是相同 的。
再按名次排序，可以看出并列的名次也统计出来了。（见下图）


5.进行科目统计
[5．1] 计算科目总分
选取所要求和的“总分”一栏的数据 ，单击“Σ”按钮，或者是在其中输入
公式“＝SUM（C4：C55）”后回车即算出科目总分。然后 ，利用自动填充柄将其
复制到右方的单元格，数学、英语总分即可得出。
[5．2] 计算科目平均分：
方法同上，只是函数不一样
在要填入语文平均分的单元格内输入公式：“ =AVERAGE(C4:C55)”即得出语
文平均分，然后，利用自动填充柄将其复制到右方的单元 格，数学、英语平均分
即可得出。
[5．3] 统计科目及格人数：
通常，统计不 同分数段最好的方法是利用COUNTIF(X，Y)函数。其中有两个
参数，第一个参数X为统计的范 围，一般最好用绝对引用;第二个参数Y为统计
条件，要加引号。
对于大于等于60分的人数只要用一个COUNTIF( )函数，如在C59单元格中
输入公式：
=COUNTIF($$C$$4:$$C$$55,
回车即可得出。其它两科复制公式后，把统计范围即其中绝对地址分别改
为$$D$$4:$$D$$5 5和$$E$$4:$$E$$55，数学和英语科目及格人数也统计完成。


[5．4] 计算单科及格率：
单科及格率等于及格人数除以参考人数。语文科目已在C59单元格统计出
及格人数，参考人数在列标题上方E2单元格内填入，因此在统计及格率C60单
元格内写入公式：=C 59E2回车即得出。单元格格式可设为“百分比”保留2位
小数。其它两科同样的方法可算出。
[5．5] 其它分数段的统计
方法同统计及格人数，优生人数的统计只需把统计条件改为“ >=80”差生
人数的统计把统计条件改为“<40”。 如果要统计80——89、70——79与60—
—69分数段的人数，需要用两个COUNTIF( )函数相减。如统计80——90分数段
人数用公式： =COUNTIF($$C$$4:$$C$$55,”)
即用小于90的人数减去小于80的人数。其它分数段统计方法相同只要把
统计条件改一下。
[5．6] 统计科目最高分和最低分：
利用函数MAX、MIN分别统计最大值和最小值。 如统计语文科目中最高分在
C65单元格内输入公式：＝MAX(C4:C55)回车即得到87.00 。最低分在C66单元格
内输入公式：=MIN(C4:C55)回车即可。

[5．7] 将百分制转换成不同的等级分：
将百分制转换成不同的等级分有多种不同的划分方法， 1是将百分制的分
数转换成A(90— —100)、B(80——89)、C(70——79)、D(60——69)与E(低于6
0)五个等 ；2是将百分制的分数转换成优(90——100)、良(75——89)、中(60—
—74)与不及 格(低于60)四个等级。具体使用哪种等级划分方法可根据实际情况
自己确定。
在百分 制转换成不同的等级分时，一般使用IF(X，Y，Z)函数。其中有三
个参数，第一个参数X为条件， 不能加引号；第二个参数为条件成立时的结果，
如果是显示某个值，则要加引号；第三个参数为条件不成 立时的结果，如果是显
示某个值，同样要加引号。该函数可以嵌套，即在第二个或第三个参数处可以再< br>写一个IF函数。
为了得到第一种方法所要的等级结果，如语文科目可以在D4单元格中输入
公式： =IF(C4>=90,
然后，利用自动填充柄将其复制到下方的所需单元格。
为了得到第二种方法所要的等级结果，语文科目可以在E4单元格中输入公
式：
=IF(C4>=90,优良中不及格
然后，利用自动填充柄将其复制到下方的所需单元格。



[5．8] 使不及格的分数以红色显示
统计学生成绩时经常需要将不及格的分数用红色显示，其结果如上图中红
色显示部分。
使不及格的分数以红色显示需要使用“格式”菜单中的“条件格式”命令。
该命令会弹出一个对话框， 其中要求确认条件与相应的格式。
对于“语文”列，可先选中C4：C55，然后使用“格式”菜单中 的“条件格
式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“小于”，
右边填写60，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确
定”按钮。
对于“等级一”列，可先选中D4：D55，然后使用“格式”菜单中的“条件
格式”命令，在弹出的对 话框中，左边使用默认的“单元格数值”，中间选“等
于”，右边填写E，然后单击右边的“格式”按钮 ，从中选择红色，最后单击两
次“确定”按钮。“等级二”列类似。

Excel电 子表格功能强大，界面直观易学易用，可以轻松地完成学科的总分、
平均分的计算；利用RANK函数排 序，可以在学生顺序不变的前提下进行学生成
绩名次排定，并且可以显示出名次并列的情况，一目了然； COUNTIF(X，Y)函数
是统计不同分数段人数的一种好方法；使用IF(X，Y，Z)函数能轻 松将百分制转
换成不同的等级分；用“条件格式”命令可以用特殊的颜色显示符合条件的一组
分 数段或某等级；总之，用Excel统计学生成绩，将会大大提高教师的工作效率
和工作质量，让我们的 教学工作变得更轻松。