如何设置Excel规划求解器获取接近目标值的近似结果

原VBA代码 #

Sub Leagues_Solver()

Dim i As Integer
For i = 0 To 30

' Leagues_Solver Macro
'Reset Solver
SolverReset

mytarget = Range("$A$1").Offset(i, 0)

'
    SolverOk SetCell:=Sheets("Footballprediction.ai Stats LG").Range("$C$1").Offset(i, 0).Address, MaxMinVal:=3, ValueOf:=mytarget, ByChange:=Sheets("Footballprediction.ai Stats LG").Range("$K$2").Offset(i, 0).Address, Engine:=1 _
        , EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:=Sheets("Footballprediction.ai Stats LG").Range("$K$2").Offset(i, 0).Address, Relation:=1, FormulaText:="1"
    SolverAdd CellRef:=Sheets("Footballprediction.ai Stats LG").Range("$K$2").Offset(i, 0).Address, Relation:=3, FormulaText:="0"
    SolverSolve (True)
    
    'Approve the solution and avoid the popups
    
    SolverSolve userFinish:=True
    SolverFinish KeepFinal:=1
    
    Next i
       
End Sub

表格数据 #

核心需求 #

通过调整K列的Alpha参数，让C列（Average-Conf.int）的值近似匹配A列（Value to Reach）的目标值（如示例中的2），保留2-3位小数即可，无需完全相等。

修改后的VBA代码 #

Sub Leagues_Solver_Approximate()

Dim i As Integer
Dim ws As Worksheet
Set ws = Sheets("Footballprediction.ai Stats LG")

For i = 0 To 30
    ' 重置求解器
    SolverReset
    
    ' 获取当前行的目标值
    mytarget = Range("$A$1").Offset(i, 0).Value
    
    ' 设置规划求解目标：让C列值接近目标值，调整K列Alpha
    SolverOk SetCell:=ws.Range("$C$1").Offset(i, 0).Address, _
             MaxMinVal:=3, _
             ValueOf:=mytarget, _
             ByChange:=ws.Range("$K$2").Offset(i, 0).Address, _
             Engine:=1, _
             EngineDesc:="GRG Nonlinear"
    
    ' 添加Alpha的约束：0 ≤ Alpha ≤ 1
    SolverAdd CellRef:=ws.Range("$K$2").Offset(i, 0).Address, _
              Relation:=1, _
              FormulaText:="1"
    SolverAdd CellRef:=ws.Range("$K$2").Offset(i, 0).Address, _
              Relation:=3, _
              FormulaText:="0"
    
    ' 设置求解精度：控制近似匹配的误差范围（三位小数用0.001，两位用0.01）
    SolverOptions Precision:=0.001, _
                  Convergence:=0.0001, _
                  MaxTime:=100, _
                  Iterations:=1000
    
    ' 执行求解并自动确认结果，避免弹窗
    SolverSolve UserFinish:=True
    SolverFinish KeepFinal:=1
Next i
       
End Sub

关键修改说明 #

• 移除原代码中重复调用的SolverSolve (True)，避免重复执行导致的异常
• 添加SolverOptions参数设置：
  • Precision:=0.001：约束Alpha值的精度，确保其在0-1范围内的误差不超过0.001
  • Convergence:=0.0001：设置目标值的收敛精度，当C列值与目标值的差异小于0.0001时停止求解，保证结果达到3位小数的近似度
  • 增大MaxTime和Iterations参数，为求解器提供足够的计算资源寻找近似解
• 定义工作表变量ws，简化代码引用，提升可读性

内容的提问来源于stack exchange，提问作者Kristóf Polányi