用EXCEL轻松处理学生成绩的方法.docx

用EXCEL轻松处理学生成绩的方法.docx

《用EXCEL轻松处理学生成绩的方法.docx》由会员分享，可在线阅读，更多相关《用EXCEL轻松处理学生成绩的方法.docx（13页珍藏版）》请在冰豆网上搜索。

用EXCEL轻松处理学生成绩的方法

用EXCEL轻松处理学生成绩

期末考试结束后，主任要求班主任自已统计本班成绩，尽快上报教导处。

流程包括录入各科成绩→计算总分、平均分并排定名次→统计各科分数段人数、及格率、优秀率及综合指数→打印各种统计报表→制作各科统计分析图表等。

有了EXCEL，我们可用不着躬着身、驼着背、拿着计算器一个一个算着学生的成绩了！

我迅速地打开电脑，启动EXCEL2000，录入学生的考试成绩，如图1所示。

然后在J2单元格处输入公式"=sum（c2:

i2）",然后拖动填充柄向下填充，便得到了每人的总分。

接着在k2单元格处输入公式"=average（c2:

i2）"，然后拖动填充柄向下填充，便得到了每人的平均分。

平均分只需保留一位小数，多了没用。

所以选中第k列，用鼠标右键单击，从弹出的快捷菜单中选"设置单元格格式（F）…",如图2所示，在数字标签中选中"数值"，小数位数设置为1位。

下面按总分给学生排出名次。

在L2单元格处输入公式"RANK（J2,J$2:

J$77,0）",然后拖动填充柄向下填充，即可得到每人在班中的名次（请参考图1）。

　　说明：

此处排名次用到了RANK函数，它的语法为：

　　RANK（number,ref,order）

　　其中number为需要找到排位的数字。

　　Ref为包含一组数字的数组或引用。

Ref中的非数值型参数将被忽略。

　　Order为一数字，指明排位的方式。

　　·如果order为0或省略，MicrosoftExcel将ref当作按降序排列的数据清单进行排位。

　　·如果order不为零，MicrosoftExcel将ref当作按升序排列的数据清单进行排位。

最后，单击L1单元格，然后在“工具”菜单中选“排序”－＞“升序”，即可按照名次顺序显示各学生成绩。

另外，我们还希望把不及格的学科突出显示，最好用红色显示。

于是拖拉选择C2:

E78（即所有学生语、数、外三科成绩），然后执行"格式"菜单下"条件格式"命令，弹出"条件格式对话框"。

我们把条件设为小于72分的用红色显示（因为这三科每科总分为120分），点击"格式"按钮，把颜色设为红色。

再按"确定"按钮。

然后用同样的方法把理、化、政、历四科小于60分的也用红色显示（因为这四科每科总分为100分）。

下面我们来统计各科的分数段以及及格率、优生率、综合指数等。

（1）60分以下人数：

在C78单元格处输入公式"=COUNTIF（C2:

C77,"<60"）"，拖动填充柄向右填充至I78单元格处；

（2）60分~69分人数：

在C79单元格处输入公式"=COUNTIF（C2:

C77,">=60"）-COUNTIF（C2:

C77,">=70"）"，拖动填充柄向右填充；

（3）70分~79分人数：

在C80单元格处输入公式"=COUNTIF（C2:

C77,">=70"）-COUNTIF（C2:

C77,">=80"）"，拖动填充柄向右填充；

（4）80分~89分人数：

在C81单元格处输入公式"=COUNTIF（C2:

C77,">=80"）-COUNTIF（C2:

C77,">=90"）"，拖动填充柄向右填充；

（5）90分以上人数：

在C82单元格处输入公式"=COUNTIF（C2:

C77,">=90"）"，拖动填充柄向右填充；

（6）平均分：

在C83单元格处输入公式"=AVERAGE（C2:

C77）",拖动填充柄向右填充至I83；

（7）最高分：

在C84单元格处输入公式"=MAX（C2:

C77）",拖动填充柄向右填充至I84；

（8）低分率：

是指各科40分以下人数与总人数的比值。

在C85单元格处输入公式"=COUNTIF（C2:

