試卷分析系統(tǒng)的Excel VBA實現(xiàn)

張祖芹，丁向民

（鹽城師范學院信息科學與技術(shù)學院，江蘇鹽城 224002）

試卷分析系統(tǒng)的Excel VBA實現(xiàn)

張祖芹，丁向民

（鹽城師范學院信息科學與技術(shù)學院，江蘇鹽城 224002）

為了減少試卷分析過程中的重復(fù)、繁瑣、不規(guī)范等情況發(fā)生，利用VBA技術(shù)在Excel中開發(fā)了一種試卷成績分析系統(tǒng).該系統(tǒng)利用試題難度、區(qū)分度、班級平均分和標準差等參數(shù)對試卷進行分析，然后利用數(shù)值參數(shù)評價表將參數(shù)值轉(zhuǎn)化成具體分析的文字表達，最終自動生成集數(shù)值、文字、圖形為一體的試卷分析表.

Excel VBA；試卷分析；成績系統(tǒng)

0 引言

試卷分析是課程考核后進行的一項常規(guī)工作，通過試卷分析可以了解學生對課程的學習情況，掌握教師的課程教學效果.所以，科學合理的試卷分析方法對教學質(zhì)量反饋至關(guān)重要.目前，很多高校都采用發(fā)放一定格式的空白試卷分析表，由教師自行填寫，由于教師對測量理論和評價方法的了解不夠充分，使得試卷評價過程存在很大的主觀性和隨意性，降低了試卷分析評價的科學性［1］.本文對高校教師經(jīng)常使用的成績分析工具——Excel進行二次開發(fā)，利用VBA（Visual Basic for Applications）開發(fā)了一種通用成績分析系統(tǒng)，對高校課程教學分析評價具有積極的應(yīng)用價值.

1 問題的提出

一般試卷分析表包括三部分內(nèi)容：試卷考查科目的基本信息、試卷成績的統(tǒng)計分析和存在的問題及改進措施.試卷考查科目的基本信息主要包括考試科目、考試時間、應(yīng)考人數(shù)、命題人等，這類信息比較簡單.試卷成績的統(tǒng)計分析是針對試卷每道題或每類題的具體分析，分析參數(shù)包括很多種，主要有平均分、滿分人數(shù)、零分人數(shù)、各分數(shù)段的人數(shù)等，用于反映學生學習的不同問題.存在問題及改進措施是利用試卷的分析結(jié)果來評判試題的難易程度，為教師改進教學提供參考.

目前，教師進行試卷分析存在以下一些問題：

1）重復(fù)工作多，分析效率低.教師利用Excel軟件進行試題分析時，每門科目都要進行常規(guī)的求最高分、最低分和平均分，統(tǒng)計各分數(shù)段人數(shù)和比例等重復(fù)性工作.如果所教課程或班級較多，這種處理方法的工作效率就很低.

2）水平差異大，分析難度高.對于教師而言，掌握Excel軟件數(shù)據(jù)輸入、制表、數(shù)據(jù)排序和簡單公式運用等操作比較容易，但進行分數(shù)段的人數(shù)統(tǒng)計、難度和區(qū)分度分析、正態(tài)分布相關(guān)性分析等復(fù)雜操作則難度不小.教師計算機處理動手能力差異較大，特別是年齡較大的老教師，熟練使用Excel軟件有一定困難.

3）重視程度不同，分析差異顯著.不同教師對成績分析的重視程度不同，其具體分析內(nèi)容差別比較明顯.部分教師僅對試題基本參數(shù)進行分析，沒有對成績進行難度、區(qū)分度、成績正態(tài)分布等情況進行實際計算，造成試卷分析內(nèi)容不夠全面規(guī)范，不能客觀反映學生考核情況.不認真進行數(shù)據(jù)分析，無法發(fā)現(xiàn)存在的問題和寫出合理的改進措施，只能用非常模糊的語句，如“試卷整體情況良好”、“今后在教學上還需要進一步改進”等來表示存在問題和改進措施.

根據(jù)上述情況，建立一種通用試卷分析系統(tǒng)，對試卷進行自動數(shù)據(jù)處理和輔助質(zhì)量評價，從而減少考試成績處理工作量，提高試卷分析的科學性.

2 試卷成績分析表分析

一般成績分析表具體包含以下幾個欄目：一是試卷科目的基本信息，主要有考試科目、班級、應(yīng)考人數(shù)、實考人數(shù)、考試時間、命題人、考試題型、題量等；二是試卷成績統(tǒng)計分析，主要有各種題型平均分、各分數(shù)段人數(shù)、全對人數(shù)、全錯人數(shù)等；三是成績分析結(jié)果的圖形化顯示；四是存在問題及改進措施.其中，欄目一不需要進行任何處理，欄目二、三和欄目四分析方法如下.

