Differentiating table functions in Excel. Graphic and numerical differentiation. Algorithm for plotting the derivative

Numerical differentiation

Section No. 5

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

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

One of effective methods solving differential equations is the difference method, when instead of the desired function, a table of its values ​​at certain points is considered, and the derivatives are approximately replaced by difference formulas.

Let the graph of the function be known y = f(X) on the segment [ A,b]You can construct a graph of the derivative of a function, remembering its geometric meaning. Let's take advantage of the fact that the derivative of a function at a point X is equal to the tangent of the angle of inclination to the abscissa axis of the tangent to its graph at this point.

If x = x 0, let's find at 0 =f(x 0) using a graph and then draw a tangent AB to the graph of the function at the point ( X 0 , y 0) (Fig. 5.1). Let's draw a straight 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 tangent to the abscissa axis, i.e., the derivative of the function f(x)at the point X 0:

at 1 = = tg α = f ¢ ( x 0), and period M 0 (X 0 , at 1) belongs to the derivative graph.

To plot the derivative graph, you need to split the segment [ A,b] into several parts with dots x i, then for each point graphically plot the derivative value 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 derivative graphics.

Algorithm for constructing a derivative graph:

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

2. Let’s construct the remaining points in the same way M 2 ,M 3 , M 4 and M 5 .

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

The resulting curve is a graph of the derivative.

Accuracy graphic method derivative definition is low. We provide a description of this method for educational purposes only.

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

5 . 2 .Difference formulas

A) Difference formulas for ordinary derivatives

Difference formulas for approximate calculation of the derivative are suggested by the very definition of the derivative. Let the function values ​​at points x i indicated by y i:

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

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

Since the limit of relation (5.1) at h® 0 is equal to the right derivative at the point x i, then this relationship is sometimes called right difference derivative at the point x i.For 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)=f(x i)+ . (5.4)

Assuming in (5.4) X= x i+ h or x = x i- h, we get

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

Solving many engineering problems often requires the calculation of derivatives. When there is a formula that describes the process, there are no difficulties: we take the formula and calculate the derivative, as we were taught in school, find the values ​​of the derivative at different points, and that’s it. The only difficulty, probably, is to remember how to calculate derivatives. But what if we only have a few hundred or thousand rows of data and no formula? Most often, this is exactly what happens in practice. I suggest two ways.

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

Let's remember what a derivative is in general:

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

So we’ll use this knowledge: we’ll simply take very small values ​​of 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 meanings degree of deformation ε) you can do this. Let's look again at the definition of the derivative and see that when using small increments of the argument Δε (that is, small increments in the degree of deformation that are recorded during testing), we can replace the value of the real derivative at point x 0 (f'(x 0)=dy/dx (x 0)) to the ratio Δy/Δx=(f (x 0 + Δx) – f (x 0))/Δx.

So 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 coordinate ε 0 along the horizontal axis, and the second with coordinate x 0 + Δx, i.e. one is the derivative in which we calculate and the one to the right. The derivative calculated in this way is called difference derivative to the right (forward) in stepsΔ x.

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

f’(x 0) ≈(f (x 0) – f (x 0 – Δx))/Δx (2)

The previous formulas were “left” and “right”, but there is another formula that allows you to calculate central difference derivative with 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 check the formula, consider a simple example with the known function y=x 3 . Let's build a table in Excel with two columns: x and y, and then build a graph using the available points.

The derivative of the function y=x 3 is y=3x 2, the graph of which, i.e. a parabola, we must obtain using our formulas.

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

Now we build a graph using the existing values ​​of x and the obtained values ​​of the central difference derivative:

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

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

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

We proceed in the same way as in the previous example and get this curve:

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

Graphic differentiation begins with plotting a function graph based on given values. In an experimental study, such a graph is obtained using recording instruments. Next, tangents to the curve are drawn in fixed positions and the values ​​of the derivative with respect to the tangent of the angle formed by the tangent with the abscissa axis are calculated.

In Fig. 5.8, A The curve obtained experimentally on the installation is shown (Fig. 5.6). The determination of angular acceleration (the desired function) is carried out by graphic differentiation according to the relationship:

(5.19)

Tangent of the angle of inclination of the tangent to the curve at some point i represented as a ratio of segments, where TO– selected integration segment (Fig. 5.8, b)

After substituting this relation into relation (5.19), we obtain

where is the ordinate of the demand graph of angular acceleration;

Scale of the desired graph; SI units: = mm; = mm/(rad s -2).

The function graph is constructed using the found ordinate values ​​for a number of positions. The points on the curve are connected by hand with a smooth line, and then outlined using a pattern.

