Excel实战技巧：从Excel预测的正态分布中返回随机数

excelperfect

两种类型的随机模拟

粗略地说，我们可以将随机模拟分为两种类型：表格和扩展。

表格模拟

使用表格模拟，可以在电子表格一行的多个单元格中创建整个模型，其中一些单元格包括随机数。

要创建模拟，可以将公式行向下复制到数千行。然后，为了分析模拟，需要分析模拟表每一行中由这些随机数生成的许多不同结果。

例如，假设你的公司每月投放数量不等的在线广告，从而为你的网站生成数量不等的访问者。并假设需要可变数量的访问者来产生可变数量的每次销售。

因此，在一行的多个单元格中，可以模拟一个月的活动，使用随机数来定义每个可变程度。可以将这些公式复制到它们的列中，以创建一个包含数千个可能结果的表格。然后，分析表格以确定一段时间内的平均销售额，以及该估计值的可变性。

扩展模拟

扩展的方法是不同的。在这种方法中，可以创建一个可以像你希望的那样详细的模型。可以在任意数量的工作表中为模型使用任意数量的行，然后多次运行该模型并汇总结果。

通常，这称为蒙特卡罗分析。

但与大多数在Excel中创建的模型不同，蒙特卡罗分析使用随机数来生成关键假设。

例如，如果你一个月的最佳销售量是120，而最坏的销售量是80，你将使用随机数在这些限制之间进行选择。或者，如果你的最高可能销售成本是40%，而最低可能成本是30%，将使用另一个随机数在这些限制之间进行选择。

使用这种方法，每次重新计算工作簿时，模型都会为你提供修改后的预测。

可以轻松地根据需要多次重新计算此模型，并从每次计算中获取结果……自动地，无需编程即可完成。在下一篇文章中，在如何使用Excel数据表创建蒙特卡罗模型和预测中会展示如何做到这一点。

现在，让我们深入研究这两种模拟的关键要素：随机数。首先，将向你展示一个显而易见的方法，你很少会在模拟中使用它。接着，将向你展示大部分时间应该使用的方法。

Excel的两个随机数函数

Excel 提供了两个生成随机数的函数：

• RAND函数返回一个介于0和1之间的随机数。
• RANDBETWEEN(bottom,top)返回参数bottom和参数top之间的随机整数。

 

这两个函数返回的结果出现在bottom和top之间任何地方的机会相同。

 

为了说明这一点，我复制了RAND函数并将其粘贴到一列中的10000个单元格中，然后将这些值分成10组大小相等区间，创建了一个直方图，显示一个值在每个区间中出现的次数。

图1

 

图2

 

图1显示了第一次按F9重新计算工作簿后的结果，图2显示了再次按下F9后的结果。如你所见，每个分组的结果均为1000左右，即结果份额相等。

 

如果使用RANDBETWEEN而不是RAND，会看到类似的结果。

 

使用RAND或RANDBETWEEN进行模拟的问题在于，我们需要如下图3所示的Excel图表的结果。

图3

 

也就是说，一旦我们定义了假设的边界，就通常希望随机数是中心加权的。那么，如何才能做到这一点呢？如何从正态分布中返回一个随机数？

 

来自正态分布的随机数

要从正态分布返回随机数，主要依赖NORM.INV函数，该函数使用以下语法：

=NORM.INV(probability, Mean, standard_dev)

 

参数probability很容易指定，概率的值从0到1，就像RAND函数生成的那样。因此，如果我们能弄清楚如何计算均值和标准差，就可以使用这个公式从正态分布中返回一个随机数：

=NORM.INV(RAND(), Mean, standard_dev)

 

再看看图3所示的图表，浅蓝色区域在均值的每一侧显示一个标准偏差。正如图表下方的第一个标签所示，浅蓝色区域约占总面积的68%。

 

浅蓝色和中蓝色区域一起显示均值的两个标准偏差内的值。图表下方的第二个标签显示，这代表了总面积的95%左右。

 

所以想想这意味着什么。假设最初的预测显示下个月的销售额将是100，但是如果你的工作依赖于此，那么你认为这些销售额的最佳情况和最坏情况估计是什么？仔细考虑之后，假设你估计销售额可能高达120，低至70。

 

这两个数字定义了图中中蓝色区域的外边界。也就是说，你的估计表明实际销售额大约有95%的可能性在70到120之间。

 

因此，

• 均值是120和70的平均值，在本例中为95。
• 标准差是120和70之间的差值除以4个标准差，在本例中为12.5。

 

因此，这是从均值为95且标准差为12.5的正态分布中返回随机数的公式：

=NORM.INV(RAND(), 95, 12.5)

 

现在让我们检查一下这个公式是否提供给了我们预期的结果。

 

用直方图检查结果

下面的两个图并不花哨，但它们讲述了在你创建Excel模型或预测时需要了解的事。

 

图4计算了上一个公式如何成功地从正态分布返回数字。

图4

 

在单元格中输入公式：

A1:=NORM.INV(RAND(),95,12.5)

将该公式向下复制直到单元格A10000。

 

在列C中显示列A中的最大值和最小值。

C2：=MIN($A$1:$A$10000)

C3：=MAX($A$1:$A$10000)

 

列D中输入9个数字作为向导。

 

在列E中按下面操作：
E2：=C2

E3：=E2+($C$3-$C$2)/9

将E3向下复制至E11。注意，单元格E11中的数值应该等于单元格C3中的最大值。

 

配置直方图数据

列F中包含要在新的直方图中显示的数据，我们使用FREQUENCY函数来生成这些数据。

 

首先，选择单元格区域F3:F11，然后输入数组公式：

=FREQUENCY(A1:A10000,E3:E11)

 

注意，是数组公式！因此，应该以Ctrl+Shift+Enter组合键结束公式输入。

 

创建直方图

选择单元格区域F3:F11，单击功能区“插入”选项卡“图表”组中的“柱形图——簇状柱形图”，创建如下图5所示的图表。

图5

 

当然，你可以让这个图表更好看些，但这对于测试来说不是必需的。该图表很容易证明我们已经通过组合NORM.INV函数和RAND函数完成了我们想要的：我们现在有一种方法可以从正态分布中返回随机数。

 

下一篇文章，在如何使用Excel数据表创建蒙特卡罗模型和预测中，会向你展示如何在此基础上使用蒙特卡罗方法创建概率模拟。

 

注：本文学习整理自exceluser.com，供有兴趣的朋友参考。