巧用Excel VBA 快速编排考场

巧用ExcelVBA快速编排考场座位【摘要】科学的考场座位编排方法可以从根本上杜绝学生考试时的串通舞弊现象，保证考试的公平、公正及其严肃性。本文结合自己的工作经验，利用ExcelVBA编程方法轻松实现了同级各班考生随机排座、且前后左右座位不是同班同学，打印考场座位表、桌贴等功能。经实际应用，操作简单，方便实用。【关键词】随机排座；考场编排；座位表；桌贴；VBA数组在编排考场时，既要基于学校实际，如需考虑各考场人数、组数、每组人数等出现差异，充好利用好每一个考场；还要让同级各班考生被重新随机排序后基本均匀地散布到各个考场，基本做到同一考场内同一班的考生前后左右均不相邻，从根本上杜绝学生考试时的串通舞弊现象，保证考试的公平、公正及其严肃性。随着学校办学规模逐渐扩大和学生人数的增加，考场编排的工作量不断加大。面对Excel工作表内动辄上千的数据行，通过手工多次排序和复制粘贴数据完成考场编排工作显然太麻烦了。那么，能不能用相对比较简单的办法来解决问题呢？答案显然是肯定的。Excel是微软公司的Microsoftoffice的组件之一，它可以进行各种数据的处理、统计分析，在学校工作中有着广泛的应用。例如花名册、座位表、登分表等的电子文档都是使用它来创建的。Excel不仅具有强大的制表功能，同时还内置了系统开发工具VBA。VBA是指VisualBasicforApplication，它是在Office中广泛应用的宏语言，可以直接对Excel对象进行编程，从而提高Excel的利用效率。使用它可以增强Excel的自动化能力，使用户更高效地完成特定任务。因此，笔者空闲时用VBA编了个程序，轻松快速地编排考场，生成考场座位表、桌贴等，经实际应用，操作简单，方便实用。一、考场编排在中高考中各考场人数一般是30人，但基于笔者学校的实际，会出现各考场人数各异、组数各异及每组人数各异的情况。因此，编排考场前，工作人员必须将考生花名册（必须含班级）录入花名册工作表，考场基本信息（必须含考场号、各组人数等）录入考场设置工作表中，如下图所示。

