Excel数据表创建蒙特卡洛-汇总数据表的结果-Excel学习网

1. Excel数据表创建蒙特卡洛-汇总数据表的结果-Excel学习网

我们将在数据表右侧的区域（此处显示的区域）中汇总结果。




蒙特卡洛预测的数据表
首先，输入如图所示的标签。然后，使用“创建名称”对话框将范围为I1：I3的标签分配给它们右侧的单元格。然后对M1：M2范围内的标签执行相同的操作。
从单元格I7和M7开始的Seq（序列）列对要汇总数据的单元格数量进行计数。使用“序列”对话框（“主页”，“编辑”，“填充”，“序列”）可以为两组序列设置从1到21的序列。
现在输入在前三行中找到的这些公式：
J1：  = ROUNDUP（MAX（利润），0）
J2：  = ROUNDDOWN（MIN（利润），0）
J3：  = COUNT（I：I）
N1：  = ROUNDUP（MAX（销售），0）
N2：  = ROUNDDOWN（MIN（销售），0）
上图底部的两个“频率”部分生成了我们将用于报告中直方图的数据。
ProfBins列包含定义利润箱的开始和结束值的数据。这是本专栏中的前两个公式：
J7：  = MinProfits
J8：  =（MaxProfits-MinProfits）/（NumBins-1）+ J7
将单元格J8中的公式向下复制到单元格J27中。请注意，单元格J27中的值大约等于MaxProfits值。
现在，选择范围J6：K27，并使用“创建名称”对话框将该范围顶部的标签分配为两列的名称。
同样，这是SalesBins列中的前两个公式：
N7：  = MinSales
N8：  =（MaxSales-MinSales）/（NumBins-1）+ N7
将单元格N8中的公式向下复制到单元格N27。N27单元格中的值应近似等于MaxSales值。
和以前一样，选择范围N6：O27，并将选择顶部的标签分配给选择顶部一行下方的两列。
现在，我们可以使用FREQUENCY函数返回在每个Profit Bin中找到的项目数。
首先，选择范围K7：K27。然后输入以下公式：
= FREQUENCY（利润，利润）
现在，按住Ctrl和Shift，然后按Enter。该组合键可在所选范围内输入公式。并且此公式返回利润范围内属于ProfBins范围内每个分类的值的数量。
同样，选择范围O7：O27，然后数组输入此公式：
= FREQUENCY（Sales，SalesBins）
现在，对我们的结果进行汇总，在创建蒙特卡洛预测之前，我们还必须采取进一步的措施。

2. Excel数据表创建蒙特卡洛-总结蒙特卡洛分析-Excel学习网

在考虑将蒙特卡洛分析应用于您自己的数据的方法时，您可能会想知道如何才能减少预测中的不确定性。再次查看“统计数据表”可以指明出路。


蒙特卡洛分析的统计表
黄色单元格显示了我们对每个关键假设所期望的最大值和最小值的诚实看法。因此，如果要减少预测中的不确定性，则需要找到切合实际的方法来缩小每个最大值和最小值之间的距离。
但是不幸的是，要做到这一点，您将需要花费更多的时间和精力来了解过去影响这些项目性能的因素以及将来可能影响性能的程度以及程度。
祝你好运！

3. Excel数据表创建蒙特卡洛-设置数据表-Excel学习网

现在，我们将建立一个数据表。
该表将自动重新计算Excel，返回该图第2行中命名的项目的值，在第4行中记录这些值，重新计算，在第5行中记录当前项目，依此类推...直到最后一行表。
要开始数据表，请在您的Monte Carlo工作簿中添加一个新表并将其命名为Data。然后输入标签，这些标签在上图中以粗体显示。


