Excel task search for solutions. Solving simple problems using Excel. Solving equations by selecting Excel parameters

One of the most interesting features in Microsoft Excel is the Solution Finder. However, it should be noted that this tool cannot be considered the most popular among users in this application. But in vain. After all, this function, using the initial data, by enumeration, finds the most optimal solution of all available ones. Let's find out how to use the Find Solution function in Microsoft Excel.

You can search for a long time on the tape where the Solution Search is located, but still not find this tool. Simply, to activate this function, you need to enable it in the program settings.

In order to activate the Search for Solutions in Microsoft Excel 2010 and later versions, go to the “File” tab. For the 2007 version, click on the Microsoft Office button in the upper left corner of the window. In the window that opens, go to the “Settings” section.

In the parameters window, click on the “Add-ons” item. After the transition, at the bottom of the window, opposite the “Management” parameter, select the “Excel Add-ins” value and click on the “Go” button.

A window with add-ons opens. We put a tick next to the name of the add-in we need - “Search for a solution.” Click on the “OK” button.

After this, a button to launch the Search for Solutions function will appear on the Excel ribbon in the “Data” tab.

Preparing the table

Now that we have enabled the feature, let's understand how it works. The easiest way to illustrate this is with a concrete example. So, we have a table of wages for company employees. We should calculate the bonus of each employee, which is the product of the salary indicated in a separate column by a certain coefficient. At the same time, the total amount of funds allocated for the bonus is 30,000 rubles. The cell in which this amount is located is called target, since our goal is to select the data exactly for this number.

We have to calculate the coefficient that is used to calculate the bonus amount using the Search for Solutions function. The cell in which it is located is called the desired one.

The target and search cells must be related to each other using a formula. In our specific case, the formula is located in the target cell and has the following form: “=C10*$G$3”, where $G$3 is the absolute address of the desired cell, and “C10” is the total amount of wages from which the bonus is calculated employees of the enterprise.

Launching the Find Solution Tool

After the table is prepared, being in the “Data” tab, click on the “Search for a solution” button, which is located on the ribbon in the “Analysis” tool block.

A settings window opens in which you need to enter data. In the “Optimize objective function” field, you need to enter the address of the target cell where the total bonus amount for all employees will be located. This can be done either by typing the coordinates manually, or by clicking on the button located to the left of the data entry field.

After this, the parameters window will collapse, and you can select the desired table cell. Then, you need to click again on the same button to the left of the form with the entered data to expand the parameters window again.

Under the window with the address of the target cell, you need to set the parameters of the values ​​​​that will be in it. This could be a maximum, a minimum, or a specific value. In our case, this will be the last option. Therefore, we put the switch in the “Values” position, and in the field to the left of it we write the number 30000. As we remember, this number, according to the conditions, is the total amount of the bonus for all employees of the enterprise.

Below is the “Changing Variable Cells” field. Here you need to indicate the address of the desired cell, where, as we remember, the coefficient is located, by multiplying the basic salary by which the bonus amount will be calculated. The address can be entered in the same way as we did for the target cell.

In the “Subject to restrictions” field, you can set certain restrictions for the data, for example, make the values ​​​​integer or non-negative. To do this, click on the “Add” button.

After this, the window for adding restrictions opens. In the “Link to cells” field, enter the address of the cells for which the restriction is being introduced. In our case, this is the desired cell with the coefficient. Next we put down the right sign: “less than or equal to”, “greater than or equal to”, “equal to”, “integer”, “binary”, etc. In our case, we will choose the "greater than or equal" sign to make the coefficient positive number. Accordingly, in the “Limitation” field we indicate the number 0. If we want to set up another limitation, then click on the “Add” button. Otherwise, click on the “OK” button to save the entered restrictions.

As you can see, after this, the constraint appears in the corresponding field of the solution search parameters window. You can also make variables non-negative by checking the box next to the corresponding parameter just below. It is advisable that the parameter set here does not contradict those that you specified in the restrictions, otherwise a conflict may arise.

Additional settings can be specified by clicking on the “Options” button.

Here you can set the accuracy of the constraint and the limits of the solution. When the required data has been entered, click on the “OK” button. But, for our case, there is no need to change these parameters.

After all the settings are set, click on the “Find solution” button.

