Graphical differentiation in excel. Graphical and numerical differentiation

Example 3: Select with the help of an autofilter students enrolled in group No. 5433 with a surname beginning with the letter C.

Sequencing

1. Copy the database (fig. 30) to Sheet 3.

2. Surname.

3. Select an item from the listText Filters -\u003e Custom Filter... In the window that appears Custom autofilterselect the selection criterion starts with, enter the required letter in the field opposite (check that the layout is in Russian). Click OK.

4. Open dropdown in columnGroup no.

5. Select the desired number.

Filtering records in a database using an advanced filter

Advanced filterallows you to search for strings using more complex criteria than custom autofilters. The advanced filter uses a range of criteria to filter data.

When using the advanced filter, the names of the columns by which the conditions are set are copied below the original table. Selection criteria are entered under the column names. After applying the filter, only those rows can be displayed on the screen that meet the specified criteria, and the filtered data can be copied to another sheet or to another area on the same worksheet.

Example 4: Select all students from group No. 5433 who have a GPA greater than or equal to 4.5.

Sequencing

1. Copy the database (fig. 30) to Sheet 4.

2. Copy column namesGroup number and average score

to the area below the original table. Enter the required selection criteria under the column names (Fig. 32)

Figure: 32. Excel window with advanced filter

2. On the Data tab on the Sorting toolbar

and select the Advanced filter. A dialog box appears (Figure 33), in which the data ranges are specified.

Figure: 33. Advanced filter window

In the input field Original Rangean interval containing the original database is specified. In our case, the range of cells from A1 to I9 is \u200b\u200bhighlighted.

In the input field Range of conditionsthe interval of cells on the worksheet is highlighted, which contains the required criteria (C12: D13).

In the input field Place the result in the range indicates the interval at which to copy lines that satisfy the

teriyam. In our case, a cell is indicated below the criteria area, for example, A16. This field is only available when the radio button is selected Copy the result to another location.

Checkbox Unique records onlyis intended to display only non-repeating lines.

The resulting table that meets the filtering criteria is shown in Fig. 34.

Figure: 34. Excel window with filtering results

1. Create your own database, the number of records in which must be at least 15, and the number of columns - at least 6. For example, a databaseList of clients (fig. 35).

2. Apply three autofilters to the database (on separate sheets). The number of criteria must be at least two.

3. Apply three advanced filters to database records, each of which must contain at least two criteria. Place all advanced filters on one sheet below the original table.

Figure: 35. Excel window with database Client list

LABORATORY WORK No. 5

Numerical Differentiation and Basic Function Analysis

Purpose of work: To investigate the function for an extremum, to learn how to determine the critical point.

It is known from the mathematics course that the derivative formula in general looks like this:

f "(x) \u003d lim
Δx 0

where Δx is the argument increment; x is a number tending to zero. The derivative can be used to determine the critical points of the function - minima, maxima, or inflections. If the value of the derivative of the function at any value of x is equal to zero, then at this value of x the function has a critical point.

Example 1: The function f x \u003d x 2 + 2x 3 is given on the interval x 5; 5. Explore the behavior of the function f (x).

Sequencing

1. Let Δx \u003d 0.00001. Enter into cell A1: šDx \u003d Ÿ (Fig. 36). Select the letter D, right-click on the highlighted letter, select Format Cells. On the Font tab, select the Symbol font. The letter D becomes the Greek letter ѓў. Alignment in the cell can be done right. Enter 0.00001 into cell B1.

2. In cells A2 through F2, fill in the “header” of the table, as shown in fig. 36.

3. Column A, starting with the third row, will contain the x values. Enter values \u200b\u200bfrom -5 to 5 in cells A3 through A13.

4. In cell B3, write the formula \u003d A3 ^ 2 + 2 * A3-3 and stretch it to the final value x (up to the 13th row).

5. To determine the derivative of a function and calculate its values \u200b\u200bat a given interval, it is necessary to make an intermediate

accurate calculations. In cell C3, enter the formula for the sum of the argument x and its increment Δx. The formula is: \u003d A3 + $ B $ 1. Stretch its value to the final value of the x argument.

Figure: 36. Excel window with investigation of function behavior

6. In cell D3, write the formula \u003d C3 ^ 2 + 2 * C3-3, which is used to calculate the value of the function f from the argument x Δx. Stretch the resulting value to the final value of the argument.

