在使用rand（）函数的单元格上使用Solver加载项

I have a monte carlo simulation model and I need to set one of my parameters (service level) to be at least 95%. The problem is that, from what I know, when Solver runs, it tries different solutions, but because of RAND() function, the target cells keeps on changing all the time. Is there any way I could deal with this? Disable RAND() from changing temporarly? I tried to switch calculations to manual but that doesnt work because then the target cell does not update at all. I appreciate any help, thanks!

我有一个蒙特卡罗模拟模型,我需要将我的一个参数(服务水平)设置为至少95%。问题是,据我所知,当Solver运行时,它尝试不同的解决方案,但由于RAND()函数,目标单元格一直在不断变化。有什么方法可以解决这个问题吗?禁用RAND()暂时改变?我试图将计算切换到手动但不起作用,因为目标单元格根本不更新。我感谢任何帮助,谢谢!

2 个解决方案

#1

What you might do is to create your own =RAND() function (let's call it MYRAND() function) that takes an ON/OFF input. The idea is:

您可能要做的是创建自己的= RAND()函数(让我们称之为MYRAND()函数),它接受ON / OFF输入。这个想法是:

You open the VBE Editor and add a module to your workbook;

您打开VBE编辑器并将模块添加到您的工作簿;

You insert your "Random on/off function", something like this:

您插入“随机开/关功能”,如下所示:

Public Function MYRAND(ByVal activation As String) As Double
Application.Volatile
If activation = "ON" Then
    MYRAND = Rnd()
Else
    MYRAND = Application.Caller.Value
End If
End Function

Now, you can put a listbox containing "ON/OFF" into a cell (let's say A1) and reference it into the cell like =MYRAND(A1). From now, if the value of A1 is at ON, the function will return the classic result you would expect from the =RAND() function. However, if it's at OFF, in that case it will just skip the calculation and let the previous value inside.

现在,您可以将包含“ON / OFF”的列表框放入单元格(假设为A1)并将其引用到单元格中,如= MYRAND(A1)。从现在开始,如果A1的值为ON,则该函数将返回您期望从= RAND()函数获得的经典结果。但是,如果它处于OFF状态,那么它将跳过计算并将之前的值放入其中。

Like this you would be simulating the effect of Calculation Manual/Automatic but applying it to the RAND() function only, leaving all the rest independent.

像这样,您将模拟计算手册/自动的效果,但仅将其应用于RAND()函数,将所有其余部分保持独立。

#2

According to the original authors of the solver, it is designed to use Excel's built-in logic for determining what needs to be recalculated. This means that at each step, the solver will tweak a value, and then ask Excel to recalculate. At that point in time, the Rand functions will run, since they are volatile.

根据求解器的原始作者,它旨在使用Excel的内置逻辑来确定需要重新计算的内容。这意味着在每一步,求解器都会调整一个值,然后让Excel重新计算。在那个时间点,Rand函数将运行,因为它们是不稳定的。

What I would recommend is to take your randomly generated numbers and hard code them into your sheet (paste special -> values), then run the solver, and then put your calls to Rand back in, once your model is calibrated.

我建议您将随机生成的数字和硬编码放入工作表中(粘贴特殊 - >值),然后运行解算器,然后在校准模型后将调用重新调入Rand。

#1