編按：

如何同時(shí)滿(mǎn)足多次隨機(jī)抽取、每次不重復(fù)值這兩種條件呢？需要自動(dòng)排除已抽取的，然后對(duì)還沒(méi)有抽過(guò)的數(shù)據(jù)賦予不同的數(shù)字，最后在這些數(shù)字中隨機(jī)抽取。一起來(lái)看看吧！

公司近期由于生產(chǎn)任務(wù)緊張，需要每周隨機(jī)抽調(diào)業(yè)務(wù)部門(mén)的一名業(yè)務(wù)員去支援生產(chǎn)，對(duì)于已經(jīng)抽調(diào)過(guò)的就不再重復(fù)抽調(diào)直到下輪開(kāi)始。那么如何來(lái)實(shí)現(xiàn)這樣的需求呢？

要注意兩個(gè)特別點(diǎn)：

（1）會(huì)進(jìn)行多次隨機(jī)抽取，而不是一次抽取

（2）每次隨機(jī)抽取前都要排除已抽取的人員，避免前后抽取中出現(xiàn)重復(fù)

也就是說(shuō)，必須在多次進(jìn)行的隨機(jī)抽取中自動(dòng)排除已抽值確保前后每次都不重復(fù)。當(dāng)前網(wǎng)上絕大多數(shù)的隨機(jī)抽取教程是辦不到的。

我們的解決思路就是只為每位還沒(méi)有抽過(guò)的人員生成一個(gè)動(dòng)態(tài)數(shù)字，然后在這些數(shù)字中隨機(jī)抽取。

1.添加動(dòng)態(tài)序號(hào)

被抽到的員工將記錄到A15:C24區(qū)域（區(qū)域行數(shù)等于員工總數(shù)），我們只為沒(méi)有抽到的人員添加序號(hào)。

定位到A2單元格輸入公式“=IF(OR(C2=$C$15:$C$24),"",COUNT($A$1:A1)+1)”，接著按CTRL+SHIFT+ENTER組合鍵完成數(shù)組公式的輸入，然后下拉填充。

公式解釋?zhuān)?/span>

IF函數(shù)判斷值，先使用OR數(shù)組函數(shù)對(duì)“C2=$C$15:$C$24”判斷，如果C2出現(xiàn)在C15:C24（即已經(jīng)抽調(diào)過(guò)），那么序號(hào)就顯示為空；否則將“COUNT($A$1:A1)”計(jì)數(shù)結(jié)果加1后作為序號(hào)。此公式自動(dòng)排除了已抽取的人員，確保不管隨機(jī)抽取多少次，前后抽取的都不會(huì)重復(fù)。

2.計(jì)算周次

定位到D2單元格輸入公式“="第"&WEEKNUM(TODAY())&"周"”，計(jì)算當(dāng)天所屬周次。這樣每周一打開(kāi)文檔后可以自動(dòng)識(shí)別周次。

3.隨機(jī)抽取

定位到E2單元格輸入公式“=IFERROR(VLOOKUP(RANDBETWEEN(1,MAX($A$2:$A$11)),$A$2:$C$11,COLUMN(B1),0),"已抽完")”。然后右拉填充公式。

公式解釋?zhuān)?/span>

使用“RANDBETWEEN(1,MAX(A2:A11))”生成一個(gè)隨機(jī)數(shù)作為VLOOKUP的查找條件，最小值是1，最大值是序號(hào)列最大序號(hào)值。由于RANDBETWEEN是隨機(jī)函數(shù)，按下F9鍵就會(huì)變化，從而實(shí)現(xiàn)了隨機(jī)抽取。

最后在外層嵌套IFERROR函數(shù)，當(dāng)員工全部被抽取后，RANDBETWEEN(1,MAX(A2:A11))會(huì)出現(xiàn)錯(cuò)誤，此時(shí)強(qiáng)制顯示為“已抽完”。

4.自動(dòng)記錄隨機(jī)值

為了方便記錄每次抽取的隨機(jī)值，我們使用宏來(lái)自動(dòng)復(fù)制。點(diǎn)擊“開(kāi)發(fā)工具→錄制宏”，按提示新建一個(gè)名為“復(fù)制記錄”的宏，并設(shè)置快捷鍵如Ctrl+X。

確定后執(zhí)行下面的操作：

1）選中D2:F2

2）按Ctrl+C復(fù)制

3）選中A15

4）點(diǎn)擊“開(kāi)始→粘貼→選擇性粘貼→數(shù)值”，將D2:F2粘貼為值

5）按Esc取消鍵取消拷貝模式。

點(diǎn)擊“停止錄制”按鈕。

按ALT+F11打開(kāi)VBA編輯，可以看到錄制好的宏代碼如下。

剛才的5個(gè)操作都記錄在代碼中，見(jiàn)圖中紅色框內(nèi)。

這時(shí)，如果我們按F9隨機(jī)抽取人員，再按Ctrl+X記錄，始終只能在A15:C15處記錄一條信息。

因此需要修改一下代碼：

用“Range("a65536").End(xlUp).Offset(1, 0).Select”覆蓋“Range("A15").Select”。

并加一句Range("D2:F2").Select，讓每次粘貼后鼠標(biāo)都回到D2:F2處。

到此，主要工作完成！剩下的是設(shè)置顏色提醒。

5.顏色標(biāo)記已抽取人員和已抽完

選擇E2:F2，點(diǎn)擊“開(kāi)始→條件格式→使用公式確定要設(shè)置格式的單元格”，輸入公式“=E2="已抽完"”，單元格填充為棕色。

同上，選中A2:C11,公式“=A2=""”，顏色填充為黃色，標(biāo)記已抽取人員。

6.實(shí)際使用

每周一打開(kāi)文件，長(zhǎng)按F9抽取人員，然后按Ctrl+X記錄抽取結(jié)果。

當(dāng)A2:C11區(qū)域顏色全變?yōu)辄S色，E2:F2變?yōu)樽厣?，表示“已抽完”。此時(shí)刪除A15:C24的數(shù)據(jù)，又可以開(kāi)始抽取了。大家可以舉一反三，可以每次抽取多名人員，也可以將上述例子變?yōu)槎啻纬楠?jiǎng)的隨機(jī)抽獎(jiǎng)工具。

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車(chē)》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

提取不重復(fù)值并統(tǒng)計(jì)數(shù)量的三個(gè)方法，一秒完成！

如何提取唯一值？試試TEXTJOIN函數(shù)搭配VBA自定義！

Excel教程：如何制作帶有層次和透視感的圖表？

八大查找函數(shù)公式，輕松搞定數(shù)據(jù)中的多條件查找

版權(quán)申明：

本文作者ITFANS；同時(shí)部落窩教育享有專(zhuān)有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。