基于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.