原文標(biāo)題：《哪位 Excel 高人琢磨出的這個(gè)隨機(jī)抽獎(jiǎng)技巧，太絕了！》
年前我們發(fā)布過(guò)一篇文章《我用 Excel 做了一個(gè)年會(huì)抽獎(jiǎng)器，然后老板點(diǎn)了一下……》，這其中的核心是獲取不重復(fù)隨機(jī)整數(shù)。
獲取不重復(fù)隨機(jī)整數(shù)的方法有很多，迭代計(jì)算太過(guò)雞肋，VBA 編程未免牛刀殺雞。
今天，小花就跟大家分享幾個(gè)實(shí)用的函數(shù)公式，超贊的那種哦！
1、輔助排名法
RAND 函數(shù)的作用是生成 0-1 之間的隨機(jī)數(shù)，于是我們只需通過(guò) RAND 函數(shù)生成一組隨機(jī)數(shù)，再將它們進(jìn)行排名，就有極大概率可以得到一組不重復(fù)隨機(jī)整數(shù)。
C 列 —— 輔助列公式：
=RAND()
B2—— 不重復(fù)隨機(jī)整數(shù)公式：
=RANK(C2,$C$2:$C$11)
公式說(shuō)明：
RAND 函數(shù)可以返回 0-1 之間的隨機(jī)數(shù)；
RANK 函數(shù)則是返回某個(gè)數(shù)在一列數(shù)字中相對(duì)于其他數(shù)值的大小排名。
所以，先生成隨機(jī)數(shù)值，再計(jì)算每個(gè)數(shù)的排名值，即可得到一組隨機(jī)整數(shù)。
PS : 公式按【F9】鍵會(huì)重新計(jì)算，重新計(jì)算即可刷新排名組，獲得新的不重復(fù)隨機(jī)整數(shù)。本文其他案例同理。
這兩個(gè)函數(shù)是我們?nèi)〔恢貜?fù)隨機(jī)數(shù)中最常見(jiàn)的一種方法。
它比較簡(jiǎn)單，容易學(xué)會(huì)。
不過(guò)這個(gè)公式有個(gè)致命 Bug 是，每一個(gè) RAND 函數(shù)生成的隨機(jī)數(shù)都是獨(dú)立的，也就是說(shuō)，這些隨機(jī)數(shù)還是有極小的概率彼此相等，進(jìn)而出現(xiàn)重復(fù)的排名值。如下圖：
RAND 函數(shù)輔助法并不嚴(yán)謹(jǐn)，且需要輔助列，因此只能算是不重復(fù)隨機(jī)整數(shù)的入門(mén)級(jí)解法。
接下來(lái)，小花來(lái)介紹兩種比較嚴(yán)謹(jǐn)?shù)姆椒?，可能比較難 ，大家重在理解解決的思路，干貨滿滿，一定要堅(jiān)持看下去哦！
2、逐次消除法
獲取不重復(fù)隨機(jī)整數(shù)的核心問(wèn)題是：如何讓已經(jīng)出現(xiàn)的數(shù)字不再出現(xiàn)？
解決思路：把出現(xiàn)的數(shù)字從抽取數(shù)字池中消除掉，然后再?gòu)氖Ｓ鄶?shù)字中隨機(jī)取數(shù)，這樣就能夠生成一組不重復(fù)隨機(jī)整數(shù)。
下面這個(gè)公式就是按照這個(gè)思路設(shè)置的。
B3—— 逐次消除法公式：
{=SMALL( IF( COUNTIF($B$1:B2,ROW($1:$10)), "", ROW($1:$10)), RANDBETWEEN(1,12-ROW()))}PS：該公式內(nèi)含數(shù)組運(yùn)算，輸入公式后需按【Ctrl+Shift+Enter】函數(shù)才能正確運(yùn)算。
公式比較復(fù)雜，大家跟著我的思路，進(jìn)一步理解公式的原理。
B3 單元格公式說(shuō)明：
① COUNTIF($B$1:B2,ROW($1:$10))
這一步的主要目的是：通過(guò) Countif 函數(shù)進(jìn)行數(shù)值計(jì)數(shù)，進(jìn)而判斷當(dāng)前單元格的上一單元格數(shù)值是否有在 1 到 10 中出現(xiàn)過(guò) 。
當(dāng)前單元格是 B3 ，$B$1:B2 是它前面的單元格。$B$1:B2 僅鎖定起始單元格，表示從首個(gè)單元格 B1 到當(dāng)前單元格的上一單元格，這就涵蓋了全部已抽取的整數(shù)。
ROW ($1:$10) 返回 1 到 10 的有序數(shù)組 {1;2;3;4;5;6;7;8;9;10}，COUNTIF 函數(shù)統(tǒng)計(jì)這組數(shù)是否在全部已抽取的整數(shù)中出現(xiàn)，如出現(xiàn)返回 1，否則返回 0。
由于僅有數(shù)字 7 出現(xiàn)在 $B$1:B2 中，B2 單元格目前是 7 , 所以 COUNTIF 數(shù)組運(yùn)算后返回?cái)?shù)組：
{ 1 ; 2 ; 3 ; 4 ; 5 ; 6 ; 7 ; 8 ; 9 ; 10 }
{ 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 1 ; 0 ; 0 ; 0 }
② IF(①,"",ROW($1:$10))
如下圖，將①中結(jié)果數(shù)組 {0;0;0;0;0;0;1;0;0;0} 作為邏輯判斷值，1 等價(jià)于 TRUE，0 等價(jià)于 FALSE。
IF 函數(shù)根據(jù)邏輯判斷值，TRUE 返回空 ""，F(xiàn)ALSE 返回對(duì)應(yīng)的數(shù)字。
于是整個(gè)公式片段②就完成了將有序數(shù)組 {1;2;3;4;5;6;7;8;9;10} 中在 $B$1:B2 已出現(xiàn)的數(shù)字替換為空，實(shí)現(xiàn)逐次消除。
將 B2 單元格中的 7 替換為空 ""，這樣后續(xù)隨機(jī)取數(shù)不會(huì)抽到前面所出現(xiàn)的數(shù)值。
{ 1 ; 2 ; 3 ; 4 ; 5 ; 6 ; "" ; 8 ; 9 ; 10 }
③ {SMALL(②,RANDBETWEEN(1,12-ROW()))}
這一步的目的主要是隨機(jī)取數(shù)。
SMALL 函數(shù)主要用來(lái)提取數(shù)組中第 k 個(gè)最小值的數(shù)值，忽略空值。
RANDBETWEEN 則負(fù)責(zé)在指定范圍內(nèi)，隨機(jī)抽取一個(gè)值作為 k。
為確保每個(gè)數(shù)被抽取的概率一致且不出錯(cuò)，k 的最小值（即 Bottom）必須等于 1，最大值（即 TOP）必須始終與②結(jié)果數(shù)組中的數(shù)字個(gè)數(shù)一致。
公式中，用 12-ROW() 作為 TOP 的值。ROW () 用于返回當(dāng)前單元格的行數(shù)，在 B2 時(shí)，12-ROW () 為 10，逐行遞減 1，B3 為 9，剛好始終與②{ 1 ; 2 ; 3 ; 4 ; 5 ; 6 ; "" ; 8 ; 9 ; 10 } 中剩余可抽取的數(shù)字個(gè)數(shù)一致。
RANDBETWEEN 函數(shù)在 1 和剩余個(gè)數(shù) 9 之間隨機(jī)取數(shù) k，再使用 SMALL 函數(shù)來(lái)取對(duì)應(yīng)第 k 小的數(shù)字 m，最終再獲得一個(gè)不重復(fù)隨機(jī)數(shù)，以此類(lèi)推，即可得到一組不重復(fù)隨機(jī)整數(shù)。
掌握了隨機(jī)不重復(fù)整數(shù)公式后，只需稍加變形，我們就可以借助 INDEX 函數(shù)實(shí)現(xiàn)隨機(jī)抽獎(jiǎng)等高端操作。下圖就是利用逐次消除法隨機(jī)抽取 5 名獲獎(jiǎng)人員的案例。
D3—— 隨機(jī)抽獎(jiǎng)公式：
{=INDEX($A$2:$A$11,SMALL(IF(COUNTIF($D$1:D2,$A$2:$A$11),"",ROW($1:$10)),RANDBETWEEN(1,12-ROW())))}
3、亂序取余法
如果能夠?qū)⒅付ǚ秶鷥?nèi)的數(shù)字隨機(jī)打亂，那么只需按打亂后的順序依次抽取數(shù)字就能夠得到一組不重復(fù)隨機(jī)數(shù)。
下面這個(gè)公式就是將有序數(shù)組加上 10 的任意倍數(shù)來(lái)打亂數(shù)字的大小次序，再使用 LARGE 或 SMALL 函數(shù)來(lái)依次取值，取余，即最終生成隨機(jī)不重復(fù)整數(shù)。
B2:B11 數(shù)組公式 —— 亂序取余法：
{=MOD( LARGE( RANDBETWEEN(ROW(1:10)^0,10)*100+ROW(1:10), ROW(1:10)),100)}
PS：該公式為數(shù)組公式，其輸出結(jié)果為一組數(shù)，需占用 B2:B11 單元格才能正確顯示。
因此，需先選擇 B2:B11 單元格，輸入公式后，再按【Ctrl+Shift+Enter】。不能像逐次消除法公式那樣在單個(gè)單元格里按【Ctrl+Shift+Enter】執(zhí)行數(shù)組運(yùn)算后再拖動(dòng)填充，切記！
下面進(jìn)行簡(jiǎn)單的公式解釋。
公式說(shuō)明：
ROW (1:10)^0 將 1 到 10 的有序數(shù)組 A {1;2;3;4;5;6;7;8;9;10} 取其 0 次冪，得到 10 個(gè) 1 組成的常數(shù)數(shù)組 B {1;1;1;1;1;1;1;1;1;1}。
PS : 在數(shù)學(xué)中，任何非 0 的 0 次冪，結(jié)果都為 1 。
RANDBETWEEN(ROW(1:10)^0,10)
使用 RANDBETWEEN 函數(shù)來(lái)生成 10 個(gè)彼此獨(dú)立的從 1 到 10 的隨機(jī)整數(shù)（可以重復(fù)），得到隨機(jī)數(shù)組 C {10;6;3;8;4;2;8;5;4;7}。
數(shù)組 C*100+ROW (1:10) 將數(shù)組 C 擴(kuò)大 100 倍再加上有序數(shù)組 A，得到 (不重復(fù)) 數(shù)組 D {1001;602;303;804;405;206;807;508;409;710}。
數(shù)組 D 的特點(diǎn)是其尾數(shù)依次是有序數(shù)組 A {1;2;3;4;5;6;7;8;9;10}，但其大小卻由位于百位的隨機(jī)數(shù)組隨機(jī)數(shù)組 C {10;6;3;8;4;2;8;5;4;7} 決定，這就實(shí)現(xiàn)了將有序數(shù)組 A 隨機(jī)打亂。
{=MOD( LARGE( RANDBETWEEN(ROW(1:10)^0,10)*100+ROW(1:10), ROW(1:10)),100)}通過(guò) LARGE 函數(shù)依次取最大的第 1 到第 10 的數(shù)字，再取 100 的余數(shù)，就可以將數(shù)組 A {1;2;3;4;5;6;7;8;9;10} 中的每個(gè)數(shù)字重新剝離出來(lái)，
但此時(shí)他們的次序已經(jīng)按隨機(jī)數(shù)組 C 的大小進(jìn)行了重排，得到公式計(jì)算結(jié)果數(shù)組 E {1;7;4;10;2;8;9;5;3;6}，數(shù)組運(yùn)算將結(jié)果數(shù)組依次填列在 B2:B11 單元格中。
運(yùn)用亂序取余法公式，可以輕松解決隨機(jī)分組問(wèn)題。
我們來(lái)簡(jiǎn)單看看一個(gè)基礎(chǔ)案例。
如下圖，將一組姓名隨機(jī)分為 2 組。
B2:B11 數(shù)組公式 —— 亂序取余法：
{=MOD( MOD( LARGE(RANDBETWEEN(ROW(1:10)^0,10)*100+ROW(1:10),ROW(1:10)) ,100), 2)+1&"組"}
學(xué)會(huì)這三種方法，不重復(fù)隨機(jī)取值的做法就難不倒大家啦~
4、最后的話
本文小花分享的獲取隨機(jī)不重復(fù)整數(shù)的三個(gè)函數(shù)公式，包括：
? 輔助排名法：
通過(guò) RAND 構(gòu)建輔助列，再使用 RANK 進(jìn)行排名；
? 逐次消除法：
使用 IF+COUNTIF 消除已出現(xiàn)值，在用 SMALL 隨機(jī)取值；
? 亂序取余法：
構(gòu)建隨機(jī)數(shù)組 * 100 + 有序數(shù)組，用 LARGE 進(jìn)行排序后再由 MOD 取余數(shù)；
大家學(xué)會(huì)了嗎？這下平時(shí)的抽獎(jiǎng)和團(tuán)建隨機(jī)組隊(duì)不用愁了吧！
本文來(lái)自微信公眾號(hào)：秋葉 Excel （ID：excel100），作者：小花
廣告聲明：文內(nèi)含有的對(duì)外跳轉(zhuǎn)鏈接（包括不限于超鏈接、二維碼、口令等形式），用于傳遞更多信息，節(jié)省甄選時(shí)間，結(jié)果僅供參考，IT之家所有文章均包含本聲明。