在 Excel 中加速蒙特卡罗 [英] Speed up a Monte Carlo in Excel

问题描述

我正在 Excel 中运行蒙特卡罗.为了表示每个月每个产品 SKU 的需求分布，我使用以下公式超过 500 次:

I am running a Monte Carlo in Excel. To represent the distribution of demand for each product SKU during each month, I am using the following formula over 500 times:

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

因此，一次超过 500 个 RAND() 易失性函数.

So, that's over 500 RAND() volatile functions at a time.

公式每次迭代的输出都存储在表中.我有一个 VBA 来处理:

The output from each iteration of a formula is stored in a table. I have a VBA to handle that:

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

这些语句的左侧代表记录输出数据的表位置，右侧是上述公式的输出.

The left-hand side of these statements represent the table location for recording output data, the right-hand side are the outputs from the above mentioned formula.

我注意到这个 VBA 贯穿每一行.每一行都会导致所有 500 个易失性 RAND() 函数重新计算.在 Core i7/32GB RAM 的所有 8 个内核上，此 VBA 的一次迭代需要 30 秒.我想定期运行 5,000 次迭代.

I notice that this VBA runs through each and every line in series. Each line causes all 500 volatile RAND() functions to recalculate. One iteration of this VBA takes 30 seconds on all 8 cores of a Core i7/ 32GB RAM. I want to run 5,000 iterations on a regular basis.

你能提出一些方法让这个模型更有效地运行吗?

Can you suggest methods to make this model run more efficiently?

• 将一个单元格设为 RAND()，然后让所有 500 个单元格都引用它?
• 重构 VBA?

• Make one cell a RAND() and then have all 500 reference it?
• Restructure the VBA?

为了让 Excel 运行更高效，我已经完成了所有基本/一般的事情.

I have done all the basic/general things to make Excel run more efficiently.

推荐答案

一个小的改变可能会产生很大的不同.

One small change might make a large difference.

通过一次调用从数组中读取和写入大范围.我现在无法检查确切的代码，但您可以说类似

Read and write a large range with a single call into / from an array. I can't check the exact code now, but you can say something like

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

或者通过拆分读写来检查调试器中的内容

Or by splitting the read and write so you can check things in the Debugger

   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

通过这样做，您将一次性读取/写入所有内容，并且仅写入一次，您将强制执行一次易失性计算，而不是每次写入时进行.

By doing this you will read / write everything in one shot, and by writing only once you will once force the volatile calculation to happen once, instead of on every write.

这看起来是一篇关于将范围作为数组读取和写入并在调试器下检查的不错的文章:https://bettersolutions.com/excel/cells-ranges/vba-working-with-arrays.htm

This looks like a nice write-up about reading and writing ranges as arrays, and checking things out under the debugger: https://bettersolutions.com/excel/cells-ranges/vba-working-with-arrays.htm

其他一些想法:

• 在 VBA 中用您自己的 rand 函数替换 volatile 函数(它可能只是在内部调用 Rnd).然后，当您明确重新计算所有值时，if 只会获得现在的值(并重新计算).
• 在记录"循环期间将工作表切换为手动计算，以防止在写入工作表时计算不稳定的单元格.

• Replace the volatile function with your own rand function in VBA (which maybe just calls Rnd internally). Then if will only get now values (and recalculate) when you explictly recalculate all.
• Switch the sheet to manual calculation during your 'recording' loop to prevent the volatile cells from calculating while you're writing to the sheet.

这篇关于在 Excel 中加速蒙特卡罗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！