excel求乘积怎么操作(基于Excel的蒙特卡洛仿真分析—报童问题为例)

引言蒙特卡洛仿真（Monte-Carlo Simulation），更多的是被称为随机数仿真，是系统仿真领域里面一个非常重要和非常基石的仿真分支说其是系统仿真领域中的一个基石的分支，是指蒙特卡洛仿真是当前几个主要仿真方法（如离散事件系统仿真、连续系统仿真、系统动力学仿真和多智能体仿真等）的基础。

区别于这些更加“流行”的仿真方法，蒙特卡洛仿真属于静态仿真，即仿真模型中并不包含任何时间因素或变量；而上面列举的几种仿真都属于动态仿真说其还是一个非常重要的仿真分支，是指其是风险分析中非常常用的分析工具。

一、蒙特卡洛仿真的“八卦”历史蒙特卡洛仿真名字中的“蒙特卡洛”，实际上是指一个以赌博业闻名的欧洲城市如果想了解有关这个城市的风景，可以从2011年的一部电影《Monte Carlo》中获得直观感受

蒙特卡洛仿真与这个城市的渊源，如果去追究的话，那么一定是因为蒙特卡洛城市的赌博行业老虎机、转盘等赌博游戏中存在着众多的不确定性因素和随机性因素，其与蒙特卡洛仿真所研究的风险分析有着直接和必然的联系因此，一个原本概率论中的枯燥分析方法被赋予了“漂亮”的名字，吸引和驱动着人们对其的研究工作。

有兴趣的朋友可以回看一下2008年的一部美国电影《21》，侧面验证了概率论为主的数学知识在风险、投资、投机领域中的重要性。这也是为什么诺贝尔经济学奖多数情况下颁给了数学家。

【花边】电影主角之一也主演过另一部讲述伟大数学家Nash生平的电影《美丽心灵（A Beautiful Mind）》虽然谈到仿真，我们默认都是以计算机为工具完成模型构建和仿真，即计算机建模与仿真但实际上，蒙特卡洛仿真思想的出现远远早于计算机出现的年代。

早在1777年，法国数学家Buffon就提出了“投针实验”用于计算圆周率，并且从数学上证明了方法的正确性现在回顾历史，我们常常把“投针实验”是蒙特卡洛仿真中的一个先驱应用当然，计算机的出现，使得“蒙特卡洛仿真”分析更加简单方便，不需要人手工去完成大量重复的（仿真）实验。

（在“投针实验”中，需要一个人重复投针，并同时纪录相关实验数据）二、蒙特卡洛仿真的数学基础蒙特卡洛仿真的核心是随机数（random number）和随机变量（random variable），主要是研究不确定情景下的应用问题，即风险分析。

从数学上说，蒙特卡洛仿真的模型基础是概率论，即比较常见的随机期望举例来说，一个折叠雨伞的贩卖小贩，每天在地铁口兜售10元一把的雨伞如果这天下雨，他能够卖出40把雨伞（挡雨）；如果这天不下雨，他仅能卖出5把雨伞（遮阳）。

对于海滨城市的夏季而言，假如明天下雨的可能性是40%，不下雨的可能性是60%，那么：小贩的一天“平均”收入/“期望”收入为—期望收入 = 0.40 * ( 40 * 10 ) + 0.60 * ( 5 * 10 ) = 190 元

在这个问题中，小贩一天收入的计算公式（模型）中，存在着一个不确定性因素，即天气而且，问题中，天气的不确定性“通过数据分析和统计”，采用了一个随机分布中的二项分布进行刻画，即例子中每天“下雨”状态发生的可能性是40%，“不下雨”状态发生的可能性是60%。

如果例子中只包含天气一个不确定性因素，概率论计算小贩的期望收益还比较简单但是，如果模型中包括了更多的不确定性因素，如下雨天小贩卖出去雨伞的数量是一个不确定性因素，晴天小贩卖出去的雨伞数量也是一个不确定性因素，那么小贩的收入计算就一点一点的变得更加复杂了。

