什么是Excel求解器?

Excel Solver加载项执行数学优化。这通常用于将复杂的模型适合数据或找到问题的迭代解决方案。例如，您可能希望使用等式来符合某些数据点的曲线。求解器可以在等式中找到常量，其给予最适合数据。另一个应用是难以重新排列模型以使所需的输出等式的主题。

求解器在Excel中的位置?

求解插件包含在Excel中，但它并不总是作为默认安装的一部分加载。要检查它是否加载，请选择数据选项卡并寻找解算器图标分析部分。

如果你不能在DATA标签下找到求解器，那么你将需要加载加载插件:

1. 选择文件选项卡，然后选择选项。

   

2. 在选项对话框选择加载项从左侧的标签。

   

3. 在窗口底部，选择Excel插件从管理下拉选择去…

   

4. 检查旁边的复选框解算器插件并选择好吧。

   

5. 这解算器命令现在应该出现在数据标签。你准备使用求解器。

   

在Excel中使用求解器

让我们从一个简单的例子开始理解求解器的作用。假设我们想知道一个面积为50平方单位的圆的半径是多少。我们知道圆的面积的方程a = pi r²）。当然，我们可以重新排列这个方程，以给定区域所需的半径，但是为了例子，让我们假装我们不知道如何做到这一点。

创建一个半径为in的电子表格B1计算面积B2使用等式= pi（）* b1 ^ 2。

我们可以手动调节这个值B1直到B2显示一个足够接近50的值。根据我们需要的准确程度，这可能是一种实用的方法。但是，如果我们需要非常精确，需要很长时间才能进行所需的调整。实际上，这基本上是求解器的奴役。它对某些单元格中的值进行调整，并检查目标单元中的值：

1. 选择数据选项卡,解算器，以载入解算器参数对话框

2. 设定目标细胞是该地区，B2。这是将被检查的值，调整其他单元格直到这个达到正确的值。

   

3. 选择按钮的价值：将值设为50。这是B2应该达到的值。

   

4. 在标题为通过更改可变单元格：输入包含半径的单元格，B1。

   

5. 保留其他选项，因为它们是默认选择并选择解决。进行优化，调整B1的值，直到B2为50且解决的结果对话显示。

   

6. 选择好吧保持溶液。

   

这个简单的例子显示了求解器如何工作。在这种情况下，我们可以更容易地以其他方式解决方案。接下来，我们将看一些求解器给出解决方案的一些例子，这将难以找到任何其他方式。

使用Excel Solver加载项拟合复杂的模型

Excel有一个内置的函数来执行线性回归，通过一组数据拟合一条直线。许多常见的非线性函数可以线性化，这意味着线性回归可以用于拟合函数，如指数。对于更复杂的函数，求解器可以用来执行“最小二乘最小化”。在这个例子中，我们将考虑拟合一个这种形式的方程ax残雪^ ^ b + d给下面显示的数据。

这包括以下步骤:

1. 用列A中的x值和列B中的y值排列数据集。

2. 在电子表格中创建4个系数值(a、b、c和d)，这些值可以是任意的起始值。

3. 创建一个列安装Y的值,使用一个方程ax残雪^ ^ b + d形式引用系数在步骤2中创建的x值列a .注意,为了复制公式列,系数必须绝对引用,引用x值必须是相对的。

   

4. 虽然不是必须的，但您可以通过将两个y列与单个XY散点图上的x值绘制在一起来直观地显示出方程的拟合程度。使用它是有意义的标记对于原始数据点，由于这些是具有噪声的离散值，并且为装配方程使用线。

   

5. 接下来，我们需要一种方法来量化数据和拟合方程之间的差异。标准的方法是计算差的平方和。在第三列中，对于每一行，从拟合的方程值中减去Y的原始数据值，然后将结果平方。所以,在D2，该值由= (C2-B2) ^ 2。然后计算所有这些平方值的和。因为这些值是平方的，所以它们只能是正的。

   

6. 现在可以使用Solver执行优化了。有四个系数需要调整(a, b, c和d)。您还需要将一个客观值最小化，即差异平方和。像上面一样启动求解器，并设置求解器参数来引用这些值，如下所示。

   

7. 取消选择使无约束变量非负，这将迫使所有系数采取正值。

   

8. 选择解决检查结果。该图表将更新给出良好的适应度指示。如果求解器在第一次尝试中没有产生良好的匹配，您可以再次尝试运行它。如果匹配度有所提高，请尝试从当前值进行解析。否则，您可以尝试在解析之前手动改进匹配。

   

9. 一旦得到一个很好的拟合，就可以退出求解器。

迭代求解模型

有时存在相对简单的等式，其在某些输入方面给出了输出。但是，当我们尝试颠倒解决问题时，无法找到一个简单的解决方案。例如，车辆消耗的功率约为由p = av + bv ^ 3式中，v为速度，a为滚动阻力系数，b为气动阻力系数。虽然这是一个相当简单的方程，但要重新整理出一个给定功率输入时飞行器将达到的速度方程并不容易。然而，我们可以使用求解器迭代地找到这个速度。例如，找出功率输入为740 W时获得的速度。

1. 建立一个简单的电子表格，包括速度、系数a和b，以及由此计算出的功率。

   

2. 启动求解器，进入电源，B5.作为目标。设定目标价值740并选择速度，B2，作为可变单元格来更改。选择解决启动解决方案。

   

3. 求解器调整速度的值，直到电源非常接近740，提供我们所需的速度。

   

4. 以这种方式解决模型通常比反相复杂模型更快，更低易于易于易于易受。

了解求解器中可用的不同选项可能非常困难。如果您难以获得合理的解决方案，那么它通常有助于将边界条件应用于可变细胞。这些限制了超出它们不应调整的值。例如，在前面的示例中，速度不应小于零，并且还可以设置上限。这将是一个速度，你很确定车辆不能比。如果您能够为可变变量单元设置界限，则它还使其他高级选项更好，例如MultiStart。这将运行多个不同的解决方案，从不同的变量初始值开始。

选择解决方法也很困难。单纯形LP只适用于线性模型，如果问题不是线性的，它就会以不满足该条件的消息失败。另外两种方法都适用于非线性方法。GRG非线性是最快的，但它的解决方案可能高度依赖于初始启动条件。它的灵活性在于它不需要变量有边界。进化求解器通常是最可靠的，但它要求所有变量都有上界和下界，这可能很难提前计算出来。

Excel求解插件是一个非常强大的工具，可以应用于许多实际问题。要充分利用Excel的强大功能，可以尝试将求解器与Excel宏。