在Excel中生成隨機數的5種方法

並不是每個用戶都需要在 Excel 中使用隨機數。大多數人處理的是固定數字和公式，可能不需要在報告中出現隨機數。

然而，在對一組數據進行不同場景的模擬或執行各種統計分析時，隨機數生成器具有巨大的用途。

財務模型可能會使用依賴於概率的隨機模擬。該模型可能需要運行數千次，而隨機數生成器則為每次模擬提供參數。

無論您對隨機數的需求是什麼，Excel 都有多種生成方法。

在這篇文章中，我將向您展示所有可以用來在工作簿中插入隨機數的方法。

如何在 Microsoft Excel 中查找工作表名稱代碼

使用 RAND 函數生成隨機數

我要展示的第一種方法是 Excel 中生成隨機值最簡單的方法。

有一個非常簡單的 RAND 函數，它不需要參數，會生成一個介於 0 和 1 之間的隨機數。

RAND 函數的語法

= RAND ( )

此函數沒有必選或可選參數。輸入該函數時始終帶有一對空括號。

此函數將生成一個介於 0 和 1 之間的十進制隨機數，但不包括 0 或 1。

雖然可能出現重複值，但由於 RAND 函數是從連續範圍中產生數字，因此可能性極低。

返回的值將遵循均勻分佈。這意味著 0 到 1 之間的任何數字被返回的概率都是相等的。

在任意兩個數字之間生成隨機數

如果您需要 1 到 10 之間的數字，那麼 0 到 1 之間的十進制數可能不太好用。

但您可以使用一個涉及 RAND 函數的簡單公式，在任意兩個數字之間生成隨機數。

= RAND ( ) * ( Y - X ) + X

通常，您可以使用上述公式在 X 和 Y 之間創建一個隨機數。

= RAND ( ) * 9 + 1

例如，要生成 1 到 10 之間的數字，可以使用上述公式。

這會將生成的隨機數乘以 9，然後加 1。這將產生 1 到 10 之間的十進制數。

在任意兩個數字之間生成隨機整數

您可能遇到的另一個需求是在兩個給定數字之間生成隨機整數。這也可以通過一個簡單的公式來實現。

= ROUND ( RAND ( ) * ( Y - X ) + X, 0 )

通常，您可以使用上述公式在兩個值 X 和 Y 之間生成隨機整數。

= ROUND ( RAND ( ) * 9 + 1, 0 )

例如，上述公式將創建 1 到 10 之間的隨機整數。

這與之前的公式相同，但使用了 ROUND 函數將其四捨五入到零位小數。

您可以將此公式向下複製到電子表格的列中，如果您不斷按 F9 進行重新計算，您將看到 1 到 10 之間數字的各種組合。

由於可能的數字集是離散的，生成的隨機數很可能會在列表中重複，這取決於範圍的最小值和最大值。

= ROUND ( RAND ( ) * ( 4 - -3 ) + -3, 0 )

這也適用於生成負數。假設您需要在 -3 和 4 之間生成隨機整數，那麼上述公式就是您需要的。

將 RAND 函數乘以 7 將產生 0 到 7 之間的隨機數。在結果中加上 -3 並四捨五入到零位小數，這將得到 -3 到 4 的隨機數範圍。

使用 RANDBETWEEN 函數生成隨機數

Excel 有一個非常有用的函數，用於在上限和下限範圍內生成隨機數。

這比使用帶有運算符的 RAND 函數更易於使用，因為它直接通過參數確定特定範圍。

RANDBETWEEN 函數的語法

= RANDBETWEEN ( bottom, top )

• bottom 是返回值的下限。
• top 是返回值的上限。

這兩個參數都是必選的。

此函數將生成 bottom 和 top 值之間的隨機整數。該函數也會將上限和下限作為可能的返回值返回，因為它在此函數中並非「嚴格介於」兩者之間。

RANDBETWEEN 函數示例

= RANDBETWEEN ( -3, 4 )

例如，如果您想獲得 -3 到 4 之間的隨機數，如前一個示例所示，可以使用上述公式。

注意：RANDBETWEEN 函數只能生成整數。無法讓該函數生成十進制數。但是，它比使用 RAND 函數加運算符來實現相同結果要簡單得多。

