在Excel中生成随机数的5种方法

并不是每个用户都需要在 Excel 中使用随机数。大多数人处理的是固定数字和公式，可能不需要在报告中出现随机数。

然而，在对一组数据进行不同场景的模拟或执行各种统计分析时，随机数生成器具有巨大的用途。

财务模型可能会使用依赖于概率的随机模拟。该模型可能需要运行数千次，而随机数生成器则为每次模拟提供参数。

无论您对随机数的需求是什么，Excel 都有多种生成方法。

在这篇文章中，我将向您展示所有可以用来在工作簿中插入随机数的5种方法。

如何在 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 中生成随机数的方法？您还知道其他方法吗？请在下方的评论中告诉我！