張久慶

（微山縣教育局，山東濟(jì)寧277600）

Excel實(shí)現(xiàn)考場(chǎng)編排的幾種方法

張久慶

（微山縣教育局，山東濟(jì)寧277600）

實(shí)現(xiàn)考務(wù)管理的信息化、現(xiàn)代化是當(dāng)今各類招生考試機(jī)構(gòu)及學(xué)校教務(wù)部門面臨的共同課題，文章在總結(jié)了幾種計(jì)算機(jī)編排考場(chǎng)算法的基礎(chǔ)上，充分利用Excel的易操作性及內(nèi)置的VBA編程特性，提供了三種利用Excel工作表數(shù)據(jù)進(jìn)行考場(chǎng)自動(dòng)編排的實(shí)現(xiàn)方法。

考場(chǎng)編排；排考場(chǎng)

利用計(jì)算機(jī)程序進(jìn)行考場(chǎng)編排，快捷、方便、高效、公平，是很多考務(wù)人員進(jìn)行考場(chǎng)編排首選的通行辦法。但由于絕大多數(shù)編排程序都是一些專用軟件，或者需依托數(shù)據(jù)庫(kù)管理系統(tǒng)平臺(tái)才能運(yùn)行，對(duì)考務(wù)人員的計(jì)算機(jī)應(yīng)用水平提出了更高的要求，同時(shí)也將一部分計(jì)算機(jī)基礎(chǔ)比較差的同志拒之門外。在對(duì)當(dāng)前比較流行的考場(chǎng)編排算法進(jìn)行分析研究的基礎(chǔ)上，考慮到Excel電子表格軟件應(yīng)用普及率較高，許多辦公人員都能熟練操作，筆者認(rèn)為運(yùn)用Excel內(nèi)置的VBA（Visual Basic For Application程序設(shè)計(jì)語(yǔ)言）進(jìn)行簡(jiǎn)單編程、實(shí)現(xiàn)考場(chǎng)編排不失為一種值得推薦的做法。

使用VBA操作Excel中的數(shù)據(jù)，可以將其工作簿中的多個(gè)工作表作為關(guān)系型數(shù)據(jù)庫(kù)中的數(shù)據(jù)表來操作，甚至不需要打開工作簿也可以讀取工作表中的數(shù)據(jù)。要實(shí)現(xiàn)上述操作，就要用到微軟提供的訪問數(shù)據(jù)庫(kù)的ADO（ActiveX）數(shù)據(jù)對(duì)象模型，透過OLE-DB編程接口對(duì)數(shù)據(jù)表進(jìn)行存取查詢等操作。要在VBA中使用ADO，首先需要在Excel的VBA編程環(huán)境中設(shè)置對(duì)ADO的引用，在VBA項(xiàng)目窗口中單擊工具菜單中的“引用”，在彈出的窗口中選擇Microsoft ActiveX Data Objects 2.8，點(diǎn)擊確定。如果本應(yīng)用程序可能要在低版本的Excel中運(yùn)行，則要考慮使用低版本的ADO library，以提高兼容性。

一、考場(chǎng)編排前期數(shù)據(jù)準(zhǔn)備工作

編程環(huán)境使用Excel2007，文件存貯格式采用2007啟用宏的工作薄文件（XLSM擴(kuò)展名）或者Excel97-2003（XLS擴(kuò)展名）工作薄文件。在此我們假設(shè)需要為某一高中招生考試編排考場(chǎng)，某考點(diǎn)共有N所學(xué)校的學(xué)生考試，每一個(gè)考場(chǎng)都標(biāo)準(zhǔn)化安排30名考生，要求每個(gè)考生的前后左右都不能是同一個(gè)學(xué)校的學(xué)生，需要編排考場(chǎng)的數(shù)據(jù)已存放在當(dāng)前工作簿的SHEET1工作表中，主要欄目包括考生姓名、畢業(yè)學(xué)校等信息，其中畢業(yè)學(xué)校是必須的欄目，因?yàn)榫幣趴紙?chǎng)時(shí)要依據(jù)畢業(yè)學(xué)校字段對(duì)考生進(jìn)行分組排序，在進(jìn)行編排考場(chǎng)前，需要處理的前期數(shù)據(jù)準(zhǔn)備工作主要有以下幾點(diǎn)：①在VBA項(xiàng)目窗口中插入一個(gè)模塊，并在此模塊中新建一個(gè)“編排考場(chǎng)”過程。②聲明并初始化相關(guān)變量。③由于Excel VBA的ADO對(duì)自身工作簿中的工作表進(jìn)行查詢時(shí)效率非常低，且存在無法關(guān)閉進(jìn)程的問題，因此需要將本工作簿中存放考生數(shù)據(jù)的SHEET1工作表復(fù)制另存為另一個(gè)XLS擴(kuò)展名工作薄文件（與Excel97-2003完全兼容的工作薄文件格式），下一步將從此工作薄而不是當(dāng)前工作薄文件查詢數(shù)據(jù)。④在當(dāng)前工作薄中新建一個(gè)考場(chǎng)編排工作表，以存放編場(chǎng)結(jié)果。⑤連接第三步另存的存貯原始考生數(shù)據(jù)的工作簿，統(tǒng)計(jì)各學(xué)校名稱及人數(shù)，并按學(xué)校人數(shù)多少排序?qū)⑵浯嫒胍粋€(gè)臨時(shí)數(shù)組。⑥使用同一連接查詢?nèi)靠忌涗洠磁R時(shí)數(shù)組中學(xué)校名稱及順序循環(huán)設(shè)置篩選條件，將所有考生數(shù)據(jù)依次復(fù)制到新建的“考場(chǎng)編排”工作表中，同時(shí)由程序自動(dòng)為此新工作表插入準(zhǔn)考證號(hào)、考場(chǎng)號(hào)、座號(hào)字段，為下一步編排考場(chǎng)做好數(shù)據(jù)準(zhǔn)備工作。

