張久慶

（微山縣教育局，山東濟寧277600）

Excel實現(xiàn)考場編排的幾種方法

張久慶

（微山縣教育局，山東濟寧277600）

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

考場編排；排考場

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

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

一、考場編排前期數(shù)據(jù)準備工作

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

主要代碼如下所示：

Public Sub編排考場()

Dim cnn As New ADODB.Connection//聲明并引用一個ADO的Connection連接對象變量

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

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

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

Dim pretext As String

pretext="1037"http://指定準考證流水號前的預置代碼，可隨意設定，這里假定為“1037”

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

Dim ws As Worksheet//聲明Excel工作表對象變量

On Error Resume Next

Application.DisplayAlerts=False

Sheet1.Copy

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

ActiveWorkbook.Close

Worksheets("考場編排").Delete

Application.DisplayAlerts=True

On Error GoTo 0

With Worksheets

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

End With

ws.Name="考場編排"

myWbName=ThisWorkbook.Path&"臨時數(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è)學校,count(*)as ren from [Sheet1$]group by畢業(yè)學校order by count(*)desc" //設置查詢篩選SQL語句，查詢各個畢業(yè)學校的人數(shù)

rs.Open SQL，cnn，adOpenKeyset，adLockOptimistic

//打開查詢的記錄集

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

sl=rs.RecordCount

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

ReDim sz(1 To sl,2)

rs.MoveFirst//將查詢結果集的數(shù)據(jù)復制到字符串數(shù)組中

For i=1 To sl Step 1

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

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

rs.MoveNext

Next i

rs.Close//關閉記錄集并釋放記錄集對象變量

Set rs=Nothing

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

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

rs.Open SQL,cnn,adOpenKeyset,adLockOptimistic

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

//復制到新建的考場編排工作表中

For i=1 To sl Step 1

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

ws.Activate

If i=1 Then//如果是第一個學校則在工作表首行添加標題行

xb=2//xb變量用來存貯當前記錄行號

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//取消對記錄集的篩選條件

ws.Columns(1).Insert//給工作表增加準考證號、考場號及座位號字段

ws.Columns(1).Insert

ws.Columns(1).Insert

ws.Cells(1,1)="準考證號"

ws.Cells(1,2)="考場號"

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

分段排序法排準考證號代碼/環(huán)形抽取法排準考證號代碼/混洗法排準考證號代碼//三種準考證號編排方法的實現(xiàn)代碼請參見考場編排部分

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

cnn.Close

Set rs=Nothing

Set cnn=Nothing

Set ws=Nothing

End Sub//考場編排結束，將“考場編排”工作表按準考證號重新排序即可

二、考場編排

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

1.分段排序法

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

考場號=INT（（準考證號-1）/30）+1

座位號=（準考證號-1）MOD 30+1

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

分段排序法排準考證號的具體代碼如下：

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

Dim xu As Integer//定義一個段序號變量

duan=0

xu=1

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

If 3*duan+xu>rs.RecordCount Then//這里假設同一學校考生在同一考場的座號間隔三人

duan=0

xu=xu+1

End If

ws.Cells(i+1,1)=pretext&Format(3*duan+ xu,"0000")//分別為準考證號、考場號及座位號賦值

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//結束分段排序法排準考證號

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

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

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

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

Dim point As Integer//定義一個抽取點變量

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

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

If point>rs.RecordCount+1 Then//抽取點大于尾號地址時將其重置到相應位置

point=point-rs.RecordCount+1

End If

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

//分別為準考證號、考場號、座號賦值

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

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

point=point+701//將抽取點加上計算好的抽取因子，本例中抽取因子計算值為701

Next//結束環(huán)形抽取法排準考證號

3.混洗法

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

混洗法排準考證的具體代碼如下：

Dim zkzh As Integer//定義并初始化準考證號

zkzh=1

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

dw=0

Dim duan As Integer//定義并初始化由人數(shù)最多的學校人數(shù)計算出來的分段數(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//定義每段的起始號

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

jsq=1

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

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

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”)

//準考證號賦值

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ù)之后的位置則將預置位數(shù)值減1

zkzh=zkzh+duan-1

Else

zkzh=zkzh+duan

End If

jsq=jsq+1

Loop

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

zkzh=(i+1)+1//每一段循環(huán)的第一個準考證號總比當前i計數(shù)器大1,則賦給下一循環(huán)的準考證號值應比當前i值大2

Next//結束混洗法排準考證號

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

（編輯：魯利瑞）

G202

B

1673-8454（2010）24-0051-04