如何让Excel Solver保存Evolutionary迭代中候选解的目标单元格值？

首先，先解决你之前用Scenario Manager遇到的核心问题：场景管理器保存的是Solver求解完成后的最终状态，而非迭代过程中的中间候选解。哪怕你在迭代过程中手动保存场景，Excel会在Solver结束后自动更新所有场景的数值，导致所有场景显示的都是最终最优解的数据，这就是你看到全部是最大值的原因。

针对你的需求，我推荐两种实用方案：一种是用VBA捕获Solver的每一次迭代过程，记录实际尝试的候选解；另一种是针对单变量的情况，直接遍历所有可能的整数取值，完整绘制目标函数曲线。

方案一：用VBA捕获Solver迭代过程的候选解 #

这个方法会让Solver每次只运行1次迭代，然后记录当前的变量值和目标值，直到Solver找到最优解为止。

步骤1：启用Solver引用 #

打开VBA编辑器（按下Alt+F11），点击顶部菜单栏的工具→引用，找到并勾选「Microsoft Solver Foundation 3.0」（不同Excel版本名称可能略有差异，比如Excel 365可能显示为「Microsoft Solver Foundation」），点击确定。

步骤2：运行以下VBA代码 #

Sub RecordSolverIterations()
    Dim wsLog As Worksheet
    Dim targetCell As Range
    Dim variableCell As Range
    Dim iterationCount As Integer
    Dim lastRow As Long
    
    ' 创建/获取用于记录的工作表
    On Error Resume Next
    Set wsLog = ThisWorkbook.Worksheets("SolverLogs")
    On Error GoTo 0
    If wsLog Is Nothing Then
        Set wsLog = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        wsLog.Name = "SolverLogs"
        ' 写入表头
        wsLog.Range("A1").Value = "迭代次数"
        wsLog.Range("B1").Value = "可变变量值"
        wsLog.Range("C1").Value = "目标单元格值"
        wsLog.Range("A1:C1").Font.Bold = True
    End If
    
    ' 替换成你实际的目标单元格和可变变量单元格地址
    Set targetCell = ThisWorkbook.Worksheets("Sheet1").Range("B2") ' 示例：目标单元格是Sheet1的B2
    Set variableCell = ThisWorkbook.Worksheets("Sheet1").Range("A2") ' 示例：可变变量是Sheet1的A2
    
    ' 初始化Solver设置（和你之前的配置一致）
    SolverReset
    SolverOk SetCell:=targetCell.Address, MaxMinVal:=1, ValueOf:=0, ByChange:=variableCell.Address, _
        Engine:=3, EngineDesc:="Evolutionary" ' Engine:=3对应Evolutionary求解器
    SolverOptions MaxTime:=0, Iterations:=1, Precision:=0.000001, Convergence:=0.0001, _
        StepThru:=True, Scaling:=False, AssumeNonNeg:=True, Derivatives:=1
    SolverAdd CellRef:=variableCell.Address, Relation:=4, FormulaText:="integer" ' 添加整数约束
    
    iterationCount = 0
    Do
        iterationCount = iterationCount + 1
        ' 运行1次迭代
        SolverSolve UserFinish:=True
        
        ' 记录当前迭代的数值
        lastRow = wsLog.Cells(wsLog.Rows.Count, "A").End(xlUp).Row + 1
        wsLog.Cells(lastRow, "A").Value = iterationCount
        wsLog.Cells(lastRow, "B").Value = variableCell.Value
        wsLog.Cells(lastRow, "C").Value = targetCell.Value
        
        ' 检查Solver是否已完成求解
        If SolverSolve(UserFinish:=False) = 0 Then ' 返回0表示求解完成
            Exit Do
        End If
    Loop Until iterationCount > 1000 ' 设置迭代上限，防止无限循环
    
    MsgBox "迭代记录已保存到SolverLogs工作表，共记录" & iterationCount & "次迭代。"
End Sub

代码说明 #

• 代码会自动创建名为SolverLogs的工作表，用于存储每次迭代的序号、变量值和目标值
• 每次只运行1次迭代（通过SolverOptions Iterations:=1），确保能捕获每一步的中间值
• 请务必将代码中的targetCell和variableCell替换为你实际使用的单元格地址
• 如果你的变量有额外取值范围约束，可以在SolverAdd中添加对应的条件

方案二：遍历所有可能的整数变量值（单变量专属） #

因为你只有一个整数型可变变量，这个方法更简单直接：遍历变量的所有可能取值，计算每个值对应的目标值，完整记录下来。适合变量取值范围不大的情况，能帮你绘制全局的目标函数曲线，确认30是否为全局最大值。

VBA代码示例 #

Sub RecordAllVariableValues()
    Dim wsLog As Worksheet
    Dim targetCell As Range
    Dim variableCell As Range
    Dim minVal As Integer, maxVal As Integer
    Dim i As Integer
    Dim lastRow As Long
    
    ' 创建/获取记录工作表
    On Error Resume Next
    Set wsLog = ThisWorkbook.Worksheets("VariableLogs")
    On Error GoTo 0
    If wsLog Is Nothing Then
        Set wsLog = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        wsLog.Name = "VariableLogs"
        wsLog.Range("A1").Value = "可变变量值"
        wsLog.Range("B1").Value = "目标单元格值"
        wsLog.Range("A1:B1").Font.Bold = True
    End If
    
    ' 替换成你的实际单元格和变量范围
    Set targetCell = ThisWorkbook.Worksheets("Sheet1").Range("B2")
    Set variableCell = ThisWorkbook.Worksheets("Sheet1").Range("A2")
    minVal = 0 ' 变量的最小值，根据你的情况调整
    maxVal = 50 ' 变量的最大值，根据你的情况调整
    
    ' 遍历所有整数值并记录目标值
    For i = minVal To maxVal
        variableCell.Value = i
        Calculate ' 确保公式计算完成
        lastRow = wsLog.Cells(wsLog.Rows.Count, "A").End(xlUp).Row + 1
        wsLog.Cells(lastRow, "A").Value = i
        wsLog.Cells(lastRow, "B").Value = targetCell.Value
    Next i
    
    MsgBox "所有变量值对应的目标值已保存到VariableLogs工作表。"
End Sub

优缺点 #

• 优点：代码简单，无需处理Solver的迭代逻辑，能得到完整的目标函数曲线，方便验证全局最大值
• 缺点：不是捕获Solver实际迭代的候选解，而是遍历所有可能值，适合单变量且取值范围有限的场景

内容的提问来源于stack exchange，提问作者RRTG