Next, the Excel program performs the necessary calculations in cells. Simultaneously with the output of the results, a window opens in which you can either save the solution found or restore the original values ​​by moving the switch to the appropriate position. Regardless of the option chosen, by checking the “Return to options dialog box” checkbox, you can go back to the solution search settings. After the checkboxes and switches are selected, click on the “OK” button.

If for some reason the results of the search for solutions do not satisfy you, or when calculating them the program generates an error, then, in this case, we return, using the method described above, to the parameters dialog box. We are reviewing all the entered data, as there may have been an error somewhere. If the error was not found, then go to the “Select solution method” parameter. Here you have the opportunity to choose one of three calculation methods: “Searching for a solution is not linear problems OPG method”, “Search for solutions to linear problems using the simplex method”, and “Evolutionary search for solutions”. By default, the first method is used. We try to solve the problem by choosing any other method. If unsuccessful, try again using the last method. The algorithm of actions is the same as what we described above.

As you can see, the Find a Solution function is a rather interesting tool that, if used correctly, can significantly save the user’s time on various calculations. Unfortunately, not every user knows about its existence, let alone knows how to work with this add-in correctly. In some ways this tool resembles the function “Selection of parameter...”, but at the same time, has significant differences with it.

A significant part of the problems that are solved using spreadsheets assume that the user already has at least some initial data to find the desired result. However, Excel 2010 has the necessary tools with which you can solve this problem in reverse - select the necessary data to get the desired result.

“Search for a solution” is one of these tools, most convenient for “optimization problems”. And if you haven’t had to use it before, now is the time to fix it.

So, we start by installing this add-in (since it will not appear on its own). Fortunately, now this can be done quite simply and quickly - open the “Tools” menu, and already in it “Add-ons”

All that remains is to indicate “Excel Add-ins” in the “Management” column, and then click the “Go” button.

After this simple action, the “Search for a solution” activation button will be displayed in “Data”. As shown in the picture

Let's look at how to properly use the solution search in Excel 2010 with a few simple examples.

Example one .

Let's say that you hold the post of head of a large production department and you need to correctly distribute bonuses to employees. Let's say the total amount of bonuses is 100,000 rubles, and it is necessary that bonuses be proportional to salaries.

That is, now we need to select the correct proportionality coefficient to determine the size of the bonus relative to the salary.

First of all, you need to quickly create (if you don’t already have one) a table where the initial formulas and data will be stored, according to which you can get the desired result. For us, this result is the total amount of the premium. And now pay attention - the target cell C8 must be linked using formulas to the desired cell to be changed at address E2. This is critical. In the example, we connect them using intermediate formulas, which are responsible for calculating the bonus for each employee (C2:C7).

Now you can activate “Search for solutions”. A new window will open in which we need to specify the necessary parameters.

Under " 1 " indicates our target cell. There can only be one.

« 2 "are possible optimization options. There are a total of "Maximum", "Minimum" or "Specific" possible values ​​to choose from. And if you need a specific value, then you need to indicate it in the appropriate column.

« 3 "—there can be several changeable cells (a whole range or separately specified addresses). After all, Excel will work with them, sorting through the options so that the value specified in the target cell is obtained.

« 4 "- If you need to set restrictions, then you should use the “Add” button, but we will look at this a little later.

« 5 » — a button for switching to interactive calculations based on the program we have specified.

But now let’s return to the ability to change our task using the “Add” button. This stage is quite important (no less than the construction of formulas), since it is the limitation that allows us to obtain the correct result at the output. Here everything is done as conveniently as possible, so you can set them not only for the entire range at once, but also for certain cells.

To do this, you can use a number of specific (and familiar to all Excel 2010 users) symbols “=”, “>=”, “<=», а также варианты «цел» (от «целое»), «бин» («бинарное» или же «двоичное»), «раз» («все разные»).

But in our example, there can be only one limitation - a positive coefficient. Of course, you can set it in several ways - either using “Add” (which is called “explicitly specifying the constraint”), or simply checking the “Make unconstrained variables non-negative” function active. This can be done in the “Solution Search” add-on by clicking on the “Options” button.

By the way, after confirming the parameters and launching the program (the “Run” button), you can view the result in the table. Then the program will display a “search results” window.

If the demonstrated result suits you completely, then all that remains is to confirm it again (the “OK” button), which will record the result in your table. If something in the calculations does not suit you, then you need to cancel the result (the “Cancel” button), return to the previous state of our table and correct the mistakes.

The correct solution to the example problem should look like this

