在 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 中生成隨機的「是」或「否」