摘 要：成績(jī)分析是課程考核工作的必要環(huán)節(jié)，科學(xué)有效的成績(jī)分析建立在教育統(tǒng)計(jì)學(xué)基礎(chǔ)上，常用的成績(jī)分析方法有頻數(shù)分析法、集中量分析法、差異量分析法。然而在實(shí)際工作中，許多教師沒(méi)有教育統(tǒng)計(jì)學(xué)基礎(chǔ)理論，不了解數(shù)理統(tǒng)計(jì)分析方法，而且手工計(jì)算過(guò)程紛繁復(fù)雜，不僅費(fèi)時(shí)費(fèi)力，而且容易出錯(cuò)，難以實(shí)現(xiàn)成績(jī)分析的高效性和準(zhǔn)確性。文章從工作實(shí)際出發(fā)，以Excel軟件為開(kāi)發(fā)平臺(tái)，采用VBA技術(shù)自定義解決方案，針對(duì)課程考核結(jié)果進(jìn)行自動(dòng)成績(jī)分析。

關(guān)鍵詞：ExcelVBA開(kāi)發(fā)；自動(dòng)成績(jī)分析；高效辦公

中圖分類(lèi)號(hào)：TP319 文獻(xiàn)標(biāo)志碼：A 文章編號(hào)：1673-8454（2014）04-0065-04

課程考核是檢驗(yàn)教學(xué)質(zhì)量的重要途徑和關(guān)鍵手段，考核成績(jī)作為量化評(píng)價(jià)指標(biāo)，能夠有效反映學(xué)生的學(xué)習(xí)情況和教師的教學(xué)效果。系統(tǒng)科學(xué)的成績(jī)分析，不僅能夠客觀公正的反映學(xué)生的學(xué)習(xí)狀態(tài)和知識(shí)技能的掌握程度，而且能夠引導(dǎo)教師發(fā)現(xiàn)教學(xué)過(guò)程中存在的不足和主要問(wèn)題，幫助教師正確認(rèn)識(shí)問(wèn)題背后的關(guān)鍵影響因素，促使教師不斷改進(jìn)教學(xué)方法，切實(shí)提高教學(xué)質(zhì)量。

一、成績(jī)分析方法

常用的成績(jī)分析方法有頻數(shù)分析法、集中量分析法、差異量分析法。頻數(shù)分析法包括等級(jí)頻數(shù)及頻數(shù)分布曲線，集中量分析法包括算數(shù)平均數(shù)、中位數(shù)、眾數(shù)，差異量分析法包括全距、平均差、標(biāo)準(zhǔn)差。教師手工計(jì)算上述參數(shù)存在一定難度，一方面許多教師沒(méi)有教育統(tǒng)計(jì)學(xué)基礎(chǔ)知識(shí)，不了解數(shù)理統(tǒng)計(jì)分析方法，另一方面手工計(jì)算過(guò)程紛繁復(fù)雜，不僅費(fèi)時(shí)費(fèi)力，而且容易出錯(cuò)，難以實(shí)現(xiàn)成績(jī)分析的高效性和準(zhǔn)確性，必須采用技術(shù)手段解決這一問(wèn)題?？紤]到《課程成績(jī)登記表》普遍采用Excel軟件，筆者以此為平臺(tái)進(jìn)行二次開(kāi)發(fā)，采用VBA開(kāi)發(fā)技術(shù)自定義解決方案，針對(duì)課程考核結(jié)果進(jìn)行自動(dòng)成績(jī)分析。

二、開(kāi)發(fā)工具

VBA（Visual Basic for Applications）是微軟公司開(kāi)發(fā)的一種通用控制Visual Basic編程語(yǔ)言，可以方便地調(diào)用和定制主應(yīng)用程序?qū)ο?。VBA內(nèi)嵌在Microsoft Office（Word、Excel、Access等）軟件中，用來(lái)擴(kuò)展Microsoft Office應(yīng)用程序功能。用戶可以根據(jù)需要編寫(xiě)VBA宏代碼，創(chuàng)建自定義解決方案，自動(dòng)完成重復(fù)工作，提高工作效率，實(shí)現(xiàn)高效辦公。VBA的開(kāi)發(fā)和運(yùn)行依賴主應(yīng)用程序，本課題利用Excel中內(nèi)嵌的VBA實(shí)現(xiàn)自動(dòng)成績(jī)分析。[1]

