Excel Data Analysis 简明教程
Optimization with Excel Solver
Solver 是 Microsoft Excel 加载项，可用于优化假设分析。
根据 O’Brien 和 Marakas 所述， optimization analysis 是目标寻求分析更复杂的扩展。它不是为变量设置一个特定目标值，而是要在特定约束下找出某个或多个目标变量的最佳值。然后，在指定约束的范围内，反复更改某个或多个其他变量，直到找出最佳目标变量值。
在 Excel 中，可以使用 Solver 查找公式中一个单元（称为目标单元）的 optimal value （最大值或最小值，或某个值），同时还须符合工作表中其他公式单元的值的某些约束或限制。
这意味着求解器使用一组单元格，称为决策变量，这些单元格用于计算目标单元格和约束单元格中的公式。求解器调整决策变量单元格中的值以满足约束单元格的限制，并生成所需的针对目标单元格的结果。
可以使用求解器来查找各种问题的最优解，例如：
-
确定使制药单位盈利能力最大化的月度产品结构。
-
安排组织中的劳动力。
-
Solving transportation problems.
-
Financial planning and budgeting.
Activating Solver Add-in
在使用 Solver 查找问题的解决方案之前，请确保 Solver Add-in 已在 Excel 中激活，如下所示 −
-
单击功能区的“数据”标签。 Solver 命令应该显示在分析组中，如下所示。
如果您找不到 Solver 命令，请按照以下步骤将其激活 −
-
Click the FILE tab.
-
单击左侧窗格中的“选项”。“Excel 选项”对话框随即出现。
-
单击左侧窗格中的“加载项”。
-
在“管理”框中选择“Excel 加载项”，然后单击“转到”。
将出现“加载项”对话框。选中 Solver Add-in ，然后单击“确定”。现在，您应该能够在功能区中的“数据”选项卡下找到 Solver 命令。
Solving Methods used by Solver
根据问题的类型，您可以选择 Excel Solver 支持的以下三种求解方法之一 −
LP Simplex
用于线性问题。在以下条件下， Solver 模型是线性的 −
-
目标单元格通过将 (更改单元格)*(常量) 形式的项相加来计算。
-
每个约束都满足线性模型要求。这意味着每个约束都是通过将 (更改单元格)*(常量) 形式的项相加，然后将和与常量进行比较来计算的。
Generalized Reduced Gradient (GRG) Nonlinear
用于平滑非线性问题。如果您的目标单元格，您的任何约束，或者两者都包含对不是 (更改单元格)*(常量) 形式的更改单元格的引用，则您将拥有一个非线性模型。
Understanding Solver Evaluation
求解器需要以下参数 −
-
Decision Variable Cells
-
Constraint Cells
-
Objective Cells
-
Solving Method
求解器求值基于以下条件 −
-
决策变量单元格中的值受到约束单元格中的值的限制。
-
目标单元格中的值计算包括决策变量单元格中的值。
-
求解器使用所选的求解方法来获得目标单元格中的最优值。
Defining a Problem
假设您正在分析一家制造并销售某个产品的公司的利润。您被要求找到在未来两个季度可以用于广告的最大金额，上限为 20,000。每个季度的广告水平会影响以下内容 −
-
销售单位数，间接决定销售收入。
-
The associated expenses, and
-
The profit.
您可以进行问题定义，如下所示−
-
Find Unit Cost.
-
查找每个单位的广告成本。
-
Find Unit Price.
接下来，设置所需计算的单元格，如下所示。
正如您所观察到的，计算已针对正在考虑的第一季度和第二季度进行−
-
第一季度的待售单位数为 400，第二季度为 600（单元格 – C7 和 D7）。
-
广告预算的初始值设置为每季度 10000（单元格 – C8 和 D8）。
-
销售单位数取决于每单位广告成本，因此是季度预算/每个单位的广告成本。请注意，我们使用了 Min 函数来注意，售出的单位数⇐可用的单位数。（单元格 – C9 和 D9）。
-
收入计算为单价 * 已售出的单位数（单元格 – C10 和 D10）。
-
支出计算为单位成本 * 可用单位数 + 当季度广告成本（单元格 – C11 和 D12）。
-
利润为收入 - 支出（单元格 C12 和 D12）。
-
总利润为第一季度的利润 + 第二季度的利润（单元格 – D3）。
接下来，您可以设置求解器的参数，如下所示：−
如您所见，求解器的参数是−
-
目标单元格为 D3，其中包含您要最大化的总利润。
-
决策变量单元格为 C8 和 D8，其中包含两个季度的预算 – 第一季度和第二季度。
-
有三个约束单元格 - C14、C15 和 C16。单元格 C14 其中包含总预算将设置 20000 的约束（单元格 D14）。单元格 C15 其中包含第一季度售出的单位数将设置⇐第一季度可用单位数的约束（单元格 D15）。单元格 C16 其中包含第二季度售出的单位数将设置⇐第二季度可用单位数的约束（单元格 D16）。
Solving the Problem
下一步是使用求解器来查找解决方案，如下所示：−
Step 1 − 转到功能区的“数据”>“分析”>“求解器”。将显示“求解器参数”对话框。
Step 2 − 在“设置目标”框中，选择单元格 D3。
Step 3 − 选择最大值。
Step 4 − 在 By Changing Variable Cells 框中选择范围 C8:D8。
Step 5 − 接下来，单击“添加”按钮以添加已确定的三个约束条件。
Step 6 − 出现“添加约束条件”对话框。设置以下给定的总预算约束条件，然后单击添加。
Step 7 − 设置以下给定的第一季度总销售单位数约束条件，然后单击添加。
Step 8 − 设置以下给定的第二季度总销售单位数约束条件，然后单击确定。
求解器参数对话框出现，在其“学科约束”框中添加了三个约束条件。
Step 9 − 在 Select a Solving Method 框中，选择 Simplex LP。
Step 10 − 单击求解按钮。求解器结果对话框出现。选择 Keep Solver Solution ，然后单击确定。
结果将显示在工作表中。
如您所见，符合给定约束条件并产生最大总利润的最佳求解方法的发现是以下内容−
-
Total Profit – 30000.
-
第一季度高级预算 − 8000。
-
第二季高级预算 − 12000。
Stepping through Solver Trial Solutions
您可以逐步执行求解器试验求解方法，并查看迭代结果。
Step 1 − 单击求解器参数对话框中的“选项”按钮。
Options 对话框会出现。
Step 2 − 选中“显示迭代结果”框，然后单击确定。
Step 3 − Solver Parameters 对话框出现。单击 Solve 。
Step 4 − Show Trial Solution 会出现一个对话框，并显示消息 - Solver paused, current solution values displayed on worksheet 。
正如你可以看到，当前的迭代值显示在你的工作单元格中。你可以停止求解器接受当前的结果或者从求解器寻找更多步骤中的解决方案。
Step 5 - 单击继续。
Show Trial Solution 对话框出现在每一步中，最终在找到最佳解决方案后，求解器结果对话框出现。你的工作表在每一步都更新，最终显示结果值。
