如何使用具有二进制约束的求解器外接程序

Question

我试图找到包含公式的单元格的最大可能值。公式的结果依赖于一些二进制值。我创建了一个例子。这是一个更简单的工作簿，但它有所有相同的问题。

截图在截图中:

• 列A包含x值，该值为10或1
• B列中y的值为10或1
• C列包含二进制值，该值为0或1
• D列包含基于简单公式的选择值，如果Binary(列C)的值为0，则返回x(列a)的值，如果Binary(列C)的值为1，则返回y(列B)的值。如。单元格D2中的公式为:=IF(C2=0,A2,IF(C2=1,B2,0))
• 单元格E2是选择的和(列D)。

我写了三个宏来找到单元格E2中的最大值:

• 改变二进制的所有10个值(例如:细胞C2: C11)
• 改变二进制的前两个值(例如:细胞C2, C3)
• 改变二进制的第一个值(例如:单元格C2)

每次运行宏时，我将二进制值重置为原始值(例如:如图所示).

宏"Maximise10"应该返回值100:

Sub Maximise10()
Dim CellToChange, CellToSolve As String
Sheets("Example").Select
CellToChange = "C2:C11"
CellToSolve = "E2"

SolverReset
SolverOptions Precision:=0.1, Convergence:=0.5
SolverOK SetCell:=Range(CellToSolve), MaxMinVal:=1, ByChange:=Range(CellToChange), Engine:=3
SolverAdd CellRef:=Range(CellToChange), Relation:=5
SolverSolve UserFinish:=True

End Sub

宏"Maximise2"应该返回值28:

Sub Maximise2()
Dim CellToChange, CellToSolve As String
Sheets("Example").Select
CellToChange = "C2:C3"
CellToSolve = "E2"

SolverReset
SolverOptions Precision:=0.1, Convergence:=0.5
SolverOK SetCell:=Range(CellToSolve), MaxMinVal:=1, ByChange:=Range(CellToChange), Engine:=3
SolverAdd CellRef:=Range(CellToChange), Relation:=5
SolverSolve UserFinish:=True

End Sub

宏"Maximise1"应该返回值19:

Sub Maximise1()
Dim CellToChange, CellToSolve As String
Sheets("Example").Select
CellToChange = "C2"
CellToSolve = "E2"

SolverReset
SolverOptions Precision:=0.1, Convergence:=0.5
SolverOK SetCell:=Range(CellToSolve), MaxMinVal:=1, ByChange:=Range(CellToChange), Engine:=3
SolverAdd CellRef:=Range(CellToChange), Relation:=5
SolverSolve UserFinish:=True

End Sub

我只得到"进化"的结果;方法。在求解器选项中，我尝试调整:

迭代

精度
• AssumeNonNeg
• PopulationSize
• MutationRate
• MaxSubproblems

上面的代码提供了最好的结果，但是结果还是不能令人满意。

• "Maximise1"Sub应该是最容易解决的问题，因为只有两个选项。Excel经常锁死(例如;不响应)，当我运行这个宏与不同的选项。如果它没有锁定，求解器将继续运行而不会找到解决方案。
• "Maximise2"sub应该相对容易，因为只有四个选项需要考虑。
• 尽管有二进制约束，求解器始终考虑c列中的非整数值。

我将非常感谢一些帮助，以确定如何最好地配置求解器外接程序，以确保它只考虑整数值，以最快，最有效的方式确定最大值。

Answer 1

您的病情:

=IF(C2=0,A2,IF(C2=1,B2,0))

等价于

=IF(C2=0,A2,B2)

由于Solver的Simplex方法不喜欢IF函数，我们可以将其重写为:

=(1-C2)*A2+C2*B2

这是变量的线性函数，所以单纯形法可以工作。尽管将整数优化选项更改为0%。在本例中，最优解为目标函数值为100。