期望收入 = 晴天的概率 * 晴天的销量 + 雨天的概率 * 雨天的销量进而，如果天气下雨与本地地区、季节有关，而雨伞的销量又与雨伞价格有关，那么期望收入就包含了更多的不确定性因素，分析就难以进行了由于这些不确定性因素在现实世界中多数经济问题、社会问题中普遍存在，蒙特卡洛仿真的应用价值就非常突出了。

而蒙特卡洛仿真就是研究这种复杂的随机性问题，即问题中包含较多的不确定性因素此研究方法指导下，将研究问题中的不确定性因素逐一用相互独立的随机数或随机变量刻画（x1,x2,x3,...），得到在这些自变量交互影响下的因变量模型（y），即：。

y = f ( x1 , x2 , x3 , ... )通过（计算机）仿真，我们能够得到在多个随机变量/不确定性因素共同作用下的仿真输出结果通过重复大量的仿真，我们就能得到研究问题的“期望输出EX(y)”。

在研究问题的仿真建模中，最为关键的是确定问题包含的不确定性因素的随机函数表达形式当确定了不确定性因素的随机函数后，研究问题的模型就相对简单在现实世界中，几种常见的随机函数包括：* 扔骰子、硬币的均匀分布Uniform，。

* 产品生产质量水平的正态分布Normal* 顾客到来的负指数函数分布Exponential和泊松分布Possion，* 产品生命周期的威布尔分布Weibull，* 项目完工的三角分布Triangular，等。

在蒙特卡洛仿真中，随机数其实是特指服从均匀分布的随机数字例如，一个骰子出现1-6数字的可能性是均等的，都是1/6的可能性而随机变量则是泛指服从某种具体分布的一个变量另外，注意到在雨伞贩卖的例子中，我们并没有考虑小贩今天没有卖出去的雨伞是可以明天接着卖的情景。

如果考虑到今天、明天天气变化的相关性，雨伞存货的相关性，那么问题中考虑的因素就成为了一个“动态仿真”问题了进而，如果小贩卖的不是雨伞，而是鲜花、水果，那么产品的保鲜期/保质期又加大了问题研究的难度三、Excel中的蒙特卡洛仿真工具

如上引例所述，蒙特卡洛仿真的核心“仅”在于随机变量的刻画（随机分布的确定设计到“概率与统计”中的“统计”技术，以及现在的大数据技术）在Excel中，有一个非常简单和方便使用的随机变量生成工具—“数据分析”。

工具这个工具同Excel中的规划求解工具一样，都是在“数据”菜单之下的一个小插件使用这个工具，能够非常方便的生成一些简单实用的随机分布当然，如果仿真中涉及更加复杂的随机分布，需要求助于更加专业的概率统计工具软件。

在Excel软件主菜单“数据”下找到并打开“数据分析”工具，将会出现如下所示的一个窗口。其中，包括了“随机数发生器”在内的多种数据分析基本工具。例如，相关系数、回归分析、方差分析等。

【注1】随机数发生器（random number generater, RNG）是指产生随机数的工具【注2】Mac操作系统下的Excel软件，部分版本不提供“数据分析”工具 !选中“随机数发生器”，点击。

“确定”按钮，得到主窗口如下：

如上所示，随机数发生器的输入内容非常简单，窗口内容主要包括以下四个部分：（1）随机变量的数量定义* 参数“变量个数”定义了蒙特卡洛仿真模型中具有相同随机分布的随机变量个数如果研究问题中随机变量服从相同的随机分布，并具有相同的模型参数，那么可以一次性生成多个随机变量。

这样效率更高回到卖伞小贩的例子中，模型中只包含1个随机变量，即天气因素* 参数“随机数个数”定义了一个随机变量生成多少个随机数字由于研究问题中带有随机因素，那么一定要重复多次仿真才能让结果更准确重复的次数就是这里的。

“随机数个数”定义内容这好比要想得到扔硬币实验中硬币正面出现的次数占比50%，那么扔1次，2次都不行，一定是要扔1万次或更多从蒙特卡洛仿真的视角，这表示一个二项分布的随机变量重复了1万次，即“随机数个数”。

