在 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 中生成随机的是或否