巧用Excel VBA 快速编排考场试场

1、巧用Excel VBA快速编排考场座位广西桂林市阳朔县外语实验中学莫孟福百度文库下载地址：学校考试考场编排软件（单年级） HYPERLINK /view/464023029ec3d5bbfc0a740f.html /view/464023029ec3d5bbfc0a740f.html学校考试考场编排软件（多年级） HYPERLINK /view/62cfe5d652d380eb63946d6f.html /view/62cfe5d652d380eb63946d6f.html学校考试考场编排软件（多年级，文理绲编），请联系作者索要。【摘要】科学的考场座位编排方法可以从根本上杜绝学生考试时的串通舞

2、弊 现象，保证考试的公平、公正及其严肃性。本文结合自己的工作经验，利用Excel VBA编程方法轻松实现了同级各班考生随机排座、且前后左右座位不是同班同 学，打印考场座位表、桌贴等功能。经实际应用，操作简单，方便实用。【关键词】随机排座；考场编排；座位表；桌贴；VBA数组在编排考场时，既要基于学校实际，如需考虑各考场人数、组数、每组人数 等出现差异，充好利用好每一个考场；还要让同级各班考生被重新随机排序后基 本均匀地散布到各个考场，基本做到同一考场内同一班的考生前后左右均不相 邻，从根本上杜绝学生考试时的串通舞弊现象，保证考试的公平、公正及其严肃 性。随着学校办学规模逐渐扩大和学生人数的增加，

3、考场编排的工作量不断加大。 面对Excel工作表内动辄上千的数据行，通过手工多次排序和复制粘贴数据完成 考场编排工作显然太麻烦了。那么，能不能用相对比较简单的办法来解决问题呢？ 答案显然是肯定的。Excel是微软公司的Microsoft office的组件之一，它可以进行各种数据的处 理、统计分析，在学校工作中有着广泛的应用。例如花名册、座位表、登分表等 的电子文档都是使用它来创建的。Excel不仅具有强大的制表功能，同时还内置 了系统开发工具 VBA。VBA 是指 Visual Basic for Application，它是在 Office 中 广泛应用的宏语言，可以直接对Excel对象进

4、行编程，从而提高Excel的利用效 率。使用它可以增强Excel的自动化能力，使用户更高效地完成特定任务。因此， 笔者空闲时用VBA编了个程序，轻松快速地编排考场，生成考场座位表、桌贴 等，经实际应用，操作简单，方便实用。一、考场编排在中高考中各考场人数一般是30人，但基于笔者学校的实际，会出现各考 场人数各异、组数各异及每组人数各异的情况。因此，编排考场前，工作人员必 须将考生花名册（必须含班级）录入花名册工作表，考场基本信息（必须含考场 号、各组人数等）录入考场设置工作表中，如下图所示。A1教室考场1组人数W组人数3组人数4组人数5组人数6组人数2高一 1112121212133高一 22

5、666674高一 339999975高一 44889996局559999997高一 66777778Bi 779999109高一 887777910高一 99101091010811高一107888812多媒体118898813电教室125555514阶梯教教114445516电教W155555考场设置工作表编排考场时，为保证考生既要随机分布，又要均匀分布，在程序设计上多次 使用了随机编排。（一）、班级内部考生的随机编排。程序在G列（辅助列）对学生生成一次 随机数，再以班级和随机数为关键字段进行排序，实现班级内学生顺序的随机性。代码:Randomize (Timer)

6、初始化随机数生成器For x = 2 To Sht2R花名册行循环Cells(x, G) = RndG列写入随机数Next xWorksheets（花 名册）.UsedRange.Sort Key1:=班级”，Order1:=xlAscending, Key2:=随机数”,Order2:=xlAscending, Header:=xlYes, Orientation:=xlSortColumns排序运行结果如下图：ABCDEFG1姓名班级学号语文 I-1- 数学英语随机数21434855570.01236927531455356240.03354632441206460740.06703686

7、75186166440.0753140456136760690.09680682471114834740.09328549681336468580.09998148791624269530.103147562101465645470.147281587111444281350.152936757121585863490.181188285(二)、各班人数均匀分配到各考场。程序自动从花名册及考场设置 工作表获取班级人数、考生总人数及考场人数后，按比例取整的方式计算各班在 各考场分配的人数，公式：各班在各考场分配的人数二班级人数*(考场人数/考生 总人数)。再因取整余下的考生作二次分配。代码：为提

