基于Excel VBA的高校考试质量分析模板

罗海凤，谢垂益

（1.韶关学院 政治与公共事务管理学院，广东 韶关512005；2.韶关学院 数学与信息科学学院，广东 韶关512005）

课程考核是高校的常规教学工作，在每学期结束前，教师都要对所教班级的课程进行考核，填写课程成绩，进行考试质量分析.考试成绩主要反映学生对课程知识的掌握程度和教师的教学效果.通过采取科学的手段对考试成绩进行分析，能够客观、合理地反映教学质量的好坏，还可以对教学管理工作进行有效的监督和指导.通过对当前国内一些高校课程考核的成绩处理和考试质量分析过程的分析，发现存在工作效率低、操作难度高、质量分析不够科学等问题.因此，本文提出基于Excel对象模型和VB应用编程(Visual Basic for Applications，VBA)语言编制考试质量分析模板，进行考试成绩的统计和后期分析，自动生成Word文档报表.

1 问题背景

考试质量分析主要包括试题概况、考试成绩统计分析、教学成功经验、存在问题和改进措施等内容.通过分析韶关学院和调研其他一些高校的课程考核成绩记录和考试质量分析过程，发现各高校基本都是用Excel软件或者教务管理系统来进行成绩录入和报表生成，目前存在一些问题：

（1）工作效率低.多数教务管理软件只提供成绩录入和报表，通常要借助Excel软件进行考试质量分析.一些教师习惯先对课程成绩进行排序，再求最高分、最低分和平均分，统计各分数段人数和比例等，在这过程中经常采用手工计数、用计算器进行辅助计算，还需要对数据统计结果重复验证检查，避免计算错误.如果所教课程或班级较多，由于对每个班都要重复一次枯燥的成绩处理和分析过程，工作效率低.

（2）操作难度高.Excel软件的公式复杂，对计算机操作技能的要求较高，一般教师难以熟练使用.目前计算机和网络已经比较普及，虽然多数教师都掌握了使用Excel软件进行数据输入和制表，但只有一半左右的教师会使用数据排序和简单的公式，很少教师能够运用公式进行分数段人数统计、难度和区分度分析、正态分布相关性分析等复杂操作.

（3）质量分析不够科学.考核分析是课程考核成绩分析表的重要内容，许多教师没有对考试成绩进行难度、区分度、成绩正态分布情况等进行实际计算,分析过程较为粗略，质量分析内容不够全面规范，不能客观的反映考核情况.比如有的试题区分度不够，却写成“区分度较好”;学生成绩分布不符合统计学上的正态分布，没有去分析形成原因或是否合理，却简单地写为“成绩分布合理”.

根据现实中存在的问题，考虑对目前普遍使用Excel软件处理考试成绩和质量分析的方法进行改进，设计一个考试质量分析模板来进行自动数据处理和辅助质量评价，减少考试成绩处理工作量、使计算机软件操作更平民化、确保数据准确性、提高质量分析结论的科学性.

2 研究现状

2.1 考试成绩的正态分布特征

根据概率论中的中心极限定理，自然界中大量随机现象都服从或近似服从正态分布.教育统计学的研究发现，如果试题难度和区分度适中、考试过程无作弊现象、学生数量足够多，每个学生的考试成绩将是相互独立的随机变量，考试成绩一般都符合正态分布，满足“两头小、中间大”的特点.成绩符合正态分布的理论已被国内绝大多数教育界同行认可.通过考察学生的考试成绩是否服从正态分布，可以判断教学过程是否正常进行.如果学生考试成绩符合正态分布,就认为教学过程是正常的，试卷的难易程度合理;否则，可能在教学过程的一些环节出现了问题，可以结合其它的统计指标，分析实际教学过程，找出偏离正态分布的原因，采取相应的措施，改进教学质量.

2.2 考试质量评价理论

