Excel Data Analysis 简明教程

Optimization with Excel Solver

Solver 是 Microsoft Excel 加载项，可用于优化假设分析。

根据 O’Brien 和 Marakas 所述， optimization analysis 是目标寻求分析更复杂的扩展。它不是为变量设置一个特定目标值，而是要在特定约束下找出某个或多个目标变量的最佳值。然后，在指定约束的范围内，反复更改某个或多个其他变量，直到找出最佳目标变量值。

在 Excel 中，可以使用 Solver 查找公式中一个单元（称为目标单元）的 optimal value （最大值或最小值，或某个值），同时还须符合工作表中其他公式单元的值的某些约束或限制。

这意味着求解器使用一组单元格，称为决策变量，这些单元格用于计算目标单元格和约束单元格中的公式。求解器调整决策变量单元格中的值以满足约束单元格的限制，并生成所需的针对目标单元格的结果。

可以使用求解器来查找各种问题的最优解，例如：

  1. 确定使制药单位盈利能力最大化的月度产品结构。

  2. 安排组织中的劳动力。

  3. Solving transportation problems.

  4. Financial planning and budgeting.

Activating Solver Add-in

在使用 Solver 查找问题的解决方案之前，请确保 Solver Add-in 已在 Excel 中激活，如下所示 −

  1. 单击功能区的“数据”标签。 Solver 命令应该显示在分析组中，如下所示。

activating solver addin

如果您找不到 Solver 命令，请按照以下步骤将其激活 −

  1. Click the FILE tab.

  2. 单击左侧窗格中的“选项”。“Excel 选项”对话框随即出现。

  3. 单击左侧窗格中的“加载项”。

  4. 在“管理”框中选择“Excel 加载项”，然后单击“转到”。

select excel addins

将出现“加载项”对话框。选中 Solver Add-in ，然后单击“确定”。现在，您应该能够在功能区中的“数据”选项卡下找到 Solver 命令。

solver addin

Solving Methods used by Solver

根据问题的类型，您可以选择 Excel Solver 支持的以下三种求解方法之一 −

LP Simplex

用于线性问题。在以下条件下， Solver 模型是线性的 −

  1. 目标单元格通过将 (更改单元格)*(常量) 形式的项相加来计算。

  2. 每个约束都满足线性模型要求。这意味着每个约束都是通过将 (更改单元格)*(常量) 形式的项相加，然后将和与常量进行比较来计算的。

Generalized Reduced Gradient (GRG) Nonlinear

用于平滑非线性问题。如果您的目标单元格，您的任何约束，或者两者都包含对不是 (更改单元格)*(常量) 形式的更改单元格的引用，则您将拥有一个非线性模型。

Evolutionary

用于平滑非线性问题。如果您的目标单元格，您的任何约束，或者两者都包含对不是 (更改单元格)*(常量) 形式的更改单元格的引用，则您将拥有一个非线性模型。

Understanding Solver Evaluation

求解器需要以下参数 −

  1. Decision Variable Cells

  2. Constraint Cells

  3. Objective Cells

  4. Solving Method

求解器求值基于以下条件 −

  1. 决策变量单元格中的值受到约束单元格中的值的限制。

  2. 目标单元格中的值计算包括决策变量单元格中的值。

  3. 求解器使用所选的求解方法来获得目标单元格中的最优值。

Defining a Problem

假设您正在分析一家制造并销售某个产品的公司的利润。您被要求找到在未来两个季度可以用于广告的最大金额，上限为 20,000。每个季度的广告水平会影响以下内容 −

  1. 销售单位数，间接决定销售收入。

  2. The associated expenses, and

  3. The profit.

您可以进行问题定义，如下所示−

  1. Find Unit Cost.

  2. 查找每个单位的广告成本。

  3. Find Unit Price.

defining problem

接下来，设置所需计算的单元格，如下所示。

set cells