7. In cell E3, write the derivative formula (1), taking into account that the f x values \u200b\u200bare in B3, and the f x + Δx values \u200b\u200bare in D3.

The formula will look like: \u003d (D3-B3) / $ B $ 1.

8. Determine the behavior of the function at a given interval (increases, decreases, or there is a critical point). To do this, you need to write a formula to determine the behavior of the function in cell F3. The formula contains three conditions:

		f "(x)< 0	- the function decreases;
		f "(x)\u003e 0	- the function increases;
		f "(x) \u003d 0	- there is a critical point *.

9. Build graphs based on the values \u200b\u200bof f x and f "(x). The graph (Fig. 37) shows that if the value of the derivative of the function is equal to zero, then the function has a critical point at this place.

* Due to a too large calculation error, the f "(x) value may not equal 0. But it is still necessary to describe this situation.

Figure: 37. Diagram of the study of the behavior of the function

Self-study assignments

The function f (x) is defined on the interval x. Explore the behavior of the function f (x). Build graphs.

			2x 2						X [4; 4]
								X [5; 5]
			2x + 2
	f (x) \u003d x3		3x 2		2, x [2; 4]
	f (x) \u003d x					X [2; 3]
				x 2 + 7

LABORATORY WORK No. 6

Plotting a function tangent to a graph

Purpose of work: To master the calculation of the values \u200b\u200bof the equation of the tangent to the graph of the function at the point x 0.

The equation of the tangent to the graph of the function y \u003d f (x) at the point

Example 1: The function y \u003d x 2 + 2x 3 is specified on the interval x [5; five ] . Construct a tangent line to the graph of this function at the point x 0 \u003d 1.

Sequencing:

1. To differentiate numerically this function (see Laboratory work No. 5). The initial data table is shown in Fig. 38.

Figure: 38. Source data table

2. Determine the location of x, x 0, f (x 0) and f "(x 0) in the table. Obviously, the values \u200b\u200bfrom

column A, starting with the third row (Figure 38). If x 0 \u003d 1, then cell A9 will act as x 0. Accordingly, the value of the function f at point x 0 is in cell B9, and the value f "(x 0)

- in cell E9.

3. Column F calculates the equation of the tangent to the graph of the function f (x). When calculating equation (1), it is necessary that the values \u200b\u200bx 0, f (x 0) and f "(x 0) did not change. Therefore, in writing

when addressing cells A9, B9, and E9, you must use absolute references to those cells. Cells are fixed using the š $ Ÿ sign. The cells will look like: $ A $ 9, $ B $ 9 and $ E $ 9.

Figure: 39. Graph of the function f (x) and the tangent to the graph at the point x \u003d 1

Self-study assignments

The function f (x) is defined on the interval x. Calculate the tangent equation. Construct a tangent to the graph of the function at a given point.

			2x 2						X [4; 4], x0 \u003d 1
								X [5; 5], x0
			2x + 2
	f (x) \u003d x3		3x 2		2, x [2; 4], x0 \u003d 0
	f (x) \u003d x					X [2; 3], x0
				x 2 + 7

1. Vedeneeva, EA Functions and formulas Excel 2007. User's library / EA Vedeneeva. - SPb .: Peter, 2008 .-- 384 p.

2. Sviridova, M. Yu. Excel spreadsheets / M. Yu. Sviridova. - M.: Academia, 2008 .-- 144 p.

3. Serogodsky, V.V. Graphs, calculations and data analysis

at Excel 2007 / V. V. Serogodsky, R. G. Prokdi, D. A. Kozlov, A. Yu. Druzhinin. - M .: Science and technology, 2009 .-- 336 p.

Numerical differentiation

Section No. 5

The problem of approximate calculation of the derivative can arise in cases where the analytical expression for the function under study is unknown. The function can be set in a table, or only the graph of the function is known, obtained, for example, as a result of the readings of the sensors of the technological process parameters.

Sometimes, when solving some problems on a computer, due to the cumbersome calculations, it may be more convenient to calculate the derivatives by a numerical method than an analytical one. In this case, of course, it is necessary to justify the applied numerical method, that is, to make sure that the error of the numerical method is within acceptable limits.

One of the effective methods for solving differential equations is the difference method, when instead of the desired function, a table of its values \u200b\u200bat certain points is considered, while the derivatives are approximately replaced by difference formulas.

Let the graph of the function be known y \u003d f(x) on the segment [ and,b]. You can build a graph of the derivative of a function, remembering its geometric meaning. Let us use the fact that the derivative of the function at the point xis equal to the tangent of the angle of inclination to the abscissa axis of the tangent to its graph at this point.

