基于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—），男，汉族，云南宾川人，高校讲师，在职研究生，研究方向：电气工程及计算机。