Seq（序列）列之所以方便，有几个原因。要创建列...
在单元格B4中输入值1。
在选中单元格B4的情况下，按Ctrl + Shift +向下箭头以选择B列中最后一个单元格中的单元格B4。
选择“主页”，“编辑”，“填充”，“系列”以启动“系列”对话框。
接受默认的步进值1，但输入5000作为终止值。
选择确定。
现在您有了一个具有5000行的表的开头。
现在，在显示的单元格中输入以下公式：
C3：  = m.NetProfit
D3：  = m。销售
E3：  = m.PctCOGS
F3：  = m.OpExp
G3： = m.TaxRate
现在是时候设置数据表了。这样做之后，Excel将计算工作簿5000次，因为数据表将包含5000行。每次计算后，完成表的每一行都将包含返回到该图的第3行的值。
但是，在启动数据表之前，请将计算选项设置为“手动”。为此，请选择“公式”，“计算”，“计算选项”，“手册”。通过执行此步骤，请确保仅在按F9键或保存工作簿时才计算数据表。
现在，要设置表格，使其显示从下图的单元格C4开始的结果...
1.选择单元格B3。
2.按Ctrl + Shift + DownArrow将Seq列向下滑动到表格的第5000行。
3.按Ctrl +。（句号），但似乎什么也没做...
4.按住Shift键，然后按一次向右 箭头。这样做时，Excel会将选择范围扩大一列，并且还将显示表格的顶部，如此5.处所示，而不是底部。（这就是为什么您按下Ctrl加点号键的原因。）现在，点按Shift + RightArrow几次，直6.到通过单元格G3选择了第3行中的所有计算。 数据表的顶部
7.确保关闭所有其他工作簿，否则，接下来的几个步骤可能会花费很长时间。
8.选择数据，数据工具，假设分析，数据表。
9.在“数据表”对话框中，单击“列输入单元格”编辑框；单击表格区域之外的任何空白单元格；然后选择确定。



如果您忘记关闭其他工作簿，则可以按Esc键中断数据表的计算。
大概十秒钟的延迟后（假设您没有按Esc键），您的数据表应已完成。
这是发生了什么...
严格来说，数据表旨在在模型用于每次计算的一个或两个单元格中输入一个值。但是在这种情况下，您的模型将忽略在空白单元格中输入的值。相反，它仅在每次写入新值时才重新计算模型。由于模型包含随机数，因此模型的值每次都会更改。然后，数据表将捕获我们使用第3行中的公式指定的结果。

4. Excel数据表创建蒙特卡洛-命名数据表列-Excel学习网

这是完成的数据表的顶部。为了方便引用该表的列，我们需要为其命名。
要命名列，我们不能使用“创建名称”对话框，因为我们需要从定义的名称中排除第3行。因此...
1.选择单元格C4。
2.按Ctrl + Shift +向下键可将选择范围扩展到表格的底行。当您这样做时，Excel将显示在表格的底部。
3.按Ctrl + Back显示活动单元格，仍然是单元格C4。
4.按Ctrl + Alt + F3组合键，或选择“公式”，“定义的名称”，“定义名称”以启动“新名称”对话框，该对话
5.框应将所选范围显示为默认的“引用范围”。
6.要为该利润范围命名，请在“名称”框中键入名称，然后选择“确定”。


现在，对上图中显示的其他列（从Sales到TaxRate）重复此过程。

5. Excel数据表创建蒙特卡洛-蒙特卡洛预报概述-Excel学习网

该图显示了迄今为止完成的工作所产生的预测。每次重新计算工作簿时，它可能会稍有变化...但不应有太大变化。



完成的蒙特卡洛预测。
预测分为四个部分...
该预测净利润显示了简单的利润表，其采用的平均值所显示的每个项目。
“ 关键百分位数”部分显示了销售和利润的可能值。它表明销售和利润有25％的机会等于25％行中显示的值或更少。它表明销售和利润有50％的机会等于50％行中显示的值，或者更少...等等。
同样，“ 损失百分位数”值表明有17％的机会发生损失。
该销售直方图显示的销售业绩，我们的模型进行的5000个销售预测之间的分配。
该利润直方图显示了5000所盈利预测的分布。
现在让我们创建此报告...

6. Excel数据表创建蒙特卡洛-设置随机模型-Excel学习网

该图说明了我们将使用的模型。假设数据列中的四个公式均引用您刚刚在统计表中命名的四个值之一。