If x \u003d x 0, we find at 0 \u003d f(x 0) using the graph and then draw a tangent ABto the graph of the function at the point ( x 0 , y 0) (fig.5.1). Let's draw a line parallel to the tangent AB,through the point (-1, 0) and find the point at 1 its intersection with the ordinate axis. Then the value at 1 is equal to the tangent of the angle of inclination of the tangent to the abscissa axis, i.e., the derivative of the function f(x) at the point x 0:

at 1 = = tg α \u003d f ¢ ( x 0), and point M 0 (x 0 , at 1) belongs to the graph of the derivative.

To plot the derivative, you need to split the segment [ and, b] into several parts by dots x i, then for each point graphically plot the value of the derivative and connect the resulting points with a smooth curve using patterns.

In fig. 5.2 shows the construction of five points M 1, M 2 ,... , M 5 and the graph of the derivative.

Derivative plotting algorithm:

1. We build a tangent to the graph of the function at= f(x) at the point ( x 1 , f(x 1)); from the point (-1, 0) parallel to the tangent at the point ( x 1 , f(x 1)) draw a straight line to the intersection with the ordinate; this intersection point gives the value of the derivative f ¢ ( x 1). Build a point M 1 (x 1 , f ¢ ( x 1)).

2. Similarly, we construct the remaining points M 2 , M 3 , M 4 and M 5 .

3. Connect the dots M 1 , M 2 , M 3 , M 4 , M 5 smooth curve.

M 4

The resulting curve is a graph of the derivative.

The accuracy of the graphical method for determining the derivative is low. We provide a description of this method for educational purposes only.

Comment... If in the derivative plotting algorithm instead of the point (-1, 0) we take the point ( -l, 0), where l \u003e 0, then the graph will be plotted on a different scale along the ordinate axis.

5 . 2 .Difference formulas

and) Difference formulas for ordinary derivatives

Difference formulas for the approximate calculation of the derivative are prompted by the very definition of the derivative. Let the values \u200b\u200bof the function at the points x iindicated through y i:

y i= f(x i), x i \u003d a + ih, i \u003d0, 1, ... , n; h=

We consider the case of uniform distribution of points on the segment [ a, b]. For an approximate calculation of the derivatives at the points x iyou can use the following difference formulas , or difference derivatives .

Since the limit of the ratio (5.1) at h® 0 is equal to the right derivative at the point x i, then this relationship is sometimes called the right difference derivative at the point x iFor a similar reason, relation (5.2) is called left difference derivative at the point x i. Relation (5.3) is called central difference derivative at the point x i.

Let us estimate the error of difference formulas (5.1) - (5.3), assuming that the function f(x) expands into a Taylor series in the vicinity of the point x i:

f(x) \u003d f(x i)+ . (5.4)

Assuming in (5.4) x= x i+ hor x \u003d x i- h, we get

By directly substituting expansions (5.5) and (5.6) into formula (5.10), we can obtain the dependence between the second derivative of the function and the difference formula for the second-order derivative .

Computing derivatives is often required to solve many engineering problems. When there is a formula that describes the process, there are no difficulties: we take the formula and calculate the derivative, as they taught in school, we find the values \u200b\u200bof the derivative at different points, and that's it. The difficulty, perhaps, is only in this, to remember how to calculate the derivatives. But what if we only have a few hundred or thousands of rows of data, and there is no formula? Most often this is how it happens in practice. I suggest two ways.

The first is that we approximate our set of points with a standard Excel function, that is, we select a function that best fits our points (in Excel, it is a linear function, logarithmic, exponential, polynomial and power). The second way is numerical differentiation, for which we only need to be able to enter formulas.

Let's remember what a derivative is in general:

The derivative of the function f (x) at the point x is the limit of the ratio of the increment Δf of the function at the point x to the increment Δx of the argument when the latter tends to zero:

So we will use this knowledge: we will simply take very small values \u200b\u200bof the argument increment to calculate the derivative, i.e. Δx.

In order to find the approximate value of the derivative at the points we need (and our points are different values \u200b\u200bof the degree of deformation ε), you can do this. Let's look again at the definition of the derivative and see that when using small values \u200b\u200bof the increment of the argument Δε (that is, small increments of the degree of deformation that are recorded during testing), we can replace the value of the real derivative at the point x 0 (f '(x 0) \u003d dy / dx (x 0)) by the ratio Δy / Δx \u003d (f (x 0 + Δx) - f (x 0)) / Δx.