三、設(shè)計(jì)目標(biāo)

在《課程成績(jī)登記表中》設(shè)置“圖表生成”按鈕，鼠標(biāo)單擊后出現(xiàn)選擇對(duì)話框，顯示“請(qǐng)單擊某一單元格”，選擇某列數(shù)據(jù)標(biāo)題單元格作為分析對(duì)象，確定后自動(dòng)生成“成績(jī)分析”工作表，自動(dòng)統(tǒng)計(jì)并顯示總?cè)藬?shù)、平均分、中位數(shù)、標(biāo)準(zhǔn)差、最高分、最低分、各分?jǐn)?shù)段人數(shù)及百分比、峰度、偏度，自動(dòng)生成柱狀圖和正態(tài)分布曲線，如圖1所示。

四、功能設(shè)計(jì)

成績(jī)分析針對(duì)某個(gè)班級(jí)的某門(mén)課程進(jìn)行，VBA宏程序由數(shù)據(jù)準(zhǔn)備、數(shù)據(jù)統(tǒng)計(jì)和曲線圖生成三個(gè)模塊構(gòu)成。

數(shù)據(jù)準(zhǔn)備模塊包括原始成績(jī)錄入、數(shù)據(jù)列選擇、分析頁(yè)面生成三個(gè)環(huán)節(jié)。任課教師首先將原始成績(jī)錄入到《課程成績(jī)登記表》中，之后點(diǎn)擊“圖表生成”按鈕，在選擇對(duì)話框中確定某列數(shù)據(jù)作為分析對(duì)象，完成數(shù)據(jù)列選擇，單擊“確定”，程序根據(jù)用戶指定單元格所對(duì)應(yīng)的數(shù)據(jù)列自動(dòng)生成“成績(jī)分析表”。

數(shù)據(jù)統(tǒng)計(jì)模塊和曲線圖生成模塊是系統(tǒng)設(shè)計(jì)的核心部分，系統(tǒng)執(zhí)行VBA宏程序，自動(dòng)進(jìn)行數(shù)據(jù)計(jì)算并顯示統(tǒng)計(jì)結(jié)果，同時(shí)自動(dòng)生成柱狀圖和正態(tài)曲線圖。

1.數(shù)據(jù)準(zhǔn)備

原始數(shù)據(jù)錄入由任課教師手工完成，這里不再闡述。

原始成績(jī)錄入后，鼠標(biāo)單擊“圖表生成”按鈕，系統(tǒng)自動(dòng)彈出“請(qǐng)選擇”對(duì)話框，其宏代碼如下。

err： On Error Resume Next

Set myCell = Application.InputBox（Prompt：=" "， Title：="請(qǐng)選擇"， Default：="請(qǐng)單擊紅色字體中的某一單元格"， Left：=800， Top：=500， Type：=8）

If myCell Is Nothing Then Exit Sub

If myCell.Row <> 4 Then

MsgBox "單元格輸入錯(cuò)誤，請(qǐng)重新選擇"

GoTo err

ElseIf myCell.Column > 8 Then

MsgBox "單元格輸入錯(cuò)誤，請(qǐng)重新選擇"

GoTo err

ElseIf myCell.Column < 3 Then

MsgBox "單元格輸入錯(cuò)誤，請(qǐng)重新選擇"

GoTo err

ElseIf myCell.Value = False Then

MsgBox "請(qǐng)單擊紅色字體中的某一單元格"

GoTo err

如果正確選擇了紅色區(qū)域的某一單元格作為分析列，單擊“確定”后系統(tǒng)自動(dòng)進(jìn)入數(shù)據(jù)統(tǒng)計(jì)模塊。考慮到操作過(guò)程中可能出現(xiàn)錯(cuò)誤選擇單元格的實(shí)際情況，為了提高程序的容錯(cuò)性，增加了錯(cuò)誤處理程序，當(dāng)操作人員不慎選擇了非指定區(qū)域的其他單元格時(shí)，系統(tǒng)自動(dòng)彈出錯(cuò)誤提示框，如圖 2所示。