It is very important that in order to get the correct result even with the slightest change in the initial data, you need to restart the “Search for Solutions”.

To take a more detailed look at how this program works, let's look at another example.

Let's say you are the owner of a large furniture enterprise and you need to organize production in such a way as to get the maximum possible profit. You produce only bookshelves, with only two models - “A” and “B”, the production of which is limited solely by the availability (or absence) of high-quality boards, as well as machine time (processing on a machine).

Model “A” requires 3 m 3 of boards, and model “B” requires 1 m 3 more (that is, 4). From your suppliers you receive a maximum of 1700 m 3 of boards per week. In this case, model “A” is created in 12 minutes of machine operation, and “B” in 30 minutes. In total, the machine can work no more than 160 hours per week.

The question is: how many products (and what model) should a company produce per week in order to get the maximum possible profit, if shelf “A” gives 60 rubles of profit, and “B” - 120?

Since the procedure is known, we begin to create the table we need with data and formulas. The location of the cells, as before, you can set at your discretion. Or use our

In any convenient way, we launch our “Search for Solutions”, enter data, and make settings.

So let's look at what we have. The target cell F7 contains a formula that will calculate the profit. We set the optimization parameter to maximum. Among the changeable cells we have “F3: G3”. Limitations - all detected values ​​​​must be non-negative integers, the total amount of machine time spent does not exceed 160 (our cell D9), the amount of raw materials does not exceed 1700 (cell D8).

Of course, in this case it was possible not to indicate the cell addresses, but to directly enter the necessary digital values, but if you use the addresses, then changes to the restrictions can be made in the table, which will help calculate the profit of this enterprise in the future, when the source data changes.

We activate the program, and it prepares a solution.

However, this is not the only solution and you may well get a different result. This can happen even if all the data was entered correctly and there were no errors in the formulas either.

Yes. This can happen even if we told the program to search whole number. And if this suddenly happens, then you just need to make additional adjustments to the “Search for Solutions”. Open the “Search for solutions” window and enter “Options”.

Our top parameter is responsible for accuracy. The smaller it is, the higher the accuracy, and in our case this significantly increases the chances of getting an integer. The second parameter (“Ignore integer restrictions”) answers the question of how we were able to get such a response with the fact that we explicitly specified an integer in the request. “Solution Finder” simply ignored this limitation due to the fact that the advanced settings told it so.

So be extremely careful in the future.

The third and perhaps last example. Let's try to minimize the costs of a transport company using the search for solutions in Excel 2010.

So, the construction company gives an order for the transportation of sand, which is taken from 3 suppliers (quarries). It needs to be delivered to 5 different consumers (which are construction sites). The cost of cargo delivery is included in the cost of the facility, so our task is to ensure the delivery of cargo to construction sites with minimal costs.

We have - a supply of sand in the quarry, the need for construction sites for sand, the cost of transportation "supplier-consumer".

It is necessary to find a scheme for the optimal transportation of cargo (to and from), in which the total cost of transportation would be minimal.

The gray cells of our table contain formulas for the sums by columns and rows, and the target cell is a formula for the overall calculation of the cost of delivering goods. We launch our “Search for a solution” and make the necessary settings

After that, we begin to find a solution to this problem.

However, let’s not forget that quite often transport tasks can be complicated by some additional restrictions. Let’s say a complication has arisen on the road and now it is simply technically impossible to deliver cargo from quarry 2 to construction site 3. To take this into account, you just need to add the additional constraint “$D$13=0”. And if you now run the program, the result will be different

Finally, all that remains to be said is about the choice of solution method. And if the problem is really very complex, then in order to get the desired result, most likely you will need to select the necessary solution method.

That's all on this issue.

We searched for solutions in Excel 2010 - for solving complex problems

In this article we will show you how to use formulas to solve systems of linear equations.

Here is an example of a system of linear equations:
3x + 4y = 8
4x + 8y = 1

The solution is to find such values X And at, which satisfy both equations. This system of equations has one solution:
x = 7.5
y = -3.625

The number of variables in the system of equations must be equal to the number of equations. The previous example uses two equations with two variables. Three equations are required to find the values ​​of three variables ( X,at And z). General steps for solving systems of equations are as follows (Fig. 128.1).

1. Express the equations in standard form. If necessary, use basic algebra and rewrite the equation so that all variables appear to the left of the equal sign. The following two equations are identical, but the second is given in standard form:
   3x - 8 = -4y
   3x + 4y = 8.
