基于ExcelVBA的试卷质量分析与评价系统

朱西敏 韩爱庆 张未未 王丽 唐燕 翟兴

摘要：在试卷评价中引入难度、区分度、信度和效度（四度）等数理统计指标，设计和开发能全面反映试卷质量的试卷分析系统。该系统一方面可以对试卷指标进行计算和客观分析，多维评价和发现试卷存在的问题，辅助提高试卷命题质量;另一方面可客观反映教师的教学水平与学生的学习效果，发现教学活动中存在的薄弱环节，促进教学方法的改革。目前该系统已有近600个班次的应用，已经在本单位各学院，各专业全面推广，取得了较好的应用效果。

关键词： 试卷; 质量分析; VBA; 评价

【Abstract】 In the evaluation of test papers， mathematical statistics such as difficulty， discrimination， reliability and validity （four degrees） are introduced， and the test paper analysis system which can fully reflect the quality of test papers is designed and developed. On the one hand， the system can calculate and objectively analyze the four degrees of the test paper， multi-dimensionally evaluate and discover the problems existing in the test paper to help improve the quality of the test paper proposition; on the other hand， it can objectively reflect the teaching level of the teacher and the learning effect of the students， discover the weak links in the teaching activities， promote the reform of teaching methods. At present， the system has nearly 600 shifts of application， and has been widely promoted in various colleges of the author's university and has achieved good application results.

【Key words】  test paper; quality analysis; VBA; evaluation

0 引 言

试卷分析是教学工作的重要组成部分，通过有效的试卷分析，一方面可以对试卷本身进行合理评价，提高教师的命题水平;另一方面可评价学生的能力，判断试卷的整体价值，促进教学方法的改进和改革。查阅相关文献，已有高校引进试卷分析系统，以健全试卷质量评价体系，不同的高校也形成了不同的基于多个评价指标的分析理论。基于中医药院校特殊的学科性质，本文通过各类型题目（如选择题、填空题、判断题）的答题情况的整体分析，借助数理统计方法，引入对试卷难度、区分度、信度和效度等深入、专业的分析。对此拟展开研究论述如下。

1 问题描述

目前，大部分教务教学系统可将期末成绩自动生成试卷分析表，对期末成绩进行初步分析。显然，当前的试卷分析提供的评价信息严重不足。一方面，该试卷分析结果是基于总成绩的分析，无法体现细节，未全面、合理地反映试题信息，对有针对性的修订试卷，提高教师的命题水平并不具备相应借鉴意义;另一方面未反映出试卷的难度、区分度和信度，无法更好地进行教学设计，改进教学方法，提升教学质量。所以无法从该分析获知试卷评估及改进试卷质量所需的具体信息。本次研究中，将用到的学生成绩分布图如图1所示。

4 核心算法及其实现

4.1 信度算法及实现

4.1.1 信度算法

信度算法的基本原理是：对于一个好的问卷，单个题目（考察某一主题）的组内成绩变异一般比较小，而不同题目间（组间）的成绩变异一般比较大，组内变异与组间变异之和得到总变异，组内变异在总变异中占比越小，试卷的质量越好。算法如下：

步骤1 计算各个题型的方差，即组内变异。

步骤2 计算每个样本的方差，即计算每个样本在各个题型和总分上的方差。

步骤3 计算每个样本方差的和，即总方差。

步骤4 计算自由度，因包含总分列，所以自由度=题型数量+1。

步骤5 将总变异、组内变异、自由度代入Cronbach公式，计算信度值。

步骤6 按照以上方法，逐一计算删除各个题型后的信度值。

4.1.2 信度算法实现

算法实现中，首先在主程序中复制data表中的原始数据到辅助表data_rel表，然后调用以下rel_val（）函数。在以下函数中，利用辅助表分别计算各个题型的变异以及总变异，再将中间结果分别代入Cronbach公式，計算信度。若要计算删除某个题型后的试卷信度值，只需利用循环方法逐一删除data_rel表中各个题型，并循环调用rel_val即可。程序代码具体如下：

Function rel_val（） As Single

Dim ws1 As Worksheet， ws2 As Worksheet， ws3 As Worksheet

Dim ii as integer， jj as integer

Dim rel As Single， rng1 As Range， rng2 As Range

Set ws1 = Worksheets（"Data"）

Set ws2 = Worksheets（"Data_rel"）

'注释：ws1指向data表，ws2指向辅助表data_rel表，辅助计算行列方差

