基于ExcelVBA的試卷質(zhì)量分析與評價系統(tǒng)

朱西敏 韓愛慶 張未未 王麗 唐燕 翟興

摘要：在試卷評價中引入難度、區(qū)分度、信度和效度（四度）等數(shù)理統(tǒng)計指標(biāo)，設(shè)計和開發(fā)能全面反映試卷質(zhì)量的試卷分析系統(tǒng)。該系統(tǒng)一方面可以對試卷指標(biāo)進(jìn)行計算和客觀分析，多維評價和發(fā)現(xiàn)試卷存在的問題，輔助提高試卷命題質(zhì)量;另一方面可客觀反映教師的教學(xué)水平與學(xué)生的學(xué)習(xí)效果，發(fā)現(xiàn)教學(xué)活動中存在的薄弱環(huán)節(jié)，促進(jìn)教學(xué)方法的改革。目前該系統(tǒng)已有近600個班次的應(yīng)用，已經(jīng)在本單位各學(xué)院，各專業(yè)全面推廣，取得了較好的應(yīng)用效果。

關(guān)鍵詞： 試卷; 質(zhì)量分析; 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 引 言

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

1 問題描述

目前，大部分教務(wù)教學(xué)系統(tǒng)可將期末成績自動生成試卷分析表，對期末成績進(jìn)行初步分析。顯然，當(dāng)前的試卷分析提供的評價信息嚴(yán)重不足。一方面，該試卷分析結(jié)果是基于總成績的分析，無法體現(xiàn)細(xì)節(jié)，未全面、合理地反映試題信息，對有針對性的修訂試卷，提高教師的命題水平并不具備相應(yīng)借鑒意義;另一方面未反映出試卷的難度、區(qū)分度和信度，無法更好地進(jìn)行教學(xué)設(shè)計，改進(jìn)教學(xué)方法，提升教學(xué)質(zhì)量。所以無法從該分析獲知試卷評估及改進(jìn)試卷質(zhì)量所需的具體信息。本次研究中，將用到的學(xué)生成績分布圖如圖1所示。

4 核心算法及其實(shí)現(xiàn)

4.1 信度算法及實(shí)現(xiàn)

4.1.1 信度算法

信度算法的基本原理是：對于一個好的問卷，單個題目（考察某一主題）的組內(nèi)成績變異一般比較小，而不同題目間（組間）的成績變異一般比較大，組內(nèi)變異與組間變異之和得到總變異，組內(nèi)變異在總變異中占比越小，試卷的質(zhì)量越好。算法如下：

步驟1 計算各個題型的方差，即組內(nèi)變異。

步驟2 計算每個樣本的方差，即計算每個樣本在各個題型和總分上的方差。

步驟3 計算每個樣本方差的和，即總方差。

步驟4 計算自由度，因包含總分列，所以自由度=題型數(shù)量+1。

步驟5 將總變異、組內(nèi)變異、自由度代入Cronbach公式，計算信度值。

步驟6 按照以上方法，逐一計算刪除各個題型后的信度值。

4.1.2 信度算法實(shí)現(xiàn)

算法實(shí)現(xiàn)中，首先在主程序中復(fù)制data表中的原始數(shù)據(jù)到輔助表data_rel表，然后調(diào)用以下rel_val（）函數(shù)。在以下函數(shù)中，利用輔助表分別計算各個題型的變異以及總變異，再將中間結(jié)果分別代入Cronbach公式，計算信度。若要計算刪除某個題型后的試卷信度值，只需利用循環(huán)方法逐一刪除data_rel表中各個題型，并循環(huán)調(diào)用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"）

'注釋：復(fù)制data表數(shù)據(jù)到data_rel表

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

'注釋：ii分別為data_rel表的行數(shù)

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

'注釋：jj分別為data_rel表的列數(shù)

For j = 2 To jj

'注釋：for循環(huán)用于計算每種題型的列方差，計算結(jié)果存儲到該列下方

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循環(huán)計算每一行（每個人）單項成績和總成績的和

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

' 注釋：返回計算結(jié)果

End Function

4.2 效度算法及實(shí)現(xiàn)

