EXCEL实现考场编排的几种方法.pdf

1、中国教育信息化发行部：cylmoeeducn 技术应用 Excel实现考场编排的几种方法 张久庆 (微山县教育局，山东济宁277600) 摘要：实现考务管理的信息化、现代化是当今各类招生考试机构及学校教务部门面临的共同课 题，文章在总结了几种计算机编排考场算法的基础上，充分利用Excel的易操作性及内置的VBA编 程特性，提供了三种利用Excel工作表数据进行考场自动编排的实现方法。 关键词：考场编排；排考场 中图分类号：G202 文献标识码：B 文章编号：16738454(2010)24005104 利用计算机程序进行考场编排，快捷、方便、高 效、公平，是很多考务人员进行考场编排首选的通行

2、办法。但由于绝大多数编排程序都是一些专用软件， 或者需依托数据库管理系统平台才能运行，对考务人 员的计算机应用水平提f“了更高的要求同时也将一 部分计算机基础比较差的同志拒之门外。在对当前比 较流行的考场编排算法进行分析研究的基础上考虑 到Excel电子表格软件应用普及率较高，许多办公人 员都能熟练操作，笔者认为运用Excel内置的VBA (Visual Basic For Application程序设计语言)进行简 单编程、实现考场编排不失为一种值得推荐的做法。 使用VBA操作Excel中的数据，可以将其工作 簿中的多个T作表作为关系型数据库中的数据表来 操作，甚至不需要打开_T作簿也可以读

3、取T作表中的 数据。要实现上述操作，就要用到微软提供的访问数 据库的ADO(ActiveX)数据对象模型，透过OLEDB 编程接口对数据表进行存取查询等操作 要在VBA 中使用ADO，首先需要在Excel的VBA编程环境中 设置对ADO的引用，在VBA项目窗口中单击工具菜 单中的“引用”，在弹出的窗口中选择Microsoft Ac tiveX Data Objects 28，点击确定。如果本应用程序可 能要在低版本的Excel中运行，则要考虑使用低版本 的ADO library，以提高兼容性。 一、考场编排前期数据准备工作 编程环境使用Excel2007，文件存 格式采用 2007启用宏的工作

4、薄文件(XLSM扩展名)或者Ex ce1972003(XLS扩展名)T作薄文件。在此我fr,fE设 需要为某一高中招生考试编排考场，某考点共有N 所学校的学生考试，每一个考场都标准化安排30名 考生，要求每个考生的前后左右都不能是同一个学校 的学生，需要编排考场的数据已存放在当前工作簿的 SHEET1 T作表中，主要栏目包括考生姓名、毕业学 校等信息，其中毕业学校是必须的栏目，因为编排考 场时要依据毕业学校字段对考生进行分组排序，在进 行编排考场前，需要处理的前期数据准备工作主要有 以下几点：在VBA项目窗口中插入一个模块，并在 此模块中新建一个“编排考场”过程。声明并初始化 相关变量。由于E

5、xcel VBA的ADO对自身工作簿 中的工作表进行查询时效率非常低，且存在无法关闭 进程的问题，因此需要将本工作簿中存放考生数据的 SHEET1工作表复制另存为另一个XLS扩展名工作 薄文件(与Excel972003完全兼容的T作薄文件格 式)，下一步将从此工作薄而不是当前工作薄文件查 询数据。在当前工作薄中新建一个考场编排工作 表，以存放编场结果。连接第三步另存的存贮原始 考生数据的T作簿，统计各学校名称及人数，并按学 校人数多少排序将其存入一个临时数组。使用同一 连接查询全部考生记录，并按临时数组中学校名称及 顺序循环设置筛选条件，将所有考生数据依次复制到 新建的“考场编排”工作表中，同

6、时由程序自动为此新 工作表插入准考证号、考场号、座号字段，为下一步编 排考场做好数据准备工作。 主要代码如下所示： Public Sub编排考场f1 Dim cnn As New ADODBConnection 声明并 引用一个ADO的Connection连接对象变量 Dim rs As New ADODBRecordset 声明并 引用一个ADO的Recordset记录集对象变量 Dim cnnStr As String 声明连接字符串变量 chma Education Info 5 1 圈技术应用 Dim SQL As String 声明查询字符串变量 Dim pretext As Str

7、ing pretext=”1037” 指定准考证流水号前 的预置代码，可随意设定，这里假定为“1037” Dim myWbName As String 声明Excel工作 簿名称、字符串变量 Dim WS As Worksheet 声明Excel工作 表对象变量 On Error Resume Next ApplicationDisplayAlerts=False Sheet1Copy ActiveWorkbookSaveAs ThisWorkbookPath&”临 时数据xls”，FileFormat：=xlExcel8将当前工作薄的 SHEET1工作表另存为当前目录下的临时数据xls以 备