ws1.Range（"a1"）.CurrentRegion.Copy ws2.Range（"a1"）

'注释：复制data表数据到data_rel表

ii = ws2.Cells（65536， 1）.End（xlUp）.Row

'注释：ii分别为data_rel表的行数

jj = ws2.Cells（1， 1）.End（xlToRight）.Column

'注释：jj分别为data_rel表的列数

For j = 2 To jj

'注释：for循环用于计算每种题型的列方差，计算结果存储到该列下方

Set rng1 = Range（ws2.Cells（2， j）， ws2.Cells（ii， j））

ws2.Cells（ii + 1， j） = （Application.WorksheetFunction.StDev（rng1）） ^ 2

Next

For i = 2 To ii

'注释：for循环计算每一行（每个人）单项成绩和总成绩的和

Set rng2 = Range（ws2.Cells（i， 2）， ws2.Cells（i， jj））

ws2.Cells（i， jj + 1） = （Application.WorksheetFunction.Sum（rng2））

Next

'注释：rng1，rng2分别指向末行，末列，即，各个题型方差，和每个人的成绩和

Set rng1 = Range（ws2.Cells（ii + 1， 2）， ws2.Cells（ii + 1， jj））

Set rng2 = Range（ws2.Cells（2， jj + 1）， ws2.Cells（ii， jj + 1））

ssi2 = Application.WorksheetFunction.Sum（rng1）

'注释：计算各题型成绩方差的和

s2 = （Application.WorksheetFunction.StDev（rng2）） ^ 2

'注釋：计算每个人总成绩的方差

k = jj - 1

rel = （k / （k - 1）） * （1 - ssi2 / s2）

'Cronbach公式，计算信度

rel_val = rel

' 注释：返回计算结果

End Function

4.2 效度算法及实现

效度计算本质上是计算各试题之间的pearson线性相关系数，在Excel VBA中，可直接调用Application.Correl（array1，array2）返回2个向量的相关系数，例如，可以将选择题（i列）和填空题得分（j列）分别作为实参传递给Correl函数，计算两列之间的相关系数。

本研究基于Correl函数设计了专门的MCorrelation函数，对全部题目分别做两两相关分析，研发代码及代码解释如下。

Function MCorrelation（[WT5HX]rng[WT5BX]As Range）

‘注释：[WT5HX]rng[WT5BX]为各人各题目得分矩阵

Dim x As Variant， y As Variant

Dim s As Integer， t As Integer， c（） As Variant

cols = [WT5HX]rng[WT5BX].Columns.Count

ReDim c（cols， cols）

‘注释：cols为列数，矩阵[WT5HX]c[WT5BX]（）用于存储效度值

For i = 1 To cols Step 1

For j = 1 To i Step 1

c（i， j） = Application.Correl（Application.Index（[WT5HX]rng[WT5BX]， ， i）， Application.Index（[WT5HX]rng[WT5BX]， ， j））

‘注释：Correl函数功能是计算效度

Next j

Next i

MCorrelation = c

End Function

4.3 生成分析报告

利用Excel VBA计算生成的难度、区分度、信度和效度等各项分析结果分散于同一工作簿的不同的表中。基于应用需求，需将各项分析指标整合为一个系统的Word版分析报告，本系统借助Excel VBA实现了该功能。

Excel VBA具备创建Word对象，并对Word对象进行格式控制，添加、修改及删除文本和图表等内容的功能。研究中得到的创建Word对象并将信度分析结果输出到分析报告的代码具体如下。

Public Sub OutputAnalysisReport（）

'注：本代码展示新建Word对象和输出信度分析结果

Dim appWD As Object

Dim strWd As String

Dim ws As Worksheet

Set appWD = CreateObject（"Word.Application"）

appWD.documents.Add

'注：創建Word对象并新建一个空文档

appWD.Selection.Font.Size = 14

appWD.Selection.Font.Name = "宋体"

appWD.Selection.Font.Bold = True

'注：设置字体格式

strWd = "二、信度分析"

appWD.Selection.TypeParagraph

appWD.Selection.TypeText （strWd）

'注：向Word输入标题“二、信度分析”

Set ws = Worksheets（"信度分析"）

appWD.Selection.TypeParagraph

ws.Range（"a1"）.CurrentRegion.Copy

With appWD

.Selection.PasteExcelTable False， False， False

End With

'注：将“信度分析”表中的信度分析结果输出到Word中

