考场的随机排座及动态演示

汤泽梅 郭民之 孙鹿

摘要：当给定了考生的学号、姓名等信息之后，如何快速地将一个考场内考生的座位随机安排？这是一个很有实用价值的问题。本文灵活运用了Excel中的多个函数的组合，巧妙地解决了考场的随机排座和考生座位的查找问题，并用实例进行动态演示说明。

关键词：随机排座；行列人数任意；Excel函数组合应用；动态演示

中图分类号：TP311 文献标识码：A 文章编号：1009-3044（2018）16-0253-03

Random Seating Arrangement in the Examination Room and Dynamic Demonstration

TANG Ze-mei，GUO Min-zhi，SUN Lu

（School of Mathematics， Yunnan Normal University， Kunming 650500， China）

Abstract： How to quickly arrange the seats of examinees in an exam room when given the information of the students number， name and so forth？ This is a very practical problem. This paper uses the combination of several functions in Excel flexibly， which perfectly solves the problem of random arrangement and finding of examinee seats， and uses examples to demonstrate and illustrate.

Key words： Random seating arrangement； The number of each column is arbitrary； Combination application of Excel function； Dynamic demonstration

1 引言

隨着社会的发展，越来越多的数据处理问题可以借助计算机利用专业统计软件来解决，比如SPSS，SAS，Matlab，R等，但这些软件通常操作和编程都较为复杂。而Excel是一款广为流行的大众化表格数据处理软件，操作简单，功能强大，绘图方便，内置函数丰富，还可加载自带的数据分析工具。若能将它们巧妙的组合使用，就能解决绝大多数实际数据分析问题。本文主要讨论如何运用Excel软件解决考场的随机排座问题。从已有文献来看，涂志芳[1]在研究高校校园运动会秩序册编排时，运用了Excel的函数组合解决了运动员分组、场次安排、成绩汇总等问题，提高了组织工作的准确率和效率，但编程较为复杂，不够简明；黎国胜[2]使用Excel产生随机数的方法实现了考生座位的随机编排，但不能根据考场情况改变考场内座位的行数和列数。本文利用Excel中多个函数的巧妙组合，实现了一个考场内的考生座位的随机安排，同时考场座位的列数及每列考生数都可以随机变化，这是一个目前还没有人研究过的问题。同时，本文给出了通过点击下拉菜单中的根据考生个人信息，就能快速查找出考生座位位置的方法。进一步，本文用一个有40名考生、5列座位的考场中考生随机排座案例来进行动态演示说明。

2 用Excel实现考场座位的随机安排

2.1 随机排列考生信息

这里实现考生随机排序的方法是在每一位考生信息后随机产生一个（0，1）区间内的随机数，然后把随机数列从小到大排序（注意排序时要选中“扩展选定区域”，使得考生信息也跟着随机数一起排序），达到将考生信息随机排序的目的。具体操作如下：

如图1，在“考场随机排座1”工作表中，A列是学号，B列是姓名，在单元格C2内输入“=A2&B2;”，该公式将A2和B2单元格中的考生学号和姓名连接在一起构成第一个考生的信息。接着将C2中的公式向下拖动填充至最后一名学生所在的行，得到40名考生的完整信息，再把它们定义为一个名称[3]（表示一个动态单元格区域）方便使用，取名为“考生信息1”，即：

考生信息1“=OFFSET（$C$2，，，（COUNTA（$C：$C）-1））”.

这里使用了OFFSET函数来构造动态单元格区域，该函数是以指定单元格引用为参照系，通过给定偏移量来得到新的单元格区域的引用[4]，其中COUNTA函数是返回参数列表区域中非空单元格的个数。定义名称“考生信息1”的好处是当考生人数增加或减少时，该动态单元格区域也会随之增加或减少。然后在D2单元格中输入公式“=RAND（）”并向下拖动填充最后一名学生所在的行，这样就在区域D2：D41中随机产生了40个0到1 之间的随机数[5]，它们与区域C2：C41（即“考生信息1”）中的40个考生信息一一对应。再一起选中这40个随机数（或直接选中D列），依次点击【开始】/【排序和筛选】/【升序】/【扩展选定区域】/【排序】，就实现了对考生顺序的随机排列。

下面用两种方法对考生座位进行随机编排。方法1的特点是：每一列都在第一个单元格内编写一个公式，然后对所在列单元格进行公式自动填充，有几列就重复填充几次，优点是不需使用数组运算；方法2的优点是只需在考场单元格区域的左上角单元格内编写一个公式就可对整个考场单元格区域进行自动填充，但是需要使用数组运算。两种方法都可以通过D列随机数的重新排序实现对考生座位的随机安排。

