试卷分析系统的Excel VBA实现

张祖芹，丁向民

（盐城师范学院信息科学与技术学院，江苏盐城 224002）

试卷分析系统的Excel VBA实现

张祖芹，丁向民

（盐城师范学院信息科学与技术学院，江苏盐城 224002）

为了减少试卷分析过程中的重复、繁琐、不规范等情况发生，利用VBA技术在Excel中开发了一种试卷成绩分析系统.该系统利用试题难度、区分度、班级平均分和标准差等参数对试卷进行分析，然后利用数值参数评价表将参数值转化成具体分析的文字表达，最终自动生成集数值、文字、图形为一体的试卷分析表.

Excel VBA；试卷分析；成绩系统

0 引言

试卷分析是课程考核后进行的一项常规工作，通过试卷分析可以了解学生对课程的学习情况，掌握教师的课程教学效果.所以，科学合理的试卷分析方法对教学质量反馈至关重要.目前，很多高校都采用发放一定格式的空白试卷分析表，由教师自行填写，由于教师对测量理论和评价方法的了解不够充分，使得试卷评价过程存在很大的主观性和随意性，降低了试卷分析评价的科学性［1］.本文对高校教师经常使用的成绩分析工具——Excel进行二次开发，利用VBA（Visual Basic for Applications）开发了一种通用成绩分析系统，对高校课程教学分析评价具有积极的应用价值.

1 问题的提出

一般试卷分析表包括三部分内容：试卷考查科目的基本信息、试卷成绩的统计分析和存在的问题及改进措施.试卷考查科目的基本信息主要包括考试科目、考试时间、应考人数、命题人等，这类信息比较简单.试卷成绩的统计分析是针对试卷每道题或每类题的具体分析，分析参数包括很多种，主要有平均分、满分人数、零分人数、各分数段的人数等，用于反映学生学习的不同问题.存在问题及改进措施是利用试卷的分析结果来评判试题的难易程度，为教师改进教学提供参考.

目前，教师进行试卷分析存在以下一些问题：

1）重复工作多，分析效率低.教师利用Excel软件进行试题分析时，每门科目都要进行常规的求最高分、最低分和平均分，统计各分数段人数和比例等重复性工作.如果所教课程或班级较多，这种处理方法的工作效率就很低.

2）水平差异大，分析难度高.对于教师而言，掌握Excel软件数据输入、制表、数据排序和简单公式运用等操作比较容易，但进行分数段的人数统计、难度和区分度分析、正态分布相关性分析等复杂操作则难度不小.教师计算机处理动手能力差异较大，特别是年龄较大的老教师，熟练使用Excel软件有一定困难.

3）重视程度不同，分析差异显著.不同教师对成绩分析的重视程度不同，其具体分析内容差别比较明显.部分教师仅对试题基本参数进行分析，没有对成绩进行难度、区分度、成绩正态分布等情况进行实际计算，造成试卷分析内容不够全面规范，不能客观反映学生考核情况.不认真进行数据分析，无法发现存在的问题和写出合理的改进措施，只能用非常模糊的语句，如“试卷整体情况良好”、“今后在教学上还需要进一步改进”等来表示存在问题和改进措施.

根据上述情况，建立一种通用试卷分析系统，对试卷进行自动数据处理和辅助质量评价，从而减少考试成绩处理工作量，提高试卷分析的科学性.

2 试卷成绩分析表分析

一般成绩分析表具体包含以下几个栏目：一是试卷科目的基本信息，主要有考试科目、班级、应考人数、实考人数、考试时间、命题人、考试题型、题量等；二是试卷成绩统计分析，主要有各种题型平均分、各分数段人数、全对人数、全错人数等；三是成绩分析结果的图形化显示；四是存在问题及改进措施.其中，栏目一不需要进行任何处理，栏目二、三和栏目四分析方法如下.

2.1 试题成绩统计分析

主要涉及的参数有：班级平均分、全对人数、全错人数、各分数段人数及百分比，计算公式相对比较简单：

班级平均分：

各分数段人数百分比：

2.2 成绩分析表

概率论的中心极限定理表明：在自然界中，大量随机现象都服从或近似服从正态分布．教育统计学研究同样发现：在满足考试无作弊、试题难度和区分度适中、学生人数足够多三个条件下，每个学生的考试成绩可看作相互独立的随机变量，这时考试成绩一般都符合正态分布．通过观察学生的考试成绩是否服从正态分布，可以非常直观地判断教学过程的合理性，从而进一步分析实际教学过程存在的不足，并找出其中原因，采取相应措施，改进教学质量．

假设实考学生人数有n个，成绩按百分制计算，用ξk表示第k个学生的成绩，则ξk为取值在［0，100］区间中的随机变量．当n足够大时，变量ξk近似服从正态分布n（a，σ2），其中a表示平均分、σ2表示方差［2］．通常一个班级的人数样本数量不够大（n为50左右），而试卷分数的分布区间是［0，100］，因此从每个分数去考察没有意义，本文按区间间隔为10的分数段进行比较分析．

2.3 具体指标设计

着重选择4个参数进行分析：试题难度、区分度、班级平均分和标准差．

1）试题难度可以评价试卷题目的难易程度，可以用得分率来表示，其值域为［0，1］，数值越大，说明试卷（题）越容易．难度d的定义为：

2）区分度反映的是试卷对不同水平学生加以区别的能力．如果试卷区分度较好，则水平高的学生应该得到高分，水平低的学生应该得到低分．本文采用极端分组法［3］，将成绩在前27%的学生成绩作为高分组XH、成绩在后27%的学生成绩作为低分组XL，则试题的区分度q可定义为：

