基于Excel VBA的等級考試成績管理系統(tǒng)設計

蔣 勇

(揚州職業(yè)大學, 江蘇 揚州 225009)

高校學生對于各類等級考試,例如全國英語四六級考試、全國計算機等級考試等都非常重視,因為這些考試證書是學生畢業(yè)后就業(yè)的重要材料之一,另外班級各種等級考試過關率的數(shù)據(jù)也成為學校先進班級評選條件和院系年終總結材料。但是由于等級考試要求的特殊性,例如全國英語四六級考試,只有英語四級過關的學生才可以報考英語六級,有的學生英語四級過關后,也不一定參加英語六級的考試,而四級沒有過關學生還需要繼續(xù)報名英語四級考試,因此每次等級考試并不是全體學生都參加,所以教務處下發(fā)到各院系的等級考試成績中學號都是不連續(xù)的,且各班學生等級考試成績都是分散在各個時期等級考試的成績表中,而要查詢或匯總本院系學生各類等級考試的成績十分麻煩,需要在不同表中查找。如果將成績匯總到一張表中,按照之前的做法,手工錄入等級考試成績,就要分別對著成績表和匯總表中班級、學生、姓名、學號等信息,一個一個學生分別復制粘貼,這種手工錄入成績的操作費時耗力、效率低下。

VBA是一種宏語言,在office內Word、Excel、PPT等都內置了VBA。它能完成許多重復繁瑣的操作,使工作處理變得很高效。

本系統(tǒng)是利用Excel VBA自動功能使其通過自動識別記錄的唯一關鍵字“學號”,從而迅速自動匯總本院系各班級學生的等級考試成績,并利用其Excel計算功能,自動算出各班級的各類等級考試的過關率。

1 系統(tǒng)基本結構設計

本系統(tǒng)是以Excel工作薄作為系統(tǒng)平臺,首先創(chuàng)建表名為“數(shù)據(jù)表”和“學生統(tǒng)計表”的兩個工作表,“數(shù)據(jù)表”是作為等級考試成績數(shù)據(jù)導入表使用,學校下發(fā)的各類等級考試的成績表不能直接導入,要將這些成績表備份后刪除無關列,只保留“學號”“姓名”“分數(shù)”這三列數(shù)據(jù),然后將等級考試成績表三列數(shù)據(jù)復制到此表中對應的三列中。在數(shù)據(jù)列旁邊建立表單控件中6個按鈕控件。分別將這6個按鈕進行命名為5個等級考試名稱和“數(shù)據(jù)清空”。前5個按鈕作為匯總相應的等級成績用，第6個按鈕是為方便一鍵清空“數(shù)據(jù)表”中之前復制的學生成績。點擊前5個按鈕中的其中一個,都會自動識別“數(shù)據(jù)表”中學生的學號并將這個學號所對應的成績自動復制到 “學生統(tǒng)計表”相應的數(shù)據(jù)列。[1]

“學生統(tǒng)計表”是作為院系保存所有學生各類等級考試成績記載表,分別設有“班級”“學號”“姓名”“計算機成績”“英語三級A”“英語三級B”“英語四級”“英語六級”這8列,其中班級、學號、姓名這三列信息可以從學校學籍管理系統(tǒng)導出本院系的所有學生數(shù)據(jù)，再復制到這三列中。在此表最上面K和L列處插入一個按鈕控件,命名為“統(tǒng)計過關率”，如圖1所示。

圖1 學生統(tǒng)計表界面

2 考試成績匯總功能的代碼設計

導入和匯總各類等級考試的成績是在“數(shù)據(jù)表”的表中點擊相應等級考試項目的按鈕來實現(xiàn),以學生學號作為唯一關鍵字,通過代碼逐一提取 “數(shù)據(jù)表”中學生學號并和“學生統(tǒng)計表”中的學生學號一一比對,如果學號相同,則將“數(shù)據(jù)表”相對應的學生成績復制到同一學號學生相應的考試科目的單元格中。以計算機等級考試成績?yōu)槔?其代碼如下：

Private Sub CommandButton1-Click()

Dim xuehao As Variant: Dim tj As Range: Dim R As Range: Dim fen As Variant

h=Application.WorksheetFunction.CountA(Sheets(“數(shù)據(jù)表”).Range(“B2:B65536”))’’計算“數(shù)據(jù)表”學生人數(shù)

For i=1 To h

xuehao=Sheets(“數(shù)據(jù)表”).Cells(i+1, 2).Value

fen=Sheets(“數(shù)據(jù)表”).Cells(i+1, 2).Offset(0, 2).Value’ “數(shù)據(jù)表”學生和學號相對應的成績賦予給變量