为10000（2）随机变量的函数参数设置* 参数“分布”定义了随机变量的分布函数默认情况下，这个下拉列表框里给出的是“离散”的随机分布从这个下拉列表中，我们可以找到更加熟悉的分布形式，如均匀分布、正态分布、泊松分布等。

不同的随机分布，“参数”栏给出了这个分布函数的具体参数设置以熟悉的正态分布为例，如果我们在下拉列表框中选择了“正态”分布，那么下面“参数”栏就给出了正态分布函数的两个参数“平均值”和“标准偏差”。

从默认参数可以看出，这里给出的是一个标准正态分布，即平均值为0，标准偏差为1的正态分布随机变量Normal(0,1)（3）随机变量的输出位置/单元格* 参数“输出选项”给出了生成的随机数序列的存放位置在默认的情况下，数据有N列（N为。

“变量个数”设置值），M行（M为“随机数个数”设置值）例如，如果我们设置“变量个数”为1，“随机数个数”为15，那么就会生成1列、15行的数据（4）随机变量的输出定义* 参数“随机数基数”定义了随机变量生成算法中的一个重要参数，在有的地方也被称为。

“种子值（seed）”。

对于一个随机变量而言，如果设置“随机数基数”值相同，那么生成的随机变量（共“随机数个数”个数）都完全一样；反之，这两个随机变量值不一样以上面的标准正态分布随机变量Normal(0,1)为例，如果我们设置。

“随机数基数”为150，生成15个随机数。

得到的输出结果如下：

如果我们重复上面的操作，仅修改“随机数基数”为120。

【注】参数“输出区域”设置做了修改。得到的输出结果如下：

如图所示，采用不同“随机数基数”生成的两列数据，15个数据没有一个相同！但是，如果我们再重复一次上面操作，此时再次设置参数“随机数基数”为150。

再次得到随机变量如下。

此时，我们可以看到参数“随机数基数”均为150的两列数据，15个数字完全相同！甚至是，在不同的电脑上，在相同的Excel 2019版软件中，输入同样的参数（标准正态分布、变量为1、随机数个数为15、随机数基数为150），得到的15个数字与上面数据完全一样。

这就是参数“随机数基数”的意义四、Excel中经典报童问题的蒙特卡洛仿真运营管理、运筹学和库存管理中，有一个非常经典的随机库存问题，叫做报童问题（Newsvendor Problem）它是随机库存管理的基础模型，和确定库存管理中的。

经济订货批量模型EOQ（economic order quantity）具有相同的重要性有关两个模型的介绍，有兴趣的朋友可以查看相关教材报童问题可以形象的描述如下：一位卖新闻报纸的商人，每天凌晨会从“厂家”以批发价买进一批新闻报纸（数量假定为Q）。

接下来的一整天，商人将向市场提供这些报纸到了晚上，如果新闻报纸没有卖出去，这些报纸将成为废纸而被处理掉设定商人卖出一份报纸能够获得收益为G元；如果报纸没有被卖出去，那么商人将损失L元；每天市场上对该新闻报纸的需求服从函数为F的随机分布。

假定商人只能够在凌晨获得新闻报纸在白天的其他时间，其都不能够再次从“厂家”获得新闻报纸（即使是支付额外的费用）在这样的一个市场环境下，商人的最优订货量应该是多少？在报童问题中，商人的订货量Q与报纸的需求量D~F()之间的关系可以简单描述为：如果供大于求，商人会因为订多报纸而损失。

反之，如果供不应求，商人会因为订少报纸而损失（机会）因此，商人需要在订多和订少中（市场需求不确定性不由商人决定）找到一个最佳的平衡点，即最优的订货量问题采用数学模型去描述商人的收益情况R，其描述如下：IF Q > D

R = G * D - ( Q - D ) * LELSE  // ( Q < D )R = G * Q或者也用如下所示的函数来实现：R = MIN ( Q , D ) * G + MAX ( Q - D , 0 ) * L

