Excel Data Analysis 简明教程

Optimization with Excel Solver

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

Solver is a Microsoft Excel add-in program you can use for optimization in what-if analysis.

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

According to O’Brien and Marakas, optimization analysis is a more complex extension of goal-seeking analysis. Instead of setting a specific target value for a variable, the goal is to find the optimum value for one or more target variables, under certain constraints. Then, one or more other variables are changed repeatedly, subject to the specified constraints, until you discover the best values for the target variables.

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

In Excel, you can use Solver to find an optimal value (maximum or minimum, or a certain value) for a formula in one cell called the objective cell, subject to certain constraints or limits, on the values of other formula cells on the worksheet.

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

This means that the Solver works with a group of cells called decision variables that are used in computing the formulas in the objective and constraint cells. Solver adjusts the values in the decision variable cells to satisfy the limits on constraint cells and produce the result you want for the objective cell.

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

You can use Solver to find optimal solutions for diverse problems such as −

  1. Determining the monthly product mix for a drug manufacturing unit that maximizes the profitability.

  2. Scheduling workforce in an organization.

  3. Solving transportation problems.

  4. Financial planning and budgeting.

Activating Solver Add-in

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

Before you proceed with finding solution for a problem with Solver, ensure that the Solver Add-in is activated in Excel as follows −

  1. Click the DATA tab on the Ribbon. The Solver command should appear in the Analysis group as shown below.

activating solver addin

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

In case you do not find the Solver command, activate it as follows −

  1. Click the FILE tab.

  2. Click Options in the left pane. Excel Options dialog box appears.

  3. Click Add-Ins in the left pane.

  4. Select Excel Add-Ins in the Manage box and click Go.

select excel addins

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

The Add-Ins dialog box appears. Check Solver Add-in and click Ok. Now, you should be able to find the Solver command on the Ribbon under the DATA tab.

solver addin

Solving Methods used by Solver

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

You can choose one of the following three solving methods that Excel Solver supports, based on the type of problem −

LP Simplex

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

Used for linear problems. A Solver model is linear under the following conditions −

  1. The target cell is computed by adding together the terms of the (changing cell)*(constant) form.

  2. Each constraint satisfies the linear model requirement. This means that each constraint is evaluated by adding together the terms of the (changing cell)*(constant) form and comparing the sums to a constant.

Generalized Reduced Gradient (GRG) Nonlinear

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

Used for smooth nonlinear problems. If your target cell, any of your constraints, or both contain references to changing cells that are not of the (changing cell)*(constant) form, you have a nonlinear model.

Evolutionary

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

Used for smooth nonlinear problems. If your target cell, any of your constraints, or both contain references to changing cells that are not of the (changing cell)*(constant) form, you have a nonlinear model.

Understanding Solver Evaluation

求解器需要以下参数 −

The Solver requires the following parameters −

  1. Decision Variable Cells

  2. Constraint Cells

  3. Objective Cells

  4. Solving Method

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

Solver evaluation is based on the following −

  1. The values in the decision variable cells are restricted by the values in the constraint cells.

  2. The calculation of the value in the objective cell includes the values in the decision variable cells.

  3. Solver uses the chosen Solving Method to result in the optimal value in the objective cell.

Defining a Problem

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

Suppose you are analyzing the profits made by a company that manufactures and sells a certain product. You are asked to find the amount that can be spent on advertising in the next two quarters subject to a maximum of 20,000. The level of advertising in each quarter affects the following −

  1. The number of units sold, indirectly determining the amount of sales revenue.

  2. The associated expenses, and

  3. The profit.

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

You can proceed to define the problem as −

  1. Find Unit Cost.

  2. Find the advertising cost per Unit.

  3. Find Unit Price.

defining problem

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

Next, set the cells for the required calculations as given below.

set cells

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

