年前我們發(fā)布過一篇文章《我用 Excel 做了一個(gè)年會(huì)抽獎(jiǎng)器，然后老板點(diǎn)了一下……》，這其中的核心是獲取不重復(fù)隨機(jī)整數(shù)。

獲取不重復(fù)隨機(jī)整數(shù)的方法有很多，迭代計(jì)算太過雞肋，VBA 編程未免牛刀殺雞。

今天，小花就跟大家分享幾個(gè)實(shí)用的函數(shù)公式，超贊的那種哦！

輔助排名法

RAND 函數(shù)的作用是生成 0-1 之間的隨機(jī)數(shù)，于是我們只需通過 RAND 函數(shù)生成一組隨機(jī)數(shù)，再將它們進(jìn)行排名，就有極大概率可以得到一組不重復(fù)隨機(jī)整數(shù)。

C 列——輔助列公式：

=RAND()

B2——不重復(fù)隨機(jī)整數(shù)公式：

=RANK(C2,$C$2:$C$11)

公式說明：

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ù)中最常見的一種方法。

它比較簡單，容易學(xué)會(huì)。

不過這個(gè)公式有個(gè)致命 Bug 是，每一個(gè) RAND 函數(shù)生成的隨機(jī)數(shù)都是獨(dú)立的，也就是說，這些隨機(jī)數(shù)還是有極小的概率彼此相等，進(jìn)而出現(xiàn)重復(fù)的排名值。如下圖：

RAND 函數(shù)輔助法并不嚴(yán)謹(jǐn)，且需要輔助列，因此只能算是不重復(fù)隨機(jī)整數(shù)的入門級(jí)解法。

接下來，小花來介紹兩種比較嚴(yán)謹(jǐn)?shù)姆椒?，可能比較難 ，大家重在理解解決的思路，干貨滿滿，一定要堅(jiān)持看下去哦！

逐次消除法

獲取不重復(fù)隨機(jī)整數(shù)的核心問題是：如何讓已經(jīng)出現(xiàn)的數(shù)字不再出現(xiàn)？

解決思路：把出現(xiàn)的數(shù)字從抽取數(shù)字池中消除掉，然后再從剩余數(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 單元格公式說明：

① COUNTIF($B$1:B2,ROW($1:$10))

這一步的主要目的是：通過 Countif 函數(shù)進(jìn)行數(shù)值計(jì)數(shù)， 進(jìn)而判斷當(dāng)前單元格的上一單元格數(shù)值是否有在 1 到 10 中出現(xiàn)過?。

當(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ù)主要用來提取數(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ù)來取對(duì)應(yīng)第 k 小的數(shù)字 m，最終再獲得一個(gè)不重復(fù)隨機(jī)數(shù)，以此類推，即可得到一組不重復(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())))}

亂序取余法

如果能夠?qū)⒅付ǚ秶鷥?nèi)的數(shù)字隨機(jī)打亂，那么只需按打亂后的順序依次抽取數(shù)字就能夠得到一組不重復(fù)隨機(jī)數(shù)。

下面這個(gè)公式就是將有序數(shù)組加上 10 的任意倍數(shù)來打亂數(shù)字的大小次序，再使用 LARGE 或 SMALL 函數(shù)來依次取值，取余，即最終生成隨機(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)行簡單的公式解釋。

公式說明：

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ù)來生成 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)}

通過 LARGE 函數(shù)依次取最大的第 1 到第 10 的數(shù)字，再取 100 的余數(shù)，就可以將數(shù)組 A{1;2;3;4;5;6;7;8;9;10}中的每個(gè)數(shù)字重新剝離出來，

但此時(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ī)分組問題。

我們來簡單看看一個(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ī)取值的做法就難不倒大家啦~

最后的話

本文小花分享的獲取隨機(jī)不重復(fù)整數(shù)的三個(gè)函數(shù)公式，包括：

? 輔助排名法：

通過 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ì)不用愁了吧！

大家還遇到隨機(jī)不重復(fù)整數(shù)的哪些應(yīng)用場景或問題，歡迎留言與我們交流！順便拿上源文件學(xué)起來哦~

如果你想系統(tǒng)性學(xué)習(xí) Excel，掌握更多 Excel 技能。

正好，我們《秋葉 Excel 3 天集訓(xùn)營》專為職場人準(zhǔn)備，全部基于職場真實(shí)表格案例設(shè)計(jì)，還有很多超實(shí)用 Excel 技巧教學(xué)。

每天學(xué)習(xí)大概?30 分鐘，從日常的功能出發(fā)，全程演示，一課一練，夯實(shí)進(jìn)階每一步。

秋葉 Excel 3 天集訓(xùn)營

原價(jià)?99 元??

現(xiàn)在?只需 1?元?

每天學(xué)習(xí)?30 分鐘

你也有可能成為 Excel 高手！

現(xiàn)在就掃碼報(bào)名吧！

??????