2. Place the coefficients in a range of cells of size n x n, Where n represents the number of equations. In Fig. 128.1 coefficients are in the range I2:J3.
3. Place the constants (the numbers to the right of the equal sign) in a vertical range of cells. In Fig. 128.1 constants are in the range L2:L3.
4. Use an array of formulas to calculate the inverse coefficient matrix. In Fig. 128.1 the following array formula is entered into the range I6:J7 (remember to press Ctrl+Shift+Enter to enter an array formula): =MOBR(I2:J3) .
5. Use an array formula to multiply the inverse of the coefficient matrix by the constant matrix. In Fig. 128.1, the following array formula is entered into the range J10:JJ11, which contains the solution (x = 7.5 and y = -3.625): =MULTIPLE(I6:J7;L2:L3) . In Fig. Figure 128.2 shows a worksheet set up to solve a system of three equations.

One of the most pressing problems of computer training is the problem of selecting and using pedagogically appropriate training programs.

When studying individual topics and solving certain problems in mathematics lessons in high school, cumbersome calculations, such as, for example, when solving equations by dividing a segment in half or by the method of successive approximations, obscure the essence of the mathematical problem and do not allow one to see the beauty and rationality of the solution method used.

In this article, I presented those tasks, the solution of which using MS EXCEL allows one to obtain a visual solution that is understandable for students, showing its logic and rationality. Along the way, students gain stable skills in working with the program.

Finding the roots of an equation using parameter selection

Example 1.

Let it be known that the hospital staff consists of 6 nurses, 8 nurses, 10 doctors, 3 heads of departments, a chief physician, a pharmacy manager, a housekeeping manager and a hospital manager. The total monthly salary fund is 1,000,000 conventional units. It is necessary to determine what the salaries of hospital employees should be.

The solution to such a problem can be sought using the brute force method. However, at best, this takes a long time. You can suggest another solution. In EXCEL, it is implemented as a search for the value of a formula parameter that satisfies its specific value.

Let's build a model for solving this problem. Let's take the nurse's salary as a basis, and we will calculate the rest of the salaries based on it: so many times or so much more. In mathematical terms, each salary is a linear function of the nurse’s salary: A i *C+B i, where C is the nurse’s salary; A i and B i are coefficients that are determined for each position as follows:

• a nurse receives 1.5 times more than a nurse (A 2 =1.5; B 2 =0);
• doctor - 3 times more than a nurse (A 3 = 3; B 3 = 0);
• head of the department - at 30 y.e. more than a doctor (A 4 =3; B 4 =30);
• the head of the pharmacy is 2 times more than the nurse (A 5 = 2; B 5 = 0);
• farm manager – for 40 y.e. more nurses (A 6 =1.5; B 6 =40);
• head of the hospital - at 20 y.e. more than the chief physician (A 8 =4; B 8 =20);
• chief physician - 4 times more than a nurse (A 7 =4; B 7 =0);

Knowing the number of people in each position, our model can be written as the equation: N 1 *(A 1 *C+B 1)+N 2 *(A 2 *C+B 2)+...+N 8 *(A 8 *C+B 8) = 1000000, where N 1 is the number of nurses, N 2 is the number of nurses, etc.

In this equation, we know A 1 ...A 8, B 1 ...B 8 and N 1 ...N 8, but C is unknown. Analysis of the equation shows that the problem of calculating wages has been reduced to solving a linear equation with respect to C. Let us assume that the salary of a nurse is 150.00 y.e.

Enter the source data into the spreadsheet worksheet as shown below.

Medical salary Workers
Job title			Salary	The number of employees	Total salary
Nurse
Nurse
Head department
Head pharmacy
Chief physician
Head hospital
The total fund is

In Column D, calculate the salary for each position. For example, for cell D4 the calculation formula is =B4*$D$3+C4.

In column F, calculate the wages of all workers in this position. For example, for cell F3 the calculation formula is =D3*E3.

In cell F11, calculate the hospital's total payroll. The spreadsheet worksheet will look like below.

Medical salary Workers
Job title			Salary	The number of employees	Total salary
Nurse
Nurse
Head department
Head pharmacy
Chief physician
Head hospital
The total fund is

To determine the salary of a nurse so that the payroll fund is equal to the specified one, you need to:

