Excel 中的蒙特卡罗模拟：完整指南

为何用 Excel 进行蒙特卡罗模拟？

一旦决定实施蒙特卡罗模拟，您可以选择多种工具来完成模拟，例如 Excel、Python、R、SAS 和 MATLAB。

尤其是在首次实施蒙特卡罗模拟时，最重要的考量因素是您对工具的整体熟悉程度。Excel 是商业世界中使用最广泛的工具之一，这意味着许多人已经熟悉其基本操作。这可缩短培训时间，免去从零学习新软件的需求。

Excel 还提供易用的图表与图形工具，有助于可视化模拟结果。此外，Excel 还可通过多个强大的外接程序来增强其执行复杂蒙特卡罗模拟的能力。

不过同样值得注意的是，对于更高级的模拟，尤其是需要处理大型数据集或运行数量极多的模拟时，使用 Excel 以外的更专业工具可能更为合适。

蒙特卡罗中的关键 Excel 函数

接下来，我们将介绍两个关键的 Excel 函数：RAND() 与 NORM.INV()，涵盖其语法、参数和典型用例。这些函数用于生成随机数并定义概率分布，是任何模拟的基本要素。

RAND() 函数

RAND() 会生成一个大于等于 0 且小于 1 的随机数。这些数均匀分布，意味着在指定范围内任意数出现的可能性相同。

RAND() 的语法如下：

RAND()技术支持

RAND() 函数不需要任何参数。直接以 RAND() 的形式使用。

在蒙特卡罗模拟的语境中，RAND() 可用于模拟随机事件的发生，或用于改变模型的输入。

NORM.INV() 函数

虽然 RAND() 生成的是均匀分布的随机数，NORM.INV() 则用于从正态分布中生成随机数，这在蒙特卡罗模拟中十分常见。该函数返回指定均值与标准差下的正态累计分布的反函数值。

NORM.INV() 函数的语法如下：

NORM.INV(probability, mean, standard_deviation)技术支持

参数说明：

• probability：对应正态分布的概率，取值必须在 0 到 1 之间。通常由 RAND() 函数生成。

• mean：算术平均数，即该正态分布的均值。

• standard_deviation：正态分布的标准差，衡量数据围绕均值的离散程度。

NORM.INV() 用于将 RAND() 函数产生的均匀分布随机数转换为服从指定正态分布的数值。这对于建模那些预期会沿正态曲线呈现自然变动的变量十分有用。

现在我们已经具备了蒙特卡罗模拟所需的构件、函数与概念，接下来就在 Microsoft Excel 中实现一个示例。

在 Microsoft Excel 中实现蒙特卡罗模拟：示例

设想这样一个情景：您是一家充满活力的消费电子公司的数据分析师，被指派评估推出一款新的可穿戴健身追踪器的财务可行性。

此类设备的市场竞争激烈，消费者需求波动较大，受季节趋势、营销效果和竞争对手行动的影响。此外，制造这些设备的成本也会因材料成本变化和供应链不确定性而波动。

您决定在 Excel 中使用蒙特卡罗模拟来应对这些挑战。您认为这种方法将帮助您在不同情景下估计潜在盈利能力，从而支持公司就定价策略、产量和营销投入做出明智决策。

您还分析了消费电子行业内类似产品发布的历史数据和市场研究。通过这项分析，您确定了一些指标以指导模拟：

• 新设备在上市第一年的平均需求为 10,000 台，标准差为 2,000 台，反映消费者采纳的不确定性。
• 单价通常介于 50 至 70 美元之间，取决于竞争性定价与市场饱和度。
• 单台成本受材料价格波动和制造效率影响，平均为 30 美元，标准差为 5 美元。

这些历史数据构成了您的模拟参数的基本假设，帮助模拟更准确地反映当前市场状况。

针对本示例，您可以按照以下步骤来实施蒙特卡罗模拟：

步骤 1：设置 Excel 工作表

首先，准备 Excel 工作表，包含每个变量的列以及用于计算利润的列。

初始视图如下：

设置 Excel 工作表。

步骤 2：为变量输入公式

在每一行中，根据您确定的分布输入公式来生成需求、销售价格和成本的随机值：

• 需求：正态分布（均值 = 10,000 台，标准差 = 2,000 台）
• 销售价格：均匀分布（50 至 70 美元）
• 成本：正态分布（均值 = 30 美元，标准差 = 5 美元）

逐一输入这些公式时，选中单元格 A2 并输入：

=NORM.INV(RAND(), 10000, 2000)技术支持

上述公式将按如下所示创建具有给定均值和标准差的正态分布：

创建需求分布。

接着，选中单元格 B2 并输入：

=50 + (70-50) * RAND()技术支持

上述公式将为销售价格创建 50 至 70 美元之间的均匀分布，如下所示：

创建销售价格分布。

选中单元格 C2，并输入：

=NORM.INV(RAND(), 30, 5)技术支持

与需求公式类似，上述公式将创建具有给定均值和标准差的正态分布，如下所示：

创建成本分布。

步骤 3：计算因变量

现在，计算利润（因变量）。在 D 列使用以下公式对每次模拟进行计算：

