基于ＶＢＡ與Ｅｘｃｅｌ的高校成績統計分析模板設計

摘要：該文應用VBA（Visual Basic for Application）對Excel二次開發的功能，編程設計了成績統計與分析模板，實現了學生考試成績的自動統計和填寫、自動完成了各分數段人數和重點觀測指標的統計、自動生成了分析圖表等。應用該模板，可以大大減輕教師進行學生考試成績統計與分析工作帶來的負擔，對教學管理中的學生成績統計與分析具有一定的推廣使用價值。

關鍵詞：VBA；Excel；成績統計與分析；模板；設計

中圖分類號：TP311文獻標識碼：A文章編號：1009-3044(2008)35-2367-04

The Template Design of Result Statistics and Analysis Based on VBA and Excel

GUI Lin-bin

(Computer and Information Engineering Department， Yunnan Vocational Institute of Energy Technology， Qujing 655001， China)

Abstract: This article applied VBA (Visual Basic for Application) to the Excel re-development functions， the programming has designed the result statistics and the analysis template， realized the student to take a test the result automatic statistics and filling in， has completed various scores section population and the key observation target statistics automatically， has produced the analysis graphics automatically and so on. Using this template， may reduce the teacher to carry on the student greatly to take a test the burden which the result statistics and the analysis work bring， has certain promotion use value to in the teaching management student result statistics and the analysis.

Key words: VBA;excel;result statistics and analysis; template; design

1 引言

教師在教學工作中，每學期末都要按照本校的管理要求和表格樣式，統計、填寫和報送學生的考試成績，同時還要按下發的成績分析表對進行分析和填寫。重復和繁瑣的手工操作，加大了教師的工作負擔，作為管理人員必須考慮兩方面的問題，首先，必須將紙質表格電子化，其次，必須將電子化的表格模板化。只有這樣，才能讓教師減輕工作負擔，從形式化的工作方式中解脫出來，節省更多的時間，以便投入到更有價值的工作中。

筆者針對上述問題，應用VBA對Excel二次開發的功能，編程設計了成績統計與分析模板，實現了學生考試成績的自動統計和填表，同時在成績分析表中自動完成了各分數段人數和重要觀測指標的統計、自動生成了分析圖表等。

2 Excel 的 ActiveX 對象模型

2.1 Workbooks 集合對象

一個 WorkBook 對象實際上就是一個 Excel 文件，Excel 應用程序可以同時打開或創建多個文件，它們被保存在 WorkBooks 集合對象中，可以通過索引號或名稱訪問集合中的任何一個工作簿。

例：'創建一個新的工作薄

Workbooks.Add