A1教室考场1组人数W组人数3组人数4组人数5组人数6组人数2高一1112121212133高一22666674高一339999975高一44889996局~559999997高一66777778[Bi~779999109高一887777910高一99101091010811高一107888812多媒体118898813电教室125555514阶梯教教114445516电教W155555考场设置工作表编排考场时，为保证考生既要随机分布，又要均匀分布，在程序设计上多次使用了随机编排。（一）、班级内部考生的随机编排。程序在G列（辅助列）对学生生成一次随机数，再以班级和随机数为关键字段进行排序，实现班级内学生顺序的随机性。代码:Randomize(Timer)'初始化随机数生成器Forx=Randomize(Timer)'初始化随机数生成器Forx=2ToSht2R'花名册行循环Cells(x,"G")=Rnd'G列写入随机数NextxWorksheets（"花名册"）.UsedRange.SortKey1:="班级”，Order1:=xlAscending,Key2:="随机数”,Order2:=xlAscending,Header:=xlYes,Orientation:=xlSortColumns'排序运行结果如下图：ABCDEFG1姓名班级学号语文■I-1-数学英语随机数2■1434855570.01236927531455356240.03354632441206460740.0670368675186166440.0753140456136760690.09680682471114834740.09328549681336468580.09998148791624269530.103147562101465645470.147281587111444281350.152936757121585863490.181188285(二)、各班人数均匀分配到各考场。程序自动从《花名册》及《考场设置》工作表获取班级人数、考生总人数及考场人数后，按比例取整的方式计算各班在各考场分配的人数，公式：各班在各考场分配的人数二班级人数*(考场人数/考生总人数)。再因取整余下的考生作二次分配。代码：为提高程序运行速度，程序将各工作表数据读入VBA数组中处理。Forx=2ToUBound(ArrKC,1)考场号循环j=j+1'考场号列号Fori=2ToUBound(RenShuFenPei,1)'班级循环RenShuFenPei(i,j)=Val(ArrBJ(i,2))*Val(ArrKC(i,9))\Val(ArrTJ(2,2))'按比例分配考生一一班级人数*(考场设置的人数/考生总人数)NextiNextx人数分配结果如下图：（三）、编排考场号。获得各班在各考场的人数后，对各班每考生按分配的人数编考场号，对二次分配考生编上"座位"&vbTab&"100"（键盘无法输入vbTab，可防错）。代码：m=0ArrSht2=Worksheets("花名册”).UsedRange.Value读入数组处理数据Forx=2ToSht2R'x某班第一个考生行号k=0'按分配人数按班编考场号***********Forx1=2ToUBound(RenShuFenPei,1)'班级循环IfArrSht2(x,Sht2BJL)=RenShuFenPei(x1,1)Then'班级名相同Fory=2ToUBound(RenShuFenPei,2)'考场循环Forj=1ToVal(RenShuFenPei(x1,y))'该班x1该考场y分配的人数ArrSht2(x+k+j-1,Sht2L+2)=RenShuFenPei(1,y)'考场号ArrSht2(x+k+j-1,Sht2L+3)=j+Rnd'座位号(辅助列，处理前后同班)Nextjk=k+Val(RenShuFenPei(x1,y))该班已编排的人数NextyExitForEndIfNextx1*********************************'该班剩余考生编辅助考场号*************DoWhilek<Val(RenShuFenPei(x1,2))'(班级人数)该班未编考场号人数循环ArrSht2(x+k,Sht2L+2)="座位"&vbTab&"100"考场号(辅助列)ArrSht2(x+k,Sht2L+3)=Rnd'座位号(辅助列)k=k+1'该班已编排的人数累加(含辅助)m=m+1'年级编辅助考场号”座位100"的考生人数累加Loopg““““““““““““““““““““““““““““““““*********************************x=x+k-1'该班结束的行号=开始行号+班级人数-1Nextx将数据写入工作表，并以考场号和座位号为关键字段进行排序，这样二次分配的考生都汇集到一起，并且按座位号列生成的随机数随机排序。然后采用循环语句，将二次分配考生分配到每考场，如果该考场人数已满，则分配给下一考场。这样使得剩余考生还是能尽可能均匀的、随机的分布到各考场。代码：Forx=2ToUBound(ArrSht2,1)IfArrSht2(x,Sht2L+2)="座位”&vbTab&"100"Thenk=x'二次分配考生开始行号ExitForEndIfNextxx=k'开始行号i=Int(KCshu*Rnd+1)'随机产生第一个考生的考场号DoWhilex<m+k'年级未编考场号的行号循环IfiModKCshu<>0Theny=(iModKCshu)+2Elsey=KCshu+2EndIfIfVal(RenShuFenPei(11,y))<Val(RenShuFenPei(12,y))Then已编排考生数<该考场设置的考生数ArrSht2(x,Sht2L+2)=RenShuFenPei(3,y)'考场号Forj=2ToUBound(RenShuFenPei,1)IfArrSht2(x,Sht2BJL)=RenShuFenPei(j,1)Then班级名相同ArrBJL=jExitForEndIfNextjRenShuFenPei(ArrBJL,y)=Val(RenShuFenPei(ArrBJL,y))+1该班ArrBJL该考场y分配的人数累加ArrSht2(x,Sht2L+3)=Val(RenShuFenPei(ArrBJL,y))+Rnd'座位号(辅助列，处理前后同班)RenShuFenPei(11,y)=Val(RenShuFenPei(11,y))+1考场已分配的人数累加x=x+1'循环到下一行EndIfi=(iModKCshu)+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、3.xxx、4.xxx、5.xxx、6.xxx样式的随机数，如下图1。再以考场号和座位号为关键字段进行排序后，初步实现前后无同班，如下图2。

ABCDEFGH1姓名班级.学号语文数芋英语考闻座位气1.68E2李森燕4348555713苏松48E4郑雪玲12064607413.395涕风凝1861664414.71E6徐瑜1367606915.8257吴晓雪11148347416.65E8■4娇22051647311.4539杨灵22354656912.75910徐湘娜24254426413.561121064556414.18212陆灿聪21445716615.67913陈洁煽35061511311.444图1

ABCDEFGH1姓名嶂学号语文■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.05114方亩燕45260414612.14515李小曼94963396012.40416黄文金35958584612.44517苏松48818陈春85561582812.62319嗣桂芳74755566812.69220杨灵22354656912.75921二'-I-110953405712.881224558484813.163图2程序再按蛇形排列座位的方法检查各座位前后左右是否有同班，如果遇有同班，程序通过循环将该考生与同考场其他考生调换座位，直到符合前后左右无同班后退出循环。代码：k=2'第一行为标题列，考生从第二行开始DoWhilek<=UBound(ArrSht2,1)'座次按考场写入数组********Forx=2ToUBound(ArrKC,1)考场号循环IfArrSht2(k,Sht2L+2)=ArrKC(x,2)Then找到考场ExitForEndIfNextxi=0Fory=3ToUBound(ArrKC,2)-1组循环IfVal(ArrKC(x,y))>0Then'该组分配了人数i=i+1'组数累加Forj=1ToVal(ArrKC(x,y))'该组人数IfiMod2<>0Then'奇数组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))-j'行号EndIfNextjk=k+Val(ArrKC(x,y))巳编排的总人数(年级)+1EndIfNextyg““““““““““““““““““““““““*************************'处理前后左右同班***********Form=1ToMaxRenFory=1ToUBound(RenShuFenPei,2)Step2IfLen(RenShuFenPei(m,y))>0AndLen(RenShuFenPei(m,yR))>0ThenIfRenShuFenPei(m,y)=RenShuFenPei(m,yR)OrRenShuFenPei(m,y)=RenShuFenPei(mD,y)Then'左右或前后同班Fory1=1ToUBound(RenShuFenPei,2)Step2CaseCaseRenShuFenPei(m,y)CaseRenShuFenPei(mU,y)CaseRenShuFenPei(mD,y)CaseRenShuFenPei(m,yL)CaseRenShuFenPei(m,yR)CaseElseSelectCaseRenShuFenPei(m,y)CaseRenShuFenPei(m1U,y1)CaseRenShuFenPei(m1D,y1)CaseRenShuFenPei(m1,y1R)CaseRenShuFenPei(m1,y1L)CaseElse'改动座次表&&&&&&&&&StrY=RenShuFenPei(m,y)RenShuFenPei(m,y)=RenShuFenPei(m1,y1)RenShuFenPei(m1,y1)=StrY'&&&&&&&&&&&&&&&&&&'改动考场编排表&&&&&&&&&&Forj=1ToSht2LTemp1(0,j)=ArrSht2(RenShuFenPei(m,y+1),j)NextjForj=1ToSht2LArrSht2(RenShuFenPei(m,y+1),j)=ArrSht2(RenShuFenPei(m1,y1+1),j)NextjForj=1ToSht2LArrSht2(RenShuFenPei(m1,y1+1),j)=Temp1(0,j)Nextj'&&&&&&&&&&&&EndSelectEndSelectNextyNextmLoop运行结果如图3所示:图3最后对各考生编上座位号，考场编排完成。二、座位表、桌贴的生成与打印考生座位表、桌贴等的制作总体上讲是对单元格填充的过程，因此代码比较简单。但由于存在各考场人数不同、组数不同及各组人数不同的情况，在算法上，需利用考场组数及各组人数，采用循环语句进行填充。代码详见上面的前后左右同班处理，运行结果如图3、图4所示。

ABCDEFGHIJKLMN1♦考场座位表2教室:高一63401蛙名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姓名I1姓名26姓名31姓名15-■MBBOA•16班缜3班象5班绩1班缓4班象317学号39学号20学号6学号49学号51图4座位表及桌贴生成完毕后，由于是每个考场一张表，需要将这些表作为一个组才能一次性打印，而且可以选择打印部分考场，否则操作比较麻烦，有违简洁高效的目的，因此，在打印和预览上，在界面用两个ListBox列表框提供选择需打印的工作簿及相应的工作表，程序智能根据不同的表格设置页面，当然也可以在界面更改页面设置，以适应打印输出。然后根据界面上的数据设置工作表页面，再对这些表利用数组一次性选择输出打印和预览。打印界面如图5所示。打印打印范围L工作簿l工作表□□□□□□□□□□□4页面设置「打印范围L工作簿l工作表□□□□□□□□□□□4页面设置「纸张方向■觌向囚厂横向囚-缩敢厂缩放比例：|100g%■调整为：一页宽|g页高-页边距芸厂g右：厂司..r水平居中上:广M下:广M「垂直居中页眉|1司页脚|1司-页码一起始页码：|自动W寻打印页码厂打印顺序先列后行汗先行后列图5代码：Fori=0ToMe.ListBox2.ListCount-1IfMe.ListBox2.Selected(i)=TrueThenReDimPreserveArrSh