ExcelSolver

ExcelSolver is an add-in optimization tool that is used to find the desired outcome based on changing assumptions of the model. It can find the minimum or maximum value in a cell by changing the values in another cell, ultimately leading to the "best" outcome for the problem. It is generally used for simulating different business and engineering models using what-if analysis.

Enabling the Excel Solver Add-in

求解器加载项通常安装在Excel默认情况下。但是，您需要通过遵循以下步骤来使用它：

1. Open the Start menu and click on the Excel icon, followed by double-clicking on the Blank workbook option to create a blank workbook.
2. 单击文件，然后单击选项以将自己导航到“附加”选项卡。您会发现将求解器加载程序放置在无活动应用程序附加列中。首先，单击“求解器加载项”，然后进行GO。
3. This will open a new dialog box. Just check the box for Solver Add-Ins and press on OK.
4. 现在转到“数据”选项卡，您将在分析部分中找到求解器。

求解器参数的组件

该工具需要设置三个不同的参数来找到问题的解决方案：

• 客观细胞:它代表我们probl的目标或目的em. It contains a formula, and it is used to calculate a maximum, minimum, or target value.
• Variable cell：它们包含用于获得目标单元格值的可调节数据。
• Constraints: These are the limitations set up to restrict the solutions from deviating from objective value. Simply put, constraints are the conditions that must be met to obtain objective cell value.

这Solver Parameters window looks as below:

不同类型的求解器方法

这re exist three different types of Solver methods:

• GRG非线性: GRG is an acronym for "Generalized Reduced Gradient." This method examines the slope of the objective functions as the input values change to find the optimum solution.
  它很快，但缺点是它高度依赖于初始条件，这意味着最佳值将接近布置的初始条件。然而，它是解决非线性问题的最常用方法。
• Simplex LP: LP stands for "linear programming." The Simplex LP method is used to solve linear problems where the value of one entity is proportional to another entity.
• Evolutionary：进化algorithmis slower than the GRG Nonlinear algorithm when used for nonlinear relationships. However, it is more robust and finds a globally optimum solution. Like the theory of natural selection, it weeds out the input values that are not close to the target values to finally reach a stage where the solution represents the closest relation to the target value.

线性解决方案

线性方程是可以使用方法来找到线性解的方法。线性解决方案的常见示例是年龄，力和压力，工资和小时费率问题。

这parent and child age difference for different instances of time and their calculation of present age is an example of a linear problem.

您可以使用Simplex LP求解器方法在Excel中求解此类线性方程。结果，与GRG非线性和进化方法相比，其应用受到限制，因为它不能用于非线性方程。但是，通过单纯形LP方法获得的解决方案始终是全局最佳的解决方案，使其成为强大的方法。

非线性解决方案

Solver works best in situations in which nonlinear relationships exist. But, what exactly are nonlinear relationships? A nonlinear relationship exists between two entities when the change in the value of one entity does not correlate with the change in the value of another entity. On the contrary, linear relationships exist when two entities are proportional to each other.

Linear equations can't solve nonlinear problems. The most common example is the traveling salesman problem (TSP).

TSP是一个非线性优化问题。它回答了如何以最少的旅行访问n个城市，每个城市都有相关成本。由于这些问题易于描述，但实际上解决了挑战，因此他们引起了人们的注意。解决此类问题的解决方案并不容易，如果在计算这些非线性优化问题的最佳途径时添加了更多目的地，则变得更加复杂。

One way to solve this problem is by using Solver methods GRG Nonlinear or Evolutionary methods.

To find an optimal solution for the TSP, one must first create a model with two columns: one for all possible trips and another for total costs. Then, Solver will then assign values for different trip distances and expenses until it finds an answer that satisfies both columns in the model at once.

Excel求解器的案例研究-LP单纯形方法

Excelsolver can be used to work on different financial planning tasks, for example, monthly mortgage payments, savings for retirement and business problems. The calculations behind these complex tasks are elaborate and time-consuming. The tool helps to make such calculations easier based on the availability of inputs from the user.

让我们考虑一下特斯拉及其两种汽车模型的示例-S模型和模型3。每单位需要20个小时的劳动力才能制作模型S，而每单位30小时30小时。考虑到有限的资源，确定要生产多少辆汽车。manbetx3.0手机客户端假设只有10,000小时的劳动力，300个车架和2200个车轮可用。

