蔣 勇

(揚(yáng)州職業(yè)大學(xué), 江蘇 揚(yáng)州 225009)

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

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

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

1 系統(tǒng)基本結(jié)構(gòu)設(shè)計

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

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

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

2 考試成績匯總功能的代碼設(shè)計

導(dǎo)入和匯總各類等級考試的成績是在“數(shù)據(jù)表”的表中點(diǎn)擊相應(yīng)等級考試項(xiàng)目的按鈕來實(shí)現(xiàn),以學(xué)生學(xué)號作為唯一關(guān)鍵字,通過代碼逐一提取 “數(shù)據(jù)表”中學(xué)生學(xué)號并和“學(xué)生統(tǒng)計表”中的學(xué)生學(xué)號一一比對,如果學(xué)號相同,則將“數(shù)據(jù)表”相對應(yīng)的學(xué)生成績復(fù)制到同一學(xué)號學(xué)生相應(yīng)的考試科目的單元格中。以計算機(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ù)表”學(xué)生人數(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ù)表”學(xué)生和學(xué)號相對應(yīng)的成績賦予給變量

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

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

If Not tj Is Nothing Then

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

Else

MsgBox“沒有找到”+“學(xué)號為”+xuehao+“學(xué)生”

End If

End With

Next

End Sub

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

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

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

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

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

3.1 入學(xué)年級調(diào)整的代碼編寫

本代碼為旋轉(zhuǎn)按鈕可對入學(xué)年級進(jìn)行調(diào)整,在列表框中顯示相應(yīng)的班級。

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(“學(xué)生統(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 過關(guān)率統(tǒng)計的代碼編寫

過關(guān)率代碼是通過統(tǒng)計相應(yīng)班級過關(guān)人數(shù)和班級總?cè)藬?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(“學(xué)生統(tǒng)計表”).Range(“A2:A65536”)‘遍列“學(xué)生統(tǒng)計表”A列A2到最后一個單元格

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

Zsu=Zsu+1 ‘計算班級總?cè)藬?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)+“%” ‘計算過關(guān)率。

End Sub

4 結(jié)語

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