Excel规划求解未提供一致的结果 [英] Excel Solver Not Providing Consistent Results

问题描述

我正在尝试使用求解器来解决非线性问题，但是结果不一致，并且在某些情况下会发生变化.使用Risk Solver Platform时不存在此问题，但在使用常规Excel Solver时却存在.优化和约束并不复杂，所以我不知道发生了什么.

这里是文件: temp.xlsm

请注意，带有用于运行求解器的按钮的宏.

要复制问题:

• 打开文件
• 点击Months标签
• 初始感染率应为11％
• 单击Optimize Net Revenue(正确的最佳状态；这很好)
• 将初始感染度更改为1％
• 点击Optimize Net Revenue
• 将初始感染度更改回11％
• 点击Optimize Net Revenue

注意:最佳净收入"现在较低，并且在上个月的最后一列为负数(应为零).似乎求解器每次都需要重置，或者有错误，但是我无法弄清楚.这是宏:

Sub Monthly_Solver()

    Application.Run "SolverReset"
    Application.Run "SolverOk", "$T$18", 1, "0", "$F$6:$F$17,$N$6:$N$17", 1, "GRG Nonlinear"
    Application.Run "SolverAdd", "$F$6:$F$17", 5, "binary"
    Application.Run "SolverAdd", "$N$6:$N$17", 1, "$M$6:$M$17"
    Application.Run "SolverAdd", "$N$6:$N$17", 3, "0"
    Application.Run "SolverAdd", "$T$18", 3, "0"
    Application.Run "SolverSolve", True

End Sub

解决方案

GRG Nonlinear不保证找到全局最优解.它找到一个局部最优解，并且该解的质量受您提供给算法的初始值的影响.从Frontline的网站:

当出现"Solver找到解决方案"消息时，表示GRG方法已找到了局部最优解–决策变量没有其他接近当前值的值集，可以为当前值提供更好的值.目标函数.形象地讲，这意味着Solver找到了一个峰值"(如果最大化)或谷"(如果最小化)–但是如果该模型是非凸的，则可能有其他更高的峰或更深的谷底远离当前解决方案.在数学上，此消息表示满足局部最优的Karush-Kuhn-Tucker(KKT)条件(在一定的公差范围内，与求解器选项"对话框中的精度"设置有关).

现在，您的Harvest列在N14:N16中具有3131.如果将initial infestation更改为1％并再次求解，则N14:N17的值为3131.因此，您的初始解决方案已更改.如果将其改回11％而未更改N17中的值，则求解器可能会找到另一个局部最优解.如果设置N17=0，则将获得与第一个相同的解决方案.

换句话说，如果GRG非线性的唯一原因是二进制Spray变量及其表示的if/then条件，则可以使用线性约束(因此更可能具有全局最优性).这是快速参考.

I'm trying to use solver to solve a non-linear problem, but the results are not consistent and change under certain conditions. This problem does not exist when using Risk Solver Platform, but does when using the regular Excel Solver. The optimization and constraints are not complex, so I don't understand what is going on.

Here is the file: temp.xlsm

Notice there are macros involved with buttons attached to run the solver.

To Replicate problem:

• Open file
• Click Months tab
• Initial Infestation should be at 11%
• Click Optimize Net Revenue (Correct Optimal Condition; this is fine)
• Change Initial Infestation to 1%
• Click Optimize Net Revenue
• Change Initial Infestation back to 11%
• Click Optimize Net Revenue

Note: Optimal Net Revenue is now lower and in the last month the last column is negative when it should be zero. It seems like solver needs to be reset everytime, or there is a bug, but I can't figure it out. Here's the macro:

Sub Monthly_Solver()

    Application.Run "SolverReset"
    Application.Run "SolverOk", "$T$18", 1, "0", "$F$6:$F$17,$N$6:$N$17", 1, "GRG Nonlinear"
    Application.Run "SolverAdd", "$F$6:$F$17", 5, "binary"
    Application.Run "SolverAdd", "$N$6:$N$17", 1, "$M$6:$M$17"
    Application.Run "SolverAdd", "$N$6:$N$17", 3, "0"
    Application.Run "SolverAdd", "$T$18", 3, "0"
    Application.Run "SolverSolve", True

End Sub

解决方案

GRG Nonlinear does not guarantee finding the global optimal solution. It finds a local optimal solution and the quality of that solution is affected by the initial values you supply to the algorithm. From Frontline's website:

When the message "Solver found a solution" appears, it means that the GRG method has found a locally optimal solution – there is no other set of values for the decision variables close to the current values that yields a better value for the objective function. Figuratively, this means that Solver has found a "peak" (if maximizing) or "valley" (if minimizing) – but if the model is non-convex, there may be other taller peaks or deeper valleys far away from the current solution. Mathematically, this message means that the Karush - Kuhn - Tucker (KKT) conditions for local optimality have been satisfied (to within a certain tolerance, related to the Precision setting in the Solver Options dialog).

Right now, your Harvest column has 3131 in N14:N16. If you change initial infestation to 1% and solve again, N14:N17 will have the value of 3131. So your initial solution has changed. If you change it back to 11% without changing the value in N17 the solver may found another local optimal solution. If you set N17=0 you will get the same solution as the first one.

On a different note, if the only reason for GRG Nonlinear is the binary Spray variable and the if/then conditions it represents, you can use linear constraints (so global optimality is more likely). Here's a quick reference.

这篇关于Excel规划求解未提供一致的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！