基于Excel VBA的成績管理系統的探索與實現

楊發友 曹瀚天 黃恩相 張光福 鐵衛華

摘? 要：文章以云南水利水電職業學院的成績管理為研究對象，以Excel 2010為平臺，采用VBA編程，設計了一款能夠自動匯總班級成績并可以統計出補考學生名單的成績管理系統。利用Excel VBA實現學生成績管理的自動化處理，提高工作效率和數據處理能力，加強數據處理結果的準確性，實現學生成績信息管理工作流程的系統化、規范化和自動化，降低工作量，提高工作效率。

關鍵詞：VBA;自動生成;排版;成績管理

中圖分類號：TP311.52? ? ? 文獻標識碼：A 文章編號：2096-4706（2020）22-0011-05

Exploration and Implementation of Achievement Management System

Based on Excel VBA

YANG Fayou，CAO Hantian，HUANG Enxiang，ZHANG Guangfu，TIE Weihua

（Yunnan Water Resources and Hydropower Vocational College，Kunming? 650499，China）

Abstract：The article takes the achievement management of Yunnan Water Resources and Hydropower Vocational College as the research object，uses Excel 2010 as the platform，and uses VBA programming to design a achievement management system that can automatically summarize class results and make statistics on the list of students who take the retake exam. Using Excel VBA to realize the automatic processing of student achievement management，improve work efficiency and data processing ability，strengthen the accuracy of data processing results，realize the systematization，standardization and automation of student achievement information management workflow，reduce workload and improve work efficiency.

Keywords：VBA;automatic generation;typesetting;achievement management

0? 引? 言

經過多年的教育信息化發展，以互聯網為基礎的成績管理系統在各級各類學校中得到了普及。但經過調查，仍然有很多學校還沒有專門的成績管理系統，學校對成績的管理還停留在使用Excel進行管理。

Excel雖然本身具有很強的數據處理能力，但是存在以下兩個問題：一是由于Excel的網絡功能有限，對數據錄入的工作很難實現多人在線收集;二是雖然教師們都熟悉Excel，但是該軟件的強大的數據處理功能要想發揮出來，需要掌握復雜的公式或者VBA編程才能實現，這一點卻少有人能夠精通。

云南水利水電職業學院目前有5個二級學院，將近5 000名在校學生，開設26個專業，每學期開設約200門課程，包含理論課和實踐課。由于學院有在線成績管理系統，但有部分教師暫不能接受新系統使用操作，特別年長的老教師習慣了使用Excel統計各科成績后上報教務處，再由教務處按照要求錄入系統，生成班級成績匯總表，進行排名和補考統計。每次考試結束后，教務處的工作量非常大，不得不抽調其他工作人員進行支援。這樣做，不僅工作效率低，而且學生成績錄入錯誤的風險非常大。

解決這個問題最好的辦法是升級優化或重新采購一套操作更簡潔的在線成績管理系統，但是由于學校經費有限，近幾年都沒有這方面的資金預算。

鑒于以上原因，本文從云南水利水電職業學院成績管理需求出發，通過分析學校需求，從Excel本身的功能開發考慮，用VBA開發了一套低成本的解決方案。

1? 研究的意義

我國的教育信息化經過多年的發展，已經取得了長足的進步。市場上不乏優秀的成績管理系統，但是這些系統要么就是商業化嚴重，雖然好用，后臺服務也能及時跟進，但是價格不菲;要么就是地方教育行政管理部門推廣的系統，具有一定的優勢，但是售后服務跟不上。有的學校自己有能力開發各種子系統，但是也存在各子系統之間數據不統一等問題。

Microsoft Office是廣大學校教師最熟悉也是使用最多的辦公軟件，Excel軟件是有強大的數據處理能力的表格系統，可以存儲各種類型的數據。Visual Basic宏語言（Visual Basic for Application，VBA）是Office內嵌的一種完全面向對象的編程語言。Excel本身雖然具有非常強大的數據處理能力，但是往往需要大量復雜的公式或者用VBA進行二次開發，才能使Excel的功能得以充分發揮。運用VBA開發成績管理系統，Excel軟件既是應用程序又是數據庫，去避免了應用程序和數據庫軟件頻繁交換數據的問題。

