EXCEL VBA在考場(chǎng)編排中的設(shè)計(jì)與實(shí)現(xiàn)

錢建明

摘 要：為保證考試的公平、公正及其嚴(yán)肅性，科學(xué)的考場(chǎng)座位編排是一個(gè)重要手段。文章介紹的是利用EXCEL VBA編寫考場(chǎng)編排軟件的設(shè)計(jì)和實(shí)現(xiàn)，利用此軟件，可輕松實(shí)現(xiàn)學(xué)?？荚囍蟹爆嵉目紙?chǎng)編排、座位表、桌貼、班級(jí)考生分布等的一次性生成及打印。經(jīng)實(shí)際應(yīng)用，操作簡(jiǎn)單，方便實(shí)用。

關(guān)鍵詞：隨機(jī)；試場(chǎng)編排；座位表；桌貼

中圖分類號(hào)：TP393 文獻(xiàn)標(biāo)志碼：B 文章編號(hào)：1673-8454（2014）14-0065-05

現(xiàn)今中學(xué)階段的學(xué)校教務(wù)工作中，考務(wù)工作占了很大的比重，而考場(chǎng)座位編排是考務(wù)信息化管理中一項(xiàng)十分重要的工作?？紕?wù)工作主要分為考前的考場(chǎng)編排和考后的成績(jī)統(tǒng)計(jì)與分析，這里專門就考前的考場(chǎng)編排展開分析和討論。隨著學(xué)校辦學(xué)規(guī)模逐漸擴(kuò)大和學(xué)生人數(shù)的增加，考場(chǎng)安排的工作量不斷加大。因此，筆者決定編寫基于 Excel VBA的考場(chǎng)編排系統(tǒng)，希望通過(guò)本系統(tǒng)能實(shí)現(xiàn)對(duì)考場(chǎng)教室進(jìn)行合理有效的管理。

我們采用Excel作為系統(tǒng)編寫工具。Excel不僅具有強(qiáng)大的制表功能，同時(shí)還內(nèi)置了系統(tǒng)開發(fā)工具VBA。VBA是指Visual Basic for Application，它是在Office中廣泛應(yīng)用的宏語(yǔ)言，可以直接對(duì)Excel對(duì)象進(jìn)行編程，從而提高Excel的利用效率。使用它可以增強(qiáng)Excel的自動(dòng)化能力，使用戶更高效地完成特定任務(wù)。

軟件分為試場(chǎng)編排、座位表生成、桌貼的制作三大模塊。設(shè)計(jì)完成后，工作人員將基本數(shù)據(jù)，如考生名冊(cè)（含班級(jí)）、考場(chǎng)基本信息（考場(chǎng)名稱、每考場(chǎng)人數(shù)等）錄入到軟件中，軟件自動(dòng)對(duì)考生進(jìn)行考場(chǎng)隨機(jī)編排，編排完成后，要使學(xué)生能基本均勻地分布在各考場(chǎng)，同時(shí)，實(shí)現(xiàn)同考場(chǎng)中前后座位不同班，各考場(chǎng)人數(shù)可以不同，每列人數(shù)可以不同。另外，根據(jù)實(shí)際生成座位表、各班考生座位分布表、考生桌貼等。

一、考場(chǎng)編排

在中考或高考中，考場(chǎng)人數(shù)一般是30人，但基于學(xué)校實(shí)際在實(shí)際操作中很難做到，一般會(huì)出現(xiàn)各考場(chǎng)人數(shù)各異。有鑒于此，考場(chǎng)編排系統(tǒng)需具備很大的靈活性，因此軟件必須取得以下信息：學(xué)生名冊(cè)，考場(chǎng)名稱，各考場(chǎng)人數(shù)，各考場(chǎng)每列人數(shù)。

在編排試場(chǎng)時(shí)，要盡量保證學(xué)生座位的隨機(jī)性。對(duì)此每個(gè)學(xué)校都有自己的做法，有的按前一次考試成績(jī)進(jìn)行排座位，有的按姓氏筆畫排座位，有的采用Excel隨機(jī)函數(shù)來(lái)排座位。對(duì)于這些排座位的方式，雖在一定程度上實(shí)現(xiàn)了隨機(jī)性，但都存在前后同班情況的出現(xiàn)。網(wǎng)上也有采用VBA編寫的考場(chǎng)編排軟件，但班級(jí)分布上還是存在多種問(wèn)題，如5班考生大量出現(xiàn)在某個(gè)考場(chǎng)，但隔壁考場(chǎng)卻沒(méi)有一個(gè)5班考生，另外也出現(xiàn)了類似班級(jí)分布是固定的，如座位上考生班級(jí)按1班、2班、3班……順序排列，表面上實(shí)現(xiàn)了前后無(wú)同班，但容易被閱卷老師發(fā)現(xiàn)規(guī)律而引起其他的不公。在這種情況下，如果采用簡(jiǎn)單的方式編排座位，勢(shì)必造成大量的前述問(wèn)題。