对于这样的一个订货量决策问题/风险决策问题，我们可以采用蒙特卡洛仿真的方法进行分析下面给出其中的基础模型和分析过程（0）问题定义—假定产品的市场需求服从平均值为500，标准偏差为50的正态分布商人卖出一份报纸能够获得收益10元（G = 10），如果没有卖出去，其将损失5元（L = 5）。

如果商人的订货量为市场需求的平均值，即Q=500其（期望）收益如何？（1）Excel中模型设置—根据问题描述，首先在Excel中输入和定义参数如下：

为了计算方便，和以后重用（模型修改）方便，将问题中的参数定义放在固定的单元格中（2）Excel中生成正态分布随机数—在Excel中生成报纸市场需求的随机数，生成500个数字（设定 随机数基数 = 150）。

生成的随机数如下所示：

由于报纸数量为整数，而生成的数字为实数，于是采用Excel自带的函数ROUND(.)实现数据的四舍五入（函数ROUNDUP(.)或ROUNDDOWN(.)也可以实现数字的四舍五入操作，具体方式略有不同）。

操作过程如下所示：关闭观看更多更多退出全屏视频加载失败，请刷新页面再试

刷新

视频详情 （3）Excel中计算报纸的销售收入—当供不应求时，报纸的销售收入为订货量Q与单位收益G的乘积；而当供大于求时，报纸的销售收入为市场需求与单位收益G的乘积这个逻辑，可以用以下两种方式实现：。

IF Q < DR = Q * GELSER = D * G或者R = MIN ( Q , D ) * G第一种方式的Excel实现如下：

第二种方式的Excel实现如下：

操作过程如下所示：关闭观看更多更多退出全屏视频加载失败，请刷新页面再试

刷新

视频详情 （4）Excel中计算报纸的销售损失—当供大于求时，由于部分报纸未能销售出去，商人的损失（Q - D）为此部分报纸与单位损失L的乘积这个逻辑，同样可以用两种方式实现：。

IF Q > DC = ( Q - D ) * LELSEC = 0或者C = MAX ( Q - D , 0 ) * L第一种方式的Excel实现如下：

第二种方式的Excel实现如下：

操作过程如下所示：关闭观看更多更多退出全屏视频加载失败，请刷新页面再试

刷新

视频详情 （5）Excel中计算商人的净利润，即收入减去损失—

（6）Excel中计算商人的平均收益—在Excel中，用函数AVERAGE(.)计算500次重复仿真的结果，作为研究问题中订货量Q=500所对应的收益情况。

小结—通过以上操作介绍，我们可以非常容易的计算出订货量Q=500对应的收益但是，如果我们希望能够计算出最优的订货量Q*，以上模型和操作还不能实现这就是仿真分析相比数学分析的劣势之一，即不能够直接计算得到。

研究问题的最优解但是，仿真分析的优势也很明显如果一个问题过于复杂，包含的随机因素过多，数学分析就无能为力了此时，仿真模型能够实现问题求解五、蒙特卡洛仿真的特点小结如前面对于蒙特卡洛仿真、Excel中随机数发生器工具以及报童问题实例的介绍，我们可以对Excel实现蒙特卡洛仿真有以下几个初步认识：。

（1）蒙特卡洛仿真的核心在于随机数，而Excel的随机数发生器工具能够非常方面的实现这一点（2）蒙特卡洛仿真对随机问题进行研究，因此仿真实验要重复多次，如报童问题仿真中随机数个数为500并且，从概率统计的角度，重复次数越多，仿真结果“精度”越高，结果越准确。

（3）蒙特卡洛仿真只能实现研究问题的仿真分析，如报童问题仿真中计算订货量Q=500时的收益情况，但是其本身没有办法去求解最优解（需要借用Excel中的其它工具，如规划求解工具等）（4）蒙特卡洛仿真并不复杂。

应用问题的复杂性主要体现在研究问题分析时，不确定性因素的描述和量化，即数学模型的模型构建，和随机变量的函数刻画以及，仿真结果的分析，即如何根据重复仿真的结果得到一个正确、准确、精确的结果

亲爱的读者们，感谢您花时间阅读本文。如果您对本文有任何疑问或建议，请随时联系我。我非常乐意与您交流。