蒙特卡洛模拟的随机模型
要创建此图，请在您的Monte Carlo工作簿中添加一个新工作表，并将其命名为Model。
现在，在D列中输入以下公式：
D5： = c。销售
D6：  = c.PctCOGS
D9：  = c.OpExp
D12：  = c.TaxRate
金额部分中的公式取决于D列中的假设：
G5：  = D5
G6：  = D6 * G5
G7：  = G5-G6
G9：  = D9
G10：  = G7-G9
G12：  = D12 * G10
G13：  = G10-G12
最后，要完成该图，请使用“创建名称”对话框将E列中的标签分配给它们左侧的单元格，然后对H列执行相同的操作。
请注意，每次您重新计算工作簿时，模型都会生成不同的结果。现在，我们需要自动捕获这些结果，以进行许多重新计算。

7. Excel数据表创建蒙特卡洛-创建直方图-Excel学习网

这又是两个直方图。
这两张图汇总了产生顶图所示销售结果和底图所示利润结果的计算数量（总计5000）。
您可以在“销售频率”和“利润频率”部分的“数据”工作表中找到这些图表的数据。
从逻辑上讲，您首先要通过选择SalesItems列底部的范围N6来创建销售直方图，如下所示...





蒙特卡洛预测的“销售频率”表。
...然后选择“插入”，“图表”，“柱形图”，“ 2D”，“聚簇的列”。但是，如果这样做，Excel不会将SalesBins列视为X轴的数据。相反，它将SalesBins视为第二个数据系列，并绘制数据的两列。
我发现解决此问题的最简单方法是为Excel提供一个线索，让您将SalesBins列用作X轴，而不用作单独的数据系列。这样做，暂时从工作表中删除单元格N6中的标签。我这样做的方法是选择单元格N6，在编辑栏中选择文本，按Ctrl + X进行剪切，然后按Enter。
现在，您可以选择范围N6到SalesItems列的最后一个单元格，然后选择“插入”，“图表”，“柱形图”，“ 2D”，“聚集列”。当您这样做时，Excel将生成您期望的直方图。然后，在创建图表后，再次选择单元格N6，然后按Ctrl + v将SalesBins文本粘贴回该单元格中。
现在，您可以选择图表，按Ctrl + X进行剪切，选择“报表”工作表，然后将其粘贴到所需的位置。然后，您可以格式化图表使其漂亮。

8. 怎么用 Excel 做蒙特卡洛模Ƌ

下面是在Excel中模拟一只股票价格的例子。假设股票价格
的对数收益率服从正态分布，均值为0，每日变动标准差为0.1，
模拟股票价格1年的路径，过程如下：
用到两个内置函数，即用rand()来产生0到1之间的随机数，然后用norminv()来获得服从既定分布的随机数，即收益率样本=norminv(rand(), 0, 0.1)。假定股票价格的初始值是100元，那么模拟的价格就是 S=100 * exp(cumsum(收益率样本))。
其中的cumsum（）不是Excel的内置函数，其意思就是收益率样本的累积，每个时刻的值都是当前样本及此前所有样本的和，如，收益率样本从单元格C3开始，当前计算C15对应的模拟价格，则模拟价格计算公式是：100 * exp(sum($C$3:C15))。
由此可以得到股票价格的一条模拟路径。


其他非正态分布也可以通过类似方式得到分布的抽样，即分布函数的逆函数，这些函数Excel都内置了。所以，做蒙特卡洛模拟的时候，关键是先确定所需模拟的分布，然后进行抽样，然后应用层面的各种公式就可以在抽样的基础上进行计算了。


--------以下是补充的--------
根据上面提到的思路，其实可以很便捷地为期权做定价。下面就用蒙特卡洛方法为一个普通的欧式看涨期权定价（蒙特卡洛在为普通期权plain vanilla option定价时不占优势，因为相对于解析法而言计算量很大。但是，如果要给结构比较复杂的奇异期权定价时，可能蒙特卡洛法就比较实用，有时可能成为唯一的方法）。

1）假设这个期权是欧式看涨期权，行权价格为50元，标的股票当前的价格也是50元，期权剩余时间是1天。
2）假设标的股票的价格服从对数正态分布，即股票的每日收益率服从正态分布，均值为0，每日标准差为1%。