Graphic differentiation using the tangent method considered has relatively low accuracy. Higher accuracy is obtained by graphical differentiation using the chord method (Fig. 5.8, V And G).

The remaining constructions are similar to those previously described in graphic differentiation using the tangent method. Select a segment (mm); conduct rays inclined at angles until the intersection with the ordinate axis at points 1 ", 2 ", 3 "..., which are transferred to the ordinates drawn in the middle of each of the intervals. The resulting points 1 *, 2 *, 3 * are points of the required function .

The scales along the coordinate axes with this construction method are related by the same relation (5.21), which was derived for the case of graphic differentiation using the tangent method.

Differentiation of a function f(x), specified (or calculated) in the form of an array of numbers, is performed by the method of numerical differentiation using a computer.

The smaller the step in the array of numbers, the more accurately you can calculate the value of the derivative of the function in this interval

Example 3: Using an auto-filter, select students studying in group No. 5433 with a last name starting with the letter C.

Sequencing

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

2. Last name.

3. Select an item from the listText filters → Custom filter. In the window that appears Custom AutoFilter select the selection criterion begins with , enter the desired letter in the field opposite (check that the layout is Russian). Click OK.

4. Open dropdown list in column Group no.

5. Select the desired number.

Filter database records using an advanced filter

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

When using an advanced filter, the names of the columns on which the conditions are set are copied below the source table. Selection criteria are entered under the column names. After applying a filter, only those rows that meet the specified criteria can be displayed on the screen, 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 whose average score is greater than or equal to 4.5.

Sequencing

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

2. Copy column names Group number and average score

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

Rice. 32. Excel window with advanced filter

2. On the Data tab on the Sort toolbar

and filter select Advanced. A dialog box will appear (Fig. 33), in which the data ranges are indicated.

Rice. 33. Advanced filter window

In the input field Original range specifies the interval containing the source database. In our case, the range of cells from A1 to I9 is ​​highlighted.

In the input field Range of conditions The range of cells on the worksheet that contains the required criteria (C12:D13) is highlighted.

In the input field Place result in range indicates the interval into which lines that satisfy the criteria are copied

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

Checkbox Only unique entries designed to display only non-repeating lines.

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

Rice. 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 must be at least 6. For example, a database List 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 under the original table.

Rice. 35. Excel window with database Client list

LABORATORY WORK No. 5

Numerical differentiation and simple function analysis

Purpose of work: Investigate a function to an extremum, learn to determine the critical point.

From a mathematics course we know that the formula for the derivative in general view looks like that:

where Δx is the increment of the argument; x is a number tending to zero. Using the derivative, you can determine the critical points of a function - minimums, maximums or inflections. If the value of the derivative of a function at some 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 = x 2 + 2x 3 is given on the interval x 5;5. Investigate the behavior of the function f(x) .

Sequencing

1. Let Δx = 0.00001. In cell A1 enter: šDx=Ÿ (Fig. 36). Select the letter D, right-click on the selected letter, select Format Cells. On the Font tab, select the Symbol font. The letter D will turn into the Greek letter ѓў. Alignment in a cell can be done to the right. In cell B1, enter the value 0.00001.

2. In cells A2 to F2, create a table header, as shown in Fig. 36.

3. Column A, starting from the third row, will contain the x values. In cells A3 through A13, enter values ​​from –5 to 5.

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

5. To determine the derivative of a function and calculate its values ​​on 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 looks like: =A3+$B$1. Extend its value to the final value of the argument x .

Rice. 36. Excel window with a study of the behavior of a function

6. In cell D3 write the formula =C3^2+2*C3-3, which calculates the value of the function f from the argument x Δx. Extend the resulting value to the final value of the argument.

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

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

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

9. Draw graphs based on the values ​​of f x and f" (x). The graph (Fig. 37) shows that if the value of the derivative of a function is zero, then at this point the function has a critical point.

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

Rice. 37. Diagram for studying the behavior of a function

Tasks for independent work

The function f(x) is given on the interval x. Investigate the behavior of the function f(x) . Build graphs.

LABORATORY WORK No. 6

Constructing a tangent to the graph of a function

Purpose of work: To master the calculation of the values ​​of the equation of a tangent to the graph of a function at point x 0.

Equation of the tangent to the graph of the function y = f(x) at a point

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

Sequencing:

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

Rice. 38. Table of initial data

2. Determine the location of x, x 0, f(x 0) and f" (x 0) in the table. Obviously, x will be the values ​​from

column A, starting from the third line (Fig. 38). If x 0 = 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 of f" (x 0)

– in cell E9.