C77,"<=40"）/COUNT（C2:

C77）*100"，拖动填充柄向右填充至I85；

（9）及格率：

语、数、外三科及格分为72分，所以在C86单元格处输入公式"=（COUNTIF（C2:

C77,">=72"）/COUNT（C2:

C77））*100"，并拖动填充柄向右填充至E86；而理、化、政、历等四科及格分60分，所以在F86单元格处输入公式"=（COUNTIF（F2:

F77,">=60"）/COUNT（F2:

F77））*100"，并拖动填充柄向右填充至I86；

（10）优生率：

语、数、外三科96分以上为优生，所以在C87单元格处输入公式"=（COUNTIF（C2:

C77,">=96"）/COUNT（C2:

C77））*100"，拖动填充柄向右填充至E87；理、化、政、历等四科80分以上为优生，所以在F87单元格处输入公式"=（COUNTIF（F2:

F77,">=80"）/COUNT（F2:

F77））*100"，拖动填充柄向右填充至I87处；如图3所示。

（11）综合指数：

我们学校的综合指数的计算公式为z=[（1+优生率－低分率）/2+及格率+平均分/该科总分]/3。

所以在C88单元格处输入公式"=（（1+C87/100-C85/100）/2+C86/100+C83/120）/3"，拖动填充柄向右填充至E88；在F88单元格处输入公式"=（（1+F87/100-F85/100）/2+F86/100+F83/100）/3"，拖动填充柄向右填充至I88。

如图3所示。

对了，为了让别人对各科的分数段有一个较直观的认识，可以考虑采用图表。

单击“插入”菜单中“图表”命令，弹出“图表向导”对话框，在“图表类型”列表框中选择一种图型，如“饼图”，单击“下一步”，单击“数据区域”文本框右边的压缩列表框，拖拉选择B78：

C82，再次点击该压缩列表框；单击“下一步”，输入图表标题，如“高一

（1）班语文成绩分析图”；单击“下一步”，再单击“完成”。

如图4所示。

其它各科同样处理，但在拖拉选择数据区域时，因为是不连续的区域，所以要按住“Ctrl”键。

好！

一切OK！

且慢！

为了以后的考试中不再重复上述繁琐的工作，最好把上述工作表另存为一个模板。

于是我把上述工作表复制一份到另一工作簿中，然后删掉所有学生的单科成绩（即表中C2:

I77部分），执行"文件"菜单中的"另存为"命令，在"保存类型"下拉列表框中选"模板（*.xlt）"，把它保存为一个模板文件，这下可以一劳永逸了。

应用Excel轻松应对特殊的学生成绩分析统计

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）多次考试成绩统计的简单套用：

这次考试的统计表，到下次考试成绩统计时，照用不误，只将第一个工作表换成新生的成绩记载就可以了。

用Excel宏命令快速处理学生成绩

在教学管理工作中，学生成绩的处理是一项枯燥烦琐的工作。

Excel2000提供了一个宏命令，利用它可以大大地缩短工作时间，提高工作效率。

　　准备工作

　　在完成某年级所有班的成绩录入之后，首先打开班级人数最多的那个成绩文件（例如，该班学生人数为70名）。

成绩表中A列为学号（A1内容为“学号”，后同）、B列为姓名、C列为语文、D列为数学、E列为政治、F列为总分、G列为名次。

　　录制宏命令

　　单击菜单“工具→宏→录制新宏”，出现“录制新宏”的对话框，输入宏名，如“高一”，选择“保存在”项目中的“个人宏工作簿”，单击确定按钮，开始录制宏命令。

　　1．计算总分并按“总分”排序

　　选中单元格C2至E2，单击工具栏上汇总命令“∑”，得出第一个学生的总分。

选中F2单元格，拖动填充柄至F71。

选中F列中任一有内容的单元格，打开菜单“数据→排序”的对话框，以“总分”为主要关键字递减，以学号为次要关键字递增，单击确定按钮。

　　2．排名次

　　排名次一般来说有两种情况，一是从小到大的顺序排序：

