如何自动化 Excel 求解器

已发表: 2021-10-23

在如何充分利用 Excel 求解器中，我介绍了增强模型和增强分析能力的技术。我收到最多问题的主题之一是自动求解器来求解多个值。

上一篇有简单的说明，但是没有专门讲如何从头到尾实现。在本文中，我们将从头到尾深入介绍概念和方法。最后，您将能够自动化您自己的任何个人模型并扩展您的分析。

我们将如何做

我们将利用 Excel 中的宏记录器来处理大部分实际代码。记录宏工具将允许 Excel 将我们的手动步骤转换为 VBA 代码。

然后，我们将确定要更改的关键代码段。然后我们将创建一个循环来动态选择和替换这些值并再次运行求解器。

这将是半技术性的，但通过这种开发风格，我们可以专注于过程和概念，而不是专注于如何编写代码。

设置宏记录器

宏记录功能位于功能区的开发人员选项卡中。这在默认情况下不可用，但很容易实现。如果您导航到功能区选项，您将看到开发人员选项卡的复选框。

下面的屏幕截图适用于 Mac，但基于 PC 的 Excel 是类似的。

如何让开发人员使用 excel 功能区

如果您尚未安装求解器插件，您可以通过插件菜单安装它。 Microsoft 支持站点提供适用于所有平台的说明，在 Excel 中加载求解器加载项。

录制宏

设置完成后，我们就可以开始录制了！使用所有公式和参考适当地设置您的工作区。在构建求解器模型之前，您需要将所有内容设置到要点。

返回功能区上的开发人员选项卡。您会看到一张带有红点的纸。单击该按钮以打开记录器。

在excel中记录宏按钮

为您的宏命名并根据需要填写说明。

命名你的 excel 宏

一旦你点击确定，录音机就会开始。

完成设置求解器的步骤并求解。完成后，返回开发人员选项卡并停止录制。它与您用于开始录制的按钮位置相同。

停止在 excel 位置录制宏

现在我们有了基本代码，我们可以清理它并为下一步做准备。

清理代码

现在我们可以编辑代码，删除任何不必要的部分，并确保我们只使用宏录制的基本部分。

您可以通过 Excel 的 VBA 编辑器编辑代码。要访问您录制的代码，请单击宏按钮。

如何在excel中查看宏

在新菜单中导航到您的宏，然后单击编辑。

在excel中编辑宏代码的位置

现在我们打开了代码编辑器，我们可以开始进行调整。

删除不必要的代码

如果您在录制时碰巧点击了一堆其他东西，您现在可以清除它们，并留下类似于下面的内容。如果有很多额外的代码并且您担心破坏它，您可以随时重新录制。

录制后的示例求解器 VBA 代码

设置求解器参考

在我们继续之前的最后一步，转到“工具”>“参考”并选择“求解器”。如果你不这样做，包将不会加载到宏中，你会得到一个错误。

您现在可以更改参数，打开宏菜单，选择您的宏，点击运行，它将更新结果。

让我们根据求解器模型排列代码。你会看到它是如何排列的。语言是不同的，但你可以拼凑出它的全部含义。

比较求解器参数与宏代码

接下来，我们将隔离需要更改的部分并结束我们的自动化。

冲洗并重复——建立循环

现在我们有了一个有效的求解器。我们需要多次重复。让我们一起举一个例子并巩固计划。

我们在第一个模型中以 1,000 美元的预算开始，但我们希望以 100 美元至 2,500 美元的增量做同样的事情。

为宏示例设置表

让我们潜入吧！我们在 B 列中有一个值列表，在 C 列中有我们的输出。如果我们写出这个过程，它看起来像，

将总支出设置为 B10 中的值。
求解模型。
将输出放在 C10 中。
选择下一个 B 值。
在 C 行设置输出。
对 B 中的所有值重复步骤 2-5。

开始循环

循环是重复操作直到达到逻辑终点的编码结构。一个循环在这里会派上用场，重复相同的过程，在每个值上运行求解器，直到我们到达该值的末尾。

对于本示例，我们将通过行数定义终点。 B10:B25 有 16 个值。

我们将开始我们的循环并将其包裹在我们的求解器代码中。我们将设置一个变量来保存循环编号 (i) 并遍历每个值，每次求解循环。

启动excel宏的循环代码

重置求解器

我们将在代码的开头添加另一个条件。由于我们希望每次迭代都重新运行求解器，因此我们将在每个循环中重置它。这将清除设置并启动新模型。

我们可以通过SolverReset做到这一点。

重置求解器代码 excel 宏

更新单元格引用

我们现在解决了 16 次，但需要更新我们的单元格引用。虽然我们最初引用了 B10，但每次运行时我们需要向下移动一个单元格以更新限制。我们可以通过 offset 函数来做到这一点。我们可以更新“$B$10”以引用单元格，然后偏移 i 行（第一次运行时为 0，第二次运行时为 1 行，依此类推）。每次循环运行时， i 增加一。

在excel中更新宏的单元格引用代码