2.2 考场随机排座方法1

如图1，假定考场有5列座位（列数可改变），每列的座位数（行数）可以取为1至10（行数也可改变）中任一个数，即考场区域是图1中的浅蓝色区域H3：L12。我们可在每一列的上方单元格即H2，I2，J2，K2和L2内分别设置该列最大人数。为方便起见，可使用下拉菜单。具体操作为：选中要设置下拉菜单的单元格（包括H2，I2，J2和K2），依次点击【数据】/【数据有效性】/【序列】，然后在“来源”框内输入“=$F$3：$F$12”。这时再点击其中任一个单元格（如H2），该单元格右边就会出现一个黑色的实心小三角形，点击该三角形，就会出现“1，2，…，10”一串数（即单元格区域$F$3：$F$12中的10个数），此时用鼠标点击其中某个数就能方便地选定该列最大人数。最后在L2单元格内输入公式“=40-$H$2-$I$2-$J$2-$K$2”进行考场人数控制。

注意函数INDEX的使用格式，这里INDEX（考生信息1， ROW（k：k））是对名称为“考生信息1”的这个单列区域中第k行单元格的引用[4]。条件执行函数IF和INDEX函数经常组合在一起执行条件查找引用[6]。本节中多次使用了這种组合，即在每一列的第一个单元格H3，I3，…，L3内分别输入公式：

=IF（ROW（1：1）>H$2，""，INDEX（考生信息1，ROW（1：1）））；

=IF（ROW（1：1）>I$2，""，INDEX（考生信息1，ROW（1：1）+$H$2））；

……

=IF（ROW（1：1）>L$2，""，INDEX（考生信息1，ROW（1：1）+$H$2+$I$2+$J$2+$K$2））.

公式输入完成后将单元格公式在本列内向下各自填充至最后一行，这样就完成了对所有列（即整个考场单元格区域H3：L12）考生座位的编排，注意公式中的行数随之由ROW（1：1）变动到ROW（10：10）。这几个公式相差不大，含义基本相同：如果考场区域内某列的某单元格的行数ROW（k：k）超过该列指定的最大人数，就显示空值，否则，就依次从“考生信息1”中查找引用行数“=当前行数+已经排定的考生人数”的单元格内的考生信息。为了节省篇幅，截图时对第26-39行进行了隐藏，见图1。

2.3 考场随机排座方法2

方法1需要在每列开始单元格内编写一个公式然后在本列内向下填充至最后一行，n列就要编写填充n次，稍显麻烦。下面介绍的方法2克服了这个不足，只需在考场单元格区域的左上角单元格内编写一个公式就可向下向右对整个考场单元格区域进行公式自动填充，但是要构造一个辅助矩阵并需要作数组运算。在对考生的随机排序之后，先构造一个上三角辅助矩阵，见图2中的单元格区域H1：L5（注：这个辅助矩阵是根据考场的列数构造的，本文假定考场有5列，因此就构造了一个5×5阶上三角矩阵，上三角元素全为1），这个上三角阵在后面的公式中起到重要的作用。仿照方法1在单元格H8，I8，J8，K8和L8内设置各列最大人数和下拉菜单，然后在单元格H9中输入公式：

“=IF（ROW（1：1）>H$8，""，INDEX（考生信息2， ROW（1：1）

+SUM（$H$8：$L$8*TRANSPOSE（H$1：H$5））））”.

公式输入完成后要同时按住Ctrl+Shift键点击Enter向下向右拖动自动填充至L18，完成对整个考场对应的单元格区域H9：L18的公式自动填充，其中考生信息2“=OFFSET（$C$2，，，（COUNTA（$C：$C）-1））”。

