基于Excel VBA的成绩管理系统的探索与实现

２０２０年１１月２５日

第４卷第２２期

DOI:10.19850/.2096-4706.2020.22.003

现代信息科技

Modern Information Technology

Nov.2020

Vol.4No.22

基于Ｅｘｃｅｌ ＶＢＡ的成绩管理系统的探索与实现

杨发友，曹瀚天，黄恩相，张光福，铁卫华

（，）

云南水利水电职业学院云南 昆明 650499

摘 要文章以云南水利水电职业学院的成绩管理为研究对象以Excel 2010为平台采用VBA编程设计了一款能够

：，，，

自动汇总班级成绩并可以统计出补考学生名单的成绩管理系统利用Excel VBA实现学生成绩管理的自动化处理提高工作效

。，

率和数据处理能力加强数据处理结果的准确性实现学生成绩信息管理工作流程的系统化规范化和自动化降低工作量提

，，、，，

高工作效率

。

关键词VBA自动生成排版成绩管理

：；；；

中图分类号TP311.52 文献标识码A 文章编号2096-4706202022-0011-05

：：：（）

Ｅｘｐｌｏｒａｔｉｏｎ ａｎｄ Ｉｍｐｌｅｍｅｎｔａｔｉｏｎ ｏｆ Ａｃｈｉｅｖｅｍｅｎｔ Ｍａｎａｇｅｍｅｎｔ Ｓｙｓｔｅｍ

Ｂａｓｅｄ ｏｎ Ｅｘｃｅｌ ＶＢＡ

YANG Fayou，CAO Hantian，HUANG Enxiang，ZHANG Guangfu，TIE Weihua

（Yunnan Water Resources and Hydropower Vocational College，Kunming 650499，China）

the rearch object，us Excel 2010 as the platform，and us 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

Abstract：

The article takes the achievement management of Yunnan Water Resources and Hydropower Vocational College as

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；typetting；achievement management

0 引 言

经过多年的教育信息化发展以互联网为基础的成绩管

，

理系统在各级各类学校中得到了普及但经过调查仍然有

。，

很多学校还没有专门的成绩管理系统学校对成绩的管理还

，

停留在使用Excel进行管理

。

Excel虽然本身具有很强的数据处理能力但是存在以

，

下两个问题一是由于Excel的网络功能有限对数据录

：，

入的工作很难实现多人在线收集二是虽然教师们都熟悉

；

Excel但是该软件的强大的数据处理功能要想发挥出来

，，

需要掌握复杂的公式或者VBA编程才能实现这一点却少

，

有人能够精通

。

云南水利水电职业学院目前有5个二级学院将近5 000

，

名在校学生开设26个专业每学期开设约200门课程

，，，

包含理论课和实践课由于学院有在线成绩管理系统但有

。，

部分教师暂不能接受新系统使用操作特别年长的老教师习

，

惯了使用Excel统计各科成绩后上报教务处再由教务处按

，

照要求录入系统生成班级成绩汇总表进行排名和补考统

，，

计每次考试结束后教务处的工作量非常大不得不抽调

。，，

收稿日期2020-09-28

：

基金项目云南省教育厅科学研究基金项目

：

（）

2020J1176

其他工作人员进行支援这样做不仅工作效率低而且学

。，，

生成绩录入错误的风险非常大

。

解决这个问题最好的办法是升级优化或重新采购一套操

作更简洁的在线成绩管理系统但是由于学校经费有限近

，，

几年都没有这方面的资金预算

。

鉴于以上原因本文从云南水利水电职业学院成绩管理

，

需求出发通过分析学校需求从Excel本身的功能开发考

，，

虑用VBA开发了一套低成本的解决方案

，。

1 研究的意义

我国的教育信息化经过多年的发展已经取得了长足的进

，

步市场上不乏优秀的成绩管理系统但是这些系统要么就是

。，

商业化严重虽然好用后台服务也能及时跟进但是价格

，，，

不菲要么就是地方教育行政管理部门推广的系统具有一

；，