Tesla can earn a profit of $2,000 on its Model S while generating a profit of $2,500 on the Model 3. Both the cars require one vehicle frame and four tires on top of the labor hours for making the cars. Considering the above assumptions, Tesla must decide how many vehicles they will need to produce in the fiscal year to maximize their profit.

第一步是建立一个数学翅tation of the business problem and portray it on the spreadsheet.

We must determine the values in cells E3 and F3, which are the variables in finding the maximum profit using the available resources. Cell G4 contains the formula=（e4*e3） +（f4*f3）这将计算特斯拉可以赚取的最大利润。细胞E8：E10和F8：F10是特斯拉模型的每单位生产要求。为了计算将用于制造汽车的个人资源的总体资源，我们在g8：g10：manbetx3.0手机客户端g8：g10中使用了以下公式：

• g8 =（e8*e3） +（f8*f3）
• G9 = (E9*E3) + (F9*F3)
• G10 = (E10*E3) + (F10*F3)

这se formulas will be used to compare with the available resources to avoid Tesla from overusing its resources. Speaking in excel terms, cells G8:G10 will represent the left-hand side of the constraint functions, while cells H8:H10 will form the right-hand side of the constraint function in the Solver tool.

由于实现了电子表格模型，因此让我们移至求解器，以找到解决我们业务问题的最佳解决方案。要运行它，只需单击菜单选项卡中的数据，您将在“分析”部分中找到它。您也可以使用Excel快捷键alt + a + y3。

我们需要在“参数”窗口中定义不同的组件。要概述的第一个关键组件是目标单元，其值可以是最大或最小值或特定值。第二个组件是可变单元格。基于可变单元格值的变化，客观细胞中的值往复。第三个组件是称为约束的模型的局限性。这意味着我们想要我们的Excel模型to stick to these limitations, making them a barrier for our calculations.

例如，为了制造这两种车型，我们有10,000小时的劳动力。因此，可以设置其中一个限制，因为劳动时小时不应超过10,000。同样，我们设置了更多的约束，例如车辆框架<= 450，轮胎<= 2,200，模型s的数量将根据要求在财政年度等于模型3的数量。

Once all the parameters are set up, select the solving method as Simplex LP. Then, make sure you tick the box that says, 'Make Unconstrained Variables Non-Negative' and click on Solve.

A dialog box should pop up that says, 'Solver has found a solution.' This means that Excel has given us the output adhering to all our constraints. From this, we can determine that Tesla can produce 200 cars each of Model S and Model 3 while consuming 10,000 hours of labor work completely. The other resources, i.e., Vehicle frames and tires, are in excess and are to be stored in inventory.

Case Study for Excel Solver – GRG Nonlinear Method

Let's formulate the model upon which we need to run our Solver in the spreadsheet. In this hypothetical example, we will try to find the solution for a simple nonlinear problem

假设我们（华尔街绿洲）打算在市场上万博app足彩下载引入一门名为“信用分析师”的新课程。为此，我们需要投资5,000美元（不，不是我们的实际成本，而是此示例的名义数字），这代表了投资专家团队和其他杂项成本的费用。我们需要估算课程的销售价格，以帮助我们在一年内收回投资。

Our spreadsheet model for the problem is as below:

Now let's run the Solver tool to find the solution for our problem. Use the keyboard shortcutsalt + a + y3打开求解器参数窗口。我们的客观细胞将是包含公式的一个，即这里是单元B7。由于我们知道支付费用的月数，因此我们将选择“无线电”按钮的“值”，并将该值设置为12。

Our variables are estimated clients per month and buying costs that we will set up for our course, based on the fact that the number of clients each month won't be constant. We have also set up our constraints which we do not intend to cross, i.e., in this case, the course price of $5,000 and unique clients each month (base case scenario), which are <= 15.

Once all the parameters are set up, select the solving method as GRG Nonlinear method. You will get the solution for the problem as below:

如您所见，该工具满足了所有条件，并为我们的“信用分析师”课程的售价约为27美元。（这是大多数迷你轮廓的实际价格！here!) All the assumptions made are modeled upon base case方案and can be tweaked to find the best- and worst-case scenarios.

笔记c:永远记住,避免让你的变量ells empty. In this case, we had assigned the value of $1 to cell B5 to avoid getting an error while using the Solver.

Excel求解器的案例研究 - 进化法

Let's assume that a movie theater showcases six different movies. The customers can watch a single movie (check the image below for individual prices) or buy the entire bundle at $48.50 and $60.70 for teenagers and adults, respectively. So, of course, the movie theater owner wants to find the optimum price of the bundle and individual movies to maximize their profit.