主要代碼如下所示：

Public Sub編排考場(chǎng)()

Dim cnn As New ADODB.Connection//聲明并引用一個(gè)ADO的Connection連接對(duì)象變量

Dim rs As New ADODB.Recordset//聲明并引用一個(gè)ADO的Recordset記錄集對(duì)象變量

Dim cnnStr As String//聲明連接字符串變量

Dim SQL As String//聲明查詢字符串變量

Dim pretext As String

pretext="1037"http://指定準(zhǔn)考證流水號(hào)前的預(yù)置代碼，可隨意設(shè)定，這里假定為“1037”

Dim myWbName As String//聲明Excel工作簿名稱、字符串變量

Dim ws As Worksheet//聲明Excel工作表對(duì)象變量

On Error Resume Next

Application.DisplayAlerts=False

Sheet1.Copy

ActiveWorkbook.SaveAs ThisWorkbook.Path&"臨時(shí)數(shù)據(jù).xls",FileFormat:=xlExcel8//將當(dāng)前工作薄的SHEET1工作表另存為當(dāng)前目錄下的臨時(shí)數(shù)據(jù).xls以備查詢，并在當(dāng)前工作簿中新建一個(gè)考場(chǎng)編排工作表，以保存篩選結(jié)果

ActiveWorkbook.Close

Worksheets("考場(chǎng)編排").Delete

Application.DisplayAlerts=True

On Error GoTo 0

With Worksheets

Set ws=.Add(after:=.Item(.Count))

End With

ws.Name="考場(chǎng)編排"

myWbName=ThisWorkbook.Path&"臨時(shí)數(shù)據(jù). xls"http://指定要連接查詢的工作簿名稱（帶完整路徑）

cnnStr="Provider=Microsoft.Jet.OLEDB.4.0;"_

//建立與指定工作簿的連接

&"Extended Properties=Excel 8.0;"_

&"Data Source="&myWbName

cnn.Open cnnStr

SQL="select畢業(yè)學(xué)校,count(*)as ren from [Sheet1$]group by畢業(yè)學(xué)校order by count(*)desc" //設(shè)置查詢篩選SQL語(yǔ)句，查詢各個(gè)畢業(yè)學(xué)校的人數(shù)

rs.Open SQL，cnn，adOpenKeyset，adLockOptimistic

//打開查詢的記錄集

Dim sl As Integer//聲明一整型變量用來存貯記錄集中的學(xué)校數(shù)量

sl=rs.RecordCount

Dim sz()As String//建立一個(gè)動(dòng)態(tài)二維字符串?dāng)?shù)組sz()，存放各學(xué)校的學(xué)校名稱及人數(shù)

ReDim sz(1 To sl,2)

rs.MoveFirst//將查詢結(jié)果集的數(shù)據(jù)復(fù)制到字符串?dāng)?shù)組中

For i=1 To sl Step 1

sz(i,1)=rs("畢業(yè)學(xué)校").Value

sz(i,2)=rs("ren").Value

rs.MoveNext

Next i

rs.Close//關(guān)閉記錄集并釋放記錄集對(duì)象變量

Set rs=Nothing

SQL="select*from[Sheet1$]order by畢業(yè)學(xué)校"