定的优势但是售后服务跟不上有的学校自己有能力开发

，。

各种子系统但是也存在各子系统之间数据不统一等问题

，。

Microsoft Office是广大学校教师最熟悉也是使用最多

的办公软件Excel软件是有强大的数据处理能力的表格系

，

统可以存储各种类型的数据Visual Basic宏语言Visual

，。（

Basic for ApplicationVBA是Office内嵌的一种完全面向

，）

对象的编程语言Excel本身虽然具有非常强大的数据处理

。

能力但是往往需要大量复杂的公式或者用VBA进行二次

，

开发才能使Excel的功能得以充分发挥运用VBA开发

，。

2020.11

11

第22期

成绩管理系统Excel软件既是应用程序又是数据库去避

，，

免了应用程序和数据库软件频繁交换数据的问题

。

学生成绩是学生评先评优的重要依据同时也是学生是

，

否需要补考能否达到毕业条件的重要考核指标考试成绩

、。

管理是学校教学管理最重要的环节之一成绩能否及时收

，，

集固定是否能准确无误的进行统计汇总关系学校管理的

、，

严肃性严谨性利用VBA技术只需把Excel成绩单模

、。，

板进行格式统一这样就能使数据标准化再利用VBA编

，，

程就能轻松对成绩进行收集管理

，、。

2 所用VBA关键技术的介绍

本系统直接用Excel+VBA进行开发VBA是应用程序

。

开发语言VBVisual Basic的子集一般是嵌入Excel等

（），

已有应用程序中对其进行二次开发提高自动化功能VBA

，。

编程可以自定义Excel的工具栏菜单和界面对数据进行

、，

复杂的操作和分析使Excel突破自身局限成为开发平台

，，，

从而实现基于VBA的成绩管理系统的各项功能

。

使用VBA开发有以下几个好处

：

（），

1VBA设计的窗体虽然不能脱离Excel独立运行

但可以让Excel在后台工作并利用Windows API可以

，，

设计浮动在桌面上的窗体让使用者感觉不出是Excel中

，

的窗体

；

（），

2使用Excel软件中的VBA开发系统主要是要充

分利用Excel软件自身的优势Excel软件还有很多内置的

，

函数可以调用在VBA中使用非常方便

，；

（）（），

3VBEVBA的编辑器提供了模块功能可以根

据功能需要编写模块代码实现模块化编程功能也非常利

，，

于系统功能的扩展升级

；

（）

4用VBA读取Excel文件中的数据比读取数据库的

数据更方便而且程序的设计更简单

，。

因此本系统选用Excel+VBA作为平台和工具是十分

，

便捷和高效的

。

3 系统的设计分析与实现

本系统分别由成绩报告单成绩收集汇总表补考统计

、、

表成绩收集模块补考统计模块用户窗体6部分组成

、、、。

3.1 用户登录验证及添加用户窗体

运行软件后系统会自动进入此窗口如图1a所示

，，（），

选择对应的用户名有成绩管理员临时用户两种

“”“、”，

输入正确密码即可登录也可以点击新用户注册如

“”；“”

图1b所示添加用户名密码后点添加即

（），“”“”“”

完成新用户注册

。

（）

a用户登录验证窗口

12

2020.11

现代信息科技

（）

b新用户注册窗口

图1 用户登录验证及新用户注册窗口

此登录验证模块能很好解决管理员用户和普通用户的权

限问题能够有效追踪成绩出错环节从而进行责任划分

，，；

同时也能添加不同用户方便管理

，。

实现程序代码为

：

Private Sub cmbUrname_Change()

End Sub

Public Sub cmdExit_Click()

Unload Me

savechanges:=Fal

End Sub

Private Sub cmdNewyh_Click()

End Sub

Private Sub lblWelcome_Click()

End Sub

Private Sub UrForm_QueryClo(Cancel As Integer,

CloMode As Integer)

If CloMode = 0 Then

Cancel = True

End If

End Sub

Private Sub UrForm_Initialize()

N1 = (Sheet3.

Range("b:b"))

AA1 = "b2:b"& N1

