第19卷第2期收稿日期：2011-01-26基金项目：全国教育科学“十一五”规划2010年度单位资助教育部规划课题（FFB108148）作者简介：李翠梅（1974-），女，讲师，硕士，从事计算机应用教学工作和计算机应用研究。
文章编号：1005－1228（2011）02－0055－03运用Excel 实现学生成绩统计与试卷分析的程序化李翠梅（内蒙古财经学院计算机信息管理学院，呼和浩特010051）摘要：Excel 是一个具有强大数据处理功能的电子表格软件，可以高效而准确的完成对学生成绩的统计、分析和处理。
文章详细介绍了一种用Excel 2007处理学生成绩的模板，该模板的使用，能够使教师对学生成绩的处理变得程序化，从而极大的提高成绩处理的效率和准确率。
关键词：Excel ；成绩处理；模板中图分类号：TP317文献标识码：ARealizing Students'Score Statistics andExamination Paper Analysis Procedure By Use of ExcelLI Cui-mei（Computer Information Management College of Inner Mongolia Financial College,Hohhot 010051,China ）Abstract ：Excel is a strong spreadsheet software of data processing functions .It can be more efficient and accurate tofinish the score statistics ,analysis and processing.This paper introduces a kind of student score processing template with Excel 2007,using this template can make the teacher to process student score becoming stylized,thus greatly increase the efficiency and accuracy to process score.Key words:Excel ；Score Processing ；template Vol．19No．2Apr ．2011第19卷第2期2011年4月电脑与信息技术Computer and Information Technology随着高校学生人数的增加，每个学期期末教师对学生成绩的统计就变得异常繁琐。
而最为关键的是，教师的压力也很大，因为成绩的统计和分析不能出错，一旦出现错误，对学生的影响可能是很大的，而对教师自身也会产生不好的负面影响。
Excel 2007是办公系列软件之一，是M icrosoft 公司开发的专门用来制作电子表格的软件，该软件具有强大的数据处理功能，能够方便快捷地编辑生成表格数据，并对表格数据进行计算、排序、筛选、分类汇总、生成图表的功能[1]，可应用于学生成绩处理，避免错误并大大提高处理效率，辅助完成大量的、繁琐的、重复性的计算和统计工作[2]，有助于教师高效而准确的完成学生成绩的各项统计和分析工作。
笔者是一名计算机公共基础课的老师，同本教学部的老师一起，随着对Excel 的深入了解，不断探索改进，现形成了一份比较成熟的成绩处理模板，并以此文对该成绩模板做一个详细说明，以期和老师们共享。
1工作簿的结构和数据录入该成绩处理模板中，每个班级对应一个工作簿，工作簿中包含两张工作表Sheet1和Sheet2。
在Sheet1工作表中输入原始的成绩数据和计算总评成绩；Sheet2工作表中进行成绩的分析和统计。
首先对Sheet1工作表做如下说明：一般来说，一个班级的人数大约在40-60人，因此Sheet1工作表打印预览后有两页，下面分别是在打印预览视图中对这两页当中前面部分的截图，如图1和图2所示：图1成绩报告单一电脑与信息技术2011年4月图2成绩报告单二在这张工作表中涉及到模板制作的内容有：（1）整个表格框架的设计，包括表头、各个列标题、表格线、页眉页脚等。
（2）B44单元格中输入一个简单的公式“=B3”，这样当修改B3单元格中的班级时，B44单元格自动修改。
（3）总评成绩的计算：模板中已经定义好公式，教师在输入各项原始成绩后，系统便可自动计算得出总评成绩。
设计思路是利用公式计算出第一个同学（第5行）的成绩，具体的公式为“=ROUND（D5*0.2+E5*0.2+ F5*0.5+G5*0.1，1）-H5”，其中ROUND是一个四舍五入的函数[1]，格式为ROUND（number，num_didits）功能是按num_digits指定的位数，对number进行四舍五入。
计算出第一位同学的总评成绩后，用填充柄向下复制，便可得到其他同学的总评成绩。
当然，这个总评成绩的计算，可能不同课程不同老师成绩的组成和比例可能不同，因此要根据具体情况进行修改。
在录入和处理成绩时，要把握如下几个技巧：（1）“学号”列数据输入时采取自动填充，输入前两位同学的学号值确定后，拖动完成其他学生学号的录入。
（2）选定所有数据区域，通过“数据”选项卡下“数据工具”中的“数据有效性”来设置数据的有效性条件为介于0-100之间，这样在输入数据的时候可以避免因误操作而输入非法数据。
（3）在查看部分重要数据如总评成绩时，避免其他数据的干扰，可以冻结窗口，如图3，是冻结前4行和前3列（插入点置于第5行和第4列交汇的单元格中，选择“视图\窗口”下的“冻结拆分窗格”）后查看总评成绩的效果。
图3冻结效果（4）要突出显示一些成绩，如不及格的同学或者90分以上的优秀同学，可以设置条件格式。
条件格式属于样式的一种。
如果不再对成绩进行统计、分析和生成图表，到这里一个简单的成绩报告单就已经做出来了。
而如果还要进一步的处理成绩，则就需要我们工作簿中的Sheet2工作表了。
2成绩的分析和统计对一个班的成绩分析可以有多项内容[4]，我们的模板中包含三项内容：成绩分段统计、成绩分析图、试卷分析。
而对于教师来说，这个工作表中是不需要做任何工作的，只要把Sheet1工作表的原始数据录入，Sheet2工作表中统计分析的结果会自动生成，非常方便快捷。
2.1成绩分段统计表分段统计表中按照期末笔试成绩和总评成绩分别统计了参加考试的人数（注意：缺考同学不能录入0分，否则影响统计结果，保留空白就可以了），不同分数段的学生人数、最高分、最低分、不及格人数、及格率和缺考人数，如图4所示：图4成绩分段统计表在Sheet2工作表中A1是一个合并若干列的单元格，在该单元格中定义了一个公式如下：=MID（Sheet1!B3，4，LEN（Sheet1!B3））&"《Visual Basic+SQL Server》期末成绩及总评成绩分段统计表"这样做的目的是保证班级信息和Sheet1中一致。
其中LEN（Sheet1!B3）计算的是Sheet1中B3单元格字符的个数，包含空格；MID函数是一个求字符串子串的函数[1]，本公式是从第4个字符起，对Sheet1中B3单元格提取字符串，提取的长度由LEN函数值确定。
整个公式计算的结果正好是Sheet1中B3单元格里的具体班级信息。
期末成绩和总评成绩统计的方法相似，只是定义公式中涉及的单元格区域有所区别。
因此本文以总评成绩为例来介绍各个统计项目。
（1）总人数人数是用公式“=COUNT（Sheet1!I5:I42,Sheet1!I47: I77）”来统计的，其中COUNT是一个计数函数，统计的是数字类型的单元格个数。
·56·第19卷第2期（2）分数段统计分数段的统计也都定义好了公式，0-39分数段统计的公式为：=SUM（COUNTIF（Sheet1!F5:F42，"<40"），COUNTIF （Sheet1!F47:F77，"<40"））后面分数段的统计方法基本相同，只需要把前面分数段统计的人数减掉就可以了，如50-59分统计公式为：=SUM（COUNTIF（Sheet1!F5:F42，"<60"），COUNTIF （Sheet1!F47:F77，"<60"））-SUM（D4:E4）其中SUM是求和函数，COUNTIF的功能是统计指定区域中满足条件的单元格个数。
（3）最高分、最低分和平均分最高分、最低分和平均分分别用MAX、M IN和AVERAGE函数来统计，定义公式如下：=MAX（Sheet1!F5:F42，Sheet1!F47:F77）=MIN（Sheet1!F5:F42，Sheet1!F47:F77）=AVERAGE（Sheet1!F5:F42，Sheet1!F47:F77）（4）不及格人数、及格率和缺考人数的统计这三项统计内容很简单，分别用公式“=SUM（D4：F4）”、“=SUM（G4:K4）/C4”和“=COUNT（Sheet1!B5:B42，Sheet1!B47:B78）-C4”来完成，涉及到的函数都是前面用过的，这里不再描述。
2.2成绩分析图根据分段统计的结果，生成图表中的折线图，如图5所示：图5成绩分析图将工作表中的数据以图表的形式表示，以图形、曲线来表示数据值的大小和数据间的相互比例关系[1]，比单纯的数据表示更形象、生动和直观。
2.3试卷分析试卷分析对考试时间和形式、试题情况、评分方法、成绩分布等作了详细说明。
图6是试卷分析一部分的截图。
图6试卷分析其中，A45这个单元格中定义了一个公式：="6.期末及总评成绩总体呈正态分布，期末平均分为"&ROUND（N4，2）&"，及格率为"&ROUND （P4*100，2）&这样便可以动态的保证期末笔试和总评成绩的平均分和及格率与上面成绩分段统计表中一致，从而避免手工填写的繁琐和容易出错的问题。
3讨论和总结我院计算机公共基础课是必修课，在大学一年级开设，第一学期讲授的是计算机文化基础，第二学期开设数据库应用（VB+SQL）。
这两门课程的考核工作都是比较复杂的，中间要有几次上机考试，平时还要交作业记录一个平时成绩，期末要进行笔试。
所以对于计算机公共基础课教学的老师，学期期末汇总、统计和分析学生的成绩是一项非常繁琐而又是非常重要，不能有丝毫马虎的事情。
用Excel制作的这个成绩报告单的模板，在我院计算机公共基础课的成绩综合评价中得到了很好的应用，效果是非常明显的。