That is, this is what happens:

f '(x 0) ≈ (f (x 0 + Δx) - f (x 0)) / Δx (1)

To calculate this derivative at each point, we perform calculations using two neighboring points: the first with the coordinate ε 0 along the horizontal axis, and the second with the coordinate x 0 + Δx, i.e. one - the derivative in which we calculate and the one that is better. The derivative calculated in this way is called differential derivative to the right (forward) with a stepΔ x.

We can do the opposite, taking the other two neighboring points: x 0 - Δx and x 0, that is, the one of interest to us and the one to the left. We get the formula for calculating differential derivative to the left (back) with a step -Δ x.

f '(x 0) ≈ (f (x 0) - f (x 0 - Δx)) / Δx (2)

The previous formulas were "left" and "right", and there is another formula that allows you to calculate central difference derivativewith a step of 2 Δx, and which most often used for numerical differentiation:

f '(x 0) ≈ (f (x 0 + Δx) - f (x 0 - Δx)) / 2Δx (3)

To test the formula, consider a simple example with the well-known function y \u003d x 3. Let's build a table in Excel with two columns: x and y, and then build a graph based on the available points.

The derivative of the function y \u003d x 3 is y \u003d 3x 2, the graph of which, i.e. a parabola, we must get it using our formulas.

Let's try to calculate the values \u200b\u200bof the central difference derivative at points x. For this. In the cell of the second row of our table, we hammer our formula (3), i.e. following formula in Excel:

Now we build a graph using the existing values \u200b\u200bof x and the obtained values \u200b\u200bof the central difference derivative:

And here is our red parabola! So the formula works!

Well, now we can move on to a specific engineering problem, which was discussed at the beginning of the article - to finding the change in dσ / dε with increasing deformation. The first derivative of the stress-strain curve σ \u003d f (ε) in foreign literature is called the “strain hardening rate”, and in ours it is called the “hardening factor”. So, as a result of the tests, we have a data array, which consists of two columns: one with the values \u200b\u200bof deformations ε and the other with the values \u200b\u200bof stresses σ in MPa. Let's take cold deformation of steel 1035 or our 40G (see the table of steel analogs) at 20 ° C.

C	Mn	P	S	Si	N
0.36	0.69	0.025	0.032	0.27	0.004

Here is our curve in the coordinates "true stress - true strain" σ-ε:

We act in the same way as in the previous example and we get the following curve:

This is the change in the rate of hardening in the course of deformation. What to do with it is already a separate question.

In addition to formatting cell, row, and column field items, it is often helpful to use multiple Excel worksheets. To organize and search for information in the book, it is convenient to assign proper names to the names of the sheets, reflecting their semantic content. For example, "initial data", "calculation results", "graphs", etc. It is convenient to do this by using context menu... Right-click on the sheet tab, Rename sheet and click .

To add one or more new sheets, select the Sheet command from the Insert menu. To insert several sheets at once, you need to select the tabs of the required number of sheets by holding , then from the Insert menu, execute the Sheet command. The reverse operation for removing sheets is carried out in the same way. Across context menuwhere the Delete command is selected.

A useful operation for moving sheets is to grab the sheet tab with the left mouse button and move it to the desired location. If you press , the copy of the sheet will move, and the number 2 will be added to the sheet name.

Task 7. Change the format of the whole cell B2 to: font - Arial 11; location - in the center, along the bottom edge; one word per line; number format - “0.00”; cell border - double line

2.3. Built-in functions

Excel contains over 150 built-in functions to simplify calculations and data processing. An example of the contents of a cell with a function: \u003d B2 + SIN (C7), where B2 and C7 are the addresses of cells containing numbers, and SIN () is the name of the function. Most used Excel functions:

SQRT (25) \u003d 5 - calculates the square root of (25) RADIANS (30) \u003d 0.5 - converts 30 degrees to radians INT (8.7) \u003d 8 - rounds to the nearest integer RST (-3; 2) \u003d 1 - leaves the remainder of dividing the number (-3) by

divisor (2). The result has a divisor sign. IF (E4\u003e 0.2; "extra"; "error")- if the number in cell E4 is less than 0.2,

then Excel returns "extra" (true), otherwise - "error" (false).

In a formula, functions can be nested within each other, but no more than 8 times.