使用 RANDARRAY 函數生成隨機數

通常情況下，您需要的不僅僅是一個隨機值，而是一整套隨機值。

RANDARRAY 函數是對此的完美解決方案。

它會用一組隨機數填充單元格區域，功能非常強大。

此函數僅在 Microsoft 365 版本的 Excel 中可用。

RANDARRAY 函數的語法

= RANDARRAY ( [rows], [columns], [min], [max], [whole_number] )

• Rows 是要返回的行數。
• Columns 是要返回的列數。
• Min 是隨機數的最小值。
• Max 是隨機數的最大值。
• Whole_Number 為 TRUE 則返回整數，為 FALSE 則返回十進制數。

此函數的所有參數都是可選的。

如果不包含任何參數，您將得到一個帶小數點的單個隨機數，方式與 RAND 函數相同。

RANDARRAY 函數示例

= RANDARRAY ( 4, 3, 6, 14, TRUE )

要生成一個包含 4 行 3 列、介於 6 到 14 之間的隨機整數數組，可以使用上述公式。

這將產生一個數值數組。看到數字周圍的藍色邊框了嗎？這些都是由單個公式生成的！

注意：數組的左上角始終固定在公式所在的單元格中。按 F9 重新計算電子表格將更改數組中的所有數字。

如果不輸入最小值或最大值，將默認使用 0 到 1。

最小值必須小於最大值，否則會出現 #VALUE! 錯誤。

如果您更改 RANDARRAY 公式中的 rows 或 columns 參數，數組將自動調整大小。這就是它們被稱為動態數組的原因。

警告：如果輸出範圍內的某個單元格中已經有數據，您將收到 #SPILL!（溢出）錯誤。不會有任何數據被覆蓋。

使用分析工具庫加載項生成隨機數

還有另一種不使用公式插入隨機數的方法。

您可以使用加載項來創建隨機數。Excel 附帶了 分析工具庫 (Analysis Tool Pak) 加載項，但您需要先安裝它才能使用。

安裝分析工具庫

以下是安裝 分析工具庫 加載項的步驟。

1. 點擊功能區中的 文件 (File) 選項卡。

2. 在窗口左下角的窗格中，向下滾動並點擊 選項 (Options)。您也可以在電子表格窗口中使用鍵盤快捷鍵 Alt, F, T 來打開 選項 窗口。

3. 在彈出窗口的左側窗格中，點擊 加載項 (Add-Ins)。
4. 在顯示的主窗口底部，從下拉列表中選擇 Excel 加載項，然後點擊 轉到 (Go) 按鈕。

5. 這將顯示一個包含 Excel 所有可用加載項的彈出窗口。勾選 分析工具庫 (Analysis ToolPak) 復選框，然後點擊 確定 (OK)。

6. 在 Excel 功能區的 數據 (Data) 選項卡上，現在多了一個名為「分析」的組，其中有一個 數據分析 按鈕。

使用分析工具庫生成隨機數

點擊 分析 組中的 數據分析 按鈕。

這將顯示一個彈出窗口。向下滾動並選擇 隨機數發生器 (Random Number Generation) 選項，然後點擊 確定 (OK)。

將出現一個新的彈出窗口，您可以在其中輸入生成隨機數的參數。

有幾個設置可以自定義：

• 變量個數 (Number of Variables) 這是您希望在輸出表中生成的隨機數列表（列）的數量。如果留空，則將填滿您指定的輸出區域中的所有列。
• 隨機數個數 (Number of Random Numbers) 這是您要生成的隨機數的行數。如果留空，將填滿您指定的輸出區域。
• 分佈 (Distribution) 您可以從下拉列表中選擇多種分佈方法，如均勻分佈或正態分佈。根據此處的選擇，參數 (Parameters) 部分將出現不同的選項。
• 參數 (Parameters) 輸入用於描述所選分佈特徵的值。
• 隨機種子 (Random Seed) 這是可選的，將作為算法生成隨機數的起點。如果您再次使用相同的種子，它將生成相同的隨機數。如果留空，它將從計時器事件中獲取種子值。
• 輸出區域 (Output Range) 輸入要在電子表格中構建表的左上角單元格。如果您將「變量個數」參數留空，則需要指定整個區域。請注意，該區域中的現有數據將被覆蓋。
• 新工作表 (New Worksheet Ply) 此選項將在工作簿中插入一個新工作表，並將結果粘貼到 A1 單元格。在相鄰框中輸入工作表名稱，否則將使用默認名稱。
• 新工作簿 (New Workbook) 這將創建一個新工作簿，並將結果粘貼到第一個工作表的 A1 單元格中。