近年来很多科研人员对评价学生考试成绩的具体理论和方法进行了深入研究.文献［1］指出，当学生水平差别较大时，若发生考场作弊现象，由于考生之间的考试成绩具有相关性，成绩分布有可能不服从正态分布;文献［2］对考试难度和区分度的选择、不同性质考试和招生决策对试卷难度控制的要求、理想的考试分数分布等问题进行了探讨;文献［3］提出学生成绩正态分布的形成条件,分析了数学期望和均方差的变化对正态曲线形状的影响,依据学生成绩的分布曲线判断教师讲授与学生学习两方面的质量准则;文献［4］将试卷难度分成5个等级，根据难度和区分度将试题分为优良题、较好题、一般题、差题四个级别，统计多个专业、年级课程试卷答卷情况，分析考试成绩分布，评价试卷质量，以便反映课程教学过程，提高命题的质量和科学性;文献［5］对学生的英语四级模拟考试成绩进行分析，统计了各分数段的人数与比例、最高分、最低分和整体分布情况，用信度、效度、难度和区分度等度量值对试卷的质量进行定量分析.文献［6］利用数理统计的理论与方法，采用检验法对学生考试成绩进行分析，提出运用偏度、峰度检验法对学生考试成绩进行评估的方法.

2.3 基于Excel VBA编程的成绩处理和质量分析

VBA是一种宏语言，主要用来扩展Microsoft Office软件(包括Excel、Word、PowerPoint等)应用功能.Office软件中几乎所有需要使用鼠标、键盘或对话框执行的操作都能使用VBA编程来执行.使用VBA可使Excel软件的应用更加高效，例如实现画面的切换、格式报表的输出、复杂逻辑的统计等.通过执行VBA代码可快速实现多个步骤的手工操作，规范用户的操作，使操作功能更人性化、更方便用户尤其是初级用户使用.尽管开启Office软件的宏功能有感染宏病毒的风险，但目前几乎所有杀毒软件都能对宏病毒有效地查杀，因此VBA编程的方法是安全可控的.根据高校考试质量分析的需求，采用基于Excel内置的VBA语言来辅助进行考试成绩处理和质量分析，是一个很合理的选择.文献［7］实现了一个成绩分析模板，统计分数段人数和每题得分率;文献［8］设计的管理模板完成实验成绩的按学生汇总、考试成绩的统计和报表生成、统计各分数段人数和比例;文献［9］设计的课程考核成绩分析样板能够自动统计成绩分布状态、绘制成绩分布曲线图、根据教师调查问卷生成课程考核分析结论;文献［10］认为期望成绩评定应该表现为完全正态分布的绝对化观点是不科学的，负偏态分布(高分人数较多、平均分较高的情况)也具有合理性，并设计了一个考试成绩分析系统，利用统计学中的偏态和峰度指标分析成绩的总体分布.

2.4 存在的问题

以上文献的研究存在一定不足：没有对考试成绩的正态分布情况提出定量的测定标准;没有将计算机处理结果与教师主动评价意见有效结合，只是机械地输出数据计算结果或图表，分析结论不够全面;很少考虑将结果整体打包输出以简化用户的操作，仅仅是输出各种单项处理结果.

3 实现方法

3.1 实现过程概述

以韶关学院为例，对于一般课程，总评成绩(FS)由平时成绩(US)和考试成绩(TS)组成，三个成绩均为整数、最低分0分、满分100分.总评成绩(FS)的计算公式是：

需要完成的任务是：创建一个Excel文件（下面称为“Excel模板”），在文件中嵌入VBA程序代码来完成考试质量分析功能.用户只需将学生的成绩数据输入或者粘贴到Excel模板中，点击鼠标按钮即可完成成绩统计和质量分析、生成Word报表.

任务分解：分为基本数据统计、试题难度和区分度分析、成绩分布分析、分析意见生成、Word文档报表导出五个模块，通过在Excel模板编写VBA程序分别予以实现。

3.2 基本数据统计

基本数据统计的内容包括：最高分、最低分、平均分、各分数段人数及比例.由于VBA语言本身没有进行统计类的相关函数，求最高分、最低分、平均分是通过Application对象中的WorksheetFunction属性调用Excel的内置函数进行计算.

为了统计各分数段人数及比例，以及方便后续的其他数据处理，定义了一个整数数组sect(100)，数组元素sect(i)表示得分为i的人数.将取值范围为0至100的全部考试成绩读一遍，同时对sect()中的元素进行累加，即可统计出每个分数的人数.

图1 成绩统计

对数组sect()的部分元素分别求和，就得到考试成绩在各分数段的人数.本文以有70人参加考试的某课程成绩作为案例，Excel模板中程序的统计结果如图1所示.

3.3 试题难度和区分度分析

难度是指试卷(题)的难易程度［2］，通常用难度系数即试卷(题)的得分率或答对率表示，其值在0～1之间，数值越大，说明试卷(题)越容易.因此，试题的难度d定义为：

