Excel随机数生成全攻略：从RAND到RANDARRAY的实用技巧

Introduction to Random Number Generation in Excel (Excel随机数生成简介)

Not every user needs random numbers in Excel. Most people work with fixed numbers and formulas and may not require random digits in their reports. However, random number generators prove extremely useful when dealing with different scenarios of a dataset or performing various statistical analyses. Financial models can utilize random simulations that depend on probability. Such models might need to run thousands of times, with the random number generator providing parameters for each simulation. Regardless of your specific need for random numbers, Excel offers several methods to generate them. In this article, I will demonstrate all the ways to insert random numbers into your workbook.

并非每个用户都需要Excel中的随机数。大多数人使用固定数字和公式，可能不需要在报告中出现随机数字。然而，当处理一组数据的不同场景或执行各种统计分析时，随机数生成器确实有很大的用处。金融模型可以使用依赖于概率的随机模拟。该模型可能需要运行数千次，但随机数生成器提供每个模拟的参数。无论你需要什么样的随机数，Excel都有几种生成方法。在这篇文章中，我将向你展示在工作簿中插入随机数的所有方法。

Generating Random Numbers with the RAND Function (使用RAND函数Excel内置函数，无需参数，生成0到1之间（不包括0和1）的均匀分布随机小数。生成随机数)

The first method I will show you is the simplest way to generate random values in Excel. There is a very straightforward RAND function that requires no arguments and will generate a random number between 0 and 1.

我将向你展示的第一种方法是在Excel中生成随机值的最简单方法。有一个非常简单的RAND函数Excel内置函数，无需参数，生成0到1之间（不包括0和1）的均匀分布随机小数。，它不需要任何参数，并且将生成0到1之间的随机数。

RAND Function Syntax (RAND函数Excel内置函数，无需参数，生成0到1之间（不包括0和1）的均匀分布随机小数。语法): =RAND()

This function has no required or optional arguments. The input for the function is always a set of empty parentheses. This function will generate a decimal random number between 0 and 1, excluding 0 and 1. Duplicate values are possible but unlikely, as the RAND function produces numbers from a continuous range. The returned values will follow a uniform distribution. This means any number between 0 and 1 is equally likely to be returned.

此函数没有必需或可选的参数。函数的输入总是带有一组空括号。此函数将生成一个介于0和1之间的十进制随机数，但不包括0或1。重复的值是可能的，但不太可能，因为RAND函数Excel内置函数，无需参数，生成0到1之间（不包括0和1）的均匀分布随机小数。从连续的数字范围中产生数字。返回的值将遵循均匀分布。这意味着，0和1之间的任何数字都同样可能被返回。

Generating Random Numbers Between Any Two Numbers (在任意两个数字之间生成随机数)

If you need numbers between 1 and 10, decimal numbers between 0 and 1 might not be very useful. However, you can use a simple formula incorporating the RAND function to generate random numbers between any two numbers:

如果你需要1到10之间的数字，0到1之间的十进制数字可能不会太有用。但是，你可以使用一个包含RAND函数Excel内置函数，无需参数，生成0到1之间（不包括0和1）的均匀分布随机小数。的简单公式来生成任意两个数字之间的随机数：

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

In general, you can use the formula above to create random numbers between X and Y. For example, to generate numbers between 1 and 10, you can use the formula:

通常，可以使用上面的公式创建X和Y之间的随机数。例如，要生成1到10之间的数字，可以使用公式：

=RAND()*9+1

This will multiply the random number by 9 and then add 1. This will produce a decimal number between 1 and 10.

这将产生的随机数乘以9，然后再加1。这将产生1到10之间的十进制数。

Generating Random Integers Between Any Two Numbers (在任意两个数字之间生成随机整数)

Another potential requirement you might encounter is generating random integers between two given numbers. This can also be accomplished using a simple formula:

你可能遇到的另一个可能的需求是在两个给定的数字之间生成随机整数。这也可以使用一个简单的公式来完成：

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

In general, you can use the formula above to generate random integers between two values, X and Y. For example, the formula above will create random integers between 1 and 10. This is the same as the previous formula but uses the ROUND function to round to zero decimal places.

通常，可以使用上面的公式生成两个值X和Y之间的随机整数。例如，上面的公式将创建介于1和10之间的随机整数。这与以前的公式相同，但使用ROUND函数四舍五入到小数点后零位。

