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

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

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

蒙特卡罗模拟是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-12所示。

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

单元格公式如下：

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(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：指定的位数，按此位数进行四舍五入。