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