成績(jī)分析頁(yè)面（sheet2）生成部分的宏代碼如下。在生成成績(jī)分析頁(yè)面（sheet2）的同時(shí)，確定成績(jī)登記表（sheet1）中的數(shù)據(jù)統(tǒng)計(jì)范圍。

ColumnNo = myCell.Column

Set myCell_1 = Sheet1.Range（Cells（5， ColumnNo）， Cells（34， ColumnNo））

Set myCell_2 = Sheet1.Range（Cells（5， ColumnNo + 9）， Cells（34， ColumnNo + 9））endprint

Set ReturnRange = Sheet2.Range（Cells（2， 12）， Cells（52， 12））

Sheet2.Select

Sheet2.Activate

Sheet2.Visible = xlSheetVisible

2.數(shù)據(jù)統(tǒng)計(jì)

數(shù)據(jù)統(tǒng)計(jì)模塊包括總?cè)藬?shù)（CountA）、平均分（Sum/ CountA）、中位數(shù)（Median）、標(biāo)準(zhǔn)差（StDev）、最高分（Max）、最低分（Min）、峰度系數(shù)（Kurt）、偏度系數(shù)（Skew）。

總?cè)藬?shù)為點(diǎn)計(jì)數(shù)據(jù)，將學(xué)生人數(shù)作為點(diǎn)計(jì)結(jié)果。

平均分（算術(shù)平均數(shù)） 和中位數(shù)屬于集中量數(shù)據(jù)，用來(lái)描述數(shù)據(jù)集中趨勢(shì)的統(tǒng)計(jì)量，能夠反映成績(jī)分布的一般水平。平均分反映的是一組成績(jī)的總體平均水平。平均分的理想數(shù)值為75分，70-80分之間為合理范圍。中位數(shù)像一條分界線，將數(shù)據(jù)分成前半部分和后半部分，用來(lái)代表一組成績(jī)的中等水平。

標(biāo)準(zhǔn)差為差異量數(shù)據(jù)。可以直接地、概括地、平均地描述數(shù)據(jù)變異的大小，對(duì)于同質(zhì)性數(shù)據(jù)來(lái)說(shuō)，標(biāo)準(zhǔn)差越小，數(shù)據(jù)的變異程度越小，數(shù)據(jù)越整齊，分布范圍越集中，反之標(biāo)準(zhǔn)差越大，數(shù)據(jù)的變異程度越大，數(shù)據(jù)越參差不齊，分布范圍越廣。通常情況下，一組成績(jī)數(shù)據(jù)的標(biāo)準(zhǔn)差應(yīng)該在7-10之間為合理范圍，標(biāo)準(zhǔn)差小于5， 說(shuō)明分?jǐn)?shù)過(guò)度集中，試題區(qū)分度較低，標(biāo)準(zhǔn)差大于15，說(shuō)明分?jǐn)?shù)過(guò)度分散，試題區(qū)分度過(guò)高。

最高分和最低分為度量數(shù)據(jù)，既有相等單位又有絕對(duì)零點(diǎn)。

峰度系數(shù)和偏度系數(shù)表示頻數(shù)分布的形態(tài)。偏度表示正態(tài)分布曲線的對(duì)稱(chēng)性，-1.5<偏度<+1.5，符合標(biāo)準(zhǔn)正態(tài)分布的對(duì)稱(chēng)性，偏度>+1.5，為正偏或右偏（長(zhǎng)尾在右），說(shuō)明試題難度偏高，偏度<-1.5，為負(fù)偏或左偏（長(zhǎng)尾在左），說(shuō)明試題難度偏低。峰度表示正態(tài)分布曲線的陡緩程度，-1.5<峰度<+1.5，符合標(biāo)準(zhǔn)正態(tài)分布的陡峭性。峰度>+1.5，為陡峭型分布，說(shuō)明試題區(qū)分度過(guò)低，分?jǐn)?shù)分布過(guò)于集中，峰度<-1.5，為平坡型分布，說(shuō)明試題區(qū)分度較高，但分?jǐn)?shù)之間的差異偏大。