8、查询，并在当前工作簿中新建一个考场编排工作 表，以保存筛选结果 ActiveWorkbookClose Worksheets(”考场编排”1Delete ApplicationDisplayAlerts=True On Error GoTo 0 With Worksheets Set WS=Add(after：=Item(Count)1 End With WSName=”考场编排” myWbName=ThisWorkbookPath&“临时数据 xls”指定要连接查询的工作簿名称(带完整路径) cnnStr=”Provider=Microsof1JetOLEDB40；”一 建立与指定工作簿的连

9、接 &”Extended Propenies=Excel 80；”一 &”Data Source=”&myWbName cnnOpen cnnStr SQL=”select毕业学校，count( )as ren from 【Sheetl$group by毕业学校order by count( )desc” ，设置查询筛选SQL语句查询各个毕业学校的人数 rsOpen SQL，cnn，adOpenKeyset，adLock0ptimistic 打开查询的记录集 Dim sl As Integer 声明一整型变量用来 存贮记录集中的学校数量 sl=rsRecordCount Dim sz0 As

10、String 建立一个动态二维字 符串数组sz0，存放各学校的学校名称及人数 52 中国教育信息化2010 24(基础教育) 中国教育信息化编辑部：mismoeeducI1 ReDim sz(1 To sl，2) rsMoveFirst 将查询结果集的数据 复制到字符串数组中 For i=l To sl Step 1 sz(i，1)：rs(”毕业学校”)Value sz(i，2)：rs(”ren”)Value rsMoveNext Next i rsClose 关闭记录集并释放记录集对象变量 Set rs=Nothing SQL=”select$fromSheetl$order by毕业学校”

11、 ，设置查询筛选SQL语句，查询全部考生数据 rsOpen SQL，cnn，adOpenKeyset，adLockOptimistic 设置过滤条件，从记录集中分别筛选出各学 校的考生数据并按照学校人数从多到少的顺序 ，复制到新建的考场编排工作表中 For i=l To sl Step 1 rsFilter=”毕业学校= ”&sz(i，1)&” ” WSActivate If i：1 Then 如果是第一个学校则在工作表 首行添加标题行 xh=2 xb变量用来存贮当前记录行号 For ii=l To rsFieldsCount WSCells(1，ii)=rsFields(ii1)Name N

12、ext ii Else xb=xb+sz(i一1，21 End If WSRange(”a&xb)CopyFromRecordset rs Next rsFilter=adFiherNone取消对记录集的筛选条件 WSColumns f1)Insert 给工作表增加准考证 号、考场号及座位号字段 WSColumns(I)Insert WSColumns(1)Insert WSCells(1，11=”准考证号” WSCells(1，2_l：”考场号” WSCells(1，3)=”座位号” 分段排序法排准考证号代码环形抽取法排准考 证号代码混洗法排准考证号代码 Z种准考证号 编排方法的实现代码请参

13、见考场编排部分 中国教育信息化发行部：cylmoeeduCB 技术应用 rsClose 清除变量释放内存 cnnClose Set rs=Nothing Set cnn=Nothing Set WS=Nothing End Sub 考场编排结束，将“考场编 排”工作表按准考证号重新排序即可 二、考场编排 计算机考场编排目前比较常见及通用的算法主 要有分段排序法、环形抽取法、混洗法等。现将Excel 实现以上几种算法的过程介绍如下。 1分段排序法 在前期已将所有考生按学校名称及人数由大到 小的顺序排列并存入“考场编排”数据表后，虚拟地将 该表中所有考生分为若干段，这里我们假设为N段 从前往后循环

14、地为每一段的每一名考生赋予唯一的 准考证序号，方法是用N乘以一个以1为单位不断递 增的计数器变量，再加上本段所在的段序号(段序号 起始值为1，同一段的段序号值相同)。不必理会每段 具体有多少人，也不必理会计数器变量的大小，只要所 生成的准考证序号大于考生总人数，则本段循环结束 进入下一段循环，计数器变量清零，段序号加1。这意味 着同一学校的考生在同一考场里是不会相邻的，前后相 隔N一1个其他学校的考生。准考证号产生后，套用如下 公式生成相应的考场号及座位号： 考场号-INT(准考证号一1)30)+l 座位号=(准考证号一1)MOD 30+1 可见，应该先生成准考证号，然后根据准考证号 生成考场

15、号及座号。排考场的关键在于生成准考证 号，考场号及座位号的生成来自于准考证号的简单转 换。这种考场编排办法，只要人数最多学校的考生数 不超过考生总人数的1N(N为分段值，即同一学校考 生在同一考场需要间隔的人数)，配合S形考场编排 就能保证所有的考生前后左右非同一个学校。 分段排序法排准考证号的具体代码如下： Dim duan As Integer 定义段内计数器变量 在段内以1为增量单位递增 Dim XU As Integer 定义一个段序号变量 duan=0 XL1：1 For i=I To rsRecordCount Step I 设置循 环值为1至考生总人数 If 3 duan+XUr

16、sRecordCount Then 这里 假设同一学校考生在同一考场的座号间隔三人 duan=0 XU=XU+l End If WSCells(i+1。11 pretext&Format(3 duan+ XU，”0000”)分别为准考证号、考场号及座位号赋值 WSCells(i+1，2)=Int(3 duan+xll一1)30)+1 WSCells(i+1，3)=(3 duan+XU一1)Mod 30+1 duan：duan+l Next 结束分段排序法排准考证号 2环形抽取法 设想所有已按学校排序的考生均处于圆环的某 一位置，然后将其中人数最多学校的第一名考生的位 置定为初始位置，从这个初始

17、位置开始每隔一同定人 数抽取一名考生，并将这些抽出的考生赋予连续的编 号，如此不断循环，直到最后一名考生。抽出的考生形 成一个新的有序的考生集，按照这个顺序连续给各考 生编排准考证号即可。这里有几个关键点：所有考 生要按学校名称排序。抽取考生时间隔的人数一定 要比考生库里人数最多的学校人数还要多，否则就不 能保证编排考场时同一学校的考生不编在相邻的座 位上。抽取考生数要与考生总人数均为质因数，即 这两个数的最大公约数只能是1，否则不能保证每次 抽取的考生位置不重复 环形抽取算法的主要工作是将考生的线性地址 转换为环形位置，关键在于要假想线性地址的首号与 尾号是连续的，只要抽取点大于尾号的地址就

18、将其指 针换算为圆环的相应位置。 环形抽取法排准考证号的具体代码如下： Dim point As Integer 定义一个抽取点变量 point=2 由于第一行为标题行 所以抽取的起始值为2 For i=1 To rsRecordCount Step l设定循环 次数为考生总人数 If pointrsRecordCount+1 Then 捕取点大 于尾号地址时将其重置到相应位置 point pointrsRecordCount+l End Tf chma Education Info 53 丑技术应用 WSCells(point，1)=pretext&Format(I，”0000”) 分别为准

19、考证号、考场号、座号赋值 WSCells(point，2)=Int(i一1)30)-4-1 WSCells(point，3)=(i-1)MOD 30+1 point=point+701 将抽取点加上计算好的 抽取因子，本例中抽取因子计算值为701 Next 结束环形抽取法排准考证号 3混洗法 明确需要将各学校按人数多少排序后，以人数最 多的学校人数为基数，将所有考生分成若干段，每段为 一组，各组首尾相接，第一组组号码初始值为0，最后一 组人数不足一组时作为尾组处理，尾组最后的若干位 置可看成“空号”，这样各组人数就可虚拟为相等的，循 环为各组计算预置位(可看成一个指针)及预置准考证 号。每组的

20、第一个考生的预置位应为组号码 每组的 人数+1+1(因为组号码初始值为0，而且还要算上标题 行所以要加上两个1)，下一预置位依次加1。每组的第 一个考生的预置准考证号为当前组号码加1(加1同样 是因为组号码初始值为0)，下一预置准考证号依次加 上所划分的总组数，加上总组数的目的是为下面各组 的相同位置的考生留下预置位。需要处理的特殊情况 是当分组中存在尾组时，尾组最后的“空号”是不需要 留预置准考证号的这就要在进行分组循环为准考证 号赋值时随时检查当前组、当前赋值位置是否是“尾 组”及“空号”位置。如果当前组虽然不是尾组，但当前 位置对应的是尾组中相应的“空号”位置，则也不再为 该“空号”预留

21、预置位，因此该位置之后的准考证号的 赋值应比之前的数小1，改为组号码+总组数，换个说 法，也就是非尾组“空号”位之后的前后准考证号之问 的差值较前少1。如果当前位置是尾组中的“空号”位 置，则退出循环。简而言之，混洗法就是错位插值，特 殊处理尾组 混洗法排准考证的具体代码如下： Dim zkzh As Integer 定义并初始化准考证号 zkzh=1 Dim dw As Integer 定义并初始化段尾人数 dw=0 Dim duan As Integer 定义并初始化由人数最 多的学校人数计算出来的分段数 If rsRecordCountClnt(sz(1，2)dw And i=duan一

22、1 Then ,0断如果是段尾且空白段则退出循环 Exit Do End If WSCells(qsh，1)=pretext&Format(zkzh，”0000”) ，准考证号赋值 WSCells(qsh，2)=Int(zkzh-1)30)4-1 WSCells(qsh，3)=(zkzh-1)MOD 30 4-1 qsh：qsh 4-1 If dw0 And jsqdw Then ll-i断如果 是段尾尾数之后的位置则将预置位数值减1 zkzh=zkzh 4-duanl Else zkzh=zkzh+duan End If jsq=jsq+1 Loop jsq=1 每一段循环结束后将计数器置l zkzh= +1)+1 每一段循环的第一个准考 证号总比当前i计数器大1，则赋给下一循环的准考 证号值应比当前i值大2 Next 结束混洗法排准考证号 以上只是众多计算机编排考场程序中常用的三种 算法，并且三种算法各有其优劣之处。这些算法还可以 在应用交互上做进一步的改进如在SHEET1工作表 上增加一个运行宏的按钮，在程序运行时交互式输入 各种变量值，同时稍加改造本程序代码也可在FOX PRO编程环境下正确运行，限于篇幅在此不再赘述。 (编辑：鲁利瑞)