使用 Excel 求解器优化

---

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

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

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

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

您可以使用 Solver 为各种问题找到最佳解决方案，例如 –

• 确定制药单位的月度产品组合，以最大限度地提高盈利能力。

• 安排组织中的劳动力。

• 解决交通问题。

• 财务规划和预算编制。

激活求解器插件

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

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

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

• 单击文件选项卡。
• 单击左窗格中的选项。出现 Excel 选项对话框。
• 单击左侧窗格中的加载项。
• 在“管理”框中选择 Excel 加载项，然后单击“执行”。

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

求解器使用的求解方法

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

LP单工

用于线性问题。甲求解模型是在下列条件下的线性-

• 目标单元格是通过将（变化单元格）&ast（常量）形式的项加在一起来计算的。

• 每个约束满足线性模型要求。这意味着通过将（变化单元格）&ast（常量）形式的项相加并将总和与常量进行比较来评估每个约束。

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

用于平滑非线性问题。如果您的目标单元格、任何约束条件或两者都包含对不属于（更改单元格）&ast（常量）形式的更改单元格的引用，则您具有非线性模型。

进化的

用于平滑非线性问题。如果您的目标单元格、任何约束条件或两者都包含对不属于（更改单元格）&ast（常量）形式的更改单元格的引用，则您具有非线性模型。

了解求解器评估

求解器需要以下参数 –

• 决策变量单元格
• 约束单元
• 目标细胞
• 解法

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

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

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

• 求解器使用所选的求解方法在目标单元格中​​产生最佳值。

定义问题

假设您正在分析制造和销售某种产品​​的公司的利润。要求您找出未来两个季度可用于广告的金额，最多为 20,000。每个季度的广告水平影响以下方面 –

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

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

• 查找单位成本。
• 求出每单位的广告成本。
• 查找单价。

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

正如您所观察到的，所考虑的 Quarter1 和 Quarter2 的计算是 –

• 第一季度可供出售的单位数量为 400，第二季度为 600（单元格 – C7 和 D7）。

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

• 售出的单位数量取决于每单位的广告成本，因此是本季度/Adv. 的预算。每单位成本。请注意，我们使用了 Min 函数来小心地看到 no。<= 没有售出的单位数 可用单位数。（单元格 – C9 和 D9）。

• 收入计算为单位价格和销售单位的最后数量（单元格 – C10 和 D10）。

• 费用计算为单位成本 & 可用单位数 & 加上 Adv。该季度的成本（单元格 – C11 和 D12）。

• 利润是收入 – 费用（单元格 C12 和 D12）。

• 总利润是第 1 季度的利润加上第 2 季度的利润（单元格 – D3）。

接下来，您可以设置 Solver 的参数，如下所示 –

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

• 目标单元格是 D3，其中包含您想要最大化的总利润。

• 决策变量单元格是 C8 和 D8，其中包含两个季度的预算 – Quarter1 和 Quarter2。

• 共有三个约束单元 – C14、C15 和 C16。

  • 包含总预算的单元格 C14 将设置约束为 20000（单元格 D14）。

  • 包含编号的单元格 C15。Quarter1 中销售的单位数量是设置 <= no 的约束。Quarter1 中可用的单位数（单元格 D15）。

  • 包含编号的单元格 C16。Quarter2 中销售的单位数量是设置 <= no 的约束。Quarter2 中可用的单位数（单元格 D16）。

解决问题

下一步是使用 Solver 找到解决方案，如下所示 –

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

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

步骤 3 – 选择最大。

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

步骤 5 – 接下来，单击“添加”按钮以添加您确定的三个约束。

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

步骤 7 – 设置总数的约束。如下所示在第 1 季度销售的单位数量，然后单击添加。

步骤 8 – 设置总数的约束。如下所示在 Quarter2 中销售的单位数量，然后单击“确定”。

解算器参数对话框出现，并在框中添加了三个约束——受约束。

步骤 9 – 在“选择求解方法”框中，选择 Simplex LP。

步骤 10 – 单击求解按钮。出现求解器结果对话框。选择保留求解器解决方案并单击确定。

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

如您所见，在给定约束下产生最大总利润的最佳解决方案如下 –

• 总利润 – 30000。
• 高级 第 1 季度的预算 – 8000。
• 高级 第二季度预算 – 12000。

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

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

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

在选项对话框。

步骤 2 – 选择“显示迭代结果”框并单击“确定”。

步骤 3 –出现求解器参数对话框。单击求解。

Step 4 – Show Trial Solution对话框出现，显示消息 – Solver paused, current solution values shown on worksheet。

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

步骤 5 – 单击继续。

该展会试验解决方案出现在每一个步骤对话框，最后是最佳的解决方案被发现后，规划求解结果对话框。您的工作表在每一步都会更新，最终显示结果值。

保存求解器选择

对于使用 Solver 解决的问题，您有以下保存选项 –

• 您可以通过保存工作簿来将“求解器参数”对话框中的最后选择与工作表一起保存。

• 工作簿中的每个工作表都可以有自己的规划求解选项，当您保存工作簿时，所有这些选项都将被保存。

• 您还可以在工作表中定义多个问题，每个问题都有自己的求解器选择。在这种情况下，您可以使用求解器参数对话框中的加载/保存单独加载和保存问题。

  • 单击加载/保存按钮。出现加载/保存对话框。

  • 要保存问题模型，请输入要放置问题模型的垂直空单元格范围的第一个单元格的引用。单击保存。

• 问题模型（求解器参数集）从您作为参考给出的单元格开始出现。

• 要加载问题模型，请输入包含问题模型的整个单元格范围的引用。然后，单击加载按钮。