为何无法在Excel中实现蒙特卡洛嵌套模拟？求无VBA方案

问题核心 #

Excel的**模拟运算表（数据表格）**嵌套时，内层表不会随外层表的迭代重新计算——因为模拟运算表的计算逻辑是一次性生成所有结果，外层表迭代过程中不会触发内层表的刷新，导致所有返回值重复。

方案1：动态数组直接生成多组均值（Excel 365/2021+） #

利用Excel动态数组函数，无需数据表格，直接批量生成所有模拟均值：

1. 确定单次模拟的样本量（比如50个随机数）和总模拟次数（比如1000次）。
2. 在空白单元格输入公式：

   =BYROW(SEQUENCE(1000), LAMBDA(x, AVERAGE(RANDARRAY(50, 1, 0, 1))))
   

   • SEQUENCE(1000)生成1到1000的序列，代表1000次模拟。
   • RANDARRAY(50,1,0,1)生成50个0-1之间的随机数（可根据需求调整范围）。
   • BYROW遍历每个模拟次数，计算对应样本的均值。
3. 公式会自动溢出生成1000个均值，直接用于中心极限定理的分布演示。

方案2：旧版Excel兼容方案（无动态数组） #

通过迭代计算+辅助列实现多组模拟：

1. 开启迭代计算：
   • 点击「文件」>「选项」>「公式」，勾选「启用迭代计算」，设置迭代次数为模拟总次数（比如1000）。
2. 构建单次模拟区域：
   • 在A1:A50输入=RAND()（生成50个随机样本），在B1输入=AVERAGE(A1:A50)（单次模拟均值）。
3. 构建模拟结果列：
   • 在C2输入=B1，C3输入=IF(ROW()<=1001, B1, "")，然后下拉到C1001（对应1000次模拟）。
4. 刷新获取结果：
   • 按F9触发刷新，Excel会迭代计算并生成1000个不同的均值；若需要保留多组结果，可将C列的值复制粘贴到其他区域。

为什么嵌套数据表格无效？ #

Excel模拟运算表的计算是静态触发的：仅在创建表格、手动右键刷新，或输入单元格被手动修改时才会重新计算。外层数据表格迭代时，内层表的输入单元格并未被手动修改，因此内层表不会重新执行模拟，导致所有结果重复。

内容的提问来源于stack exchange，提问作者Joseph Stover