excel - 如何使用具有二进制约束的求解器插件

Question

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

截屏

在屏幕截图中：

• A 列包含 x 的值为 10 或 1
• B 列包含 10 或 1 的 y 值
• C 列包含二进制值，它们是 0 或 1
• D 列包含的选择值基于一个简单的公式，如果二进制（C 列）的值为 0，则返回 x（A 列）的值，如果二进制（C 列）返回 y（B 列）的值) 的值为 1。例如。单元格 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

我只能通过“进化”方法获得结果。在求解器选项中，我尝试调整：

• 迭代
• 精确
• 缩放
• 假设非否定
• 人口规模
• 突变率
• 最大子问题

上面的代码已经提供了最好的结果，但结果却不尽如人意。

• “Maximise1”子应该是最容易解决的问题，因为应该只有两个选项。当我使用不同的选项运行此宏时，Excel 会定期锁定（例如，不响应）。如果它没有锁定，Solver 将继续运行而找不到解决方案。
• “Maximise2”子应该相对容易，因为只有四个选项需要考虑。
• 尽管有二进制约束，求解器始终考虑 C 列中的非整数值。

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

标签： excelvbabinaryconstraintssolver

Answer 1

你的情况：

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

相当于

=IF(C2=0,A2,B2)

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

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

这是变量的线性函数，因此 Simplex 方法将起作用。虽然将 Integer Optimality 选项更改为 0%。在示例中，最优解是目标函数值为 100。