应用Excel实现成绩统计与试卷分析

陈智勇 王海娥

摘要：本文介绍了应用Excel制作学生成绩考核表及试卷分析表模块的方法，为分析学生学习情况和教师教学效果提供了快捷而准确的统计软件。并根据教育测量学原理，提出了改进建议。

关键词：成绩考核；试卷分析；教育统计

1引言

成绩统计与试卷分析是教学考核工作的一个重要环节，也是一项非常复杂细致的工作。如果采取手工计算，不仅费力费时而且结果易出差错。专业的统计软件SPSS、SYS-TAT等其功能固然强大，统计分析的专业性、权威性不可否认，但是对于没有开设统计学专业的院校这些软件并不常用。学生自己也比较难以找到相应的工具。微软公司开发的Excel软件作为一款优秀的表格软件，其提供的统计分析功能虽然比不上专业统计软件，但它比专业统计软件易学易用。便于掌握。以学生试卷成绩为依据，利用Excel建立了试卷分析和成绩统计两个模块，并能够根据录入信息自动进行成绩统计和试卷分析，避免了大量、复杂的手工计算，使用效率很高。

2成绩录入模块

大量的原始成绩需要输入，一旦有误便会影响分析结果。人工多次校对也费时费力，效果得不到保证。在这里我们应用“二次校验输入法”，在格式相似的成绩表2中再次录入，和成绩表1做比较，如果不一致则自动显示出错。这种方法高效且出错率在万分之一以下。

(1)录入成绩

把成绩录入到Excel工作薄中。命名为“1”。

(2)二次校验

新建校验工作薄，命名为“2”。“姓名”和“学号”与表1布局相同，同时在每门分数后增加一列。这里以检验英语成绩为例，在D3中输入公式IF(AND(1＇!C3=C3，AND(C3＞＝0.C3＞=120))，“pass”，“error”)。如果出现单元格的数值与表1中对应的数据不一致或越界就显示错误。此时设定满分为150分。单击菜单栏上的“格式一条件格式”，设计单元格内容为“error”时显示红色。如果表2中C3单元格中输入110分，和表1的原始分数一致，D3就显示“pass”；C4单元格与表1相关单元格分数不一致时，D4显示红色“error”。

(3)锁定原始数据

点击菜单栏上的“工具”。进入“保护→保护工作表”，输入密码确定。这样做以防疏忽导致的错误。设置密码对数据处理起到保护作用。

3成绩指标分析模块

对成绩进行录入、汇总之后，新建工作表命名为“试卷分析表”，试卷分析表格式按照人数、平均分、标准差、优秀率四个方面对试卷自动进行相关数据分析。

(1)人数

人数指每门课程参加考试的总人数。在此单元格中输入“COUNT(C9:C1000)”，由于每门课程参加考试的人数不同，可以设计大于最大的人数。

(2)平均分

平均分指每门课程的平均成绩，是衡量学习成绩的重要参数。在此单元格输入“AVERAGE(C9:C1000)”。

(3)标准差

样本中各数据与样本平均数的差的平方和的平均数叫做样本方差，标准差指样本方差的算术平方根。如果标准差大，则反映学习成绩波动大，均衡的素质教育没有得到好的实现。在此单元格输入“STDEV(D9:D1000)”。

(4)优秀率

本例满分为150分，输入“COUNTIF(C9:C1000，“＞=120”)／COUNT(C9:C1000)”。

获得每个单位成绩统计数据后，数据分项放入同一表内，以完成总成绩分析数据统计。

4改进建议

根据教育统计与测量学原理，可增加以下分析指标：

(1)增加对试卷效度分析

试卷效度是反映试卷有效性和准确性的指标，即考试达到预期目标的程度。

(2)增加对试卷信度分析

试卷信度是检验考试结果稳定性和可靠性的指标。

(3)增加对试卷区分度分析

试卷区分度是指考试题目对考生水平的区分程度。

5结束语

合理而高效地进行试卷分析，对提高今后的命题组卷水平，为建立与不断完善题库，为改进考试工作都是很重要的。