2.1 試題成績統(tǒng)計分析

主要涉及的參數(shù)有：班級平均分、全對人數(shù)、全錯人數(shù)、各分數(shù)段人數(shù)及百分比，計算公式相對比較簡單：

班級平均分：

各分數(shù)段人數(shù)百分比：

2.2 成績分析表

概率論的中心極限定理表明：在自然界中，大量隨機現(xiàn)象都服從或近似服從正態(tài)分布．教育統(tǒng)計學研究同樣發(fā)現(xiàn)：在滿足考試無作弊、試題難度和區(qū)分度適中、學生人數(shù)足夠多三個條件下，每個學生的考試成績可看作相互獨立的隨機變量，這時考試成績一般都符合正態(tài)分布．通過觀察學生的考試成績是否服從正態(tài)分布，可以非常直觀地判斷教學過程的合理性，從而進一步分析實際教學過程存在的不足，并找出其中原因，采取相應(yīng)措施，改進教學質(zhì)量．

假設(shè)實考學生人數(shù)有n個，成績按百分制計算，用ξk表示第k個學生的成績，則ξk為取值在［0，100］區(qū)間中的隨機變量．當n足夠大時，變量ξk近似服從正態(tài)分布n（a，σ2），其中a表示平均分、σ2表示方差［2］．通常一個班級的人數(shù)樣本數(shù)量不夠大（n為50左右），而試卷分數(shù)的分布區(qū)間是［0，100］，因此從每個分數(shù)去考察沒有意義，本文按區(qū)間間隔為10的分數(shù)段進行比較分析．

2.3 具體指標設(shè)計

著重選擇4個參數(shù)進行分析：試題難度、區(qū)分度、班級平均分和標準差．

1）試題難度可以評價試卷題目的難易程度，可以用得分率來表示，其值域為［0，1］，數(shù)值越大，說明試卷（題）越容易．難度d的定義為：

2）區(qū)分度反映的是試卷對不同水平學生加以區(qū)別的能力．如果試卷區(qū)分度較好，則水平高的學生應(yīng)該得到高分，水平低的學生應(yīng)該得到低分．本文采用極端分組法［3］，將成績在前27%的學生成績作為高分組XH、成績在后27%的學生成績作為低分組XL，則試題的區(qū)分度q可定義為：

3）班級平均分為班級所有同學的均分，反映了全班同學知識掌握的整體水平，值越大，說明學生知識掌握情況越好．其均分X的定義為：

其中Xi為每位同學的分數(shù)，N為班級人數(shù)．

4）標準差反映的是班級同學得分的離散程度［4］，值越大，說明班級同學的成績分布差異越大．標準差S的定義為：

為了將實際的參數(shù)數(shù)值轉(zhuǎn)化為更加直觀的評價標準，本文定義了4個數(shù)值評價表：試題難度評價表、試題區(qū)分度評價表、平均分評價表、標準差評價表．評價表利用專家經(jīng)驗的方法將參數(shù)數(shù)值轉(zhuǎn)化為評價的等次．

3 試卷分析系統(tǒng)的實現(xiàn)

3.1 試卷分析系統(tǒng)實現(xiàn)的流程

由于Excel本身可以看成是數(shù)據(jù)庫，也可以看成是開發(fā)工具，所以其開發(fā)流程與傳統(tǒng)流程稍有不同［5］.一般的開發(fā)流程如圖1所示.

圖1 Excel開發(fā)信息系統(tǒng)的一般流程

Excel的初始界面是工作表.工作表在數(shù)據(jù)輸入、顯示方面能較好地滿足用戶需求，但在交互方面則不能很好地滿足用戶需求.Excel提供了用戶窗體（UserForm），可以實現(xiàn)按鈕、文本框、選擇列表等，結(jié)合Excel工作表，可以較好地滿足信息系統(tǒng)的界面設(shè)計.

由于Excel工作表既是界面，也是需要打印的報表，所以Excel界面一旦設(shè)計完成，就不需要用戶修改.但Excel工作表很容易被用戶修改，為了解決這個矛盾，需要對界面進行保護.界面保護的類型主要有兩類：一是鼠標的響應(yīng)，即限制鼠標在工作表的某些部分進行點擊；二是工作表的操作，即限制對工作表某些部分進行操作，主要包括單元格的格式設(shè)置、插入或刪除單元格的行列等.

算法設(shè)計主要是對系統(tǒng)的流程和核心部分進行設(shè)計，根據(jù)算法設(shè)計進行宏代碼編寫，最后進行系統(tǒng)測試.

3.2 試卷分析系統(tǒng)實現(xiàn)的核心算法

