Excel 高级数据分析 - 使用 Excel Solver 求解器进行优化

Solver(求解器) 是一个 Microsoft Excel 插件程序，可用于在假设分析中进行优化。

根据 O'Brien 和 Marakas 的说法，优化分析 是目标寻求分析的更复杂的扩展。目标不是为变量设置特定的目标值，而是在特定约束下为一个或多个目标变量找到最佳值。然后，根据指定的约束，重复更改一个或多个其他变量，直到您发现目标变量的最佳值。

在 Excel 中，您可以使用 Solver(求解器) 为一个称为目标单元格的单元格中的公式找到一个最佳值（最大值或最小值，或某个特定值），工作表上其他公式单元格的值受到某些约束或限制。

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

您可以使用求解器为各种问题寻找最佳解决方案，例如 −

确定药物制造单位的月度产品组合，以最大限度地提高盈利能力。
安排组织中的劳动力。
解决交通问题。
财务规划和预算。

激活 Solver 插件

在您继续寻找求解器问题的解决方案之前，请确保在 Excel 中激活求解器加载项，如下所示 −

单击功能区上的"数据"选项卡。 求解器 命令应出现在分析组中，如下所示。

如果您没有找到求解器命令，请按以下方式激活它 −

单击"文件"选项卡。
单击左侧窗格中的"选项"。出现 Excel 选项对话框。
单击左侧窗格中的加载项。
在"管理"框中选择"Excel 加载项"，然后单击"开始"。

出现加载项对话框。选中 求解器插件 并单击 Ok。现在，您应该能够在数据选项卡下的功能区上找到规划求解命令。

求解器使用的求解方法

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

LP 线性单纯形

用于线性问题。 求解器模型在以下条件下是线性的 −

目标单元格是通过将（变化的单元格）*（常量）形式的项加在一起来计算的。
每个约束都满足线性模型要求。这意味着通过将（变化的单元格）*（常量）形式的项加在一起并将总和与常量进行比较来评估每个约束。

广义约化梯度 (GRG) 非线性

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

渐进式

了解求解器计算

求解器需要以下参数 −

决策可变单元格
约束单元格
目标单元格
求解方法

求解器评估基于以下内容 −

决策变量单元格中的值受制于约束单元格中的值。
目标单元格中值的计算包括决策变量单元格中的值。
求解器使用所选的求解方法在目标单元格中产生最优值。

定义一个问题

假设您正在分析一家制造和销售某种产品的公司的利润。要求你找出未来两个季度可以花在广告上的金额，最多为 20,000。每个季度的广告投放水平影响如下 −

销售的单位数量，间接决定销售收入的金额。
相关费用，以及
利润。

您可以继续将问题定义为 −

查找单位成本。
找出每单位的广告费用。
查找单价。

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

如您所见，已对所考虑的 Quarter1 和 Quarter2 进行了计算 −

第 1 季度的可售单位数量为 400，第 2 季度为 600（单元格 - C7 和 D7）。
广告预算的初始值设置为每季度 10000（单元格 - C8 和 D8）。
售出的单位数量取决于每单位的广告成本，因此是季度/广告的预算。每单位成本。请注意，我们使用了 Min 函数来注意查看编号。售出单位数 <= 可用单位数。（单元格 – C9 和 D9）。
收入计算为单价 * 售出的单位数量（单元格 – C10 和 D10）。
费用计算为单位成本 * 可用单位数 + Adv。该季度的成本（单元格 – C11 和 D12）。
利润是收入 - 费用（单元格 C12 和 D12）。
总利润是第 1 季度的利润 + 第 2 季度的利润（单元格 - D3）。

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

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

目标单元格是 D3，其中包含您要最大化的总利润。
决策变量单元格是 C8 和 D8，其中包含两个季度（Quarter1 和 Quarter2）的预算。
有三个约束单元格 - C14、C15 和 C16。
- 包含总预算的单元格 C14 用于设置 20000 的约束（单元格 D14）。
- 包含编号的单元格 C15。 Quarter1 中售出的单位数是设置约束条件 <= Quarter1 中可用的单位数（单元格 D15）。
- 包含编号的单元格 C16。在 Quarter2 中售出的单位数用于设置约束条件 <= Quarter2 中可用的单位数（单元格 D16）。

解决问题

接下来就是使用求解器求解如下 −

步骤 1 − 转到功能区上的数据 > 分析 > 求解器。出现求解器参数对话框。

步骤 2 − 在"设置目标"框中，选择单元格 D3。

步骤 3 − 选择最大值。

步骤 4 − 在通过更改变量单元格框中选择范围 C8:D8。

步骤 5 − 接下来，单击"添加"按钮添加您已确定的三个约束。

步骤 6 − 出现添加约束对话框。如下所示设置总预算的约束条件，然后单击"添加"。

步骤 7 − 设置总数的约束。如下所示在 Quarter1 中售出的单位数，然后单击添加。

步骤 8 − 如下所示设置第 2 季度售出单位总数的限制条件，然后单击"确定"。

求解器参数对话框出现，其中三个约束添加在框 – 受制于约束中。

步骤 9 − 在选择求解方法框中，选择单纯形 LP。

步骤 10 − 单击求解按钮。求解器结果对话框出现。选择 保持求解器解决方案 并点击 OK。

结果将显示在您的工作表中。

如您所见，在给定约束条件下，产生最大总利润的最优解如下所示 −

Total Profit – 30000.
Adv. Budget for Quarter1 – 8000.
Adv. Budget for Quarter2 – 12000.

逐步完成求解器试验解决方案

您可以逐步执行求解器试验解决方案，查看迭代结果。

步骤 1 − 单击求解器参数对话框中的选项按钮。

出现选项对话框。

步骤 2 − 选择"显示迭代结果"框并单击"确定"。

步骤 3 − 出现求解器参数对话框。点击解决。

步骤 4 − 显示试用解决方案 对话框出现，显示消息 - 求解器已暂停，当前解决方案值显示在工作表上。

如您所见，当前迭代值显示在您的工作单元格中。您可以停止求解器接受当前结果，或者继续求解器在进一步的步骤中寻找解决方案。

步骤 5 − 点击继续。

每一步都会出现显示试用解决方案对话框，最后在找到最优解后，会出现求解器结果对话框。您的工作表每一步都会更新，最终显示结果值。

保存求解器选择

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

您可以通过保存工作簿来使用工作表保存"求解器参数"对话框中的最后选择。
工作簿中的每个工作表都可以有自己的求解器选择，当您保存工作簿时，所有这些都会被保存。
您还可以在工作表中定义多个问题，每个问题都有自己的求解器选择。在这种情况下，您可以使用求解器参数对话框中的加载/保存单独加载和保存问题。
- 单击加载/保存 按钮。出现加载/保存对话框。
- 要保存问题模型，请输入要放置问题模型的垂直范围的空单元格的第一个单元格的引用。单击保存。