根据分布假设，首先用rand()函数产生在0到1之间的均匀分布样本。为了提高精确度，这里抽样的数量为1000个（其实1000个是很少的了，通常需要10万个甚至50万个，但是在Excel表格中操作这么多数字，不方便，这是Excel的不足之处）。
下一步，用norminv(probability, mean, std)函数来获得股票收益率分布的1000个抽样，其中的probability参数由rand()产生的抽样逐个代入，mean=0.0, std = 0.01。注意这里抽样得到的日度收益率。也就是说，这个样本对应的下一个交易日股票价格的收益率分布。
下一步，股票价格=50×exp(收益率样本)，得到股票价格分布的抽样，有1000个样本。


根据我做的实验，这1000个样本的分布图形（histogram）跟对数正态分布是比较接近的，如下图所示：
图的横轴是股票价格，纵轴是样本中出现的频率。
得到了股票价格未来一天分布的样本之后，就可以以此样本来计算期权的价格了。
欧式看涨期权的定义为：
C=max(S-K,0)


所以，根据这个计算公式可以计算出在到期那天在特定的价格下期权的价值。在Excel中，相当于 期权价值=max(股票价格样本 - 50，0)。由此就可以得到了该期权未来1天价值的样本。
然后，将未来价值贴现回来（用无风险利率贴现，假设无风险利率为0.05，则贴现公式是=exp(-0.05/360）×期权价值，得到期权价格的1000个样本。
最后，对期权价格的1000个样本求平均，Excel函数average(期权价格样本)，就可以得到期权的价格了。
我这里算出来的是：0.2015元。
而根据Black-Scholes期权定价公式算出来的理论价格则是0.2103元。二者比较接近，但是还是有差距。


而且，每次刷新Excel表格，就重新做一次模拟，得到的模拟价格变动比较大，有时是0.2043元，有时是0.1989元。由于这个抽样的数量比较小（1000个样本），所以估算的结果受到样本的影响会比较大。如果把抽样数量提高100倍甚至500倍，那么样本变动的影响可能会小一个或者两个数量级。但是计算量就大了，如果计算机性能不够高，那么利用Excel来做的话，比较困难。
这就是我的工作台：

------ 再来一个 --------
看到有人提到利用蒙特卡洛方法来估计圆周率Pi，挺有意思，也简单，所以就在Excel中做了一个实验。
基本原理在于在直角坐标系中的第一个象限中的一个单位圆，如下图所示：
在这个面积为1的正方形中，有四分之一的圆，圆的半径与正方向的边长都是1。那么根据圆的面积公式，这个图形中阴影部分的面积应该是 Pi/4。
下面开始进入蒙特卡洛的解法。
即，如果我们对这个正方形平面中的点进行均匀地抽样，随着抽样点的增多，那么落入阴影内的点的数量与总抽样数量的比，应该基本上等于阴影的面积Pi/4与整个正方形面积1的比，即Pi/4。用数学表示，就是
阴影内的样本点数量 ÷ 总数量 = Pi/4
所以，Pi = 4 × 阴影内的样本点数量 ÷ 总数量。


下面就在Excel中进行实验。
用rand()函数生成2000个随机数，作为随机样本点的X轴坐标，
再用rand()函数生成2000个随机数，作为随机样本点的Y轴坐标。
如此就得到了2000个随机样本点，这些点的X轴坐标和Y轴坐标都大于零且小于1，所以是在前面所说的正方形之中的点。
下一步，判断样本点是否处于阴影之内，由于这个阴影就是单位圆在直角坐标系第一想象的四分之一，所以圆阴影内的点都符合如下不等式：
翻译到Excel中，就是用IF函数来判断，例如：
IF(A2^2 + B2^2 <=1, 1, 0)


即，如果样本点在阴影中，得到1，否则得到0。这样就把样本点区分开来了。
最后，把所有得到的1和0加总，就知道所有样本点中处于阴影中样本点的数量了。
最后根据
Pi = 4 × 阴影内的样本点数量 ÷ 总数量
就可以算出Pi来了。
我这个试验中算出来的 Pi=3.142。
以下是样本点的散点图：
由于样本数量有限，所以计算出来的Pi的精度并不高。
以下是工作界面，挺简单的。
来源：知乎