用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 或省略，Microsoft Excel 将 ref 当作按降序排列的数据清单进行排位。
·如果 order 不为零，Microsoft Excel 将 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）"，把它保存为一个模板文件，这下可以一劳永逸了。
统计项目Excel2003来帮忙
运行Excel2003，在成绩表第一行中输入以下内容：学号、姓名、各考试科目名称、总分、名次，输入学号时，用填充柄自动填充。在学号下方依次输入“总人数”、“及格人数”、“及格率”、“总分”、“均分”、“最高分”、“最低分”。
各段成绩格式不同
选定所有录入学生成绩的单元格，进入“格式→条件格式”，在“单元格数值”范围中填入“80”与“100”，单击“格式”按钮，将单元格数值设置为自己喜欢的格式：如数值颜色为蓝色、字形为倾斜等，这样，在80到100之间的分数在输入后自动变为蓝色并倾斜。确定，返回条件格式对话框，单击“添加”，设置60到79、小于60分等分数段的格式。
个人总分
单击总分下的单元格，在编辑栏中输入=SUM(C2:H2)，在下一个单元格中输入=SUM(C3:H3)，然后选定这两个单元格，将光标移动到两个单元格的右下角，到变成细十字，按住鼠标左键向下拖拉至最后一名学生，以后只要输入学生各科成绩，每位学生的总分即自动求出。
个人名次
在Excel中可以用单元格自动排序的方式给成绩排序，但不好处理同分数的名次，利用RANK函数则没有这样的问题。
方法是：总分位于I列，名次位于J列，在第一名学生的名次单元格中输入=RANK(I3,$I$3:$I$59)，该公式的目的是排出I3单元格的数据从I2到I59所有数据中的位次(假设该班共56名学生，学生的姓名、成绩等数据从第3行排到第59行)。在第二名学生的名次单元格中输入=RANK(I4,$I$3:$I$59)，然后拖动鼠标，选中这两个单元格，利用自动填充的功能，在每个学生的名次单元格中输入RANK函数的公式。这样只要将学生各科成绩输入到对应的单元格中后，RANK函数即自动求出该学生的名次，同分数的名次则自动排为相同的名次，下一个名次数值自动空出。
各科逐一分析
以“语文”为例。在“总人数”单元格中输入公式=COUNT(C3:C59)，在“及格人数”单元格中输入=COUNTIF(C3:C59,">=72")，在“及格率”单元格中输入=COUNTIF(C3:C59,">=72")/COUNT(C3:C59)，[满分为120分为">=72"，若满分为100分则为">=60"]，“总分”单元格中输入=SUM(C3:C59)，“平均分”单元格中输入=AVERAGE(C3:C59)，“最高分”输入=MAX(C3:C59)，“最低分”单元格输入=MIN(C3:C59)。以上公式输入后可利用自动填充功能填充其他各科的公式。
每科各分数段人数
可用FREQUENCY函数来实现。在（A67：A74）区域输入39、49、59、69、79、89、99、109（即统计出40分以下~120分范围内组距为10的各分数段人数），同时选中c67至c75单元格区域，输入公式:=FREQUENCY(C3:C59,$A$67:$A$74)，按下“Ctrl+Shift+Enter”组合键进行确认，即可求出c67至c75区域中，按A67:A74区域进行分隔的各段数值的出现频率数目。其他单元格可用自动填充功能实现。
有时要将优秀人数和不及格人数统计出来，在语文优秀人数的单元格中输入公式=COUNTIF(C3:C59,“>=80”)(假设各科的满分为100分)。及格人数改为>=60，不及格人数改为<=59即可。这样Excel就自动统计出各分数段人数了。
保存为模板文件
在“打印预览”中，按下“设置”，单击“工作表”标签，选中打印风格线，单击“页眉/页脚”标签，单击“自定义”，设置表格标题、页眉、页脚、纸张的大小、页边距，调整字体、字号、单元格的宽度及高度，然后“另存为”为“模板”，取名为“成绩统计”，确定，保存为模板文件。
最后，我们就可以使用已建立的模板来处理学生的成绩。
启动Excel，执行“文件”菜单中的“新建”，在对话框中出现的“常用”标签下找到已建立的“成绩统计”模板，双击，出现了原来的模板文件，输入学生新的成绩总分、各科的平均分、各分数段的人数，最高分、各学生的总分名次即自动统计得出。最后打印输出，既美观又便于分析。
_xyz