When using a function, the main thing is to define the function itself and its argument. As an argument, as a rule, the address of the cell in which the information is written is indicated.

You can define a function by typing text (icons, numbers, etc.) in the desired cell, or use Function Wizard... Here, for the convenience of searching, all functions are divided into categories: mathematical, statistical, logical and others. Within each category, they are sorted alphabetically.

Function wizard invoked by a menu commandInsert, Function

or by clicking the icon (f x). In the first appeared window of the Function Wizard (Fig. 4), define the Category and name of a specific function, click ... In the second window (Fig. 5) it is necessary to define Function arguments... To do this, by clicking the button to the right of the first range of cells (Number 1), “close” the window. Select the cells on the basis of which the calculation will be carried out. After that, the selected cells will be entered into the window of the first range. Press the right key again. If the argument is several ranges of cells, then the action is repeated. Then, to complete the work, press ... The result of the calculation will appear in the original cell.

Figure: 4. View of the Function Wizard window

Figure: 5. Window for setting the arguments of the selected function

Task 8. Find the average value of a series of numbers: 2.5; 2.9; 1.8; 3.4; 6.1;

1,0; 4,4.

Decision . We enter numbers in cells, for example, C2: C8. Select cell C9, in which we write the function \u003d AVERAGE (C2: C8), press , in C9 we get the average value of these numbers - 3.15.

Problem 9. Applying the conditional logical function IF, draw up a formula for renaming odd numbers into "autumn", even ones - "spring".

Decision . We select a column for entering the initial data - even (odd) numbers, for example, A. In cell B3, write the formula \u003d IF (OSTAT (A3; 2) \u003d 0, "weight", "axis")... Copying cell B3 along column B, we get the results of the analysis of the numbers written in column A. The results of solving the problem are shown in Fig. 6.

Figure: 6. Solution to problem number 9

Problem 10. Calculate function valuey \u003d x3 + sinx - 4ex for x \u003d 1.58.

Decision . Let's place the data in cells A2 - x, B2 - y. The solution to the problem is shown in Fig. 7 in numerical form on the left and formula form on the right. When solving this problem, you should pay attention to the call of the SIN and exponent functions for entering the argument (see Fig. 8).

Fig. 7. Solution to problem number 10

Fig. 8. SIN and EXP function argument windows

Problem 11. Create in Excel a mathematical model of the problem for calculating the function y \u003d 1 / ((x- 3) (x + 4)), with the values \u200b\u200bx \u003d 3 and y \u003d -4 display "undefined", the numerical values \u200b\u200bof the function - in other cases ...

Problem 12. Make a mathematical model of the problem in Excel: 12.1. to compute with roots

a) √ x3 y2 z / √ x z; b) (z · √ z) 2; c) 3 √ x2 3 √ x; d) √ 5 x5 3-1 / √ 20 x 3-1

12.2. for geometric calculations a) determine the angles of a right-angled triangle, if x is a leg, y is a hypotenuse;

b) determine the distance between two points in the Cartesian system of coordinates XYZ by the formula

d \u003d (x2 - x1) 2 + (y2 - y1) 2 + (z2 - z1) 2

c) determine the distance from the point (x 0, y 0) to the straight line a x + b y + c \u003d 0 by the formula

d \u003d a x0 + b y0 + c / √ (a2 + b2)

d) determine the area of \u200b\u200bthe triangle by the coordinates of the vertices using the formula

S \u003d 1 2 [(x1 - x3) (y2 - y3) - (x2 - x3) (y1 - y3)]

3. Solving problems using formulas and functions

There are actually many tasks that can be successfully solved using Excel formulas and functions. Consider the problems that in practice are most often solved using spreadsheets: linear equations and their systems, calculating the numerical values \u200b\u200bof derivatives and definite integrals.

The derivative of a function y \u003d f (x) is the ratio of its increment ∆y to the corresponding increment ∆x of the argument when

∆x → 0

y \u003d f (x + x) - f (x)

Task .13. Find the derivative of the function y \u003d 2x 3 + x 2 at the point x \u003d 3.

Decision. The derivative calculated analytically is 60. Calculation of the derivative in Excel will be carried out by the formula (1). To do this, follow the sequence of actions:

· Let's designate columns: X - function arguments, Y - function values, Y` - function derivative (Fig. 9).

· We tabulate the function in the vicinity of the pointx \u003d 3 with a small step, for example, 0.001, the results are entered in column X.