Sheet3.Activate

rce = AA1

End Sub

Private Sub cmdLogin_Click()

Dim I As Integer,Urname As String,Password As String

N1 = (Sheet3.

Range("b:b"))

For I = 2 To N1

Urname =

Password =

If Urname = Sheets(3).Cells(I,2) Then

If Password = Sheets(3).Cells(I,3)

ThenMsgBox &",欢迎您进入系统!",

48,"提示"

Unload Me

杨发友，等：基于Excel VBA的成绩管理系统的探索与实现

第22期

ElMsgBox "请正确输入用户名和密码!",48,"提示" (X + 1,2) =

= "" (X + 1,3) =

= ""End Sub

us

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 = ("A1").

(X + 1,1) = (X,1).Value + 1

Public Sub cmdExit_Click()

Unload Me

savechanges:=True

End Sub

3.2 成绩汇总统计模块

当用户验证成功后在成绩收集汇总表中点击一

，“”“

键收成绩如图2所示在弹出的提示对话框内输入需要

”，，

汇总的班级名称如输入150175确定后系统即运

“”：“”，

行后台程序代码把指定班级文件夹中收集的各科成绩报告

，

单进行汇总并能自动排版和实现成绩汇总和排名等功能

，，

生成统一的汇总表可以直接打印和存档

。

图2 成绩汇总统计窗口

实现程序为

：

Sub 一键收集成绩并汇总()Cells(4,j)

Dim myPath$,myFile$,AK As Workbook,Tk As Workbook,(1).Range("f8").Copy (1).Cells(3,j)

I,j,n,m,X,Y,Num1,Num2,Num3,Wjs As Integer(1).Range("f9").Copy (1).Cells(2,j)

Updating = FalTk.Sheets(1).Cells(1,j) = AK.Sheets(1).[f6]

Sheet1.[A1].(1).[b1] = (1).[f4]

Dim className As String(1).[b2] = (1).[f3]

className = ox("请输入班级名称","For I = 2 To Num1

班级名称","",510,380)

If className = "" Then Exit Sub

myPath = &""& className &""(1).Range("b"& I).Copy (1).Cells(I +

Set Tk = ThisWorkbook

myFile = Dir(myPath &"*.xls")(1).Cells(I + 3,1) = (1).[f2]

Do While myFile <>""(1).Range("c"& I + 1).Copy (1).Cells(I + 3,j)

If myFile <> ThenNext

a = CreateObject("stemObject").GetFolder Workbooks(myFile).Clo Fal

(myPath).myFile = Dir

For j = 6 To 5 + a Next

Set AK = (myPath & myFile) End If

(1).Unprotect Password:="cxb"Loop

Num1 = (1).Range("A2")..(1).[A1] = "应考人数"

2,3)

2020.11

Count (1).Range("f7").Copy (1).

(1).Range("a"& I).Copy (1).Cells(I +

2,2)

13

第22期

现代信息科技

(1).[A2] = "专业"+COUNTIF(R["& -Num2 + 5 &"]C:R[-1]C,""缺考"")+COUNTIF

(1).[a3] = "单人单科成绩"

(1).[a4] = "班级"

(1).[e1] = "学期"

(1).[e2] = "考试类别"

(1).[e3] = "任课教师"

(1).[c1] = "日期"

(1).[d4] = "总分"

(1).[e4] = "名次"

(1).[d1] = "=today()"

Num2 = (1).Range("b5")..

Count

Num3 = (1).Range("f4").s.

Count

(1).Cells(5,4).Select

For n = 5 To Num2 - 1

For m = 6 To Num3

(1).Cells(n,4) = "=Sum(RC[2]:RC["& Num3 - 4

&"])"

Next

Next

(1).Cells(5,5).Select

For n = 5 To Num2 - 1

(1).Cells(n,5).Select

aR1C1 = "=RANK(RC[-1],R5C4:R"&

Num2 - 1 &"C4,0)"

Next

Num2 = (1).Range("b5")..

Count

(1).Cells(Num2,1) = "合计与不及格统计"

aR1C1 = "=SUM(R["& -Num2 + 5 &"]

C:R[-1]C)"