學生成績是學生評先評優的重要依據，同時也是學生是否需要補考、能否達到畢業條件的重要考核指標。考試成績管理，是學校教學管理最重要的環節之一，成績能否及時收集固定、是否能準確無誤的進行統計匯總，關系學校管理的嚴肅性、嚴謹性。利用VBA技術，只需把Excel成績單模板進行格式統一，這樣就能使數據標準化，再利用VBA編程，就能輕松對成績進行收集、管理。

2? 所用VBA關鍵技術的介紹

本系統直接用Excel+VBA進行開發。VBA是應用程序開發語言VB（Visual Basic）的子集，一般是嵌入Excel等已有應用程序中對其進行二次開發，提高自動化功能。VBA編程可以自定義Excel的工具欄、菜單和界面，對數據進行復雜的操作和分析，使Excel突破自身局限，成為開發平臺，從而實現基于VBA的成績管理系統的各項功能。

使用VBA開發有以下幾個好處：

（1）VBA設計的窗體雖然不能脫離Excel獨立運行，但可以讓Excel在后臺工作，并利用Windows API，可以設計浮動在桌面上的窗體，讓使用者感覺不出是Excel中的窗體;

（2）使用Excel軟件中的VBA開發系統，主要是要充分利用Excel軟件自身的優勢，Excel軟件還有很多內置的函數可以調用，在VBA中使用非常方便;

（3）VBE（VBA的編輯器）提供了模塊功能，可以根據功能需要編寫模塊代碼，實現模塊化編程功能，也非常利于系統功能的擴展升級;

（4）用VBA讀取Excel文件中的數據比讀取數據庫的數據更方便，而且程序的設計更簡單。

因此本系統選用Excel+VBA作為平臺和工具，是十分便捷和高效的。

3? 系統的設計分析與實現

本系統分別由成績報告單、成績收集匯總表、補考統計表、成績收集模塊、補考統計模塊、用戶窗體6部分組成。

3.1? 用戶登錄驗證及添加用戶窗體

運行軟件后，系統會自動進入此窗口，如圖1（a）所示，選擇對應的“用戶名”有“成績管理員、臨時用戶”兩種，輸入正確“密碼”即可登錄;也可以點擊“新用戶注冊”如圖1（b）所示，添加“用戶名”“密碼”后點“添加”即完成新用戶注冊。

（a）用戶登錄驗證窗口

（b）新用戶注冊窗口

圖1? 用戶登錄驗證及新用戶注冊窗口

此登錄驗證模塊能很好解決管理員用戶和普通用戶的權限問題，能夠有效追蹤成績出錯環節，從而進行責任劃分;同時也能添加不同用戶，方便管理。

實現程序代碼為：

Private Sub cmbUsername_Change（）

End Sub

Public Sub cmdExit_Click（）

Unload Me

Application.Quit

ActiveWorkbook.Close savechanges：=False

End Sub

Private Sub cmdNewyh_Click（）

frmAddUser.Show

End Sub

Private Sub lblWelcome_Click（）

End Sub

Private Sub UserForm_QueryClose（Cancel As Integer， CloseMode As Integer）

If CloseMode = 0 Then

Cancel = True

End If

End Sub

Private Sub UserForm_Initialize（）

N1 = Application.WorksheetFunction.CountA（Sheet3.Range（"b：b"））

AA1 = "b2：b"& N1

Sheet3.Activate

cmbUsername.RowSource = AA1

End Sub

Private Sub cmdLogin_Click（）

Dim I As Integer，Username As String，Password As String

N1 = Application.WorksheetFunction.CountA（Sheet3.Range（"b：b"））

For I = 2 To N1

Username = cmbUsername.Value

Password = txtPassword.Text

If Username = Sheets（3）.Cells（I，2） Then

If Password = Sheets（3）.Cells（I，3）

ThenMsgBox cmbUsername.Value &"，歡迎您進入系統！"， 48，"提示"

Unload Me

ElseMsgBox "請正確輸入用戶名和密碼！"，48，"提示"