• Activate the team Parameter selection in the tab Data / Working with Data /What-If Analysis;
• In the "Set in cell" field of the window that appears, enter a link to cell F11 containing the formula;
• In the "Value" field, enter the desired result 1000000;
• In the "Changing cell value" field, enter a link to the cell D3 being changed and click on the button OK.

Analysis of the problem shows that using Excel you can solve linear equations. Of course, any schoolchild can solve such an equation. However, thanks to this simple example, it became obvious that finding the value of a formula parameter that satisfies its specific value is nothing more than a numerical solution of equations. In other words, using Excel, you can solve any equation with one variable.

Assignment for students:

Create several staffing options using the function Parameter selection and arrange them in the form of a table :

• Change the number of employees in different positions ;
• Find a nurse's salary under new conditions;
• Make a table of several staffing options.

Let's consider another example of finding the roots of an equation using parameter selection. When solving this equation, the method of successive approximations is also used. Students in advanced math classes are familiar with this method. Therefore, to make this example accessible to other students, I offer a brief theory of this method.

Let the equation be given, written in the form x=F(x). Select some initial approximation x 1 and substitute it instead of x in F(x). The resulting value x 2 =F(x 1) of this function is considered the second approximation. Next, find the third approximation using the formula x 3 =F(x 2) and so on. Thus, we obtain a sequence x 1 , x 2 , x 3 ,…, x n ,… numbers with limit α. Then if the function F(x) is continuous, from the equality x n+1 =F(x n) we obtain α=F(α). This means that α is a solution to the equation x=F(x).

Example 2.

Let us be given a polynomial of the third degree:

x 3 -0.01x 2 -0.7044x+0.139104=0.

Since we are looking for the roots of a polynomial of the third degree, there are no more than three real roots. To find the roots, they must first be localized, that is, find the intervals in which they exist. Such root localization intervals can be intervals at the ends of which the function has the opposite sign. In order to find intervals at the ends of which the function changes sign, it is necessary to construct its graph or tabulate it. Let's make a table of function values ​​on the interval [-1;1] with a step of 0.2. To do this you need:

• Enter the value -1 in cell A2, and the value -0.8 in cell A3.
• Select the range A2:A3, position the mouse pointer on the fill marker of this range and drag it to the range A4:A12, the argument is tabulated.
• In cell B2 enter the formula: =A2^3-0.01*A2^2-0.7044*A2+0.139104
• Select cell B2. Place the mouse pointer on the fill marker of this cell and drag it to the range B3:B12. The function is also tabulated.

The value of the argument x	Function value y

The table shows that the polynomial changes sign on the intervals [-1; -0.8], and , and therefore each of these intervals has its own root. Since a third-degree polynomial has no more than three roots, they are all localized.

Before you start finding roots using parameter selection, you need to do some preparatory work:

• Set the accuracy with which the root is found. The root is found by selecting a parameter using the method of successive approximations. For this in Customize Quick Access Toolbar / Other Commands , and on the tab Formulas dialog box Excel Options set in Calculation options relative error And limit number iterations equal to 0.00001 and 1000, respectively.
• Place a cell on the worksheet, for example C2, under the desired root. This cell will play a dual role. Before applying the parameter selection, it contains the initial approximation to the root of the equation, and after application, the found approximate value of the root.
• Using the selection of a parameter, we find the root using the method of successive approximations. Therefore, in cell C2 you need to enter a value that is an approximation to the desired root. In our case, the first segment of root localization is [-1;-0.8]. Therefore, it is reasonable to take the midpoint of this segment -0.9 as the initial approach to the root.
• Assign a cell, for example D2, to the function for which the root is being searched, and instead of an unknown one, this function should indicate a link to the cell allocated for the sought root. Thus, in cell D2 enter the formula: =C2^3-0.01*C2^2-0.7044*C2+0.139104

You need to do the same with the other two roots you are looking for:

• Place cell C8 under the second root, enter the initial approximation 0.3 into it, and enter the following formula in cell D8: =C8^3-0.01*C8^2-0.7044*C8+0.139104
• Place cell C10 under the second root, enter the initial approximation 0.7 into it, and enter the following formula in cell D10: =C10^3-0.01*C10^2-0.7044*C10+0.139104

The results of the actions performed are shown in the table.

x value	The value of y	Initial approximation before applying the method	Function value

Now you can move on to finding the first root of the equation:

Choose a team Parameter selection. A dialog box will appear on the screen Parameter selection.

