How to calculate the 2nd derivative of a function in excel. Numerical differentiation in Excel

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

On fig. 5.8, a the curve obtained experimentally on the installation is shown (Fig. 5.6). The determination of the angular acceleration (the desired function) is carried out by graphical differentiation according to the ratio:

(5.19)

The tangent of the slope of the tangent to the curve at some point i are represented as a ratio of segments , where To- the selected segment of integration (Fig. 5.8, b)

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

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

The scale of the desired graph; SI units: = mm; \u003d mm / (rad with -2).

The graph of the function is built according to the found values ​​of the ordinates for a number of positions. The points on the curve are connected by hand with a smooth line, and then circled with a pattern.

Graphical differentiation by the considered method of tangents has a relatively low accuracy. Higher accuracy is obtained with graphical differentiation by the chord method (Fig. 5.8, in and G).

A number of points are marked on a given curve 1 ", 2 ", 3" , which are connected by chords, i.e. replace the given curve with a broken line. The following assumption is made: the angle of inclination of the tangents at the points located in the middle of each section of the curve is equal to the angle of inclination of the corresponding chord. This assumption introduces some error, but it applies only to this point. These errors are not summed, which ensures an acceptable accuracy of the method.

The remaining constructions are similar to those previously described for graphical differentiation by the tangent method. Select a segment (mm); conduct beams inclined at angles to the intersection with the y-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 the points of the desired 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 graphical differentiation by the tangent method.

Function differentiation f(x), given (or calculated) as 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

Many engineering problems often require 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 learned at school, we find the values ​​​​of the derivative at different points, and that's it. The difficulty, perhaps, lies only in this, in order to remember how to calculate derivatives. But what if we have only a few hundreds or thousands of rows of data, and there is no formula? Most of the time, this is exactly what happens in practice. I offer two ways.

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

Recall 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 let's use this knowledge: we will 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 values ​​of the degree of deformation ε), you can do the following. 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 the point x 0 (f'(x 0)=dy/dx (x 0)) to 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 more correct. The derivative calculated in this way is called difference 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, i.e. the one that interests us and the one to the left. We get the formula for calculating difference 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 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 . We will build a table in Excel with two columns: x and y, and then we will 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. parabola, we must obtain using our formulas.

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

Now we build a graph using the already 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 a specific engineering problem, which was discussed at the beginning of the article - to finding the change in dσ/dε with increasing strain. The first derivative of the "stress-strain" curve σ=f (ε) in foreign literature is called "hardening rate" (strain hardening rate), and in ours - "hardening factor". So, as a result of testing, we have a data array, which consists of two columns: one with strain values ​​ε and the other with stress values ​​σ in MPa. Let's take the cold deformation of steel 1035 or our 40G (see the table of analogues of steels) 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 get the following curve:

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

In addition to formatting cell box elements, rows, and columns, it is often useful to use multiple Excel worksheets. To organize and search for information in the book, it is convenient to assign proper names to the titles of the sheets, reflecting their semantic content. For example, “initial data”, “calculation results”, “graphs”, etc. It is convenient to do this using context menu. Press the right mouse button on the sheet tab, Rename sheet and click .

To add one or more new sheets, select Sheet from the Insert menu. To insert several sheets at once, select the tabs for the required number of sheets by holding , then from the Insert menu, execute the command Sheet. The reverse operation to remove sheets is carried out in a similar way. 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 at the same time 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 (25) RADIANS(30) = 0.5 - Converts 30 degrees to radians INT(8,7) = 8 - Rounds down to nearest integer MOD(-3;2) = 1 - leaves the remainder of the division of the number (-3) by

divisor(2). The result has a divisor sign. IF(E4>0,2;”additional”;”error”)- if the number in cell E4 is less than 0.2,

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

In a formula, functions can be nested into each other, but not 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 recorded 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 the menu command Insert, Function

or by pressing the icon (f x ). In the first window that appears of the Function Wizard (Fig. 4), we define the Category and the name of a specific function, click . In the second window (Fig. 5) it is necessary to determine Function Arguments. To do this, by clicking the button to the right of the first range of cells (Number 1) “cover” the window. We select the cells on the basis of which the calculation will be carried out. After that, the selected cells will be entered in the first range window. Press the right key again. If the argument is several ranges of cells, then the action is repeated. Then press to finish the job. . The source cell will contain the result of the calculation.

Rice. 4. View of the Function Wizard window

Rice. 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 the 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 function IF, make a formula for renaming odd numbers into "autumn", even numbers - "spring".