為保證考生既要隨機(jī)分布，又要均勻分布，因此，在設(shè)計(jì)上，要多次用到隨機(jī)編排。首先是班級(jí)內(nèi)部考生的隨機(jī)編排，由于得到的原始數(shù)據(jù)并非按班排列，所以軟件首先對(duì)全年級(jí)數(shù)據(jù)進(jìn)行一次按班排序，然后在第五列對(duì)學(xué)生生成一次隨機(jī)數(shù)，再以班為單位將這些隨機(jī)數(shù)進(jìn)行排序，這樣進(jìn)行了考生第一次隨機(jī)，實(shí)現(xiàn)班級(jí)內(nèi)學(xué)生順序的隨機(jī)性。（注：在最終座位表排定前，考號(hào)列（D列）、考場(chǎng)列（E列）均作為輔助列，考場(chǎng)號(hào)首先都是用數(shù)字編號(hào)標(biāo)示。）

……

banji = Cells（30， 22） //班級(jí)數(shù)

Cells（i + 1， 5） = Rnd

For i = 1 To banji

Range（"A" & renshu1 & "：e" & renshu2）.Sort Key1：=Range（"e" & renshu1）， Order1：=xlAscending

Next

……

注：由于Value屬性是Cells集合的默認(rèn)屬性，所以省略不寫，下同。

系統(tǒng)自動(dòng)獲取班級(jí)數(shù)及考場(chǎng)數(shù)后，計(jì)算各班在各考場(chǎng)可以設(shè)置的最多人數(shù)，如14個(gè)班，某考場(chǎng)人數(shù)是34人，則每班提供給該考場(chǎng)的人數(shù)應(yīng)該是int（34/14），但這樣會(huì)出現(xiàn)有的班人數(shù)還不足以按要求分布到各考場(chǎng)，為解決這個(gè)問(wèn)題，程序會(huì)對(duì)各考場(chǎng)對(duì)要求每班提供的人數(shù)進(jìn)行累加，并與班級(jí)人數(shù)進(jìn)行比對(duì)，當(dāng)班級(jí)人數(shù)不足以滿足要求時(shí)，則順減1人，直至滿足提供給各試場(chǎng)人數(shù)之和小于等于實(shí)際人數(shù)為止，比如某班人數(shù)27，按前面試場(chǎng)要求，應(yīng)分配給各試場(chǎng)2人，總計(jì)28人，但該班不足以提供這么多考生，則分配到各試場(chǎng)的人數(shù)都減1人，余下考生作二次分配。

……

For j = 1 To Cells（30， 7） //試場(chǎng)數(shù)

For i = 1 To Cells（30， 22） //班級(jí)數(shù)

fenpei = Int（Cells（j + 1， 8） / Cells（30， 22））

If fenpei = 0 Then

arr（j， i） = 0

Else

If Cells（i + 1， 23） / fenpei >= Cells（30， 7） Then

arr（j， i） = fenpei

Else

Do While Cells（i + 1， 23） / fenpei < Cells（30， 7）

fenpei = fenpei - 1

arr（j， i） = fenpei

If fenpei = 0 Then

arr（j， i） = 0

Exit Do

End If

Loop

End If

End If

Next

Next

……

獲得各班在各試場(chǎng)的人數(shù)后，對(duì)各班每考生進(jìn)行編號(hào)，即考場(chǎng)號(hào)（數(shù)字表示）。如按考場(chǎng)人數(shù)34計(jì)，int（34/14）=2，即則從上往下對(duì)學(xué)生編號(hào)，多余的都編上100，運(yùn)行結(jié)果如圖1。

接下來(lái)按初排的考場(chǎng)進(jìn)行排序，這樣每班余下人數(shù)都匯集到一起，初排完成。程序接著對(duì)剩余部分考生進(jìn)行考場(chǎng)分配，采用循環(huán)語(yǔ)句，分配到每考場(chǎng)，如果該考場(chǎng)人數(shù)已滿，則分給下一考場(chǎng)。這樣使得剩余考生還是能盡可能的均勻分布到各考場(chǎng)。