3. In column F, the equation of the tangent to the graph of the function f(x) is calculated. When calculating equation (1), it is necessary that the values ​​of x 0, f(x 0) and f" (x 0) do not change. Therefore, in the writing

To determine the addresses of cells A9, B9 and E9, you must use absolute references to these cells. Cells are fixed using the š$Ÿ sign. The cells will look like: $A$9 , $B$9 and $E$9 .

Rice. 39. Graph of the function f(x) and the tangent to the graph at point x=1

Tasks for independent work

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.

1. Vedeneeva, E. A. Functions and formulas Excel 2007. User library / E. A. Vedeneeva. – St. Petersburg: 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

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

In addition to formatting cell, row, and column field elements, it is often useful to use multiple Excel worksheets. To organize and search for information in a book, it is convenient to assign names to sheets proper names, reflecting their semantic content. For example, “initial data”, “calculation results”, “graphs”, etc. It is convenient to do this 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 to remove sheets is carried out similarly. Through context menu, where 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 , a copy of the sheet will be moved, and the number 2 will be added to the sheet name.

Task 7. Change the format of the entire 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 more than 150 built-in functions to simplify calculations and data processing. An example of the contents of a cell with a function: =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) = 5 – calculates the square root of the number (25) RADIANS(30) = 0.5 – converts 30 degrees to radians WHOLE(8,7) = 8 – rounds to the nearest lower integer REMAIN(-3,2) = 1 – leaves a remainder when dividing the number (-3) by

divisor(2). The result has the divisor sign. IF(E4>0.2;”additional”;”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. The argument, as a rule, specifies the address of the cell in which the information is recorded.

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

Function Wizard called by menu command Insert, Function

or by pressing the icon (f x ). In the first window of the Function Wizard that appears (Fig. 4), determine the Category and name of the specific function, click . In the second window (Fig. 5) you need to define Function Arguments. To do this, click the button to the right of the first range of cells (Number 1) to “close” the window. We select the cells on the basis of which the calculation will be carried out. After this, 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 repeat the action. Then, to complete the work, click . The original cell will contain the result of the calculation.

Rice. 4. Function Wizard window view

Rice. 5. Window for specifying 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.

Solution . Enter numbers into cells, for example, C2:C8. Select cell C9, in which we write the function = AVERAGE(C2:C8), press , in C9 we get the average value of the indicated numbers - 3.15.

Task 9. Using the conditional logical IF function, create a formula for renaming odd numbers as “autumn” and even numbers as “spring”.

Solution . We select a column for entering the initial data - even (odd) numbers, for example, A. In cell B3 we write the formula =IF(REM(A3,2)=0,"weight","axis"). By copying cell B3 along column B, we obtain the results of the analysis of the numbers written in column A. The results of solving the problem are presented in Fig. 6.

Rice. 6. Solution to problem No. 9

Problem 10. Calculate function value y = x3 + sinx – 4ex for x = 1.58.

Solution . 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 in formula form on the right. When solving this problem, you should pay attention to calling the SIN and exponent functions to enter an argument (see Fig. 8).

Fig.7. Solution to problem No. 10

Fig.8. Window for input of function argument SIN and EXP

Problem 11. Create a mathematical model of the problem in Excel to calculate the function y= 1/ ((x- 3) · (x+ 4)), for values ​​x= 3 and y= -4 display “undefined”, numerical values ​​of the function – in other cases .

Problem 12. Create a mathematical model of the problem in Excel: 12.1. for calculations 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 triangle, if x is the leg and y is the hypotenuse;

b) determine the distance between two points in the Cartesian XYZ coordinate system using the formula

d = (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 = 0 using the formula

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

d) determine the area of ​​the triangle from the coordinates of the vertices using the formula

S = 1 2 [ (x1 − x3 )(y2 − y3 ) − (x2 − x3 )(y1 − y3 )]

3. Solving problems using formulas and functions

There are actually many problems that can be successfully solved using Excel formulas and functions. Let's consider the problems that are most often solved in practice using spreadsheets: linear equations and their systems, calculation of numerical values ​​of derivatives and definite integrals.

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

∆x→ 0

y = f (x + x) − f (x)

Problem .13. Find the derivative of the function y = 2x 3 + x 2 at the point x=3.

Solution. The derivative calculated by the analytical method is 60. We will calculate the derivative in Excel using formula (1). To do this, we perform the following sequence of actions:

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

· Tabulate the function in a neighborhood of the point x = 3 with a small step, for example, 0.001, we enter the results in the X column.

Rice. 9. Table for calculating the derivative of a function

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

· Let's copy the formula to the line 7, we get the function values ​​at the argument tab stops.

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

· Let's copy the formula to the line 6, we obtain the values ​​of the derivatives at the tabulation points of the argument.

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