效度計算本質(zhì)上是計算各試題之間的pearson線性相關(guān)系數(shù)，在Excel VBA中，可直接調(diào)用Application.Correl（array1，array2）返回2個向量的相關(guān)系數(shù)，例如，可以將選擇題（i列）和填空題得分（j列）分別作為實(shí)參傳遞給Correl函數(shù)，計算兩列之間的相關(guān)系數(shù)。

本研究基于Correl函數(shù)設(shè)計了專門的MCorrelation函數(shù)，對全部題目分別做兩兩相關(guān)分析，研發(fā)代碼及代碼解釋如下。

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為列數(shù)，矩陣[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函數(shù)功能是計算效度

Next j

Next i

MCorrelation = c

End Function

4.3 生成分析報告

利用Excel VBA計算生成的難度、區(qū)分度、信度和效度等各項分析結(jié)果分散于同一工作簿的不同的表中。基于應(yīng)用需求，需將各項分析指標(biāo)整合為一個系統(tǒng)的Word版分析報告，本系統(tǒng)借助Excel VBA實(shí)現(xiàn)了該功能。

Excel VBA具備創(chuàng)建Word對象，并對Word對象進(jìn)行格式控制，添加、修改及刪除文本和圖表等內(nèi)容的功能。研究中得到的創(chuàng)建Word對象并將信度分析結(jié)果輸出到分析報告的代碼具體如下。

Public Sub OutputAnalysisReport（）

'注：本代碼展示新建Word對象和輸出信度分析結(jié)果

Dim appWD As Object

Dim strWd As String

Dim ws As Worksheet

Set appWD = CreateObject（"Word.Application"）

appWD.documents.Add

'注：創(chuàng)建Word對象并新建一個空文檔

appWD.Selection.Font.Size = 14

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

appWD.Selection.Font.Bold = True

'注：設(shè)置字體格式

strWd = "二、信度分析"

appWD.Selection.TypeParagraph

appWD.Selection.TypeText （strWd）

'注：向Word輸入標(biāo)題“二、信度分析”

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

appWD.Selection.TypeParagraph

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

With appWD

.Selection.PasteExcelTable False， False， False

End With

'注：將“信度分析”表中的信度分析結(jié)果輸出到Word中

appWD.Selection.TypeParagraph

Set ws = Worksheets（"說明"）

ws.Shapes（"pic2"）.Copy

appWD.Selection.Paste

'注：將“說明”表中的信度分析評價標(biāo)準(zhǔn)輸出到Word中

End Sub

5 結(jié)束語

本文主要討論了試卷質(zhì)量分析和評價系統(tǒng)的評價指標(biāo)、系統(tǒng)的功能界面和主要程序設(shè)計。較完備的評價指標(biāo)體系使試卷的質(zhì)量分析更加全面具體，可以從多角度反映試卷的質(zhì)量，為試卷的進(jìn)一步改進(jìn)提供了新思路。Excel處理數(shù)據(jù)有其非常出色的功能，簡潔的界面、便捷的操作，VBA的主要任務(wù)是組織或集合Office程序的功能，雖然無法脫離Office環(huán)境獨(dú)立運(yùn)行，但卻在自動化工作進(jìn)程、個性化工作界面等方面有著內(nèi)在的優(yōu)勢[1]。Office Excel界面友好，簡單易學(xué)，普及程度高，基于Excel VBA編寫的試卷系統(tǒng)既能實(shí)現(xiàn)試卷分析所需功能，又具有操作簡單，使用門檻低的優(yōu)勢。目前該系統(tǒng)已有近600個班次的應(yīng)用，已經(jīng)在本單位各學(xué)院，各專業(yè)全面推廣，取得了較好的應(yīng)用效果。

參考文獻(xiàn)

[1] 韓愛慶，王允亮，李軍祥，等. 基于VBA的中醫(yī)電子醫(yī)案數(shù)據(jù)預(yù)處理方法研究[J]. 電腦知識與技術(shù)，2017，13（20）：1.

[2]黃鋼. 基于excelVBA的高校臨時收費(fèi)系統(tǒng)的設(shè)計[J]. 信息系統(tǒng)工程，2019（1）：57.

[3]李敏. ExcelVBA在高職院校提前招生錄取工作中的應(yīng)用[J]. 計算機(jī)產(chǎn)品與流通，2018（7）：189.

[4]唐燕，韓愛慶，翟興. 基于VBA技術(shù)實(shí)現(xiàn)調(diào)查問卷數(shù)據(jù)自動讀取[J]. 計算機(jī)與現(xiàn)代化，2014（2）：226.

[5]韓愛慶，薛磊，唐燕，等. 基于VBA的題庫查重算法研究[J]. 電腦知識與技術(shù)，2012，8（28）：6761.

[6]歐靖. 英語試卷中多項選擇題的信度與效度分析[J]. 遼寧行政學(xué)院學(xué)報，2006，8（10）：114.

[7]宋小平，張敏，陳建軍，等. SPSS在醫(yī)學(xué)生物學(xué)試卷的信度和效度分析中的應(yīng)用[J]. 生物學(xué)通報，2008，43（11）：39.

[8]孫銀祚，馬開劍. Cronbach公式和Kuder-Richardson公式的應(yīng)用研究[J]. 煙臺大學(xué)學(xué)報（自然科學(xué)與工程版），1999，12（1）：14.

[9]黃穎. 試卷分析理論與實(shí)踐探討[D]. 福州：福建中醫(yī)藥大學(xué)，2005.

[10]王淵，劉清華，曹中華. 基于MFC的試卷分析系統(tǒng)關(guān)鍵技術(shù)探討[J]. 電子技術(shù)與軟件工程，2013（24）：89.

[11]項軍平. 運(yùn)用Excel進(jìn)行英語試題項目分析和信度分析[J]. 民族論壇，2007（8）：52.

[12]張祖芹，丁向民. 試卷分析系統(tǒng)的Excel VBA實(shí)現(xiàn)[J]. 淮陰師范學(xué)院學(xué)報（自然科學(xué)版），2014，13（3）：231.

[13]黃艷，李俊. 2007級臨床醫(yī)學(xué)專業(yè)學(xué)生的臨床藥理學(xué)試卷分析[J]. 安徽醫(yī)藥，2009，13（12）：1602.

[14]陳少玲. 應(yīng)用數(shù)理統(tǒng)計方法分析試題試卷質(zhì)量[J]. 惠陽師專學(xué)報（自然科學(xué)版），1989（S1）：69.

[15]魏蕾，董傳仁，李瑾，等. 病理生理學(xué)期末考試試卷分析與評價[J]. 中國病理生理雜志，2000，16（7）：670.

[16]楊麗軍，劉敏鈺，薛鴻民，等. 高校教學(xué)水平評估中試卷質(zhì)量分析系統(tǒng)的研究與實(shí)現(xiàn)[J]. 微電子學(xué)與計算機(jī)，2006，23（8）：196.

[17]劉百良. 試卷質(zhì)量的數(shù)理統(tǒng)計分析[J]. 數(shù)理統(tǒng)計與管理，1987（5）：19.

[18]張繼瑄. 利用EXCEL管理學(xué)生信息及批量打印的實(shí)現(xiàn)[J]. 電腦知識與技術(shù)，2018，14（12）：164.

[18]田強(qiáng). 高?？荚嚬芾砗驮嚲矸治鱿到y(tǒng)的研究[J]. 電腦迷，2018（10）：151.

[20]馬婷婷. 高校考試管理和試卷分析系統(tǒng)的設(shè)計與實(shí)現(xiàn)[D]. 揚(yáng)州：揚(yáng)州大學(xué)，2018.

[21]倪海兒，裘曉華，魏丹毅. 試卷質(zhì)量評估與分析系統(tǒng)的構(gòu)建與實(shí)現(xiàn)[J]. 寧波大學(xué)學(xué)報（理工版），2016，29（3）：118.

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

[23]周帆帆，王昕，涂小琴. 試卷質(zhì)量分析與綜合評價系統(tǒng)的設(shè)計與實(shí)現(xiàn)[J]. 現(xiàn)代計算機(jī)（專業(yè)版），2014（12）：52.