As you can observe, the calculations are done for Quarter1 and Quarter2 that are in consideration are −

  1. No. of units available for sale in Quarter1 is 400 and in Quarter2 is 600 (cells – C7 and D7).

  2. The initial values for advertising budget are set as 10000 per Quarter (Cells – C8 and D8).

  3. No. of units sold is dependent on the advertising cost per unit and hence is budget for the quarter / Adv. Cost per unit. Note that we have used the Min function to take care to see that the no. of units sold in ⇐ no. of units available. (Cells – C9 and D9).

  4. Revenue is calculated as Unit Price * No. of Units sold (Cells – C10 and D10).

  5. Expenses is calculated as Unit Cost * No. of Units Available + Adv. Cost for that quarter (Cells – C11 and D12).

  6. Profit is Revenue – Expenses (Cells C12 and D12).

  7. Total Profit is Profit in Quarter1 + Profit in Quarter2 (Cell – D3).

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

Next, you can set the parameters for Solver as given below −

set parameters

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

As you can observe, the parameters for Solver are −

  1. Objective cell is D3 that contains Total Profit, which you want to maximize.

  2. Decision Variable cells are C8 and D8 that contain the budgets for the two quarters – Quarter1 and Quarter2.

  3. There are three Constraint cells - C14, C15 and C16. Cell C14 that contains total budget is to set the constraint of 20000 (cell D14). Cell C15 that contains the no. of units sold in Quarter1 is to set the constraint of ⇐ no. of units available in Quarter1 (cell D15). Cell C16 that contains the no. of units sold in Quarter2 is to set the constraint of ⇐ no. of units available in Quarter2 (cell D16).

Solving the Problem

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

The next step is to use Solver to find the solution as follows −

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

Step 1 − Go to DATA > Analysis > Solver on the Ribbon. The Solver Parameters dialog box appears.

solver parameters

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

Step 2 − In the Set Objective box, select the cell D3.

Step 3 − 选择最大值。

Step 3 − Select Max.

Step 4 − 在 By Changing Variable Cells 框中选择范围 C8:D8。

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

changing variable cells

Step 5 − 接下来,单击“添加”按钮以添加已确定的三个约束条件。

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

Step 6 − 出现“添加约束条件”对话框。设置以下给定的总预算约束条件,然后单击添加。

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

add constraint

Step 7 − 设置以下给定的第一季度总销售单位数约束条件,然后单击添加。

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

click add

Step 8 − 设置以下给定的第二季度总销售单位数约束条件,然后单击确定。

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 − 在 Select a Solving Method 框中,选择 Simplex LP。

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

select solving method

Step 10 − 单击求解按钮。求解器结果对话框出现。选择 Keep Solver Solution ,然后单击确定。

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 − 单击求解器参数对话框中的“选项”按钮。

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

Options 对话框会出现。

The Options dialog box appears.

Step 2 − 选中“显示迭代结果”框,然后单击确定。

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

show iteration

Step 3Solver Parameters 对话框出现。单击 Solve

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

Step 4Show Trial Solution 会出现一个对话框,并显示消息 - Solver paused, current solution values displayed on worksheet

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

show trial solution

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

As you can observe, the current iteration values are displayed in your working cells. You can either stop the Solver accepting the current results or continue with the Solver from finding solution in further steps.

Step 5 - 单击继续。

Step 5 − Click Continue.

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

The Show Trial Solution dialog box appears at every step and finally after the optimal solution is found, Solver Results dialog box appears. Your worksheet is updated at every step, finally showing the result values.

Saving Solver Selections

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

You have the following saving options for the problems that you solve with Solver −

  1. You can save the last selections in the Solver Parameters dialog box with a worksheet by saving the workbook.

  2. Each worksheet in a workbook can have its own Solver selections, and all of them will be saved when you save the workbook.

  3. You can also define more than one problem in a worksheet, each with its own Solver selections. In such a case, you can load and save problems individually with the Load/Save in the Solver Parameters dialog box. Click the Load/Save button. The Load/Save dialog box appears. To save a problem model, enter the reference for the first cell of a vertical range of empty cells in which you want to place the problem model. Click Save.

saving solver selections
solver parameters set