trapezoid method. In the trapezoidal method, the integration domain 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 ​​the trapezoid. Then calculation formula takes the following form

where h= (b- a)/ N – partition step; N – number of splitting points.

To increase accuracy, the number of partition points is doubled, and the integral is calculated again. The fragmentation of the initial interval is stopped when the required accuracy is achieved:

integral, we perform the following actions:

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

Rice. 10. Calculation of the definite integral

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

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

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

· “Stretch” cells with formulas to 4 rows down columns;

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

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

19).

Task. 15. Calculate the definite integral:

3.2. Solving Linear Equations

Linear equations in Excel can be solved using the function Selection of parameter. When selecting a parameter, the value of the influencing cell (parameter) changes until the formula depending on that cell returns the specified value.

Let's consider the procedure for searching for a parameter on simple example solutions to a linear equation with one are unknown.

Problem 16. Solve the equation 10 x - 10 / x = 15 .

Solution. For the desired value of the parameter – x, select cell A3. Let's enter into this cell any number lying in the domain of definition of the function (in our example, this number cannot be equal to zero). Let it be 3. This value will be used as the initial value. In cell, for example, B3, in accordance with the above equation, enter the formula =10*A3-10/A3. As a result of a series of calculations using this formula, the desired parameter value will be selected. Now in the Tools menu, selecting the command Selection of parameter, Let's launch the parameter search function (Fig. 11, a). Let's enter the search parameters:

· In field Set to cell Let's enter an absolute reference to cell $B$3 containing the formula.

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

· In field Changing the value of a cell enter a link to cell A3 containing the selected value and click .

Upon completion of the function Parameter selection a window will appear on the screen Result of parameter selection, 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, but only one parameter has been selected. This occurs because the parameter is only modified until the required value is returned. The first argument found in this way is returned to us as a search result. If as

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

Fig. 11. Solution of the equation: a - data input, b - solution result

methods: substitution, addition and subtraction of equations, using matrices. Let's consider a method for solving the canonical system of linear equations (3) using matrices.

a1 x + a2 y + b1 = 0

a3 x + a4 y + b2 =0

It is known that a 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 the column vector of free terms. The solution to such a system

written in the form

X = A-1 B,

where A -1 is the matrix inverse to A. This follows from the fact that when solving matrix equations for X, the identity matrix E must remain. Multiplying from the left both sides of the equation AX = B by A -1, we obtain the solution linear system equations.

Problem 18. Solve a system of linear equations

Solution. For a given system of linear equations, the values ​​of the corresponding matrix and column vector have the form:

To solve the problem, let's perform the following steps:

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

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

· Let's 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 = MULP(MOBR(A2:B3),C2:C3).

The Excel library in the mathematical functions section contains functions for performing operations on matrices. In particular, these are the functions:

The parameters of these functions can be address links to arrays containing matrix values ​​or range names and expressions.

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

· Let's tell Excel that an operation is being performed on arrays by pressing the key combination + + , in cells D2 and D3 the result will be x = 2.16667; y= - 1.33333.

4. Solving optimization problems

Many forecasting, design, and manufacturing problems can be reduced to a broad 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; drawing up staffing to achieve the best results at the lowest cost; minimizing the cost of transporting goods; achieving the specified quality of the alloy; determining the dimensions of a certain container, taking into account the cost of the material to achieve the maximum volume; various

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

Problems of this type can be solved in EXCEL using the Solution Search tool, which is located in the Tools menu. The formulation of such problems can be a system of equations with several unknowns and a set of restrictions on solutions. Therefore, solving the problem must begin with constructing an appropriate model. Let's get acquainted with these commands using an example.

Problem 20. Suppose that we decide to produce two types of lenses A and B. A lens of type A consists of 3 lens components, type B - of 4. A maximum of 1,800 lenses can be produced in a week. It takes 15 minutes to assemble a type A lens, and 30 minutes for a type B lens. The working week for 4 employees is 160 hours. How many lenses A and B must be produced to get maximum profit, if a lens of type A costs 3500 rubles, type B costs 4800 rubles.

Solution. To solve this problem, it is necessary to compose and fill out a table in accordance with Fig. 12:

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

· And similarly, let’s rename cell B3 to y.

Target function Profit = 3500*x+4800*y enter in cell B5. · The costs for packaging are equal to =3*x+4*y, enter in cell B7.

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

Fig. 12. Filling the table with source data

· Select cell B5 and select the Data menu, after which we activate the Search for a solution command. Let's fill the cells of this window in accordance with Fig. 13.

· Click<Выполнить >; if done correctly, the solution will be as below.