区分度是指试卷对不同水平的学生加以区别的能力［5］.对于具有良好区分度的试卷，实际水平高的学生应该得到高分，水平低的学生应得低分.采用极端分组法［10］，将成绩最高的27%的学生作为高分组、成绩最低的27%作为低分组，则试题的区分度q定义为：

对3.2节中的数组sect()进行进一步处理，从下标0开始向后计算sect(i)的累加和，当累加和达到学生总数的27%时，对应的数组下标即为低分组学生的分数分界点，从而容易得到.类似地，从下标100开始向前计算可以求得.

从教育测量学的角度，试题难度为0.5较为合适，因为此时均值为中位数，如果试题没问题、考生成绩相互独立，则分布为标准正态分布，考试的区分度最大.但是，高校的课程考试本质上是一种成就测验，属于合格水平考试而不是选拔性考试，目的是考查学生是否达到预定的教学目标和要求，需要考虑考试对学校教育和教学的影响以及社会认同度.因此，试题的难度值以0.7左右为宜(意味着学生考试成绩在70分上下的比例最大).综合参考文献［4-5，10］的经验和作者的工作体会，本文提出高校学生考核试题难度和区分度的评价标准，见表1和表2.

表1 试题难度评价标准

表2 试题区分度评价标准

对于3.2节中的案例，通过Excel模板中程序计算得到试题难度系数为0.70，结论为“适中”;试题区分度为0.66，结论为“很好”.

3.4 成绩分布分析

根据考试成绩符合正态分布的理论，假设有n个学生参加考试，成绩以百分制计算.用ξk表示第k个学生的成绩.则ξk是取值在区间［0，100］中的随机变量.当n足够大时，ξk近似服从正态分布 N(a,σ2),其中 a 表示平均分、σ2表示考试成绩的方差［1］.由于一个班级的人数通常在50人左右，样本数量不多，而考试分数的区间是［0,100］，因此从每个分数去考察是否服从正态分布是没有意义的，应该按分数段去进行比较.根据图1的分数段和数据(还需要计算出方差)，求得对应的正态分布函数在各分数段的概率密度积分，以分数段为横轴、人数比例为纵轴，在Excel模板中绘出实际考试成绩与正态分布的对比图，见图2.

图2 实际分布与正态分布的对比

表3 正态相关度评价标准

为了比较考试成绩在各分数段的人数分布与正态分布的相似程度，计算Pearson乘积矩相关系数(r)：

r是一个区间范围为［-1.0,1.0］的无量纲值，反映了两个数据集合之间的线性相关程度.式(4)可直接使用Excel中的Pearson()函数进行计算.在本文中r为数据序列与正态分态序列的线性相关程度，称之为“正态相关度”.从图2中观察发现两条曲线的走势比较接近，实际计算得到正态相关度为0.92（非常高），从中得到印证.

根据对本校近年来多个班级考试成绩数据的分析验证，提出正态相关度(r)的评价标准，见表3.

3.5 分析意见生成

考试质量分析评价的要求可分为试题内容、答题分析、平时表现、教学总结等方面.试题内容包括试题对知识点的涵盖面、与教学大纲中重点、难点、知识点要求的相符程度、试题难度和区分度等;答题分析包括成绩分布情况、学生应用知识分析问题和解决问题的能力、学生创造性思维的表现情况等;平时表现包括班级学习风气、考勤情况、完成作业情况等;教学总结包括教学成功经验、教学工作应改进的方面等.通过结合数据分析与问卷调查的方法，在Excel模板中自动形成考试质量分析评价的初步意见，为导出为Word格式的报表做内容上的准备.

图3 质量分析问卷界面

通过数据分析自动形成的内容有：试题难度和区分度、考试成绩分布情况.对3.2节的案例，根据前三节的数据分析评价，自动形成以下文字评价意见：“试题难度适中”、“试题区别度很好”、“考试成绩与正态分布的相符合程度很高”.

考试质量分析评价意见的大部分内容通过问卷询问的形式得到.问卷的问题分为两大类：选择题和文字题.选择题可采用单项或多项选择的形式，文字题采取内容填写的形式.在Excel模板中进行设计，采用Frame控件进行控件分组，每道题由一个Frame控件和若干个其它控件组成.单选题通过OptionButton控件实现;多选题通过CheckBox控件实现;文字题通过Text控件实现.教师通过回答问卷中的选择题和输入少量文字，即可很快形成标准规范的分析评价意见.图3是质量分析问卷的界面.

