Models and technologies for numerical solution of problems
Modeling and Exploring Functions
There are various ways of specifying functions: analytically, as a graph and as a table. With experimental measurements, in banking, accounting, etc. a tabular method of specifying functions is often used. In the tabular representation, one of the variables is always independent, and the remaining variables are the values of the functions at the points of the independent variable.
Using a formula to describe the relationship between arguments and function values is called an analytical way of specifying a function, for example y = x 2 .
Build a function graphical model . Graphical way of representation of functions allows to visually assess the nature of the phenomenon being studied, to reveal the dynamics of changes and trends of development, etc.
Example 9.6. Consider the graphical model of the function by constructing the appropriate graph for the analytically defined function y = 3 x 3 + 2x 2 - x + 1 on [-2; 2].
Create headers for ranges: arguments and function values at the points of the argument.
Let's construct the range of the arguments from -2 to 2 in increments of 0.4.
Create a range for the y value for each of these points.
Select the areas of the arguments and their values along with the headers and select the Insert tab on the toolbar.
Select Chart in the Diagrams group and draw the corresponding graph (Figure 9.22).
Calculating the limit of a function. To find the limit of a function, for example at x = 2, you need to do the following:
• in the column, in different rows, enter fairly close values to point 2, left and right;
• Calculate the values of the function at these points (Figure 9.23),
• find the difference of the received values of the function. The obtained values of the function for neighborhoods of the point 2
are equal, which is confirmed by their difference, which is equal to 0.00. This means that the limit of the function at point 2 exists and is -1.00.
Fig. 9.22. The graphical function model y = 3 x 3 + x 2 - x + 1
Fig. 9.23. Finding a function limit
Calculating the roots of a function of one variable. The root of the function y = f ( x ) is the point at which the function takes on a value of zero. We use the technology of finding the roots of a function on a segment using a spreadsheet.
Example 9.7. The y = x 2 - x function is set to +0.1, it is required to find the roots of the equation on to the interval [-2; 2].
The function is represented by a polynomial of the second degree and has no more than two roots. By constructing a range of arguments and function values in argument points, you can define ranges of arguments in which the function changes sign, i.e. crosses the axis of abscissas. From the obtained region it is obvious that the function intersects the axis of abscissas twice, hence all the roots of the equation are contained in the required interval. We copy the values (bunks, argument and value of the function) to (0; 0,1) and (0,8; -0,06) into a separate area. Preliminary adjusting the parameters of the relative error of calculations (0.00001), we call the tool "Parameter selection". Alternately, for each pair, perform the following actions:
• In the Set in the cell box, specify the address of the cell where the value of the argument is located;
• In the Value field, enter the number 0;
• In the Changing cell value field, we'll specify a reference to the cell containing the value of the function (see Figure 9.24).
The solution is x = 0.11, where y = 0.
Fig. 9.24. Using the Selection Tool for the to find the roots of the equation
Calculation of the derivative of a function. The finite difference formulas can be used to calculate the derivative at a given point by numerical approximate methods. The corresponding expression for finding the derivative has the form
Using sufficiently small increments x gives acceptable accuracy in the calculation of the derivative. We use this principle to calculate the derivative in a spreadsheet.
Example 9.8. Given the function y = 2x 2 + x, you want to find the derivative at the point x = 2 (the analytical solution yields the result 9).
Let's construct the range of approximations at the point x = 2.
Let's calculate the value of the function at these points.
Apply the formula for calculating the derivative, as shown in Fig. 9.25.
Fig. 9.25. Computation of a derivative
Finding local extremes. If you specify a continuous interval on the segment [ a ; b ] function F ( x ) , which has a local extremum on this segment, you can find it using the Find Solution tool. Let us solve an example of finding the extremum of a function.
Example 9.9. Find the extremum of the function F ( x ) = 2x 2 + 3 x - 1 on the [-2; 2].
Let's do the following:
• We introduce into the cell any number belonging to a given segment.
• We compute the value of the given function at this point.
• Call the Find Solution tool in the Optimize Target Function field, specify the address of the cell with the formula and set the switch to the minimum value.
• In the Changing cells of variables field, we'll specify a reference to the function argument.
• In the In accordance with the constraints field, add constraints to the argument: greater than or equal to -2 and less than or equal to 2 (Figure 9.26).
The solution is y = -2,125 with x = -0.75.
Fig. 9.26. Search for an extremum with the Solution search tool