湯澤梅 郭民之 孫鹿

摘要：當(dāng)給定了考生的學(xué)號(hào)、姓名等信息之后，如何快速地將一個(gè)考場(chǎng)內(nèi)考生的座位隨機(jī)安排？這是一個(gè)很有實(shí)用價(jià)值的問題。本文靈活運(yùn)用了Excel中的多個(gè)函數(shù)的組合，巧妙地解決了考場(chǎng)的隨機(jī)排座和考生座位的查找問題，并用實(shí)例進(jìn)行動(dòng)態(tài)演示說(shuō)明。

關(guān)鍵詞：隨機(jī)排座；行列人數(shù)任意；Excel函數(shù)組合應(yīng)用；動(dòng)態(tài)演示

中圖分類號(hào)：TP311 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：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 引言

隨著社會(huì)的發(fā)展，越來(lái)越多的數(shù)據(jù)處理問題可以借助計(jì)算機(jī)利用專業(yè)統(tǒng)計(jì)軟件來(lái)解決，比如SPSS，SAS，Matlab，R等，但這些軟件通常操作和編程都較為復(fù)雜。而Excel是一款廣為流行的大眾化表格數(shù)據(jù)處理軟件，操作簡(jiǎn)單，功能強(qiáng)大，繪圖方便，內(nèi)置函數(shù)豐富，還可加載自帶的數(shù)據(jù)分析工具。若能將它們巧妙的組合使用，就能解決絕大多數(shù)實(shí)際數(shù)據(jù)分析問題。本文主要討論如何運(yùn)用Excel軟件解決考場(chǎng)的隨機(jī)排座問題。從已有文獻(xiàn)來(lái)看，涂志芳[1]在研究高校校園運(yùn)動(dòng)會(huì)秩序冊(cè)編排時(shí)，運(yùn)用了Excel的函數(shù)組合解決了運(yùn)動(dòng)員分組、場(chǎng)次安排、成績(jī)匯總等問題，提高了組織工作的準(zhǔn)確率和效率，但編程較為復(fù)雜，不夠簡(jiǎn)明；黎國(guó)勝[2]使用Excel產(chǎn)生隨機(jī)數(shù)的方法實(shí)現(xiàn)了考生座位的隨機(jī)編排，但不能根據(jù)考場(chǎng)情況改變考場(chǎng)內(nèi)座位的行數(shù)和列數(shù)。本文利用Excel中多個(gè)函數(shù)的巧妙組合，實(shí)現(xiàn)了一個(gè)考場(chǎng)內(nèi)的考生座位的隨機(jī)安排，同時(shí)考場(chǎng)座位的列數(shù)及每列考生數(shù)都可以隨機(jī)變化，這是一個(gè)目前還沒有人研究過的問題。同時(shí)，本文給出了通過點(diǎn)擊下拉菜單中的根據(jù)考生個(gè)人信息，就能快速查找出考生座位位置的方法。進(jìn)一步，本文用一個(gè)有40名考生、5列座位的考場(chǎng)中考生隨機(jī)排座案例來(lái)進(jìn)行動(dòng)態(tài)演示說(shuō)明。

2 用Excel實(shí)現(xiàn)考場(chǎng)座位的隨機(jī)安排

2.1 隨機(jī)排列考生信息

這里實(shí)現(xiàn)考生隨機(jī)排序的方法是在每一位考生信息后隨機(jī)產(chǎn)生一個(gè)（0，1）區(qū)間內(nèi)的隨機(jī)數(shù)，然后把隨機(jī)數(shù)列從小到大排序（注意排序時(shí)要選中“擴(kuò)展選定區(qū)域”，使得考生信息也跟著隨機(jī)數(shù)一起排序），達(dá)到將考生信息隨機(jī)排序的目的。具體操作如下：

如圖1，在“考場(chǎng)隨機(jī)排座1”工作表中，A列是學(xué)號(hào)，B列是姓名，在單元格C2內(nèi)輸入“=A2&B2;”，該公式將A2和B2單元格中的考生學(xué)號(hào)和姓名連接在一起構(gòu)成第一個(gè)考生的信息。接著將C2中的公式向下拖動(dòng)填充至最后一名學(xué)生所在的行，得到40名考生的完整信息，再把它們定義為一個(gè)名稱[3]（表示一個(gè)動(dòng)態(tài)單元格區(qū)域）方便使用，取名為“考生信息1”，即：

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