Decision . We select a column for entering the initial data - even (odd) numbers, for example, A . In cell B3, write the formula =IF(MOD(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 shown in fig. 6.

Rice. 6. Solution of problem No. 9

Task 10. Compute function value y = x3 + sinx - 4ex for x = 1.58.

Decision . Let's place the data in cells A2 - x, B2 -y. The solution of 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 of problem number 10

Fig.8. Windows for entering the argument of the function SIN and EXP

Task 11 . Make a mathematical model of the problem in Excel to calculate the function y= 1/ ((x- 3) (x+ 4)), for the values ​​x= 3 and y= -4, display "undefined", the numerical values ​​of the function - in other cases .

Task 12 . Make a mathematical model of the problem in Excel: 12.1. for calculation 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 the leg, y is the hypotenuse;

b) determine the distance between two points in the XYZ Cartesian 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 line a x + b y + c = 0 using the formula

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

d) determine the area of ​​a 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 tasks that can be successfully solved using Excel formulas and functions. Consider the tasks that in practice are most often solved 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 .

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

· Let's draw the notation of the columns: Х – function arguments, Y – function values, Y ` – derivative of the function (Fig. 9).

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

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 .

· Copy the formula up to the line 7 , we get the values ​​of the function at the tab stops of the argument.

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

· Copy the formula up to the line 6 , we get the values ​​of the derivatives at the tab stops 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 trapezoid method, the integration area 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 the calculation formula takes the following form

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

where h= (b- a)/ N is the partition step; N is the number of split points.

To improve the accuracy, the number of split points is doubled, the integral is calculated again. The splitting of the original interval is stopped when the required accuracy is reached:

integral, do the following:

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

Rice. 10. Calculation of a 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 on 4 rows down columns;

We write the formula in cell C7 and calculate the sum of the terms,

In cell C8, we write 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 a definite integral:

1. Y = ∫ 2 x d x	2. Y = ∫ 2 x3 dx
	−1

2 pi

Y = ∫ 2sin(x )dx

Y = ∫ x2 dx

−2

Y = ∫

Y = ∫

3x − 2

(2x + 1) 3

x + 3

Y = ∫ cos

Y = ∫

x 2 + 4

3.2. Solving linear equations

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

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

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

Decision. For the desired value of the parameter - x, select cell A3. Let's enter into this cell any number that lies in the area of ​​the 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 starting value. In a 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 value of the parameter will be selected. Now on the Tools menu, choosing the command Parameter selection, run the parameter search function (Fig. 11, a) . Let's enter the search parameters:

· In field Set in cell let's enter an absolute reference to the 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 .

At the end of the function Parameter selection a window will appear on the screen Parameter selection result The in which the search results will be displayed. 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 is only changed until the required value is returned. The first argument found in this way is returned to us as a search result. If as

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

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

	Problem 17. Solve the equations
	5x/ 9- 8= 747x/ 12		(2x+ 2)/ 0.5= 6x
	0.5 (2x- 1)+x/ 3= 1/6		7(4x-6)+ 3(7-8x)= 1
	Linear system	equations		can be solved with 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 = 0

a3 x + a4 y + b2 =0

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

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

is written in the form

X=A-1 B,

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

Problem 18. Solve a system of linear equations

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

To solve the problem, 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 the system of equations.

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

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

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

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

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 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; staffing to achieve the best results at the lowest cost; minimizing the cost of transporting goods; achievement of the specified quality of the alloy; determination of the dimensions 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.

Solving problems of this kind can be done in EXCEL using the Solver 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, the solution of the problem must begin with the construction of an appropriate model. Let's take a look at these commands with an example.

Problem 20. Suppose that we decide to produce two types of lenses A and B. Type A lens consists of 3 lens components, type B - from 4. In a week, no more than 1800 lenses can be made. It takes 15 minutes to assemble a type A lens, 30 minutes for a type B lens. 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, and of type B - 4,800 rubles.

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

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

· Let's legally rename cell B3 to y .

target function Profit = 3500*x+4800*y enter in cell B5. · Picking costs are equal to =3*x+4*y enter in cell B7.

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

	Name
	complete set
	Cost over time

Fig.12. Filling the table with initial data

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

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

Example 3: Using the autofilter, 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 starts with , in the field opposite enter the desired letter (check that the layout is in Russian). Press OK.

4. Open dropdown list in a column group number.

5. Select the desired number.

Filtering records in a database with an advanced filter

Advanced filter allows you to search for rows using more complex criteria than custom autofilters. The advanced filter uses an interval of criteria to filter the data.

When using an advanced filter, the names of the columns on which conditions are specified are copied below the source table. The selection criteria are entered under the column names. After applying the 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 # 5433 whose GPA 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 (Figure 33) in which the data ranges are specified.

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 ​​selected.

In the input field Range of conditions an interval of cells on the worksheet is selected that contains the required criteria (C12:D13).

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

