excel 教學：如何產生不重複的隨機整數亂數 (RAND、RANK 與 COUNTIF 函數的應用)

在使用 Excel 進行資料處理或需要隨機選取項目時，我們常常需要產生亂數。然而，Excel 中常用的 RANDBETWEEN 函數雖然可以產生指定範圍內的整數亂數，但有個常見的問題：它產生的數字可能會重複！

例如，您想從 1 到 50 的員工編號中，隨機抽出 10 位進行表揚。如果直接使用 RANDBETWEEN(1,50) 產生 10 個數字，很有可能會抽到重複的員工編號，這樣就無法達到「不重複」的目的。

那麼，要如何在 Excel 中產生一系列不重複的隨機整數呢？這裡提供兩種方法，主要利用 RAND 和 RANK 函數的特性，或是結合 RANDBETWEEN 和 COUNTIF 來解決重複問題。

方法一：利用 RAND 函數和 RANK 函數 (適用於產生指定範圍 1 到 N 的不重複亂數)

RAND() 函數會產生一個介於 0 到 1 之間（包含 0，不包含 1）的隨機小數。由於隨機小數的精確度非常高，在一般情況下，連續產生的 RAND() 值幾乎不會重複。

RANK() 函數則是用來計算一個數字在某個數字清單中的排名（順序）。如果我們對一組不重複的 RAND() 值進行排名，得到的排名結果就會是從 1 到該數字清單個數的連續整數，而且這些排名本身是不會重複的。

步驟說明：

1. 建立需要亂數選取的項目列表： 例如，在 A 欄輸入員工編號 1 到 50 (A2:A51)。

   • 在 A2 輸入 1，A3 輸入 2。

   • 選取 A2 和 A3，將滑鼠移至 A3 右下角，當游標變成實心的十字時，按住滑鼠左鍵向下拖曳至 A51，即可產生 1 到 50 的編號。

2. 在 B 欄產生隨機小數： 在 B2 儲存格輸入公式 =RAND()。

   • =RAND() 函數不需要任何引數，直接輸入即可。

   • 將滑鼠移至 B2 右下角，雙擊滑鼠左鍵，即可將公式填充到 A 欄資料的最後一列 (B51)。

   • 您會看到 B 欄出現一串介於 0 到 1 之間的隨機小數。

3. 使用 RANK 函數對隨機小數進行排名： 在 C 欄計算 B 欄隨機小數的排名。排名的結果就是我們需要的不重複整數亂數。

   • 在 C2 儲存格輸入公式 =RANK(B2,$B$2:$B$51)

     • B2: 是要計算排名的數字 (第一個隨機小數)。

     • $B$2:$B$51: 是進行排名的範圍。請務必使用絕對參照 (按下 F4 鍵)， 這樣當公式向下填充時，這個範圍會固定不變。

     • RANK 函數預設是遞減排序 (數字越大，排名越靠前)，如果您需要遞增排序 (數字越小，排名越靠前)，可以在公式最後加上一個參數 1，例如 =RANK(B2,$B$2:$B$51,1)。對於產生不重複亂數本身，使用遞減或遞增排序的結果都是不重複的整數，只是順序不同。

   • 將滑鼠移至 C2 右下角，雙擊滑鼠左鍵，即可將公式填充到 C51。

   • 您會看到 C 欄出現從 1 到 50 的一系列整數，而且這些數字是不重複的！

應用範例 (抽獎)：

如果您需要從 50 位員工中抽出前 10 名，只需要查看 C 欄中排名為 1 到 10 的對應 A 欄員工編號即可。

重新產生亂數：

RAND() 函數的特性是每次工作表發生變動時，它都會重新計算。這意味著，如果您在工作表中進行任何編輯，B 欄和 C 欄的數字都會隨之改變。如果您想手動重新產生一組亂數，可以按下鍵盤上的 F9 鍵，這會強制 Excel 重新計算所有公式。每次按下 F9，您就會得到一組新的不重複亂數排名。

方法二：結合 RANDBETWEEN, RANK 和 COUNTIF (處理 RANDBETWEEN 產生的重複值並排名)

這個方法是針對如果您已經使用 RANDBETWEEN 產生了一組可能包含重複的整數亂數，並且需要一個機制來賦予它們不重複的排序。這個方法也可以應用於任何包含重複數值的清單，並給予它們唯一的排名。

步驟說明：

1. 在 B 欄使用 RANDBETWEEN 產生亂數： 在 B2 儲存格輸入公式 =RANDBETWEEN(1,50)。

   • 將公式填充到 B51。

   • 為了方便觀察重複值，您可以選取 B2:B51 範圍，使用「設定格式化的條件」>「醒目提示儲存格規則」>「重複值」，將重複的數字標示出來。您會發現很多數字是紅色的，表示有重複。

2. 在 C 欄使用結合公式產生不重複排名： 在 C2 儲存格輸入公式 =RANK(B2,$B$2:$B$51,1)+COUNTIF($B$2:B2,B2)-1

   • RANK(B2,$B$2:$B$51,1): 這部分計算 B2 數值在整個 B 欄範圍內的遞增排名。如果有多個相同的數字，它們會得到相同的排名（或排名的平均值，取決於 RANK 的具體實現版本）。我們使用參數 1 表示遞增排序。

   • COUNTIF($B$2:B2,B2): 這部分計算 B2 數值在範圍 $B$2:B2 中出現的次數。請注意 $B$2:B2 這個範圍的寫法：起點 $B$2 是絕對參照，終點 B2 是相對參照。當公式向下填充時，範圍會變成 $B$2:B3, $B$2:B4, ... $B$2:B51。所以 COUNTIF 會計算目前處理的 B 欄數字，從 B2 開始到當前列為止，出現了幾次。

   • -1: 由於 COUNTIF 對第一個出現的重複值會計算為 1，第二個為 2，以此類推。我們將 COUNTIF 結果減去 1，使得第一個重複值得到 0，第二個得到 1，第三個得到 2... 這個偏移量會加到 RANK 的結果上。

   • 結合效果： 假設數字 10 在 B 欄出現了三次，其 RANK 排名可能是 5 (如果其他數字比它小)。

     • 第一個 10 在 B5，COUNTIF($B$2:B5,B5)-1 得到 1-1=0。最終排名：5+0=5。

     • 第二個 10 在 B15，COUNTIF($B$2:B15,B15)-1 得到 2-1=1。最終排名：5+1=6。

     • 第三個 10 在 B30，COUNTIF($B$2:B30,B30)-1 得到 3-1=2。最終排名：5+2=7。

   • 這樣，原本相同的 RANK 排名會因為 COUNTIF 產生的偏移量而變得不重複。

   • 將公式填充到 C51。

   • 您會看到 C 欄出現一系列不重複的整數排名，從 1 到 50 (或與您 RANDBETWEEN 範圍相符的數量)。即使 B 欄有重複，C 欄的排名也是唯一的。

總結

• 如果您需要產生 1 到 N 的不重複隨機整數，最簡潔有效的方法是使用 RAND() 產生隨機小數，再用 RANK() 對這些小數進行排名。

• 如果您已經有了一組可能包含重複值的清單（例如使用 RANDBETWEEN 產生的），並且需要對其賦予不重複的順序，可以使用 RANK() 結合 COUNTIF() 來產生唯一排名。

這兩種方法都能有效地解決 Excel 中產生不重複隨機數的問題，您可以根據您的具體需求選擇合適的方法。