8、高程序运行速度，程序将各工作表数据读入VBA数组中处理。For x = 2 To UBound(ArrKC, 1)考场号循环j = j + 1考场号列号For i = 2 To UBound(RenShuFenPei, 1)班级循环RenShuFenPei(i, j) = Val(ArrBJ(i, 2) * Val(ArrKC(i,9) Val(ArrTJ(2, 2)按比例分配考生一一班级人数*(考场设置的人数/考生总人 数)Next iNext x人数分配结果如下图：（三）、编排考场号。获得各班在各考场的人数后，对各班每考生按分配的 人数编考场号，对二次分配考生编上座位& vbTab & 1

9、00（键盘无法输入 vbTab，可防错）。代码：m = 0ArrSht2= Worksheets(花 名册”).UsedRange.Value 读入数组处理数据For x = 2 To Sht2R x某班第一个考生行号 k = 0按分配人数按班编考场号*For x1 = 2 To UBound(RenShuFenPei, 1)班级循环If ArrSht2(x, Sht2BJL) = RenShuFenPei(x1, 1) Then 班级名相同For y = 2 To UBound(RenShuFenPei, 2)考场循环For j = 1 To Val(RenShuFenPei(x1, y)该

10、班 x1 该考场 y 分配的人数ArrSht2(x + k + j - 1, Sht2L + 2) = RenShuFenPei(1, y) 考场号ArrSht2(x + k + j - 1, Sht2L + 3) = j + Rnd 座位号(辅 助列，处理前后同班)Next jk = k + Val(RenShuFenPei(x1, y)该班已编排的人数Next yExit ForEnd IfNext x1*该班剩余考生编辅助考场号*Do While k Val(RenShuFenPei(x1, 2)(班级人数)该班未编考场号人数循环ArrSht2(x + k, Sht2L + 2)=座位&

11、 vbTab & 100考场号(辅助 列)ArrSht2(x + k, Sht2L + 3) = Rnd 座位号(辅助列)k = k + 1 该班已编排的人数累加(含辅助)m = m + 1 年级编辅助考场号”座位100的考生人数累加Loopg“ *x = x + k - 1该班结束的行号=开始行号+班级人数-1Next x将数据写入工作表，并以考场号和座位号为关键字段进行排序，这样二次分 配的考生都汇集到一起，并且按座位号列生成的随机数随机排序。然后采用循环 语句，将二次分配考生分配到每考场，如果该考场人数已满，则分配给下一考场。 这样使得剩余考生还是能尽可能均匀的、随机的分布到各考场。代码

12、：For x = 2 To UBound(ArrSht2, 1)If ArrSht2(x, Sht2L + 2)=座位”& vbTab & 100 Thenk = x二次分配考生开始行号Exit ForEnd IfNext xx = k开始行号i = Int(KCshu * Rnd + 1)随机产生第一个考生的考场号Do While x m + k 年级未编考场号的行号循环If i Mod KCshu 0 Theny = (i Mod KCshu) + 2Elsey = KCshu + 2End IfIf Val(RenShuFenPei(11, y) Val(RenShuFenPei(12,

13、 y) Then 已编排考 生数 该考场设置的考生数ArrSht2(x, Sht2L + 2) = RenShuFenPei(3, y) 考场号For j = 2 To UBound(RenShuFenPei, 1)If ArrSht2(x, Sht2BJL) = RenShuFenPei(j, 1) Then 班级名相同ArrBJL = jExit ForEnd IfNext jRenShuFenPei(ArrBJL, y) = Val(RenShuFenPei(ArrBJL, y) + 1 该班 ArrBJL该考场y分配的人数累加ArrSht2(x, Sht2L + 3) = Val(Re

14、nShuFenPei(ArrBJL, y) + Rnd 座位 号(辅助列，处理前后同班)RenShuFenPei(11, y) = Val(RenShuFenPei(11, y) + 1 考场已分配的 人数累加x = x + 1循环到下一行End Ifi = (i Mod KCshu) + 1考场号列号累加Loop(四)、前后左右同班处理。在上面编排考场号的代码“ArrSht2(x + k + j - 1, Sht2L + 3) = j + Rnd”用于初步处理前后同班问题。例如1、4、5、6、7、8、9、 10班在第1考场均分配了 6名考生，各班考生在座位号列都生成1.xxx、2.xxx、

15、3.xxx、4.xxx、5.xxx、6.xxx 样式的随机数，如下图1。再以考场号和座位号为关 键字段进行排序后，初步实现前后无同班，如下图2。ABCDEFGH1姓名班级.学号语文数芋英语考闻座位气1. 68E2李森燕4348555713苏松 48E4郑雪玲12064607413. 395涕风凝1861664414. 71E6徐瑜1367606915. 8257吴晓雪11148347416. 65E84娇22051647311. 4539杨灵22354656912. 75910徐湘娜24254426413. 561121064556414.18212陆灿聪214457

16、16615. 67913陈洁煽35061511311. 444图1ABCDEFGH1姓名嶂学号语文I -1- 数学英语考场座位号2蔡瑾琳71460565411. 093李灿桐105754484111. 444除洁妮35061511311. 4445机娇22051647311. 4536陈馥丹51858895211. 5117至树*容95164625411. 5388聿森 6889肖泽鹏81347704711. 78510陈集君42860666211. 89411柯益柱64064676911. 95712吴蜿6348524012. 04613黄东阳53563847712

17、. 05114方亩燕45260414612.14515李小曼94963396012. 40416黄文金35958584612. 44517苏松 48818陈春85561582812. 62319嗣桂芳74755566812. 69220杨灵22354656912. 75921二-I-110953405712. 881224558484813.163图2程序再按蛇形排列座位的方法检查各座位前后左右是否有同班，如果遇有同 班，程序通过循环将该考生与同考场其他考生调换座位，直到符合前后左右无同 班后退出循环。代码：k = 2第一行为标题列，考生从第二行开始Do While

18、k 0 Then该组分配了人数i = i + 1组数累加For j = 1 To Val(ArrKC(x, y)该组人数If i Mod 2 0 Then 奇数组RenShuFenPei(j, 2 * i - 1) = ArrSht2(k + j - 1, Sht2BJL)班级RenShuFenPei(j, 2 * i) = k + j - 1 行号Else 偶数组RenShuFenPei(j, 2 * i - 1) = ArrSht2(k + Val(ArrKC(x, y) - j, Sht2BJL)班级RenShuFenPei(j, 2 * i) = k + Val(ArrKC(x, y)

19、 - j 行号End IfNext jk = k + Val(ArrKC(x, y)巳编排的总人数(年级)+1End IfNext yg“*处理前后左右同班*For m = 1 To MaxRenFor y = 1 To UBound(RenShuFenPei, 2) Step 2If Len(RenShuFenPei(m, y) 0 And Len(RenShuFenPei(m, yR) 0 ThenIf RenShuFenPei(m, y) = RenShuFenPei(m, yR) OrRenShuFenPei(m, y) = RenShuFenPei(mD, y) Then左右或前后同

20、班For y1 = 1 To UBound(RenShuFenPei, 2) Step 2CaseCase RenShuFenPei(m, y)Case RenShuFenPei(mU, y)Case RenShuFenPei(mD, y)Case RenShuFenPei(m, yL)Case RenShuFenPei(m, yR)Case ElseSelect Case RenShuFenPei(m, y)Case RenShuFenPei(m1U, y1)Case RenShuFenPei(m1D, y1)Case RenShuFenPei(m1, y1R)Case RenShuFenP

21、ei(m1, y1L)Case Else改动座次表&StrY = RenShuFenPei(m, y)RenShuFenPei(m, y)=RenShuFenPei(m1, y1)RenShuFenPei(m1, y1)=StrY&改动考场编排表&For j = 1 To Sht2LTemp1(0, j)=ArrSht2(RenShuFenPei(m, y + 1), j)Next jFor j = 1 To Sht2LArrSht2(RenShuFenPei(m, y + 1), j) = ArrSht2(RenShuFenPei(m1, y1 + 1), j)Next jFor j = 1

22、 To Sht2LArrSht2(RenShuFenPei(m1,y1 + 1), j) = Temp1(0, j)Next j&End SelectEnd SelectNext yNext mLoop运行结果如图3所示:图3最后对各考生编上座位号，考场编排完成。二、座位表、桌贴的生成与打印考生座位表、桌贴等的制作总体上讲是对单元格填充的过程，因此代码比较 简单。但由于存在各考场人数不同、组数不同及各组人数不同的情况，在算法上， 需利用考场组数及各组人数，采用循环语句进行填充。代码详见上面的前后左右 同班处理，运行结果如图3、图4所示。ABCDEFGHIJKLMN1考场座位表2教室:高一 63

23、401蛙名14蛙名1b姓名28姓名29姓名56班缜7班缱9班象6班缱5班缱97学号13学号14学号34学号50学号38902甦名13蛀名16姓名27姓名30姓名10 11班缜8班缱10班象2班缱10班缱212学号41学号4学号4学号61学号23131403甦名I2姓名I 1姓名26姓名31姓名15-MBBOA16班缜3班象5班绩1班缓4班象317学号39学号20学号6学号49学号51图4座位表及桌贴生成完毕后，由于是每个考场一张表，需要将这些表作为一个 组才能一次性打印，而且可以选择打印部分考场，否则操作比较麻烦，有违简洁 高效的目的，因此，在打印和预览上，在界面用两个ListBox列表框提供

24、选择需 打印的工作簿及相应的工作表，程序智能根据不同的表格设置页面，当然也可以 在界面更改页面设置，以适应打印输出。然后根据界面上的数据设置工作表页面， 再对这些表利用数组一次性选择输出打印和预览。打印界面如图5所示。打印打印范围L工作簿l工作表 4页面设置纸张方向觌向囚 厂横向 囚-缩敢厂缩放比例：| 100 g %调整为：一页宽| g页高-页边距芸厂g右：厂司 . . r水平居中上:广M下:广M垂直居中 页眉| 1 司页脚| 1 司-页码一起始页码：|自动W寻打印页码厂打印顺序先列后行汗先行后列图5代码：For i = 0 To Me.ListBox2.ListCount - 1If Me.ListBox2.Selected(i) = True ThenReDim Preserve ArrSht(k)