//設(shè)置查詢篩選SQL語(yǔ)句，查詢?nèi)靠忌鷶?shù)據(jù)

rs.Open SQL,cnn,adOpenKeyset,adLockOptimistic

//設(shè)置過濾條件，從記錄集中分別篩選出各學(xué)校的考生數(shù)據(jù)并按照學(xué)校人數(shù)從多到少的順序

//復(fù)制到新建的考場(chǎng)編排工作表中

For i=1 To sl Step 1

rs.Filter="畢業(yè)學(xué)校='"&sz(i,1)&"'"

ws.Activate

If i=1 Then//如果是第一個(gè)學(xué)校則在工作表首行添加標(biāo)題行

xb=2//xb變量用來存貯當(dāng)前記錄行號(hào)

For ii=1 To rs.Fields.Count

ws.Cells(1,ii)=rs.Fields(ii-1).Name

Next ii

Else

xb=xb+sz(i-1,2)

End If

ws.Range("a"&xb).CopyFromRecordset rs

Next

rs.Filter=adFilterNone//取消對(duì)記錄集的篩選條件

ws.Columns(1).Insert//給工作表增加準(zhǔn)考證號(hào)、考場(chǎng)號(hào)及座位號(hào)字段

ws.Columns(1).Insert

ws.Columns(1).Insert

ws.Cells(1,1)="準(zhǔn)考證號(hào)"

ws.Cells(1,2)="考場(chǎng)號(hào)"

ws.Cells(1,3)="座位號(hào)"

分段排序法排準(zhǔn)考證號(hào)代碼/環(huán)形抽取法排準(zhǔn)考證號(hào)代碼/混洗法排準(zhǔn)考證號(hào)代碼//三種準(zhǔn)考證號(hào)編排方法的實(shí)現(xiàn)代碼請(qǐng)參見考場(chǎng)編排部分

rs.Close//清除變量釋放內(nèi)存

cnn.Close

Set rs=Nothing

Set cnn=Nothing

Set ws=Nothing

End Sub//考場(chǎng)編排結(jié)束，將“考場(chǎng)編排”工作表按準(zhǔn)考證號(hào)重新排序即可

二、考場(chǎng)編排

計(jì)算機(jī)考場(chǎng)編排目前比較常見及通用的算法主要有分段排序法、環(huán)形抽取法、混洗法等?，F(xiàn)將Excel實(shí)現(xiàn)以上幾種算法的過程介紹如下。

1.分段排序法

在前期已將所有考生按學(xué)校名稱及人數(shù)由大到小的順序排列并存入“考場(chǎng)編排”數(shù)據(jù)表后，虛擬地將該表中所有考生分為若干段，這里我們假設(shè)為N段，從前往后循環(huán)地為每一段的每一名考生賦予唯一的準(zhǔn)考證序號(hào)，方法是用N乘以一個(gè)以1為單位不斷遞增的計(jì)數(shù)器變量，再加上本段所在的段序號(hào)（段序號(hào)起始值為1，同一段的段序號(hào)值相同）。不必理會(huì)每段具體有多少人，也不必理會(huì)計(jì)數(shù)器變量的大小，只要所生成的準(zhǔn)考證序號(hào)大于考生總?cè)藬?shù)，則本段循環(huán)結(jié)束，進(jìn)入下一段循環(huán)，計(jì)數(shù)器變量清零，段序號(hào)加1。這意味著同一學(xué)校的考生在同一考場(chǎng)里是不會(huì)相鄰的，前后相隔N-1個(gè)其他學(xué)校的考生。準(zhǔn)考證號(hào)產(chǎn)生后，套用如下公式生成相應(yīng)的考場(chǎng)號(hào)及座位號(hào)：

考場(chǎng)號(hào)=INT（（準(zhǔn)考證號(hào)-1）/30）+1

座位號(hào)=（準(zhǔn)考證號(hào)-1）MOD 30+1

可見，應(yīng)該先生成準(zhǔn)考證號(hào)，然后根據(jù)準(zhǔn)考證號(hào)生成考場(chǎng)號(hào)及座號(hào)。排考場(chǎng)的關(guān)鍵在于生成準(zhǔn)考證號(hào)，考場(chǎng)號(hào)及座位號(hào)的生成來自于準(zhǔn)考證號(hào)的簡(jiǎn)單轉(zhuǎn)換。這種考場(chǎng)編排辦法，只要人數(shù)最多學(xué)校的考生數(shù)不超過考生總?cè)藬?shù)的1/N（N為分段值，即同一學(xué)?？忌谕豢紙?chǎng)需要間隔的人數(shù)），配合S形考場(chǎng)編排就能保證所有的考生前后左右非同一個(gè)學(xué)校。