You can copy this formula down a column in your spreadsheet, and if you keep pressing F9 to recalculate, you will see various combinations of numbers from 1 to 10. Since the set of possible numbers is discrete, the generated random numbers are more likely to repeat in the list, depending on the minimum and maximum of that range. This also works for producing negative numbers. Suppose you need to generate random integers between -3 and 4; then the formula above is what you need. Multiplying the RAND function by 7 will produce random numbers between 0 and 7. Adding -3 to the result and rounding to zero decimal places will give a random number range from -3 to 4.

你可以将这个公式复制到电子表格的列中，如果你一直按F9重新计算，你会看到从1到10的各种数字组合。由于可能的数字集是离散的，因此生成的随机数很可能在列表中重复，这取决于该范围的最小值和最大值。这也适用于产生负数。假设你需要生成-3到4之间的随机整数，那么上面的公式就是你需要的。将RAND函数Excel内置函数，无需参数，生成0到1之间（不包括0和1）的均匀分布随机小数。乘以7将产生0到7之间的随机数。结果加上-3，四舍五入到小数点后零位，这将给出-3到4的随机数范围。

Generating Random Numbers with the RANDBETWEEN Function (使用RANDBETWEEN函数Excel内置函数，需要下限和上限两个参数，生成指定范围内的随机整数。生成随机数)

Excel has a useful feature to generate random numbers within a lower and upper bound range. This is easier to use than the RAND function because it includes additional operators to reach specific ranges.

Excel有一个有用的功能，可以在上限和下限范围内生成随机数。这比使用RAND函数Excel内置函数，无需参数，生成0到1之间（不包括0和1）的均匀分布随机小数。更容易使用，因为它包括额外的运算符来达到特定的范围。

RANDBETWEEN Function Syntax (RANDBETWEEN函数Excel内置函数，需要下限和上限两个参数，生成指定范围内的随机整数。语法): =RANDBETWEEN(bottom, top)

Both arguments, bottom and top, are required, where bottom is the lower bound of the value to return, and top is the upper bound of the value to return. This function will generate a random integer between the bottom value and the top value. The function will also return the top value and the bottom value as possible, as it is not strictly between in this function. For example, if you want random numbers between -3 and 4, as in the previous example, you can use the formula above.

这两个参数bottom和top都是必须的，其中bottom是要返回的值的下限，top是要返回的值的上限。此函数将在底部值和顶部值之间生成随机整数。该函数还将返回尽可能高的值和下限值，因为它在该函数中并不严格介于两者之间。例如，如果你想要-3到4之间的随机数，就像前面的例子一样，你可以使用上面的公式。

Note that the RANDBETWEEN function can only generate integers. There is no way to make the function produce decimal numbers. However, it is much less complex compared to using the RAND function with operators to achieve the same result.

请注意，RANDBETWEEN函数Excel内置函数，需要下限和上限两个参数，生成指定范围内的随机整数。只能生成整数。没有办法使函数产生十进制数。然而，与使用带有运算符的RAND函数Excel内置函数，无需参数，生成0到1之间（不包括0和1）的均匀分布随机小数。来获得相同的结果相比，它要复杂得多。

Generating Random Numbers with the RANDARRAY Function (使用RANDARRAY函数Excel动态数组函数，可生成指定行数、列数、范围和类型的随机数数组，仅适用于Microsoft 365版本。生成随机数)

Often, you don't want just one random value but an entire set of random values. The RANDARRAY function is the perfect solution for this problem. It will fill a range of cells with a set of random numbers, which can be very powerful.

通常情况下，你不想要一个随机值，而是想要一整套随机值。RANDARRAY函数Excel动态数组函数，可生成指定行数、列数、范围和类型的随机数数组，仅适用于Microsoft 365版本。就是这个问题的完美解决方案。它将用一组随机数填充一系列单元格，这可能非常强大。

Note (注意): This feature is only available in the Microsoft 365 version of Excel.

此功能仅在Microsoft 365版本的Excel上可用。

RANDARRAY Function Syntax (RANDARRAY函数Excel动态数组函数，可生成指定行数、列数、范围和类型的随机数数组，仅适用于Microsoft 365版本。语法): =RANDARRAY([rows],[columns],[min],[max],[whole_number])

Where (其中):

• rows is the number of rows to return. (Rows是要返回的行数。)
• columns is the number of columns to return. (Columns是要返回的列数。)
• min is the minimum value of the random number. (Min是随机数的最小值。)
• max is the maximum value of the random number. (Max是随机数的最大值。)
• whole_number is TRUE to return integers, FALSE to return decimal numbers. (Whole_Number为TRUE返回整数，为FALSE返回十进制数。)

All arguments for this function are optional. If you don't include any arguments, you will get a random number with decimal places in the same way as the RAND function.

