運用Excel VBA實現(xiàn)高校新生分班

董建文

文章編號： 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：

作者簡介：

收稿日期： 引言

高校的班級是大學生的"第一社會、第二家庭、第三課堂"，班級的"人為環(huán)境"會在學生的成長道路上留下痕跡，班級的風氣和氛圍，直接或間接影響學生人生觀、價值觀的形成，影響學生學習、生活習慣的養(yǎng)成和社交能力的培養(yǎng)。因此科學、合理、人性化地對學生進行班級編排，是高校學生管理工作中非常重要的一環(huán)，也是老師、家長、學生普遍關心的問題。但是學生分班問題影響因素較多、綜合情況復雜，把各個班級分得合理均衡確非易事＼[1＼]。

迎接新生之前，每所高校都面臨著一項重要的工作--將錄取的新生分班。分班的要求：保證同一專業(yè)中多個平行班班級之間人數、成績、性別、生源地的平衡性。運用Excel進行篩選、排序的人工操作雖然能夠兼顧每個班級之間的均衡，但確實是一項高強度的繁瑣工作。面對成千上萬的新生數據，工作人員必須花費大量時間，不僅費時費力，效率低下，而且準確率低。VBA（Visual Basic for Application）是Visual Basic的一種宏語言，運用Excel VBA解決高校新生分班問題能夠發(fā)揮自動化處理的優(yōu)勢，提高分班的效率和準確性。

1需求描述

為了便于開展學生管理工作，要求各專業(yè)平行班級的人數基本相同、男女比例基本一致、同一生源地比例基本一致，學生成績分布相似，班級內不能出現(xiàn)相同姓名學生，最終學生名單按照姓名音序排列。

2過程建模

分班過程以新生數據和班級數作為輸入數據，其中招生處提供的錄取新生信息包括新生的姓名、性別、身份證號碼、二級學院、專業(yè)名稱、生源地和成績數據，班級數指各專業(yè)確定的班級數量。通過一系列的加工處理生成各班級學生名單，完成分班任務，圖1為具體數據流程圖。

分班的主要步驟如下：

（1）按照專業(yè)名稱，把生源Excel表劃分成各個專業(yè)表，統(tǒng)計各專業(yè)學生數，并將每個專業(yè)學生信息表保存為一個工作簿文件。

（2）把各專業(yè)學生信息表按照性別劃分成男、女生表。

（3）將每張男、女生表按照生源地和成績降序排列。由于全國各地高考總分不同，按照生源地和成績降序2個關鍵字進行排序，使同省份學生成績由高到低排列，保證每個平行班成績均衡，學生的學習能力基本一致。

（4）對排好序的學生表增加序號，并以Z形方式根據序號給每位學生分配班級信息。第一輪按照1班到n班順序將學生分配到各班，第二輪從n班到1班順序將學生分配到各班[2]。

（5）將男、女生表按照班級信息，劃分到各個班級，檢查是否存在同名學生，按照姓名升序排序，并統(tǒng)計各班級學生數，以"專業(yè)名稱+n班+人數"命名每張班級表。

3VBA實現(xiàn)

3.1分組

分班過程中需要對數據依次按照專業(yè)、性別、班級進行多次分組，即按照分組依據（關鍵字）將原表中數據分成多個表后保存工作簿，這里以專業(yè)分組為例進行介紹，流程圖見圖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排序與添加序號

不同專業(yè)班級數量不同，以分4個班級為例進行說明，創(chuàng)建文件夾"分4個班專業(yè)表"，將這些專業(yè)按性別分組后生成的Excel文件放入該文件夾。通過循環(huán)自動將該文件夾下文件依次打開，按照生源地升序和成績降序進行排序，再利用循環(huán)為每一行添加順序號，順序號的值為行號減去1。核心代碼如下。

3.3分班級號

以分4個班級為例進行說明，按照Z形方式，順序號為1-4的學生班級號是1、2、3、4，順序號為5-8的學生班級號是4、3、2、1，依次類推，結構圖如圖3所示。

所有的順序號分成2大類，每一類又以sxh Mod 4作為條件進行多分支條件判斷分配具體的班級號，代碼如下。

3.4同名檢查

運用循環(huán)對所有班級表中每個學生姓名與其后面的姓名進行比對， VBA系統(tǒng)方法 Find能夠快速查找一個姓名是否與比對的姓名區(qū)域內相同[3]。如果出現(xiàn)同名同姓，對話框提示同名所在的單元格位置，便于查看和調整個別同名學生，流程圖見圖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個小時，極大減輕工作量，并為編制學號、分配宿舍等迎新工作節(jié)約寶貴時間，也給高校學生管理工作打好基礎。

參考文獻

[1] 馬燕，王文發(fā)，李宏達. 基于矩陣存儲的學生分班問題的回溯算法[J]. 計算機工程與設計，2009，30（12）：3022-3025.

[2] 張德傳，周才秋. 學生分班軟件DIY[J]. 中國西部科技，2005（19）：60-62.

[3] 黃朝陽，李懿. Excel 2010VBA入門與提高[M]. 電子工業(yè)出版社，2014：96-97.