Workbooks.open(\"E:\\abc.xls\")'打開 E 盤根目錄下的 abc.xls 工作薄

2.2 Worksheets 對象

每個工作簿對象上可以有多個工作表 WorkSheet。在默認情況下， Excel 的當前工作簿上有名為Sheet1，Sheet2，Sheet3 三個工作表，并且 Sheet1 為當前工作表。如果想使 Sheet3 成為當前工作表，則可使用下列語句:

ExcelApp.Worksheets(\"Sheet3\").Activate

2.3 Range 對象

該對象用來指定工作表上的區域。例如：'將第 5 行格式復制至 100 行，語句如下：

Range(\"5:5\").Select

Selection.Copy

Range(\"5:100\").Select

Selection.PasteSpecial Paste:=xlPasteFormats， Operation:=xlNone

1.4 Cells 屬性

工作表對象中的Cells 屬性，在單元格的選擇方面可以達到與Rang相同的效果，它是以行Row和列Col作為參數的，如下語句所示：

Cell(3，4) =5 '將第三行第四列的單元格賦值5。

3 成績統計與分析模板設計

3.1 成績統計與分析模板建立步驟及功能說明

1） 打開 Excel 后，將sheet1、sheet2、sheet3工作表分別改名為“首頁”、“成績統計”和“成績分析”。

2） 選擇“首頁”工作表，按圖1 格式建立和設置相關表格格式并填寫相關基礎數據，設定“學期”成績計算公式：學期=ROUND(平時+期中*百分比+期末*百分比，0)，當輸入“平時”成績和“期中”或“期末”卷面成績時，學期成績自動按公式計算填寫。

3）定義單元格名稱。將“任課教師”、“專業班級”、“課程名稱”、“應考人數”、“實考人數”、“學年”、“學期”、“期中、期末”、“卷屬(A、B)”、“考試時間”、“平時成績占”、“考試成績占” 單元格對應右邊的數據單元格，名稱分別定義為“教師”、“班級”、“課程”、“應考人數”、“實考人數”、“學年”、“學期”、“期中、期末”、“卷屬”、“考試時間”、“平時成績”、“考試成績”，以方便在VBA代碼中按單元格名稱引用。

4） 在表格的下邊建立一個“下一步”按鈕。選擇“視圖”→“工具欄”→“控件工具箱”，在“控件工具箱”中單擊 “命令按鈕”，然后在工作表中畫一個命令按鈕。雙擊該按鈕進入VBA編輯界面，打開屬性對話框，將命令按鈕的“名稱”屬性設置為jnext、caption屬性設置為“下一步>>”。

5） 打開“成績統計”工作表，按圖2左所示完成基礎表格設置。表頭名稱及相關信息由代碼自動完成，表格行數、單元格格式由代碼按“應考人數”自動增加和設置。

圖2 成績統計設計時及運行后樣表

6） 打開“成績分析”工作表，按圖3所示完成基礎表格設置。

7） VBA程序代碼編寫。選擇菜單“工具”→“宏”→“Visual Basic編輯器”，打開“工程資源管理器”，在“首頁”代碼窗口中，對“下一步>>”按鈕編寫鼠標單擊事件代碼。

8） VBA代碼功能說明。當單擊“下一步>>”按鈕時，在“成績統計”工作表中，自動按“首頁”中輸入的“應考人數”增加表格行，按順序分欄填寫“學號”、從“首頁”表中復制“姓名”、“平時”、“期中”、“期末” 成績、計算并填寫“學期”成績。在“成績分析”工作表中自動填入表頭信息、自動根據“首頁”中的“學期”成績進行各分數段、重點觀測指標的統計填寫，同時自動生成分數段人數折線圖表。

3.2 VBA程序代碼編寫

統計模板中的統計計算、填表、生成圖表等由程序自動完成，相應單元格也可以手動修改，只需在“首頁”工作表中填寫基本數據，其余的都可以由程序自動完成。下面給出VBA程序參考代碼。

Sub jNext_Click() '下一步按鈕單擊事件

Const Row As Integer = 7 '第一行記錄前的起始行號

Dim i， j， a， b， m， n， t， x， y As Integer

Dim SourceRange， FillRange As Range

Dim f As String

Sheets(\"成績統計\").Select '切換到成績統計表

t = Worksheets(\"首頁\").Range(\"應考人數\")

x = Round(t / 2， 0)

If (t / 2 - x) > 0 Then

x = Round(t / 2 + 0.5， 0)

Else

x = Round(t / 2， 0)

End If

y = x + 7

If t > 50 Then

Worksheets(\"成績統計\").Range(\"a8:l8\").Select

Selection.Copy

Worksheets(\"成績統計\").Range(\"a9:l\" y).Select

Selection.PasteSpecial Paste:=xlPasteFormats， Operation:=xlNone

Else

End If

'把當前工作表的頁腳右邊設置為“第X頁 共X頁”的格式，格式控制代碼P 頁碼 N 共X頁

With ActiveSheet.PageSetup

.RightFooter = \"第P頁 共N頁 \" '頁腳 右

End With

With Sheets(\"成績統計\")

If t > 50 Then'根據應考人數按左右兩列格式給學號及各項成績賦值

For i = 1 To x

.Cells((i + Row)， 1) = i

For m = 2 To 6

.Cells((i + Row)， m) = Worksheets(\"首頁\").Cells((i + 3)， m)

Next m

Next i

For j = 1 To t - x

.Cells((j + Row)， 7) = j + x

For n = 8 To 12

.Cells((j + Row)， n) = Worksheets(\"首頁\").Cells((j + x + 3)， (n - 6))

Next n

Next j

ElseIf t > 25 Then

For i = 1 To 25

.Cells((i + Row)， 1) = i

For x1 = 2 To 6

.Cells((i + Row)， x1) = Worksheets(\"首頁\").Cells((i + 3)， x1)

Next x1

Next i

For j = 1 To t - 25

.Cells((j + Row)， 7) = j + 25

For y1 = 8 To 12

.Cells((j + Row)， y1) = Worksheets(\"首頁\").Cells((j + 28)， (y1 - 6))

Next y1

Next j

Else

For i = 1 To t

.Cells((i + Row)， 1) = i

For m1 = 2 To 6

.Cells((i + Row)， m1) = Worksheets(\"首頁\").Cells((i + 3)， m1)

Next m1

Next i

End If

.Cells(2， 2) = Worksheets(\"首頁\").Range(\"學年\") \"學年\" Worksheets(\"首頁\").Range(\"學期\") \"學期學生成績報告表\"

.Cells(4， 2) = \"專業班次：\" Worksheets(\"首頁\").Range(\"班級\") \"課程名稱：\" Worksheets(\"首頁\").Range(\"課程\") \"教師姓名：\" Worksheets(\"首頁\").Range(\"教師\")

End With

Sheets(\"成績分析\").Activate

With Worksheets(\"成績分析\")

.Cells(1， 2) = \"成 績 分 析 表\"

.Cells(2， 2) = Worksheets(\"首頁\").Range(\"學年\") \"學年\" Worksheets(\"首頁\").Range(\"學期\") \"學期\" \"(\" Worksheets(\"首頁\").Range(\"期中、期末\") \")考試 \" \"卷屬：\" Worksheets(\"首頁\").Range(\"卷屬\")

.Cells(4， 3) = Worksheets(\"首頁\").Range(\"課程\")

.Cells(4， 6) = Worksheets(\"首頁\").Range(\"考試時間\")

.Cells(5， 3) = Worksheets(\"首頁\").Range(\"班級\")

.Cells(5， 5) = Worksheets(\"首頁\").Range(\"應考人數\")

.Cells(5， 7) = Worksheets(\"首頁\").Range(\"實考人數\")

.Cells(29， 3) = Worksheets(\"首頁\").Range(\"教師\")

.Cells(29， 5) = Date

End With

For i = 1 To t'計算總成績和最高分

Sum = Sum + Worksheets(\"首頁\").Cells(i + 3， 6)

If Max <= Worksheets(\"首頁\").Cells(i + 3， 6) Then

Max = Worksheets(\"首頁\").Cells(i + 3， 6)

End If

Next i

Min = Worksheets(\"首頁\").Cells(4， 6)'計算最低分

For j = 1 To t

If Min >= Worksheets(\"首頁\").Cells(j + 3， 6) Then

Min = Worksheets(\"首頁\").Cells(j + 3， 6)

End If

Next j

For m = 1 To t '統計各分數段人數

Select Case Worksheets(\"首頁\").Cells(m + 3， 6)

Case Is <= 54

h1 = h1 + 1

……

Case Is >= 90

h9 = h9 + 1

End Select

Next m

Worksheets(\"成績分析\").Cells(6， 5) = h1 + h2'計算填寫成績分析表中各項數據

Worksheets(\"成績分析\").Cells(7， 6) = Max

Worksheets(\"成績分析\").Cells(8， 6) = Min

Worksheets(\"成績分析\").Cells(9， 6) = Sum / Worksheets(\"首頁\").Range(\"應考人數\")

Worksheets(\"成績分析\").Cells(10， 6) = (h3 + h4 + h5 + h6 + h7 + h8 + h9) / Worksheets(\"首頁\").Range(\"應考人數\")

Worksheets(\"成績分析\").Cells(11， 6) = (h1 + h2) / Worksheets(\"首頁\").Range(\"應考人數\")

Worksheets(\"成績分析\").Cells(7， 3) = h1'在成績分析表相應單元格自動填寫各分數段人數h1-h9

……

Worksheets(\"成績分析\").Cells(15， 3) = h9

Charts.Add'自動根據各分數段人數生成圖表

ActiveChart.ChartType = xlLineMarkers

ActiveChart.SetSourceData Source:=Sheets(\"成績分析\").Range(\"b7:c15\")， PlotBy:=xlColumns

ActiveChart.Location Where:=xlLocationAsObject， Name:=\"成績分析\"

With ActiveChart

.HasTitle = False

.Axes(xlCategory， xlPrimary).HasTitle = False

.Axes(xlValue， xlPrimary).HasTitle = False

End With

End Sub

說明：本程序在Excel2003 VBA下調試通過。

4 結論

基于VBA和Excel進行二次開發，簡便易行，各個行業的具體應用都可以定制開發，應用面廣泛。通過簡潔的編程，可以容易地解決日常工作中重復和繁瑣的手工統計和填表工作，減輕了工作負擔，極大地提高了工作效率。

參考文獻：

[1] 張雪鳳，桂林斌.Visual Basic程序設計[M].北京:中國財政經濟出版社，2005.

[2] 李政等.VBA應用基礎及實例教程[M].北京:國防工業出版社，2005.

[3] 李旭瑋，羅璇.應用VBA實現EXCEL自動化[J].科技咨詢， 2007(31):99-100.