這里使用了OFFSET函數(shù)來(lái)構(gòu)造動(dòng)態(tài)單元格區(qū)域，該函數(shù)是以指定單元格引用為參照系，通過給定偏移量來(lái)得到新的單元格區(qū)域的引用[4]，其中COUNTA函數(shù)是返回參數(shù)列表區(qū)域中非空單元格的個(gè)數(shù)。定義名稱“考生信息1”的好處是當(dāng)考生人數(shù)增加或減少時(shí)，該動(dòng)態(tài)單元格區(qū)域也會(huì)隨之增加或減少。然后在D2單元格中輸入公式“=RAND（）”并向下拖動(dòng)填充最后一名學(xué)生所在的行，這樣就在區(qū)域D2：D41中隨機(jī)產(chǎn)生了40個(gè)0到1 之間的隨機(jī)數(shù)[5]，它們與區(qū)域C2：C41（即“考生信息1”）中的40個(gè)考生信息一一對(duì)應(yīng)。再一起選中這40個(gè)隨機(jī)數(shù)（或直接選中D列），依次點(diǎn)擊【開始】/【排序和篩選】/【升序】/【擴(kuò)展選定區(qū)域】/【排序】，就實(shí)現(xiàn)了對(duì)考生順序的隨機(jī)排列。

下面用兩種方法對(duì)考生座位進(jìn)行隨機(jī)編排。方法1的特點(diǎn)是：每一列都在第一個(gè)單元格內(nèi)編寫一個(gè)公式，然后對(duì)所在列單元格進(jìn)行公式自動(dòng)填充，有幾列就重復(fù)填充幾次，優(yōu)點(diǎn)是不需使用數(shù)組運(yùn)算；方法2的優(yōu)點(diǎn)是只需在考場(chǎng)單元格區(qū)域的左上角單元格內(nèi)編寫一個(gè)公式就可對(duì)整個(gè)考場(chǎng)單元格區(qū)域進(jìn)行自動(dòng)填充，但是需要使用數(shù)組運(yùn)算。兩種方法都可以通過D列隨機(jī)數(shù)的重新排序?qū)崿F(xiàn)對(duì)考生座位的隨機(jī)安排。

2.2 考場(chǎng)隨機(jī)排座方法1

如圖1，假定考場(chǎng)有5列座位（列數(shù)可改變），每列的座位數(shù)（行數(shù)）可以取為1至10（行數(shù)也可改變）中任一個(gè)數(shù)，即考場(chǎng)區(qū)域是圖1中的淺藍(lán)色區(qū)域H3：L12。我們可在每一列的上方單元格即H2，I2，J2，K2和L2內(nèi)分別設(shè)置該列最大人數(shù)。為方便起見，可使用下拉菜單。具體操作為：選中要設(shè)置下拉菜單的單元格（包括H2，I2，J2和K2），依次點(diǎn)擊【數(shù)據(jù)】/【數(shù)據(jù)有效性】/【序列】，然后在“來(lái)源”框內(nèi)輸入“=$F$3：$F$12”。這時(shí)再點(diǎn)擊其中任一個(gè)單元格（如H2），該單元格右邊就會(huì)出現(xiàn)一個(gè)黑色的實(shí)心小三角形，點(diǎn)擊該三角形，就會(huì)出現(xiàn)“1，2，…，10”一串?dāng)?shù)（即單元格區(qū)域$F$3：$F$12中的10個(gè)數(shù)），此時(shí)用鼠標(biāo)點(diǎn)擊其中某個(gè)數(shù)就能方便地選定該列最大人數(shù)。最后在L2單元格內(nèi)輸入公式“=40-$H$2-$I$2-$J$2-$K$2”進(jìn)行考場(chǎng)人數(shù)控制。

注意函數(shù)INDEX的使用格式，這里INDEX（考生信息1， ROW（k：k））是對(duì)名稱為“考生信息1”的這個(gè)單列區(qū)域中第k行單元格的引用[4]。條件執(zhí)行函數(shù)IF和INDEX函數(shù)經(jīng)常組合在一起執(zhí)行條件查找引用[6]。本節(jié)中多次使用了這種組合，即在每一列的第一個(gè)單元格H3，I3，…，L3內(nèi)分別輸入公式：

=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））.

公式輸入完成后將單元格公式在本列內(nèi)向下各自填充至最后一行，這樣就完成了對(duì)所有列（即整個(gè)考場(chǎng)單元格區(qū)域H3：L12）考生座位的編排，注意公式中的行數(shù)隨之由ROW（1：1）變動(dòng)到ROW（10：10）。這幾個(gè)公式相差不大，含義基本相同：如果考場(chǎng)區(qū)域內(nèi)某列的某單元格的行數(shù)ROW（k：k）超過該列指定的最大人數(shù)，就顯示空值，否則，就依次從“考生信息1”中查找引用行數(shù)“=當(dāng)前行數(shù)+已經(jīng)排定的考生人數(shù)”的單元格內(nèi)的考生信息。為了節(jié)省篇幅，截圖時(shí)對(duì)第26-39行進(jìn)行了隱藏，見圖1。