cmbUsername.Value = ""

txtPassword.Text = ""

cmbUsername.SetFocus

End If

Exit Sub

End If

Next

End Sub

Private Sub cmdAdd_Click（）

Dim sht As Worksheet

Set sht = Worksheets（"用戶"）

Dim X As Integer

X = sht.Range（"A1"）.CurrentRegion.Rows.Count

sht.Cells（X + 1，1） = sht.Cells（X，1）.Value + 1

sht.Cells（X + 1，2） = AddUsername.Value

sht.Cells（X + 1，3） = AddPassword.Value

End Sub

Public Sub cmdExit_Click（）

Unload Me

Application.Quit

ActiveWorkbook.Close savechanges：=True

End Sub

3.2? 成績匯總統計模塊

當用戶驗證成功后，在“成績收集匯總”表中點擊“一鍵收成績”，如圖2所示，在彈出的提示對話框內輸入需要匯總的“班級名稱”如：輸入“150175”確定后，系統即運行后臺程序代碼，把指定班級文件夾中收集的各科成績報告單進行匯總，并能自動排版和實現成績匯總和排名等功能，生成統一的匯總表可以直接打印和存檔。

實現程序為：

Sub 一鍵收集成績并匯總（）

Dim myPath$，myFile$，AK As Workbook，Tk As Workbook，I，j，n，m，X，Y，Num1，Num2，Num3，Wjs As Integer

Application.ScreenUpdating = False

Sheet1.[A1].CurrentRegion.Clear

Dim className As String

className = Application.InputBox（"請輸入班級名稱"，"班級名稱"，""，510，380）

If className = "" Then Exit Sub

myPath = ThisWorkbook.Path &"＼"& className &"＼"

Set Tk = ThisWorkbook

myFile = Dir（myPath &"*.xls"）

Do While myFile <>""

If myFile <> Tk.Name Then

a = CreateObject（"scripting.FileSystemObject"）.GetFolder （myPath）.Files.Count

For j = 6 To 5 + a

Set AK = Workbooks.Open（myPath & myFile）

AK.Sheets（1）.Unprotect Password：="cxb"

Num1 = AK.Sheets（1）.Range（"A2"）.CurrentRegion.Rows.Count? ? ? ? ?AK.Sheets（1）.Range（"f7"）.Copy Tk.Sheets（1）.Cells（4，j）

AK.Sheets（1）.Range（"f8"）.Copy Tk.Sheets（1）.Cells（3，j）

AK.Sheets（1）.Range（"f9"）.Copy Tk.Sheets（1）.Cells（2，j）

Tk.Sheets（1）.Cells（1，j） = AK.Sheets（1）.[f6]? ? ? ? ? Tk.Sheets（1）.[b1] = AK.Sheets（1）.[f4]

Tk.Sheets（1）.[b2] = AK.Sheets（1）.[f3]

For I = 2 To Num1

AK.Sheets（1）.Range（"a"& I）.Copy Tk.Sheets（1）.Cells（I + 2，2）

AK.Sheets（1）.Range（"b"& I）.Copy Tk.Sheets（1）.Cells（I + 2，3）

Tk.Sheets（1）.Cells（I + 3，1） = AK.Sheets（1）.[f2]? ? ? ? ? ? ?AK.Sheets（1）.Range（"c"& I + 1）.Copy Tk.Sheets（1）.Cells（I + 3，j）

Next

Workbooks（myFile）.Close False

myFile = Dir

Next

End If

Loop

Tk.Sheets（1）.[A1] = "應考人數"

Tk.Sheets（1）.[A2] = "專業"

Tk.Sheets（1）.[a3] = "單人單科成績"

Tk.Sheets（1）.[a4] = "班級"

Tk.Sheets（1）.[e1] = "學期"

Tk.Sheets（1）.[e2] = "考試類別"

Tk.Sheets（1）.[e3] = "任課教師"

Tk.Sheets（1）.[c1] = "日期"

Tk.Sheets（1）.[d4] = "總分"

Tk.Sheets（1）.[e4] = "名次"

Tk.Sheets（1）.[d1] = "=today（）"

