How to find the optimal solution in excel. Quadratic equation in Excel. Solving logical problems in Excel

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 required 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 a 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 can choose one of three calculation methods: “Searching for solutions to nonlinear problems using the OPG method,” “Searching 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.

Excel has a wide range of tools for solving different types of equations using different methods.

Let's look at some solutions using examples.

Solving equations by selecting Excel parameters

The Parameter Selection tool is used in a situation where the result is known, but the arguments are unknown. Excel adjusts the values ​​until the calculation gives the desired total.

Path to the command: “Data” - “Working with data” - “What-if analysis” - “Parameter selection”.

Let's look at the example of solving the quadratic equation x 2 + 3x + 2 = 0. The procedure for finding the root using Excel:

The program uses a cyclic process to select a parameter. To change the number of iterations and error, you need to go to the Excel options. On the “Formulas” tab, set the maximum number of iterations and relative error. Check the “enable iterative calculations” checkbox.

How to solve a system of equations using the matrix method in Excel

The system of equations is given:

The roots of the equations are obtained.

Solving a system of equations using the Cramer method in Excel

Let's take the system of equations from the previous example:

To solve them using Cramer's method, we calculate the determinants of the matrices obtained by replacing one column in matrix A with the column-matrix B.

To calculate the determinants, we use the MOPRED function. The argument is a range with the corresponding matrix.

Let's also calculate the determinant of matrix A (array - range of matrix A).

The determinant of the system is greater than 0 – the solution can be found using Cramer’s formula (D x / |A|).

To calculate X 1: =U2/$U$1, where U2 – D1. To calculate X 2: =U3/$U$1. Etc. Let's get the roots of the equations:

Solving systems of equations using the Gaussian method in Excel

For example, let’s take the simplest system of equations:

3a + 2b – 5c = -1
2a – b – 3c = 13
a + 2b – c = 9

We write the coefficients in matrix A. Free terms - in matrix B.

For clarity, we highlight the free terms by filling. If the first cell of matrix A contains 0, you need to swap the rows so that a value other than 0 appears here.

Examples of solving equations using the iteration method in Excel

The calculations in the workbook should be set up as follows:

This is done on the “Formulas” tab in “Excel Options”. Let's find the root of the equation x – x 3 + 1 = 0 (a = 1, b = 2) by iteration using cyclic references. Formula:

Х n+1 = X n – F (X n) / M, n = 0, 1, 2, … .

M – maximum value of the modulo derivative. To find M, let's perform the following calculations:

f’ (1) = -2 * f’ (2) = -11.

The resulting value is less than 0. Therefore, the function will have the opposite sign: f (x) = -x + x 3 – 1. M = 11.

In cell A3 we enter the value: a = 1. Accuracy – three decimal places. To calculate the current value of x in the adjacent cell (B3), enter the formula: =IF(B3=0;A3;B3-(-B3+POWER(B3;3)-1/11)).

In cell C3, let's control the value of f (x): using the formula =B3-POWER(B3,3)+1.

The root of the equation is 1.179. Let's enter the value 2 into cell A3. We get the same result:

There is only one root on a given interval.

Excel users have long and successfully used the program to solve various types of problems in different areas.

Excel is the most popular program in every office around the world. Its capabilities allow you to quickly find effective solutions in a wide variety of areas of activity. The program is capable of solving various types of problems: financial, economic, mathematical, logical, optimization and many others. For clarity, we provide each of the above-described solutions to problems in Excel and examples of its implementation.

Solving optimization problems in Excel

Optimization models are used in the economic and technical spheres. Their goal is to select a balanced solution that is optimal under specific conditions (number of sales to generate a certain revenue, the best menu, number of flights, etc.).

In Excel, the following commands are used to solve optimization problems:

To solve the simplest problems, use the “Parameter Selection” command. The most difficult ones are “Scenario Manager”. Let's look at an example of solving an optimization problem using the “Solution Search” add-on.

Condition. The company produces several types of yogurt. Conventionally – “1”, “2” and “3”. Having sold 100 jars of yogurt “1”, the company receives 200 rubles. “2” - 250 rubles. “3” - 300 rubles. Sales have been established, but the amount of available raw materials is limited. You need to find what kind of yogurt and in what volume you need to make in order to get maximum sales income.

