【原】隨機(jī)函數(shù)用處大，隨機(jī)安排考生座位

劉卓學(xué)EXCEL 2021-04-02

展開全文

下圖左表是12名考生，現(xiàn)將其隨機(jī)安排在6行2列的考試座位中，結(jié)果如右表所示。如果讓你來做，你會(huì)怎么做？

1.一步到位，多單元格數(shù)組公式

選中D3:E8，輸入下面的公式，按ctrl+shift+enter。

=INDEX(B3:B14,RIGHT(SMALL(RANDBETWEEN(A3:A14^0,999)/1%+A3:A14,ROW(1:6)*2-{1,0}),2))

RANDBETWEEN(A3:A14^0,999)這部分用randbetween生成介于1~999的12個(gè)隨機(jī)數(shù)，結(jié)果為一個(gè)數(shù)組。A3:A14是1~12的自然數(shù)序列，A3:A14^0返回12個(gè)1。

RANDBETWEEN(A3:A14^0,999)/1%+A3:A14這部分用上面得到的12個(gè)隨機(jī)數(shù)乘以100，再加上1~12的序列號。這樣就得到加權(quán)處理后的一個(gè)新數(shù)組。這個(gè)新數(shù)組中每個(gè)元素后2位的序號分別是1~12。

ROW(1:6)*2-{1,0}這部分返回的結(jié)果如下圖所示。得到了一個(gè)6行2列的數(shù)組，結(jié)果分別是1~12，與座位的大小一致。

SMALL(RANDBETWEEN(A3:A14^0,999)/1%+A3:A14,ROW(1:6)*2-{1,0})這部分用small函數(shù)從加權(quán)后的數(shù)組中分別提取第1,2,3,···,12個(gè)最小值。也就是將加權(quán)后的數(shù)組從小到大排序。這一步的目的是將每個(gè)元素后2位的序號順序打亂。

然后用right從上一步的結(jié)果中截取出右邊的2位，得到了打亂順序的1~12的序號。最后用index返回B3:B14這個(gè)區(qū)域中對應(yīng)的姓名。

因?yàn)殡S機(jī)函數(shù)是易失性函數(shù)，結(jié)果會(huì)變化，所以不好截圖說明。下面選取其中1組隨機(jī)數(shù)來進(jìn)行簡單演示和說明。

①是由randbetween產(chǎn)生的介于1~999的12個(gè)隨機(jī)數(shù)；②是由①的結(jié)果乘以100，再加上1~12的序號得到的，可以看到每個(gè)數(shù)字的后2位分別是1~12；③是row函數(shù)產(chǎn)生的6行2列的二維數(shù)組。

④是用small函數(shù)從②中分別提取第1,2;3,4;···,12個(gè)最小值得到的，也就是②和③共同得到④，可以看到每個(gè)數(shù)字的后2位不是1~12的順序了，已經(jīng)被重排了。⑤是用right從④中截取右邊2位數(shù)得到的。這樣就得到了隨機(jī)排列的1~12的序號。

2.輔助列法，公式簡單

如果看不懂第1種方法，可以用下面的輔助列法。其中G3:H8是輔助列，選中G3:H8，輸入公式=rand()，按ctrl+enter批量填充。然后在D3單元格輸入下面的公式，右拉下拉填充。

=OFFSET($B$2,RANK(G3,$G$3:$H$8),)

rand生成的隨機(jī)數(shù)基本上是不會(huì)重復(fù)的，然后用rank計(jì)算每個(gè)隨機(jī)數(shù)在全部12個(gè)隨機(jī)數(shù)中的排名，返回的結(jié)果肯定是1~12中的一個(gè)數(shù)，并且這12個(gè)結(jié)果也不會(huì)重復(fù)。也就是無論怎么變化，都是1~12這12個(gè)數(shù)，只不過順序和位置不同。

玩過數(shù)獨(dú)的小伙伴都知道，每個(gè)九宮格里只能是1~9這9個(gè)數(shù)。

如果你不理解，可以把rank那部分提出來，單獨(dú)查看結(jié)果。最后以B2單元格為起點(diǎn)，用offset偏移對應(yīng)的行數(shù)，返回對應(yīng)的姓名。

鏈接：

https://pan.baidu.com/s/1LwNxa8_19Cb6Ar_RylNvzg

提取碼：nnti