在G2单元格中输入“1”，G3单元格中输入“2”，选中单元格G2和G3，双击填充柄或拖动填充柄至G71；另一个是要求同分同名次：

在G2单元格中输入“＝RANKF2FF”，然后选中单元格G2，双击填充柄或拖动填充柄至G71单元格。

如果按第二种情况，还要进行以下操作：

选中G列，复制，利用“选择性粘贴”中的“数值”把G列内容复制到G列（或复制到H列后再删除G列，H列自动变为G列）。

　　3．计算全班各科平均分

　　在C72单元格输入“＝AVERAGEC2C71”，求出“语文”的平均分。

选中C72，拖动填充柄至E72，就求出了所有科目的平均分。

求总分平均分时要在F72单元格中输入公式“＝SUMF2F71/COUNTAB2B71”。

其中“COUNTAB2B71”是用来计算该班人数，不要使用如“COUNTAC2C71”，以防出错。

　　4．计算前N名学生的各科平均分

　　比如计算前30名学生的各科平均分。

在C73单元格中输入“＝AVERAGEC2C31”，求出“语文”前30名的平均分，选中C73单元格，拖动填充柄至F73，就计算出了该班前30名的各科平均分及总分的平均分。

　　5．计算达优率、及格率

　　按前两科的达优分数段为120计算，政治的达优分数段为80计算，在C74单元格中输入“＝COUNTIFC2C71″＞＝120″/COUNTA＄B2＄B71100”，在D74单元格中输入“＝COUNTIFD2D71″＞＝120″/COUNTA＄B2＄B71100”，在E74单元格中输入“＝COUNTIFE2E71″＞＝80″/COUNTA＄B2＄B71100”，就会得出各科达到优秀分数段的百分比；同样，计算及格率时在C75单元格中输入“＝COUNTIFC2C71″＞＝90″/COUNTA＄B2＄B71100”，在D75单元格中输入“＝COUNTIFD2D71″＞＝90″/COUNTA＄B2＄B71100”，在E75单元格中输入“＝COUNTIFE2E71″＞＝60″/COUNTA＄B2＄B71100”，就可以算出各科达到及格分数段的百分比。

　　6．计算各分数段的人数

　　一般按10分一个分数段。

以计算语文分数段为例，在C75单元格中输入“＝COUNTIF＄C2＄C71″＞＝140″”，D75中输入“＝COUNTIF＄C2＄C71″＞＝130″”，……H75单元格中输入“＝COUNTIF＄C2＄C71″＞＝90″”，I75单元格中输入“＝COUNTIF＄C2＄C71″＜90″”，就可以求出各分数段的人数。

求其他科目时，只需把公式中“C”换成对应的列号即可，如计算政治“＞＝90”的人数，在H77单元格中输入“＝COUNTIF＄E2＄E71″＞＝90″”。

　　7．结束录制

　　单击“停止录制”按钮，宏的录制过程结束。

　　利用所创建的宏命令

　　打开第二个班的成绩文件后，选中C72单元格使其成为活动单元格，选择菜单“工具→宏→宏”，出现“宏”对话框，在“宏名”列表框中选择刚建立的宏文件名“高一”，单击执行按钮，宏命令就会运行。

运行完毕后，第二个班的成绩就计算完了。

同样，其他各班的成绩都可以这样来处理。

　　数据的归类

　　把各班平均分和各科分数段分别应用“选择性粘贴”中的“数值”复制到对应的工作表或工作簿中，就得到了完整的各班平均分（或者各班前N名的平均分）和各分数段人数的统计表。

　　在录制“宏命令”的过程中要注意，录制的都是所有成绩文件具有共性的内容。

如果是某个文件所特有的，如按“高一·1班成绩”为名来保存文件，就不能出现在录制宏的过程中，否则就会出现错误。

录制好宏命令后，在以后的考试成绩处理中，就可以直接用来处理这个年级的成绩文件了。

这个宏命令也可以用来处理其他年级的成绩文件，如果科目数量不同或要求不同，按照这一方法再建立一个新的宏命令就可以了。

虽然建立宏命令的过程有些麻烦，但是如果能够很好利用的话，会起到事半功倍的效果。

　　注：

