
蒙特卡罗是摩纳哥的一个赌城,蒙特卡罗模拟最初起源于一个游戏。在一个边长为1的正方形内有个不规则图形,如图7-11所示。如何算出这个不规则图形的面积呢?图7-11正方形内的不规则图形现在让一群小孩拿针往这个正方形里投,投的次数越多越好。我们记录投在正方形内的有多少针,其中有多少针投在了这个不规则图形内。然后用落在不规则图形内的数量除以落在正方形内的数量,就得到了这个不规则图形的面积。这就是数学史上的著名游戏——投针试验。蒙特卡罗模拟是20世纪40年代随着科学技术的发展而发明的非常重要的计算方法。它以统计理论为指导,使用随机数或伪随机数来解决预测问题,特点是万次情景仿真模拟,随机变量全值估计,概率结果完全涵盖,预测风险精确度量,在工程、计量、经济学等众多领域有着广泛应用。对于A、B、C、D四项作业的作业量的可能值及其概率,下面基于Excel电子表格介绍蒙特卡罗模拟的步骤和结果。第一步在工作表中录入各作业的作业量及相应概率,计算累计概率,填写对应随机数,如表7-7所示。表7-7各作业的作业量和相应概率操作说明:累计概率,即概率的顺序累计。对应随机数,即上一行累计概率乘以100。第二步在工作表中设置表格,定义表格单元格公式,如表7-8所示。表7-8设置表格并定义公式单元格公式如下:A14:=RAND()×99B14:=VLOOKUP(A14,$C$3:$D$9,2)C14:=RAND()×99D14:=VLOOKUP(C14,$H$3:$I$9,2)E14:=RAND()×99F14:=VLOOKUP(E14,$M$3:$N$9,2)G14:=RAND()×99H14:=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)÷20B5020:=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-12所示。图7-12利润点区间概率及正态模拟单元格公式如下:D5017:=B5017D5018:=$B$5017+$B$5019÷3D5019:=$B$5017+2×$B$5019÷3D5020:=$B$5017+3×$B$5019÷3......D5076:=$B$5017+59×$B$5019÷3D5077:=$B$5017+60×$B$5019÷3E5017:E5077区域,录入数组公式:=FREQUENCY(I14:I5014,D5017:D5077)÷5000,按Ctrl+Shift+Enter。F5017:=NORMDIST(D5017,$B$5016,$B$5020,0)选择F5017,向下填充至F5077选择D5016:F5077区域,可生成利润点区间概率和正态模拟图形。1.操作说明概率不是某利润数据点的概率,而是利润区间的概率,如表7-10所示。表7-10各利润区间及相应概率2.函数说明(1)关于Frequency函数功能:以一列垂直数组返回某个区域中数据的频率分布。语法:Frequency(data_array,bins_array)参数:data_array:为一数组或对一组数值的引用,用来计算频率。bins_array:为间隔的数组或对间隔的引用,该间隔用于对data_array中的数值进行分组。(2)关于Normdist函数功能:返回指定平均值和标准偏差的正态分布函数。语法:Normdist(x,mean,standard_dev,cumulative)参数:x:为需要计算其分布的数值。mean:分布的算术平均值。standard_dev:分布的标准偏差。cumulative:为一逻辑值,指明函数的形式。第五步可对任意指定的利润区间计算概率,如表7-11所示。表7-11对任意指定的利润区间计算概率单元格公式如下:C5104:C5106区域,录入数组公式:=FREQUENCY(I14:I5014,B5104:B5106)÷5000,按Ctrl+Shift+Enter。A5108:="预期利润在"&(ROUND(B5104,2))&"和"&(ROUND(B5105,2))&"之间的概率为"&(ROUND(C5105×100,2))&"%"1.操作说明B5104:为用户任意指定的利润下限B5105:为用户任意指定的利润上限在Excel电子表格中,每按一次F9,数据则全部重新模拟,计算过程中的数据及相应的计算结果将发生变化,表格和图形将发生变化。但无论如何变化,数据范围基本不变,图形形态基本不变。实现该功能,Excel电子表格要安装分析工具库。03版在“工具-加载宏”界面,07及以上版本在“Office按钮-Excel选项-加载项”界面。2.函数说明关于Round函数功能:返回某个数字按指定位数取整后的数字。语法:Round(number,num_digits)参数:number:需要进行四舍五入的数字。num_digits:指定的位数,按此位数进行四舍五入。