- Tutorial
Introduction
I am a mathematician and programmer. The biggest leap I took in my career was when I learned to say: "I do not understand anything!" Now I am not ashamed to tell the luminary of science that he is giving me a lecture, that I do not understand what he, the luminary, is telling me. And it's very difficult. Yes, admitting your ignorance is difficult and embarrassing. Who likes to admit that he doesn’t know the basics of something? Due to my profession, I have to attend a large number of presentations and lectures, where, I admit, in the vast majority of cases I want to sleep because I do not understand anything. But I don’t understand because the huge problem of the current situation in science lies in mathematics. It assumes that all listeners are familiar with absolutely all areas of mathematics (which is absurd). Admitting that you don’t know what a derivative is (we’ll talk about what it is a little later) is shameful.
But I've learned to say that I don't know what multiplication is. Yes, I don't know what a subalgebra over a Lie algebra is. Yes, I don’t know why quadratic equations are needed in life. By the way, if you are sure that you know, then we have something to talk about! Mathematics is a series of tricks. Mathematicians try to confuse and intimidate the public; where there is no confusion, there is no reputation, no authority. Yes, it is prestigious to speak in as abstract a language as possible, which is complete nonsense.
Do you know what a derivative is? Most likely you will tell me about the limit of the difference ratio. In the first year of mathematics and mechanics at St. Petersburg State University, Viktor Petrovich Khavin told me determined derivative as the coefficient of the first term of the Taylor series of the function at a point (this was a separate gymnastics to determine the Taylor series without derivatives). I laughed at this definition for a long time until I finally understood what it was about. The derivative is nothing more than a simple measure of how similar the function we are differentiating is to the function y=x, y=x^2, y=x^3.
I now have the honor of lecturing to students who afraid mathematics. If you are afraid of mathematics, we are on the same path. As soon as you try to read some text and it seems to you that it is overly complicated, then know that it is poorly written. I assert that there is not a single area of mathematics that cannot be discussed “on the fingers” without losing accuracy.
Assignment for the near future: I assigned my students to understand what a linear quadratic regulator is. Don’t be shy, spend three minutes of your life and follow the link. If you don’t understand anything, then we are on the same path. I (a professional mathematician-programmer) didn’t understand anything either. And I assure you, you can figure this out “on your fingers.” At the moment I don't know what it is, but I assure you that we will be able to figure it out.
So, the first lecture that I am going to give to my students after they come running to me in horror and say that a linear-quadratic regulator is a terrible thing that you will never master in your life is least squares methods. Can you solve linear equations? If you are reading this text, then most likely not.
So, given two points (x0, y0), (x1, y1), for example, (1,1) and (3,2), the task is to find the equation of the line passing through these two points:
illustration
This line should have an equation like the following:
Here alpha and beta are unknown to us, but two points of this line are known:
We can write this equation in matrix form:
Here we should make a lyrical digression: what is a matrix? A matrix is nothing more than a two-dimensional array. This is a way of storing data; no further meanings should be attached to it. It depends on us exactly how to interpret a certain matrix. Periodically I will interpret it as a linear mapping, periodically as a quadratic form, and sometimes simply as a set of vectors. This will all be clarified in context.
Let's replace concrete matrices with their symbolic representation:
Then (alpha, beta) can be easily found:
More specifically for our previous data:
Which leads to the following equation of the line passing through the points (1,1) and (3,2):
Okay, everything is clear here. Let's find the equation of the line passing through three points: (x0,y0), (x1,y1) and (x2,y2):
Oh-oh-oh, but we have three equations for two unknowns! A standard mathematician will say that there is no solution. What will the programmer say? And he will first rewrite the previous system of equations in the following form:
In our case, the vectors i, j, b are three-dimensional, therefore (in the general case) there is no solution to this system. Any vector (alpha\*i + beta\*j) lies in the plane spanned by the vectors (i, j). If b does not belong to this plane, then there is no solution (equality cannot be achieved in the equation). What to do? Let's look for a compromise. Let's denote by e(alpha, beta) exactly how far we have not achieved equality:
And we will try to minimize this error:
Why square?
We are looking not just for the minimum of the norm, but for the minimum of the square of the norm. Why? The minimum point itself coincides, and the square gives a smooth function (a quadratic function of the arguments (alpha, beta)), while simply the length gives a cone-shaped function, non-differentiable at the minimum point. Brr. A square is more convenient.
Obviously, the error is minimized when the vector e orthogonal to the plane spanned by the vectors i And j.
Illustration
In other words: we are looking for a straight line such that the sum of the squared lengths of the distances from all points to this straight line is minimal:
UPDATE: I have a problem here, the distance to the straight line should be measured vertically, and not by orthogonal projection. This commentator is right.
Illustration
In completely different words (carefully, poorly formalized, but it should be clear): we take all possible lines between all pairs of points and look for the average line between all:
Illustration
Another explanation is straightforward: we attach a spring between all data points (here we have three) and the straight line that we are looking for, and the straight line of the equilibrium state is exactly what we are looking for.
Minimum quadratic form
So, given this vector b and a plane spanned by the column vectors of the matrix A(in this case (x0,x1,x2) and (1,1,1)), we are looking for the vector e with a minimum square of length. Obviously, the minimum is achievable only for the vector e, orthogonal to the plane spanned by the column vectors of the matrix A:In other words, we are looking for a vector x=(alpha, beta) such that:
Let me remind you that this vector x=(alpha, beta) is the minimum of the quadratic function ||e(alpha, beta)||^2:
Here it would be useful to remember that the matrix can be interpreted also as a quadratic form, for example, the identity matrix ((1,0),(0,1)) can be interpreted as a function x^2 + y^2:
quadratic form
All this gymnastics is known under the name linear regression.
Laplace's equation with Dirichlet boundary condition
Now the simplest real task: there is a certain triangulated surface, it is necessary to smooth it. For example, let's load a model of my face:
The original commit is available. To minimize external dependencies, I took the code of my software renderer, already on Habré. To solve a linear system, I use OpenNL, this is an excellent solver, which, however, is very difficult to install: you need to copy two files (.h+.c) to the folder with your project. All smoothing is done with the following code:
For (int d=0; d<3; d++) {
nlNewContext();
nlSolverParameteri(NL_NB_VARIABLES, verts.size());
nlSolverParameteri(NL_LEAST_SQUARES, NL_TRUE);
nlBegin(NL_SYSTEM);
nlBegin(NL_MATRIX);
for (int i=0; i<(int)verts.size(); i++) {
nlBegin(NL_ROW);
nlCoefficient(i, 1);
nlRightHandSide(verts[i][d]);
nlEnd(NL_ROW);
}
for (unsigned int i=0; i
X, Y and Z coordinates are separable, I smooth them separately. That is, I solve three systems of linear equations, each with a number of variables equal to the number of vertices in my model. The first n rows of matrix A have only one 1 per row, and the first n rows of vector b have the original model coordinates. That is, I tie a spring between the new position of the vertex and the old position of the vertex - the new ones should not move too far from the old ones.
All subsequent rows of matrix A (faces.size()*3 = number of edges of all triangles in the mesh) have one occurrence of 1 and one occurrence of -1, with the vector b having zero components opposite. This means I put a spring on each edge of our triangular mesh: all edges try to get the same vertex as their starting and ending point.
Once again: all vertices are variables, and they cannot move far from their original position, but at the same time they try to become similar to each other.
Here's the result:
Everything would be fine, the model is really smoothed, but it has moved away from its original edge. Let's change the code a little:
For (int i=0; i<(int)verts.size(); i++) { float scale = border[i] ? 1000: 1; nlBegin(NL_ROW); nlCoefficient(i, scale); nlRightHandSide(scale*verts[i][d]); nlEnd(NL_ROW); }
In our matrix A, for the vertices that are on the edge, I add not a row from the category v_i = verts[i][d], but 1000*v_i = 1000*verts[i][d]. What does it change? And this changes our quadratic form of error. Now a single deviation from the top at the edge will cost not one unit, as before, but 1000*1000 units. That is, we hung a stronger spring on the extreme vertices, the solution will prefer to stretch the others more strongly. Here's the result:
Let's double the spring strength between the vertices:
nlCoefficient(face[ j ], 2); nlCoefficient(face[(j+1)%3], -2);
It is logical that the surface has become smoother:
And now even a hundred times stronger:
What is this? Imagine that we have dipped a wire ring in soapy water. As a result, the resulting soap film will try to have the least curvature as possible, touching the border - our wire ring. This is exactly what we got by fixing the border and asking for a smooth surface inside. Congratulations, we have just solved Laplace's equation with Dirichlet boundary conditions. Sounds cool? But in reality, you just need to solve one system of linear equations.
Poisson's equation
Let's remember another cool name.Let's say I have an image like this:
Looks good to everyone, but I don’t like the chair.
I'll cut the picture in half: 
And I will select a chair with my hands: 
Then I will pull everything that is white in the mask to the left side of the picture, and at the same time throughout the picture I will say that the difference between two neighboring pixels should be equal to the difference between two neighboring pixels of the right picture:
For (int i=0; i Here's the result: Code and pictures available Having chosen the type of regression function, i.e. the type of the considered model of the dependence of Y on X (or X on Y), for example, a linear model y x =a+bx, it is necessary to determine the specific values of the model coefficients. For different values of a and b, it is possible to construct an infinite number of dependencies of the form y x = a + bx, i.e. there are an infinite number of straight lines on the coordinate plane, but we need a dependency that best corresponds to the observed values. Thus, the task comes down to selecting the best coefficients. We look for the linear function a+bx based only on a certain number of available observations. To find the function with the best fit to the observed values, we use the least squares method. Let us denote: Y i - the value calculated by the equation Y i =a+bx i. y i - measured value, ε i =y i -Y i - difference between measured and calculated values using the equation, ε i =y i -a-bx i . The least squares method requires that ε i, the difference between the measured y i and the values Y i calculated from the equation, be minimal. Therefore, we find the coefficients a and b so that the sum of the squared deviations of the observed values from the values on the straight regression line is the smallest: By examining this function of arguments a and for extremum using derivatives, we can prove that the function takes a minimum value if the coefficients a and b are solutions to the system: If we divide both sides of the normal equations by n, we get: Considering that We get In this case, b is called the regression coefficient; a is called the free term of the regression equation and is calculated using the formula: The resulting straight line is an estimate for the theoretical regression line. We have: So, Regression can be direct (b>0) and reverse (b Example 1. The results of measuring the values of X and Y are given in the table: Assuming that there is a linear relationship between X and Y y=a+bx, determine the coefficients a and b using the least squares method. Solution. Here n=5 and normal system (2) has the form Solving this system, we get: b=0.425, a=1.175. Therefore y=1.175+0.425x. Example 2. There is a sample of 10 observations of economic indicators (X) and (Y). You need to find a sample regression equation of Y on X. Construct a sample regression line of Y on X. Solution. 1. Let's sort the data according to the values x i and y i . We get a new table: To simplify the calculations, we will draw up a calculation table in which we will enter the necessary numerical values. According to formula (4), we calculate the regression coefficient and according to formula (5) Thus, the sample regression equation is y=-59.34+1.3804x. Figure 4 shows how the observed values are located relative to the regression line. To numerically assess the deviations of y i from Y i, where y i are observed and Y i are values determined by regression, we create a table: Yi values are calculated according to the regression equation. The noticeable deviation of some observed values from the regression line is explained by the small number of observations. When studying the degree of linear dependence of Y on X, the number of observations is taken into account. The strength of the dependence is determined by the value of the correlation coefficient. Example. Experimental data on the values of variables X And at are given in the table. As a result of their alignment, the function is obtained Using least square method, approximate these data by a linear dependence y=ax+b(find parameters A And b). Find out which of the two lines better (in the sense of the least squares method) aligns the experimental data. Make a drawing. The task is to find the linear dependence coefficients at which the function of two variables A And b
Thus, solving the example comes down to finding the extremum of a function of two variables. A system of two equations with two unknowns is compiled and solved. Finding the partial derivatives of a function We solve the resulting system of equations using any method (for example by substitution method or Cramer's method) and obtain formulas for finding coefficients using the least squares method (LSM). Given A And b function That's the whole method of least squares. Formula for finding the parameter a contains the sums ,,, and parameter n- amount of experimental data. We recommend calculating the values of these amounts separately. Coefficient b found after calculation a. It's time to remember the original example. Solution. In our example n=5. We fill out the table for the convenience of calculating the amounts that are included in the formulas of the required coefficients. The values in the fourth row of the table are obtained by multiplying the values of the 2nd row by the values of the 3rd row for each number i. The values in the fifth row of the table are obtained by squaring the values in the 2nd row for each number i. The values in the last column of the table are the sums of the values across the rows. We use the formulas of the least squares method to find the coefficients A And b. We substitute the corresponding values from the last column of the table into them: Hence, y = 0.165x+2.184- the desired approximating straight line. It remains to find out which of the lines y = 0.165x+2.184 or To do this, you need to calculate the sum of squared deviations of the original data from these lines Since , then straight y = 0.165x+2.184 better approximates the original data. Everything is clearly visible on the graphs. The red line is the found straight line y = 0.165x+2.184, the blue line is In practice, when modeling various processes - in particular, economic, physical, technical, social - one or another method of calculating approximate values of functions from their known values at certain fixed points is widely used. This kind of function approximation problem often arises: when constructing approximate formulas for calculating the values of characteristic quantities of the process under study using tabular data obtained as a result of the experiment; in numerical integration, differentiation, solving differential equations, etc.; if necessary, calculate the values of functions at intermediate points of the considered interval; when determining the values of characteristic quantities of a process outside the considered interval, in particular when forecasting. If, to model a certain process specified by a table, we construct a function that approximately describes this process based on the least squares method, it will be called an approximating function (regression), and the task of constructing approximating functions itself will be called an approximation problem. This article discusses the capabilities of the MS Excel package for solving this type of problem, in addition, it provides methods and techniques for constructing (creating) regressions for tabulated functions (which is the basis of regression analysis). Excel has two options for building regressions. Adding selected regressions (trendlines) to a diagram built on the basis of a data table for the process characteristic under study (available only if a diagram has been constructed); Using the built-in statistical functions of the Excel worksheet, allowing you to obtain regressions (trend lines) directly from the source data table. Adding trend lines to a chart For a table of data that describes a process and is represented by a diagram, Excel has an effective regression analysis tool that allows you to: build on the basis of the least squares method and add five types of regressions to the diagram, which model the process under study with varying degrees of accuracy; add the constructed regression equation to the diagram; determine the degree of correspondence of the selected regression to the data displayed on the chart. Based on chart data, Excel allows you to obtain linear, polynomial, logarithmic, power, exponential types of regressions, which are specified by the equation: y = y(x) where x is an independent variable that often takes the values of a sequence of natural numbers (1; 2; 3; ...) and produces, for example, a countdown of the time of the process under study (characteristics). 1
. Linear regression is good for modeling characteristics whose values increase or decrease at a constant rate. This is the simplest model to construct for the process under study. It is constructed in accordance with the equation: y = mx + b where m is the tangent of the linear regression slope to the x-axis; b - coordinate of the point of intersection of linear regression with the ordinate axis. 2
. A polynomial trend line is useful for describing characteristics that have several distinct extremes (maxima and minima). The choice of polynomial degree is determined by the number of extrema of the characteristic under study. Thus, a second-degree polynomial can well describe a process that has only one maximum or minimum; polynomial of the third degree - no more than two extrema; polynomial of the fourth degree - no more than three extrema, etc. In this case, the trend line is constructed in accordance with the equation: y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6 where coefficients c0, c1, c2,... c6 are constants whose values are determined during construction. 3
. The logarithmic trend line is successfully used when modeling characteristics whose values initially change rapidly and then gradually stabilize. y = c ln(x) + b 4
. A power-law trend line gives good results if the values of the relationship under study are characterized by a constant change in the growth rate. An example of such a dependence is the graph of uniformly accelerated motion of a car. If there are zero or negative values in the data, you cannot use a power trend line. Constructed in accordance with the equation: y = c xb where coefficients b, c are constants. 5
. An exponential trend line should be used when the rate of change in the data is continuously increasing. For data containing zero or negative values, this type of approximation is also not applicable. Constructed in accordance with the equation: y = c ebx where coefficients b, c are constants. When selecting a trend line, Excel automatically calculates the value of R2, which characterizes the reliability of the approximation: the closer the R2 value is to unity, the more reliably the trend line approximates the process under study. If necessary, the R2 value can always be displayed on the chart. Determined by the formula: To add a trend line to a data series: activate a chart based on a series of data, i.e. click within the chart area. The Diagram item will appear in the main menu; after clicking on this item, a menu will appear on the screen in which you should select the Add trend line command. The same actions can be easily implemented by moving the mouse pointer over the graph corresponding to one of the data series and right-clicking; In the context menu that appears, select the Add trend line command. The Trendline dialog box will appear on the screen with the Type tab opened (Fig. 1). After this you need: Select the required trend line type on the Type tab (the Linear type is selected by default). For the Polynomial type, in the Degree field, specify the degree of the selected polynomial. 1
. The Built on series field lists all data series in the chart in question. To add a trend line to a specific data series, select its name in the Built on series field. If necessary, by going to the Parameters tab (Fig. 2), you can set the following parameters for the trend line: change the name of the trend line in the Name of the approximating (smoothed) curve field. set the number of periods (forward or backward) for the forecast in the Forecast field; display the equation of the trend line in the diagram area, for which you should enable the show equation on the diagram checkbox; display the approximation reliability value R2 in the diagram area, for which you should enable the Place the approximation reliability value on the diagram (R^2) checkbox; set the intersection point of the trend line with the Y axis, for which you should enable the checkbox for the intersection of the curve with the Y axis at a point; Click the OK button to close the dialog box. In order to start editing an already drawn trend line, there are three ways: use the Selected trend line command from the Format menu, having previously selected the trend line; select the Format trend line command from the context menu, which is called up by right-clicking on the trend line; double click on the trend line. The Trend Line Format dialog box will appear on the screen (Fig. 3), containing three tabs: View, Type, Parameters, and the contents of the last two completely coincide with the similar tabs of the Trend Line dialog box (Fig. 1-2). On the View tab, you can set the line type, its color and thickness. To delete a trend line that has already been drawn, select the trend line to be deleted and press the Delete key. The advantages of the considered regression analysis tool are: the relative ease of constructing a trend line on charts without creating a data table for it; a fairly wide list of types of proposed trend lines, and this list includes the most commonly used types of regression; the ability to predict the behavior of the process under study by an arbitrary (within the limits of common sense) number of steps forward and also backward; the ability to obtain the trend line equation in analytical form; the possibility, if necessary, of obtaining an assessment of the reliability of the approximation. The disadvantages include the following: the construction of a trend line is carried out only if there is a diagram built on a series of data; the process of generating data series for the characteristic under study based on the trend line equations obtained for it is somewhat cluttered: the required regression equations are updated with each change in the values of the original data series, but only within the chart area, while the data series formed on the basis of the old line equation trend remains unchanged; In PivotChart reports, changing the view of a chart or associated PivotTable report does not preserve existing trendlines, meaning that before you draw trendlines or otherwise format a PivotChart report, you should ensure that the report layout meets the required requirements. Trend lines can be used to supplement data series presented on charts such as graph, histogram, flat non-standardized area charts, bar charts, scatter charts, bubble charts, and stock charts. You cannot add trend lines to data series in 3D, normalized, radar, pie, and donut charts. Using Excel's built-in functions Excel also has a regression analysis tool for plotting trend lines outside the chart area. There are a number of statistical worksheet functions you can use for this purpose, but all of them only allow you to build linear or exponential regressions. Excel has several functions for constructing linear regression, in particular: TREND; SLOPE and CUT. As well as several functions for constructing an exponential trend line, in particular: LGRFPRIBL. It should be noted that the techniques for constructing regressions using the TREND and GROWTH functions are almost the same. The same can be said about the pair of functions LINEST and LGRFPRIBL. For these four functions, creating a table of values uses Excel features such as array formulas, which somewhat clutters the process of building regressions. Let us also note that the construction of linear regression, in our opinion, is most easily accomplished using the SLOPE and INTERCEPT functions, where the first of them determines the slope of the linear regression, and the second determines the segment intercepted by the regression on the y-axis. The advantages of the built-in functions tool for regression analysis are: a fairly simple, uniform process of generating data series of the characteristic under study for all built-in statistical functions that define trend lines; standard methodology for constructing trend lines based on generated data series; the ability to predict the behavior of the process under study by the required number of steps forward or backward. The disadvantages include the fact that Excel does not have built-in functions for creating other (except linear and exponential) types of trend lines. This circumstance often does not allow choosing a sufficiently accurate model of the process under study, as well as obtaining forecasts that are close to reality. In addition, when using the TREND and GROWTH functions, the equations of the trend lines are not known. It should be noted that the authors did not set out to present the course of regression analysis with any degree of completeness. Its main task is to show, using specific examples, the capabilities of the Excel package when solving approximation problems; demonstrate what effective tools Excel has for building regressions and forecasting; illustrate how such problems can be solved relatively easily even by a user who does not have extensive knowledge of regression analysis. Examples of solving specific problems Let's look at solving specific problems using the listed Excel tools. Problem 1 With a table of data on the profit of a motor transport enterprise for 1995-2002. you need to do the following: Build a diagram. Add linear and polynomial (quadratic and cubic) trend lines to the chart. Using the trend line equations, obtain tabular data on enterprise profits for each trend line for 1995-2004. Make a forecast for the enterprise's profit for 2003 and 2004. The solution of the problem In the range of cells A4:C11 of the Excel worksheet, enter the worksheet shown in Fig. 4. Having selected the range of cells B4:C11, we build a diagram. We activate the constructed diagram and, according to the method described above, after selecting the type of trend line in the Trend Line dialog box (see Fig. 1), we alternately add linear, quadratic and cubic trend lines to the diagram. In the same dialog box, open the Parameters tab (see Fig. 2), in the Name of the approximating (smoothed) curve field, enter the name of the trend being added, and in the Forecast forward for: periods field, set the value 2, since it is planned to make a profit forecast for two years ahead. To display the regression equation and the approximation reliability value R2 in the diagram area, enable the show equation on the screen checkboxes and place the approximation reliability value (R^2) on the diagram. For better visual perception, we change the type, color and thickness of the constructed trend lines, for which we use the View tab of the Trend Line Format dialog box (see Fig. 3). The resulting diagram with added trend lines is shown in Fig. 5. To obtain tabular data on enterprise profits for each trend line for 1995-2004. Let's use the trend line equations presented in Fig. 5. To do this, in the cells of the range D3:F3, enter text information about the type of the selected trend line: Linear trend, Quadratic trend, Cubic trend. Next, enter the linear regression formula in cell D4 and, using the fill marker, copy this formula with relative references to the cell range D5:D13. It should be noted that each cell with a linear regression formula from the range of cells D4:D13 has as an argument a corresponding cell from the range A4:A13. Similarly, for quadratic regression, fill the range of cells E4:E13, and for cubic regression, fill the range of cells F4:F13. Thus, a forecast for the enterprise's profit for 2003 and 2004 has been compiled. using three trends. The resulting table of values is shown in Fig. 6. Problem 2 Build a diagram. Add logarithmic, power and exponential trend lines to the chart. Derive the equations of the obtained trend lines, as well as the reliability values of the approximation R2 for each of them. Using the trend line equations, obtain tabular data on the enterprise's profit for each trend line for 1995-2002. Make a forecast of the company's profit for 2003 and 2004 using these trend lines. The solution of the problem Following the methodology given in solving problem 1, we obtain a diagram with logarithmic, power and exponential trend lines added to it (Fig. 7). Next, using the obtained trend line equations, we fill out a table of values for the enterprise’s profit, including the predicted values for 2003 and 2004. (Fig. 8). In Fig. 5 and fig. it can be seen that the model with a logarithmic trend corresponds to the lowest value of approximation reliability R2 = 0.8659 The highest values of R2 correspond to models with a polynomial trend: quadratic (R2 = 0.9263) and cubic (R2 = 0.933). Problem 3 With the table of data on the profit of a motor transport enterprise for 1995-2002, given in task 1, you must perform the following steps. Obtain data series for linear and exponential trend lines using the TREND and GROW functions. Using the TREND and GROWTH functions, make a forecast of the enterprise’s profit for 2003 and 2004. Construct a diagram for the original data and the resulting data series. The solution of the problem Let's use the worksheet for Problem 1 (see Fig. 4). Let's start with the TREND function: select the range of cells D4:D11, which should be filled with the values of the TREND function corresponding to the known data on the profit of the enterprise; Call the Function command from the Insert menu. In the Function Wizard dialog box that appears, select the TREND function from the Statistical category, and then click the OK button. The same operation can be performed by clicking the (Insert Function) button on the standard toolbar. In the Function Arguments dialog box that appears, enter the range of cells C4:C11 in the Known_values_y field; in the Known_values_x field - the range of cells B4:B11; To make the entered formula become an array formula, use the key combination + + . The formula we entered in the formula bar will look like: =(TREND(C4:C11,B4:B11)). As a result, the range of cells D4:D11 is filled with the corresponding values of the TREND function (Fig. 9). To make a forecast of the enterprise's profit for 2003 and 2004. necessary: select the range of cells D12:D13 where the values predicted by the TREND function will be entered. call the TREND function and in the Function Arguments dialog box that appears, enter in the Known_values_y field - the range of cells C4:C11; in the Known_values_x field - the range of cells B4:B11; and in the New_values_x field - the range of cells B12:B13. turn this formula into an array formula using the key combination Ctrl + Shift + Enter. The entered formula will look like: =(TREND(C4:C11;B4:B11;B12:B13)), and the range of cells D12:D13 will be filled with the predicted values of the TREND function (see Fig. 9). The data series is similarly filled in using the GROWTH function, which is used in the analysis of nonlinear dependencies and works in exactly the same way as its linear counterpart TREND. Figure 10 shows the table in formula display mode. For the initial data and the obtained data series, the diagram shown in Fig. eleven. Problem 4 With the table of data on the receipt of applications for services by the dispatch service of a motor transport enterprise for the period from the 1st to the 11th of the current month, you must perform the following actions. Get data series for linear regression: using the SLOPE and INTERCEPT functions; using the LINEST function. Obtain a series of data for exponential regression using the LGRFPRIBL function. Using the above functions, make a forecast about the receipt of applications to the dispatch service for the period from the 12th to the 14th of the current month. Create a diagram for the original and received data series. The solution of the problem Note that, unlike the TREND and GROWTH functions, none of the functions listed above (SLOPE, INTERCEPT, LINEST, LGRFPRIB) are regression. These functions play only a supporting role, determining the necessary regression parameters. For linear and exponential regressions built using the functions SLOPE, INTERCEPT, LINEST, LGRFPRIB, the appearance of their equations is always known, in contrast to linear and exponential regressions corresponding to the TREND and GROWTH functions. 1
. Let's build a linear regression with the equation: y = mx+b using the SLOPE and INTERCEPT functions, with the regression slope m determined by the SLOPE function, and the free term b by the INTERCEPT function. To do this, we carry out the following actions: enter the original table into the cell range A4:B14; the value of parameter m will be determined in cell C19. Select the Slope function from the Statistical category; enter the range of cells B4:B14 in the known_values_y field and the range of cells A4:A14 in the known_values_x field. The formula will be entered in cell C19: =SLOPE(B4:B14,A4:A14); Using a similar technique, the value of parameter b in cell D19 is determined. And its contents will look like: =SEGMENT(B4:B14,A4:A14). Thus, the values of the parameters m and b required for constructing a linear regression will be stored in cells C19, D19, respectively; Next, enter the linear regression formula in cell C4 in the form: =$C*A4+$D. In this formula, cells C19 and D19 are written with absolute references (the cell address should not change during possible copying). The absolute reference sign $ can be typed either from the keyboard or using the F4 key, after placing the cursor on the cell address. Using the fill handle, copy this formula into the range of cells C4:C17. We obtain the required data series (Fig. 12). Due to the fact that the number of requests is an integer, you should set the number format with the number of decimal places to 0 on the Number tab of the Cell Format window. 2
. Now let's build a linear regression given by the equation: y = mx+b using the LINEST function. For this: Enter the LINEST function as an array formula in the cell range C20:D20: =(LINEST(B4:B14,A4:A14)). As a result, we obtain the value of parameter m in cell C20, and the value of parameter b in cell D20; enter the formula in cell D4: =$C*A4+$D; copy this formula using the fill marker into the cell range D4:D17 and get the desired data series. 3
. We build an exponential regression with the equation: using the LGRFPRIBL function it is performed similarly: In the cell range C21:D21 we enter the LGRFPRIBL function as an array formula: =( LGRFPRIBL (B4:B14,A4:A14)). In this case, the value of parameter m will be determined in cell C21, and the value of parameter b will be determined in cell D21; the formula is entered into cell E4: =$D*$C^A4; using the fill marker, this formula is copied to the range of cells E4:E17, where the data series for exponential regression will be located (see Fig. 12). In Fig. Figure 13 shows a table where you can see the functions we use with the required cell ranges, as well as formulas. Magnitude R
2
called coefficient of determination. The task of constructing a regression dependence is to find the vector of coefficients m of model (1) at which the coefficient R takes on the maximum value. To assess the significance of R, Fisher's F test is used, calculated using the formula Where n- sample size (number of experiments); k is the number of model coefficients. If F exceeds some critical value for the data n And k and the accepted confidence probability, then the value of R is considered significant. Tables of critical values of F are given in reference books on mathematical statistics. Thus, the significance of R is determined not only by its value, but also by the ratio between the number of experiments and the number of coefficients (parameters) of the model. Indeed, the correlation ratio for n=2 for a simple linear model is equal to 1 (a single straight line can always be drawn through 2 points on a plane). However, if the experimental data are random variables, such a value of R should be trusted with great caution. Usually, to obtain significant R and reliable regression, they strive to ensure that the number of experiments significantly exceeds the number of model coefficients (n>k). To build a linear regression model you need: 1) prepare a list of n rows and m columns containing experimental data (column containing the output value Y must be either first or last in the list); For example, let’s take the data from the previous task, adding a column called “Period No.”, number the period numbers from 1 to 12. (these will be the values X) 2) go to the menu Data/Data Analysis/Regression If the "Data Analysis" item in the "Tools" menu is missing, then you should go to the "Add-Ins" item in the same menu and check the "Analysis package" checkbox. 3) in the "Regression" dialog box, set: · input interval Y; · input interval X; · output interval - the upper left cell of the interval in which the calculation results will be placed (it is recommended to place them on a new worksheet); 4) click "Ok" and analyze the results. After leveling, we obtain a function of the following form: g (x) = x + 1 3 + 1 . We can approximate this data using the linear relationship y = a x + b by calculating the corresponding parameters. To do this, we will need to apply the so-called least squares method. You will also need to make a drawing to check which line will best align the experimental data. Yandex.RTB R-A-339285-1 The main thing we need to do is to find such coefficients of linear dependence at which the value of the function of two variables F (a, b) = ∑ i = 1 n (y i - (a x i + b)) 2 will be the smallest. In other words, for certain values of a and b, the sum of the squared deviations of the presented data from the resulting straight line will have a minimum value. This is the meaning of the least squares method. All we need to do to solve the example is to find the extremum of the function of two variables. In order to derive formulas for calculating coefficients, you need to create and solve a system of equations with two variables. To do this, we calculate the partial derivatives of the expression F (a, b) = ∑ i = 1 n (y i - (a x i + b)) 2 with respect to a and b and equate them to 0. δ F (a , b) δ a = 0 δ F (a , b) δ b = 0 ⇔ - 2 ∑ i = 1 n (y i - (a x i + b)) x i = 0 - 2 ∑ i = 1 n ( y i - (a x i + b)) = 0 ⇔ a ∑ i = 1 n x i 2 + b ∑ i = 1 n x i = ∑ i = 1 n x i y i a ∑ i = 1 n x i + ∑ i = 1 n b = ∑ i = 1 n y i ⇔ a ∑ i = 1 n x i 2 + b ∑ i = 1 n x i = ∑ i = 1 n x i y i a ∑ i = 1 n x i + n b = ∑ i = 1 n y i To solve a system of equations, you can use any methods, for example, substitution or Cramer's method. As a result, we should have formulas that can be used to calculate coefficients using the least squares method. n ∑ i = 1 n x i y i - ∑ i = 1 n x i ∑ i = 1 n y i n ∑ i = 1 n - ∑ i = 1 n x i 2 b = ∑ i = 1 n y i - a ∑ i = 1 n x i n We have calculated the values of the variables at which the function This is the application of the least squares method in practice. Its formula, which is used to find the parameter a, includes ∑ i = 1 n x i, ∑ i = 1 n y i, ∑ i = 1 n x i y i, ∑ i = 1 n x i 2, as well as the parameter Let's go back to the original example. Example 1 Here we have n equal to five. To make it more convenient to calculate the required amounts included in the coefficient formulas, let’s fill out the table. Solution
The fourth row includes the data obtained by multiplying the values from the second row by the values of the third for each individual i. The fifth line contains the data from the second, squared. The last column shows the sums of the values of individual rows. Let's use the least squares method to calculate the coefficients a and b we need. To do this, substitute the required values from the last column and calculate the amounts: n ∑ i = 1 n x i y i - ∑ i = 1 n x i ∑ i = 1 n y i n ∑ i = 1 n - ∑ i = 1 n x i 2 b = ∑ i = 1 n y i - a ∑ i = 1 n x i n ⇒ a = 5 33, 8 - 12 12, 9 5 46 - 12 2 b = 12, 9 - a 12 5 ⇒ a ≈ 0, 165 b ≈ 2, 184 It turns out that the required approximating straight line will look like y = 0, 165 x + 2, 184. Now we need to determine which line will better approximate the data - g (x) = x + 1 3 + 1 or 0, 165 x + 2, 184. Let's estimate using the least squares method. To calculate the error, we need to find the sum of squared deviations of the data from the straight lines σ 1 = ∑ i = 1 n (y i - (a x i + b i)) 2 and σ 2 = ∑ i = 1 n (y i - g (x i)) 2, the minimum value will correspond to a more suitable line. σ 1 = ∑ i = 1 n (y i - (a x i + b i)) 2 = = ∑ i = 1 5 (y i - (0, 165 x i + 2, 184)) 2 ≈ 0, 019 σ 2 = ∑ i = 1 n (y i - g (x i)) 2 = = ∑ i = 1 5 (y i - (x i + 1 3 + 1)) 2 ≈ 0.096 Answer: since σ 1< σ 2 , то прямой, наилучшим образом аппроксимирующей исходные данные, будет The least squares method is clearly shown in the graphical illustration. The red line marks the straight line g (x) = x + 1 3 + 1, the blue line marks y = 0, 165 x + 2, 184. The original data is indicated by pink dots. Let us explain why exactly approximations of this type are needed. They can be used in tasks that require data smoothing, as well as in those where data must be interpolated or extrapolated. For example, in the problem discussed above, one could find the value of the observed quantity y at x = 3 or at x = 6. We have devoted a separate article to such examples. In order for the function to take a minimum value when a and b are calculated, it is necessary that at a given point the matrix of the quadratic form of the differential of the function of the form F (a, b) = ∑ i = 1 n (y i - (a x i + b)) 2 is positive definite. Let's show you how it should look. Example 2 We have a second order differential of the following form: d 2 F (a ; b) = δ 2 F (a ; b) δ a 2 d 2 a + 2 δ 2 F (a ; b) δ a δ b d a d b + δ 2 F (a ; b) δ b 2 d 2 b Solution
δ 2 F (a ; b) δ a 2 = δ δ F (a ; b) δ a δ a = = δ - 2 ∑ i = 1 n (y i - (a x i + b)) x i δ a = 2 ∑ i = 1 n (x i) 2 δ 2 F (a; b) δ a δ b = δ δ F (a; b) δ a δ b = = δ - 2 ∑ i = 1 n (y i - (a x i + b) ) x i δ b = 2 ∑ i = 1 n x i δ 2 F (a ; b) δ b 2 = δ δ F (a ; b) δ b δ b = δ - 2 ∑ i = 1 n (y i - (a x i + b)) δ b = 2 ∑ i = 1 n (1) = 2 n In other words, we can write it like this: d 2 F (a ; b) = 2 ∑ i = 1 n (x i) 2 d 2 a + 2 2 ∑ x i i = 1 n d a d b + (2 n) d 2 b. We obtained a matrix of the quadratic form M = 2 ∑ i = 1 n (x i) 2 2 ∑ i = 1 n x i 2 ∑ i = 1 n x i 2 n . In this case, the values of individual elements will not change depending on a and b . Is this matrix positive definite? To answer this question, let's check whether its angular minors are positive. We calculate the angular minor of the first order: 2 ∑ i = 1 n (x i) 2 > 0 . Since the points x i do not coincide, the inequality is strict. We will keep this in mind in further calculations. We calculate the second order angular minor: d e t (M) = 2 ∑ i = 1 n (x i) 2 2 ∑ i = 1 n x i 2 ∑ i = 1 n x i 2 n = 4 n ∑ i = 1 n (x i) 2 - ∑ i = 1 n x i 2 After this, we proceed to prove the inequality n ∑ i = 1 n (x i) 2 - ∑ i = 1 n x i 2 > 0 using mathematical induction. 2 ∑ i = 1 2 (x i) 2 - ∑ i = 1 2 x i 2 = 2 x 1 2 + x 2 2 - x 1 + x 2 2 = = x 1 2 - 2 x 1 x 2 + x 2 2 = x 1 + x 2 2 > 0 We have obtained a correct equality (if the values x 1 and x 2 do not coincide). We calculate: (n + 1) ∑ i = 1 n + 1 (x i) 2 - ∑ i = 1 n + 1 x i 2 = = (n + 1) ∑ i = 1 n (x i) 2 + x n + 1 2 - ∑ i = 1 n x i + x n + 1 2 = = n ∑ i = 1 n (x i) 2 + n x n + 1 2 + ∑ i = 1 n (x i) 2 + x n + 1 2 - - ∑ i = 1 n x i 2 + 2 x n + 1 ∑ i = 1 n x i + x n + 1 2 = = ∑ i = 1 n (x i) 2 - ∑ i = 1 n x i 2 + n x n + 1 2 - x n + 1 ∑ i = 1 n x i + ∑ i = 1 n (x i) 2 = = ∑ i = 1 n (x i) 2 - ∑ i = 1 n x i 2 + x n + 1 2 - 2 x n + 1 x 1 + x 1 2 + + x n + 1 2 - 2 x n + 1 x 2 + x 2 2 + . . . + x n + 1 2 - 2 x n + 1 x 1 + x n 2 = = n ∑ i = 1 n (x i) 2 - ∑ i = 1 n x i 2 + + (x n + 1 - x 1) 2 + (x n + 1 - x 2) 2 + . . . + (x n - 1 - x n) 2 > 0 The expression enclosed in curly braces will be greater than 0 (based on what we assumed in step 2), and the remaining terms will be greater than 0, since they are all squares of numbers. We have proven the inequality. Answer: the found a and b will correspond to the smallest value of the function F (a, b) = ∑ i = 1 n (y i - (a x i + b)) 2, which means that they are the required parameters of the least squares method (LSM). If you notice an error in the text, please highlight it and press Ctrl+Enter The essence of the least squares method is
in finding the parameters of a trend model that best describes the tendency of development of any random phenomenon in time or space (a trend is a line that characterizes the tendency of this development). The task of the least squares method (LSM) comes down to finding not just some trend model, but to finding the best or optimal model. This model will be optimal if the sum of square deviations between the observed actual values and the corresponding calculated trend values is minimal (smallest): where is the square deviation between the observed actual value and the corresponding calculated trend value, The actual (observed) value of the phenomenon being studied, The calculated value of the trend model, The number of observations of the phenomenon being studied. MNC is used quite rarely on its own. As a rule, most often it is used only as a necessary technical technique in correlation studies.
It should be remembered that the information basis of OLS can only be a reliable statistical series, and the number of observations should not be less than 4, otherwise the smoothing procedures of OLS may lose common sense. The MNC toolkit boils down to the following procedures: First procedure.
It turns out whether there is any tendency at all to change the resultant attribute when the selected factor-argument changes, or in other words, is there a connection between “ at
" And " X
». Second procedure.
It is determined which line (trajectory) can best describe or characterize this trend. Third procedure.
Example. Let's say we have information about the average sunflower yield for the farm under study (Table 9.1). Table 9.1 Observation number Productivity, c/ha Since the level of technology in sunflower production in our country has remained virtually unchanged over the past 10 years, it means that, apparently, fluctuations in yield during the analyzed period were very much dependent on fluctuations in weather and climatic conditions. Is this really true? First OLS procedure.
The hypothesis about the existence of a trend in sunflower yield changes depending on changes in weather and climatic conditions over the analyzed 10 years is tested. In this example, for " y
" it is advisable to take the sunflower yield, and for " x
» – number of the observed year in the analyzed period. Testing the hypothesis about the existence of any relationship between " x
" And " y
"can be done in two ways: manually and using computer programs. Of course, with the availability of computer technology, this problem can be solved by itself. But in order to better understand the MNC tools, it is advisable to test the hypothesis about the existence of a relationship between “ x
" And " y
» manually, when only a pen and an ordinary calculator are at hand. In such cases, the hypothesis about the existence of a trend is best checked visually by the location of the graphical image of the analyzed series of dynamics - the correlation field: The correlation field in our example is located around a slowly increasing line. This in itself indicates the existence of a certain trend in changes in sunflower yields. It is impossible to talk about the presence of any tendency only when the correlation field looks like a circle, a circle, a strictly vertical or strictly horizontal cloud, or consists of chaotically scattered points. In all other cases, the hypothesis about the existence of a relationship between “ x
" And " y
", and continue research. Second OLS procedure.
It is determined which line (trajectory) can best describe or characterize the trend of changes in sunflower yield over the analyzed period. If you have computer technology, the selection of the optimal trend occurs automatically. In “manual” processing, the selection of the optimal function is carried out, as a rule, visually - by the location of the correlation field. That is, based on the type of graph, the equation of the line that best fits the empirical trend (the actual trajectory) is selected. As is known, in nature there is a huge variety of functional dependencies, so it is extremely difficult to visually analyze even a small part of them. Fortunately, in real economic practice, most relationships can be described quite accurately either by a parabola, or a hyperbola, or a straight line. In this regard, with the “manual” option of selecting the best function, you can limit yourself to only these three models. Hyperbola: Second order parabola: It is easy to see that in our example, the trend in sunflower yield changes over the analyzed 10 years is best characterized by a straight line, so the regression equation will be the equation of a straight line. Third procedure.
The parameters of the regression equation characterizing this line are calculated, or in other words, an analytical formula is determined that describes the best trend model. Finding the values of the parameters of the regression equation, in our case the parameters and , is the core of the OLS. This process comes down to solving a system of normal equations. This system of equations can be solved quite easily by the Gauss method. Let us recall that as a result of the solution, in our example, the values of the parameters and are found. Thus, the found regression equation will have the following form: 
(2)
(3)
, from here, substituting the value of a into the first equation, we get:
is a linear regression equation.x i
-2
0
1
2
4
y i
0.5
1
1.5
2
3
x i =-2+0+1+2+4=5;
x i 2 =4+0+1+4+16=25
x i y i =-2 0.5+0 1+1 1.5+2 2+4 3=16.5
y i =0.5+1+1.5+2+3=8
x i
180
172
173
169
175
170
179
170
167
174
y i
186
180
176
171
182
166
182
172
169
177
x i
167
169
170
170
172
173
174
175
179
180
y i
169
171
166
172
180
176
177
182
182
186
x i
y i
x i 2
x i y i
167
169
27889
28223
169
171
28561
28899
170
166
28900
28220
170
172
28900
29240
172
180
29584
30960
173
176
29929
30448
174
177
30276
30798
175
182
30625
31850
179
182
32041
32578
180
186
32400
33480
∑x i =1729
∑y i =1761
∑x i 2 299105
∑x i y i =304696
x=172.9
y=176.1
x i 2 =29910.5
xy=30469.6
Let's plot the points (x i ; y i) on the coordinate plane and mark the regression line.
Fig 4x i
y i
Y i
Y i -y i
167
169
168.055
-0.945
169
171
170.778
-0.222
170
166
172.140
6.140
170
172
172.140
0.140
172
180
174.863
-5.137
173
176
176.225
0.225
174
177
177.587
0.587
175
182
178.949
-3.051
179
182
184.395
2.395
180
186
185.757
-0.243
The essence of the least squares method (LSM).
takes the smallest value. That is, given A And b the sum of squared deviations of the experimental data from the found straight line will be the smallest. This is the whole point of the least squares method.Deriving formulas for finding coefficients.
by variables A And b, we equate these derivatives to zero. 
takes the smallest value. The proof of this fact is given below in the text at the end of the page.
better approximates the original data, that is, makes an estimate using the least squares method.Error estimation of the least squares method.
And 
, a smaller value corresponds to a line that better approximates the original data in the sense of the least squares method. 
Graphic illustration of the least squares (LS) method.
, pink dots are the original data.
What exactly is OLS (least squares method)
How to derive formulas for calculating coefficients
F (a , b) = ∑ i = 1 n (y i - (a x i + b)) 2 will take the minimum value. In the third paragraph we will prove why it is exactly like this.
n – it denotes the amount of experimental data. We advise you to calculate each amount separately. The value of the coefficient b is calculated immediately after a.
i = 1
i=2
i=3
i=4
i=5
∑ i = 1 5
x i
0
1
2
4
5
12
y i
2 , 1
2 , 4
2 , 6
2 , 8
3
12 , 9
x i y i
0
2 , 4
5 , 2
11 , 2
15
33 , 8
x i 2
0
1
4
16
25
46
y = 0.165 x + 2.184.Proof of the OLS method
:
(9.2)