如何使用Excel的目标搜索和求解器来求解未知变量

当您拥有计算所需的所有数据时，Excel便非常强大。

但是如果它可以求解未知变量，那岂不是很好吗？<

有了Goal Seek和Solver插件，它可以。我们将向您展示如何。继续阅读有关如何使用“目标寻求"解决单个单元格或使用“求解器"解决更复杂方程式的完整指南。

如何在Excel中使用“目标寻求"

目标寻求已经存在内置到Excel中。它位于假设分析菜单中的数据标签下：

For this example, we’ll be using a very simple set of numbers. We have three quarters’ worth of sales numbers and a yearly goal. We can use Goal Seek to figure out what the numbers need to be in Q4 to make the goal.

如您所见，当前的销售总量为114,706个单位。如果我们想在今年年底之前卖出250,000，我们在第四季度需要卖出多少？ Excel的“目标搜索"将告诉我们。

以下是逐步使用“目标搜索"的方法：

在我们的案例中，解决方案是135,294套。当然，我们可以通过从年度目标中减去运行总额来发现这一点。但是，Goal Seek也可以用于已经有数据的单元格中。

请注意，Excel会覆盖我们之前的数据。最好对数据副本进行“目标搜索" 。另外，最好对使用Goal Seek生成的复制数据做个记录。您不想将其混淆为当前的准确数据。

因此，“目标搜索"是一项非常有用的Excel功能，但并不是那么令人印象深刻。让我们看一个更有趣的工具：Solver加载项。

Excel的Solver会做什么？

总之，Solver就像一个多元版本寻求目标的人。它采用一个目标变量，并调整许多其他变量，直到获得所需的答案。

它可以求解数字的最大值，数字的最小值或精确数字。

而且它在约束内起作用，因此，如果一个变量不能更改，或者只能在指定范围内变化，则Solver会将其考虑在内。

这是一种很好的方法解决Excel中的多个未知变量。但是，查找和使用它并不容易。

让我们看一下如何加载Solver加载项，然后跳到如何在Excel 2016中使用Solver。

如何加载规划求解加载项

Excel默认没有规划求解。它是一个加载项，因此，与其他强大的Excel功能一样，您必须先加载它。幸运的是，它已经在您的计算机上。

转到文件>选项>加载项。然后单击管理：Excel加载项旁边的转到。

如果此下拉菜单中显示的不是“ Excel加载项"，则表示它将需要更改：

在出现的窗口中，您会看到一些选项。确保选中了 Solver加载项旁边的框，然后点击确定。

您现在将看到 Solver < 数据标签的分析组中的/ strong>按钮：

如果您已经在使用数据分析工具库，则请参阅数据分析按钮。如果没有，则将自动显示求解器。

现在您已经加载了加载项，让我们看一下如何使用它。

如何在Excel中使用求解器

任何求解器动作都包含三个部分：目标，变量单元格和约束。我们将逐步完成每个步骤。

您还可以生成报告，我们将在Solver示例中简要介绍一下报告

您可能会告诉Excel一个变量必须大于200。当尝试使用不同的变量值时，使用该特定变量的Excel不会低于201。

要添加约束，请单击约束列表旁边的添加按钮。您会看到一个新窗口。在 Cell Reference 字段中选择要约束的一个或多个单元格，然后选择一个运算符。

以下是可用的运算符：

AllDifferent 有点令人困惑。它指定您为单元格引用选择的范围内的每个单元格都必须是一个不同的数字。但是它还指定它们必须在1到单元格数之间。因此，如果您有三个单元格，则最终将得到数字1、2和3（但不一定是该顺序）

最后，为约束添加值。

请务必记住，您可以选择多个单元格作为“单元格引用"。例如，如果您希望六个变量的值超过10，则可以全部选择它们，并告诉Solver它们必须大于或等于11。您不必为每个单元格添加约束。

您还可以使用“求解器"主窗口中的复选框来确保未指定约束的所有值均为非负值。如果您希望变量变为负数，请取消选中此框。

求解器示例

要了解所有方法的工作原理，我们将使用求解器加载项快速计算。以下是我们开始使用的数据：

其中有五个不同的工作，每个工作的薪资不同。我们还提供了理论工作者在给定的一周内完成这些工作的小时数。我们可以使用Solver加载项来找出如何在使某些变量保持在某些约束范围内的同时最大化总薪酬。

以下是我们将要使用的约束：

写出诸如以下的约束会有所帮助

这是我们在Solver中进行设置的方式：

首先，请注意，我已经创建了该表的副本 >，所以我们不会覆盖包含当前工作时间的原始记录。

第二，请注意，大于和小于约束中的值是大于或小于一个。那是因为没有大于或小于选项。只有大于等于或小于等于。

让我们点击解决，看看会发生什么。

Solver找到了解决方案！正如您在上方窗口左侧所看到的，我们的收入增加了$ 130。并已满足所有约束条件。

要保留新值，请确保选中 Keep Solver Solution （保持求解器解决方案），然后按 OK 。

但是，如果您需要更多信息，则可以从窗口右侧选择一个报告。选择所有需要的报告，告诉Excel是否要概述它们（我推荐），然后按确定。

这些报告是在工作簿中的新工作表上生成的并为您提供有关Solver加载项获得答案的过程的信息。

在我们的案例中，报告并不十分令人兴奋，并且那里没有很多有趣的信息。但是，如果运行更复杂的求解器方程，您可能会在这些新工作表中找到一些有用的报告信息。只需单击任何报告侧面的 + 按钮即可获取更多信息：

Solver高级选项

如果您对统计信息不太了解，您可以忽略Solver的高级选项，而直接按原样运行。但是，如果您运行的是大型复杂的计算，则可能需要研究一下。

最明显的是求解方法：

您可以在GRG非线性，单纯形之间选择LP和Evolutionary。 Excel提供了有关何时应使用每一个的简单说明。更好的解释需要统计和回归知识。

要调整其他设置，只需点击选项按钮。您可以告诉Excel有关整数最优性，设置计算时间约束（对于大量数据集有用），以及调整GRG和Evolutionary求解方法进行其计算的方式。

同样，如果您不知道什么无论采用哪种方式，都不必担心。如果您想了解有关使用哪种求解方法的更多信息，Engineer Excel上有一篇很好的文章为您介绍。如果您想要最大的准确性，那么Evolutionary可能是一个不错的选择。请注意，这将需要很长时间。

目标寻求和求解器：将Excel提升到一个新水平

现在，您已熟悉求解未知变量的基础知识在Excel中，您可以打开一个全新的电子表格计算世界。

Goal Seek可以通过更快地进行一些计算来帮助您节省时间，而Solver为Excel的计算能力增加了巨大的力量。

与他们融洽相处只是一个问题。您使用它们越多，它们将变得越有用。

您是否在电子表格中使用“目标搜索"或“求解器"？您还可以提供什么其他技巧来获得最佳答案？在下面的评论中分享您的想法！