蔣 勇

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

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

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

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

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

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

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

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

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

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

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

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

If Not tj Is Nothing Then

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

Else

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

End If

End With

Next

End Sub

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

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

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

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

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

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

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

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)計(jì)表”).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)計(jì)的代碼編寫

過關(guān)率代碼是通過統(tǒng)計(jì)相應(yīng)班級(jí)過關(guān)人數(shù)和班級(jí)總?cè)藬?shù)之比得出的,以江蘇省英語三級(jí)A考級(jí)考試為例,其代碼如下。

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 ‘如果列表框中班級(jí)被選中

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

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

If Alie.Value=banji Then ‘如果班級(jí)相同

Zsu=Zsu+1 ‘計(jì)算班級(jí)總?cè)藬?shù)

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

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

End If

End If

Next

End If

Next

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

End Sub

4 結(jié)語

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