分段排序法排準(zhǔn)考證號(hào)的具體代碼如下：

Dim duan As Integer//定義段內(nèi)計(jì)數(shù)器變量，在段內(nèi)以1為增量單位遞增

Dim xu As Integer//定義一個(gè)段序號(hào)變量

duan=0

xu=1

For i=1 To rs.RecordCount Step 1//設(shè)置循環(huán)值為1至考生總?cè)藬?shù)

If 3*duan+xu>rs.RecordCount Then//這里假設(shè)同一學(xué)校考生在同一考場(chǎng)的座號(hào)間隔三人

duan=0

xu=xu+1

End If

ws.Cells(i+1,1)=pretext&Format(3*duan+ xu,"0000")//分別為準(zhǔn)考證號(hào)、考場(chǎng)號(hào)及座位號(hào)賦值

ws.Cells(i+1,2)=Int((3*duan+xu-1)/30)+1

ws.Cells(i+1,3)=(3*duan+xu-1)Mod 30+1

duan=duan+1

Next//結(jié)束分段排序法排準(zhǔn)考證號(hào)

2.環(huán)形抽取法

設(shè)想所有已按學(xué)校排序的考生均處于圓環(huán)的某一位置，然后將其中人數(shù)最多學(xué)校的第一名考生的位置定為初始位置，從這個(gè)初始位置開始每隔一固定人數(shù)抽取一名考生，并將這些抽出的考生賦予連續(xù)的編號(hào)，如此不斷循環(huán)，直到最后一名考生。抽出的考生形成一個(gè)新的有序的考生集，按照這個(gè)順序連續(xù)給各考生編排準(zhǔn)考證號(hào)即可。這里有幾個(gè)關(guān)鍵點(diǎn)：①所有考生要按學(xué)校名稱排序。②抽取考生時(shí)間隔的人數(shù)一定要比考生庫(kù)里人數(shù)最多的學(xué)校人數(shù)還要多，否則就不能保證編排考場(chǎng)時(shí)同一學(xué)校的考生不編在相鄰的座位上。③抽取考生數(shù)要與考生總?cè)藬?shù)均為質(zhì)因數(shù)，即這兩個(gè)數(shù)的最大公約數(shù)只能是1，否則不能保證每次抽取的考生位置不重復(fù)。

環(huán)形抽取算法的主要工作是將考生的線性地址轉(zhuǎn)換為環(huán)形位置，關(guān)鍵在于要假想線性地址的首號(hào)與尾號(hào)是連續(xù)的，只要抽取點(diǎn)大于尾號(hào)的地址就將其指針換算為圓環(huán)的相應(yīng)位置。

環(huán)形抽取法排準(zhǔn)考證號(hào)的具體代碼如下：

Dim point As Integer//定義一個(gè)抽取點(diǎn)變量

point=2//由于第一行為標(biāo)題行，所以抽取的起始值為2

For i=1 To rs.RecordCount Step 1//設(shè)定循環(huán)次數(shù)為考生總?cè)藬?shù)

If point>rs.RecordCount+1 Then//抽取點(diǎn)大于尾號(hào)地址時(shí)將其重置到相應(yīng)位置

point=point-rs.RecordCount+1

End If

ws.Cells(point,1)=pretext&Format(I,”0000”)

//分別為準(zhǔn)考證號(hào)、考場(chǎng)號(hào)、座號(hào)賦值

ws.Cells(point,2)=Int((i-1)/30)+1

ws.Cells(point,3)=(i-1)MOD 30+1

point=point+701//將抽取點(diǎn)加上計(jì)算好的抽取因子，本例中抽取因子計(jì)算值為701

Next//結(jié)束環(huán)形抽取法排準(zhǔn)考證號(hào)

3.混洗法

