點(diǎn)擊藍(lán)字關(guān)注【秋葉AIExcel】

發(fā)送【7】

免費(fèi)領(lǐng) 1000+篇 Excel 精選教程！

本文作者：小花

本文編輯：竺蘭

文末獲取配套練習(xí)文件。

大家好，這里是秋葉編輯部~

在各種活動(dòng)中，比如每年的公司年會(huì)，抽獎(jiǎng)環(huán)節(jié)總是備受期待，能為活動(dòng)增添不少歡樂氛圍。

而在 Excel 中，我們可以借助強(qiáng)大的函數(shù)功能實(shí)現(xiàn)多種抽獎(jiǎng)需求。

今天，咱們就結(jié)合實(shí)戰(zhàn)案例，來詳細(xì)講講 5 種常見的抽獎(jiǎng)模式及其公式，讓你輕松掌握抽獎(jiǎng)的奧秘！

隨機(jī)抽取一人

這是最簡單的隨機(jī)抽獎(jiǎng)模式，僅返回單一值。

它只需使用Index+Randbetween函數(shù)組合就可以輕松搞定。

公式一：單值抽取

=INDEX(A2:A16,RANDBETWEEN(1,COUNTA(A2:A16)))

公式說明：

Counta 統(tǒng)計(jì)參與抽獎(jiǎng)的總?cè)藬?shù) n，Randbetween 生成 1 到 n 之間的一個(gè)隨機(jī)整數(shù)，Index 根據(jù)隨機(jī)整數(shù)從人員名單區(qū)域 A2:A16 中提取對(duì)應(yīng)的人員姓名，這樣就得到了隨機(jī)抽取的幸運(yùn)兒啦。

對(duì)了，如果你想由淺入深、系統(tǒng)學(xué)習(xí) Excel 干貨知識(shí)，提高效率、減輕工作負(fù)擔(dān)，

那我強(qiáng)烈推薦你報(bào)名《秋葉 Excel 高手速成實(shí)戰(zhàn)課》！

名師授課+系統(tǒng)教學(xué)+配套練習(xí)+社群答疑

長期有效，可反復(fù)回看

掃碼查看課程詳情

隨機(jī)抽取不重復(fù)多人

隨機(jī)抽取不重復(fù)多人曾是 Excel 函數(shù)公式的一座高山，直到Excel 2021 版及 365 版引入了動(dòng)態(tài)數(shù)組功能并新增了一些高能函數(shù)后，解決這一問題就變得輕而易舉了。

公式二：抽取不重復(fù)多值

=TAKE(SORTBY(A2:A16,RANDARRAY(15)),3)

公式說明：

① RANDARRAY(15)

返回一組 15 行的隨機(jī)數(shù)，其中的 Randarray 是專門的隨機(jī)數(shù)組函數(shù)，其用法如下：

② SORTBY(A2:A16,RANDARRAY(①))

根據(jù)①中隨機(jī)數(shù)的大小，對(duì) A2:A16 進(jìn)行排序，從而實(shí)現(xiàn)對(duì)抽獎(jiǎng)名單的隨機(jī)打亂排序。其中的 Sortby 函數(shù)是專門的條件排序函數(shù)，其用法如下：

③ TAKE(②,3)

從②中已亂序排列的獲獎(jiǎng)名單中取前 3 行數(shù)據(jù)為最終獲獎(jiǎng)清單，這里用到了專門用于截取數(shù)據(jù)區(qū)域的 Take 函數(shù)，其用法如下：

各位小伙伴務(wù)要理解和掌握Take+Sortby+Randarray函數(shù)的這套組合拳，后面三種抽獎(jiǎng)模式都將建立在這個(gè)組合公式的基礎(chǔ)之上，或稍加變形，或部分運(yùn)用。

均等分組抽獎(jiǎng)

假設(shè)獎(jiǎng)項(xiàng)分為 3 個(gè)等級(jí)，每個(gè)等級(jí) 4 人，需要在 15 人的大名單中一次性抽取出所有獲獎(jiǎng)人員，這就是分組抽獎(jiǎng)模式。

它實(shí)際上是隨機(jī)抽取多值和分組排列兩個(gè)運(yùn)算的組合，前者依舊使用 Take+Sortby 和 Randarray 的組合公式，后者則需要引入另一個(gè)新函數(shù) Wrapcols 來實(shí)現(xiàn)。

公式三：均等分組抽獎(jiǎng)公式

=WRAPCOLS(TAKE(SORTBY(A2:A16,RANDARRAY(15)),12),3)

公式說明：

① TAKE(SORTBY(A2:A16,RANDARRAY(15)),12)

抽取不重復(fù)多值公式，原理詳見上一公式，不再贅述。

② =WRAPCOLS(①,3)

將①中已隨機(jī)抽取的獲獎(jiǎng)名單分成 3 行排列，這是 Wrapcols 函數(shù)的基本功能，其用法如下：

如果每個(gè)獎(jiǎng)項(xiàng)的人數(shù)不同，又該如何設(shè)置公式？

很簡單，只需將不顯示人員姓名的單元格數(shù)字格式設(shè)置為三個(gè)分號(hào)【;;;】，即可實(shí)現(xiàn)隱藏。

二次抽獎(jiǎng)

如果我們需要分次抽獎(jiǎng)，且已中獎(jiǎng)人員不再參與抽獎(jiǎng)，該怎么設(shè)置公式呢？

核心要點(diǎn)是去除已中獎(jiǎng)的重復(fù)人員名單，這正好是 Unique 函數(shù)的拿手好戲。

公式四：二次抽獎(jiǎng)公式

=TAKE(UNIQUE(VSTACK(E2:E5,SORTBY(A2:A16,RANDARRAY(15)))),-3)

