基于Excel VBA的等级考试成绩管理系统设计

蒋 勇

(扬州职业大学, 江苏 扬州 225009)

高校学生对于各类等级考试,例如全国英语四六级考试、全国计算机等级考试等都非常重视,因为这些考试证书是学生毕业后就业的重要材料之一,另外班级各种等级考试过关率的数据也成为学校先进班级评选条件和院系年终总结材料。但是由于等级考试要求的特殊性,例如全国英语四六级考试,只有英语四级过关的学生才可以报考英语六级,有的学生英语四级过关后,也不一定参加英语六级的考试,而四级没有过关学生还需要继续报名英语四级考试,因此每次等级考试并不是全体学生都参加,所以教务处下发到各院系的等级考试成绩中学号都是不连续的,且各班学生等级考试成绩都是分散在各个时期等级考试的成绩表中,而要查询或汇总本院系学生各类等级考试的成绩十分麻烦,需要在不同表中查找。如果将成绩汇总到一张表中,按照之前的做法,手工录入等级考试成绩,就要分别对着成绩表和汇总表中班级、学生、姓名、学号等信息,一个一个学生分别复制粘贴,这种手工录入成绩的操作费时耗力、效率低下。

VBA是一种宏语言,在office内Word、Excel、PPT等都内置了VBA。它能完成许多重复繁琐的操作,使工作处理变得很高效。

本系统是利用Excel VBA自动功能使其通过自动识别记录的唯一关键字“学号”,从而迅速自动汇总本院系各班级学生的等级考试成绩,并利用其Excel计算功能,自动算出各班级的各类等级考试的过关率。

1 系统基本结构设计

本系统是以Excel工作薄作为系统平台,首先创建表名为“数据表”和“学生统计表”的两个工作表,“数据表”是作为等级考试成绩数据导入表使用,学校下发的各类等级考试的成绩表不能直接导入,要将这些成绩表备份后删除无关列,只保留“学号”“姓名”“分数”这三列数据,然后将等级考试成绩表三列数据复制到此表中对应的三列中。在数据列旁边建立表单控件中6个按钮控件。分别将这6个按钮进行命名为5个等级考试名称和“数据清空”。前5个按钮作为汇总相应的等级成绩用，第6个按钮是为方便一键清空“数据表”中之前复制的学生成绩。点击前5个按钮中的其中一个,都会自动识别“数据表”中学生的学号并将这个学号所对应的成绩自动复制到 “学生统计表”相应的数据列。[1]

“学生统计表”是作为院系保存所有学生各类等级考试成绩记载表,分别设有“班级”“学号”“姓名”“计算机成绩”“英语三级A”“英语三级B”“英语四级”“英语六级”这8列,其中班级、学号、姓名这三列信息可以从学校学籍管理系统导出本院系的所有学生数据，再复制到这三列中。在此表最上面K和L列处插入一个按钮控件,命名为“统计过关率”，如图1所示。

图1 学生统计表界面

2 考试成绩汇总功能的代码设计

导入和汇总各类等级考试的成绩是在“数据表”的表中点击相应等级考试项目的按钮来实现,以学生学号作为唯一关键字,通过代码逐一提取 “数据表”中学生学号并和“学生统计表”中的学生学号一一比对,如果学号相同,则将“数据表”相对应的学生成绩复制到同一学号学生相应的考试科目的单元格中。以计算机等级考试成绩为例,其代码如下：

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(“数据表”).Range(“B2:B65536”))’’计算“数据表”学生人数

For i=1 To h

xuehao=Sheets(“数据表”).Cells(i+1, 2).Value

fen=Sheets(“数据表”).Cells(i+1, 2).Offset(0, 2).Value’ “数据表”学生和学号相对应的成绩赋予给变量

With Sheets(“学生统计表”).Range(“B2:B65536”)

Set tj=.Find(What:=xuehao,LookIn:=xlValues,LookAt:=xlWhole,SearchOrder:=xlByRows,SearchDirection:=xlNext)[2]‘“学生统计表”中利用find查找功能和“统计表”中学号变量一一对比。

If Not tj Is Nothing Then

Application.Goto tj, True ‘如果两个表的学号对比一致tj.Offset(0, 2).Value=fen ‘将“数据表”相应学号学生的分数付给“学生统计表”中对应的学生的单元格中

Else

MsgBox“没有找到”+“学号为”+xuehao+“学生”

End If

End With

Next

End Sub

其他4个成绩统计的按钮的代码也差别不大。通过此代码运行只需2～3秒钟,即可完成一个考试成绩汇总工作。

3 成绩统计功能的代码设计

在“学生统计表”中要统计一个班级或多个班级各类等级考试的过关率,点击“统计过关率”按钮,将会弹出“统计过关率”窗体,如图2所示。

图2 各类等级考试过关率统计表界面

如果要统计某一个班级或几个班级的某一等级考试科目过关率,首先要在窗体中左上角点击“年级”旁边旋转按钮控件中上下调节的按键, 在其旁边文本框中就会显示入学年份,通过入学年份的变化,在下面的 “班级列表” 列表框中将会出现相应年份所有班级列表。然后选中列表框相应的一个或多个班级,也可以通过列表框上方的“全选”复选框按钮全部选中列表框中显示的全部班级。再点击“选择”按钮,所选班级将出现在右边的“已选班级”列表框中,再选中后点击相应的某个等级考试项目过关率的按钮,就可以在最右边的标签中显示所选中的班级的各个等级考试过关率。几个主要功能代码的编写如下。

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(“学生统计表”).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 过关率统计的代码编写

过关率代码是通过统计相应班级过关人数和班级总人数之比得出的,以江苏省英语三级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(“学生统计表”).Range(“A2:A65536”)‘遍列“学生统计表”A列A2到最后一个单元格

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

Zsu=Zsu+1 ‘计算班级总人数

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

Msu=Msu+1 ‘ 统计未参加考试和考试不及格的人数

End If

End If

Next

End If

Next

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

End Sub

4 结语

通过本系统的在日常管理工作中的应用,使得分散在不同时期的各类考级考试Excel成绩表能迅速进行汇总,并且也使统计各班等级考试过关率变得十分方便，而院系教学管理人员工作量得以减轻，工作效率得以提高。