Excel Solver工具

什么是Solver

Solver是一种由MS Excel支持的附加程序编程工具 。它是一种优化工具，通过改变目标问题的假设来确定最优解并获取期望的结果 ，它是一种“假设分析”的一种类型，当用户想要找出给定两个或多个假设集的“最佳”结果时很有用。

Excel Solver主要用于 模拟和优化多个商业和工程原型 。它也可用于解决线性规划模型（线性优化问题），因此也被称为 线性规划求解器 。此外，Solver在解决平滑和非平滑线性问题方面也很受欢迎。

实际上， Microsoft Solver 不可能解决所有可能的问题，但它 在处理各类优化问题以作出最佳决策时至关重要 。例如，您可以使用MS Solver来最大化投资回报率（ROD），为您的广告活动选择最佳预算，计算销售人员产生的利润并根据此数字确定要雇用的销售人员数量，为员工撰写最佳工作计划等等。

将Solver添加到你的Excel工作表

Solver插件已添加到所有版本的Microsoft Excel，尽管默认情况下未启用。您需要手动将Solver添加到Excel工作表中。要将Solver添加到Excel工作表中，请按照以下步骤操作。

1. 打开您的Excel工作表，点击 文件->选项。
   

2. Excel选项对话框会出现。从窗格左侧，点击 插件选项 。
   

3. 中央将显示插件屏幕。在位于屏幕底部的 管理框 中，确保 选择插件选项 。 点击“前往” 。
   

4. 并将出现插件对话框。选中Solver的 ‘ 添加插件’框 并单机 “确定”按钮 。
   

5. 就这样，它会 将Solver工具添加到您的Excel工作表中 。

Excel中的Solver在哪里

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

在Excel中使用Solver的步骤

在继续使用Excel Solver插件的步骤之前，让我们分析一下我们将在Excel工作表中使用Solver运营研究技术解决的问题。下面是一个简单的优化问题。

问题： 假设你是一个健身房的业主，计划扩大服务和锻炼项目，以吸引更多客户。为此，你需要购买各种新的设备，费用为90,000美元，并且你需要在12个月的时间里分期偿还这笔金额。

目标： 找出每位客户的最小费用，让你能够在规定的时间范围内支付购买新设备的预算。

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

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

步骤1：运行Excel Solver

点击 数据 选项卡，位于 分析 组。从给定选项中，点击 Solver 按钮。

步骤2：定义问题

它将打开规划求解参数窗口。我们需要设置三个主要的规划求解组件，它们如下：

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

让我用简单的方式解释一下Excel规划求解器是如何使用上述参数来解决问题的。它通过调整我们在“目标”单元格中定义的公式所需的变量的值，并遵守“约束条件”单元格的限制，确定公式的最优值（最大值、最小值或任意给定值）。

让我们简要了解一下这三个规划求解组件：

目标单元格

“目标”单元格，又称“目标”单元格（在Excel的早期版本中命名），包含描述给定问题目标或目标的公式，其中目标可以是最大化、最小化或根据某个目标值实现。

让我们用一个例子来理解一下，

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

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

可变单元格

可变单元格，也称为更改单元格或可调整单元格（在Excel的早期版本中命名），保存可以修改的可变数据以获得最佳目标。Excel求解器支持最多200个可变单元格。

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

• 我们可以将单元格B7归类为可变单元格，因为其值可以更改，需满足每月预计客户不超过50；
• 单元格B8也可以归类为可变单元格，因为它表示可以更改的“每项服务费用”。

注意：虽然在我们的案例中，可变单元格是相邻的，但是如果单元格是非相邻的，选择第一个可变单元格，然后按下键盘上的 Ctrl 键并选择非相邻的可变单元格（们）。或者另一种方法是手动输入单元格引用，并用逗号分隔它们。

约束条件

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

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

1. 比较运算符： 小于（<），小于等于（<=），等于（=），大于（>）和大于等于（>=）是四个主要的比较运算符，用于设置约束之间的关系。您可以在单元格引用中选择一个单元格，然后选择任何比较运算符（<=，=或>=）。最后，在约束框中输入数字/单元格引用。
   

2. 整数： 选择int，您会注意到 约束 框中的值将变为整数。
   

3. 不同的值： 您可以选择dif，在引用范围中输入不同的值。您会注意到 约束 框中的参数将变为 AllDifferent。
   

4. 二进制：二进制允许用户将引用的单元格限制为0或1。要启用二进制选择 bin ，您会注意到约束框参数将变为 binary 。
   

• 点击位于“ Subject to the Constraints ”框右侧的“ Add ”按钮。

• 将打开“添加约束”窗口。在相应的位置输入值，然后点击“添加”。它将在约束窗口中添加约束。

• 要在模型中添加另一个约束，再次点击“ Add ”按钮。它将允许您在窗口中输入下一个约束。同样，输入其他约束。
• 输入完所有约束后，点击“确定”按钮。它将带您进入主要的求解器参数

在我们的案例中，我们输入了两个约束，如下所示：

1. B6=90000 ，此约束值表示购买新健身器材的费用为9万美元。
2. **B7 <=50 ** ，此约束值表示每月预计的客户数不超过50个。

步骤3：解决问题

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

1. 点击位于求解参数底部的 求解 按钮，这将使求解工具寻找给定问题的最优解。
   

2. 根据问题的复杂程度、系统内存或处理器速度的不同，处理解决方案可能需要一些时间（从几分钟到几小时）。

3. 一旦求解完成处理，它将在对话框窗口中呈现 求解器输出 。从窗口中选择“保留求解器解决方案”并点击 确定 按钮。
   

4. 它将自动关闭 求解结果 对话框窗口，并且输出将立即出现在你的Excel工作表中。
   

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

重要事项

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

在Excel中保存和加载求解器问题

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

例如，我们已经计算了上述模型中仅适用于50位客户的每项服务的成本。但您可能还想尝试不同数量的项目客户每月，计算最低服务成本，并评估各种服务成本。

因此，保存和加载Excel求解器方案非常重要。保存求解器数据表示选择要保存数据的单元格范围，而加载求解器问题表示给Excel提供问题保存的单元格范围。

保存模型

按照以下步骤保存Excel求解器方案：

1. 打开您计算求解器模型输出的Excel工作表并运行求解器。求解器窗口将打开。
2. 单击求解器参数右侧的加载/保存按钮
   

3. 它将弹出一个加载/保存模型窗口，要求您选择一定范围的单元格来保存您的场景。选择指定数量的单元格，然后单击保存按钮。在我们的例子中，我们将选择6个单元格。
   

4. Excel将保存您的求解器场景，并给出以下输出。（参考下面的截图）。
   

加载保存的模型

按照下面给出的步骤加载或恢复已保存的Excel求解器场景：

1. 打开您计算求解器模型输出的Excel工作表并运行求解器。求解器窗口将打开。

2. 单击求解器参数右侧的加载/保存按钮
   

3. 选择先前保存的单元格范围，然后单击 加载选项 。
   

4. 将显示加载模型对话框窗口。选择 替换
   

5. 它将立即打开带有先前保存的模型参数的Excel求解器窗口。只需单击求解按钮即可重新计算所有求解器数据。