讀入原始成績及參數(shù)是系統(tǒng)實現(xiàn)的第一步.本系統(tǒng)一共設(shè)計了兩張表，第一張表為成績表，主要是讓教師輸入原始成績，主要欄目有學號、姓名和各科成績.第兩張表為分析表，主要是讓教師輸入試卷的相關(guān)參數(shù)和存放分析結(jié)果.系統(tǒng)首先根據(jù)班級學生人數(shù)（SNumber）和考試題型數(shù)目（INumber）生成一個動態(tài)二維數(shù)組Score（SNumber，INumber＋1），然后將成績表中的數(shù)據(jù)讀入該數(shù)組中（數(shù)組的最后一列用以存放每位同學的總成績）.然后再定義一個變量ExamNumber用以存放實考人數(shù)，一個數(shù)組ItemsScore（INumber）用以存放每種類型題目的滿分.

圖2 系統(tǒng)實現(xiàn)的核心算法流程

檢查原始數(shù)據(jù)的主要任務(wù)就是檢查輸入的成績表中是否有輸入錯誤的情況，主要被認為錯誤的情況有：單元格為空白，單元格數(shù)據(jù)為負值，單個題型的分數(shù)超過該類題型分數(shù)的滿分值，有成績?nèi)藬?shù)與實考人數(shù)不一致.如果存在以上情況中的任一錯誤，系統(tǒng)則給出錯誤提示，并中斷程序.

統(tǒng)計基本參數(shù)的主要任務(wù)是計算分析表中的各種參數(shù)，主要包括每種題型的平均分、全對人數(shù)、全錯人數(shù)、總分中各個分數(shù)段的人數(shù)、缺考人數(shù)、試卷難度、試卷區(qū)分度、成績標準差等.該部分是系統(tǒng)的核心.

繪制成績分布表是根據(jù)總分中各個分數(shù)段的人數(shù)繪制成績分布曲線，從而更直觀地觀察學生成績分布狀況.

書寫具體分析是根據(jù)計算出來的基本參數(shù)，形成試卷的文字性分析結(jié)果，其主要思想是根據(jù)參數(shù)評價表形成文字描述.

4 小結(jié)

本系統(tǒng)的程序設(shè)計和調(diào)試在Excel 2007軟件的VBA編程環(huán)境中進行，一共定義了兩個宏，一個宏對應(yīng)于界面上的“導(dǎo)入題型信息”按鈕，主要完成將成績表中的題型信息讀入分析表以及系統(tǒng)變量的初始化工作；另一個宏對應(yīng)于“生成分析表”按鈕，用以填充分析數(shù)據(jù).實踐表明，通過試卷分析系統(tǒng)生成的分析表，不僅使考試情況分析過程更為高效、全面、規(guī)范，而且極大避免了人工操作可能產(chǎn)生的誤差等情況發(fā)生.

［1］ 王華，陳景，馬翠芹.基于CTT與IRT的試卷質(zhì)量評價系統(tǒng)設(shè)計與實現(xiàn)［J］.計算機工程與設(shè)計，2013（5）：1826-1830

［2］ 李勇，宋曉霞.通用試卷分析系統(tǒng)的設(shè)計與實現(xiàn)［J］.中國教育信息化，2008（4）：55-57

［3］ 羅海鳳，謝垂益.基于Excel VBA的高?？荚囐|(zhì)量分析模板［J］.韶關(guān)學院學報，2013（2）：76-80.

［4］ 婁青.基于Excel VBA開發(fā)普通高中成績分析系統(tǒng)［J］.中國教育信息化，2012（10）：90-96.

［5］ 毛文兵.基于Excel和VBA的信息系統(tǒng)開發(fā)技術(shù)研究［J］.沿海企業(yè)與科技，2008（11）：81-85.

Design and Im p lementation of the Test Results Analysis System of Based on Excel VBA

ZHANG Zu?qin，DING Xiang?min
（School of Information Science and Technology，Yancheng Teachers University，Yancheng Jiangsu 224002）

In order to solve the test paper analysis process of repetitive，tedious，non?standard situations，a test score analysis system is developed based on VBA technology in the Excel.The system is based on?Yancheng Teachers University examination paper analysis.The system analysis content includedifficulty，dis?crimination，class average and the standard deviation of parameters on the paper.The test paper analysis table is created byconvertingthe parameter value expressionto the analysis text usingnumerical parameters evaluation table，including a set of numerical，text，graphics.

Excel VBA；analysis of test paper；score system

TP391.13

A

1671?6876（2014）03?0231?04

［責任編輯：蔣海龍］

2014?04?16

張祖芹（1976?），女，江蘇東臺人，講師，碩士，主要從事教育技術(shù)、計算機應(yīng)用研究.E?mail：yctczzq＠163.com