Num2 = Tk.Sheets（1）.Range（"b5"）.CurrentRegion.Rows.Count

Num3 = Tk.Sheets（1）.Range（"f4"）.CurrentRegion.Columns.Count

Tk.Sheets（1）.Cells（5，4）.Select

For n = 5 To Num2 - 1

For m = 6 To Num3

Tk.Sheets（1）.Cells（n，4） = "=Sum（RC[2]：RC["& Num3 - 4 &"]）"

Next

Next

Tk.Sheets（1）.Cells（5，5）.Select

For n = 5 To Num2 - 1

Tk.Sheets（1）.Cells（n，5）.Select

ActiveCell.FormulaR1C1 = "=RANK（RC[-1]，R5C4：R"& Num2 - 1 &"C4，0）"

Next

Num2 = Tk.Sheets（1）.Range（"b5"）.CurrentRegion.Rows.Count

Tk.Sheets（1）.Cells（Num2，1） = "合計與不及格統計"

ActiveCell.FormulaR1C1 = "=SUM（R["& -Num2 + 5 &"]C：R[-1]C）"

Tk.Sheets（1）.Cells（Num2，5）.Clear

Tk.Sheets（1）.[b3].Select

ActiveCell.FormulaR1C1 = "=R["& Num2 - 3 &"]C[2]/R[-2]C[0]/（"& Num3 - 5 &"）"

Selection.NumberFormatLocal = "0.000_ "

For m = 6 To Num3

Tk.Sheets（1）.Cells（Num2，6）.Select?Cells（Num2，m） = _"=COUNTIF（R["& -Num2 + 5 &"]C：R[-1]C，""<60""） +COUNTIF（R["& -Num2 + 5 &"]C：R[-1]C，""缺考""）+COUNTIF （R["& -Num2 + 5 &"]C：R[-1]C，""作弊""）+COUNTIF（R["& -Num2 + 5 &"]C：R[-1]C，""補缺""）"

Next

Num2 = Tk.Sheets（1）.Range（"b5"）.CurrentRegion.Rows.Count

Num3 = Tk.Sheets（1）.Range（"f4"）.CurrentRegion.Columns.Count

For X = 5 To Num2 - 2

For Y = 6 To Num3

If Cells（X，Y） <>"" Then

If Cells（X，Y） < 60 Or Cells（X，Y） = "缺考" Or Cells（X，Y） = "作弊" Or Cells（X，Y） = "補缺" Then

Cells（X，Y）.Interior.Color = 13551615

End If

End If

Next

Next

Tk.Sheets（1）.Range（"a1"）.CurrentRegion.Select

With Selection

.Borders.Weight = xlThin

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

End With

With Sheet1.Columns.AutoFit

End With

Dim myPath2 As String

Dim FileName2 As StringmyPath2 = "D：＼學校成績收集匯總"

FileName2 = Tk.Worksheets（1）.Range（"a5"）.Value

Tk.Worksheets（1）.SaveAs Filename：=myPath2 &"＼"& FileName2 &"班成績匯總"

Application.ScreenUpdating = True

MsgBox "匯總完成，請查看！"，64，"提示"

End Sub

3.3? 補考統計模塊

當用戶驗證成功后，在“成績收集匯總”表中點擊“一鍵統計補考”，如圖3所示，系統即運行后臺程序代碼，在匯總的成績報告表中，篩選出成績“小于60”“缺考”“作弊”“補缺”“緩考”的人員及名單及需“補考科目”等相關信息匯總到“補考統計”表中。

實現程序為：

Sub縱向補考便于教務用（）

DimI

arr=Sheet1.[A1].CurrentRegion

ReDimBRR（1ToUBound（arr）*10，1To8）

ForI=5To（UBound（arr）-2）

Forj=6ToUBound（arr，2）

Ifarr（I，3）<>""Then

Ifarr（I，j）<60Orarr（I，j）="缺考"Orarr（I，j）="作弊"Orarr（I，j）= "補缺"Orarr（I，j）="緩考"Then

K=K+1

BRR（K，1）=arr（I，1）

BRR（K，2）=arr（I，2）

