董建文

文章編號(hào)： 2095-2163（2018）03-0208-04中圖分類號(hào)： 文獻(xiàn)標(biāo)志碼： A

摘要： 關(guān)鍵詞： （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：

作者簡(jiǎn)介：

收稿日期： 引言

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

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

1需求描述

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

2過程建模

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

分班的主要步驟如下：

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

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

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

（4）對(duì)排好序的學(xué)生表增加序號(hào)，并以Z形方式根據(jù)序號(hào)給每位學(xué)生分配班級(jí)信息。第一輪按照1班到n班順序?qū)W(xué)生分配到各班，第二輪從n班到1班順序?qū)W(xué)生分配到各班[2]。

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

3VBA實(shí)現(xiàn)

3.1分組

分班過程中需要對(duì)數(shù)據(jù)依次按照專業(yè)、性別、班級(jí)進(jìn)行多次分組，即按照分組依據(jù)（關(guān)鍵字）將原表中數(shù)據(jù)分成多個(gè)表后保存工作簿，這里以專業(yè)分組為例進(jìn)行介紹，流程圖見圖2。核心代碼如下。

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

sDept = shtData.Cells（rowData， "G"）.Value '設(shè)置分組依據(jù)

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排序與添加序號(hào)

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

3.3分班級(jí)號(hào)

以分4個(gè)班級(jí)為例進(jìn)行說明，按照Z(yǔ)形方式，順序號(hào)為1-4的學(xué)生班級(jí)號(hào)是1、2、3、4，順序號(hào)為5-8的學(xué)生班級(jí)號(hào)是4、3、2、1，依次類推，結(jié)構(gòu)圖如圖3所示。

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

3.4同名檢查

運(yùn)用循環(huán)對(duì)所有班級(jí)表中每個(gè)學(xué)生姓名與其后面的姓名進(jìn)行比對(duì)， VBA系統(tǒng)方法 Find能夠快速查找一個(gè)姓名是否與比對(duì)的姓名區(qū)域內(nèi)相同[3]。如果出現(xiàn)同名同姓，對(duì)話框提示同名所在的單元格位置，便于查看和調(diào)整個(gè)別同名學(xué)生，流程圖見圖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 & "單元格數(shù)據(jù)重復(fù)"， vbYes， "同名同姓警告"）

End If

Next

4結(jié)束語(yǔ)

實(shí)踐證明利用Excel VBA方法能夠自動(dòng)、高效、準(zhǔn)確地完成新生分班工作，以2500名新生為例，人工分班需要20個(gè)小時(shí)，運(yùn)用Excel VBA完成分班僅僅需要1個(gè)小時(shí)，極大減輕工作量，并為編制學(xué)號(hào)、分配宿舍等迎新工作節(jié)約寶貴時(shí)間，也給高校學(xué)生管理工作打好基礎(chǔ)。

參考文獻(xiàn)

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

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

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