fbpx

Excel 小教室 – 如何隨機且「平均」分配項目,比如人員分組

2019/04/24 電腦小教室

今天阿湯又要來解答網友的問題了,給我的這個題目倒是有點難度,問題是這樣的:我想要將人員平均分配組別,且用隨機的方式來做。其實看起來好像很簡單,但其實頗難的,如果單純只是「隨機」分配很簡單,但如果同時要兼顧「平均」有點難度,阿湯思維了幾種做法後,想了一個盡可能簡單的做法給大家參考,用到的函數也都是常見且易用的,以便大家能夠理解,往下來看怎麼做吧。

透過 Excel 隨機平均分配組別

先給大家看一下要做的東西長這樣,假設人員有 A 到 O 共 15 位,我想要將他們平均分配到甲、乙、丙三個組別裡,等於每組是 5 人的話要怎麼做。

阿湯本來想用一行函數來全部搞定,但怕大家無法理解內容(很複雜),所以改用輔助的方式來做。

右邊的 E 列先打入組別及名稱,這邊我以甲乙丙來示範。

F 行是輔助用的隨機碼,這一列長度會和人員長度一樣,函式使用的是 RAND,直接在 F2 輸入

=RAND()

然後在 F2 將函數往下延伸,也就是 F2 到 F16 都是 RAND()。

接下來比較複雜的公式來了,我們在 C2 那欄輸入以下公式:

=INDIRECT("E"&((MOD(RANK(F2,$F$2:$F$16),3)+1)+1))

然後一樣從 C2 延伸公式到 C16(就是滑鼠移到 C2 欄位右下角會出現十字,按住後往下拖曳到 C16 即可)。

接下來說明一下公式,我們先講一下最裡面的 RANK 這一段

=RANK(F2,$F$2:$F$16)

這是將 F2~F16 排名出來 1~15 名,所以單純打這一段會出現 1~15 的數字(大家可以試試)。

然後再透過 MOD 這個函數來處理排名

=MOD(RANK(F2,$F$2:$F$16),3)+1

如果是打這一段,你會發現會顯示的就是 1~3 的數字,如果你的組別是有五個,那其中那個 3 就改成 5 就可以。

MOD 這是用來顯示餘數使用的,詳細用法可以參考 OFFICE 網站,透過這個函數會以 1~15 來說,除以 3 後會得到的餘數會是 0~2,所以在公式的尾數多一個 +1 來補成 1~3。

如果你不需要甲、乙、丙的組別名稱的話,其實到這個步驟就搞定了,就已經區分出 1~3 的隨機平均分配。

最後如果你想要直接將 E 列的甲、乙、丙直接帶入的話,最終公式就是長這樣

=INDIRECT("E"&((MOD(RANK(F2,$F$2:$F$16),3)+1)+1))

或者

=INDIRECT("E"&(MOD(RANK(F2,$F$2:$F$16),3)+2))

INDIRECT 這個公式是用來顯示欄位值,比如 INDIRECT(E2),那就會顯示 E2 的值,也就是甲,我們剛剛 C 列做到 MOD 為止是顯示 1~3,但我們要指向的內容是 E2~E4,所以在公式尾把多加了個 1,或者將裡面的 +1 改成 +2 都可以,這樣就會顯示 E 列的組別名稱了。

如果你想要驗證每個組別的人數是否有平均的話,可以用 COUNTIF 這個函數來做,這部份阿湯就不特別解說了,COUNTIF 是非常簡易使用的一個函數。

最後完整的含驗證就長這樣了。

如果你要分配的量是 15,但要分到四組,會無法整除也可以用嗎?是的,也可以,會像這樣,某一組別少一個人。

由於阿湯是用 RAND 這個函數來做隨機取數,所以當你隨便輸入一格後,排列都會重新來過,因此,分完組別後要固定下來的話,可以在組別的項目複製起來。

然後在組別的第一欄位右鍵「選擇性貼上」。

選擇值按下確定。

這樣就會覆蓋原本的公式,變成單純的文字,就不會再變動了,後面的 DEF 列也都可以清空了。

文末說明一下為什麼要用 RAND() 來做隨機碼排列,因為 RAND() 產生的隨機值重複機率非常非常的低,當然如果你的樣本有 1 萬組的話,那可能有一點點的機會重複,但原則上很難,會用這方式來做的原因是,如果你是要隨機且不重複產生 1~15 的數字,公式其實也不是簡單就搞定,不如就用 RAND(),反正有加入 COUNTIF 驗證每組人數是否平均就好了,不用那麼麻煩。

提供給大家這篇文章的範例檔:請點我下載

你可能對這些內容也有興趣

阿湯 作者:阿湯

對電腦 & 網路資訊充滿興趣與熱情、瘋了似的每日堅持發文,「阿湯」這個名字是由湯姆克魯斯而來的,雖然我沒有他帥氣,也不會演電影,但我會寫文章....

更多關於的文章》

0 留言

發表留言 »

姓名(*)
信箱(*)