Excel 中的规划求解

什么是 Solver？

Solver 是 MS Excel 支持的一个加载项编程工具。它是一个优化工具，使用运筹学技术通过改变目标问题的假设来确定最优解并获取所需结果。它是一种“假设分析”工具，当用户想要找出给定两组或多组假设下的“最佳”结果时非常有用。

Excel Solver 主要用于模拟和优化多种业务和工程原型。它还有助于解决线性规划模型（线性优化问题），因此，它也被称为线性规划求解器。除此之外，Solver 还因解决光滑和非光滑线性问题而闻名。

确实，Microsoft Solver 无法解决所有可能的问题，但当用户处理各种优化问题以做出最佳决策时，它非常有效。例如，您可以使用 MS Solver 最大化投资回报率 (ROD)，为您的广告系列选择最佳预算，计算销售人员产生的利润，并根据该数字确定要聘请的销售人员数量，为您的员工制定最佳工作时间表等。

将 Solver 添加到您的 Excel 工作表中

Solver 加载项随 Microsoft Excel 的所有版本一起提供，但默认情况下未启用。您需要手动将 Solver 添加到您的 Excel 工作表中。要将 Solver 集成到您的 Excel 工作表中，请按照以下步骤操作。

1. 打开您的 Excel 工作表，单击文件 -> 选项。
   
2. 将出现 MS Excel 选项对话框。从左侧窗格中，单击加载项选项。
   
3. 加载项屏幕将显示在中间。在屏幕底部标有管理框的位置，确保在其字段中选择了加载项选项。单击“转到”。
   
4. 将出现加载项对话框。勾选 Solver '加载项' 复选框，然后单击“确定”按钮。
   
5. 就这样；它将将 Solver 工具添加到您的 Excel 工作表中。

Excel 中的 Solver 在哪里？

Solver 加载项编程工具位于“数据”选项卡中的“分析”组中。

在 Excel 中使用 Solver 的步骤

在继续进行 Excel Solver 加载项的步骤之前，让我们先分析一下我们将使用 Solver 运筹学技术在 Excel 工作表中解决的问题。下面是一个简单的优化问题。

问题： 假设您是一家健身房的老板，并计划扩展服务和锻炼项目以吸引更多客户。为此，您需要购买各种新设备，费用为 90,000 美元，并且必须在 12 个月内分期偿还。

目标： 找出每个客户的最低成本，以便您能在规定的时间内支付新设备的预算。

为简化上述问题，我们在 Excel 工作表中创建了一个模型。

现在，让我们按步骤解决上述问题。

步骤 1：运行 Excel Solver

单击位于“分析”组中的“数据”选项卡。在给出的选项中，单击“Solver”按钮。

步骤 2：定义问题

这将打开 Solver 参数窗口。我们需要设置 3 个主要的 Solver 组件，它们如下：

1. 目标单元格
2. 变量单元格
3. 约束

让我们以简单的方式解释 Excel Solver 如何使用上述参数解决任何问题？它通过调整我们在变量单元格中指定的值并遵守约束单元格的限制，来确定我们在目标单元格中定义的公式的最优值（最大值、最小值或任何给定值）。

让我们简要了解一下这 3 个 Solver 组件。

目标单元格

目标单元格，也称为目标单元格（在 Excel 的早期版本中命名），其中包含一个公式，该公式描述了给定问题的目标或目的，该目标可以最大化、最小化或基于某个目标值来实现。

让我们通过一个例子来理解这一点，

在我们的例子中，B10 是我们的目标单元格，它将帮助我们计算付款期限，其中公式的输出“=B6 / (B7 * B8)”应等于 12。

注意：请确保目标单元格是一个公式，否则在解决问题时会报错。

变量单元格

变量单元格，也称为更改单元格或可调单元格（在 Excel 的早期版本中命名），包含可以更改以获得最优目标的变量数据。Excel Solver 最多支持 200 个变量单元格。

让我们为此示例查找变量单元格。以下是其值可以更改的单元格：

• 我们可以将单元格 B7 分类为变量单元格，因为它的值可以更改，因为每个月的预计客户数应小于或等于 50；并且
• 单元格 B8 也可以归类为变量单元格，因为它代表“每次服务的成本”，该成本也可以更改。

注意：虽然在我们的例子中变量单元格是相邻的，但如果单元格不相邻，请选择第一个变量单元格，然后从键盘按住 Ctrl 键并选择不相邻的变量单元格。或者另一种方法是手动输入单元格引用，用逗号分隔它们。

约束

约束表示问题潜在解决方案的限制或局限性。简单来说，我们可以得出结论，约束是 Excel Solver 中必须满足的条件。