點擊 確定 (OK) 按鈕，Excel 將根據所選選項插入隨機數。

請注意，與之前顯示的公式方法不同，這些數字是硬編碼的，在工作簿中刷新計算時不會改變。

使用 VBA 生成隨機數

VBA (Visual Basic for Applications) 是 Excel 後台的編程語言，也可以用來生成隨機數。

但是，這比在 Excel 單元格中簡單輸入公式要複雜，您確實需要一些編程知識才能使用它。

要打開 VBA 編譯器，請使用 Alt + F11 鍵盤快捷鍵。

在窗口左側的窗格（項目資源管理器）中，您將看到打開的工作簿（包括加載項）和可用的工作表。

在窗口頂部的菜單上，點擊 插入 (Insert)，然後點擊 模塊 (Module)。這將在當前電子表格中添加一個模塊窗口。將以下代碼粘貼或添加到模塊中。

Sub RandomNumber()
    MsgBox Rnd()
End Sub

按 F5 運行此代碼，Excel 中會出現一個帶有隨機數的彈出消息框。點擊「確定」將返回代碼窗口。

再次運行代碼，將顯示不同的隨機數。該隨機數將介於 0 和 1 之間，但不包括 0 或 1。

您還可以給 Rnd 函數提供一個參數，它是算法生成隨機數的起點種子。

如果種子值設置為負數或零，則每次都會顯示相同的隨機數。

使用 VBA 函數，您可以模擬本文涵蓋的所有前端方法的功能。

Sub RandomNumberV2()

    MsgBox Round((Rnd() * 7) + 3)
End Sub

例如，如果您想生成 3 到 10 之間的隨機整數，則可以使用上面的代碼。

這段代碼將隨機數乘以 7，然後加 3，再四捨五入到零位小數。

假設您想在單元格網格中顯示隨機數。您可以使用以下代碼來實現。

Sub RandomNumberSheet()
Dim M As Integer
For M = 1 To 5
    ActiveSheet.Cells(M, 1) = Round((Rnd(10) * 7) + 3, 0)
Next M
End Sub

此代碼使用 For Next 循環進行 5 次迭代，計算隨機數並將結果輸入到從 A1 單元格開始的一列單元格中。

請記住，原有的任何數據都將被覆蓋，並且沒有警告或撤銷功能。請事先保存之前的工作！

Sub RandomNumberV2()
    Randomize (10)
    MsgBox Round((Rnd() * 7) + 3)
End Sub

還有一個名為 Randomize 的 VBA 函數。您可以在 Rnd 函數之前使用它，將種子值重置為計時器事件或給定的任何參數。

生成沒有重複項的隨機數

您可能會遇到這樣一種情況：希望生成一系列隨機數，但不希望看到任何重複值。

您可能想在 1 到 10 之間選擇 3 個隨機數，且選中的 3 個數字各不相同。

您可以使用 RANDBETWEEN 函數生成隨機數，然後使用功能區中的 Excel 功能 刪除重複項，但這可能仍無法為您提供所需的所有數量。

這裡有幾種可能的解決方案。

使用 RANK.EQ 和 COUNTIF 函數的解決方案

如果您無法使用 Excel 中的 RANDARRAY 函數，則可以使用 RANK.EQ 和 COUNTIF 的組合來獲得唯一的隨機數。

您可以使用 RANDBETWEEN 創建隨機數，然後在下一列中使用公式對其進行排名，從而得到一個隨機排序的 1 到 10 的序列。

= RANDBETWEEN ( 1, 10 )

在單元格 B2 中，輸入上述公式。向下複製此公式，直到 B11 單元格，使其擁有 10 行隨機數。

您會注意到有些數字可能會重複，有些數字則根本沒有出現。

然後，您可以使用 RANK.EQ 函數對它們進行排名，從而創建一個 1 到 10 的序列，但排序是隨機的。