With Sheets(“學生統(tǒng)計表”).Range(“B2:B65536”)

Set tj=.Find(What:=xuehao,LookIn:=xlValues,LookAt:=xlWhole,SearchOrder:=xlByRows,SearchDirection:=xlNext)[2]‘“學生統(tǒng)計表”中利用find查找功能和“統(tǒng)計表”中學號變量一一對比。

If Not tj Is Nothing Then

Application.Goto tj, True ‘如果兩個表的學號對比一致tj.Offset(0, 2).Value=fen ‘將“數(shù)據(jù)表”相應學號學生的分數(shù)付給“學生統(tǒng)計表”中對應的學生的單元格中

Else

MsgBox“沒有找到”+“學號為”+xuehao+“學生”

End If

End With

Next

End Sub

其他4個成績統(tǒng)計的按鈕的代碼也差別不大。通過此代碼運行只需2～3秒鐘,即可完成一個考試成績匯總工作。

3 成績統(tǒng)計功能的代碼設計

在“學生統(tǒng)計表”中要統(tǒng)計一個班級或多個班級各類等級考試的過關率,點擊“統(tǒng)計過關率”按鈕,將會彈出“統(tǒng)計過關率”窗體,如圖2所示。

圖2 各類等級考試過關率統(tǒng)計表界面

如果要統(tǒng)計某一個班級或幾個班級的某一等級考試科目過關率,首先要在窗體中左上角點擊“年級”旁邊旋轉按鈕控件中上下調節(jié)的按鍵, 在其旁邊文本框中就會顯示入學年份,通過入學年份的變化,在下面的 “班級列表” 列表框中將會出現(xiàn)相應年份所有班級列表。然后選中列表框相應的一個或多個班級,也可以通過列表框上方的“全選”復選框按鈕全部選中列表框中顯示的全部班級。再點擊“選擇”按鈕,所選班級將出現(xiàn)在右邊的“已選班級”列表框中,再選中后點擊相應的某個等級考試項目過關率的按鈕,就可以在最右邊的標簽中顯示所選中的班級的各個等級考試過關率。幾個主要功能代碼的編寫如下。

3.1 入學年級調整的代碼編寫

本代碼為旋轉按鈕可對入學年級進行調整,在列表框中顯示相應的班級。

Private Sub SpinButton1-Change()

On Error Resume Next

Dim nj As String: Dim Col As New Collection: Dim rng As Range, Arr

Dim i As Integer

Me.TextBox1=Format(Me.SpinButton1, “00”)

nj=Mid(TextBox1.Text, 3, 2)‘將

On Error Resume Next

For Each rng In Sheets(“學生統(tǒng)計表”).Range(“A2:A”&a65536].End(xlUp).Row)

If Mid(rng, 3, 2)=nj Then

If Trim(rng)<> “”Then

Col.Add rng, key:=CStr(rng)

End If

Else

ListBox1.Clear

End If

Next

ReDim Arr(1 To Col.Count)

For i=1 To Col.Count

Arr(i)=Col(i)

Next

Me.ListBox1.List=Arr

End Sub

3.2 過關率統(tǒng)計的代碼編寫

過關率代碼是通過統(tǒng)計相應班級過關人數(shù)和班級總人數(shù)之比得出的,以江蘇省英語三級A考級考試為例,其代碼如下。

Private Sub CmBton3A-Click()

On Error Resume Next

Dim banji As String: Dim Alie As Range: Dim Zsu As Integer: Dim Msu As Integer

Zsu=0: Msu=0: i=0

For x=0 To ListBox1.ListCount-1

If ListBox1.Selected(x)=True Then ‘如果列表框中班級被選中

banji=ListBox1.List(x)‘將列表中選中的班級名稱付給banji變量

For Each Alie In Sheets(“學生統(tǒng)計表”).Range(“A2:A65536”)‘遍列“學生統(tǒng)計表”A列A2到最后一個單元格

If Alie.Value=banji Then ‘如果班級相同

Zsu=Zsu+1 ‘計算班級總人數(shù)

If Alie.Offset(0, 4).Value=“” Or Alie.Offset(0, 4).Value < 60 Then[3]

Msu=Msu+1 ‘ 統(tǒng)計未參加考試和考試不及格的人數(shù)

End If

End If

Next

End If

Next

Label4.Caption=CStr(Round((Zsu-Msu)/Zsu, 2)* 100)+“%” ‘計算過關率。

End Sub

4 結語

通過本系統(tǒng)的在日常管理工作中的應用,使得分散在不同時期的各類考級考試Excel成績表能迅速進行匯總,并且也使統(tǒng)計各班等級考試過關率變得十分方便，而院系教學管理人員工作量得以減輕，工作效率得以提高。