此函数的所有参数都是可选的。如果不包括任何参数，你将以与RAND函数Excel内置函数，无需参数，生成0到1之间（不包括0和1）的均匀分布随机小数。相同的方式获得一个带小数位数的随机数。

To generate an array of 4 rows by 3 columns of whole random numbers between 6 and 14, you can use the formula above. This will generate an array of values. Notice the blue border around the numbers? These are all produced by a single formula! The top-left corner of the array is always anchored to the cell where the formula is placed. Pressing F9 to recalculate the spreadsheet will change all the numbers in the array.

要生成一个由4行3列6到14之间的全随机数组成的数组，可以使用上面的公式。这将生成一个值数组。注意到数字周围的蓝色边框了吗？这些都是由单一公式产生的！数组的左上角始终固定在公式所在的单元格上。按F9重新计算电子表格将更改数组中的所有数字。

If no minimum or maximum is set, the default values of 0 to 1 will be used. The minimum must be less than the maximum, otherwise a #VALUE! error will occur. If you change the rows or columns parameter in the RANDARRAY formula, the array will automatically resize. This is why they are called dynamic arrays.

如果没有设置最小值或最大值，则将使用默认值0到1。最小值必须小于最大值，否则将出现#VALUE!错误。如果更改RANDARRAY公式中的行或列参数，数组将自动调整大小。这就是它们被称为动态数组的原因。

Warning (警告): If you have existing data in any cell within the output range you are targeting, you will get a #SPILL! error. It will not overwrite any data.

警告：如果你输入的输出范围中的某个单元格中已经有数据，你将得到一个#SPILL！错误不会覆盖任何数据。

Generating Random Numbers Using the Analysis ToolPak (使用分析工具库生成随机数)

There is another way to insert random numbers without using formulas. You can use an add-in to create random numbers. Excel comes with an Analysis ToolPak add-in, but you need to install it first before you can use it.

还有另一种方法可以在不使用公式的情况下插入随机数。你可以使用插件创建随机数。Excel附带了一个分析工具库插件，但你需要先安装它，然后才能使用它。

Here are the steps to install the Analysis ToolPak add-in (以下是安装分析工具库插件的步骤):

1. Click the File tab in the Ribbon. (单击功能区中的“文件”选项卡。)
2. In the bottom-left pane of the window, scroll down and click Options. You can also use the keyboard shortcut Alt, F, T in the spreadsheet window to open the Options window. (在窗口的左下角窗格中，向下滚动并单击“选项”。也可以使用电子表格窗口中的键盘快捷键Alt、F、T打开“选项”窗口。)
3. In the pop-up window, click Add-ins in the left pane. (在弹出窗口的左侧窗格中，单击“加载项”。)
4. At the bottom of the main window that appears, select Excel Add-ins from the dropdown list and click the Go button. (在显示的主窗口底部，从下拉列表中选择Excel加载项，然后单击转到按钮。)
5. This will show a pop-up window with all available add-ins for Excel. Check the box corresponding to Analysis ToolPak and click OK. (这将显示一个弹出窗口，其中包含Excel的所有可用加载项。选中“分析工具库”对应的框，然后单击“确定”。)

On the Data tab of the Excel Ribbon, there is now an extra group called Analysis with a button named Data Analysis.

在Excel功能区的“数据”选项卡上，现在有一个名为“分析”的额外组，其中有一个按钮名为“数据分析”。

Frequently Asked Questions (常见问题)

1. What is the difference between RAND and RANDBETWEEN?

   RAND函数Excel内置函数，无需参数，生成0到1之间（不包括0和1）的均匀分布随机小数。生成0到1之间的小数，而RANDBETWEEN函数Excel内置函数，需要下限和上限两个参数，生成指定范围内的随机整数。生成指定范围内的整数。

2. Can I generate non-repeating random numbers in Excel?

   使用基础函数很难保证完全不重复，但可以通过VBA或复杂公式实现近似不重复的序列。

3. Is the RANDARRAY function available in all Excel versions?

   不，RANDARRAY函数Excel动态数组函数，可生成指定行数、列数、范围和类型的随机数数组，仅适用于Microsoft 365版本。仅在Microsoft 365版本的Excel中可用。

4. How do I stop random numbers from changing when I recalculate?

   可以将公式结果复制并选择性粘贴为值，或使用VBA脚本控制计算。

5. What are the main applications of random numbers in Excel?

   主要应用于蒙特卡洛模拟、抽样分析、数据脱敏、游戏开发和教学演示等场景。