= RANK.EQ ( B2, $B$2:$B$11 ) + COUNTIF ( $B$2:B2, B2 ) - 1

在單元格 C2 中，輸入上述公式。

請注意，這裡使用了絕對引用（$ 符號），以便在向下複製公式時引用保持固定。

將此公式向下複製到單元格 C11，這將以隨機順序顯示 1 到 10 之間的所有數字。

深入解釋一下這個公式，它使用了 RANK.EQ 和 COUNTIF 兩個函數。

= RANK.EQ ( number, ref, [order] )

• Number 是我們想要在數組中查找其排名的數字。
• Ref 是我們要搜索該數字排名的數組。
• Order 是可選參數，允許您按升序或降序查找排名。如果省略，則使用升序。

RANK.EQ 函數返回一個數字在一組數字中的排名。

= COUNTIF ( range, criteria )

• Range 是搜索匹配條件的區域。
• Criteria 是要在區域內匹配的值。

COUNTIF 函數根據給定標準計算單元格數量。在這種情況下，它計算某個隨機數在列表中出現的次數。

對於每個隨機數，RANK.EQ 函數將確定其相對於其他隨機數的排名位置。但如果隨機數包含重複項，它們將產生並列排名。

COUNTIF 函數將補償排名中的任何並列情況，並為該隨機數之前出現的每一次增加一個排名。

這就創建了一個唯一的排名，使得並列項不會得到相同的排名。

由於這種排名是基於一組隨機數的，其結果與隨機化 1 到 10 的數字列表是一樣的。

現在，如果您只需要 5 個不重複的數字，只需從排名列表中提取前 5 個即可。

VBA 解決方案

您也可以使用 VBA 生成一串 1 到 10 之間不重複的隨機數。

Sub RandomNumberNoDuplicates()
Dim M As Integer, Temp As String, RandN As Integer
For M = 1 To 5
Repeat:
    RandN = Round((Rnd(10) * 9) + 1, 0)
    If InStr(Temp, RandN) Then GoTo Repeat
    ActiveSheet.Cells(M, 1) = RandN
    Temp = Temp & RandN & "|"
Next M
End Sub

此代碼遍歷 1 到 5 的值，每次生成 1 到 10 之間的隨機數。

它會測試生成的隨機數是否已經存在。這是通過將成功的數字連接成一個字符串，然後搜索該字符串以查看該數字是否已被使用來實現的。

如果已找到，則使用 Repeat 標籤返回並重新生成新數字。再次測試是否已被使用。如果是新數字，則將其添加到工作表中。

動態數組解決方案

如果您使用的是 Excel 中的動態數組，那麼可以使用單公式方法來避免重複值。

假設您想從 1 到 10 的序列中返回 5 個數字。並且選中的每個數字都是唯一的。

這可以使用 SEQUENCE、SORTBY、RANDARRAY 和 INDEX 函數的組合來完成。

=INDEX(
    SORTBY(
        SEQUENCE(10),
        RANDARRAY(10)
    ),
    SEQUENCE(5)
)

上述公式創建了一個從 1 到 10 的序列。

然後使用 SORTBY 函數，根據 RANDARRAY 函數生成的隨機數列對其進行排序。效果就是將序列隨機打亂。

現在，如果您想獲得 5 個隨機且唯一的數字，只需從隨機排序的序列中提取前 5 個數字即可。

這正是 INDEX 函數所做的！公式的這一部分將返回隨機排序序列中的前 5 個數字。

結論

在 Excel 中有多種生成隨機數的方法。

無論您需要整數、十進制數，還是具有上限和下限的隨機數範圍，這些功能一應俱全。Excel 在這個主題上非常多才多藝。

但是，請記住這些數字是由算法生成的偽隨機數。

雖然該隨機數生成器通過了所有的隨機性測試，但它們並不是真正的隨機數。

要成為真正的隨機數，它必須由計算機環境之外發生的隨機事件驅動。

對於構建通用模擬和統計分析的大多數用途，Excel 的隨機數生成器被認為是可以勝任的。

您是否使用過這些在 Excel 中生成隨機數的方法？您還知道其他方法嗎？請在下方的評論中告訴我！