logo标签列表

在Excel中加速蒙特卡罗模拟

excelvbarandommontecarlo
4

我正在Excel中运行蒙特卡罗模拟。为了表示每个产品SKU在每个月的需求分布，我使用以下公式500多次：

=IFERROR(BETA.INV(RAND(),(1+4*(D35-D31)/(D27-D31)),(1+4*(D27-D35)/(D27-D31)),D31,D27),0)

所以，这是一次性使用超过500个RAND()非稳定函数。

每个公式迭代的输出存储在一个表中。我有一个VBA来处理它：

Sub QARLO_Products()
'runs on QARLO_P!, for forecasting product
    Range("Q7:OC100000").ClearContents
    For Iteration = 1 To Range("G1").Value
        Range("I1").Value = Range("G1").Value - Iteration
        'For QMIN Zinc-Bulk
        Cells(6 + Iteration, 17) = Iteration
        Cells(6 + Iteration, 18) = Cells(39, 4)
        Cells(6 + Iteration, 19) = Cells(39, 5)
        Cells(6 + Iteration, 20) = Cells(39, 6)
        Cells(6 + Iteration, 21) = Cells(39, 7)
        Cells(6 + Iteration, 22) = Cells(39, 8)
        Cells(6 + Iteration, 23) = Cells(39, 9)
        Cells(6 + Iteration, 24) = Cells(39, 10)
        Cells(6 + Iteration, 25) = Cells(39, 11)
        Cells(6 + Iteration, 26) = Cells(39, 12)
        Cells(6 + Iteration, 27) = Cells(39, 13)
        Cells(6 + Iteration, 28) = Cells(39, 14)
        Cells(6 + Iteration, 29) = Cells(39, 15)
        'For QMIN Zinc-Jugs
        Cells(6 + Iteration, 30) = Iteration
        Cells(6 + Iteration, 31) = Cells(40, 4)
.... blah, blah, blah and so on for all products....
        Cells(6 + Iteration, 444) = Cells(561, 14)
        Cells(6 + Iteration, 445) = Cells(561, 15)
   Next Iteration
End Sub

这些语句左边代表记录输出数据的表位置，右边是上述公式的输出结果。
我注意到此VBA按顺序运行每一行代码。每一行都导致500个易变的RAND()函数重新计算。在Core i7/32GB RAM的8个核心上，该VBA的一个迭代需要30秒。我想定期运行5,000次迭代。
你能建议一些方法使此模型更有效地运行吗？
  • 将一个单元格设置为RAND()，然后让所有500个引用它？
  • 重构VBA代码？
我已经做了所有基本/通用的事情来使Excel运行更高效。
@pizzettix 我的意思是像这个一样的基础知识。你的屏幕更新脚本对我来说很高级。 - Patrick Dosier
4个回答
2
你说的“我已尽力做了Excel运行更高效的所有基本/通用操作”是指什么？你是否尝试关闭屏幕更新等操作？
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False

'Place your macro code here

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
2

一点小改动也许会带来巨大的变化。

通过单个调用将大范围的数据读取或写入数组中。 我现在无法检查确切的代码，但你可以这样说：

   Range(Cells(6 + Iteration, 18), Cells(6+Iteration, 455).Value = Range(Cells(39,4), Cells(561,15).Value

或者通过分离读取和写入，以便您可以在调试器中检查内容。

   Dim TempValues As Variant ' Really a 2D array of variants

   TempValues = Range(Cells(39,4), Cells(561,15)).Value
   Range(Cells(6 + Iteration, 18), Cells(6 + Iteration, 455)).Value = TempValues

通过这样做，您将一次性读取/写入所有内容，并且仅编写一次，您将一次性强制进行挥发性计算，而不是在每次编写时进行计算。
这看起来像是一个不错的关于将范围作为数组读取和写入，并在调试器下检查事物的写作：https://bettersolutions.com/excel/cells-ranges/vba-working-with-arrays.htm 其他一些想法：
- 用VBA中的自己的rand函数替换易变函数（可能只是内部调用Rnd）。然后，仅当您明确重新计算所有内容时，它才会获取新值（并重新计算）。 - 在您的“记录”循环期间将表格切换到手动计算，以防止易变单元格在您写入表格时进行计算。
1
如果您想使Excel更加高效，您需要考虑完全在VBA中运行Monte Carlo模拟。将结果存储在数组中，并在所有模拟结束后在Excel表格中绘制这些结果。
0
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接