excel中单元格 随机数_在Excel中创建随机方案

excel中单元格 随机数

My son is in an Air Traffic Control course, and there's lots of information to memorize. Directions have to be given in a very specific sequence, or the pilots don't respond. Apparently, you can't say, "Hey dude, just put it down anywhere." No, you have to address the aircraft correctly, and specify an apron and refer to a valid destination. Or something like that!

我儿子正在上一次空中交通管制课程，并且有很多信息要记住。 指示必须以非常特定的顺序给出，否则飞行员没有响应。 显然，您不能说：“嘿，伙计，把它放到任何地方。” 不，您必须正确地对飞机进行寻址，并指定一个停机坪并参考有效的目的地。 或类似的东西！

创建实践方案 (Create Practice Scenarios)

Anyway, to help himself memorize all this information, my son asked if Excel could create random scenarios, with an aircraft, apron and destination. Well, of course Excel can do that!

无论如何，为了帮助自己记住所有这些信息，我儿子问Excel是否可以使用飞机，停机坪和目的地创建随机场景。 好吧，Excel当然可以做到这一点！

Even if you're not preparing to direct giant airplanes in and out of the sky, you might find some uses for this workbook. For example, you could change the options to Protein, Side Dish 1, and Side Dish 2. Then, select tonight's dinner items with a single click.

即使您不准备将大型飞机直接出入天空，也可能会发现此工作簿有一些用途。 例如，您可以将选项更改为“蛋白质”，“配菜1”和“配菜2”。然后，单击一下即可选择今晚的晚餐。

Hmmm...What other types of scenarios would be useful?

嗯...还有哪些其他类型的方案会有用？

创建选项列表 (Create the Lists of Options)

To create the scenarios, I started by naming the worksheets – List_01, List_02 and List_03. Those generic names will make it easier to use the workbook for a different purpose later.

为了创建方案，我首先命名工作表– List_01，List_02和List_03。 这些通用名称将使以后更轻松地将工作簿用于其他目的。

On the List_01 sheet, I added the heading "Aircraft", and entered a list of 20 fake aircraft names.

在List_01工作表上，我添加了标题“飞机”，并输入了20个假飞机名称的列表。

On List_02, I created an Apron list, and on List_03, there's a list of Destinations.

在List_02上，我创建了一个围裙列表，在List_03上，创建了一个目的地列表。

命名列表 (Name the Lists)

Next, I created a dynamic named range for each list, so he can add or remove items from the list.

接下来，我为每个列表创建了一个动态的命名范围 ，因此他可以从列表中添加或删除项目。

The definition for the List01 range is:

List01范围的定义是：

=OFFSET(List_01!$B$1,1,0,COUNTA(List_01!$B:$B)-1,1)

= OFFSET(List_01！$ B $ 1,1,0，COUNTA(List_01！$ B：$ B)-1,1)

Similar names were created for List02 and List03.

为List02和List03创建了相似的名称。

计算清单项目 (Count the List Items)

The first sheet in the workbook was named Scenarios. In row 7, I created three boxes, where the random selections will be shown.

工作簿中的第一张表称为“方案”。 在第7行中，我创建了三个框，其中将显示随机选择。

In row 8, I used the COUNTA function to count the number of items in each named range.

在第8行中，我使用COUNTA函数对每个命名范围中的项目数进行计数。

Here is the formula in cell B8, which counts the items in List01:

这是单元格B8中的公式，该公式计算List01中的项目：

=COUNTA(List01)

= COUNTA(清单01)

Then, I created similar formulas in C8 and D8, to count the items in List02 and List03.

然后，我在C8和D8中创建了类似的公式，以计算List02和List03中的项目。

创建方案单元 (Create the Scenario Cells)

In the boxes in row 7, I used the RANDBETWEEN function to pick a number between 1, and the number shown in row 8. There are 20 items in the Aircraft list, so RANDBETWEEN will pick a number between 1 and 20.

在第7行的框中，我使用RANDBETWEEN函数选择了一个介于1和8所示的数字。飞机列表中有20个项目，因此RANDBETWEEN将选择一个介于1和20之间的数字。

NOTE: In Excel 2003, and earlier versions, install the Analysis Toolpak if you want to use the RANDBETWEEN function.

注意：在Excel 2003和早期版本中，如果要使用RANDBETWEEN函数，请安装Analysis Toolpak。

The INDEX function then returns an item from the list, using that random number as the list row number.

然后， INDEX函数使用该随机数作为列表行号从列表中返回一个项目。

Here is the formula in cell B7:

这是单元格B7中的公式：

=INDEX(List01,RANDBETWEEN(1,B8))

= INDEX(List01，RANDBETWEEN(1，B8))

There are similar formulas in C7 and D7, to pull random items from List02 and List03.

C7和D7中有类似的公式，用于从List02和List03中提取随机项。

测试方案选择器 (Test the Scenario Selector)

Finally, I added a heading to the worksheet, and a note that explains how to select new items.

最后，我在工作表中添加了标题，并添加了说明，说明如何选择新项目。

• Press the F9 key to recalculate, and see new items

  按F9键重新计算，然后查看新项目

Rows 8 and 9 are hidden, to keep the worksheet clean and clutter-free. You don't want to distract the air traffic controller.

第8和9行是隐藏的，以保持工作表干净整洁。 您不想分散空中交通管制员的注意力。

I sent the file to my son, and it's exactly what he wanted. His only complaint is that he wishes he had thought of it earlier!

我把文件寄给了儿子，这正是他想要的。 他唯一的抱怨是他希望他早点想到！

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

If you're studying air traffic control too, or want to create other types of random scenarios, you can download the sample file from my Contextures website.

如果您也正在研究空中交通管制，或者想创建其他类型的随机情景，则可以从我的Contextures网站下载示例文件。

Go to the Sample files page, and in the Functions section, look for FN0039 - Create Random Scenarios.

转到“样本文件”页面，然后在“功能”部分中查找“ FN0039-创建随机方案” 。

The file is in Excel xlsx format, and is zipped. There are no macros in the file. ______________

该文件为Excel xlsx格式，并已压缩。 该文件中没有宏。 ______________

翻译自: https://contexturesblog.com/archives/2011/10/07/create-random-scenarios-in-excel/

excel中单元格 随机数