公式說明：

① SORTBY(A2:A16,RANDARRAY(15))

隨機(jī)亂序排列。

② VSTACK(E2:E5,①)

將亂序排列的大名單追加到已獲獎(jiǎng)名單之后，形成部分值重復(fù)的新數(shù)組。其中， Vstack 函數(shù)專門用于將不同數(shù)組進(jìn)行組合，其用法如下：

③ UNIQUE(②)

將②返回的部分值重復(fù)數(shù)組去重，由于已中獎(jiǎng)名單 E2:E5 在前，A2:A16 在后，根據(jù) Unique 去重保留第一個(gè)不重復(fù)值的原理，A2:A16 中的已中獎(jiǎng)人員被去除，新數(shù)組末尾為未中獎(jiǎng)名單。

其中， Unique 函數(shù)用法如下：

④ TAKE(③,-3)

從③返回的不重復(fù)數(shù)組末尾提取 3 個(gè)數(shù)值，即為最終獲獎(jiǎng)名單。

加權(quán)抽獎(jiǎng)

如果每個(gè)人中獎(jiǎng)的概率不同，該怎么設(shè)置公式才能根據(jù)獲獎(jiǎng)概率公平抽獎(jiǎng)呢？我們用標(biāo)準(zhǔn)概率的倍數(shù)來表示不同的中獎(jiǎng)概率。

如下圖，胡勇軍的概率因子為 5，陳靜文的概率因子為 1，則表示胡勇軍的中獎(jiǎng)概率是陳靜文的 5 倍。

公式五：加權(quán)抽獎(jiǎng)公式

=TAKE(UNIQUE(SORTBY(TOCOL(IF(B2:B16>=COLUMN(A:E),A2:A16,0/0),2),RANDARRAY(SUM(B2:B16)))),3)

公式說明：

① IF(B2:B16>=COLUMN(A:E),A2:A16,0/0)

這是一個(gè)數(shù)組運(yùn)算。由于概率因子最大為 5，所以僅返回 Column(A:E)5 個(gè)列序值。

將概率因子 B2:B16 和列序 1-5 分別比對(duì)，概率因子大于或等于列序則返回對(duì)應(yīng)人名，不大于則返回錯(cuò)誤值/0!，于是乎，片段①返回一個(gè) 15 行×5 列的數(shù)據(jù)區(qū)域，使每個(gè)人名都按照其概率因子的大小被重復(fù)列示對(duì)應(yīng)次數(shù)。

我們單獨(dú)計(jì)算這一片段就可以理解其計(jì)算過程。

② TOCOL(①,2)

將①返回的二維數(shù)據(jù)表合并為一列，第二個(gè)參數(shù) 2 表示忽略錯(cuò)誤，這樣二維表中的/0!就被忽略掉了，僅保留按概率因子指定次數(shù)重復(fù)的人員名單，重復(fù)次數(shù)越高，獲獎(jiǎng)概率越大。

該公式運(yùn)用 Tocol 函數(shù)，其用法如下：

③ TAKE(UNIQUE(SORTBY(②,RANDARRAY(SUM(B2:B16)))),3)

Sum(B2:B16)將所有概率因子相加，Randarray 返回對(duì)應(yīng)數(shù)量的隨機(jī)數(shù)組，從而確保②中的重復(fù)人員名單能夠被 Sortby 函數(shù)完整打亂次序排列，再搭配 Unique 去重和 TAKE 截取，就實(shí)現(xiàn)了按概率因子加權(quán)抽獎(jiǎng)。

以上，就是小花分享的5 種抽獎(jiǎng)模式公式，包括：

? Index+Randbetween 的單值抽獎(jiǎng)公式；

? Take+ Sortby + Randarray 的多值抽獎(jiǎng)核心套路；

? 引入Wrapcols 實(shí)現(xiàn)分組抽獎(jiǎng)功能；

? UNIQUE+VSTACK 構(gòu)建的去重二次抽獎(jiǎng)模式；

? TOCOL+IF 區(qū)域計(jì)算從而做到加權(quán)下的隨機(jī)抽獎(jiǎng)。

這 5 個(gè)公式循序漸進(jìn)地引入了不同的新函數(shù)，這不僅有助于我們逐步理解復(fù)雜的抽獎(jiǎng)公式，還能從中學(xué)習(xí)不同新函數(shù)的用法，一舉兩得。

Excel 里實(shí)用的函數(shù)有很多很多，學(xué)會(huì)了，就可能將你的效率提高 10 倍都不止！

去哪學(xué)？

推薦《秋葉 Excel 高手速成實(shí)戰(zhàn)課》，這門課專為職場(chǎng)人準(zhǔn)備，包含 231節(jié)實(shí)戰(zhàn)教程，全部基于職場(chǎng)真實(shí)表格案例設(shè)計(jì)。

系統(tǒng)全面帶你掌握Excel 系統(tǒng)操作、商務(wù)圖表、函數(shù)公式、數(shù)據(jù)透視表、高效技巧，一站式學(xué)透 Excel 表格！

《秋葉 Excel 高手速成實(shí)戰(zhàn)課》

課程原價(jià) 999 元

限時(shí)優(yōu)惠價(jià)，僅需 99 元

名師授課+系統(tǒng)教學(xué)+配套練習(xí)+社群答疑

長期有效，可反復(fù)回看

別猶豫了！趕緊掃碼搶課

現(xiàn)在報(bào)名，隨課贈(zèng)送豐厚資料：

900+ 套精選 Excel 模板

307 個(gè)函數(shù)清單

100 例圖表實(shí)戰(zhàn)案例

70 個(gè)實(shí)用圖表模板

關(guān)注后發(fā)送【999】

即可獲取練習(xí)文件！