根據指定的平均值和標準差在 Excel 中生成隨機數

在統計模擬、測試算法或金融、工程和教育等領域的建模過程中，生成具有特定平均值和標準差的隨機數是非常普遍的需求。然而，Excel 並沒有提供直接的內置函數來即時生成符合特定平均值和標準差的隨機數列表。如果您經常需要創建符合特定統計特性的隨機測試數據，掌握如何實現這一點可以大大提高您的工作效率和數據質量。

在本教程中，我們將介紹根據您指定的平均值和標準差生成隨機數的實用方法，並提供詳細的步驟指導、公式參數解釋以及防止錯誤和故障排除的專家提示。此外，我們還為需要自動化此過程或高效生成大型數據集的用戶提供了一個 VBA 宏解決方案。

根據指定的平均值和標準差生成隨機數

VBA 代碼 - 使用指定的平均值和標準差生成隨機數

---

根據指定的平均值和標準差生成隨機數

在 Excel 中，您可以通過應用一組標準函數來生成符合目標平均值和標準差的隨機數。以下是適用於中小型數據集或快速臨時需求的解決方案步驟：

1. 首先，在兩個空單元格中輸入目標平均值和標準差。為了方便和組織清晰，假設您使用 B1 單元格作為所需的平均值，B2 單元格作為所需標準差。請參見截圖：

2. 要創建初始的隨機數據，前往 B3 單元格並輸入以下公式：

=NORMINV(RAND(),$B$1,$B$2)

輸入公式後，拖動填充柄以填滿您需要的隨機數據集行數。每個單元格將根據指定的平均值和標準差生成一個值。

提示：在公式 =NORMINV(RAND(),$B$1,$B$2) 中：

• RAND() 每次工作表重新計算時都會產生一個介於 0 和 1 之間的不同隨機概率。
• $B$1 指向您指定的平均值。
• $B$2 指向所需的標準差。

對於現代版本的 Excel（2010 及以後），考慮使用 =NORM.INV(RAND(),$B$1,$B$2)，它功能上相同，但反映了更新的函數名稱。

3. 為了驗證生成的數字是否在統計學上接近您期望的平均值和標準差，使用以下公式計算生成樣本的實際值。在 D1 單元格中，使用下面公式計算樣本平均值：

=AVERAGE(B3:B16)

在 D2 中，使用以下公式計算樣本標準差：

=STDEV.P(B3:B16)

提示：

• B3:B16 只是一個範圍示例。根據您在步驟 2 中生成的隨機值數量進行相應調整。
• 由於大數定律，更大的隨機樣本會導致實際平均值和標準差更接近您指定的值。

4. 為了進一步調整您的序列以精確匹配目標平均值和標準差，對初始隨機值進行標準化。在 D3 單元格中，輸入以下公式：

=$B$1+(B3-$D$1)*$B$2/$D$2

拖動填充手柄向下填滿與您擁有隨機數量相同的行數。該公式將您的初始值標準化並精確縮放以滿足 B1 和 B2 中的平均值和標準差。

提示：

• B1 是您要求的平均值。
• B2 是您要求的標準差。
• B3 是原始的隨機值。
• D1 是那些原始隨機值的平均值。
• D2 是那些原始隨機值的標準差。

現在，您可以通過重新計算最終值集合的平均值和標準差來確認其符合您的要求，以確保質量保證和文檔記錄。

5. 在 D17 單元格中，使用以下公式計算最終隨機數集合的平均值：

=AVERAGE(D3:D16)

然後在 D18 單元格中，使用以下公式計算標準差：

=STDEV.P(D3:D16)

提示：D3:D16 指的是您最終確定的隨機數範圍。

故障排除：

• 如果看到 #VALUE! 錯誤，請仔細檢查所有引用的單元格範圍，並確保沒有公式引用空白或無效單元格。
• 如果每次重新計算時公式不斷變化，選擇最終的隨機數，複製它們，並使用「選擇性粘貼 > 數值」來防止進一步更新。
• 請記住，Excel 中的隨機生成器依賴重新計算，因此在一致性至關重要時保存靜態結果是必要的。

---

VBA 代碼 - 使用指定平均值和標準差生成隨機數

對於需要快速生成大量符合指定平均值和標準差的隨機數據的情況——特別是在重複性、自動化或高容量的情況下——VBA 宏提供了一個節省時間的解決方案。只需一次執行，即可直接在工作簿中創建完整的數據集，減少手動重複並最小化公式複製錯誤。

這種方法適用於：

• 自動生成用於模擬、壓力測試或教學演示的隨機數據集。
• 希望以最少的人工干預來標準化輸出格式的情況。
• 熟悉使用 Excel 的 VBA 編輯器的用戶。

與公式方法相比，VBA 還允許動態調整或與更複雜的工作流程集成，但請注意，必須在您的工作簿中啟用宏，並且可能需要明確保存為“啟用宏”的 .xlsm 格式。

1. 在 Excel 功能區上，點擊開發工具（如果不可見，請通過文件 > 選項 > 自定義功能區 啟用），然後選擇Visual Basic。在 Visual Basic for Applications 窗口中，點擊插入 > 模塊，並將以下代碼複製到空白模塊窗口中：

Sub GenerateRandomNumbersWithMeanStd()
    Dim outputRange As Range
    Dim meanValue As Double, stdDevValue As Double
    Dim numItems As Long, i As Long
    Dim xTitleId As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set outputRange = Application.InputBox("Select the output range", xTitleId, Type:=8)
    meanValue = Application.InputBox("Enter the mean value", xTitleId, "", Type:=1)
    stdDevValue = Application.InputBox("Enter the standard deviation", xTitleId, "", Type:=1)
    
    If outputRange Is Nothing Or meanValue = 0 Or stdDevValue = 0 Then
        MsgBox "Please ensure you have specified all required parameters.", vbExclamation, "KutoolsforExcel"
        Exit Sub
    End If
    
    numItems = outputRange.Count
    Randomize
    
    For i = 1 To numItems
        outputRange.Cells(i).Value = Application.WorksheetFunction.NormInv(Rnd, meanValue, stdDevValue)
    Next i
End Sub

2。 點擊 運行 按鈕（或按 F5）以啟動宏。一個對話框將提示您選擇要輸出隨機數的範圍（例如，選擇 A1:A100 以生成 100 個值）。接下來，您將被要求輸入所需的平均值和標準差。宏將填充範圍內符合您規格的隨機數。

提示和故障排除：

• VBA 使用 Excel 的 NormInv 函數生成正態分佈的數字——始終雙重檢查您的版本是否支持此功能；對於較舊的 Excel 版本，該函數可能需要是 NORMINV。
• 隨機種子由 Randomize 設置，以便每次運行時產生不同的結果。
• 如果您想要可重現的結果，註釋掉或刪除 Randomize 行。
• 宏將覆蓋所選輸出範圍內的任何現有數據，因此如果需要，請確保選擇一個空白區域。
• 如果您輸入了不適當的值（例如，負數或零標準差），宏將不會繼續執行並會顯示警告消息。

---

相關文章：

• 在 Excel 中生成無重複的隨機數
• 在 Excel 中生成正數或負數的隨機數
• 停止改變 Excel 中的隨機數
• 在 Excel 中生成隨機的是或否