Solver 工具使用户能够通过建立引用单元格与约束之间的关系来定义约束。您可以选择任何给定选项来指定约束关系。

1. 比较运算符：小于（<）、小于等于（<=）、等于（=）、大于（>）和大于等于（>=）是用于设置约束之间关系的四个主要比较运算符。您可以选择“单元格引用”中的一个单元格，然后选择任何比较运算符（<=、= 或 >=）。最后，在“约束”框中键入您的数字/单元格引用。
   
   输入的值可以是整数、二进制或 dif。
2. 整数：选择 int，您会注意到约束框中的值将更改为整数。
   
3. 不同值：您可以选择 dif 输入范围内的不同值。您会注意到约束框中的参数将更改为AllDifferent。
   
4. 二进制：二进制允许用户将引用单元格限制为 0 或 1。要启用二进制，请选择bin，您会注意到“约束”框中的参数将更改为binary。
   
   请按照以下步骤在您的 Excel 工作表中添加约束：
   • 单击“受约束条件”框右侧的“添加”按钮。
     
   • 将打开“添加约束”窗口。在相应的位置输入值。完成后单击“添加”。它将在约束窗口中添加约束。
     
   • 要向模型中添加另一个约束，请再次单击“添加”。这将允许您在窗口中输入下一个约束。同样，也输入其他约束。
   • 输入完所有约束后，单击“确定”按钮。这将带您进入主 Solver 参数窗口。
     

在我们的例子中，我们输入了两个约束，它们如下：

1. B6=90000，此约束值表示购买新健身器材的费用等于 90,000 美元。
2. B7<=50，此约束值表示每月的预计客户数低于

步骤 3：解决问题

现在，我们已经填写了所有参数，是时候解决问题了。

1. 单击 Solver 参数窗口底部的“求解”按钮。这将使 Solver 工具能够为给定的问题寻找最佳解决方案。
   
2. 尽管根据问题的复杂性、系统的内存或处理器速度，处理解决方案可能需要一些时间（从几分钟到几小时）。
3. Solver 完成处理后，它将在对话框窗口中显示Solver 输出。从窗口中选择“保留 Solver 解决方案”，然后单击“确定”按钮。
   
4. 它将自动关闭Solver 结果对话框窗口，输出将立即显示在您的 Excel 工作表中。
   

在我们的例子中，您会注意到单元格B8 的值将变为 150 美元。此成本表示每位客户的最低成本，它将使您能够在每月至少有 50 位客户的情况下，在 12 个月内支付新机器的费用。

重要提示

• 如果您的处理器速度慢或问题过于复杂，或者由于任何其他原因 Solver 工具处理您的问题的时间过长，您可以通过按 Esc 键来暂停该过程。Excel 将停止处理，并使用找到的最后一个变量值计算 Excel 工作表。
• 如果您想了解有关已解决问题的更多详细信息，请转到“报表”框，选择报表类型，然后单击“确定”按钮。Excel 将在新工作表上创建报表。

在 Excel 中保存和加载 Solver 问题

解决特定问题后，您可能需要保存变量单元格的值，以便稍后进行观察或处理。

例如，我们只计算了上述模型中 50 位客户的每次服务成本。但是，您可能还想尝试不同数量的预计客户数，计算最低服务成本，并评估各种服务成本。

因此，保存和加载 Excel Solver 方案非常重要。保存 Solver 数据意味着选择要保存数据的单元格范围，而加载 Solver 问题则是指示 Excel 您的问题已保存的单元格范围。

保存模型

请按照以下步骤保存 Excel Solver 方案：

1. 打开您已经计算出 Solver 模型输出并运行了 Solver 的 Excel 工作表。Solver 窗口将打开。
2. 单击 Solver 参数右侧的“加载/保存”按钮。
   
3. 这将打开“加载/保存模型”窗口，要求您选择一个指定的单元格范围来保存您的方案。选择指定的单元格数量，然后单击“保存”按钮。在本例中，我们将选择 6 个单元格。
   
4. Excel 将保存您的 Solver 方案，并给出以下输出。（请参阅下面的截图）。
   

加载已保存的模型

请按照以下步骤加载或还原已保存的 Excel Solver 方案：

1. 打开您已经计算出 Solver 模型输出并运行了 Solver 的 Excel 工作表。Solver 窗口将打开。
2. 单击 Solver 参数右侧的“加载/保存”按钮。
   
3. 选择之前保存的单元格范围，然后单击“加载”选项。
   
4. 将显示“加载模型”对话框窗口。选择“替换”。
   
5. 它将立即打开 Excel Solver 窗口，其中包含先前保存的模型参数。只需单击“求解”按钮即可重新计算所有 Solver 数据。