运用Excel VBA实现高校新生分班

董建文

文章编号： 2095-2163（2018）03-0208-04中图分类号： 文献标志码： A

摘要： 关键词： （Dean's Office， Nanjing Audit University Jinshen College， Nanjing Jiangsu 210023， China）

Abstract： This paper analyzes the needs of the class-division for freshmen in universities， makes process modeling， and explores how to apply Excel VBA to realize the class-division of University for Freshmen. The application result shows that use of Excel VBA has improved the efficiency and accuracy of the work greatly.

Key words：

作者简介：

收稿日期： 引言

高校的班级是大学生的"第一社会、第二家庭、第三课堂"，班级的"人为环境"会在学生的成长道路上留下痕迹，班级的风气和氛围，直接或间接影响学生人生观、价值观的形成，影响学生学习、生活习惯的养成和社交能力的培养。因此科学、合理、人性化地对学生进行班级编排，是高校学生管理工作中非常重要的一环，也是老师、家长、学生普遍关心的问题。但是学生分班问题影响因素较多、综合情况复杂，把各个班级分得合理均衡确非易事＼[1＼]。

迎接新生之前，每所高校都面临着一项重要的工作--将录取的新生分班。分班的要求：保证同一专业中多个平行班班级之间人数、成绩、性别、生源地的平衡性。运用Excel进行筛选、排序的人工操作虽然能够兼顾每个班级之间的均衡，但确实是一项高强度的繁琐工作。面对成千上万的新生数据，工作人员必须花费大量时间，不仅费时费力，效率低下，而且准确率低。VBA（Visual Basic for Application）是Visual Basic的一种宏语言，运用Excel VBA解决高校新生分班问题能够发挥自动化处理的优势，提高分班的效率和准确性。

1需求描述

为了便于开展学生管理工作，要求各专业平行班级的人数基本相同、男女比例基本一致、同一生源地比例基本一致，学生成绩分布相似，班级内不能出现相同姓名学生，最终学生名单按照姓名音序排列。

2过程建模

分班过程以新生数据和班级数作为输入数据，其中招生处提供的录取新生信息包括新生的姓名、性别、身份证号码、二级学院、专业名称、生源地和成绩数据，班级数指各专业确定的班级数量。通过一系列的加工处理生成各班级学生名单，完成分班任务，图1为具体数据流程图。

分班的主要步骤如下：

（1）按照专业名称，把生源Excel表划分成各个专业表，统计各专业学生数，并将每个专业学生信息表保存为一个工作簿文件。

（2）把各专业学生信息表按照性别划分成男、女生表。

（3）将每张男、女生表按照生源地和成绩降序排列。由于全国各地高考总分不同，按照生源地和成绩降序2个关键字进行排序，使同省份学生成绩由高到低排列，保证每个平行班成绩均衡，学生的学习能力基本一致。

（4）对排好序的学生表增加序号，并以Z形方式根据序号给每位学生分配班级信息。第一轮按照1班到n班顺序将学生分配到各班，第二轮从n班到1班顺序将学生分配到各班[2]。

（5）将男、女生表按照班级信息，划分到各个班级，检查是否存在同名学生，按照姓名升序排序，并统计各班级学生数，以"专业名称+n班+人数"命名每张班级表。

3VBA实现

3.1分组

分班过程中需要对数据依次按照专业、性别、班级进行多次分组，即按照分组依据（关键字）将原表中数据分成多个表后保存工作簿，这里以专业分组为例进行介绍，流程图见图2。核心代码如下。

For rowData = 2 To shtData.Range（"A1"）.CurrentRegion.Rows.Count

sDept = shtData.Cells（rowData， "G"）.Value '设置分组依据

bln = False

For Each shtNew In Worksheets

If shtNew.Name = sDept Then

bln = True

Exit For

End If

Next

If bln = False Then

Set shtNew = Worksheets.Add（after：=Worksheets（Worksheets.Count））

shtNew.Name = sDept

shtData.Range（"A1：J1"）.Copy shtNew.Range（"A1"）

End If

rowNew = shtNew.Range（"A1"）.CurrentRegion.Rows.Count + 1

Intersect（shtData.Rows（rowData）， shtData.Range（"A：J"））.Copy shtNew.Cells（rowNew， 1）

Next rowData

3.2排序與添加序号

不同专业班级数量不同，以分4个班级为例进行说明，创建文件夹"分4个班专业表"，将这些专业按性别分组后生成的Excel文件放入该文件夹。通过循环自动将该文件夹下文件依次打开，按照生源地升序和成绩降序进行排序，再利用循环为每一行添加顺序号，顺序号的值为行号减去1。核心代码如下。

3.3分班级号

以分4个班级为例进行说明，按照Z形方式，顺序号为1-4的学生班级号是1、2、3、4，顺序号为5-8的学生班级号是4、3、2、1，依次类推，结构图如图3所示。

所有的顺序号分成2大类，每一类又以sxh Mod 4作為条件进行多分支条件判断分配具体的班级号，代码如下。

3.4同名检查

运用循环对所有班级表中每个学生姓名与其后面的姓名进行比对， VBA系统方法 Find能够快速查找一个姓名是否与比对的姓名区域内相同[3]。如果出现同名同姓，对话框提示同名所在的单元格位置，便于查看和调整个别同名学生，流程图见图4。 具体代码如下：For rowData = 2 To zhs - 1

sKey = shtData.Cells（rowData， "B"）.Value

Set Rng = Range（Cells（rowData + 1， "B"）， Cells（zhs， "B"））.Find（sKey， LookAt：=xlWhole）

If Rng Is Nothing Then

Else

msgResult = MsgBox（shtData.Name & "的B" & rowData & "单元格数据重复"， vbYes， "同名同姓警告"）

End If

Next

4结束语

实践证明利用Excel VBA方法能够自动、高效、准确地完成新生分班工作，以2500名新生为例，人工分班需要20个小时，运用Excel VBA完成分班仅仅需要1个小时，极大减轻工作量，并为编制学号、分配宿舍等迎新工作节约宝贵时间，也给高校学生管理工作打好基础。

参考文献

[1] 马燕，王文发，李宏达. 基于矩阵存储的学生分班问题的回溯算法[J]. 计算机工程与设计，2009，30（12）：3022-3025.

[2] 张德传，周才秋. 学生分班软件DIY[J]. 中国西部科技，2005（19）：60-62.

[3] 黄朝阳，李懿. Excel 2010VBA入门与提高[M]. 电子工业出版社，2014：96-97.