2024年5月26日发(作者：曲静娴)
用Excel函数快速统计学生期中考试成绩
期中考试结束，各学科考试成绩迅速汇总到班主任这里。这时候，班主任最忙的，就是要迅
速统计各学科成绩的各项指标，比如平均分、最高分、优秀率、及格率以及各学科分数的频
率统计等等。虽然现在普遍都在使用Excel进行这项工作，不过，要想使这项工作能够高效
准确地完成，那还得请Excel函数来帮忙才行。
汇总到班主任这里的成绩表如图1所示，各科成绩分布在C2:C95单元格区域。我们先
在K2:Q15单元格区域建立如图2所示表格用以存放各项统计结果。
先点击M3单元格，输入如下公式：=AVERAGE(C2:C95)，回车后即可得到语文平均分。
点击M4单元格，输入公式：=MAX(C$2:C$95)，回车即可得到语文成绩中的最高分。
优秀率是计算分数高于或等于85分的学生的比率。点击M5单元格，输入公式：
=COUNTIF(C$2:C$95,">=85")/COUNT(C$2:C$95)，回车所得即为语文学科的优秀率。
点击M6单元格，输入公式：=COUNTIF(C$2:C$95,">=60")/COUNT(C$2:C$95)，回车所得
即为及格率。
选中M3:M6单元格，拖动填充句柄向右填充公式至Q6单元格，松开鼠标，各学科的统
计数据就出来了。
再选中M5:Q6单元格区域，点击菜单命令“格式→单元格”，打开“单元格格式”对话
框。点击“数字”选项卡，在左侧“分类”列表中选择“百分比”，如图3所示，确定后可
将M5:Q6单元格区域的数据转变成百分比形式。
至于各科分数段人数的统计，那得先选中M8:M15单元格，在编辑栏中输入公式：
=FREQUENCY(C$2:C$95,$K$8:$K$15)。然后按下“Ctrl+Shift+Enter”快捷键，可以看到在
公式的最外层加上了一对大括号。现在，我们就已经得到了语文学科各分数段人数了。在K
列中的那些数字，就是我们统计各分数段时的分数分界点。
现在再选中M8:M15单元格，拖动其填充句柄向右至Q列，那么，其它学科的分数段人
数也立即显示在我们眼前了。
最终的结果如图4所示。如果觉得K列的数据有碍观瞻，那么可以选中它们，然后设置
它们的字体颜色为白色就可以了。
在学校的教学过程中，对学生成绩的处理是必不可少的，为了在教学中
提高成绩，我们需要对学生的考试成绩进行认真的分析，这就要求我们
算出与之相关的一些数值：像每一个同学的总分及班名次、级名次，各
科分数的平均分，各科的优秀率及及格率等等，如果用Excel来处理这
些数据则非常简单，下面就我在实际工作中的一点儿经验，简单谈一谈
用Excel处理学生成绩。
一、排列名次
要用到RANK函数，它是Excel中计算序数的主要工具，它的
语法为：RANK(Number,Ref,Order)，其中Number为参与计算的数字或
含有数字的单元格，Ref是对参与计算的数字单元格区域的绝对引用，
Order是用来说明排序方式的数字(如果Order为零或省略，则以降序方
式给出结果，反之按升序方式)。例如：在E2:E50单元格区域中存放着
某一个班的总分，那么计算总分名次的方法是：在F2单元格中输入
“=RANK(E2,$E$2:$E$50)”按回车键可算出E2单元格内总分在班内的
名次，我们再选定F2单元格，把鼠标指针移 动到填充柄上按下鼠标左
键向下拖动鼠标即可算出其他总分在班内的名次。
在计算的过程中我们需要注意两点：首先当RANK函数中的
Number不是一个数时，其返回值为“#VALUE!”，影响美观。另外，Excel
有时将空白单元格当成是数值“0”处理，造成所有成绩空缺者都是最
后一名，看上去也很不舒服。此时，可将上面的公式
“=RANK(E2,$E$2:$E$50)”改为
“=IF(ISNUMBER(E2),RANK(E2,$e$2:$E$50),"")”。其含义是先判断E2
单元格里面有没有数值，如果有则计算名次，没有则空白。其次当使用
RANK函数计算名次时，相同分数算出的名次也相同，这会造成后续名次
的空缺，但这并不影响我们的工作。同样的道理，我们也可以算出一个
学生的总分在年级内的名次以及各科的班名次和年级名次，但是必须注
意参与计算的数字单元格区域不一样。
二、求各种分数
求总分：主要用SUM函数，其语法格式为SUM(Ref)，此处Ref
为参与计算的单元格区域。例如：SUM(B2:E2)是表示求B2、C2、D2、E2
四个单元格内数字的和。
另外还用到SUMIF函数，语法格式为
SUMIF(Range,Criteria,Sum_range)，其功能是根据指定条件对若干单
元格求和，参数Range表示引用，用于条件判断的单元格区域。Criteria
表示数字、表达式或文本，指出哪些单元格符合被相加求和的条件。
Sum_range表示引用，需要求和的实际单元格。注意：Criteria如果是
文本，那么引号应该是半角的，而不是全角的，否则会出错！ 求平
均分：用AVERAGE函数，其语法格式为AVERAGE(Ref)，此处Ref为参与
计算的单元格区域。例如AVERAGE(F2:F50)是求F2:F50区域内数字的平
均值。默认情况下，Excel 2002会忽略掉空白的单元格，但是它不忽略
数值为0的单元格，要想忽略数值为0的单元格需要用到COUNTIF函数，
其语法为COUNTIF(Range,Criteria)，其含义是计算某个区域中满足给
定条件的单元格数目。本例求F2:F50的平均分，如果忽略数值为0的
单元格可以这样计算：SUM(F2:F50)/COUNTIF(F2:F50,"〈〉0")。另外
如果要求去掉几个最高分和几个最低分然后取平均分的话，用到LARGE
和SMALL函数，其语法格式为LARGE(array,k)，含义是返回数组中第k
个最大值，SMALL(array,k)的含义是返回数组中第k个最小值。如果我
们求F2:F50中去掉两个最高分和两个最低分之后的平均分可以这样计
算：
“SUM(F2:F50)-LARGE(F2:F50,1)-LARGE(F2:F50,2)-SMALL(F2:F50,1)
-SMALL(F2:F50,2))/COUNTIF(F2:F50-4)”。 求最高分、最低分：
MAX和MIN函数，语法格式分别为MAX(Ref)和MIN(Ref)，如上例中求
F2:F50的最高分和最低分，应该这样：MAX(F2:F50)和MIN(F2:F50)。
三、求及格率、优秀率 求及格率：及格率即一个班级中某一科大于
等于60分的比例，例如：B2:B50中是某一个班的语文成绩，可以这样
求及格率：COUNTIF(B2:B50,"〉=60")/COUNT(B2:B50)。 求优秀率：
例如：B2:B50存放的是初一一班的语文期末考试成绩，B2:B500存放的
是初一全年级语文考试的成绩，如果规定全年级20%的学生为优秀，那
么初一一班语文的优秀率应该这样计算：“COUNTIF(B2:B50,"〉
="&&LARGE(B2:B500,INT(0.2 COUNT(B2:B500))))/COUNT(B2:B50)”，
其中LARGE(B2:B500,INT(0.2 COUNT(B2:B500)))所求的是全年级语文
分数前20%中最低的一个同学的分数，COUNTIF(B2:B50,"〉
="&&LARGE(B2:B500,INT(0.2*COUNT(B2:B500))))则是求出了初一一班
语文高于或等于这个同学分数的人数，最后再除以初一一班的总人数
COUNT(B2:B50)，所得就是初一一班的语文优秀率，如果想求其他班其
他科目的优秀率，道理都是一样。
1．考试混合编，成绩统一理--老方法遇到新问题
关于使用Excel进行学生成绩处理，已经是老话题了。但在实际工作中还是会有很
多新问题，例如，现在很多学校都是全年级各班混在一起考试，以防考试改卷中的不正
当竞争。而统计成绩时，则是将已判分但未拆封的考卷统一交到教务处，先按座位号顺
序(每本考卷的自然顺序)录入各科分数，再分析统计出全年级各科成绩。举例说明，如
图1(记录11至830隐藏了)，要统计二(1)班优秀人数，传统做法就是先按考试号排序，
再通过公式“=COUNTIF(分数!D2:D69,">=96")”求出。它的弊端是要手工逐个修改
“D2:D69”这个参数中的两个行号(2和69)，这可是一项工作量很大的工作。当然，简
单的方法还是有的，往下看吧。
图1 原始成绩表
2．初步准备--考试号里提班级
如图1，从B列的考试号中取出前三位(班级编号)放在S列，即在单元格S2输入公
式“=LEFT(B2,3)”，然后双击(或拖动)S2单元格右下角的填充柄即可。
3．再做辅表--班级等级二合一
在图1所示的工作簿中再新建一工作表，并将其命名为“等级”，在单元格A1中
输入公式“=分数!A1”，回车，选定A1，按住A1右下角的填充柄向右下拖至C840单元
格，将“分数”工作表中的姓名、考号、座位号引用到“等级”工作表中(注意，千万
不能复制粘贴过来，这样不能保持两表数据的一致性)。再选定C1，按住C1右下角的填
充柄向右拖至L1单元格，将语文、数学等9个学科科目引用过来。接着，在D2单元格
中输入IF嵌套公式“=IF(分数!D2>=96,分数!$S2&&"a",IF(分数!D2>=72,分
数!$S2&&"b",IF(分数!D2<48,分数!$S2&&"d",分数!$S2&&"c")))”。D2单元格中公式的
含义是：看“分数!D2”单元格中的分数(即“分数”工作表中李悦的语文分数)是否大
于等于96。如果是，则在D2单元格中填入“201a”——“分数”工作表中S2单元格中
的字符“201”加上“a”(“201”表示二(1)班，“a”表示成绩等级为“优秀”)；如
果不是(即小于96)，再看是否大于等于72。如果是，则在D2单元格中填入“201b”；
如果不是(即小于72)，再看是否小于48。如果是，则在D2单元格中填入“201d”；如
果不是(即小于72大于48)，则在D2单元格中填入“201c”。最后按住D2单元格右下
角的填充柄向右下拖至L840单元格，就可以将每个学生各科成绩的等级及所属班级都
填好了
4．最终统计--所需数据瞬间齐
辅表制好之后，言归正传回到“统计”工作表(如图2)中，在A17到E28单元格区
域中利用自动填充功能再制作一小块辅助数据(如图2)。
图2 “统计”工作表
万事俱备，下面开始班级总人数及优秀率、及格率等的统计了。仍以二(1)班优秀
率为例，现在就改用这样的公式了“COUNTIF(等级!$D:$D,$B17)”，即对“等级”工作
表中D列所有单元格进行统计(等级!$D:$D)，找出值为“201a”(本工作表即“统计”
工作表的$B17的值，代表二(1)班优秀率)的单元格数目。 具体做法如下：
(1)班级总人数(在B4单元格中输入)：“=COUNTIF(分数!$S:$S,A17)”；
(2)优秀人数（在C4单元格中输入)：“=COUNTIF(等级!$D:$D,$B17)”；
(3)优秀率（在D4单元格中输入)：“=C4/$B4100”；
(4)及格人数（在E4单元格中输入)：“=COUNTIF(等级!$D:$D,$B17)+COUNTIF(等
级!$D:$D,$C17)”；
(5)及格率(在F4单元格中输入)：“=E4/$B4100”；
(6)低分人数(在G4单元格中输入)：”=COUNTIF(等级!$D:$D,$E17)”；
(7)低分率(在H4单元格中输入)：“=G4/$B4100”；
到此为止，其余数据通过自动填充功能，瞬间即可完成。
5．方法点评--一表成，终年用，一劳而永逸
(1)不同年级成绩统计的简单套用：比如，首先制作好了一年级的统计表，通过复
制粘贴将第一个工作表(“分数”工作表)的内容更改为二年级的数据表，则二年级的成
绩统计便自然而成。
(2)多次考试成绩统计的简单套用：这次考试的统计表，到下次考试成绩统计时，
照用不误，只将第一个工作表换成新生的成绩记载就可以了。
2024年5月26日发(作者：曲静娴)
用Excel函数快速统计学生期中考试成绩
期中考试结束，各学科考试成绩迅速汇总到班主任这里。这时候，班主任最忙的，就是要迅
速统计各学科成绩的各项指标，比如平均分、最高分、优秀率、及格率以及各学科分数的频
率统计等等。虽然现在普遍都在使用Excel进行这项工作，不过，要想使这项工作能够高效
准确地完成，那还得请Excel函数来帮忙才行。
汇总到班主任这里的成绩表如图1所示，各科成绩分布在C2:C95单元格区域。我们先
在K2:Q15单元格区域建立如图2所示表格用以存放各项统计结果。
先点击M3单元格，输入如下公式：=AVERAGE(C2:C95)，回车后即可得到语文平均分。
点击M4单元格，输入公式：=MAX(C$2:C$95)，回车即可得到语文成绩中的最高分。
优秀率是计算分数高于或等于85分的学生的比率。点击M5单元格，输入公式：
=COUNTIF(C$2:C$95,">=85")/COUNT(C$2:C$95)，回车所得即为语文学科的优秀率。
点击M6单元格，输入公式：=COUNTIF(C$2:C$95,">=60")/COUNT(C$2:C$95)，回车所得
即为及格率。
选中M3:M6单元格，拖动填充句柄向右填充公式至Q6单元格，松开鼠标，各学科的统
计数据就出来了。
再选中M5:Q6单元格区域，点击菜单命令“格式→单元格”，打开“单元格格式”对话
框。点击“数字”选项卡，在左侧“分类”列表中选择“百分比”，如图3所示，确定后可
将M5:Q6单元格区域的数据转变成百分比形式。
至于各科分数段人数的统计，那得先选中M8:M15单元格，在编辑栏中输入公式：
=FREQUENCY(C$2:C$95,$K$8:$K$15)。然后按下“Ctrl+Shift+Enter”快捷键，可以看到在
公式的最外层加上了一对大括号。现在，我们就已经得到了语文学科各分数段人数了。在K
列中的那些数字，就是我们统计各分数段时的分数分界点。
现在再选中M8:M15单元格，拖动其填充句柄向右至Q列，那么，其它学科的分数段人
数也立即显示在我们眼前了。
最终的结果如图4所示。如果觉得K列的数据有碍观瞻，那么可以选中它们，然后设置
它们的字体颜色为白色就可以了。
在学校的教学过程中，对学生成绩的处理是必不可少的，为了在教学中
提高成绩，我们需要对学生的考试成绩进行认真的分析，这就要求我们
算出与之相关的一些数值：像每一个同学的总分及班名次、级名次，各
科分数的平均分，各科的优秀率及及格率等等，如果用Excel来处理这
些数据则非常简单，下面就我在实际工作中的一点儿经验，简单谈一谈
用Excel处理学生成绩。
一、排列名次
要用到RANK函数，它是Excel中计算序数的主要工具，它的
语法为：RANK(Number,Ref,Order)，其中Number为参与计算的数字或
含有数字的单元格，Ref是对参与计算的数字单元格区域的绝对引用，
Order是用来说明排序方式的数字(如果Order为零或省略，则以降序方
式给出结果，反之按升序方式)。例如：在E2:E50单元格区域中存放着
某一个班的总分，那么计算总分名次的方法是：在F2单元格中输入
“=RANK(E2,$E$2:$E$50)”按回车键可算出E2单元格内总分在班内的
名次，我们再选定F2单元格，把鼠标指针移 动到填充柄上按下鼠标左
键向下拖动鼠标即可算出其他总分在班内的名次。
在计算的过程中我们需要注意两点：首先当RANK函数中的
Number不是一个数时，其返回值为“#VALUE!”，影响美观。另外，Excel
有时将空白单元格当成是数值“0”处理，造成所有成绩空缺者都是最
后一名，看上去也很不舒服。此时，可将上面的公式
“=RANK(E2,$E$2:$E$50)”改为
“=IF(ISNUMBER(E2),RANK(E2,$e$2:$E$50),"")”。其含义是先判断E2
单元格里面有没有数值，如果有则计算名次，没有则空白。其次当使用
RANK函数计算名次时，相同分数算出的名次也相同，这会造成后续名次
的空缺，但这并不影响我们的工作。同样的道理，我们也可以算出一个
学生的总分在年级内的名次以及各科的班名次和年级名次，但是必须注
意参与计算的数字单元格区域不一样。
二、求各种分数
求总分：主要用SUM函数，其语法格式为SUM(Ref)，此处Ref
为参与计算的单元格区域。例如：SUM(B2:E2)是表示求B2、C2、D2、E2
四个单元格内数字的和。
另外还用到SUMIF函数，语法格式为
SUMIF(Range,Criteria,Sum_range)，其功能是根据指定条件对若干单
元格求和，参数Range表示引用，用于条件判断的单元格区域。Criteria
表示数字、表达式或文本，指出哪些单元格符合被相加求和的条件。
Sum_range表示引用，需要求和的实际单元格。注意：Criteria如果是
文本，那么引号应该是半角的，而不是全角的，否则会出错！ 求平
均分：用AVERAGE函数，其语法格式为AVERAGE(Ref)，此处Ref为参与
计算的单元格区域。例如AVERAGE(F2:F50)是求F2:F50区域内数字的平
均值。默认情况下，Excel 2002会忽略掉空白的单元格，但是它不忽略
数值为0的单元格，要想忽略数值为0的单元格需要用到COUNTIF函数，
其语法为COUNTIF(Range,Criteria)，其含义是计算某个区域中满足给
定条件的单元格数目。本例求F2:F50的平均分，如果忽略数值为0的
单元格可以这样计算：SUM(F2:F50)/COUNTIF(F2:F50,"〈〉0")。另外
如果要求去掉几个最高分和几个最低分然后取平均分的话，用到LARGE
和SMALL函数，其语法格式为LARGE(array,k)，含义是返回数组中第k
个最大值，SMALL(array,k)的含义是返回数组中第k个最小值。如果我
们求F2:F50中去掉两个最高分和两个最低分之后的平均分可以这样计
算：
“SUM(F2:F50)-LARGE(F2:F50,1)-LARGE(F2:F50,2)-SMALL(F2:F50,1)
-SMALL(F2:F50,2))/COUNTIF(F2:F50-4)”。 求最高分、最低分：
MAX和MIN函数，语法格式分别为MAX(Ref)和MIN(Ref)，如上例中求
F2:F50的最高分和最低分，应该这样：MAX(F2:F50)和MIN(F2:F50)。
三、求及格率、优秀率 求及格率：及格率即一个班级中某一科大于
等于60分的比例，例如：B2:B50中是某一个班的语文成绩，可以这样
求及格率：COUNTIF(B2:B50,"〉=60")/COUNT(B2:B50)。 求优秀率：
例如：B2:B50存放的是初一一班的语文期末考试成绩，B2:B500存放的
是初一全年级语文考试的成绩，如果规定全年级20%的学生为优秀，那
么初一一班语文的优秀率应该这样计算：“COUNTIF(B2:B50,"〉
="&&LARGE(B2:B500,INT(0.2 COUNT(B2:B500))))/COUNT(B2:B50)”，
其中LARGE(B2:B500,INT(0.2 COUNT(B2:B500)))所求的是全年级语文
分数前20%中最低的一个同学的分数，COUNTIF(B2:B50,"〉
="&&LARGE(B2:B500,INT(0.2*COUNT(B2:B500))))则是求出了初一一班
语文高于或等于这个同学分数的人数，最后再除以初一一班的总人数
COUNT(B2:B50)，所得就是初一一班的语文优秀率，如果想求其他班其
他科目的优秀率，道理都是一样。
1．考试混合编，成绩统一理--老方法遇到新问题
关于使用Excel进行学生成绩处理，已经是老话题了。但在实际工作中还是会有很
多新问题，例如，现在很多学校都是全年级各班混在一起考试，以防考试改卷中的不正
当竞争。而统计成绩时，则是将已判分但未拆封的考卷统一交到教务处，先按座位号顺
序(每本考卷的自然顺序)录入各科分数，再分析统计出全年级各科成绩。举例说明，如
图1(记录11至830隐藏了)，要统计二(1)班优秀人数，传统做法就是先按考试号排序，
再通过公式“=COUNTIF(分数!D2:D69,">=96")”求出。它的弊端是要手工逐个修改
“D2:D69”这个参数中的两个行号(2和69)，这可是一项工作量很大的工作。当然，简
单的方法还是有的，往下看吧。
图1 原始成绩表
2．初步准备--考试号里提班级
如图1，从B列的考试号中取出前三位(班级编号)放在S列，即在单元格S2输入公
式“=LEFT(B2,3)”，然后双击(或拖动)S2单元格右下角的填充柄即可。
3．再做辅表--班级等级二合一
在图1所示的工作簿中再新建一工作表，并将其命名为“等级”，在单元格A1中
输入公式“=分数!A1”，回车，选定A1，按住A1右下角的填充柄向右下拖至C840单元
格，将“分数”工作表中的姓名、考号、座位号引用到“等级”工作表中(注意，千万
不能复制粘贴过来，这样不能保持两表数据的一致性)。再选定C1，按住C1右下角的填
充柄向右拖至L1单元格，将语文、数学等9个学科科目引用过来。接着，在D2单元格
中输入IF嵌套公式“=IF(分数!D2>=96,分数!$S2&&"a",IF(分数!D2>=72,分
数!$S2&&"b",IF(分数!D2<48,分数!$S2&&"d",分数!$S2&&"c")))”。D2单元格中公式的
含义是：看“分数!D2”单元格中的分数(即“分数”工作表中李悦的语文分数)是否大
于等于96。如果是，则在D2单元格中填入“201a”——“分数”工作表中S2单元格中
的字符“201”加上“a”(“201”表示二(1)班，“a”表示成绩等级为“优秀”)；如
果不是(即小于96)，再看是否大于等于72。如果是，则在D2单元格中填入“201b”；
如果不是(即小于72)，再看是否小于48。如果是，则在D2单元格中填入“201d”；如
果不是(即小于72大于48)，则在D2单元格中填入“201c”。最后按住D2单元格右下
角的填充柄向右下拖至L840单元格，就可以将每个学生各科成绩的等级及所属班级都
填好了
4．最终统计--所需数据瞬间齐
辅表制好之后，言归正传回到“统计”工作表(如图2)中，在A17到E28单元格区
域中利用自动填充功能再制作一小块辅助数据(如图2)。
图2 “统计”工作表
万事俱备，下面开始班级总人数及优秀率、及格率等的统计了。仍以二(1)班优秀
率为例，现在就改用这样的公式了“COUNTIF(等级!$D:$D,$B17)”，即对“等级”工作
表中D列所有单元格进行统计(等级!$D:$D)，找出值为“201a”(本工作表即“统计”
工作表的$B17的值，代表二(1)班优秀率)的单元格数目。 具体做法如下：
(1)班级总人数(在B4单元格中输入)：“=COUNTIF(分数!$S:$S,A17)”；
(2)优秀人数（在C4单元格中输入)：“=COUNTIF(等级!$D:$D,$B17)”；
(3)优秀率（在D4单元格中输入)：“=C4/$B4100”；
(4)及格人数（在E4单元格中输入)：“=COUNTIF(等级!$D:$D,$B17)+COUNTIF(等
级!$D:$D,$C17)”；
(5)及格率(在F4单元格中输入)：“=E4/$B4100”；
(6)低分人数(在G4单元格中输入)：”=COUNTIF(等级!$D:$D,$E17)”；
(7)低分率(在H4单元格中输入)：“=G4/$B4100”；
到此为止，其余数据通过自动填充功能，瞬间即可完成。
5．方法点评--一表成，终年用，一劳而永逸
(1)不同年级成绩统计的简单套用：比如，首先制作好了一年级的统计表，通过复
制粘贴将第一个工作表(“分数”工作表)的内容更改为二年级的数据表，则二年级的成
绩统计便自然而成。
(2)多次考试成绩统计的简单套用：这次考试的统计表，到下次考试成绩统计时，
照用不误，只将第一个工作表换成新生的成绩记载就可以了。