Excel中的概率模拟简介_相关概率事件-Excel学习网
2019年10月26日 20:00 发布来源：Excel学习网
（这是系列文章中的第一篇。第二篇文章，如何使用Excel数据表创建蒙特卡洛模型和预测。）
当我为雇主创建模型和预测时，我知道我的结果将是错误的，而且我没有实际的方式来表达我的结果的任何程度的不确定性。
例如，如果我预测一个时期的利润，则我的电子表格将使用一个数字来预测某个时期的销售，将另一个数字用于预测我的营业费用，将另一个数字用于我的商品销售成本百分比，以及以此类推。
法线曲线
我知道我预测中的每个预测数字都可能介于最佳和最差值之间……大概是由此Excel图表中所示的正态分布确定的。
（单击此处以获取此图表的副本，其中包括数据，计算和文档。）
但是我的模型无法为我提供这样的概率。他们没有提供任何方法来评估或减少我对最终预测的不确定性。
而且，在我担任现场Excel顾问的那些年里，我看到了来自世界各地的电子表格用户创建的许多模型和预测。在那段时间里，我从未见过可以做得更好的模型或预测。
输入“模拟”
从那时起，我开始意识到大多数Excel模型和预测实际上都是一种模拟形式 。
Wikipedia告诉我们，模拟是随着时间的推移模拟现实世界中流程或系统的操作。
维基百科解释说，模拟事物的行为首先需要开发模型。该模型表示系统或过程的关键特征或行为。该模型表示系统本身，而仿真表示系统随时间的运行情况。
通常，模型有两种：确定性模型和概率模型。
确定性模型使用特定数字表示假设值，包括比率。
概率（或“随机”）模型依赖于随机数，通常从正态分布中得出。
从这个角度来看，我为雇主准备的模拟类型以及我在其他公司中看到的那种类型都是 确定性模拟。
另一方面，我们应该使用的是概率模拟。这将使我们能够指定每个假设的不确定性程度，也有助于我们评估最终结果的不确定性。
我认为减少Excel用户对确定性仿真的依赖已经过去了。现在是时候在我们的工作中使用概率模拟了。
两种类型的概率模拟
宽松地说，我们可以将概率模拟分为表格和扩展两种类型。
表格模拟
通过表格模拟，您可以在电子表格的一行中的多个单元格中创建整个模型。其中一些单元格包含随机数。
要创建模拟，您需要将公式的行向下复制到数千行。然后，要分析模拟，您需要分析模拟表每一行中由随机数产生的许多不同结果。
例如，假设您的公司每个月放置可变数量的在线广告，从而产生了可变数量的网站访问者。并假设需要可变数量的访问者来生成可变数量的每次销售。
因此，在连续的多个单元格中，您可以模拟一个月的活动，使用随机数定义每个程度的可变性。您可以将这些公式复制到其列的下方，以创建具有数千种可能结果的表。然后，您需要分析表格以确定一个时期的平均销售额以及该估算值的可变性。
扩展模拟
扩展的方法是不同的。用这种方法，您可以创建一个模型，该模型可以像您希望的那样详细。您可以根据需要在模型中使用尽可能多的行。
通常，这被称为蒙特卡洛分析。
但是与大多数在Excel中创建的模型不同，蒙特卡洛分析将使用随机数生成关键假设。
例如，如果一个月的最佳销售可能是120，而最坏的销售可能是80，则可以使用随机数在这些限制之间进行选择。或者，如果您的最高可能销售成本为40％，而最低可能的成本为30％，则可以使用另一个随机数在这些限制之间进行选择。
使用这种方法，每次重新计算工作簿时，模型都会为您提供修订的预测。
您可以轻松地任意多次重新计算该模型，并自动获取每次计算的结果。而且，您无需编程即可做到。在下一篇文章如何使用Excel数据表创建蒙特卡洛模型和预测中，我将向您展示如何做到这一点。
但是现在，让我们深入研究两种模拟的重要组成部分：随机数。首先，我将向您展示一种显而易见的方法，您很少在模拟中使用该方法。然后，我将向您展示大多数情况下应使用的方法。
Excel的两个随机数函数
Excel提供了两个生成随机数的函数...
RAND（）返回0到1之间的随机数。
RANDBETWEEN（底部，顶部）返回底部和顶部参数之间的随机整数。
这两个函数返回的结果都有可能出现在上下边界之间的任何位置。
为了说明这一点，我复制了RAND（）函数并将其粘贴到一栏中的10,000个单元格中。我将其值分为10组大小相等的垃圾箱，然后创建了一个直方图，以显示一个值在每个垃圾箱中出现了多少次。
Excel的RAND函数的结果
左图显示了第一次按F9重新计算工作簿后的结果。第一张图表中的第一列显示RAND在这10,000行中生成的值介于0到.10之间，相差1010次。该图的最后一列显示RAND生成的值介于0.9到1.0之间，约为990倍。另一个图表显示了再次按下F9后的结果。
如您所见，每一列的结果被分组为1000，这是每一列在结果中的均等份额。
如果我使用RANDBETWEEN而不是RAND，我会看到类似的模式。
法线曲线
在模拟中使用RAND或RANDBETWEEN的问题是我们希望结果看起来像这张Excel图表。
就是说，一旦我们定义了假设的边界，我们通常就希望对随机数进行中心加权。
那么我们该怎么做呢？我们如何从正态分布中返回一个随机数？
来自正态分布的随机数
要从正态分布中返回随机数，我们主要依赖于 NORM.INV函数，该函数使用以下语法：
= NORM.INV（概率，均值，standard_dev）
概率参数很容易指定。概率值从零到一，就像RAND函数生成的一样。因此，如果我们能够弄清楚如何计算平均值和标准差，则可以使用以下公式从正态分布中返回一个随机数：
= NORM.INV（RAND（），平均值，standard_dev）
如果您是统计巨星，那么可以轻松计算出用于此公式的数据的均值和标准差。但是，如果您像我们大多数Excel用户一样，则需要一种更简单的方法。这里是...
法线曲线
再看一下此Excel图表。浅蓝色区域在平均值的一个标准偏差内。
如图表下方的第一个标签所示，浅蓝色区域约占总面积的68％。
浅蓝色和中等蓝色区域一起显示均值的两个标准差之内。图表下方的第二个标签显示，这大约占总面积的95％。
因此，请考虑这意味着什么。假设您的原始预测显示下个月的销售量将为100。但是，如果您的工作依赖于此，那么您认为对于这些销售而言，最佳和最差情况估计是什么？在认真考虑之后，假设您估计销售可能高达120到70。
这两个数字定义了图中中等蓝色区域的外部边界。就是说，您的估计是实际销售在70到120之间的可能性约为95％。
因此...
您的平均值是120和70的平均值，在这种情况下为95。
您的标准偏差是120和70之间的差，再除以4个标准偏差，在这种情况下为12.5。
因此，这是您的公式，可从正态分布中返回均值为95且标准差为12.5的随机数：
= NORM.INV（RAND（），95,12.5）
现在，让我们检查一下此公式是否能给我们期望的结果。
用直方图检查结果
以下两个数字并不理想，但是它们讲述了一个故事，如果您创建Excel模型或预测，则需要了解。
从正态分布检验随机结果
此图计算先前公式如何成功地从正态分布中返回数字。这是设置方法...
在显示的单元格中输入此公式：
A1： = NORM.INV（RAND（），95,12.5）
我们要将此单元格沿列复制到单元格A10000。这是执行此操作的快速方法：
复制单元格A1。
按F5功能键启动“转到”对话框。
在“引用”编辑框中，输入A10000，然后按Enter选择单元格A10000。
按住Ctrl和Shift键，然后按向上箭头键，这将从A10000到A1中选择所有单元格。
按Ctrl + v将单元格A1粘贴到所选单元格中。
C列中的单元格在A列中显示最小值和最大值。在显示的单元格中输入公式：
C2： = MIN（$ A $ 1：$ A $ 10000）
C3： = MAX（$ A $ 1：$ A $ 10000）
D列显示了九个数字，可用作可视指南。输入您想要的任何方式。
单元格E2中的第一个公式仅引用最小值：
E2： = C2
下一个公式计算最小值加上最小值和最大值之间距离的九分之一：
E3： = E2 +（$ C $ 3- $ C $ 2）/ 9
如图所示，向下复制单元格E3中的公式。请注意，单元格E11等于单元格C3中的最大值。
设置直方图的数据
F列包含将在新直方图中显示的数据。我们将使用FREQUENCY函数生成它。
为此，请首先选择范围F3：F11，然后在公式栏中输入以下公式：
= FREQUENCY（A1：A10000，E3：E11）
现在，数组输入此公式。也就是说，按住Ctrl和Shift键，然后按Enter。
完成后，您应该具有与上图所示数据类似的模式。也就是说，列F中公式中间的单元格应显示的数字比两端的单元格大得多。
创建直方图
直方图
测试的最后一步是创建直方图，如下所示。
这样做，请确保仍选择范围F3：F11，然后选择“插入”，“图表”，“柱形图”，“聚集列”。
完成后，Excel将创建类似于此图表的图表。如果您使用的是Excel 2007或2010，则图表可能不包含标题。
您当然可以使此图表看起来更好，但这不是测试所必需的。该图表很容易说明我们通过组合NORM.INV和RAND函数完成了所需的工作：现在，我们有了一种从正态分布返回随机数的方法。
在 如何使用Excel数据表创建蒙特卡洛模型和预测中，我向您展示了如何在此基础上使用蒙特卡洛方法创建概率模拟。
原创文章如转载请注明：转载自Excel学习网 [ http://www.excelxx.com/]
需要保留本文链接地址：http://www.excelxx.com/jiqiao/10770.html
需要保留本文链接地址：http://www.excelxx.com/jiqiao/10770.html
相关教程：
- → Excel数据表创建蒙特卡洛-总结蒙特卡洛分析-Excel学习网
- → Excel数据表创建蒙特卡洛-完成统计表-Excel学习网
- → Excel数据表创建蒙特卡洛-设置随机模型-Excel学习网
- → Excel数据表创建蒙特卡洛-设置数据表-Excel学习网
- → Excel数据表创建蒙特卡洛-命名数据表列-Excel学习网
- → Excel数据表创建蒙特卡洛-蒙特卡洛预报概述-Excel学习网
- → Excel数据表创建蒙特卡洛-汇总数据表的结果-Excel学习网
- → Excel数据表创建蒙特卡洛-创建直方图-Excel学习网
- → Excel数据表创建蒙特卡洛-创建预计的净利润部分-Excel学习网
- → 如何限制EXCEL中数据的位数-创建关键百分位数部分-Excel学习网
经典Excel学习视频及教程推荐：
Excel优秀工具推荐：
小工具推荐：