vba Excel 求解器中的多个目标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43380593/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiple objectives in Excel solver
提问by Clauric
I have a macro that runs through multiple sets of permutations using Excel's solver. Each macro runs the solver 3 times, using Simplex followed by 2 x Evolutionary. It seems to work reasonably well.
我有一个宏，它使用 Excel 的求解器运行多组排列。每个宏运行求解器 3 次，使用 Simplex，然后使用 2 x Evolutionary。它似乎工作得相当好。
However, I noticed that the solution keeps changing, due to one of the variables only being constrained by a maximum value. As such, I need to make the variable a max/min objective.
但是，我注意到解决方案不断变化，因为其中一个变量仅受最大值约束。因此，我需要将变量设为最大/最小目标。
I know that Excel's solver only allows for one max/min objective. Is there any way that I can set a second max/min objective?
我知道 Excel 的求解器只允许一个最大/最小目标。有什么方法可以设置第二个最大/最小目标吗？
The code I have is as follows:
我的代码如下：
a = Range("Q1")
SolverReset
SolverOk SetCell:="$N", MaxMinVal:=1, ValueOf:=0, ByChange:="$M:$M" & a, _
Engine:=3, EngineDesc:="Simplex LP"
SolverAdd CellRef:="$O", Relation:=1, FormulaText:="80"
SolverAdd CellRef:="$M", Relation:=2, FormulaText:="8"
SolverAdd CellRef:="$P", Relation:=3, FormulaText:="5"
SolverOk SetCell:="$N", MaxMinVal:=1, ValueOf:=0, ByChange:="$M:$M" & a, _
Engine:=3, EngineDesc:="Simplex LP"
SolverAdd CellRef:="$M:$M" & a, Relation:=4, FormulaText:="integer"
SolverAdd CellRef:="$M:$M" & a, Relation:=1, FormulaText:="1"
SolverAdd CellRef:="$M:$M" & a, Relation:=3, FormulaText:="0"
SolverOk SetCell:="$N", MaxMinVal:=1, ValueOf:=0, ByChange:="$M:$M" & a, _
Engine:=3, EngineDesc:="Simplex LP"
SolverSolve ShowRef = 0
SolverReset
SolverOk SetCell:="$N", MaxMinVal:=1, ValueOf:=0, ByChange:="$M:$M" & a, _
Engine:=3, EngineDesc:="Evolutionary"
SolverAdd CellRef:="$O", Relation:=1, FormulaText:="80"
SolverAdd CellRef:="$M", Relation:=2, FormulaText:="8"
SolverAdd CellRef:="$P", Relation:=3, FormulaText:="5"
SolverOk SetCell:="$N", MaxMinVal:=1, ValueOf:=0, ByChange:="$M:$M" & a, _
Engine:=3, EngineDesc:="Evolutionary"
SolverAdd CellRef:="$M:$M" & a, Relation:=4, FormulaText:="integer"
SolverAdd CellRef:="$M:$M" & a, Relation:=1, FormulaText:="1"
SolverAdd CellRef:="$M:$M" & a, Relation:=3, FormulaText:="0"
SolverOk SetCell:="$N", MaxMinVal:=1, ValueOf:=0, ByChange:="$M:$M" & a, _
Engine:=3, EngineDesc:="Evolutionary"
SolverSolve ShowRef = 0
SolverReset
SolverOk SetCell:="$N", MaxMinVal:=1, ValueOf:=0, ByChange:="$M:$M" & a, _
Engine:=3, EngineDesc:="Evolutionary"
SolverAdd CellRef:="$O", Relation:=1, FormulaText:="80"
SolverAdd CellRef:="$M", Relation:=2, FormulaText:="8"
SolverAdd CellRef:="$P", Relation:=3, FormulaText:="5"
SolverOk SetCell:="$N", MaxMinVal:=1, ValueOf:=0, ByChange:="$M:$M" & a, _
Engine:=3, EngineDesc:="Evolutionary"
SolverAdd CellRef:="$M:$M" & a, Relation:=4, FormulaText:="integer"
SolverAdd CellRef:="$M:$M" & a, Relation:=1, FormulaText:="1"
SolverAdd CellRef:="$M:$M" & a, Relation:=3, FormulaText:="0"
SolverOk SetCell:="$N", MaxMinVal:=1, ValueOf:=0, ByChange:="$M:$M" & a, _
Engine:=3, EngineDesc:="Evolutionary"
SolverSolve ShowRef = 0
采纳答案by Algau
You shall see this as a sequential process.
您将看到这是一个连续的过程。
- Obtain the initial, optimal, solution.
- Add the optimal value to the system of constraint.
- Solve again the whole problem, but with your new objective wich would be to maximize (or minimize) your variable of interest.
- 获得初始的最优解。
- 将最优值添加到约束系统中。
- 再次解决整个问题，但您的新目标是最大化（或最小化）您感兴趣的变量。
So your first objective will be fulfiled, and by adding it's optimal value to the system of constraint it won't be possible to affect it. And than, as long as it doesn't interfere with the previous objective, your second objective will also be fulfilled.
因此，您的第一个目标将实现，并且通过将其最佳值添加到约束系统中，将不可能影响它。而且，只要它不干扰前一个目标，您的第二个目标也将实现。
In fact, doing so you could add as many objective as you want and they would be solve with a lexicographic order (higher priority given to the previous objective).
实际上，这样做您可以添加任意数量的目标，并且它们将按照字典顺序解决（前一个目标具有更高的优先级）。