We have created the spreadsheet model based on the data below:

A bit complicated? Maybe, but we will make it easier for you to understand. Our objective cell is B26, while our variables are in range B3:H3. Range B7:H7 shows us how much it will cost teenagers to watch individual movies and buy the entire bundle ($48.5). Range B8:H8 gives the pricing guide for adults to purchase a ticket at the movie theater and the purchase price of the whole bundle, which is $60.70.

Next, we determine how much surplus is charged for the individual movies and the entire bundle. Here we have subtracted the ticket prices by our variable range B3:H3.

接下来，我们分别从B11：H11和B12：H12找到最大值。这描绘了我们每部电影和捆绑包中的最高盈余价格。在我们的电子表格中看到罚款表吗？这是一项附加的约束检查，如果电影的个人价格大于整个捆绑包，它将降低我们的最大收入。

这final step is to find the revenue generated from both age categories to achieve maximum profit with optimum prices. We have set up our constraints as below:

这constraints set up are such that the maximum price of the individual movies or the bundle should not exceed the complete pricing guide for teenagers & adults while buying the movie ticket.

Now select the Evolutionary method of solving and click on Solve. The Solver gives the following result:

Thus, the movie theatre owner can generate maximum revenue of $97.00, while the optimum prices for the individual movie tickets are in cell B3:H3.

Multi-objective and Mixed-integer Nonlinear Problems

A typical Solver problem has a single objective: to minimize or maximize a target cell. However, many modeling applications require multi-objective and mixed-integer nonlinear problems.

多目标优化问题具有两个或多个目标，而混合成员的非线性编程问题结合了两种类型的约束：线性和非线性。

To determine how to solve a multi-objective or mixed-integer nonlinear problem using Excel 2010, you can use the Gurobi Optimizer add-in that will identify the optimal solution based on your specific model.

接下来，我们将探讨如何在Excel 2019中实施求解多目标和混合企业非线性问题的求解器工具。

Solution for Multi-Objective Nonlinear Problem

Let's assume that a company needs to install a coffee vending machine on its first floor consisting of five cubicles. The coordinates for the cubicles are as below:

这first objective is to minimize the distance of vending machines from each cubicle, while the second objective is to install the vending machines as far as possible from the washroom on the same floor.

洗手间的坐于X= 6,y= 14），而我们的可变单元格为L7：M7。这些变量将确定必须在何处设置咖啡自动售货机，以便它靠近每个隔间。

Next, we find the distance between the cubicles and the variables in L7:M7. This will be calculated in the range H7:H11 by using the formula= sqrt（（$ l $ 7- f7）^2 +（$ m $ 7 -g7）^2）in cell H7 and dragged up to cell H11.

由于计算自动售货机和隔间之间的距离，因此我们需要使用公式，所有隔间的总长度与自动售货机=SUM(H7:H11)in cell N7. This is our first objective function, while cell N11 gives our second objective function that consists of the formula=SQRT((L7 - L11)^2 + (M7 - M11)^2)。这是咖啡自动售货机和洗手间之间的距离。

现在，我们通过使用公式来确定电动机与单元O14中每个隔间的最小距离= N7/N11。

接下来，我们使用公式计算单元格O15中的自动售货机和洗手间之间的最大距离= N11/N7。First, we use the Solver with the O14 cell as the objective value. Our variable cells will be L7:M7. Our constraints will be H7:H11 <= 20 and H7:H11 >= 4 as given in the data.

When we run Solver, the distance obtained for goal 1 is 2.80m, for which the coordinates areX= 5,y= 1. Similarly, when O15 cell is the objective value, with similar variables and constraints, the distance obtained is 0.05m, for which the coordinates areX= 7.8和y= 13.8.

这散点图for the two goals is given below:

这样，可以解决多目标非线性问题。此外，可以通过对情况添加更多约束来获得“好”答案来解决最佳解决方案。

什么时候使用Excel求解器？

ExcelSolver is a tool that can find the desired outcome by changing assumptions in a model. The program has many uses and is often used for business, engineering, and science applications.

例如，它可以为公司找到最佳的生产水平和产品定价决策。它还可以通过计算各种贷款付款和下款替代方案来帮助客户确定最佳付款计划。

Solving problems with Solver is particularly relevant for businesses or organizations that need to maximize profit or minimize cost. This type of software relies on mathematical equations to find the solution. However, it may not be appropriate for all kinds of problems because it assumes no constraints other than the ones given in the problem description.