基于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 引 言

試卷分析是教學工作的重要組成部分，通過有效的試卷分析，一方面可以對試卷本身進行合理評價，提高教師的命題水平;另一方面可評價學生的能力，判斷試卷的整體價值，促進教學方法的改進和改革。查閱相關文獻，已有高校引進試卷分析系統，以健全試卷質量評價體系，不同的高校也形成了不同的基于多個評價指標的分析理論?；谥嗅t藥院校特殊的學科性質，本文通過各類型題目（如選擇題、填空題、判斷題）的答題情況的整體分析，借助數理統計方法，引入對試卷難度、區分度、信度和效度等深入、專業的分析。對此擬展開研究論述如下。

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]田強. 高?？荚嚬芾砗驮嚲矸治鱿到y的研究[J]. 電腦迷，2018（10）：151.

[20]馬婷婷. 高?？荚嚬芾砗驮嚲矸治鱿到y的設計與實現[D]. 揚州：揚州大學，2018.

[21]倪海兒，裘曉華，魏丹毅. 試卷質量評估與分析系統的構建與實現[J]. 寧波大學學報（理工版），2016，29（3）：118.

[22]黃秋香. 通過試卷評估提升高校試卷質量[J]. 亞太教育，2015（28）：273.

[23]周帆帆，王昕，涂小琴. 試卷質量分析與綜合評價系統的設計與實現[J]. 現代計算機（專業版），2014（12）：52.