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

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

透過 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 驗證每組人數是否平均就好了，不用那麼麻煩。

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

想看更多 Office 教學嗎？全新的影音頻道 Office 知識家上線囉，快點擊圖片前往訂閱。