……

k = Application.WorksheetFunction.CountIf（Columns（5）， 100）

k = Cells（30， 23） + 2 - k

Do While l = last

If j Mod m <> 0 Then

j = j Mod m

Else

j = m

End If

tongji = Application.WorksheetFunction.CountIf（Columns（5）， j）

If tongji < Cells（1 + j， 8） Then

Cells（k + i， 5） = j

i = i + 1

j = j + 1

l = Cells（n， 5）

Else

j = j + 1

l = Cells（n， 5）

End If

Loop

……

運(yùn)行結(jié)果如圖2。

完成后，各班考生基本均勻分布在各考場(chǎng)，下面要對(duì)各考場(chǎng)的考生進(jìn)行既隨機(jī)又均勻的分配。首先按考場(chǎng)和班級(jí)進(jìn)行多條件排序，完成后，每考場(chǎng)內(nèi)考生按班級(jí)順序排列，接下來(lái)分別對(duì)同班每生編上有序號(hào)碼。

……

Do While j <= p

For i = 1 To n

ar（i） = Application.WorksheetFunction.CountIf（Range（"d" & 2 + renshu1 & "：d" & 1 + renshu2 & ""）， i）

For m = 1 To ar（i）

Cells（1 + renshu1 + m + k， 6） = m

Next

k = k + ar（i）

Next

k = 0

renshu1 = renshu1 + Cells（j + 1， 8）

renshu2 = renshu1 + Cells（j + 2， 8）

j = j + 1

Loop

……

運(yùn)行結(jié)果如圖3。

在第六列，每個(gè)考生對(duì)應(yīng)產(chǎn)生一個(gè)隨機(jī)數(shù)，與前面第五列的數(shù)求和，并按考場(chǎng)號(hào)及所求和進(jìn)行多條件排序，如1、2、3班在某試場(chǎng)均有3名考生，則與隨機(jī)數(shù)字相加后，各班考生對(duì)應(yīng)的和數(shù)都是1.xxx，2.xxx，3.xxx樣式，對(duì)數(shù)據(jù)按考場(chǎng)及所求和進(jìn)行多條件排序后，初步實(shí)現(xiàn)前后無(wú)同班。但問(wèn)題也隨之而來(lái)，假設(shè)14個(gè)班，同考場(chǎng)每14個(gè)考生前后無(wú)同班，但第14和15考生可能會(huì)出現(xiàn)同班。于是再加一段代碼，防止同考場(chǎng)前后同班，方法是采用Do語(yǔ)句，如果遇到前后同班同考場(chǎng)，前一個(gè)考生往前挪2～12位，2～12隨機(jī)產(chǎn)生，這樣就有效地避免了前后同考場(chǎng)，也防止把該考生往前移動(dòng)后，與前一組最后考生同考場(chǎng)同班的情況，完成后，程序再?gòu)牡谝粋€(gè)考生開始檢查。

……

i = 1 //前后同班處理

Do While Not IsEmpty（Cells（i， 1））

If Cells（i， 1） = Cells（i + 1， 1） And Cells（i， 5） = Cells（i + 1， 5） Then

temp = Int（Rnd（） * （p - 2） + 2）

Range（"A" & i & "：c" & i & ""）.Cut

Range（"A" & i - temp）.Insert Shift：=xlDown

i = 0

End If

i = i + 1

Loop

……

對(duì)于一些相對(duì)重大的考試，一般均采用S型排列（也稱蛇形排列），對(duì)于這個(gè)要求，系統(tǒng)可以按要求完成排列，代碼如下（a，，b，c，d，e為設(shè)置的每列人數(shù)）：

……

m = 0

For i = 1 To IIf（k - m > a， a， k - m）

Cells（m + i + 1， 6） = Cells（m + i + 1， 4）

Next

m = m + a

n = IIf（k - m > b， b， k - m）

For i = 1 To n

Cells（m + i + 1， 6） = Cells（m + n + 2 - i， 4）

Next

m = m + b

……

If e <> 0 Then

For i = 1 To IIf（k - m > e， e， k - m）

Cells（m + i + 1， 6） = Cells（m + i + 1， 4）

Next

m = m + e

End If

……

End If

Next

……