通过数据分析结论与教师主观意见的结合，能够形成更为科学、合理的考试质量评价意见，更好地总结教学中的成绩经验和不足之处，达到通过考试质量分析来促进教学质量的目的.自动评价与问卷询问相结合的方法，可以节省教师撰写分析意见的时间，提高工作效率.

3.6 Word文档报表导出

将前四节的输出结果组合，导出到Word文档，将形成一份完整的“考试质量分析表”.“考试质量分析表”文档为8开纸张大小，内容如图4所示.事先将这个表保存为Word模板，由Excel模板中的VBA程序控制进行写操作，根据Word模板的规定位置输出相应的质量分析内容.VBA程序的主要步骤如下：

图4 考试质量分析表

（1）根据图4的Word模板创建一个新文档;

（2）输出报表标题.是一行文本.在图4的“①表格标题”区事先定义一个名称为“Title”的书签，选择书签“Title”，在书签位置输出标题字符串;

（3）输出课程信息和成绩统计.图4的“②课程信息”区、“③成绩统计”区属于表格的一部分，分别由多个单元格组成.通过引用Word的Tables对象，将课程信息和3.2节中的统计数据输出到这两个区域的单元格中;

（4）输出考试质量分析意见.图4的“⑤任课教师的分析1”区定义了书签“FenXi1”，选择该书签位置，输出试题难度和区分度、考试成绩正态相关度、实际考试成绩与正态分布的对比图（图2）.往Word文档中导出图表需要借助临时文件，先将Excel中的图表导出为JPEG格式的图像文件，再将图像文件导入到Word文档.图4的“⑥任课教师的分析2”区定义了书签“FenXi2”，选择该书签位置，输出3.5节形成的考试质量分析意见.

教师只需点击Excel工作簿中运行该程序的按钮，即可将报表导出为Word文档，将其保存、打印，完成考试质量分析.

4 结论

程序设计和调试在Excel 2007软件的VBA编程环境中进行，任务中规定的功能已经全部实现，全部程序正常运行。

实践证明，通过考试质量分析模板能快速进行课程成绩统计和分析，辅助生成考试质量分析意见,导出Word格式的报表，使考试质量分析过程更为高效、全面、规范.实际应用结果表明，该模板能够快速进行成绩处理、统计分析和报表生成，简化人工操作步骤，提高工作效率.

本文仅仅对试卷的总分进行统计处理.如果需要对考试质量进行更为深入细致的分析，可以先对每道题的难度、区分度分别进行统计，以得到详细的答题情况和考试成绩的组成情况.这样的话要求有每道题的得分情况等数据.对于高校普通课程的考试而言，暂时还没有这种需求.本文的工作内容针对性强，与现阶段高校考试质量分析的需求相适应.

［1］裴丽,李觉先.考试成绩服从正态分布的条件［J］.辽宁大学学报:自然科学版,2008(4):321-322.

［2］吴新建,张贤金.考试难度的测量学意义的探讨［J］.福建教育学院学报,2011(6):63-66.

［3］李中复,吕秀芳,王大雷.正态分布密度及学生考试成绩统计［J］.辽宁工学院学报:社会科学版,2004(5):109-110.

［4］孙艳芹,李洪梅,熊晖等.病理学考试成绩和试卷质量分析与评价［J］.基础医学教育,2013(1):20-22.

［5］王小燕.MS Excel数据分析在成绩分析暨试卷质量评估的应用［J］.现代教育技术,2010(10):135-137.

［6］周晓东,邹娅玲.关于考试成绩评估的统计分析方法［J］.昆明冶金高等专科学校学报,2006(5):85-89.

［7］顾士学.基于Excel2003 VBA成绩分析模板的设计与实现［J］.白城师范学院学报,2011(3):51-54.

［8］廖丽嵘.基于Excel VBA的成绩管理系统［J］.现代计算机(专业版),2012(23):64-66+77.

［9］吴智,文清平.基于Excel高校课程考核成绩分析样板的开发［J］.办公自动化,2012(4):60-62.

［10］谭忠,孟丽,蔡勇.军校考试成绩分析的新思路［J］.高等教育研究学报,2010(3):36-39.