• In field Set to cell enter a reference to cell D2. This field provides a link to the cell in which the formula that calculates the value of the left side of the equation is entered. To find the root by selecting a parameter, the equation must be presented in such a way that its right side does not contain a variable.
• In field Meaning enter 0. This is the value from the right side of the equation.
• In field Changing the value of a cell enter C2. This field provides a link to the cell reserved for the variable.
• Click the button OK.

A window appears on the screen Result of parameter selection with the results of the team's work Parameter selection. In addition, the tool in question places the found approximate value of the root in cell C2. In this case it is equal to -0.920. Similarly, cells C8 and C10 contain the two remaining roots. They are 0.210 and 0.721.

x value	The value of y	Root of the equation	Function value

Assignment for students:

Find all roots of equations

1. X 3 -2.92X 2 +1.4355X+0.791136=0

2. X 3 -2.56X 2 -1.3251X+4.395006=0

3. X 3 +2.84X 2 -5.6064X-14.766336=0

Finding the roots of an equation by dividing a segment in half

Brief theory of the method. Let the continuous function F(x) have values ​​of different signs at the ends of the segment, that is, F(a)F(b)<0.Тогда уравнение F(x)=0 имеет корень внутри этого отрезка. Отрезок отрезком локализации корня. Пусть c=(a+b)/2 – середина отрезка . Если F(a)F(c)<=0, то корень находится на отрезке , который берем за новый отрезок локализации корня. Если F(a)F(c)>0, then we take 0 as the new root localization segment. Note that the new root localization segment is two times smaller than the original one. We continue the process of dividing the segment to localize the root until its length becomes less than ε, the accuracy of finding the root. In this case, any point on the localization segment differs from the root by no more than ε/2.

Let's find the roots of the equation x 2 –2=0 with an accuracy of 0.001 by dividing the segment in half. For the initial segment of root localization, . To implement this method, enter the formulas or values ​​shown in the table below into the worksheet cells:

Cell	Formula or value
	=(A3^2-2)*(C3^2-2)
	IF(B3–A3<$B$1;""Корень найден и равен "" & текст (C3;""0,000""); "" "")
	IF(D3<=0; A3;C3)
	IF(D3<=0; C3; B3)
	=(A4^2-2)*(C4^2-2)
	IF(B4-A4<$B$1; ""Корень найден и равен "" & текст(C4; ""0,000""); "" "")

Now all that remains is to select the range A4:F4, position the mouse pointer on its fill marker and drag it down until a message appears in column F indicating that the root has been found. In this case, the message will appear in cell F14, and the root value to the nearest 0.001 is 1.415.

You can determine the number of steps in advance and copy the formulas into a range of the required number of rows. The number of steps before finding the root is determined by the formula: +1 (1), where [x] is the integer part of the number x, t is the specified accuracy.

In conclusion, I note that in the considered example we used:

• A string concatenation operation that combines multiple strings into one (indicated by the ampersand character &). When two lines are concatenated, the second line is added directly to the end of the first line.
• Worksheet function from the text functions category TEXT. This function converts a value to text in a given numeric format.

Assignment for students:

Calculate the root of the equation cosx = x on the segment with an accuracy of 0.001. Calculate the number of steps to determine the root using formula (1).

Using MS EXEL significantly expands the range of tasks that can be used in training. This is due to the possibility of transferring labor-intensive operations to a computer, for example, when solving equations using iteration methods and dividing a segment in half.

Literature:

1. Informatics at school / Ed. Makarova N.V. - St. Petersburg: Peter Kom, 1999.
2. Simvolokov L.V. Solving business problems in Microsoft Office - M.: ZAO "Publishing House BINOM", 2001.
3. Shokholovich V.F. Information technologies for education. Computer Science and Education. 1998. – No. 2.
4. Ignekova G.S. Methodological aspects of computer science teacher training. Computer Science and Education. 1998. – No. 3.

Most problems solved using a spreadsheet involve finding the desired result using known source data. But Excel has tools that allow you to solve the inverse problem: select the initial data to obtain the desired result.

One such tool isFinding a solution, which is especially convenient for solving so-called "optimization problems".

If you haven't used it beforeFinding a solution, then you will need to install the appropriate add-on.

You can do it like this:

for versions older than Excel 2007 via the command menu Service --> Add-ons;

since Excel 2007 via dialog boxExcel Options

Since Excel 2007button to startFinding a solution will appear on the tab Data.

