range_lookup）

　　=2147元

　　期望值法有着很大的缺陷，就是它对各种各样的可能只给出了唯一一个预测结果，这个预测结果经常与实际并不相符但又无法解释不相符的原因。

　　二、蒙特卡罗模拟

　　蒙特卡罗是摩纳哥的一个赌城，蒙特卡罗模拟最初起源于一个游戏。在一个边长为1的正方形内有个不规则图形如图7-12所示。如何算出这个不规则图形的面积呢？

　　图7-12正方形内的不规则图形

　　现在让一群小孩拿针往这个正方形里投，投的次数越多越好。我们记录投在正方形内的有多少针，其中有多少针投在了这个不规则图形内。然后用落在不规则图形内的数量除以落在正方形内的数量，就得到了这个不规则图形的面积。这就是数学史上的著名游戏——投针试验。

　　蒙特卡罗模拟是20世纪40年代随着科学技术的发展而发明的非常重要的计算方法。它以统计理论为指导，使用随机数或伪随机数来解决预测问题，特点是万次情景仿真模拟，随机变量全值估计，概率结果完全涵盖，预测风险精确度量，在工程、计量、经济学等众多领域有着广泛应用。

　　对于A、B、C、D四项作业的作业量的可能值及其概率，下面基于Excel电子表格介绍蒙特卡罗模拟的步骤和结果。

　　第一步

　　在工作表中录入各作业的作业量及相应概率，计算累计概率，填写对应随机数如表7-7所示。

　　表7-7各作业的作业量和相应概率【/图说】

　　操作说明：

　　累计概率，即概率的顺序累计。

　　对应随机数，即上一行累计概率乘以100。

　　第二步

　　在工作表中设置表格，定义表格单元格公式如表7-8所示。

　　表7-8设置表格并定义公式【/图说】

　　单元格公式如下：

　　A14：=RAND（）×99

　　B14：=VLOOKUP（A14，$C$3：$D$9，2）

　　C14：=RAND（）×99

　　D14：=VLOOKUP（C14，$H$3：$I$9，2）

　　E14：=RAND（）×99

　　F14：=VLOOKUP（E14，$M$3：$N$9，2）

　　G14：=RAND（）×99

　　H14：=VLOOKUP（G14，$R$3：$S$7，2）

　　I14：=4×B14+3×D14+2×F14+5×H14-1000

　　选择A14：I14区域，向下填充至A5014：I5014。

　　1.操作说明

　　A14、C14、E14、G14单元格公式，表示在1和100之间随机取数。

　　B14、D14、F14、H14单元格公式，表示按随机数取各作业的作业量，并使各作业的作业量按相应的概率出现。

　　I14单元格公式，表示按各作业的作业量计算利润。

　　向下填充5000行，表示模拟5000次。

　　2.函数说明

　　（1）关于Rand函数

　　功能：返回大于等于0及小于1的均匀分布随机数，每次计算工作表时都将返回一个新的数值。

　　语法：Rand（）

　　参数：

　　若要生成a与b之间的随机实数，使用：Rand（）×（b-a）+a

　　（2）关于Vlookup函数

　　功能：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。

　　语法：Vlookup（lookup_value, table_array, col_index_num, range_lookup）

　　参数：

　　lookup_value：为需要在数组第一列中查找的数值。

　　table_array：为需要在其中查找数据的数据表。

　　col_index_num：为table_array中待返回的匹配值的列序号。

　　range_lookup：为一逻辑值，指明函数vlookup返回时是精确匹配还是近似匹配。

　　第三步

　　计算利润的各项统计指标如表7-9所示。

　　表7-9利润的统计指标【/图说】

　　单元格公式如下：

　　B5016：=AVERAGE（I14：I5014）

　　B5017：=MIN（I14：I5014）

　　B5018：=MAX（I14：I5014）

　　B5019：=（B5018-B5017）÷20

　　B5020：=STDEV（I14：I5014）

　　1.操作说明

　　蒙特卡罗模拟在本次模拟中计算的利润平均值为2141，与期望值法计算的预期利润2147相差不大，但期望值法只提供了一个利润结果，蒙特卡罗模拟可提供所有的利润结果及相应的概率。

　　2.函数说明

　　（1）关于Average函数

　　功能：返回参数的平均值（算术平均值）。

　　语法：Average（number1，number2，……）

　　参数：

　　number1，number2，……：为需要计算平均值的1到30个参数。

　　（2）关于Min函数

　　功能：返回一组值中的最小值。

　　语法：Min（number1，number2，……）

　　参数：

　　number1，number2，……：是要从中找出最小值的数字参数。

　　关于Max函数

　　功能：返回一组值中的最大值。

　　语法：Max（number1，number2，……）

　　参数：

　　number1，number2，……：是要从中找出最大值的数字参数。

　　关于Stdev函数

　　功能：估算样本的标准偏差。

　　语法：Stdev（number1，number2，……）

　　参数：

　　number1，number2，……：为对应于总体样本的参数。

　　第四步

　　按间距列举利润数据点，计算各利润数据点区间概率并进行正态模拟如图7-13所示。

　　图7-13利润点区间概率及正态模拟

　　单元格公式如下：

　　D5017：=B5017

　　D5018：=$B$5017+$B$5019÷3

　　D5019：=$B$5017+2×$B$5019÷3

　　D5020：=$B$5017+3×$B$5019÷3

　　……

　　D5076：=$B$5017+59×$B$5019÷3

　　D5077：=$B$5017+60×$B$5019÷3

　　E5017：E5077区域，录入数组公式：=FREQUENCY（I14：I5014，D5017：D5077）÷5000，按Ctrl+Shift+Enter。

　　F5017：=NORMDIST