We enter the known data (including raw material consumption rates) into the table:

Based on this data, we will create a worksheet:

1. We do not yet know the number of products. These are the variables.
2. The following formulas are entered in the “Profit” column: =200*B11, =250*B12, =300*B13.
3. The consumption of raw materials is limited (these are limitations). The following formulas are entered into the cells: =16*B11+13*B12+10*B13 (“milk”); =3*B11+3*B12+3*B13 (“leaven”); =0*B11+5*B12+3*B13 (“shock absorber”) and =0*B11+8*B12+6*B13 (“sugar”). That is, we multiplied the consumption rate by the quantity.
4. The goal is to find the highest possible profit. This is cell C14.

Activate the “Search for a solution” command and enter the parameters.

After clicking the “Run” button, the program displays its solution.

The best option is to concentrate on producing yogurt “3” and “1”. Yogurt “2” should not be produced.

Solving financial problems in Excel

Most often, financial functions are used for this purpose. Let's look at an example.

Let's format the source data in the form of a table:

Since the interest rate does not change throughout the entire period, we use the PS function (RATE, NPER, PMT, BS, TYPE).

Filling in the arguments:

1. The rate is 20%/4, because interest is calculated quarterly.
2. Nper – 4*4 (total deposit term * number of accrual periods per year).
3. PMT – 0. We don’t write anything, because... the deposit will not be replenished.
4. Type – 0.
5. BS is the amount we want to receive at the end of the deposit period.

The investor needs to invest this money, so the result is negative.

To check the correctness of the solution, we use the formula: PS = BS / (1 + bet) nper. Let's substitute the values: PS = 400,000 / (1 + 0.05) 16 = 183245.

Econometrics solution in Excel

To establish quantitative and qualitative relationships, mathematical and statistical methods and models are used.

There are 2 ranges of values:

The X values ​​will play the role of a factor characteristic, Y - the resultant one. The task is to find the correlation coefficient.

To solve this problem, the CORREL function (array 1; array 2) is provided.

Solving logical problems in Excel

The table processor has built-in logical functions. Any of them must contain at least one comparison operator that will determine the relationship between the elements (=, >, =,

The students took the test. Each of them received a mark. If more than 4 points, the test is passed. Less – not passed.

1. Place the cursor in cell C1. Click the functions icon. Select "IF".
2. Fill in the arguments. Logical expression – B1>=4. This is the condition under which the Boolean value is TRUE.
3. If TRUE – “Passed the test.” FALSE – “I didn’t pass the test.”

Solving math problems in Excel

Using the program's tools, you can solve both the simplest mathematical problems and more complex ones (operations with functions, matrices, linear equations, etc.).

Conditions of the learning task. Find the inverse of matrix B for matrix A.

1. We make a table with the values ​​of matrix A.
2. Select an area on the same sheet for the inverse matrix.
3. Click the “Insert Function” button. Category – “Mathematical”. Type – “MOBR”.
4. In the “Array” argument field we enter the range of matrix A.
5. Press Shift+Ctrl+Enter at the same time - this is a prerequisite for entering arrays.

Excel's possibilities are not limitless. But the program can handle many tasks. Moreover, the features that can be expanded using macros and user settings are not described here.

The purpose of the lesson: Continue to develop spreadsheet skills.

• educational:
develop the skills of creating, editing, formatting and performing simple calculations in spreadsheets.
• developing:
expand students’ understanding of possible areas of application of spreadsheets; develop analytical thinking, speech and attention skills.
• educational:
to form and cultivate cognitive interest; instill skills of independence at work.

Lesson plan.

1. Organizing time.
2. Updating students' knowledge.
3. Checking homework.
4. Problem solving.
5. Independent problem solving.
6. Summarizing. Ratings.
7. Homework.

During the classes

1. Organizational moment.

Inform the topic of the lesson, formulate the goals and objectives of the lesson.

Today we will again be visiting the little giant Vasya in Fairytale Land. As always, he needs your help, guys.

Can you help Vasya? Let's check it now!

2. Updating students' knowledge.

1) Answer questions orally.