數(shù)據(jù)統(tǒng)計(jì)模塊的宏代碼如下：

Sheet2.Cells（21， 7） = WorksheetFunction.CountA（myCell_1， myCell_2）

Sheet2.Cells（21， 4） = WorksheetFunction.Sum（myCell_1， myCell_2） / WorksheetFunction.CountA（myCell_1， myCell_2）

Sheet2.Cells（22， 4） = WorksheetFunction.Median（myCell_1， myCell_2）

Sheet2.Cells（23， 4） = WorksheetFunction.StDev（myCell_1， myCell_2）

Sheet2.Cells（24， 4） = WorksheetFunction.Max（myCell_1， myCell_2）

Sheet2.Cells（25， 4） = WorksheetFunction.Min（myCell_1， myCell_2）

Sheet2.Cells（26， 4） = WorksheetFunction.Kurt（myCell_1， myCell_2）

Sheet2.Cells（27， 4） = WorksheetFunction.Skew（myCell_1， myCell_2）

除了上述統(tǒng)計(jì)數(shù)據(jù)之外，還要計(jì)算各分?jǐn)?shù)段人數(shù)及百分比，將分?jǐn)?shù)劃分為優(yōu)秀（90-100）、良好（80-89）、一般（70-79）、及格（60-69）、不及格（60以下）五個(gè)等級(jí)，統(tǒng)計(jì)不同等級(jí)區(qū)間的人數(shù)分布情況，其宏代碼如下：

Sheet2.Cells（22， 7） = WorksheetFunction.CountIfs（myCell_1， ">= 89.5"） + WorksheetFunction.CountIfs（myCell_2， ">= 89.5"）

Sheet2.Cells（22， 8） = Sheet2.Cells（22， 7） / Sheet2.Cells（21， 7）

Sheet2.Cells（23， 7） = WorksheetFunction.CountIfs（myCell_1， ">= 79.5"） + WorksheetFunction.CountIfs（myCell_2， ">= 79.5"） - Sheet2.Cells（22， 7）

Sheet2.Cells（23， 8） = Sheet2.Cells（23， 7） / Sheet2.Cells（21， 7）

Sheet2.Cells（24， 7） = WorksheetFunction.CountIfs（myCell_1， ">= 69.5"） + WorksheetFunction.CountIfs（myCell_2， ">= 69.5"） - Sheet2.Cells（23， 7） - Sheet2.Cells（22， 7）

Sheet2.Cells（24， 8） = Sheet2.Cells（24， 7） / Sheet2.Cells（21， 7）

Sheet2.Cells（25， 7） = WorksheetFunction.CountIfs（myCell_1， ">= 59.5"） + WorksheetFunction.CountIfs（myCell_2， ">= 59.5"） - Sheet2.Cells（24， 7） - Sheet2.Cells（23， 7） - Sheet2.Cells（22， 7）endprint

Sheet2.Cells（25， 8） = Sheet2.Cells（25， 7） / Sheet2.Cells（21， 7）

Sheet2.Cells（26， 7） = Sheet2.Cells（21， 7） - Sheet2.Cells（25， 7） - Sheet2.Cells（24， 7） - Sheet2.Cells（23， 7） - Sheet2.Cells（22， 7）

Sheet2.Cells（26， 8） = Sheet2.Cells（26， 7） / Sheet2.Cells（21， 7）

3.曲線圖生成

曲線圖生成包括直方圖生成和正態(tài)曲線圖生成兩部分。[2]

繪制直方圖的方法是：首先將0-100分以間隔2分的級(jí)差分成50個(gè)等級(jí)，分?jǐn)?shù)段設(shè)置為0、2、4、6、8、10……96、98、100，在VBA宏程序中使用FREQUENCY函數(shù)統(tǒng)計(jì)各分?jǐn)?shù)段人數(shù)，宏代碼如下：

If ColumnNo = 3 Then