Figure: 9. Table for calculating the derivative of a function

In cell B2, enter the formula for calculating the function \u003d 2 * A2 ^ 3 + A2 ^ 2.

· Copy the formula to the line7, we get the values \u200b\u200bof the function at the tab stops of the argument.

In cell C2, enter the formula for calculating the derivative \u003d (B3-B2) / (A3-A2).

· Copy the formula to the line6, we get the values \u200b\u200bof the derivatives at the tab stops of the argument.

For x \u003d 3, the derivative of the function is equal to 60.019, which is close to the value calculated analytically.

trapezium method. In the trapezoid method, the region of integration is divided into segments with a certain step and the area under the graph of the function on each segment is considered equal to the area of \u200b\u200bthe trapezoid. Then the calculation formula takes the following form

S N \u003d ∫ f (u) du ≈ h N ∑ - 1 [f (a + h i) + f (a + h (i + 1))] (2),
	2 i \u003d 0

where h \u003d (b- a) / N is the partitioning step; N is the number of split points.

To improve accuracy, the number of split points is doubled, and the integral is re-calculated. The fragmentation of the initial interval is stopped when the required accuracy is achieved:

integral, we perform the following actions:

- choose N \u003d 5, in cell F2 we calculate h-step of the partition (Fig. 10);

Figure: 10. Calculation of the definite integral

· In the first columnAnd we write down the number of the interval i;

· In cell B2, write the formula \u003d 3 * (2 + F2 * A2) ^ 2 to calculate the first term in formula (2);

· In cell C2, write the formula \u003d 3 * (2 + F2 * (A2 + 1)) ^ 2 to calculate the second term;

· Let's “stretch” cells with formulas on4 rows down by columns;

In cell C7 we write down the formula and calculate the sum of the terms,

In cell C8, write down the formula and calculate SN the desired value of the definite integral 19.02 (the value of S N obtained analytically

19).

Task. 15. Calculate the definite integral:

1.Y \u003d ∫ 2 x d x	2.Y \u003d ∫ 2 x3 dx
	−1

2 π

Y \u003d ∫ 2sin (x) dx

Y \u003d ∫ x2 dx

−2

Y \u003d ∫

Y \u003d ∫

3x - 2

(2x + 1) 3

x + 3

Y \u003d ∫ cos

Y \u003d ∫

x 2 + 4

3.2. Solving linear equations

Linear Equationsin Excel can be solved using function Selection of the parameter.When you select a parameter, the value of the influencing cell (parameter) is changed until the formula depending on this cell returns the specified value.

Consider the procedure for finding a parameter using a simple example of solving a linear equation with one unknowns.

Problem 16. Solve the equation 10 x - 10 / x \u003d 15.

Decision. For the desired value of the parameter - x, select cell A3. Let's enter in this cell any number that lies in the scope of the function definition (in our example, this number cannot be equal to zero). Let it be 3. This value will be used as the starting value. In a cell, for example, B3, in accordance with the above equation, enter the formula \u003d 10 * A3-10 / A3. As a result of a series of calculations using this formula, the desired parameter value will be selected. Now on the Tools menu, choosing the command Selection of the parameter,run the parameter search function (Fig. 11, a). Let's enter search parameters:

· In field Set in cellwe will enter an absolute reference to the cell $ B $ 3 containing the formula.

· In the Value field, enter the desired result 15.

· In field By changing the value of a cellenter a link to cell A3 containing the selected value, and click .

At the end of the function Parameter selectiona window will appear on the screen Parameter selection result, which will display the search results. The found parameter 2.000025 will appear in cell A3, which was reserved for it.

Pay attention to the fact that in our example the equation has two solutions, and the parameter is selected only one. This is because the parameter changes only until the required value is returned. The first argument found in this way is returned to us as a search result. If as

the initial value in our example, specify -3, then the second solution to the equation will be found: -0.5.

Fig. 11. Equation solution: a - data entry, b - solution result

	Problem 17. Solve the equations
	5x / 9- 8 \u003d 747x / 12		(2x + 2) / 0.5 \u003d 6x
	0.5 (2x- 1) + x / 3 \u003d 1/6		7 (4x- 6) + 3 (7- 8x) \u003d 1
	Linear system	equations		can be addressed by different

ways: substitution, addition and subtraction of equations, using matrices. Consider a method for solving the canonical system of linear equations (3) using matrices.

a1 x + a2 y + b1 \u003d 0

