運用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.