appWD.Selection.TypeParagraph

Set ws = Worksheets（"说明"）

ws.Shapes（"pic2"）.Copy

appWD.Selection.Paste

'注：将“说明”表中的信度分析评价标准输出到Word中

End Sub

5 结束语

本文主要讨论了试卷质量分析和评价系统的评价指标、系统的功能界面和主要程序设计。较完备的评价指标体系使试卷的质量分析更加全面具体，可以从多角度反映试卷的质量，为试卷的进一步改进提供了新思路。Excel处理数据有其非常出色的功能，简洁的界面、便捷的操作，VBA的主要任务是组织或集合Office程序的功能，虽然无法脱离Office环境独立运行，但却在自动化工作进程、个性化工作界面等方面有着内在的优势[1]。Office Excel界面友好，简单易学，普及程度高，基于Excel VBA编写的试卷系统既能实现试卷分析所需功能，又具有操作简单，使用门槛低的优势。目前该系统已有近600个班次的应用，已经在本单位各学院，各专业全面推广，取得了较好的应用效果。

参考文献

[1] 韩爱庆，王允亮，李军祥，等. 基于VBA的中医电子医案数据预处理方法研究[J]. 电脑知识与技术，2017，13（20）：1.

[2]黄钢. 基于excelVBA的高校临时收费系统的设计[J]. 信息系统工程，2019（1）：57.

[3]李敏. ExcelVBA在高职院校提前招生录取工作中的应用[J]. 计算机产品与流通，2018（7）：189.

[4]唐燕，韩爱庆，翟兴. 基于VBA技术实现调查问卷数据自动读取[J]. 计算机与现代化，2014（2）：226.

[5]韩爱庆，薛磊，唐燕，等. 基于VBA的题库查重算法研究[J]. 电脑知识与技术，2012，8（28）：6761.

[6]欧靖. 英语试卷中多项选择题的信度与效度分析[J]. 辽宁行政学院学报，2006，8（10）：114.

[7]宋小平，张敏，陈建军，等. SPSS在医学生物学试卷的信度和效度分析中的应用[J]. 生物学通报，2008，43（11）：39.

[8]孙银祚，马开剑. Cronbach公式和Kuder-Richardson公式的应用研究[J]. 烟台大学学报（自然科学与工程版），1999，12（1）：14.

[9]黄颖. 试卷分析理论与实践探讨[D]. 福州：福建中医药大学，2005.

[10]王渊，刘清华，曹中华. 基于MFC的试卷分析系统关键技术探讨[J]. 电子技术与软件工程，2013（24）：89.

[11]项军平. 运用Excel进行英语试题项目分析和信度分析[J]. 民族论坛，2007（8）：52.

[12]张祖芹，丁向民. 试卷分析系统的Excel VBA实现[J]. 淮阴师范学院学报（自然科学版），2014，13（3）：231.

[13]黄艳，李俊. 2007级临床医学专业学生的临床药理学试卷分析[J]. 安徽医药，2009，13（12）：1602.

[14]陈少玲. 应用数理统计方法分析试题试卷质量[J]. 惠阳师专学报（自然科学版），1989（S1）：69.

[15]魏蕾，董传仁，李瑾，等. 病理生理学期末考试试卷分析与评价[J]. 中国病理生理杂志，2000，16（7）：670.

[16]杨丽军，刘敏钰，薛鸿民，等. 高校教学水平评估中试卷质量分析系统的研究与实现[J]. 微电子学与计算机，2006，23（8）：196.

[17]刘百良. 试卷质量的数理统计分析[J]. 数理统计与管理，1987（5）：19.

[18]张继瑄. 利用EXCEL管理学生信息及批量打印的实现[J]. 电脑知识与技术，2018，14（12）：164.

[18]田强. 高校考试管理和试卷分析系统的研究[J]. 电脑迷，2018（10）：151.

[20]马婷婷. 高校考试管理和试卷分析系统的设计与实现[D]. 扬州：扬州大学，2018.

[21]倪海儿，裘晓华，魏丹毅. 试卷质量评估与分析系统的构建与实现[J]. 宁波大学学报（理工版），2016，29（3）：118.

[22]黄秋香. 通过试卷评估提升高校试卷质量[J]. 亚太教育，2015（28）：273.

[23]周帆帆，王昕，涂小琴. 试卷质量分析与综合评价系统的设计与实现[J]. 现代计算机（专业版），2014（12）：52.