明確需要將各學(xué)校按人數(shù)多少排序后，以人數(shù)最多的學(xué)校人數(shù)為基數(shù)，將所有考生分成若干段，每段為一組，各組首尾相接，第一組組號(hào)碼初始值為0，最后一組人數(shù)不足一組時(shí)作為尾組處理，尾組最后的若干位置可看成“空號(hào)”，這樣各組人數(shù)就可虛擬為相等的，循環(huán)為各組計(jì)算預(yù)置位（可看成一個(gè)指針）及預(yù)置準(zhǔn)考證號(hào)。每組的第一個(gè)考生的預(yù)置位應(yīng)為組號(hào)碼*每組的人數(shù)+1+1（因?yàn)榻M號(hào)碼初始值為0，而且還要算上標(biāo)題行所以要加上兩個(gè)1），下一預(yù)置位依次加1。每組的第一個(gè)考生的預(yù)置準(zhǔn)考證號(hào)為當(dāng)前組號(hào)碼加1（加1同樣是因?yàn)榻M號(hào)碼初始值為0），下一預(yù)置準(zhǔn)考證號(hào)依次加上所劃分的總組數(shù)，加上總組數(shù)的目的是為下面各組的相同位置的考生留下預(yù)置位。需要處理的特殊情況是當(dāng)分組中存在尾組時(shí)，尾組最后的“空號(hào)”是不需要留預(yù)置準(zhǔn)考證號(hào)的，這就要在進(jìn)行分組循環(huán)為準(zhǔn)考證號(hào)賦值時(shí)隨時(shí)檢查當(dāng)前組、當(dāng)前賦值位置是否是“尾組”及“空號(hào)”位置。如果當(dāng)前組雖然不是尾組，但當(dāng)前位置對(duì)應(yīng)的是尾組中相應(yīng)的“空號(hào)”位置，則也不再為該“空號(hào)”預(yù)留預(yù)置位，因此該位置之后的準(zhǔn)考證號(hào)的賦值應(yīng)比之前的數(shù)小1，改為組號(hào)碼+總組數(shù)，換個(gè)說法，也就是非尾組“空號(hào)”位之后的前后準(zhǔn)考證號(hào)之間的差值較前少1。如果當(dāng)前位置是尾組中的“空號(hào)”位置，則退出循環(huán)。簡(jiǎn)而言之，混洗法就是錯(cuò)位插值，特殊處理尾組。

混洗法排準(zhǔn)考證的具體代碼如下：

Dim zkzh As Integer//定義并初始化準(zhǔn)考證號(hào)

zkzh=1

Dim dw As Integer//定義并初始化段尾人數(shù)

dw=0

Dim duan As Integer//定義并初始化由人數(shù)最多的學(xué)校人數(shù)計(jì)算出來的分段數(shù)

If rs.RecordCount/CInt(sz(1,2))<>Int(rs.Record-Count/CInt(sz(1,2)))Then

duan=Int(rs.RecordCount/CInt(sz(1,2)))+1

dw=rs.RecordCount-(duan-1)*CInt(sz(1,2))

Else

duan=Int(rs.RecordCount/CInt(sz(1,2)))

End If

Dim qsh As Integer//定義每段的起始號(hào)

Dim jsq As Integer//定義并初始化計(jì)數(shù)器變量，設(shè)置目的在于判斷當(dāng)前循環(huán)是否已到段尾尾數(shù)之后

jsq=1

For i=0 To duan-1//用所分的段數(shù)循環(huán)

qsh=i*CInt(sz(1,2))+2//計(jì)算起始預(yù)置位

Do While zkzh<=rs.RecordCount

If dw>0 And jsq>dw And i=duan-1 Then

//判斷如果是段尾且空白段則退出循環(huán)

Exit Do

End If

ws.Cells(qsh,1)=pretext&Format(zkzh,”0000”)

//準(zhǔn)考證號(hào)賦值

ws.Cells(qsh,2)=Int((zkzh-1)/30)+1

ws.Cells(qsh,3)=(zkzh-1)MOD 30+1

qsh=qsh+1

If dw>0 And jsq>dw Then//判斷如果是段尾尾數(shù)之后的位置則將預(yù)置位數(shù)值減1

zkzh=zkzh+duan-1

Else

zkzh=zkzh+duan

End If

jsq=jsq+1

Loop

jsq=1//每一段循環(huán)結(jié)束后將計(jì)數(shù)器置1

zkzh=(i+1)+1//每一段循環(huán)的第一個(gè)準(zhǔn)考證號(hào)總比當(dāng)前i計(jì)數(shù)器大1,則賦給下一循環(huán)的準(zhǔn)考證號(hào)值應(yīng)比當(dāng)前i值大2

Next//結(jié)束混洗法排準(zhǔn)考證號(hào)

以上只是眾多計(jì)算機(jī)編排考場(chǎng)程序中常用的三種算法，并且三種算法各有其優(yōu)劣之處。這些算法還可以在應(yīng)用交互上做進(jìn)一步的改進(jìn)，如在SHEET1工作表上增加一個(gè)運(yùn)行宏的按鈕，在程序運(yùn)行時(shí)交互式輸入各種變量值，同時(shí)稍加改造本程序代碼也可在FOXPRO編程環(huán)境下正確運(yùn)行，限于篇幅在此不再贅述。

（編輯：魯利瑞）

G202

B

1673-8454（2010）24-0051-04