正如您所观察到的，计算已针对正在考虑的第一季度和第二季度进行−

  1. 第一季度的待售单位数为 400，第二季度为 600（单元格 – C7 和 D7）。

  2. 广告预算的初始值设置为每季度 10000（单元格 – C8 和 D8）。

  3. 销售单位数取决于每单位广告成本，因此是季度预算/每个单位的广告成本。请注意，我们使用了 Min 函数来注意，售出的单位数⇐可用的单位数。（单元格 – C9 和 D9）。

  4. 收入计算为单价 * 已售出的单位数（单元格 – C10 和 D10）。

  5. 支出计算为单位成本 * 可用单位数 + 当季度广告成本（单元格 – C11 和 D12）。

  6. 利润为收入 - 支出（单元格 C12 和 D12）。

  7. 总利润为第一季度的利润 + 第二季度的利润（单元格 – D3）。

接下来，您可以设置求解器的参数，如下所示：−

set parameters

如您所见，求解器的参数是−

  1. 目标单元格为 D3，其中包含您要最大化的总利润。

  2. 决策变量单元格为 C8 和 D8，其中包含两个季度的预算 – 第一季度和第二季度。

  3. 有三个约束单元格 - C14、C15 和 C16。单元格 C14 其中包含总预算将设置 20000 的约束（单元格 D14）。单元格 C15 其中包含第一季度售出的单位数将设置⇐第一季度可用单位数的约束（单元格 D15）。单元格 C16 其中包含第二季度售出的单位数将设置⇐第二季度可用单位数的约束（单元格 D16）。

Solving the Problem

下一步是使用求解器来查找解决方案，如下所示：−

Step 1 − 转到功能区的“数据”>“分析”>“求解器”。将显示“求解器参数”对话框。

solver parameters

Step 2 − 在“设置目标”框中，选择单元格 D3。

Step 3 − Select Max.

Step 4 − Select range C8:D8 in the By Changing Variable Cells box.

changing variable cells

Step 5 − Next, click the Add button to add the three constraints that you have identified.

Step 6 − The Add Constraint dialog box appears. Set the constraint for total budget as given below and click Add.

add constraint

Step 7 − Set the constraint for total no. of units sold in Quarter1 as given below and click Add.

click add

Step 8 − Set the constraint for total no. of units sold in Quarter2 as given below and click OK.

set constraint

The Solver Parameters dialog box appears with the three constraints added in box –Subject to the Constraints.

Step 9 − In the Select a Solving Method box, select Simplex LP.

select solving method

Step 10 − Click the Solve button. The Solver Results dialog box appears. Select Keep Solver Solution and click OK.

keep solver solution

The results will appear in your worksheet.

result

As you can observe, the optimal solution that produces maximum total profit, subject to the given constraints, is found to be the following −

  1. Total Profit – 30000.

  2. Adv. Budget for Quarter1 – 8000.

  3. Adv. Budget for Quarter2 – 12000.

Stepping through Solver Trial Solutions

You can step through the Solver trial solutions, looking at the iteration results.

Step 1 − Click the Options button in the Solver Parameters dialog box.

The Options dialog box appears.

Step 2 − Select the Show Iteration Results box and click OK.

show iteration

Step 3 − The Solver Parameters dialog box appears. Click Solve.

Step 4 − The Show Trial Solution dialog box appears, displaying the message - Solver paused, current solution values displayed on worksheet.

show trial solution

正如你可以看到，当前的迭代值显示在你的工作单元格中。你可以停止求解器接受当前的结果或者从求解器寻找更多步骤中的解决方案。

Step 5 - 单击继续。

Show Trial Solution 对话框出现在每一步中，最终在找到最佳解决方案后，求解器结果对话框出现。你的工作表在每一步都更新，最终显示结果值。

Saving Solver Selections

对于你用求解器求解的问题，你有以下保存选项 −

  1. 你可以通过保存工作簿来使用工作表中的求解器参数对话框保存最后的选择。

  2. 工作簿中的每个工作表都可以有其自己的求解器选择，并且所有这些选择都会在保存工作簿时保存。

  3. 你也可以在一个工作表中定义多个问题，每个问题都有其自己的求解器选择。在这种情况中，你可以在求解器参数对话框中使用加载/保存来加载和保存各个问题。单击 Load/Save 按钮。加载/保存对话框出现。要保存问题模型，请输入你希望放置问题模型的空单元格垂直范围第一个单元格的引用。单击保存。

saving solver selections
solver parameters set