Graphic differentiation. Graphic and numerical differentiation Find out how to do it in excel
Crimean formatting of the elements of the middle field, rows and stovptsiv often happens to be a coronal vicoristovuvaty a sprat of working excel arches. To systematize and search for information in the book, manually enter the names of the arkush in the power of the names, which reflect their zmistovy zmist. For example, “output data”, “results of surveys”, “graphs” just. Tse zrobiti manually, huddled context menu. Right-click the bear button on the arkush shortcut, Rename the arkush and press
To add one or more new arches from the Insert menu, select the Sheet command. To insert in a row a sprat of arches, it is necessary to see the labels of the necessary number of arches, emphasizing
Korisna operation for moving arches - with the left button of the mouse, grab the label of the sheet and move it in the right place. Yakshcho with tsioma to squeeze
Task 7 . Change the format of the commercial B2 to: font - Arial 11; roztashuvannya - at the center, along the lower edge; one word in a row; number format - "0.00"; between the middle - the underline
2.3. Introduced functions
The Excel program has over 150 built-in functions for simplifying rozrachunkiv and data processing. The butt instead of the middle with the function: \u003d B2 + SIN (C7) , de B2 and C7 are the addresses of the middle, which will replace the numbers, and SIN () is the name of the function. Most popular Excel functions:
SQRT(25) = 5 - Calculate the square root of the number (25) RADIANI(30) = 0.5 - Convert 30 degrees per radian QILOE(8.7) = 8 - Round to the nearest smaller integer ZASTAT(-3;2) = 1 – leave a surplus in the subdivision of the number (-3) on
dilnik(2). The result is a sign of delnika. YAKSHO(E4>0,2; "additional"; "pardoned")- so the number in the middle of E4 is less than 0.2,
then Excel rotate "additional" (true), otherwise - "pardon" (nonsense).
For formulas, functions can be nested one into one, but not more than 8 times.
From the selection of functions to the main ones, the assignment of the function itself and the її of the argument. As an argument, as a rule, the addresses of the commissary are indicated, in which the information is written.
You can select the function by typing the text (icons, numbers, etc.) in the required middle, or speed up Master of functions. Here, for the sake of clarity, I will ask for all the functions divided into categories: mathematical, statistical, logical and others. In the skin category, stinks are sorted in alphabetical order.
Function Master called by menu command Insert, function
or the pressure of pictograms (f x). For the first time, the Master of Functions (small 4) is assigned the category and the name of the specific function that is being pressed
Rice. 4. Look at the window Meister of functions
Rice. 5. Window for specifying the arguments of the selected function
Task 8. Know the average value of low numbers: 2.5; 2.9; 1.8; 3.4; 6.1;
1,0; 4,4.
Solution. We enter the numbers in the closets, for example, C2: C8. We see the C9 commissure, in the way we write the function = AVERAGE (C2: C8), we press
Task 9. Zastosovuyuchi mental logical function YAKSHO, add the formula for renaming unpaired numbers to "autumn", paired numbers - "spring".
Solution. We select a column for entering weekend data - paired (unpaired) numbers, for example, A. In the middle of B3 we write the formula =YAKSHO(OSTAT(A3,2)=0,"vaga","osi"). Kopyuchi oseredok B3 vzdovzh stovptsya, otrimuemo results of the analysis of the numbers recorded in column A. The results of the development of the task are presented in fig. 6.
Rice. 6. Solving problems No. 9
Manager 10. Calculate the value of the function y = x3 + sinx - 4ex for x = 1.58.
Solution. We place data in the middle A2 - x, B2 - y. The solution of the problem is shown in Fig. 7 for the numerical view and for the formula one - on the right. When rozv'yazannі tsієї tasks sіlіd brіd brіd avіdut vіklіkі functions SIN і eksponenti і for vіddeniya argument (div. mal.8).
Fig.7. Solving problems No. 10
Fig.8. A window for entering the function argument SIN and EXP
Head office 11 . Store in Excel the mathematical model of the problem for the expansion of the function y = 1/((x-3) (x + 4)), with the values x = 3 and y = -4, display "undisplayed" on the screen, the numerical values of the function - in other cases .
Manager 12 . Store the mathematical model of the problem in Excel: 12.1. for calculation from 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) designate the cuti of a straight-cut tricot, as in x - leg, y - hypotenuse;
b) find the difference between two points in the Cartesian coordinate system XYZ according to the formula
d = (x2 − x1 )2 + (y2 − y1 )2 + (z2 − z1 )2
с) calculate from the point (x 0 , y 0 ) to the straight line a x + b y + c = 0 according to the formula
d = a x0 + b y0 + c / √ (a2 + b2)
d) assign the area of the tricoutnik to the coordinates of the vertices according to the formula
S = 1 2 [ (x1 − x3 )(y2 − y3 ) − (x2 − x3 )(y1 − y3 )]
3. Development of problems from the development of formulas and functions
For sure, if you can successfully use different formulas and Excel functions, it’s really rich. Let's take a look at the tasks, which in practice most often diverge from the spread of electronic tables: the linear alignment of that system, the calculation of the numerical values of similar and singful integrals.
An incremental function y = f(x) is called an incremental increment ∆y to a variable increment ∆x to the argument, if
∆x→ 0
y = f(x + x) − f(x)
Zavdannya .13. Find a random function y = 2x3 + x2 for point x=3.
Solution. Pokhіdna, calculated by the analytical method, cost 60 . Calculation of the cost of Excel will be carried out according to the formula (1). For whom we need the following sequence:
· Let's determine the values: X - function arguments, Y - function values, Y - random functions (Fig. 9).
· Tabulate the function on the periphery of the point x \u003d 3 with a small croc, for example, 0.001, the results are entered in the column X.
Rice. 9. Table of calculation of similar functions
· In the room B2, we introduce the formula for calculating the function = 2 * A2 ^ 3 + A2 ^ 2 .
· Copy the formula to the row 7 take the value of the function at the tabulation points of the argument.
· In room C2, we introduce the formula for the calculation of expenses = (B3-B2) / (A3-A2).
· Copy the formula to the row 6 take values similar to the argument tabulation points.
For the value x = 3, the function is similar to the value 60.019, which is close to the value calculated analytically.
trapezium method. In the method of trapezium, the area of integration is divided into cuts with a deakim croc and the area under the graph of the function on the skin cut is taken into account by a flat area of the trapezium. Todi rozrahun's formula looks like this
S N = ∫ f (u) du ≈ h N ∑ − 1 [ f (a + h i) + f (a + h (i + 1)) ] (2), |
|
2 i = 0 |
|
de h \u003d (b-a) / N - crochet; N - the number of breaking points.
To improve the accuracy, the number of split points is subdivided, and the integral is re-drawn. The splitting of the output interval is to be pinned, if the necessary accuracy is achieved:
іntegrа vіkonаєmo so dії:
- Vibermo N = 5, in the middle of F2, h-rozbitya is cut (Fig. 10);
Rice. 10. Calculation of the sing integral
· At the first stop And write down the number of interval i;
· In the middle of B2, we write the formula =3 * (2 + F2 * A2) ^ 2 for the first dodanku formula (2);
· In the middle of C2, we write down the formula =3 * (2 + F2 * (A2 + 1)) ^ 2 for another dodanku;
· "Stretchy" middles with formulas on 4 rows down with stitches;
· In the middle of C7 we write down the formula and calculate the sum of the additions,
At the middle of C8 we write down the formula i calculable SN shukane the value of the sing integral 19.02 (the value of S N otrimane analytically
19).
Manager. 15. Calculate the singing 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. Razv'yazannya linear rivnyan
Linear alignment in Excel you can use the victorious function Select a parameter. When you select the parameter, the value of the room, which is added (parameter) is changed to the next, until the formula, which should be deposited in the value of the room, does not change the given value.
Let's take a look at the procedure for looking for a parameter on a simple example of the development of a linear alignment with one unknown.
Manager 16 . Razvyazati equalization 10 x-10/x = 15 .
Solution. For a random value of the parameter – x, select the A3 box. Let's introduce a number in the middle, which lies in the area of the designated function (in our application, the number cannot be zero). Let it be 3 . Tse meaning will vikoristano like cob. For example, B3, for example, before the induced alignment, we introduce the formula =10*A3-10/A3 . As a result of a series of calculations, the value of the parameter will be selected by the formula. Now at the Service menu, by issuing the command Parameter selection, let's run the function by looking for a parameter (Fig. 11 a). Let's enter parameters and a joke:
· At the floor Put it in the middle we will introduce an absolute charge on the commission of $ $ 3, which will avenge the formula.
· For the Value field, enter the result 15 .
· At the floor Changing the meaning of the middle let's put it on the A3 commissary, what to avenge the meaning, what to pick up, and press
After the end of the work function Parameter selection appear on the screen Result of parameter selection, in which the results of the search are shown. Finding the parameter 2.000025 to appear in the middle of A3, which is the name for the new reservation.
Give respect to the fact that in the application there are two solutions, and the parameter of selection is only one. It is worth considering that the parameter is changed less until now, until the required value is turned. The first knowledge of such a rank is an argument and turns to us as the result of a joke. Yakscho yak
cob value at the butt indicate -3, then another solution will be found: -0.5.
Fig.11. Solution of alignment: a - introduction of data, b - result of opening
Task 17 |
||||
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 |
|||
Line system |
rivnyan |
you can change your mind |
||
ways: substations, folding and vіdnіmannya rіvnyan, with different matrices. Let's take a look at the way of deriving the canonical system of linear alignments (3) from different matrices.
a1 x + a2 y + b1 = 0
a3 x + a4 y + b2 =0
As you can see, the system of linear alignments of the matrix data is recorded in the following way:
de A - matrix of coefficients, X - vector - stovpets nevіdomih,
B is a vector of all members. The perfection of such a system
sign up at the sight
X=A-1 B,
de A -1 -matrix, reversible to A . It is obvious that when splitting the matrix equals for X, the single matrix E is to blame. Multiplying the evil parts of the equalization AX = by A -1, we take the decision of the linear equalization system.
Task 18. Untie the system of linear lines
Solution. For a given system of linear alignments, the values of the corresponding matrices and the vector-stations may look like:
To complete the task, it is necessary to do:
A2: B3 i will be written in the new element of the matrix A.
· We see a block of middle ones, for example,С2:С3 i will be written in the new element of the matrix.
· We see a block of middle ones, for example, D2:D3 for arranging the result of the deployment of the alignment system.
· In the room D2, we introduce the formula = MULTIPLE (MOBR (A2: B3); C2: C3).
The Excel library in the distribution of mathematical functions to replace the functions of the operation on matrices. Zocrema, its functions:
Parameters of these functions can be addressing the array to replace the values of the matrix or the names of the ranges and types.
For example, MOBR (A1: B2) or MOBR (matrix_1).
· Let's say Excel, which operation on arrays is to be used, for which to press a combination of keys
4. Decoupling of optimization problems
A lot of forecasting problems, designing and shaping up to a wide class of optimization problems. Such tasks are, for example: maximization of the release of goods with the exchange of syrovine for these goods; folding staffing to achieve the best results for the lowest costs; minimization of expenses for the transportation of goods; attainment of the assigned metal quality; designation of the expansion of the singing capacity with the improvement of the varity of the material for reaching the maximum commitment; different
the task, up to which values are included, and the other task of the optimal allocation of resources and optimal design.
Solving tasks of this type can be created in EXCEL for the help of the Solution Search tool, which is available in the Tools menu. The formulation of such orders can be made by the system equal to the decals of the nevidomimi and the collection of the exchange for the decision. Therefore, the development of the problem needs to be started from a prompt model. Known by these commands on the butt.
Problem 20. Let's assume that we have seen two types of objects A and B. The object of the view A is composed of 3 lens components, the view - of 4. You can make more than 1800 lenses in a day. For the folding of the lens, mind A needs 15 quilins, and 30 quilins for the species. Working day for 4 warehouse workers 160 years. Skilki ob'ektiviv A and B are required to prepare in order to take the maximum profit, so that the object of the mind A costs 3500 rubles, the mind - 4800 rubles.
Solution. To complete this task, it is necessary to add and store the table correctly up to fig. 12:
· Change the middle B2 x , the number of objects in the mind A.
· And podatkovo change the middle B3 in y.
Purpose function Surplus \u003d 3500 * x + 4800 * y let's put it into room B5. · Cost of equipment equals =3 * x + 4 * y is entered in B7.
· Vtrati per hour equal = 0.25 * x + 0.5 * y is entered in B8.
name |
|||
complete set |
|||
Vitrati by the hour |
|||
Fig.12. Filling tables with output data
· You can see the room B5 and select the Dani menu, after which we activate the Search solution command. It is worth remembering that the middle part of this vein is clear until Fig.13.
· Natisnemo<Выполнить >; if everything is done correctly, then the solution will be the same as it is indicated below.
Example 3: Choose students for help autofilter, if they are enrolled in group No. 5433 with a nickname that starts with the letter C.
Sequence diy
1. Copy the data base (Fig. 30) to Arkush 3.
2. Call.
3. Select an item from the listText filters → Filter settings. At the vikni, what happened Koristuvalnicky autofilter Select the selection criteria based on s, in the field, enter the required letter in the field (verify that the layout was Russian). Press OK.
4. Open the list that is being developed, in the column group number.
5. Pick a number.
Filtering entries in the database for the help of an extended filter
Filter Extensions allows you to see rows for additional folding criteria, in pairs with corystuvalny autofilters. Wicker filter extensions for filtering these criteria intervals.
When choosing an expanded filter, the names of the stovptsiv, for which the mind is asked, are copied below for the output table. Under the names of the stovptsiv, the selection criteria are entered. After the filter is stopped on the screen, those rows can be displayed, as if they satisfy the designated criterion, and also, after filtering, the data can be copied to a different archway or to a different region on the same working arch.
Example 4: Select all students from group No. 5433, who have a higher average score of 4.5.
Sequence diy
1. Copy the data base (Fig. 30) to Arkush 4.
2. Copy the names of the participants Group number and average score
to the area below the exit table. Under the names of the stovptsіv, you need to specify the necessary selection criteria (Fig. 32)
Rice. 32. Excel window with advanced filter
2. On the Data tab on the Sorting toolbar
and select the Dodatkovo item in the filter. The dialogue will appear (Mal. 33), de show the range of data.
Rice. 33. Extended filter window
At the introduction field Outdoor range Specify the interval to avenge the loss of the data base. At times, the range of the middle ones from A1 to I9 is seen.
At the introduction field range of minds there is an interval in the middle on the working arc, which is to meet the necessary criteria (C12: D13).
For input field Save result for range indicate the interval in which the rows are copied, which satisfy the cry-
losses. In times it is indicated in the middle below the area of criteria, for example A16. This field is no longer available in that view, if the switch is selected Copy the result in another place.
Praporets Only unique entries appointments for the selection of rows, which only do not repeat.
The resulting table, in accordance with the filtering criteria, is shown in Fig. 34.
Rice. 34. Excel window with filter results
1. Create your own data base, the number of records for each may be at least 15, and the number of entries - at least 6. For example, the database List of clients (Fig. 35).
2. Three auto-filters (on okremih arches) are blocked before the base of the data. The number of criteria can be no less than two.
3. Zastosuvat three expanded filters to records the data base, skin of which is guilty of at least two criteria. Use expanded filters on one archway under the output table.
Rice. 35. Excel window with data base Client List
Laboratory robot number 5
Numerical differentiation and the simplest analysis of functions
Meta work: Follow the function to the extremum, learn to find the critical point.
From the course of mathematics, it is clear that the formula looks like this:
f "(x) = lim |
||
∆x0 |
||
de Δx - zbіlshennya argument; x is a number that is equal to zero. For help, it is possible to determine the critical points of the function - minimums, maxima and overshoots. If the value of a similar function for any value of x is equal to zero, then for any value of x the function may have a critical point.
Example 1: The function f x = x 2 + 2x3 is set on the interval x5; 5 . Follow the behavior of the function f(x).
Sequence diy
1. Let Δx = 0.00001. At roommate A1, send: šDx=Ÿ (Fig. 36). See the letter D, right-click on the seen letter, select Format of the middle. On the Font tab, select the Symbol font. Letter D to transform into the Greek letter Ґв. Virivnyuvannya in the middle can be robiti right-handed. Enter the value 0.00001 for B1.
2. In the middle of A2 to F2, make a table header, as shown in fig. 36.
3. Setting A, starting from the third row, the value of x will be replaced. For room from A3 to A13, enter the value -5 to 5.
4. In the middle of B3 write the formula =A3^2+2*A3-3 and expand її to the end value x (up to the 13th row).
5. In order to calculate a random function and calculate its value on a given interval, it is necessary to work an inter-
exact calculation. Enter the summation formula for the argument x and the th ogo increase Δx in the room C3. The formula looks like: =A3+$B$1. Expand її the value of the end value of the argument x .
Rice. 36. Excel window showing function behavior
6. Write down the formula =C3^2+2*C3-3 in the room D3, for which the value of the function f is calculated as the argument x Δx. Stretch the value to the final value of the argument.
7. Write down the following formula (1) for the room E3, versing that the value of f x is in B3, and the value of f x + Δx is in D3.
Matime formula looks like: =(D3-B3)/$B$1.
8. Change the behavior of the function for a given interval (increase, change, and critical point). For which it is necessary to independently write down the formula for the behavior of the function in the F3 cell. The formula to revenge three minds:
f" (x)< 0 |
– the function changes; |
||
f" (x) > 0 |
- The function is growing; |
||
f" (x) = 0 |
– є critical point*. |
9. Look at the graphs for the values f x і f "(x). The graph (Fig. 37) shows that even though the value of a similar function is equal to zero, then here the function has a critical point.
* After a great mistake, calculate, the value of f "(x) can be equal to 0. But it is necessary to describe this situation all the same.
Rice. 37. Diagram of follow-up behavior of the function
Task for independent work
The function f(x) is defined on the interval x. Follow the behavior of the function f(x). Encourage graphics.
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 |
|||||||||
Laboratory robot number 6
Pobudov dotichny graphic function
Meta work : Learn the calculation of the value of the equal to the graph of the function at the point x 0 .
Alignment of the dot to the graph of the function y = f(x) at the point
Example 1: The function y = x 2 + 2x3 is set on the interval x [5; 5]. Encourage a dotistic graph of the function at the point x 0 = 1.
Sequence dіy:
1. Numerically differentiate this function (div. Laboratory robot No. 5). The output data table is shown in fig. 38.
Rice. 38. Weekly data table
2. Calculate x, x 0, f (x 0 ) and f "(x 0) in the tables. It is obvious that x
Stovptsya A, starting from the third row (Fig. 38). If x 0 = 1, then if x 0 is the middle point A9 . Obviously, the value of the function f at the point x 0 is in the middle of B9 and the value f "(x 0 )
- In the middle of E9.
3. Set up F to ensure that the graph of the function f(x) is equal. When expanding the alignment (1), it is necessary that the values of x 0 , f(x 0 ) and f" (x 0 ) are not changed.
The middle addresses of A9, B9 and E9 need to be chosen absolutely on the middle number. Fixation of the middle to go for an additional sign š$Ÿ. The middle ones look like: $A$9 , $B$9 and $E$9 .
Rice. 39. The graph of the function f(x) is dotichny to the graph at the point x=1
Task for independent work
The function f(x) is assigned to the interval x . Razrahuvati dotichny. Encourage me to reach the graph of the function at the 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. Vєdєnєєva, Y. A. Functions and Formulas of Excel 2007. Koristuvach's Library / Є. A. Vedєnєeva. - St. Petersburg: Peter, 2008. - 384 p.
2. Sviridova, M. Yu. Excel spreadsheets/M. Y. Sviridova. - M.: Academia, 2008. - 144 p.
3. Serogodsky, V. V. Graphs, calculation and analysis of data
in Excel 2007 / V. V. Sirogodsky, R. G. Prokdi, D. A. Kozlov, A. Yu. Druzhinin. - M.: Science and technology, 2009. - 336 p.
For the completion of rich engineering jobs, it is often necessary to calculate the cost of the latter. If there is a formula that describes the process, there are no folds: we take the formula that calculates the cost, like in a school at school, the value is significant at different points, and that's it. Skladnіst, mabut, only in the same field, to guess, how to count the good ones. And how buti, how do we have only a few hundreds of thousands of rows of danim, but there are no other formulas? Most of all, it is so practical and buvae. I offer two ways.
The first one lies in the fact that our collection of points is approximated by a standard Excel function, so that we choose a function, as the best way to fall on our points (in Excel, a whole linear function, logarithmic, exponential, polynomial and static). Another way is numerical differentiation, for which we will need to use the formulas.
Guess what is so funny:
The incremental function f (x) at point x is called the interim increase Δf of the function at point x to increment Δx to the argument, if the remainder remains to zero:
Axis and we will speed up our knowledge: we will simply take up the rozrahunka of a similar argument, even if the value of the argument is small. Δx.
In order to know the approximate value of a similar one at the points we need (and our points are the only values of the degree of deformation ε), we can find the axis yak. Let's look once again at the value of the similarity and Bachimo, that when the value of the increase in the argument Δε is small (then the increment of the degree of deformation is small, which is recorded during testing) it is possible to replace the value of the real value at the point x 0 (f'' x 0)) to the reference Δy / Δx = (f (x 0 + Δx) - f (x 0)) / Δx.
Tobto axis scho to enter:
f′(x 0) ≈(f (x 0 + Δx) – f (x 0))/Δx (1)
For the calculation of the value of the same in the skin points, it is necessary to calculate the number of different points of two suicidal points: the first with the coordinate 0 along the horizontal axis, and the other with the coordinate x 0 + Δx, tobto. one - I’ll go to the next one, and the one that is better. Charged with such a rank, it’s bad to be called retail pokhіdnoy to the right (forward) with a cropΔ x.
We can act in the same way, taking the other two main points: x 0 - Δx і x 0, so that the cicava for us and the one that is left-handed. Take the formula for the calculation retail outlet to the left (back) with croqueΔ x.
f'(x 0) ≈(f (x 0) - f (x 0 - Δx)) / Δx (2)
The forward formulas are “left” and “right”, and there is one more formula that allows you to calculate the central retail store with crop 2 Δx, ta yak most often victorious for numerical differentiation:
f'(x 0) ≈(f (x 0 + Δx) - f (x 0 - Δx))/2Δx (3)
To rewrite the formula, let's look at a simple example from the given function y=x3. We will create a table in Excel with two entries: x and y, and then we will create a graph for the actual points.
Pokhіdna funkії y=x 3 tse y=3x 2 graph that way. parabola, we are to blame for the variations of our formulas.
Let's try to calculate the value of the central cost difference at points x. For whom. In the middle of another row of our table, we hammer in the formula (3), so. I'll come up with the formula in Excel:
Now there will be a graph with the already existing values of x and subtracting the value of the central variable:
And the i-axis is our little red parabola! Get out, the formula works!
Well, now we can move on to a specific engineering task, they talked about it on the cob of statistics - the importance of changing dσ / dε for greater deformation. The first one is similar to the “stress-strain” curve σ = f (ε) in foreign literature is called “strain hardening rate”, and ours is “coefficient of stress”. Later, as a result, we tested an array of data, which consists of two steps: one - with the values of deformation ε and the second - with the values of the stress in MPa. We take cold deformation of steel 1035 or 40G (div. 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 |
The axis of our curve is in the coordinates "correct stress - correct strain" σ-ε:
It’s the same way, like in the front butt and we take such a curve:
Tse i є zmіna shvidkostі zmіtsnennya in the process of deformation. Why work with her, why don’t you eat more.
How can you help Excel calculate the cost of useful functions? If the function is given to equals, then after the analytical differentiation, the Excel formulas will help you quickly sort out the values similar to any argument that chirps the koristuvach.
Since the function is taken away by practical variables and given by tabular values, Excel can give you more help in the case of numerical differentiation and further analysis of the results.
In practice, the calculation of the cost by the method of numerical differentiation can be blamed in the mechanics (if the speed of the object is determined to be faster for the obvious winds, the way to that hour) and heat engineering (with the expansion of heat transfer per hour). It may also be necessary, for example, when drilling sverdlovin for analysis of the thickness of the ball to the ground, which should pass through the drill, when executing a whole series of ballistic tasks, and so on.
The situation is similar in the case of the “reversible” task of rozrahunka smoothly navantazhenih beams, if behind the bends it is necessary to know the value of the navantage, what to breathe.
In the other part of the statistics on the “live” butt, it is clear that the calculation is similar to the approximate formula for numerical differentiation from the susceptibility of viruses in the final retailers and the calculation in nutrition - chi can vicorist similar to similar kіntsevy retails Behind the troughs of the beams, there is an emphasis on what is to be found at the troughs?
Minimum theory.
Pohіdna vznachaє shvidkіst zmіni funktsії, scho describ whether it's a process at the hour or space.
Between changes in the point of function of change of change in case of normal change of change to zero, it is called a similar uninterrupted function.
y'(x)=lim(Δy /Δx) at ∆x→0
The geometrical sense of the similar function at the point is the ce tangent of the cut up to the x axis, which is similar to the graph of the function at the point.
tg (α) = ∆y /∆x
As the function is discrete (tabular), the approximate value of the similar value is known in point for the help of the final differences.
y′ (x ) i ≈(Δy /Δx )i=(y i +1 -y i -1 )/(x i +1 -x i -1 )
Kіntsevy ryznica is called that which stinks can be more specific, vimiryans, kіtsev znachennya on vіdmіnu vіd values, scho pragvat zero or neskіnchennostі.
The tables below the representations have a number of formulas, which will become useful in case of numerical differentiation of table functions.
Center-to-weight formulas usually give more accurate results, but often cannot be stuck at the edges of the value ranges. For these vipadkіv znadoblyazhennya left and right kіntsevyh raznitsy.
The calculation is similar to a different order on the butt of the rozrahunka of moments at the beam cuts behind the girths.
Given:
On a beam with a length of 8 meters, with hinged supports along the edges, two paired steel (St3) double-beams 30M are spiraled 7 runs with a crochet of 1 meter. To the central part of the beam, there is a maidanchik from the arms. Imovirno zusillya vіd pokrittya, scho is transmitted through the drive to the beam, at all points the same F1. Suspended maidanchik maє vag 2*F2 and fasten to the beam at two points.
It turns out that the beam was absolutely straight before the tension was set, and then the tension was in the zone of spring deformations.
The little one below shows the Rozrakhun's scheme of the manager and the flashy looking diagrams.
External data is presented on the next screen.
Rosrakhunkov's output data:
3. Linear weight of a double tee 30M:
γ =50.2 kg/m
The span of the beam is stacked from two double-beams:
n=2
Pitoma vaga beams:
q \u003d γ * n * g \u003d 50.2 * 2 * 9.81 / 1000 \u003d 0.985 N / mm
5. Moment of inertia of the double tee 30M:
I x1 = 95000000 mm 4
Moment of inertia of a folding beam section:
I x \u003d I x 1 * n \u003d 95,000,000 * 2 \u003d 190,000,000 mm 4
10. The beam oscillators are wound symmetrically according to their middle, the reactions of both supports are the same and even skin halves of the total stress:
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 rozrahunka is insured by the power of the beams!
Manager:
Know the meaning of the original moment Mxi at the beam cuts, analytically for the formulas, the support of the materials is supported by the method of numerical differentiation of the rozrachunk line of the progins. Compare and analyze the results.
Solution:
First, what we can do, why we need to remove the spread of the Excel transverse forces Q y, final moments M x, turn around U x axes of beams and progins V x according to the classical formulas of strength of materials in all cross-sections h. (Wishing, in principle, the significance of the forces of that kutіv we should not need.)
The results are calculated by rebuying at the middles of I5-L54. The screenshot below shows half of the table, the values of the other half of the table are mirrored or similar to the presented values.
You can marvel at the vikoristani in the rozrahunka formulas.
Otzhe, we know the exact meaning of the moment and progress.
From the theory we know that:
Kut turn - tse persha is like a bend U=V'.
Moment - tse friend is like a trough M=V''.
Strength is the third thing like a bend Q=V'''.
Let's assume that the exact values of the prognosis were taken not by analytical surveys, but by the real world, and we can't get more than any other data. Let's calculate other similar values of the exact values of prognosis, using the formula (6) of the table of the front section of the article, and find the value of the moment by the method of numerical differentiation.
M xi =V y '′ ≈((V i +1 -2*V i +V i -1 )/h 2)*E *I x
Podsumok rozrahunkiv mi bachimo in the middle M5-M54.
The exact values of the moments, calculated for the analytical formulas of the supromat with the improvement of the beam itself, are discussed in the light of the known formulas for calculating the similar ones vaguely. The moments were chosen for sure, judging by the visible streaks, razrahovanyh in vіdsotkakh in the middle N5-N54.
ε =(M x -V y '' )/M x *100%
The task has been completed. Mi vikonals of the calculation of a similar order to the approximate formula for the victories of the end of the end of the cost and took away the final result.
Knowing exactly the value of the deflection can be known with high accuracy by the method of numerical differentiation with a high accuracy in terms of the moment and the degree of the beam's vanity!
Prote...
It's a pity, don't think about it, what's in practice easy to take the necessary high-current results of vimiryuvan bending in folding beams!
On the right, in that it is necessary to measure the bending with an accuracy of ~ 1 micron and to maximize the change in the bending curve. h, "Leading yoga to zero," if you want to help me get pardons.
Most of the time, changing the timing of deaths with significant abductions of deaths can lead to absurd results. Be sure to respect the numerical differentiation, so that fatal pardons are avoided.
Suitable accessories - laser interferometry, which ensure high speed, stability and accuracy of measurement up to 1 micron, programmatically control noise, and a lot of things programmatically include, but their price is more than $ 300,000.
Let's wonder what it will become, as we simply round off the exact value of the bends from our butt to two signs after the Komi - that's up to hundreds of fractions of a millimeter and again for the same formula for calculating the exact moment at the cuts.
As before, the maximum pardon did not exceed 0.7%, then at once (for i\u003d 4) exceeding 23%, even though it is more acceptable in the most unsafe situation ( ε 21=1,813%).
With the help of the numerical method of calculation of the last ones for the help of the end of the century, it is possible (and often it is necessary) to catch another way - freeze with a rich rich term and know the better analytically, and then return the results, take away the different ways. Ale slіd razumiti, scho differentiation approximating static polynomial - tezh zreshtoj naproklady sposіb, scho stochno lie in the degree of accuracy of approximation.
The last data - the results of the vimiryuvan - in the most vipadkіv in front of the victories at the rozrahunka, they were able to work out, seeing the meaning that vibrates from the logical row.
Calculation by random numerical methods must be done carefully!
New readers, comments and comments before the article, place it in the special block below the article.
To take a look at information about the release of new articles on the blog, subscribe to the announcements in the blog, rosted in the mountains, or once again after the article.
ask shanovnykh the work of the author to capture the file with the butt PISLA OVERPAYMENT on new articles.
Graphical differentiation is started from prompting the graph of the function for the given values. With an experimental follow-up, such a schedule is taken off with the help of self-written fittings. Further, dotic to the curve at fixed positions and calculate the values similar to the tangent of the kuta, fixed to the dot from the abscissa.
On fig. 5.8, a the curve is shown experimentally on the setup (Fig. 5.6). The purpose of the apical acceleration (shukan function) is to carry out graphical differentiation for spivdenshennia:
(5.19)
The tangent of the cut is slightly dotichno to the curve in the deakіy point i to present at the sight of the vіdrіzkіv, de Before- Vibranium vіdrіzok іntegruvannya (Fig. 5.8, b)
After the substitution of which spіvvіdnoshennia at spіvvіdnoshennia (5.19) wins
de is the ordinate of the call graph of the apex acceleration;
Zoom chart scale; units CI: = mm; \u003d mm / (radium s -2).
The graph of the function will follow the known values of the ordinates for a number of positions. The speckles on the curves run into the hands in a smooth line, and then we circle the patterns for help.
Let's take a look at graphically differentiation by the method of dot- ic maє visibly low accuracy. Greater accuracy is achieved with graphical differentiation by the chord method (Fig. 5.8, inі G).
 |
On a given curve, a series of points are assigned 1 ", 2 ", 3" , which zadnuyut chords, tobto. replace the given curve with a laman line. Take the following priymayutnya: kutu nahily dotichnyh at the points, ruffled in the middle of the skin patches of crooked, old kutu nachily vydpovidnoy chords. Tse pripuschennya to make a pepna pohibka, but it’s not enough to lay down to the point. Qi pohibki do not hesitate, which will ensure the accuracy of the method.
Other reasons similar to those described earlier in graphical differentiation by the dotistic method. Choose the ribs (mm); spend promenі, nachilenі pіd kutami 
to the crossbar from the entire line of ordinates at the points 1
", 2
", 3
... , to be transferred to the ordinates, carried out in the middle of the skin intervals. 1
*, 2
*, 3
* є points shukanoї functions 
.
The scales along the coordinate axes with which method should be related to such very spіvvіdnosheniya (5.21), as it was shown for vipadki graphic differentiation by the method of dotichnyh.
Function differentiation f(x), given (calculated) as an array of numbers, to be drawn by the way of numerical differentiation from zastosuvannyam EOM.
The smaller the value of the array of numbers, the more precisely you can calculate the values of similar functions for this interval