In versions prior to Excel 2007, a similar command will appear in the menuService

Let's look at the work order Finding a solution using a simple example.

Example 1. Bonus distribution

Let’s assume that you are the head of a production department and you have to fairly distribute a bonus in the amount of 100,000 rubles. between department employees in proportion to their official salaries. In other words, you need to select a proportionality coefficient to calculate the size of the salary bonus.

First of all, we create a table with the initial data and formulas with which the result should be obtained. In our case, the result is the total amount of the premium. It is very important that the target cell (C8) is linked through formulas to the desired cell to be changed (E2). In the example, they are connected through intermediate formulas that calculate the bonus amount for each employee (C2:C7).

Now let's launch Finding a solution and in the dialog box that opens, set the necessary parameters. The appearance of the dialog boxes varies slightly between versions:

Since Excel 2010

Before Excel 2010

After pressing the buttonFind solution (Run)You can already see the result obtained in the table. At the same time, a dialog box appears on the screenResults of the search for a solution.

Since Excel 2010

Before Excel 2010

If the result that you see in the table suits you, then in the dialog box Solution search results press OK and record the result in the table. If the result does not suit you, then click Cancel and return to the previous state of the table.

The solution to this problem looks like this

Important: for any changes to the source data to obtain a new result Finding a solution will have to run again.

Let's look at another optimization problem (maximizing profit)

Example 2. Furniture production (profit maximization)

The company produces two models A and B of prefabricated bookshelves.

Their production is limited by the availability of raw materials (high-quality boards) and machine processing time.

Each model A product requires 3 m²boards, and for product model B - 4 m². The company can receive up to 1,700 m² of boards per week from its suppliers.

Each model A product requires 12 minutes of machine time, and for product model B - 30 min. 160 hours of machine time can be used per week.

How many products of each model should the company produce per week to achieve maximum profit if each product of model A brings 60 rubles. profit, and each product of model B costs 120 rubles. arrived?

We already know the procedure.

First, we create tables with source data and formulas. The arrangement of cells on the sheet can be absolutely arbitrary, as convenient for the author. For example, as in the picture

Let's launch Finding a solutionand in the dialog box set the necessary parameters

1. Target cell B12 contains the formula for calculating profit
2. Optimization parameter - maximum
3. Modified cells B9:C9
4. Restrictions: the found values ​​must be integer, non-negative; the total amount of computer time should not exceed 160 hours (reference to cell D16); the total amount of raw materials should not exceed 1700 m² (reference cell D15). Here, instead of links to cells D15 and D16, numbers could be specified, but when using links, any changes to restrictions can be made directly in the table
5. Press the button Find solution (Run) and after confirmation we get the result

But even if you created the formulas correctly and set the restrictions, the result may be unexpected. For example, when solving this problem you may see the following result:

And this despite the fact that the restriction was set whole. In such cases, you can try adjusting the settings Finding a solution. To do this in the window Finding a solution press the button Options and we get into the dialog box of the same name

The first of the selected parameters is responsible for the accuracy of calculations. By reducing it, you can achieve a more accurate result, in our case - integer values. The second of the highlighted options (available starting from Excel 2010) answers the question: how could fractional results be obtained when limitingwhole? Turns out Finding a solutionthis restriction was simply ignored in accordance with the checked flag.

Example 3. Transport problem (cost minimization)

To the order of a construction company, sand is transported from three suppliers (quarries) to five consumers (construction sites). The cost of delivery is included in the cost of the project, so the construction company is interested in meeting the sand needs of its construction sites in the cheapest way.

Given: sand reserves in quarries; construction site sand needs; transportation costs between each supplier-consumer pair.

It is necessary to find an optimal transportation scheme to satisfy needs (from where and to where), in which the total transportation costs would be minimal.

An example of the location of cells with source data and restrictions, the desired cells and the target cell is shown in the figure

In the gray cells there are formulas for the sums by rows and columns, and in the target cell there is a formula for calculating the total transportation costs.

We launch the Search for a solution and set the necessary parameters (see figure)

Click Find solution (Run) and get the result shown below

In conclusion, I suggest you try your hand at using Finding a solution and use it to solve an old problem:

A peasant bought 100 head of cattle at the market for 100 rubles. A bull costs 10 rubles, a cow 5 rubles, a calf 50 kopecks. How many bulls, cows and calves did the peasant buy?