(1).Cells(Num2,5).Clear

(1).[b3].Select

aR1C1 = "=R["& Num2 - 3 &"]C[2]/

R[-2]C[0]/("& Num3 - 5 &")"

FormatLocal = "0.000_ "

For m = 6 To Num3

(1).Cells(Num2,6).SelectCells(Num2,m)

､

= _"=COUNTIF(R["& -Num2 + 5 &"]C:R[-1]C,""<60"")

(R["& -Num2 + 5 &"]C:R[-1]C,""作弊"")+COUNTIF(R["& -Num2

+ 5 &"]C:R[-1]C,""补缺"")"

Next

Num2 = (1).Range("b5")..

Count

Num3 = (1).Range("f4").s.

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). = 13551615

End If

End If

Next

Next

(1).Range("a1").

With Selection

. = xlThin

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

End With

With Sheet1.t

End With

Dim myPath2 As String

Dim FileName2 As StringmyPath2 = "D:学校成绩收集汇总"

FileName2 = eets(1).Range("a5").Value

eets(1).SaveAs Filename:=myPath2 &""&

FileName2 &"班成绩汇总"

Updating = True

MsgBox "汇总完成,请查看!",64,"提示"

End Sub

3.3 补考统计模块

当用户验证成功后在成绩收集汇总表中点击一

，“”“

键统计补考如图3所示系统即运行后台程序代码在

”，，，

汇总的成绩报告表中筛选出成绩小于60缺考作

，“”“”“

弊补缺缓考的人员及名单及需补考科目等相

”“”“”“”

关信息汇总到补考统计表中

“”。

图3 补考统计表窗口

14

2020.11

杨发友，等：基于Excel VBA的成绩管理系统的探索与实现

实现程序为

：

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].(1).ClearContents

Sheet2.[A2].Resize(UBound(BRR),8)=BRR

Sheet2.yle=xlNone

Sheet2.lCells(xlCellTypeConstants,3).

yle=xlContinuous

WithSheet2

.[a1:h1]=Array("班级","学号","姓名","补考科目","

成绩","任课教师","学期","考试类别")

.Columns("a:h").AutoFit

EndWith

EndSub

4 系统主要功能

4.1 成绩报告单规范化

通过设计统一的成绩报告单使过去老师们报告成绩格

，

式不统一的情况得以完全改善成绩报告单设置了工作表保

，

护任课教师不能随意更改学生等信息填入报告单的数据

，，

也只能按照设置统一格式填写这样就规范了成绩数据

，。

4.2 成绩自动收集汇总功能

考试结束后班主任向老师们收集各班的各科成绩后

，，

报给教务员教务员一键即可生成各班的成绩汇总表自动

，，

汇总自动排名一键打印

、、。

第22期

4.3 补考统计功能

过去补考统计也是一项复杂的工作使用该系统后教

，，

务员可一键统计出各班各同学的补考科目真正实现办公自

，

动化

。

5 结 论

研究环境以Excel 2010为平台利用VBA编程语言

，，

开发出成绩管理系统能适用于各中小型规模学校一方

，，

面能大大减轻教务处工作人员的工作量经验证过去需

。，

要10多位老师加班五六天的工作量使用该系统后只需

，，

要教务员1人加班三个小时左右即可完成全部成绩汇总和

补考统计工作此外由于VBA编程语言的普适性系统

。，，

的可移植性也非常高稍做修改就可改为其他类似的管理

，

系统

。

由于笔者的技术水平有限该系统基于Excel 2010采用

，

VBA编程开发没有考虑在线收集成绩的功能所以还需

，，

要人工点选收集汇总最新版本Office或WPS网络功能得

。

以增强后续可以在网络功能开发上进一步开发就可以设

，，

计出适应其他学校需要的成绩管理系统

。

参考文献

：

[1] 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男汉族云南宾川人高校讲师在职研究生

（—），，，，，，

研究方向电气工程及计算机

：。

2020.11

15