本文中有些公式用到“＄”，在复制公式时引用范围不会发生变化。

如计算达优率时在C74单元格中输入公式后，复制公式至E74，然后再依次修改对应的内容即可。

如采用复制公式的方法，在D74中只需把“″＞＝120″”改为“″＞＝80″”即可。

Excel统计学生成绩时的四个难题及解决

一、前言

　　对于教师而言，经常需要用Excel进行学生成绩统计，会被一些常见问题难住。

碰到的难题主要有：

如何统计不同分数段人数、如何在保持学号不变前提下进行排名、如何将百分制转换成不同分数段与如何用红色显示不及格的分数等，本文着重对这些问题的解决方法与技巧加以分析和讨论。

　　本文假设读者已对Excel的基本操作已经有一定基础，已经掌握如何进行求和、求平均和如何使用自动填充柄进行复制公式等操作，本文对这些一般性操作不做详细介绍，仅对一些难度较大的操作技巧进行讨论。

　　二、Excel统计学生成绩时的四个难题

　　假设在统计学生成绩时，我们需要统计出如图1所示的相关结果。

图1　学生成绩统计所需要的结果图

　　这里，假设学号、姓名、成绩等列及行15都已经事先输好，需要让Excel统计其他的相关数据结果。

这时，成绩统计中主要难解决的问题及它们在图中的位置如下：

　　问题1：

如何统计不同分数段的学生人数?

（图中A16~E16）

　　问题2：

如何在保持学号顺序不变的前提下进行学生成绩名次排定?

（图中F2~F13）

　　问题3：

如何将百分制转换成不同的等级分?

（图中“等级1”与“等级2”列）

　　问题4：

如何使不及格的分数以红色显示?

（图中红色显示部分，即第12行）

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

　　下面，针对上面提出的四个难题分别讨论解决的方法与技巧。

　　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,"<60"）。

　　对于其他在两个分数之间的分数段的人数统计，需要用两个COUNTIF（）函数相减。

如在A16单元格中输入公式：

=COUNTIF（$C$2:

$C$13,"<=100"）-COUNTIF（$C$2:

$C$13,"<90"），即用小于等于100的人数减去小于90的人数。

　　如果要统计80~89、70~79与60~69分数段的人数，只要利用自动填充柄将该公式复制到右边三个单元格，再把"<=100"与"<90"作相应的修改，就可以得到正确的结果。

　　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）四个等级。

具体使用哪种等级划分方法可根据实际情况自己确定。

　　在百分制转换成不同的等级分时，一般使用IF（X，Y，Z）函数。

其中有三个参数，第一个参数X为条件，不能加引号;第二个参数为条件成立时的结果，如果是显示某个值，则要加引号;第三个参数为条件不成立时的结果，如果是显示某个值，同样要加引号。

该函数可以嵌套，即在第二个或第三个参数处可以再写一个IF函数。

　　为了得到“等级1”列所要的等级结果，可以在D2单元格中输入公式：

=IF（C2>=90,"A",IF（C2>=80,"B",IF（C2>=70,"C",IF（C2>=60,"D","E"）））），然后，利用自动填充柄将其复制到下方的几个单元格。

　　为了得到“等级2”列所要的等级结果，可以在E2单元格中输入公式：

=IF（C2>=90,"优",IF（C2>=75,"良",IF（C2>=60,"中","不及格"））），然后，利用自动填充柄将其复制到下方的几个单元格。

　　4、使不及格的分数以红色显示

　　统计学生成绩时经常需要将不及格的分数用红色显示，其结果如图1中红色显示部分（如第12行）。

　　使不及格的分数以红色显示需要使用“格式”菜单中的“条件格式”命令。

该命令会弹出一个对话框，其中要求确认条件与相应的格式。

　　对于“成绩”列，可先选中C2：

C13，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“小于”，右边填写60，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。

　　对于“等级1”列，可先选中D2：

D13，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“等于”，右边填写E，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。

“等级2”列类似。

　　对于其他的一些统计计算要求，如怎样计算各分数段的百分比、如何计算机优良率与合格率等功能，应该比较简单，本文此处不赘述。