2.3 考場(chǎng)隨機(jī)排座方法2

方法1需要在每列開始單元格內(nèi)編寫一個(gè)公式然后在本列內(nèi)向下填充至最后一行，n列就要編寫填充n次，稍顯麻煩。下面介紹的方法2克服了這個(gè)不足，只需在考場(chǎng)單元格區(qū)域的左上角單元格內(nèi)編寫一個(gè)公式就可向下向右對(duì)整個(gè)考場(chǎng)單元格區(qū)域進(jìn)行公式自動(dòng)填充，但是要構(gòu)造一個(gè)輔助矩陣并需要作數(shù)組運(yùn)算。在對(duì)考生的隨機(jī)排序之后，先構(gòu)造一個(gè)上三角輔助矩陣，見圖2中的單元格區(qū)域H1：L5（注：這個(gè)輔助矩陣是根據(jù)考場(chǎng)的列數(shù)構(gòu)造的，本文假定考場(chǎng)有5列，因此就構(gòu)造了一個(gè)5×5階上三角矩陣，上三角元素全為1），這個(gè)上三角陣在后面的公式中起到重要的作用。仿照方法1在單元格H8，I8，J8，K8和L8內(nèi)設(shè)置各列最大人數(shù)和下拉菜單，然后在單元格H9中輸入公式：

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

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

公式輸入完成后要同時(shí)按住Ctrl+Shift鍵點(diǎn)擊Enter向下向右拖動(dòng)自動(dòng)填充至L18，完成對(duì)整個(gè)考場(chǎng)對(duì)應(yīng)的單元格區(qū)域H9：L18的公式自動(dòng)填充，其中考生信息2“=OFFSET（$C$2，，，（COUNTA（$C：$C）-1））”。

這里組合使用了查找引用函數(shù)INDEX、轉(zhuǎn)置函數(shù)TRANSPOSE及求和函數(shù)SUM[4]。公式自動(dòng)填充過程中，行數(shù)由ROW（1：1）變到ROW（10：10），$H$8：$L$8保持不變，表示各列最大人數(shù)，函數(shù)TRANSPOSE括號(hào)中的選項(xiàng)隨著列名的變動(dòng)由H$1：H$5變動(dòng)到L$1：L$5。這個(gè)公式的含義是如果考場(chǎng)區(qū)域內(nèi)某單元格所在列的行數(shù)“ROW（k：k）”超過該列指定的最大人數(shù)，就顯示空值，否則，就依次從“考生信息2”所在列中去查找引用行數(shù)“= ROW（k：k）+SUM（$H$8：$L$8*TRANSPOSE（#$1：#$5））” 的單元格內(nèi)的考生信息，即“考生信息2”中行數(shù)為“當(dāng)前行數(shù)+已經(jīng)排定的考生人數(shù)”的單元格內(nèi)的考生信息。注意到當(dāng)列名“#”由左向右依次逐列取為H，I，J，K和L時(shí)，公式“SUM（$H$8：$L$8*TRANSPOSE（#$1：#$5））”給出的對(duì)應(yīng)值依次為0，$H$8，$H$8+$I$8，$H$8+$I$8+$J$8和$H$8+$I$8+$J$8+$K$8，計(jì)算結(jié)果與上述方法1中的計(jì)算結(jié)果是一致的。但這里巧妙地構(gòu)造了一個(gè)輔助矩陣，只需在H9中輸入一個(gè)公式，就可對(duì)整個(gè)考場(chǎng)區(qū)域H9：L18進(jìn)行公式自動(dòng)填充。為了節(jié)省篇幅，截圖時(shí)也對(duì)第26-39行進(jìn)行了隱藏。圖2給出了部分公式批注及其執(zhí)行結(jié)果。

2.4 兩種排座的比較說(shuō)明

對(duì)考生信息隨機(jī)排列之后，我們可以用本文介紹的兩種方法實(shí)現(xiàn)考場(chǎng)隨機(jī)排座。這兩種方法的對(duì)比結(jié)果如下：相同點(diǎn)：都是用RAND（）產(chǎn)生的隨機(jī)數(shù)，按快捷鍵F9可重新生成隨機(jī)數(shù)，隨機(jī)數(shù)重新排序后考場(chǎng)座位的安排也會(huì)隨之發(fā)生變化；不同點(diǎn)：對(duì)方法1，每列需要編寫一個(gè)公式，該公式只對(duì)本列單元格進(jìn)行自動(dòng)填充，有n列座位的考場(chǎng)要拖放填充n次，但不需要構(gòu)造輔助矩陣和數(shù)組運(yùn)算。對(duì)方法2，只需要編寫一個(gè)公式就能對(duì)整個(gè)考場(chǎng)區(qū)域進(jìn)行公式自動(dòng)填充，但需要構(gòu)造一個(gè)輔助矩陣并且使用數(shù)組運(yùn)算才能實(shí)現(xiàn)，比方法1步驟要多一些。兩種方法都可以通過“動(dòng)態(tài)產(chǎn)生隨機(jī)數(shù)+重新排序”實(shí)現(xiàn)對(duì)考生座位的隨機(jī)安排。并且兩種方法都具可移植性，程序編寫完成后都能方便地用于其他考場(chǎng)的隨機(jī)排序，只需把其他考場(chǎng)的考生學(xué)號(hào)和姓名復(fù)制過來(lái)覆蓋原來(lái)考生的對(duì)應(yīng)信息即可。