theories. 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 Only unique records is designed to display only non-repeating rows.

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, the 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 containing at least two criteria. Place all advanced filters on one sheet below the original table.

Rice. 35. Excel window with database Customer List

LAB #5

Numerical differentiation and simple analysis of functions

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

From the course of mathematics, it is known that the derivative formula in general looks like this:

f "(x)= lim
∆x0

where Δx is the increment of the argument; x is a number tending to zero. With the help of the derivative, you can determine the critical points of the function - minima, maxima or inflections. If the value of the derivative of a function for some value of x is equal to zero, then for this value of x the function has a critical point.

Example 1 : The function f x = x 2 + 2x 3 is defined on the interval x 5;5 . Explore 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 become the Greek letter ѓў. Alignment in a cell can be done to the right. In cell B1, enter the value 0.00001.

2. In cells from A2 to F2, arrange a header for the table, as shown in fig. 36.

3. Column A , starting from the third row, will contain 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 expand it to the final value x (up to the 13th line).

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

Rice. 36. Excel window with the study of the behavior of the 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 . Stretch the resulting value to the end value of the argument.

7. In cell E3, write the derivative formula (1), given 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 there is a critical point). To do this, you need to write a formula in cell F3 to determine the behavior of the function. The formula contains three conditions:

		f" (x)< 0	- the function is decreasing;
		f" (x) > 0	- the function increases;
		f"(x)=0	– there is a critical point* .

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

* 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 of the study of the behavior of a function

Tasks for independent work

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

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

LAB #6

Construction of a tangent to the graph of a function

Purpose of work: To master the calculation of the values ​​​​of 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 = f(x) at the point

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

Sequencing:

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

Rice. 38. Table of initial data

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

column A, starting from the third row (Fig. 38). If x 0 = 1, then cell A9 will act as x 0 . Accordingly, the value of the function f at the 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 ​​x 0, f (x 0) and f "(x 0) do not change. Therefore, in writing

To address 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 the 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 function graph at a given point.

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

1. Vedeneeva, E. A. Excel 2007 Functions and Formulas. User Library / E. A. Vedeneeva. - St. Petersburg: Peter, 2008. - 384 p.

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

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

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

How can Excel help in calculating the derivative of a function? If the function is given by an equation, then after analytical differentiation and obtaining a formula, Excel will help you quickly calculate the values ​​of the derivative for any argument values ​​\u200b\u200bof interest to the user.

If the function is obtained by practical measurements and given in tabular values, then Excel can provide more significant assistance in this case when performing numerical differentiation and subsequent processing and analysis of the results.

In practice, the problem of calculating the derivative by the method of numerical differentiation can also arise in mechanics (when determining the speed and acceleration of an object from the available measurements of the path and time) and in heat engineering (when calculating heat transfer over time). This may also be necessary, for example, when drilling wells to analyze the density of the soil layer passed by the drill, when solving a number of ballistic problems, etc.

A similar situation takes place in the "inverse" problem of calculating complexly loaded beams, when there is a desire to find the values ​​of the acting loads from deflections.

In the second part of the article, using a “live” example, we will consider the calculation of the derivative by the approximate formula for numerical differentiation using expressions in finite differences and understand the question - is it possible using approximations of derivatives by finite differences to determine the loads acting in the sections from the deflections of the beam?

Minimum theory.

The derivative determines the rate of change of a function that describes a process in time or space.

The limit of the ratio of a change at a function point to a change in a variable as the change in the variable tends to zero is called the derivative of a continuous function.

y ' (x) \u003d lim (Δy / Δx) at ∆x→0

The geometric meaning of the derivative of a function at a point is the tangent of the slope to the x-axis of the tangent to the graph of the function at that point.

tg (α)=Δy /Δx

If the function is discrete (tabular), then the approximate value of its derivative at a point is found using finite differences.

y' (x ) i ≈(Δy /Δx )i=(y i +1 -y i -1 )/(x i +1 -x i -1 )

Finite differences are called because they have a specific, measurable, finite value, in contrast to quantities tending to zero or infinity.

The table below presents a number of formulas that will be useful in the numerical differentiation of table functions.

Central difference formulas usually give more accurate results, but often they cannot be applied at the edges of the value ranges. For these cases, approximations by left and right finite differences are useful.

Calculation of the second order derivative using the example of calculating moments in beam sections from known deflections.

Given:

On a beam 8 meters long with hinged supports along the edges, made of two paired steel (St3) I-beams 30M, 7 runs are supported with a step of 1 meter. A platform with equipment is attached to the central part of the beam. Presumably, the force from the coating, transmitted through the girders to the beam, is the same at all points and is equal to F1. The suspended platform has a weight 2*F2 and is attached to the beam at two points.