最后對(duì)所有考生編上考場(chǎng)名稱，考生試場(chǎng)編排完成。對(duì)于編排的容錯(cuò)處理，這里不再累述。

二、座位表的生成

學(xué)生考場(chǎng)編排完成后，接著就是座位表的制作?？忌槐硪话銖堎N于考場(chǎng)門口，需要直觀地反應(yīng)考場(chǎng)布置情況。座位表制作總體上講是個(gè)對(duì)單元格的填充過(guò)程，因此代碼比較簡(jiǎn)單。但由于存在各考場(chǎng)人數(shù)不同，列數(shù)不同，以及同考場(chǎng)不同列人數(shù)不同的情況，因此在算法上，利用考場(chǎng)人數(shù)及列數(shù)，采用for語(yǔ)句進(jìn)行填充。每考場(chǎng)按4～6列設(shè)置，預(yù)先制作好模板，再將數(shù)據(jù)填入模板即可。

模板的選擇：

……

Select Case n

Case Is = 4

Sheets（"四列"）.Copy Before：=Sheets（Sheets.Count）

Sheets（"四列 （2）"）.Name = Sheets（"試場(chǎng)編排"）.Cells（j + 1， 7）

……

End Select

試場(chǎng)座位的填充：

Cells（1， 1） = Sheets（"試場(chǎng)編排"）.Cells（j + 1， 7） & " 試場(chǎng)座位示意圖"

For i = 1 To a

Cells（2 * i + 5， 1） = "考號(hào)"

Cells（2 * i + 5， 2） = Sheets（"試場(chǎng)編排"）.Cells（m + i + 1， 4）

Cells（2 * i + 6， 1） = Sheets（"試場(chǎng)編排"）.Cells（m + i + 1， 2）

Next

m = m + a

……

桌位表生成完畢后，由于是每個(gè)考場(chǎng)一張表，需要將這些表作為一個(gè)組才能一次性打印，否則操作比較麻煩，有違簡(jiǎn)潔高效的目的，因此，在座位表的打印和預(yù)覽上，對(duì)這些表利用數(shù)組進(jìn)行一次性選擇。

……

temp = Cells（30， 22）

For i = 1 To temp

ReDim Preserve ar（1 To i）

ar（i） = Cells（i + 1， 7） //座位表名稱賦值到數(shù)組

Next

Sheets（ar）.PrintOut

……

三、桌貼的制作

桌貼制作與座位表類似，首先制作一張桌貼的空白模板，再將數(shù)據(jù)填充上去即可。桌貼是按考場(chǎng)分發(fā)，不能將所有考生以連續(xù)分布的方式打印在紙上，因此這里采用for語(yǔ)句，將循環(huán)次數(shù)定義為試場(chǎng)數(shù)，每一試場(chǎng)考生都打印在一張A4紙上（40人以內(nèi)）。另外考慮到有的試場(chǎng)名稱較長(zhǎng)，系統(tǒng)對(duì)此作出判斷，如果試場(chǎng)名稱長(zhǎng)度數(shù)字超過(guò)9位，漢字超過(guò)6個(gè)，將縮小字體，以使得在桌貼上放下試場(chǎng)名稱：

……

Sheets（"桌貼"）.Cells（1 + 5 * （j - 1） + 50 * （i - 1）， 2 + 3 * n）. = Sheets（"考生名冊(cè)"）.Cells（m + 1， 5）

If Application.WorksheetFunction.IsNumber（Cells（1 + 5 * （j - 1） + 50 * （i - 1）， 2 + 3 * n）.） And Len（Cells（1 + 5 * （j - 1） + 50 * （i - 1）， 2 + 3 * n）.） > 11 Then

Cells（1 + 5 * （j - 1） + 50 * （i - 1）， 2 + 3 * n）.Font.Size = 9

ElseIf Not Application.WorksheetFunction.IsNumber

（Cells（1 + 5 * （j - 1） + 50 * （i - 1）， 2 + 3 * n）.） And Len

（Cells（1 + 5 * （j - 1） + 50 * （i - 1）， 2 + 3 * n）. > 6） Then

Cells（1 + 5 * （j - 1） + 50 * （i - 1）， 2 + 3 * n）.Font.Size = 9

End If

Sheets（"桌貼"）.Cells（2 + 5 * （j - 1） + 50 * （i - 1）， 2 + 3 * n）. = Sheets（"考生名冊(cè)"）.Cells（m + 1， 6）.

