Last week, I was creating an Excel file with sample data, to use for a few experiments. But don’t worry, they weren’t mad-scientist-type experiments – I was doing Power Pivot experiments, and needed some data to play with.

上周，我用示例数据创建了一个Excel文件，用于一些实验。 但请放心，它们不是疯狂科学家式的实验-我正在进行Power Pivot实验，需要一些数据才能使用。

I needed 2 types of data:

我需要两种类型的数据：

• Numbers: sample test scores in one column
  数字：在一列样本测试分数
• Text: random Region names and Gender in other columns.
  文本：其他列中包含随机的区域名称和性别。

创建随机数 (Create Random Numbers)

It’s easy to create random numbers in Excel – just use the RANDBETWEEN function. Set a bottom and top number, and a random number from that range is returned. I wanted test scores between 30 and 100, so I used this formula:

在Excel中创建随机数很容易-只需使用RANDBETWEEN函数即可。 设置一个底部和顶部数字，并返回该范围内的随机数。 我希望测试成绩在30到100之间，因此我使用了以下公式：

=RANDBETWEEN(30,100)

= RANDBETWEEN(30,100)

获取随机文本 (Get Random Text)

The RANDBETWEEN function can only return numbers, so it’s a little trickier to create a set of random text data. I’ve tried different ways in the past, and they usually involve multiple steps. It’s nothing too complicated, but it takes longer than just using RANDBETWEEN.

RANDBETWEEN函数只能返回数字，因此创建一组随机文本数据比较麻烦。 过去，我尝试过不同的方法，它们通常涉及多个步骤。 没什么太复杂的，但是比使用RANDBETWEEN花费的时间更长。

This time, I tested a new method – using RANDBETWEEN inside a CHOOSE formula. Both lists of options were short – 2 genders and 3 regions – so they would work nicely as values in a CHOOSE formula.

这次，我测试了一种新方法-在CHOOSE公式中使用RANDBETWEEN。 这两个选项列表都很简短-2个性别和3个地区-因此它们可以很好地用作CHOOSE公式中的值。

输入选择值列表 (Type the List of CHOOSE Values)

In my Gender formula, there would be two values, so the RANDBETWEEN function used 1 as the bottom value and 2 as the top.

在我的Gender公式中，会有两个值，因此RANDBETWEEN函数使用1作为下限值，使用2作为上限值。

Then, I typed in the Genders, as the values, putting double quote marks around each value.

然后，我输入Genders作为值，在每个值周围加上双引号。

=CHOOSE(RANDBETWEEN(1,2),"Male","Female")

= CHOOSE(RANDBETWEEN(1,2)，“ Male”，“ Female”)

使用单元格引用作为选择值 (Use Cell References as CHOOSE Values)

In my Region formula, there are 3 values, so the RANDBETWEEN function used 1 as the bottom value and 3 as the top.

在我的Region公式中，有3个值，因此RANDBETWEEN函数使用1作为下限值，使用3作为上限值。

Instead of typing the region names, I used absolute references to the cells which contained the region names. Cell references are a better choice if the values are long, or if they’ll change frequently. Also, it’s quicker than typing!

我没有键入区域名称，而是使用对包含区域名称的单元格的绝对引用。 如果值很长或它们会经常更改，则单元格引用是一个更好的选择。 此外，它比打字还快！

=CHOOSE(RANDBETWEEN(1,3),$K$4,$K$5,$K$6)

=选择(RANDBETWEEN(1,3)，$ K $ 4，$ K $ 5，$ K $ 6)

粘贴为值 (Paste As Values)

The final step in setting up the random data is to copy the columns, and paste them as values. That will lock in the values, so they don’t keep changing, as you work in the file.

设置随机数据的最后一步是复制列，并将其粘贴为值。 这将锁定值，因此在您处理文件时，它们将不会不断更改。

• I use the mouse to select the columns, then drag to the right, while holding the mouse button.
  我使用鼠标选择列，然后在按住鼠标按钮的同时向右拖动。
• Drag the column back to its original location, and let go of the mouse button.
  将列拖回到其原始位置，然后放开鼠标按钮。
• Then, in the popup menu, click on Copy here as Values Only
  然后，在弹出菜单中，单击“此处复制为仅值”

You can see that step near the end of the video, below.

您可以在下面的视频结尾处看到该步骤。

视频：使用“选择”创建随机文本 (Video: Create Random Text With CHOOSE)

Watch this video to see the steps for using RANDBETWEEN on its own, and combined with CHOOSE.

观看此视频，以查看结合使用RANDBETWEEN和CHOOSE的步骤。

演示地址

下载样本文件 (Download the Sample File)

To download the sample file, go to the Excel Sample Files page on my website, and in the Functions section, look for FN0035 - Create Random Text with CHOOSE Function. The file is in xlsx format, and does not contain macros. One sheet has the formulas, and the other has the data pasted as values.

要下载示例文件，请转到我网站上的“ Excel示例文件”页面，然后在“功能”部分中查找“ FN0035-使用CHOOSE函数创建随机文本” 。 该文件为xlsx格式，不包含宏。 一张纸具有公式，另一张纸具有粘贴为值的数据。

创建随机测试数据 (Create Random Test Data)

This video shows how to create random numbers with RANDBETWEEN, then change the formulas to static values.

该视频演示了如何使用RANDBETWEEN创建随机数，然后将公式更改为静态值。

演示地址