这里组合使用了查找引用函数INDEX、转置函数TRANSPOSE及求和函数SUM[4]。公式自动填充过程中，行数由ROW（1：1）变到ROW（10：10），$H$8：$L$8保持不变，表示各列最大人数，函数TRANSPOSE括号中的选项随着列名的变动由H$1：H$5变动到L$1：L$5。这个公式的含义是如果考场区域内某单元格所在列的行数“ROW（k：k）”超过该列指定的最大人数，就显示空值，否则，就依次从“考生信息2”所在列中去查找引用行数“= ROW（k：k）+SUM（$H$8：$L$8*TRANSPOSE（#$1：#$5））” 的单元格内的考生信息，即“考生信息2”中行数为“当前行数+已经排定的考生人数”的单元格内的考生信息。注意到当列名“#”由左向右依次逐列取为H，I，J，K和L时，公式“SUM（$H$8：$L$8*TRANSPOSE（#$1：#$5））”给出的对应值依次为0，$H$8，$H$8+$I$8，$H$8+$I$8+$J$8和$H$8+$I$8+$J$8+$K$8，计算结果与上述方法1中的计算结果是一致的。但这里巧妙地构造了一个辅助矩阵，只需在H9中输入一个公式，就可对整个考场区域H9：L18进行公式自动填充。为了节省篇幅，截图时也对第26-39行进行了隐藏。图2给出了部分公式批注及其执行结果。

2.4 两种排座的比较说明

对考生信息随机排列之后，我们可以用本文介绍的两种方法实现考场随机排座。这两种方法的对比结果如下：相同点：都是用RAND（）产生的随机数，按快捷键F9可重新生成随机数，随机数重新排序后考场座位的安排也会随之发生变化；不同点：对方法1，每列需要编写一个公式，该公式只对本列单元格进行自动填充，有n列座位的考场要拖放填充n次，但不需要构造辅助矩阵和数组运算。对方法2，只需要编写一个公式就能对整个考场区域进行公式自动填充，但需要构造一个辅助矩阵并且使用数组运算才能实现，比方法1步骤要多一些。两种方法都可以通过“动态产生随机数+重新排序”实现对考生座位的随机安排。并且两种方法都具可移植性，程序编写完成后都能方便地用于其他考场的随机排序，只需把其他考场的考生学号和姓名复制过来覆盖原来考生的对应信息即可。

3 考生座位查询

当考场随机排座完成之后，考生如何快速地在电脑上查出自己的考场座位位置？这可轻松地按下列步骤来编程实现。以方法2为例，先在“座位查找”右边的单元格H23（见图2）内设置下拉菜单，依次点击【数据】/【数据有效性】/【序列】，然后在“来源”框内输入“=$C$2：$C$41”（即 “考生信息2”）完成下拉菜单设置。然后在单元格F24，F25内分别输入公式：

“=SUMPRODUCT（（H9：L18=$H$23）*COLUMN（H9：L18））-7”，

“=SUMPRODUCT（（H9：L18=$H$23）*ROW（H9：L18））-8”.

得到H23中的考生座位的列数和行数，这里SUMPRODUCT函数返回数组间对应的元素乘积之和[4]。公式中“-7”和“-8”是因为考场单元格区域H9：L18的左边和上边已经被占用了7列和8行。例如，单击单元格H23，在出现的下拉菜单中选中 “1104424姚一诺”，在单元格H24和H25中就立刻会分别出现4和5，表明考生“1104424姚一诺”在考场中座位的列数为4，行数为5，见图2。对方法1中考场座位的查找也可类似进行，详情见图1中单元格H24和H25的批注。

4 小结

两种方法都可以事先任意设定考场列数，每列考生最大人数，按一次F9键就能重新产生一列随机数，对该列随机数重新扩展排序后就能对考生座位进行随机安排。而且本文给出的程序是通用的、可移植的，把其他考场的考生信息复制过来覆盖原有的考生信息，就能实现对其他考场座位的随机安排。

本文使用的函数单独看都相对简单，容易理解，但将它们适当组合起来运用后却能解决复杂的问题，用Excel的任一个版本都可以轻松实现。这样，在安排布置考场时，就可节省很多工作量，提高考务工作的效率和准确性。同时，修改和调整也方便快捷，保证了考场随机排座的质量与速度。

参考文献：

[1] 涂志芳. 运用Excel数编排高校校园运动会秩序册[J]. 武汉体育学刊， 2013，20（5）：64-67.

[2] 黎国胜. 用Excel2002轻松实现考试座位的随机编排[J]. 信息技术教育， 2003，9：83.

[3] 郭民之，等. 概率统计实验[M]. 北京：北京大学出版社， 2012：6-11.

[4] 王巧伶. Excel 2010函数应用速查手册[M]. 北京：机械工业出版社， 2013.

[5] 顾晓鸣，陆群，沈伟. 运用 Excel“随机数发生器”进行随机化分组 [J]. 中国医院统计， 2007，14（3）：287-288.

[6] Jacky Davis. 函数组合应用范例[M]. 北京：科学出版社， 2013.