3 考生座位查詢

當(dāng)考場(chǎng)隨機(jī)排座完成之后，考生如何快速地在電腦上查出自己的考場(chǎng)座位位置？這可輕松地按下列步驟來(lái)編程實(shí)現(xiàn)。以方法2為例，先在“座位查找”右邊的單元格H23（見圖2）內(nèi)設(shè)置下拉菜單，依次點(diǎn)擊【數(shù)據(jù)】/【數(shù)據(jù)有效性】/【序列】，然后在“來(lái)源”框內(nèi)輸入“=$C$2：$C$41”（即 “考生信息2”）完成下拉菜單設(shè)置。然后在單元格F24，F(xiàn)25內(nèi)分別輸入公式：

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

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

得到H23中的考生座位的列數(shù)和行數(shù)，這里SUMPRODUCT函數(shù)返回?cái)?shù)組間對(duì)應(yīng)的元素乘積之和[4]。公式中“-7”和“-8”是因?yàn)榭紙?chǎng)單元格區(qū)域H9：L18的左邊和上邊已經(jīng)被占用了7列和8行。例如，單擊單元格H23，在出現(xiàn)的下拉菜單中選中 “1104424姚一諾”，在單元格H24和H25中就立刻會(huì)分別出現(xiàn)4和5，表明考生“1104424姚一諾”在考場(chǎng)中座位的列數(shù)為4，行數(shù)為5，見圖2。對(duì)方法1中考場(chǎng)座位的查找也可類似進(jìn)行，詳情見圖1中單元格H24和H25的批注。

4 小結(jié)

兩種方法都可以事先任意設(shè)定考場(chǎng)列數(shù)，每列考生最大人數(shù)，按一次F9鍵就能重新產(chǎn)生一列隨機(jī)數(shù)，對(duì)該列隨機(jī)數(shù)重新擴(kuò)展排序后就能對(duì)考生座位進(jìn)行隨機(jī)安排。而且本文給出的程序是通用的、可移植的，把其他考場(chǎng)的考生信息復(fù)制過來(lái)覆蓋原有的考生信息，就能實(shí)現(xiàn)對(duì)其他考場(chǎng)座位的隨機(jī)安排。

本文使用的函數(shù)單獨(dú)看都相對(duì)簡(jiǎn)單，容易理解，但將它們適當(dāng)組合起來(lái)運(yùn)用后卻能解決復(fù)雜的問題，用Excel的任一個(gè)版本都可以輕松實(shí)現(xiàn)。這樣，在安排布置考場(chǎng)時(shí)，就可節(jié)省很多工作量，提高考務(wù)工作的效率和準(zhǔn)確性。同時(shí)，修改和調(diào)整也方便快捷，保證了考場(chǎng)隨機(jī)排座的質(zhì)量與速度。

參考文獻(xiàn)：

[1] 涂志芳. 運(yùn)用Excel數(shù)編排高校校園運(yùn)動(dòng)會(huì)秩序冊(cè)[J]. 武漢體育學(xué)刊， 2013，20（5）：64-67.

[2] 黎國(guó)勝. 用Excel2002輕松實(shí)現(xiàn)考試座位的隨機(jī)編排[J]. 信息技術(shù)教育， 2003，9：83.

[3] 郭民之，等. 概率統(tǒng)計(jì)實(shí)驗(yàn)[M]. 北京：北京大學(xué)出版社， 2012：6-11.

[4] 王巧伶. Excel 2010函數(shù)應(yīng)用速查手冊(cè)[M]. 北京：機(jī)械工業(yè)出版社， 2013.

[5] 顧曉鳴，陸群，沈偉. 運(yùn)用 Excel“隨機(jī)數(shù)發(fā)生器”進(jìn)行隨機(jī)化分組 [J]. 中國(guó)醫(yī)院統(tǒng)計(jì)， 2007，14（3）：287-288.

[6] Jacky Davis. 函數(shù)組合應(yīng)用范例[M]. 北京：科學(xué)出版社， 2013.