Sheets（"桌貼"）.Cells（3 + 5 * （j - 1） + 50 * （i - 1）， 2 + 3 * n）. = Sheets（"考生名冊(cè)"）.Cells（m + 1， 4）.

Sheets（"桌貼"）.Cells（4 + 5 * （j - 1） + 50 * （i - 1）， 2 + 3 * n）. = Sheets（"考生名冊(cè)"）.Cells（m + 1， 3）.

……

對(duì)于人數(shù)超過(guò)40的試場(chǎng)，在右側(cè)增加一頁(yè)：

……

If k > 40 Then

If Sheets（"桌貼"）.Range（"l" & 1 + 50 * （i - 1）） = "" Then

Sheets（"桌標(biāo)"）.Range（"A1：k50"）.Copy Sheets（"桌貼"）.Range（"l" & 1 + 50 * （i - 1））

End If

……

由于剛才右側(cè)增加一頁(yè)，會(huì)導(dǎo)致桌貼在打印時(shí)產(chǎn)生大量的空白頁(yè)，為解決此問(wèn)題，采用打印設(shè)置，使得只打印有數(shù)據(jù)的頁(yè)面，有數(shù)據(jù)的頁(yè)面的判斷：

……

For i = 1 To j Step 50

If Sheets（"桌貼"）.Range（"l" & i）.Value <> "" Then

k = k & "l" & i & "：v" & i + 49 & "，"

End If

Next

……

四、各班座位表的生成

各班座位表是考前下發(fā)給班主任，由班主任告知考生被安排在哪個(gè)試場(chǎng)。各班座位表的生成按每張A4紙49人設(shè)置（默認(rèn)班級(jí)人數(shù)不超過(guò)49，如果超過(guò)，可以和桌貼超40人方法一致）。接下來(lái)只需將各班學(xué)生填充即可，數(shù)據(jù)的填充，首先是對(duì)班級(jí)的識(shí)別，先從考生名冊(cè)表（考生名冊(cè)表在生成考場(chǎng)座位表后自動(dòng)生成，因?yàn)樵O(shè)計(jì)和算法都比較簡(jiǎn)單，不再累述）提取班級(jí)：

……

Do While Not IsEmpty（Sheets（"考生名冊(cè)"）.Cells（i， 1））

Sheets（"考生名冊(cè)"）.Cells（i， 7） = Right（Sheets（"考生名冊(cè)"）.Cells（i， 1）， 2）

i = i + 1

Loop

……

再按49循環(huán)，對(duì)單元格進(jìn)行填充，得到各班考生座位分布表：

……

Do While Not IsEmpty（Sheets（"考生名冊(cè)"）.Cells（j， 1））

If Sheets（"考生名冊(cè)"）.Cells（j， 7） = k Then

Sheets（"各班座位表"）.Cells（i + 1 + 49 * （k - 1）， 1） = Sheets（"考生名冊(cè)"）.Cells（j， 1）

Sheets（"各班座位表"）.Cells（i + 1 + 49 * （k - 1）， 2） = Sheets（"考生名冊(cè)"）.Cells（j， 3）

Sheets（"各班座位表"）.Cells（i + 1 + 49 * （k - 1）， 3） = Sheets（"考生名冊(cè)"）.Cells（j， 4）

Sheets（"各班座位表"）.Cells（i + 1 + 49 * （k - 1）， 4） = Sheets（"考生名冊(cè)"）.Cells（j， 5）

Sheets（"各班座位表"）.Cells（i + 1 + 49 * （k - 1）， 5） = Sheets（"考生名冊(cè)"）.Cells（j， 6）

i = i + 1

End If

j = j + 1

Loop

……

至此，VBA編寫的考場(chǎng)編排系統(tǒng)初步完成，加上稍許美化和容錯(cuò)處理，一個(gè)高效簡(jiǎn)潔的考場(chǎng)編排系統(tǒng)就完成了。

此軟件是在Excel 2003上開發(fā)的，結(jié)合VBA所編寫的程序，使用門檻低，操作方便，極大地減輕了考務(wù)工作者的負(fù)擔(dān)，提高了工作效率，取得了預(yù)期效果。

參考文獻(xiàn)：

[1]王曉東.計(jì)算機(jī)算法設(shè)計(jì)與分析（第3版）[M].北京：電子工業(yè)出版社，2007.

[2]楊章偉，張婉婉.Excel VBA語(yǔ)法辭典[M].北京：機(jī)械工業(yè)出版社，2010.（編輯：魯利瑞）