a3 x + a4 y + b2 \u003d 0

It is known that the system of linear equations in matrix representation is written in the form:

where A is a matrix of coefficients, X is a vector - a column of unknowns,

B is a column vector of free members. The solution to such a system

written in the form

X \u003d A-1 B,

where A -1 is the inverse matrix with respect to A. This follows from the fact that when solving matrix equations at X, the unit matrix E must remain. Multiplying both sides of the equation AX \u003d B by A -1 on the left, we obtain a solution to the linear system of equations.

Problem 18. Solve the system of linear equations

Decision. For a given system of linear equations, the values \u200b\u200bof the corresponding matrix and column vector are:

To solve the problem, we will perform the following actions:

A2: B3 and write the elements of the matrix A into it.

· Select a block of cells, for example,C2: C3 and write the elements of the matrix B into it.

· Select a block of cells, for example,D2: D3 to place the result of solving a system of equations.

In cell D2, enter the formula \u003d MULTIPLE (MOBR (A2: B3); C2: C3).

The Excel library under Math Functions contains functions for performing operations on matrices. In particular, these are functions:

Parameters of these functions can be address references to arrays containing matrix values \u200b\u200bor range names and expressions.

For example, MOPR (A1: B2) or MOPR (matrix_1).

We will indicate to Excel that an operation is performed on arrays, for this we press the key combination + + , in cells D2 and D3 the result will be x \u003d 2.16667; y \u003d - 1.33333.

4. Solving optimization problems

Many problems of forecasting, design and production are reduced to a wide class of optimization problems. Such tasks are, for example: maximizing the output of goods with restrictions on raw materials for the production of these goods; scheduling staffing to achieve the best results at the lowest cost; minimizing the cost of transporting goods; achieving a given alloy quality; determination of the size of a certain container, taking into account the cost of the material to achieve the maximum volume; various

problems that include random variables, and other problems of optimal resource allocation and optimal design.

The solution of problems of this kind can be carried out by EXCEL using the Find solution tool, which is located in the Service menu. The formulation of such problems can be a system of equations with several unknowns and a set of constraints on solutions. Therefore, the solution of the problem must begin with the construction of the corresponding model. Let's get acquainted with these commands using an example.

Problem 20. Suppose that we decided to produce two types of lenses, A and B. The lens of view A consists of 3 lens components, type B of 4. No more than 1800 lenses can be made per week. It takes 15 minutes to assemble a type A lens, and 30 minutes for type B. The working week for 4 employees is 160 hours. How many lenses A and B need to be made in order to get the maximum profit, if a lens of type A costs 3,500 rubles, type B - 4,800 rubles.

Decision. To solve this problem, it is necessary to draw up and fill in a table in accordance with Fig. 12:

· Rename the cellB2 in x, number of type A lenses.

· Taxically rename cell B3 to y.

Objective function Profit \u003d 3500 * x + 4800 * yenter in cell B5. · The cost of a complete set is equal to \u003d 3 * x + 4 * y in cell B7.

· Time costs are equal to \u003d 0.25 * x + 0.5 * y enter in cell B8.

	Name
	complete set
	Time cost

Fig. 12. Filling the table with source data

· Select cell B5 and select the Data menu, after which we activate the Find solution command. Fill in the cells of this window in accordance with Fig. 13.

· Let's press<Выполнить >; if everything is done correctly, then the solution will be as shown below.

It is known that by numerical approximate methods, the derivative of a function at a given point can be calculated using the finite difference formula. The expression for calculating the derivative of a function of one variable at the point x k, written in finite differences, has the form

where Δх is a very small finite value.

For sufficiently small values \u200b\u200bof Δx, it is possible to obtain the value of the derivative of the function at a point with acceptable accuracy. To calculate the derivative in MS Excel, we will use the above formula. Consider the technology for calculating the derivative using the example.

Example 1.18Find the derivative of the function y \u003d 2x 3 + x 2 at the point x \u003d 3. Note that the derivative of the reduced function at the point x \u003d 3, calculated by the analytical method, is 60 - we need this value to check the result obtained by calculating it numerically.

The task of calculating a derivative in a tabular processor can be solved in two ways.

Solution in the first way

Let's enter into the cell of the worksheet the formula on the right side of the given functional dependence, for example, in cell B2, as shown in the figure, making a reference to the cell where the value x will be located, for example A2,

2 * A2 ^ 3 + A2 ^ 2.