• What is a spreadsheet?
• What basic elements of a spreadsheet do you know?
• How to set the name of a cell (row, column) in a spreadsheet?
• What can be the contents of a cell?
• The number 1 is in column..., in row..., in cell with address...
• The number 4 is in the cell with the address...
• What are the rules for writing formulas in cells?
• What is the value calculated by the formula in cell C1?
• What is the value calculated by the formula in cell D2?

2) What result will be obtained in cells with formulas?

Answer: 25*4/2=50

• What does =SUM(B1:D3) mean?
• How many elements does block B1:D3 contain? Answer: 9.
• Contents of cell D3? Answer: 5+2+1+6+8+3+8+3+4= 40

3) Checking homework

Swimming competition results

One student talks about how he completed his homework (via the projector).

• The average time for each athlete is found as the arithmetic mean of his three swims.
• The minimum result of 3 swims is recorded in the “Best time” cell.
• The minimum time from the column is recorded in the “Best competition result” cell.
• The “Deviation” column records the difference between the athlete’s best time and the best result of the competition.
• The maximum value of the column is recorded in the "Maximum deviation" cell.

4) Solving simple problems.

The little giant Vasya decided to repair the fence around his garden and dig it up for planting vegetables (another spring has arrived), and mark out rectangular beds. To work, he needed to find the length of the fence and the area of ​​the site. But he never went to school. Let's help Vasya.

No. 1. Calculate the perimeter and area of ​​a rectangle with sides:

a) 3 and 5; b) 6 and 8; c) 10 and 7.

We discuss this task together with the children:

• How to design a table?
• What formulas should I use?
• How to use already written formulas for the next rectangle?

Design of the table - on the board and in notebooks.

At the same time, another student solves the next problem independently and presents his solution to the students (via a projector).

Having discussed the solution to problem No. 2, we move on to solving the next one.

One student shows how to work with formulas, another shows how to use the sum function, number format (general, monetary), etc. (The table is already ready, students will have to enter formulas, use summation and get the answer).

No. 3. Calculate, using ET, is 150 rubles enough for Vasya to buy all the products that his mother ordered for him, and will it be enough for chips for 10 rubles? Mom allowed me to put the change in the piggy bank. How many rubles will go into the piggy bank?

Suggested solution:

5) Independent problem solving.

The little giant Vasya often visited the residents of the Flower City.

Getting ready to go to the beach, the cheerful little people decided to stock up on soft drinks. Dunno took with him 2 liters of kvass, 1 liter of soda and 1 liter of raspberry syrup, Donut - 3 liters of soda and 2 liters of raspberry syrup, Toropyzhka - 2 liters of soda, Doctor Pilyulkin - 1 liter of kvass and 1 liter of castor oil.

• How many liters of each type of drink did all the little men take together?
• How many liters of drinks did each of the men take with them?
• How many liters of drinks did all the little men take together?

Design the table as you wish and save it in your personal folder.

The result of the work.

7) Summing up. Ratings.

8) Homework.

Think and solve this problem if the following quantities are also known.

How will the table change? What formulas will appear?

It is known that 1 liter of kvass in the Flower City costs 1 coin, 1 liter of soda costs 3 coins, 1 liter of raspberry syrup costs 6 coins, 1 liter of castor oil costs 2 coins.

• How many coins did each person spend on buying drinks?
• How many coins were spent on purchasing each type of drink?
• How much money did all the people spend together?

Literature

1. Computer science. Problem book-workshop in 2 volumes /Ed. I.G.Semakina, E.K.Henner - M.: Laboratory of Basic Knowledge, 2010.
2. Efimova O. Computer technology course with the basics of computer science. – M.: LLC “AST Publishing House”; ABF, 2005.

The task of solving the equation is faced not only by students and schoolchildren. Excel has a variety of ways to accomplish this task. The solution method by selecting a parameter will be discussed in this article.
Finding the roots of a nonlinear equation using the tool "Selection of parameter" comes down to two steps:

• determining the approximate boundaries of segments and the number of roots using a graphical method;
• selection of a root value on each segment that satisfies the given calculation accuracy.