3）班级平均分为班级所有同学的均分，反映了全班同学知识掌握的整体水平，值越大，说明学生知识掌握情况越好．其均分X的定义为：

其中Xi为每位同学的分数，N为班级人数．

4）标准差反映的是班级同学得分的离散程度［4］，值越大，说明班级同学的成绩分布差异越大．标准差S的定义为：

为了将实际的参数数值转化为更加直观的评价标准，本文定义了4个数值评价表：试题难度评价表、试题区分度评价表、平均分评价表、标准差评价表．评价表利用专家经验的方法将参数数值转化为评价的等次．

3 试卷分析系统的实现

3.1 试卷分析系统实现的流程

由于Excel本身可以看成是数据库，也可以看成是开发工具，所以其开发流程与传统流程稍有不同［5］.一般的开发流程如图1所示.

图1 Excel开发信息系统的一般流程

Excel的初始界面是工作表.工作表在数据输入、显示方面能较好地满足用户需求，但在交互方面则不能很好地满足用户需求.Excel提供了用户窗体（UserForm），可以实现按钮、文本框、选择列表等，结合Excel工作表，可以较好地满足信息系统的界面设计.

由于Excel工作表既是界面，也是需要打印的报表，所以Excel界面一旦设计完成，就不需要用户修改.但Excel工作表很容易被用户修改，为了解决这个矛盾，需要对界面进行保护.界面保护的类型主要有两类：一是鼠标的响应，即限制鼠标在工作表的某些部分进行点击；二是工作表的操作，即限制对工作表某些部分进行操作，主要包括单元格的格式设置、插入或删除单元格的行列等.

算法设计主要是对系统的流程和核心部分进行设计，根据算法设计进行宏代码编写，最后进行系统测试.

3.2 试卷分析系统实现的核心算法

读入原始成绩及参数是系统实现的第一步.本系统一共设计了两张表，第一张表为成绩表，主要是让教师输入原始成绩，主要栏目有学号、姓名和各科成绩.第两张表为分析表，主要是让教师输入试卷的相关参数和存放分析结果.系统首先根据班级学生人数（SNumber）和考试题型数目（INumber）生成一个动态二维数组Score（SNumber，INumber＋1），然后将成绩表中的数据读入该数组中（数组的最后一列用以存放每位同学的总成绩）.然后再定义一个变量ExamNumber用以存放实考人数，一个数组ItemsScore（INumber）用以存放每种类型题目的满分.

图2 系统实现的核心算法流程

检查原始数据的主要任务就是检查输入的成绩表中是否有输入错误的情况，主要被认为错误的情况有：单元格为空白，单元格数据为负值，单个题型的分数超过该类题型分数的满分值，有成绩人数与实考人数不一致.如果存在以上情况中的任一错误，系统则给出错误提示，并中断程序.

统计基本参数的主要任务是计算分析表中的各种参数，主要包括每种题型的平均分、全对人数、全错人数、总分中各个分数段的人数、缺考人数、试卷难度、试卷区分度、成绩标准差等.该部分是系统的核心.

绘制成绩分布表是根据总分中各个分数段的人数绘制成绩分布曲线，从而更直观地观察学生成绩分布状况.

书写具体分析是根据计算出来的基本参数，形成试卷的文字性分析结果，其主要思想是根据参数评价表形成文字描述.

4 小结

本系统的程序设计和调试在Excel 2007软件的VBA编程环境中进行，一共定义了两个宏，一个宏对应于界面上的“导入题型信息”按钮，主要完成将成绩表中的题型信息读入分析表以及系统变量的初始化工作；另一个宏对应于“生成分析表”按钮，用以填充分析数据.实践表明，通过试卷分析系统生成的分析表，不仅使考试情况分析过程更为高效、全面、规范，而且极大避免了人工操作可能产生的误差等情况发生.

［1］ 王华，陈景，马翠芹.基于CTT与IRT的试卷质量评价系统设计与实现［J］.计算机工程与设计，2013（5）：1826-1830

［2］ 李勇，宋晓霞.通用试卷分析系统的设计与实现［J］.中国教育信息化，2008（4）：55-57

［3］ 罗海凤，谢垂益.基于Excel VBA的高校考试质量分析模板［J］.韶关学院学报，2013（2）：76-80.

［4］ 娄青.基于Excel VBA开发普通高中成绩分析系统［J］.中国教育信息化，2012（10）：90-96.

［5］ 毛文兵.基于Excel和VBA的信息系统开发技术研究［J］.沿海企业与科技，2008（11）：81-85.

Design and Im p lementation of the Test Results Analysis System of Based on Excel VBA

ZHANG Zu⁃qin，DING Xiang⁃min
（School of Information Science and Technology，Yancheng Teachers University，Yancheng Jiangsu 224002）

In order to solve the test paper analysis process of repetitive，tedious，non⁃standard situations，a test score analysis system is developed based on VBA technology in the Excel.The system is based on⁃Yancheng Teachers University examination paper analysis.The system analysis content includedifficulty，dis⁃crimination，class average and the standard deviation of parameters on the paper.The test paper analysis table is created byconvertingthe parameter value expressionto the analysis text usingnumerical parameters evaluation table，including a set of numerical，text，graphics.

Excel VBA；analysis of test paper；score system

TP391.13

A

1671⁃6876（2014）03⁃0231⁃04

［责任编辑：蒋海龙］

2014⁃04⁃16

张祖芹（1976⁃），女，江苏东台人，讲师，硕士，主要从事教育技术、计算机应用研究.E⁃mail：yctczzq＠163.com