Let's set the neighborhood of the point x \u003d 3 of a sufficiently small size, for example, the value on the left x k \u003d 2.9999999, and the value on the right x k +1 \u003d 3.00000001, and enter these values \u200b\u200binto cell A2 and A3, respectively. In cell C2, enter the formula for calculating the derivative \u003d (B3-B2) / (A3-A2).

As a result of the calculation, the approximate value of the derivative of the given function at the point x \u003d 3 will be displayed in cell C2, the value of which is 60, which corresponds to the result obtained analytically (Figure 1.24).

Solution in the second way

Let's enter the given value of the argument equal to 3 in the cell of worksheet A2, in cell B2 we indicate a sufficiently small increment of the argument - (1E - 9), in cell C2 we enter the formula for calculating the derivative

\u003d (2 * (A2 + B2) ^ 3+ (A2 + B2) ^ 2- (2 * A2 ^ 3 + A2 ^ 2)) / B2.

After pressing the key we get the calculation result 60.0000.

As you can see, the result is the same as in the first method. The given second method is more preferable in cases when you need to build a table of values \u200b\u200bof the derivative of a function for the given values \u200b\u200bof the argument.

Calculation of local extrema of a function

Recall that the function Y \u003d f (x) has an extremum at the value x \u003d x k if the derivative of the function at this point is equal to zero.

If the function f (x) is continuous on the segment [a, b] and has a local extremum inside this segment, then it can be found using the Excel add-in Search for a solution.

Consider the sequence of finding the extremum of a function using the example

Example 1.19 A continuous function is given y \u003d x 2 + x + 2. It is required to find its extremum (minimum value) on the segment [-2; 2].

Decision

In cell A3 of the worksheet, enter any number that belongs to the specified segment, this cell will contain the value x.

In cell B3, enter the formula that determines the given functional dependence. Instead of the variable x in this formula, there should be a reference to cell A3: \u003d A3 ^ 2 + A3 + 2.

Let's execute the menu command Service / Search for a solution.

In the Search for solution dialog box that opens, in the Set target cell field, specify the address of the cell containing the formula (B3), set the Minimum value switch, in the Change cell field, specify the address of the cell that contains the variable x-A3.

Add two constraints to the corresponding field: A3\u003e \u003d - 2 and A3<=2 (рис. 1.25).

Let's click on the button Parameters and in the opened dialog box the parameters of the search for a solution set the relative error of calculations and the limiting number of iterations.

Let's click on the Run button. In cell A3, the value of the argument x of the function will be calculated, at which it takes the minimum value, and in cell B3, the minimum value of the function.

As a result of performing calculations in cell A3, the value of the independent variable will be obtained, at which the function takes the smallest value -0.5, and in cell B3, the minimum value equal to 1.75.

Let's build a graph of the given function and make sure that the solution to the equation is found, right.

Note.In a particular case, when finding a local extremum using the considered technology, it is possible to obtain a value that is not an extremum, but is simply the minimum or maximum of the function in a given range of variation of the argument.

Therefore, additional verification is necessary, i.e. calculation of the derivative of the function at the found point.

Using the above technology for the numerical calculation of the derivative of a function at a given point, let us check whether the found point x \u003d -0.5 is the extremum point of the function y \u003d x 2 + x + 2. The solution is shown in the figure.

As you can see, the derivative at the found point is zero, therefore, the found value of the function is its extreme value.

Example 1.20You want to find the values \u200b\u200bof the argument in the range [-1; 1], at which the function y \u003d x 2 + x + 2 has extrema.

Decision

We tabulate the given function with a step of 0.2.

Applying the second of the above methods for calculating the derivative, we calculate the values \u200b\u200bof the function y \u003d f (x + dx).

Let's calculate the values \u200b\u200bof the derivative for each table value of the argument.

Analyzing the obtained values \u200b\u200bof the derivatives of the function at points, we find that the derivative changes sign in the range of values \u200b\u200bof the argument (-0.6; -0.4), therefore, there is an extremum point in this interval. In addition, note that the sign of the derivative changes from minus to plus, therefore, the extremum point is the minimum of the function.

Applying tool Parameter selectionor Searchsolutions for solving the equation Y (x) \u003d 0

with respect to x, we calculate the exact value of the argument at which the original function takes an extra small value (-0.5) (Fig. 1.26).

The obtained value of the derivative of the investigated function in pointx \u003d -0.5 is equal to zero, therefore, at this point the function has an extremum.