=(B2 - C2) * A2技术支持

计算利润。

步骤 4：向下填充以模拟多个情景

到目前为止，我们只创建了单次模拟。现在将其扩展为多次，例如一千次模拟。

选中 A2 到 D2 的单元格，拖动填充柄（选区右下角的小方块）向下填充公式，直至您希望模拟的行数（例如 1000 行对应 1000 次模拟）。

效果如下所示：

创建模拟。

步骤 5：分析结果

运行模拟后，您可以使用最小值、最大值、平均值和标准差等统计函数来分析结果。需要时不妨快速查阅Excel 速查表，回顾我们接下来将使用的内置函数。

要计算每月预期的平均利润，在某个单元格（例如 G6）中输入：

=AVERAGE(D2:D1001)技术支持

要计算每月预期的最小利润，在某个单元格（例如 G7）中输入：

=MIN(D2:D1001)技术支持

要计算每月预期的最大利润，在某个单元格（例如 G8）中输入：

=MAX(D2:D1001)技术支持

要计算利润的标准差，在某个单元格（例如 G9）中输入：

=STDEV.P(D2:D1001)技术支持

执行后，Excel 表应类似如下：

分析模拟结果。

我们可以如下解读对产品发布的估计结果与影响：

• 平均利润表示推出新款健身追踪器的预期利润。它表明，在给定假设下，每次模拟的平均预测利润约为 298,278.67 美元。该数值可作为盈利能力的中心估计。
• 最小利润为 67,598.78 美元，是所有模拟中观察到的最低利润。它代表在模型假设下的最坏情形，虽然仍有盈利，但明显低于平均水平。这可能源于该次模拟中需求特别低或成本条件不利。
• 最大利润为 641,955.42 美元，代表最佳情形，即在所有模拟中需求与价格可能处于较高水平、成本处于较低水平。这显示了在条件极为有利时的潜在上行空间。

鉴于最小与最大利润之间的跨度较大且标准差可观，推出新产品所面临的财务风险不容小觑。

决策者应考虑公司是否能接受这样的不确定性水平，以及利润低于平均值的可能性。

此外，虽然不是必需，但我们鼓励您创建如直方图等可视化，以直观理解模拟结果。

改进 Excel 蒙特卡罗模拟的技术

当您重新运行上述同一模拟时，可能会观察到计算结果略有差异，如下所示：

模拟结果的变化。

这是因为原始模拟中的取值会在不同迭代间变化，从而影响估计结果。尽管波动很小，但当估计值发生变化时，决策者可能会对模拟的准确性与可靠性产生疑虑。

下面探讨几种可用于提升模拟准确性与可靠性的高级技术。

增加模拟次数

运行更多次数的模拟有助于平滑随机波动，从而提供更稳定、更准确的结果估计。

对应上述示例，我们可以增加模拟运行次数（例如从 1,000 次提高到 10,000 次或更多），尤其是在参数波动较大的情况下。

确定“合适”的模拟次数取决于多种因素。

模型越复杂（变量越多、相互作用范围越广），通常需要更多模拟来覆盖所有可能结果，确保结果并非偶然所致。

如果输入的波动性较高或偏度较大，则需要更多模拟来准确估计结果分布的尾部（极端值）。

对于更精细的分析，尤其在金融或风险管理中，运行 10,000 至 100,000 次模拟并不罕见。该范围通常用于确保在多种情景与输入下获得稳健结果。当然，正如前文所述，对于如此大规模的分析，Excel 并非总是最佳工具，R 或 Python 往往更适用。

优化输入分布

模拟的准确性在很大程度上取决于输入概率分布能否真实反映底层变量的不确定性与行为。在上述示例中，我们假设需求与成本服从正态分布，销售价格服从均匀分布。

此外，我们可以分析更全面的历史数据，以更好地参数化分布；结合领域专家的意见，更好地理解成本、售价与需求对外部因素的响应；也可考虑使用对数正态、Beta、Gamma 等分布，或基于经验数据构建自定义分布。

进行敏感性分析

敏感性分析通过在保持其他输入不变的情况下系统性地改变某一输入，来了解哪些输入变量对输出影响最大。

在上述示例中，我们可以保持两个变量不变，改变另一个变量的分布来观察估计结果变化。然后对其余两个变量依次重复该过程。最终，这一技术有助于明确应将精力优先投入在哪些变量上以提升准确性。

迭代采用上述技术并分析结果，可获得更准确、可靠的结论。

结论

本教程向您介绍了蒙特卡罗模拟及相关统计概念。在介绍相关 Excel 函数之后，教程通过一个真实示例提供了在 Excel 中实施蒙特卡罗模拟的分步指南。

最后，您还了解了一些最佳实践与高级技巧，以确保结果更加准确、可靠。

如果您希望使用其他工具（如 Python 或 R）来实现上述蒙特卡罗模拟，以下两份资源将有所帮助：

• R 中的蒙特卡罗模拟
• Python 中的蒙特卡罗模拟

或者，如果您希望继续使用熟悉的 Microsoft Excel，并想精进这款广泛采用的工具，欢迎查看我们的Excel 基础学习路径。