It is assumed that the beam before the application of loads was absolutely straight, and after loading it is in the zone of elastic deformations.

The figure below shows the calculation scheme of the problem and the general view of the diagrams.

The following screenshot shows the original data.

Estimated initial data:

3. Running weight of I-beam 30M:

γ =50.2 kg/m

The beam section is made up of two I-beams:

n=2

Specific weight of the beam:

q \u003d γ * n * g \u003d 50.2 * 2 * 9.81 / 1000 \u003d 0.985 N / mm

5. Moment of inertia of I-beam section 30M:

I x1 =95,000,000 mm 4

The moment of inertia of the composite section of the beam:

I x \u003d I x 1 * n \u003d 95,000,000 * 2 \u003d 190,000,000 mm 4

10. Since the beam is loaded symmetrically about its middle, the reactions of both supports are the same and equal to each half of the total load:

R \u003d (q * z max + 8 * F 1 + 2 * F 2) / 2 \u003d (0.985 * 8000 + 8 * 9000 + 2 * 50000) / 2 \u003d 85 440 N

The calculation takes into account the self-weight of the beam!

Task:

Find Bending Moment Values Mxi in beam sections analytically by the formulas for the resistance of materials and by the method of numerical differentiation of the calculated deflection line. Compare and analyze the results obtained.

Decision:

The first thing we will do is calculate the shear forces in Excel. Q y, bending moments Mx, rotation angles U x beam and deflection axes V x according to the classical formulas of strength of materials in all sections with a step h. (Although, in principle, we will not need the values ​​of forces and angles in what follows.)

The calculation results are in cells I5-L54. The screenshot below shows half of the table, because the values ​​in the second part are mirrored or similar to the values ​​shown.

The formulas used in the calculations can be viewed.

So, we know the exact values ​​of moments and deflections.

We know from theory that:

The angle of rotation is the first derivative of the deflection U=V'.

Moment is the second derivative of deflection M=V''.

Force is the third derivative of deflection Q=V'''.

Let's assume that the column of exact deflections is not obtained by analytical calculations, but by measurements on a real beam, and we no longer have any other data. We calculate the second derivatives of the exact values ​​of the deflections using formula (6) from the table of the previous section of the article, and find the values ​​of the moments by the method of numerical differentiation.

M xi \u003d V y ’’ ≈ ((V i +1 -2 * V i + V i -1 ) / h 2) * E * I x

We see the result of the calculations in cells M5-M54.

The exact values ​​of the moments calculated by the analytical formulas of the strength of the material, taking into account the weight of the beam itself, differ slightly from those found by the approximate formulas for calculating the derivatives. The moments are determined very accurately, judging by the relative errors calculated as a percentage in cells N5-N54.

ε \u003d (M x -V y ’’) / M x * 100%

The task has been solved. We performed the calculation of the second derivative by an approximate formula using central finite differences and got an excellent result.

Knowing precise the values ​​of deflections can be found by numerical differentiation with high accuracy to find the moments acting in the sections and determine the degree of loading of the beam!

However...

Alas, one should not think that in practice easy to get necessary high-precision measurements of deflections of complexly loaded beams!

The fact is that deflection measurements must be performed with an accuracy of ~1 µm and try to minimize the measurement step h, "directing it to zero", although this may not help to avoid errors.

Often, a decrease in the measurement step with significant errors in deflection measurements can lead to absurd results. One must be very careful in numerical differentiation to avoid fatal errors.

Today there are devices - laser interferometers that provide high speed, stability and measurement accuracy up to 1 micron, programmatically filter out noise, and many other things that can be programmed, but their price is more than $ 300,000 ...

Let's see what happens if we simply round the exact deflection values ​​from our example to two decimal places - that is, to hundredths of a millimeter and recalculate the moments in sections using the same formula for calculating the derivative.

If earlier the maximum error did not exceed 0.7%, now (in the section i=4) exceeds 23%, although it remains acceptable in the most dangerous section ( ε 21=1,813%).

In addition to the considered numerical method for calculating derivatives using finite differences, it is possible (and often necessary) to apply another method - measurements with a power polynomial and find the derivatives analytically, and then compare the results obtained in different ways. But it should be understood that the differentiation of an approximating power polynomial is also ultimately an approximate method, which essentially depends on the degree of accuracy of the approximation.

The initial data - the results of measurements - in most cases, before being used in calculations, should be processed, removing values ​​that are out of the logical series.

The calculation of the derivative by numerical methods must always be done very carefully!

Dear readers, please place reviews and comments on the article in a special block below the article.

To receive information about the release of new articles on the blog, subscribe to announcements in the window located at the top of the page or immediately after the article.

ask RESPECTING the work of the author download a file with an example AFTER SUBSCRIPTION for article announcements.