您可以通过多种方式在Excel(Excel)中使用随机数生成器。但在此之前，了解随机数生成器的功能以及如何将它用于不同的应用程序非常重要。
一旦你学会了如何使用随机数函数，你就可以做一些事情，比如生成虚拟样本数据来测试计算、选择游戏的玩家顺序、挑选比赛的获胜者以及许多其他事情。
(Random Number Generator Functions)Excel中的随机数生成器函数
Excel中用于创建随机数的两个函数包括RAND、RANDBETWEEN和RANDARRAY。
- RAND：返回 0 到 1 之间的随机数。这通常是小数，例如 0.358432。
- RANDBETWEEN：返回您提供的两个数字之间的随机数。换句话说，如果您想要一个介于 1 和 100 之间的随机数，则此函数将返回该范围内的整数值。
- RANDARRAY：返回任意数量的随机整数或小数，介于任何最小或最大数字之间，以及您想要的任何行和列范围。
每当您打开电子表格或刷新计算时，这两个函数都会生成一个新的随机值。不幸的是，对电子表格的任何更改都会触发这些值的更新。
另外，请记住，当您在多个单元格(multiple cells)中使用这两个随机数函数时，它们都会生成重复值。RANDBETWEEN更有可能生成重复项，因为RAND生成小数并且有更多可能的数字。
如何使用 RAND 函数
RAND函数使用起来非常简单，因为根本没有参数。只需(Just)在单元格中输入“ =RAND ()”，然后按Enter。
这将立即生成 0 和 1 之间的小数。
何时使用 RAND 函数
这么简单的功能怎么会有用呢？以下是使用RAND函数的一些实用想法。
假设您有一个玩棋盘游戏的朋友名单，并且您想快速整理出游戏的顺序。RAND函数非常适合这一点。
只需在第一列中列出您朋友的所有姓名。
使用RAND () 函数在下一列中生成随机小数。
最后，选择两列，从菜单中选择Home ，从功能区中选择(Home)Sort & Filter，然后选择Custom Sort。
在“排序(Sort)”窗口中，选择要排序的随机数列，并将它们从最大到最小(Largest to Smallest)排序。选择确定(OK)。
这将根据生成的随机数重新排序您的列表。除此之外，它还会将名称重新纳入游戏玩法的新顺序。
在您想要随机化计算中的值、文本值列表或存储在电子表格中的任何其他内容的任何情况下，您都可以类似地使用RAND 。
如何使用 RANDBETWEEN 函数
RANDBETWEEN函数的使用几乎与RAND函数一样简单。只需(Just)键入“ RANDBETWEEN ([min],[max]”，将min和max替换为您想要的数字范围的最低和最高数字。
当您按Enter时，该单元格将包含介于该低值或高值之间的随机数。
何时使用RANDBETWEEN 函数(RANDBETWEEN Function)
RANDBETWEEN随机数生成器函数比RAND函数更有用。这是因为它允许您将返回的随机数包含在一个范围内。它还返回整数而不是小数。
这为您提供了一些非常有创意的方法来使用此功能在您想要公平时应用随机分配，或者在不显示任何偏见的情况下为人或事物分配固定数字。
让我们看一个例子。假设您有一个包含 12 个人的列表，并且您想将他们分成两组，每组 6 人，而不会因为让人们为每组“挑选”他们最喜欢的人而感到尴尬。
首先，创建一个包含所有玩家姓名的电子表格。
将以下函数添加到下一列，每个玩家姓名旁边。然后按Enter。
=RANDBETWEEN(1,2)
您可以将其输入到第一个单元格中，然后填写其下方的其余部分(fill the rest below it)。这将自动将每个玩家分配到团队 1 或团队 2。
如果您发现两个团队之间的分布不均匀，只需在第一个单元格中重新输入函数并按Enter即可。这将再次更新所有单元格。重复(Repeat)，直到两队之间的分裂是均匀的。
如何使用 RANDARRAY 函数
RANDARRAY函数仅在Excel (RANDARRAY)365(Excel 365 only)中可用。如果您有此版本的Excel ，这是(Excel)Excel中非常强大的随机生成器函数，您可以将其用于多种用途。
使用它非常简单，有很多可选设置。只需(Just)在任何单元格中输入以下内容：
=RANDARRAY ([rows], [columns], [min], [max], [integer])
参数如下：
- rows : 生成随机数的行数
- columns：生成随机数的列数
- min：数字的最小限制
- max：数字的最大限制
- integer：无论您想要小数还是整数（TRUE或FALSE）
如果您只使用RANDARRAY函数并且不使用任何可选参数，它将像RAND函数一样工作。
请记住，RANDARRAY函数非常灵活，只会按照您指定的方式填充您指定的行和列。默认(Default)始终为小数，并且只有 1 行和 1 列。
何时使用 RANDARRAY 函数
当您需要完整的随机数集合（包括多列和多行）时，最好使用RANDARRAY函数。(RANDARRAY)
例如，如果您正在模拟一个随机的彩票号码选择列表，您可以快速创建一个包含 6 列的行列表（对于 6 个彩票球）。将限制设置为 69 并让Excel使用以下函数为您猜测 10 个彩票选择：
=RANDARRAY(10,6,1,69,TRUE)
将其放入电子表格的第一个单元格中，然后按Enter。
您将看到生成的包含 10 行和 6 列的表。
如果您需要大型随机数据集并且不想手动构建它，此功能可能是科学或统计研究中的一个很好的解决方案。
在Excel中选择正确的随机数生成器(Random Number Generator)
您使用的三个Excel 函数(Excel functions)中的哪一个主要取决于您要查找的结果。如果您真的不在乎数字是多少，兰德(RAND)就可以了。如果要限制生成的数字范围，请使用RANDBETWEEN。如果您正在寻找包含整个数字表的更大数据集，那么RANDARRAY是您的最佳选择。
Random Number Generator in Excel: How and When to Use It
There are a surрrising number of ways yoυ can make use of a random number generator in Excel. But before you do, іt’s important to understand the function of a random number generator and how you can use it for different applications.
Once you learn how to use the random number functions, you can do things like generating dummy sample data to test calculations, choosing the order of players for a game, picking winners for a contest, and many other things.
Random Number Generator Functions in Excel
The two functions in Excel that work to create random numbers include RAND, RANDBETWEEN, and RANDARRAY.
- RAND: Returns a random number between 0 and 1. This is usually a decimal like 0.358432.
- RANDBETWEEN: Returns a random number between two numbers you provide. In other words, if you want a random number between 1 and 100, this function will return an integer value inside that range.
- RANDARRAY: Returns any number of random integers or decimals, between any minimum or maximum numbers, and across any range of rows and columns you want.
Whenever you open a spreadsheet or refresh calculations, both of these functions will generate a new random value. Unfortunately, any change to the spreadsheet triggers an update of these values.
Also, keep in mind that both of these random number functions can generate duplicate values when you use them in multiple cells. It’s more likely that RANDBETWEEN will generate duplicates because RAND generates decimals and there are many more possible numbers.
How to Use the RAND Function
The RAND function is very straightforward to use because there are no arguments at all. Just type “=RAND()” into a cell and press Enter.
This will immediately generate a decimal between 0 and 1.
When to Use the RAND Function
How can such a simple function be useful? Here are a few practical ideas for using the RAND function.
Let’s say you have a list of names of friends playing a board game and you want to quickly sort out the order of play. The RAND function is perfect for this.
Just list all of the names of your friends in the first column.
Use the RAND() function to generate random decimals in the next column.
Finally, select both columns, select Home from the menu, select Sort & Filter from the ribbon, and then select Custom Sort.
In the Sort window, choose the column with the random numbers to sort, and order them from Largest to Smallest. Select OK.
This will reorder your list according to the random numbers that were generated. Along with those, it’ll also resort the names into the new order for gameplay.
You can use RAND similarly in any case where you want to randomize a value in a calculation, a list of text values, or anything else stored in your spreadsheet.
How to Use the RANDBETWEEN Function
The RANDBETWEEN function is almost as simple as the RAND function to use. Just type “RANDBETWEEN([min],[max]”, replacing min and max with the lowest and highest numbers of the range you want numbers for.
When you press Enter, that cell will contain a random number between that low or high value.
When to Use the RANDBETWEEN Function
The RANDBETWEEN random number generator function is even more useful than the RAND function. This is because it lets you contain the returned random numbers within a range. It also returns integers instead of decimals.
This gives you some very creative ways to use this function to apply random assignments when you want to be fair, or assigning fixed numbers to people or things without showing any bias.
Let’s look at an example. Let’s say you have a list of 12 people and you want to split them into two teams of 6 each without the embarrassment that comes from letting people “pick” their favorite people for each team.
First, create a spreadsheet with all of the players’ names.
Add the following function to the next column, next to each player name. Then press Enter.
=RANDBETWEEN(1,2)
You can enter this into the first cell and then fill the rest below it. This will automatically assign each player to team 1 or team 2.
If you find that the distribution between the two teams isn’t even, just reenter the function in the first cell and press Enter. This will update all cells again. Repeat until the split between the two teams is even.
How to Use the RANDARRAY Function
The RANDARRAY function is available in Excel 365 only. If you have this version of Excel, this is a very powerful random generator function in Excel that you can use for a wide range of purposes.
Using it is very simple, with lots of optional settings. Just type the following into any cell:
=RANDARRAY ([rows], [columns], [min], [max], [integer])
The parameters are as follows:
- rows: Number of rows to generate random numbers
- columns: Number of columns to generate random numbers
- min: Minimum limit for the numbers
- max: Maximum limit for the numbers
- integer: Whether you want decimals or integers (TRUE or FALSE)
If you just use the RANDARRAY function alone and don’t use any of the optional parameters, it’ll work just like the RAND function.
Just remember that the RANDARRAY function is very flexible and will only fill in as many rows and columns as you specify, in the exact way that you specify. Default is always decimals, and only 1 row and 1 column.
When to Use the RANDARRAY Function
The RANDARRAY function is best used when you need an entire collection of random numbers, including multiple columns and multiple rows.
For example, if you’re simulating something like a random list of lottery number choices, you can quickly create a list of rows that contain 6 columns (for the 6 lottery balls). Set the limit to 69 and let Excel guess your 10 lottery picks for you with the following function:
=RANDARRAY(10,6,1,69,TRUE)
Place this into the first cell of your spreadsheet and press Enter.
You’ll see a table generated that includes 10 rows and 6 columns.
This function could be a great solution in science or statistical research if you need a large random dataset and don’t want to build it all manually.
Choosing the Right Random Number Generator in Excel
Whichever of the three Excel functions you use depends mostly on what results you’re looking for. If you don’t really care what the number is, RAND will do just fine. If you want to limit the range of numbers generated, then go with RANDBETWEEN. And if you’re looking for a much larger dataset with an entire table of numbers, that’s when RANDARRAY is the best option for you.