Sheet2.Range（"l2：l52"）.FormulaArray = "=FREQUENCY（成績(jī)登記表！C$5：C$34，K$2：K$52）+FREQUENCY（成績(jī)登記表！L$5：L$34，K$2：K$52）"

ElseIf ColumnNo = 4 Then

Sheet2.Range（"l2：l52"）.FormulaArray = "=FREQUENCY（成績(jī)登記表！d$5：d$34，K$2：K$52）+FREQUENCY（成績(jī)登記表！m$5：m$34，K$2：K$52）"

ElseIf ColumnNo = 5 Then

Sheet2.Range（"l2：l52"）.FormulaArray = "=FREQUENCY（成績(jī)登記表！e$5：e$34，K$2：K$52）+FREQUENCY（成績(jī)登記表！n$5：n$34，K$2：K$52）"

ElseIf ColumnNo = 6 Then

Sheet2.Range（"l2：l52"）.FormulaArray = "=FREQUENCY（成績(jī)登記表！f$5：f$34，K$2：K$52）+FREQUENCY（成績(jī)登記表！o$5：o$34，K$2：K$52）"

ElseIf ColumnNo = 7 Then

Sheet2.Range（"l2：l52"）.FormulaArray = "=FREQUENCY（成績(jī)登記表！g$5：g$34，K$2：K$52）+FREQUENCY（成績(jī)登記表！p$5：p$34，K$2：K$52）"

Else

Sheet2.Range（"l2：l52"）.FormulaArray = "=FREQUENCY（成績(jī)登記表！h$5：h$34，K$2：K$52）+FREQUENCY（成績(jī)登記表！q$5：q$34，K$2：K$52）"

End If

完成各分?jǐn)?shù)段人數(shù)統(tǒng)計(jì)后，在Excel成績(jī)分析表界面中使用SERIES函數(shù)繪制直方圖，SERIES函數(shù)具體形式為“=SERIES（成績(jī)分析！$Q$1，成績(jī)分析！$K$2：$K$52，成績(jī)分析！$L$2：$L$52，1）”，如圖3所示。

繪制正態(tài)曲線圖的方法是使用NORMDIST函數(shù)，該函數(shù)用于返回指定平均值和標(biāo)準(zhǔn)差的正態(tài)分布函數(shù)。平均分（平均值）和標(biāo)準(zhǔn)差在前面已經(jīng)獲得，將其帶入NORMDIST函數(shù)生成正態(tài)曲線圖。NORMDIST函數(shù)的具體形式為：“=NORMDIST（K2，D$21，D$23，0）”，其中D$21單元格的內(nèi)容為平均分?jǐn)?shù)值，D$23單元格的內(nèi)容為標(biāo)準(zhǔn)差數(shù)值。

完成正態(tài)分布函數(shù)計(jì)算后，在Excel成績(jī)分析表界面中仍然使用SERIES函數(shù)繪制正態(tài)分布圖，SERIES函數(shù)具體形式為“=SERIES（成績(jī)分析！$S$1，成績(jī)分析！$K$2：$K$52，成績(jī)分析！$S$2：$S$52，2）”，如圖4所示。

五、結(jié)束語(yǔ)

系統(tǒng)實(shí)現(xiàn)了設(shè)計(jì)目標(biāo)和功能要求，以Excel軟件為平臺(tái)進(jìn)行二次開(kāi)發(fā)，按照成績(jī)分析工作的實(shí)際要求編寫(xiě)宏代碼，開(kāi)發(fā)成果具有較強(qiáng)的實(shí)用性和適用性。界面簡(jiǎn)潔清晰、操作簡(jiǎn)單，運(yùn)行速度快，實(shí)現(xiàn)了成績(jī)分析工作的科學(xué)性、高效性和準(zhǔn)確性。

參考文獻(xiàn)：

[1]李萍.利用ExcelVBA實(shí)現(xiàn)考場(chǎng)清冊(cè)中照片的批量插入[J].中國(guó)教育信息化，2013（4）：81-83.

[2]馬瑩瑩.用Excel畫(huà)直方圖和正態(tài)分布圖[J].科技信息，2011（21）：269-270.（編輯：魯利瑞）endprint