BRR（K，3）=arr（I，3）

BRR（K，4）=arr（4，j）

BRR（K，5）=arr（I，j）

BRR（K，6）=arr（3，j）

BRR（K，7）=arr（1，j）

BRR（K，8）=arr（2，j）

EndIf

EndIf

Next

Next

Sheet2.[A2].CurrentRegion.Offset（1）.ClearContents

Sheet2.[A2].Resize（UBound（BRR），8）=BRR

Sheet2.UsedRange.Borders.LineStyle=xlNone

Sheet2.UsedRange.SpecialCells（xlCellTypeConstants，3）.Borders.LineStyle=xlContinuous

WithSheet2

.[a1：h1]=Array（"班級"，"學號"，"姓名"，"補考科目"，"成績"，"任課教師"，"學期"，"考試類別"）

.Columns（"a：h"）.AutoFit

EndWith

EndSub

4? 系統主要功能

4.1? 成績報告單規范化

通過設計統一的成績報告單，使過去老師們報告成績格式不統一的情況得以完全改善，成績報告單設置了工作表保護，任課教師不能隨意更改學生等信息，填入報告單的數據也只能按照設置統一格式填寫，這樣就規范了成績數據。

4.2? 成績自動收集匯總功能

考試結束后，班主任向老師們收集各班的各科成績后，報給教務員，教務員一鍵即可生成各班的成績匯總表，自動匯總、自動排名、一鍵打印。

4.3? 補考統計功能

過去補考統計也是一項復雜的工作，使用該系統后，教務員可一鍵統計出各班各同學的補考科目，真正實現辦公自動化。

5? 結? 論

研究環境以Excel 2010為平臺，利用VBA編程語言，開發出成績管理系統，能適用于各中小型規模學校，一方面能大大減輕教務處工作人員的工作量。經驗證，過去需要10多位老師加班五六天的工作量，使用該系統后，只需要教務員1人加班三個小時左右即可完成全部成績匯總和補考統計工作。此外，由于VBA編程語言的普適性，系統的可移植性也非常高，稍做修改就可改為其他類似的管理系統。

由于筆者的技術水平有限，該系統基于Excel 2010采用VBA編程開發，沒有考慮在線收集成績的功能，所以還需要人工點選收集匯總。最新版本Office或WPS網絡功能得以增強，后續可以在網絡功能開發上進一步開發，就可以設計出適應其他學校需要的成績管理系統。

參考文獻：

[1] Excel Home.Excel 2007 VBA實戰技巧精粹 [M].北京：人民郵電出版社，2013.

[2] Excel Home.別怕，Excel VBA其實很簡單：第1版 [M].北京：人民郵電出版社，2012.

[3] 郭剛.Excel VBA入門與應用典型實例 [M].北京：北京希望電子出版社，科學出版社，2009.

[4] 王鴻儒.Excel VBA程序設計 [M].北京：中國鐵道出版社，2005.

[5] 魏汪洋.從零開始學Excel VBA：第2版 [M].北京：電子工業出版社，2014.

[6] 伍遠高.Excel VBA編程實戰寶典 [M].北京：清華大學出版社，2014.

[7] 李政，王月，鄭月鋒，等.VBA應用基礎與實例教程：第2版 [M].北京：國防工業出版社，2009.

[8] 張峋.Excel VBA入門與典型實例 [M].北京：清華大學出版社，2007.

[9] 李洋.Excel函數、圖表與數據分析應用實例 [M].北京：清華大學出版社，2007.

作者簡介：楊發友（1985.09—），男，漢族，云南賓川人，實驗師，本科，研究方向：網絡與信息安全;曹瀚天（1984.10—），男，漢族，云南祿豐人，講師，本科，研究方向：電氣工程;黃恩相（1970.12—），男，漢族，云南梁河人，高級實驗師，本科，研究方向：電氣工程及自動化;張光福（1979.06—），男，漢族，云南雙江人，經濟師，本科，研究方向：高校內控及信息化;鐵衛華（1975.12—），男，漢族，云南賓川人，高校講師，在職研究生，研究方向：電氣工程及計算機。