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

朱西敏 韓愛(ài)慶 張未未 王麗 唐燕 翟興

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

關(guān)鍵詞： 試卷; 質(zhì)量分析; VBA; 評(píng)價(jià)

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

1 問(wèn)題描述

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

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

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

4.1.1 信度算法

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

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

步驟2 計(jì)算每個(gè)樣本的方差，即計(jì)算每個(gè)樣本在各個(gè)題型和總分上的方差。

步驟3 計(jì)算每個(gè)樣本方差的和，即總方差。

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

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

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

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

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

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)用于計(jì)算每種題型的列方差，計(jì)算結(jié)果存儲(chǔ)到該列下方

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)計(jì)算每一行（每個(gè)人）單項(xiàng)成績(jī)和總成績(jī)的和

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

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

Next

'注釋：rng1，rng2分別指向末行，末列，即，各個(gè)題型方差，和每個(gè)人的成績(jī)和

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）

'注釋：計(jì)算各題型成績(jī)方差的和

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

'注釋：計(jì)算每個(gè)人總成績(jī)的方差

k = jj - 1

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

'Cronbach公式，計(jì)算信度

rel_val = rel

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

End Function

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

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

本研究基于Correl函數(shù)設(shè)計(jì)了專門(mén)的MCorrelation函數(shù)，對(duì)全部題目分別做兩兩相關(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]（）用于存儲(chǔ)效度值

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ù)功能是計(jì)算效度

Next j

Next i

MCorrelation = c

End Function

4.3 生成分析報(bào)告

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

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

Public Sub OutputAnalysisReport（）

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

Dim appWD As Object

Dim strWd As String

Dim ws As Worksheet

Set appWD = CreateObject（"Word.Application"）

appWD.documents.Add

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

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（"說(shuō)明"）

ws.Shapes（"pic2"）.Copy

appWD.Selection.Paste

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

End Sub

5 結(jié)束語(yǔ)

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

參考文獻(xiàn)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[18]田強(qiáng). 高校考試管理和試卷分析系統(tǒng)的研究[J]. 電腦迷，2018（10）：151.

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

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

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

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