Excel实战：使用VBA实现自动规划求解

标签：VBA，规划求解

规划求解可能是Excel中最好的功能之一，但它使用起来相当不便，本文探讨一种自动化实现这项功能的方法。

规划求解功能确定实现特定结果所需的输入。示例根据销售量、销售价格、成本价格和固定成本来计算利润。

图1

灰色单元格是变量，如果任何变量发生变化，最终利润将发生变化。

手工规划求解

使用上面的数字，假设想知道我们需要卖出多少套才能实现盈亏平衡（即，利润等于零）。

1.单击功能区“数据”选项卡“预测”组中的“模拟分析——单变量求解”，如下图2所示。

图2

2.在“单变量求解”对话框，设置参数如下图3所示。

图3

3.单击“确定”，Excel尝试确定可能的解决方案，并相应地更新单元格E12。

图4

4.一旦找到解决方案，单击“确定”关闭单变量求解对话框。

如果按上述操作，示例中需要卖出571台才能实现收支平衡。

如果其中一个变量发生变化（例如，如果销售价格增加到110），那么我们需要重新运行相同的目标寻求过程。这相当耗时。此外，如果正在为设计电子表格，那么这不是一个很好的用户体验。

使用VBA自动化求解

我们可以将相关的单元格进行命名，然后在代码中运用，这样更加灵活且通用。

示例中命名的单元格如下：

C4: SalesUnits

D4: SalesPrice

D6: VariableCostPrice

E10: FixedCost

E12: Profit

I6: TargetValue

相应的代码如下：

Sub Macro1()
    Range("Profit").GoalSeek Goal:=Range("TargetValue"), _
       ChangingCell:=Range("SalesPrice")
End Sub

我们再命名两个单元格名称：

I4: SetCell

I8: ChangeCell

在这两个单元格中输入下面的值：

I4 = Profit

I8 = SalesUnits

接着，在工作表代码模块，输入下面的代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim inputCells As Range
    '列出所有输入单元格. (1)命名单元格或 (2) 单元格引用
    Set inputCells = Range("SalesUnits, SalesPrice, VariableCostPrice, FixedCost, " & _
         "TargetValue, SetCell, ChangeCell")
    '如果输入单元格改变则运行宏
    If Not Application.Intersect(Range(Target.Address),inputCells) Is Nothing Then
      '使用SetCell,TargetValue和ChangeCell单元格中的值运行规划求解
      Range(Range("SetCell").Value).GoalSeek Goal:=Range("TargetValue").Value, _
         ChangingCell:=Range(Range("ChangeCell").Value)
    End If
End Sub

这样，每次改变输入单元格值，会自动触发规划求解。

注：本文学习整理自exceloffthegrid.com，VBA应用场景示例，供参考。