張祖芹，丁向民

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

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

張祖芹，丁向民

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

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

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

0 引言

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

1 問(wèn)題的提出

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

目前，教師進(jìn)行試卷分析存在以下一些問(wèn)題：

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

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

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

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

2 試卷成績(jī)分析表分析

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

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

主要涉及的參數(shù)有：班級(jí)平均分、全對(duì)人數(shù)、全錯(cuò)人數(shù)、各分?jǐn)?shù)段人數(shù)及百分比，計(jì)算公式相對(duì)比較簡(jiǎn)單：

班級(jí)平均分：

各分?jǐn)?shù)段人數(shù)百分比：

2.2 成績(jī)分析表

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

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

2.3 具體指標(biāo)設(shè)計(jì)

著重選擇4個(gè)參數(shù)進(jìn)行分析：試題難度、區(qū)分度、班級(jí)平均分和標(biāo)準(zhǔn)差．

1）試題難度可以評(píng)價(jià)試卷題目的難易程度，可以用得分率來(lái)表示，其值域?yàn)椋?，1］，數(shù)值越大，說(shuō)明試卷（題）越容易．難度d的定義為：

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

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

其中Xi為每位同學(xué)的分?jǐn)?shù)，N為班級(jí)人數(shù)．

4）標(biāo)準(zhǔn)差反映的是班級(jí)同學(xué)得分的離散程度［4］，值越大，說(shuō)明班級(jí)同學(xué)的成績(jī)分布差異越大．標(biāo)準(zhǔn)差S的定義為：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

4 小結(jié)

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

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

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

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

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

［5］ 毛文兵.基于Excel和VBA的信息系統(tǒng)開(kāi)發(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

［責(zé)任編輯：蔣海龍］

2014?04?16

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