The least squares method for finding errors. Method of least squares examples of problem solving

Method smallest squares (MNC) allows you to evaluate different values \u200b\u200busing the results of a set of measurements containing random errors.

Characteristic of MNK.

Main idea this method It is that as the criterion for the accuracy of solving the problem, the sum of the squares of errors, which they strive to minimize are considered. When using this method, both numerical and analytical approach can be applied.

In particular, as a numerical implementation, the least squares method implies how to more Unknown measurements random variable. Moreover, the more calculations, the more accurate will be the solution. At this set of computing (source data), another set of alleged solutions are obtained, from which the best is then selected. If a plurality of solutions to parameterize, then the method of the smallest squares is reduced to the search for the optimal value of the parameters.

As an analytical approach to the implementation of MNA on a plurality of source data (measurements) and the estimated set of solutions, some (functional) is determined, which can be expressed by the formula obtained as a certain hypothesis that requires confirmation. In this case, the method of least squares is reduced to finding a minimum of this functional on the set of squares of source data errors.

Note that not the errors themselves, namely the squares of errors. Why? The fact is that often deviations of measurements from accurate value There are both positive and negative. When determining the average simple summation, it may lead to an incorrect conclusion about the quality of the assessment, since the mutual destruction of positive and negative values \u200b\u200bwill reduce the power of the sampling of the set of measurements. And, consequently, the accuracy of the assessment.

In order not to happen, and summarize the squares of deviations. Even moreover, to level the dimension of the measured value and the final assessment, from the sum of the squares of the errors

Some MNK applications

MNC is widely used in various fields. For example, in probability theory and mathematical statistics The method is used to determine such a challenge of random variable as the average quadratic deviationdetermining the width of the range of random values.

Programming

Tutorial

Introduction

I am a mathematician programmer. I committed the biggest jump in my career when I learned to say: "I do not understand anything!" Now I am not ashamed to tell the luminaire of science, what he reads a lecture that I do not understand what it is, shouted, I tell me. And it is very difficult. Yes, it's difficult to confess in my ignorance and shame. Who will like to admit that he does not know something-there. By virtue of your profession, I must be present on a large number of presentations and lectures, where, I confess, in the overwhelming majority I want to sleep, because I do not understand anything. And I do not understand because the huge problem of the current situation in science lies in mathematics. She suggests that all the listeners are familiar with absolutely all areas of mathematics (which is absurd). To confess that you do not know what a derivative is (about what it is a little later) is a shame.

But I learned to say that I do not know what multiplication is. Yes, I do not know what a subalgebra is above algebra Lee. Yes, I do not know why you need in life quadratic equations. By the way, if you are sure you know, then we have something to talk about! Mathematics is a series of focus. Mathematics try to confuse and intimidate the public; Where there is no confusion, there is no reputation, there is no authority. Yes, it is prestigious to speak as an abstract language as possible that there is a complete nonsense.

Do you know what is derived? Most likely, you tell me about the limit of a difference relationship. In the first year of Matmeach SPbSU Viktor Petrovich Havin me determined The derivative as the coefficient of the first member of the Taylor series of functions at the point (it was separate gymnastics to determine the Taylor series without derivatives). I laughed at such a definition for a long time, as a result, I did not understand what it was. The derivative is nothing but just a measure of how much the function we differentiate is similar to the function y \u003d x, y \u003d x ^ 2, y \u003d x ^ 3.

I now have the honor to lecture students who afraid mathematics. If you are afraid of mathematics - we are with you along the way. Once you are trying to read some text, and it seems to you that it is overly complicated, then you know that he is written. I argue that there is not a single area of \u200b\u200bmathematics, which cannot be said to "on the fingers" without losing the accuracy.

Task for the near future: I instructed my students to understand what is a linear-quadratic regulator. Do not hesitate, spend three minutes of your life, go on the link. If you do not understand anything, then we are with you along the way. I (professional mathematician programmer) also did not understand anything. And I assure it in this one can figure out "on the fingers." On the this moment I do not know what it is, but I assure that we will understand.

So, the first lecture I'm going to read my students after they resort to me in horror with the words that a linear-quadratic regulator is a terrible bjak, which is never to master in life, this methods of the smallest squares. Can you decide linear equations? If you read this text, then there are most likely not.

So, two points (x0, y0), (x1, y1) are given, for example, (1,1) and (3,2), the task to find the equation direct passing through these two points:

illustration

This direct should have the following equation:

Here Alpha and Beta are unknown to us, but two points are known for this straight:

You can write this equation in matrix form:

Here it is necessary to make a lyric retreat: what is the matrix? The matrix is \u200b\u200bnothing but a two-dimensional array. This is a way to store data, no one should give any more values. It depends on us, exactly how to interpret some matrix. Periodically, I will interpret it as a linear mapping, periodically like a quadratic form, and sometimes just like a set of vectors. This will be updated in context.

Let's replace specific matrices on their symbolic representation:

Then (Alpha, Beta) can be easily found:

More specifically for our previous data:

Which leads to the next equation direct passing through points (1.1) and (3.2):

Okay, everything is clear here. And let's find the equation direct passing through three Points: (x0, y0), (x1, y1) and (x2, y2):

Oh oh oh, and we have three equations into two unknown! Standard mathematician will say that the solutions do not exist. What will the programmer say? And he first will rewrite the previous system of equations in the following form:

In our case vectors I, J, B Three-dimensional, therefore, (generally) the solution of this system does not exist. Any vector (Alpha \\ * i + Beta \\ * j) lies in a plane stretched into vectors (i, j). If b does not belong to this plane, then the solutions do not exist (equality in the equation is not achieved). What to do? Let's look for a compromise. Let's indicate through e (Alpha, Beta) As far as we have not reached equality:

And we will try to minimize this error:

Why square?

We are looking for not just at least the norm, and at least the square of the norm. Why? The minimum point itself coincides, and the square gives a smooth function (the quadratic function from the agruit (ALPHA, BETA)), while simply the length gives a function in the form of a cone, undifferentiated at a minimum point. Brr. Square is more convenient.

Obviously, the error is minimized when vector e. orthogonal plane stretched on vectors i. and j..

Illustration

In other words: we are looking for such a straight, that the sum of the squares of the distance lengths from all points to this direct is minimal:

Update: Here I have a jamb, the distance to the straight should be measured by vertical, and not orthogonal projection. Commentar right.

Illustration

Sobsmie in different words (carefully, poorly formalized, but it should be clear on the fingers): we take all the possible straight lines between all pairs of points and looking for an average direct between everyone:

Illustration

An explanation on the fingers: we attach the spring between all the data points (here we have three) and direct that we are looking for, and direct equilibrium state is exactly what we are looking for.

Minimum quadratic form

So having this vector b. and plane stretched on columns matrix vectors A. (In this case (x0, x1, x2) and (1,1,1)), we are looking for a vector e. With minimum square of length. Obviously, the minimum is achievable only for the vector e., orthogonal plane, stretched on columns-vectors of the matrix A.:

In other words, we are looking for such a vector x \u003d (Alpha, Beta) that:

I remind you that this vector x \u003d (Alpha, Beta) is a minimum quadratic function || E (Alpha, Beta) || ^ 2:

Here it will be necessary to remember that the matrix can be interpreted including as a quadratic form, for example, single matrix ((1.0), (0,1)) can be interpreted as a function x ^ 2 + y ^ 2:

quadratic form

All this gymnastics are known under the name of linear regression.

Laplace equation with border condition Dirichlet

Now the simplest real task: there is a certain triangulated surface, it is necessary to smooth it out. For example, let's upload the model of my face:

The initial commote is available. To minimize external dependencies, I took the code of my software renderor, already on Habré. For solutions linear system I use OpenNL, it is an excellent solver, which is true, it is very difficult to install: you need to copy two files (.h + .c) to the folder with your project. All smoothing is done by the following code:

For (int d \u003d 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 & face \u003d faces [i]; for (int j \u003d 0; j<3; j++) { nlBegin(NL_ROW); nlCoefficient(face[ j ], 1); nlCoefficient(face[(j+1)%3], -1); nlEnd(NL_ROW); } } nlEnd(NL_MATRIX); nlEnd(NL_SYSTEM); nlSolve(); for (int i=0; i<(int)verts.size(); i++) { verts[i][d] = nlGetVariable(i); } }

X, Y and Z coordinates are separated, I smooth them separately. That is, I solve three systems of linear equations, each has the number of variables equal to the number of vertices in my model. The first n strings of the matrix A have only one unit on the string, and the first n rows of the vector B have the original coordinates of the model. That is, I bind on the spring between the new position of the vertex and the old position of the vertex - new should not be too far away from the old.

All subsequent lines of the matrix A (faces.size () * 3 \u003d quantity of the edge of all triangles in the grid) have one entry 1 and one entry -1, and the vector B has zero components opposite. This means I hang spring on every edge of our triangular mesh: all the ribs are trying to get the same vertex as the starting and final point.

Once again: all the vertices are variables, and they cannot far away from the original position, but they try to become similar to each other.

Here is the result:

Everything was fine, the model is really smoothed, but she moved away from her original edge. Let's change the code slightly:

For (int i \u003d 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, I am for vertices that are on the edge, add not a string from discharge v_i \u003d verts [i] [d], and 1000 * v_i \u003d 1000 * Verts [i] [d]. What does it change? And changes this to our quadratic form of error. Now a single deviation from the top on the edge will cost not one unit, as before, and 1000 * 1000 units. That is, we hung a stronger spring to extreme peaks, the solution would prefer to stretch more stronger. Here is the result:

Let's double the springs between the vertices:
nlcoefficient (Face [j], 2); nlcoefficient (Face [(J + 1)% 3], -2);

It is logical that the surface has become smoother:

And now another hundred times stronger:

What is it? Imagine that we did the wire ring in soapy water. As a result, the resulting soap film will try to have the smallest curvature, as far as possible, touching the borders - our wire rings. That is what we got, fixing the border and asking to get a smooth surface inside. I congratulate you, we have just solved the Laplace equation with the boundary conditions of Dirichle. Cool sounds? But in fact, only one system of linear equations is solved.

Poisson equation

Let's still remember a cool name.

Suppose I have such a picture:

Everyone is good, only a chair I do not like.

We will cut a picture in half:

And he highlights the chair:

Then, everything that is white in the mask, attracted to the left side of the picture, and at the same time I will say that the difference between two adjacent pixels should be equal to the difference between two adjacent pixels of the right picture:

For (int i \u003d 0; i

Here is the result:

Code and pictures available

Tutorial

Introduction

But I learned to say that I do not know what multiplication is. Yes, I do not know what a subalgebra is above algebra Lee. Yes, I do not know why square equations are needed in life. By the way, if you are sure you know, then we have something to talk about! Mathematics is a series of focus. Mathematics try to confuse and intimidate the public; Where there is no confusion, there is no reputation, there is no authority. Yes, it is prestigious to speak as an abstract language as possible that there is a complete nonsense.

Do you know what is derived? Most likely, you tell me about the limit of a difference relationship. In the first year of Matmeach SPbSU Viktor Petrovich Havin me determined The derivative as the coefficient of the first member of the Taylor series of functions at the point (it was separate gymnastics to determine the Taylor series without derivatives). I laughed at such a definition for a long time, as a result, I did not understand what it was. The derivative is nothing but just a measure of how much the function we differentiate is similar to the function y \u003d x, y \u003d x ^ 2, y \u003d x ^ 3.

Task for the near future: I instructed my students to understand what is a linear-quadratic regulator. Do not hesitate, spend three minutes of your life, go on the link. If you do not understand anything, then we are with you along the way. I (professional mathematician programmer) also did not understand anything. And I assure it in this one can figure out "on the fingers." At the moment I do not know what it is, but I assure that we will understand.

So, the first lecture I'm going to read my students after they resort to me in horror with the words that a linear-quadratic regulator is a terrible bjak, which is never to master in life, this methods of the smallest squares. Do you know how to solve linear equations? If you read this text, then there are most likely not.

So, two points (x0, y0), (x1, y1) are given, for example, (1,1) and (3,2), the task to find the equation direct passing through these two points:

illustration

This direct should have the following equation:

Here Alpha and Beta are unknown to us, but two points are known for this straight:

You can write this equation in matrix form:

Let's replace specific matrices on their symbolic representation:

Then (Alpha, Beta) can be easily found:

More specifically for our previous data:

Which leads to the next equation direct passing through points (1.1) and (3.2):

Okay, everything is clear here. And let's find the equation direct passing through three Points: (x0, y0), (x1, y1) and (x2, y2):

In our case, vectors I, J, B three-dimensional, therefore, (generally) the solution of this system does not exist. Any vector (Alpha \\ * i + Beta \\ * j) lies in a plane stretched into vectors (i, j). If b does not belong to this plane, then the solutions do not exist (equality in the equation is not achieved). What to do? Let's look for a compromise. Let's indicate through e (Alpha, Beta) As far as we have not reached equality:

And we will try to minimize this error:

Why square?

Obviously, the error is minimized when vector e. orthogonal plane stretched on vectors i. and j..

Illustration

In other words: we are looking for such a straight, that the sum of the squares of the distance lengths from all points to this direct is minimal:

Update: Here I have a jamb, the distance to the straight should be measured by vertical, and not orthogonal projection. This commentator is right.

Illustration

Minimum quadratic form

In other words, we are looking for such a vector x \u003d (Alpha, Beta) that:

I remind you that this vector X \u003d (Alpha, Beta) is a minimum of a quadratic function || E (Alpha, Beta) || ^ 2:

Here it will be useful to remember that the matrix can be interpreted including as a quadratic form, for example, a single matrix ((1.0), (0.1)) can be interpreted as a function x ^ 2 + y ^ 2:

quadratic form

All this gymnastics are known under the name of linear regression.

Laplace equation with border condition Dirichlet

Now the simplest real task: there is a certain triangulated surface, it is necessary to smooth it out. For example, let's upload the model of my face:

The initial commote is available. To minimize external dependencies, I took the code of my software renderor, already on Habré. To solve the linear system, I use OpenNL, it is an excellent solver, which is true, it is very difficult to install: you need to copy two files (.h + .c) to the folder with your project. All smoothing is done by the following code:

Once again: all the vertices are variables, and they cannot far away from the original position, but they try to become similar to each other.

Here is the result:

Everything was fine, the model is really smoothed, but she moved away from her original edge. Let's change the code slightly:

For (int i \u003d 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); }

Let's double the springs between the vertices:
nlcoefficient (Face [j], 2); nlcoefficient (Face [(J + 1)% 3], -2);

It is logical that the surface has become smoother:

And now another hundred times stronger:

Poisson equation

Let's still remember a cool name.

Suppose I have such a picture:

Everyone is good, only a chair I do not like.

We will cut a picture in half:

And he highlights the chair:

For (int i \u003d 0; i

Here is the result:

Code and pictures available

Example.

Experimental data on variable values h.and w.led in the table.

As a result of their alignment, a function was obtained

Using least square method, approximate this data linear dependence y \u003d AX + B (Find Parameters but and b.). Find out which of the two lines is better (in the sense of the least squares method) aligns experimental data. Make a drawing.

The essence of the least squares method (MNC).

The task is to find the coefficients of linear dependence in which the function of two variables but and b. takes the smallest value. That is, with data but and b. The sum of the squares of the deviations of the experimental data from the direct line will be the smallest. This is the whole essence of the least squares method.

Thus, the example solution comes down to finding the extremum function of two variables.

Displays the formula for finding coefficients.

A system of two equations with two unknowns is compiled and solved. We find private derivatives by variables but and b., equate these derivatives to zero.

Solve the resulting system of equations by any method (for example for a substitution method or cramer method) And we obtain formulas for finding the coefficients using the least squares method (MNC).

With data butand b.function takes the smallest value. Proof of this fact is given below in the text at the end of the page.

That is the whole method of least squares. Formula for finding a parameter a. contains amounts ,,, and parameter n. - Number of experimental data. The values \u200b\u200bof these sums are recommended to calculate separately. Coefficient b. Located after calculation a..

It's time to remember about the source example.

Decision.

In our example n \u003d 5.. Fill out a table for the convenience of calculating amounts that are included in the formula of the desired coefficients.

Values \u200b\u200bin the fourth line of the table are obtained by multiplying the values \u200b\u200bof the 2nd string to the values \u200b\u200bof the 3rd string for each number i..

Values \u200b\u200bin the fifth line of the table are obtained by the construction of the 2nd string values \u200b\u200bfor each number. i..

The values \u200b\u200bof the last column of the table are the sums of values \u200b\u200bby lines.

We use the formulas of the least squares method for finding coefficients but and b.. We substitute the corresponding values \u200b\u200bfrom the last column of the table:

Hence, y \u003d 0.165x + 2.184 - The desired approximating straight line.

It remains to find out which of the lines y \u003d 0.165x + 2.184 or it is better to approximate the initial data, that is, it is estimated by the method of smallest squares.

Assessment of the error of the least squares method.

This requires to calculate the sums of the squares of the deviations of the source data from these lines. and A smaller value corresponds to a line that is better in the sense of the smaller square method approximates the source data.

Since, then straight y \u003d 0.165x + 2.184 Better brings the source data.

Graphic illustration of the least squares method (MNC).

On the charts everything is perfectly visible. The red line is the found straight y \u003d 0.165x + 2.184, blue line is Pink dots are the source data.

In practice, when modeling various processes - in particular, economic, physical, technical, social - certain methods for calculating the approximate values \u200b\u200bof functions according to their known values \u200b\u200bin some fixed points are widely used.

This kind of tasks of the approximation of functions often occur:

when constructing approximate formulas for calculating the values \u200b\u200bof the characteristic values \u200b\u200bof the under study on table data obtained as a result of the experiment;

with numerical integration, differentiation, solving differential equations, etc.;

if you need to calculate the values \u200b\u200bof functions at the intermediate points of the interval under consideration;

when determining the values \u200b\u200bof the characteristic values \u200b\u200bof the process outside the interval under consideration, in particular during forecasting.

If to simulate some process specified by the table, to construct a function that approximately describing this process based on the least squares method, it will be called an approximating function (regression), and the task of constructing approximating functions is the task of approximation itself.

This article discusses the capabilities of the MS Excel package to solve this kind of tasks, in addition, methods and methods of constructing (creation) regressions are given for tables specified functions (which is the basis of regression analysis).

In Excel, there are two possibilities for the construction of regressions.

Adding selected regressions (trend lines - Trendlines) in a diagram based on the data table for the process being studied (available only in the presence of a built diagram);

Using the built-in statistical functions of the Excel working sheet, allowing to obtain regression (trend lines) directly based on the source data table.

Adding a trend lines in a chart

For the data table, describing some process and presented by the diagram, there is an effective regression analysis tool in Excel, which allows:

build on the basis of the method of least squares and add five types of regressions to a diagram, which with one degree of accuracy simulate the process under study;

add to the diagram the equation of constructed regression;

determine the degree of compliance of the selected regression displayed on the data diagram.

Based on the EXCEL diagram data, it allows to obtain a linear, polynomial, logarithmic, power, exponential types of reggesters, which are given by the equation:

y \u003d y (x)

where X is an independent variable, which often takes the values \u200b\u200bof the sequence of a natural range of numbers (1; 2; 3; ...) and produces, for example, counting the course of the process under study (characteristics).

1 . Linear regression is good when modeling the characteristics whose values \u200b\u200bincrease or decrease with a constant speed. This is the most simple in the construction of the process under study. It is built in accordance with the equation:

y \u003d MX + B

where m - tangent angle of inclination linear regression to the abscissa axis; B is the coordinate of the intersection point of linear regression with the owner of the ordinate.

2 . The trend polynomial line is useful for describing the characteristics having several pronounced extremes (maxima and minima). The choice of the degree of polynomial is determined by the amount of extremums of the studied characteristics. So, the second degree polynomial can well describe a process with only one maximum or a minimum; polynomial of the third degree - no more than two extremes; polynomial fourth degree - no more than three extremes, etc.

In this case, the trend line is based in accordance with equation:

y \u003d C0 + C1X + C2X2 + C3X3 + C4X4 + C5X5 + C6X6

where the coefficients C0, C1, C2, ... C6 are constants, the values \u200b\u200bof which are determined during the construction.

3 . The logarithmic line of the trend is successfully used when modeling the characteristics, the values \u200b\u200bof which first change quickly, and then gradually stabilize.

y \u003d C ln (x) + b

4 . The trend's power line gives good results if the values \u200b\u200bof the dependence of the dependence are characterized by a constant change in the growth rate. An example of such a dependence can be a graph of the equilibrium movement of the car. If there are zero or negative values \u200b\u200bamong the data, it is impossible to use a power trend.

Constructed in accordance with equation:

y \u003d c xb

where the coefficients B, C - constants.

5 . The trend exponential line should be used if the data change rate increases continuously. For data containing zero or negative values, this type of approximation is also not applicable.

Constructed in accordance with equation:

y \u003d C EBX

where the coefficients B, C - constants.

When selecting the Excel trend line automatically calculates the value of the value R2, which characterizes the accuracy of the approximation: the closer the value R2 to one, the more reliable the line of the trend approximates the process under study. If necessary, the R2 value can always be displayed on the diagram.

Determined by the formula:

To add a trend line to a number of data:

activate the diagram built on the basis of a number of data, i.e., click within the diagram area. In the main menu, the paragraph will appear;

after clicking at this point, a menu will appear on the screen in which the Add Trend line command should be selected.

The same actions are easy to implement if you bring the mouse pointer to a chart corresponding to one of the data series, and right-click; In the context menu that appears, select the Add Trend line. The Trend line dialog box with the type opened type dialog box appears (Fig. 1).

After that you need:

Select the type of Trend Type type on the Type tab (the default linear type is selected). For the type polynomial in the degree field, specify the degree of selected polynomial.

1 . The field built on a number lists all the number of the data of the diagram under consideration. To add a trend line to a specific number of data, the field is built in the field to select its name.

If necessary, by going to the Options 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.

set the number of periods (forward or backward) to predict the forecast field;

to bring the trend line equation to the chart to the chart, for what should be enabled checkbox Show equation on the diagram;

to display the value of the accuracy of the approximation R2 to the chart, for which it should be enabled to put the value for the diagram the value of the accuracy of approximation (R ^ 2);

set the point of intersection of the trend line with the Y axis, for which you should enable the checkbox in crossing the curve with the Y axis at the point;

click on the OK button to close the dialog box.

In order to start editing the already constructed trend line, there are three ways:

take advantage of the selected trend line from the format menu, after selecting the trend line;

select the trend line format command from the context menu, which is called by clicking the right mouse button along the trend line;

double-clicking along the trend line.

The Trend Line (Fig. 3) dialog box appears on the screen, containing three tabs: view, type, parameters, and the contents of the last two completely coincides with the same tabs of the Trend line dialog box (Fig. 1-2). On the View tab, you can specify the type of line, its color and thickness.

To delete the already built trend line, select a deleted trend line and press the Delete key.

The advantages of the examined regression analysis tool are:

relative ease of construction on trend line diagrams without creating a data table for it;

a fairly wide list of types of proposed trend lines, and this list includes the most frequently used regression types;

the ability to predict the behavior of the under study to arbitrary (within common sense) the number of steps forward, as well as back;

the possibility of obtaining the equation of the trend line in analytical form;

the possibility, if necessary, obtain an assessment of the accuracy of the approximation conducted.

The following points include disadvantages:

the construction of the trend line is carried out only if there is a diagram built on a number of data;

the process of forming data series for the studied characteristic based on the trend lines obtained for it is somewhat clouded: the desired regression equations are updated with each change of the values \u200b\u200bof the source row of data, but only within the diagram area, while a number of data formed on the basis of the old line equation trend, remains unchanged;

in the reports of consolidated diagrams when changing the diagram or related reports, the existing trend lines are not saved, that is, before holding a trend lines or other formatting of the summary diagrams, make sure that the report layout satisfies the necessary requirements.

Trend lines include data rows, presented on chart diagrams, histogram, flat non-normalized charts with regions, line, point, bubble and stock exchange.

It is impossible to supplement the trend lines of data on bulk, normalized, petal, circular and ring diagrams.

Using Extra Excel Functions

Excel also has a regression analysis tool for constructing a trend lines outside the diagram area. For this purpose, you can use a number of statistical functions of the working sheet, but they all allow us to build only linear or exponential regressions.

Excel has several functions for building linear regression, in particular:

TREND;

Tilt and cut.

As well as several functions for constructing the trend exponential line, in particular:

LGRFPribl.

It should be noted that the methods of constructing regressions using the functions trends and growth practically coincide. The same can be said about the pair of Linene and LGRFPribl. For four of these functions, when creating a table of values, Excel features are used as formulas for arrays, which slightly climbs the process of constructing regression. We also note that the construction of linear regression, in our opinion, is easiest to be easier to carry out the tilt and segment using functions, where the first of them determines the angular coefficient of linear regression, and the second is a segment cut off by regression on the ordinate axes.

The advantages of the tool of the built-in functions for regression analysis are:

a fairly simple single type of formation of the data series of the studied characteristics for all built-in statistical functions that specify the trend lines;

standard method of constructing a trend lines based on formed data series;

the ability to predict the behavior of the process under study to the required number of steps forward or backward.

And the disadvantages include the fact that there are no embedded functions in Excel to create others (except linear and exponential) types of trend lines. This circumstance often does not allow you to choose a fairly accurate model of the process under study, as well as to obtain projections close to reality. In addition, when using functions, the trend and growth is not known to the equation of the trend lines.

It should be noted that the authors did not set the purpose of the article to present the course of regression analysis with one degree of completeness. Its main task - on specific examples, show the features of the Excel package when solving approximation tasks; Demonstrate what effective tools to build regressions and forecasting has excel; Illustrate, as relatively easy, such tasks can be solved even by a user who do not speak the profound knowledge of regression analysis.

Examples of solving specific tasks

Consider a solution to specific tasks using the enumerated Excel package tools.

Task 1.

With the table of data on the profit of the motor transportation for 1995-2002. The following steps must be performed.

Build a chart.

Add linear and polynomial (quadratic and cubic) trend line to the diagram.

Using the equations of the trend lines, to obtain tabular data on the profit of the enterprise for each Trend line for 1995-2004.

Create a forecast on the profit of the company for 2003 and 2004.

The solution of the problem

In the range of cells A4: C11, the Excel worksheet is introduced the work table shown in Fig. four.

Having allocate 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) alternately add a linear, quadratic and cubic trend line to a diagram. In the same dialog box, open the parameters tab (see Fig. 2), in the Name field of the approximating (smoothed) curve, enter the name of the trend added, and in the Forecast field forward to: periods set to 2, as it is planned to make a forecast for profits for two years ahead. To display the regression equation equation and the validity of the approximation R2 equation, turn on the flags to show the equation on the screen and put on the diagram the value of the accuracy of the approximation (R ^ 2). For better visual perception, we change the type, color and thickness of the built trend lines, for which we use the tab View of the dialog box format the trend line (see Fig. 3). The obtained diagram with the added trend lines is presented in Fig. five.

To obtain tabular data on the profit of the enterprise for each Trend line for 1995-2004. We use the equations of the trend lines presented in Fig. 5. To do this, in the range of D3: F3, we enter text information about the type of the selected trend line: linear trend, a quadratic trend, a cubic trend. Next, we enter a linear regression formula into the cell D4 and, using the filling marker, copy this formula with relative references in the range of cells D5: D13. It should be noted that each cell with a linear regression formula from the range of cells D4: D13 is the corresponding cell from the A4 range: A13 as an argument. Similarly, for quadratic regression, the range of cells E4: E13 is filled, and for cubic regression - the range of cells F4: F13. Thus, the forecast on the profit of the enterprise for 2003 and 2004 was drawn up. With the help of three trends. The obtained table of values \u200b\u200bis presented in Fig. 6.

Task 2.

Build a chart.

In the diagram add logarithmic, power and exponential trend lines.

Release the equation of the trend lines obtained, as well as the validity of the approximation R2 for each of them.

Using the trend lines equations, to obtain table data on the company's profits for each Trend line for 1995-2002.

To make a forecast for the profit of the company for 2003 and 2004, using these trend lines.

The solution of the problem

Following the method described in solving problems 1, we obtain a diagram with the logarithmic, power and exponential trend lines (Fig. 7) added to it (Fig. 7). Next, using the trend lines obtained, fill in the value table of the enterprise's profits, including predicted values \u200b\u200bfor 2003 and 2004. (Fig. 8).

In fig. 5 and rice. It can be seen that models with a logarithmic trend corresponds to the smallest value of the accuracy of approximation

R2 \u003d 0,8659.

The greatest R2 values \u200b\u200bcorrespond to models with a polynomial trend: quadratic (R2 \u003d 0.9263) and cubic (R2 \u003d 0.933).

Task 3.

With a table of data on the profit of a motor transportation enterprise for 1995-2002, given in Problem 1, the following steps must be performed.

Get the series of data for the linear and exponential trend line using the functions trend and growth.

Using the trend and growth features, make a forecast for the company's profits for 2003 and 2004.

For the source data and the received data series to build a diagram.

The solution of the problem

We use the working table of tasks 1 (see Fig. 4). Let's start the trend:

select the range of cells D4: D11, which should be filled with the values \u200b\u200bof the function the trend corresponding to the known data on the profit of the enterprise;

call the command Function from the Insert menu. In the dialog box that appears, the Function Wizard allocate the Trend function from the statistical category, then click on the OK button. The same operation can be performed by pressing the button (Function Insert) of the standard toolbar.

In the dialog box that appears, the function arguments enter in the field known_stations_y the range of cells C4: C11; In the field of known_stations_x - the range of cells B4: B11;

in order for the resulting formula to become an array formula, we use the + + key combination.

The formula introduced by us in the formula string will be: \u003d (trend (C4: C11; B4: B11)).

As a result, the range of cells D4: D11 is filled with the corresponding values \u200b\u200bof the Trend function (Fig. 9).

To prepare a forecast about the company's profits for 2003 and 2004. Need:

select the range of cells D12: D13, where the values \u200b\u200bpredicted by the trend function will be entered.

call the Trend function and in the Arguments dialog box that appears, Introducts the known_y_s_n - the range of cells C4: C11; In the field of known_stations_x - the range of cells B4: B11; And in the field of new_stations_x - the range of cells B12: B13.

turn this formula in the solve formula using the CTRL + SHIFT + ENTER key combination.

The introduced formula will be viewed: \u003d (trend (C4: C11; B4: B11; B12: B13)), and the range of cells D12: D13 will be filled with the predicted values \u200b\u200bof the function the trend (see Fig. 9).

Similarly, a number of data are filled with a function that is used when analyzing nonlinear dependencies and works in the same way as its linear analogue trend.

Figure 10 presents a table in the display mode of formulas.

For the source data and the obtained data series, the diagram shown in fig. eleven.

Task 4.

With a table of data on admission to the dispatch service of a motor transportation enterprise for services for the period from 1 to 11, the number of the current month must be performed.

Get data series for linear regression: Using Functions Tilt and Cut; Using the linear function.

Get a number of data for exponential regression using the LGRFPrble function.

Using the above functions, make a forecast for receipt of applications to the dispatch service for the period from 12 to 14 the number of the current month.

For the initial and received data series to build a chart.

The solution of the problem

Note that, in contrast to the functions, the trend and growth, none of the functions listed above (slope, segment, linear, LGRFRIB) is not a regression. These functions play only an auxiliary role, determining the necessary regression parameters.

For linear and exponential regression, built with the help of slope, segment, linear, LGRFPRIB, the appearance of their equations is always known, in contrast to linear and exponential regressions corresponding to the trend and growth features.

1 . We construct a linear regression having an equation:

y \u003d MX + B

using the functions, the slope and segment, with the angular regression coefficient M, is determined by the slope function, and the free member B is the function of the segment.

To do this, carry out the following actions:

we introduce the source table in the range of cells A4: B14;

the value of the M parameter will be determined in the C19 cell. Select the statistical function of the slope from the category; We introduce the range of cells B4: B14 in the Field of the known_stations_y and the range of cells A4: A14 in the Field of the known_stations_x. The formula will be introduced into the C19 cell: \u003d tilt (B4: B14; A4: A14);

by a similar method, the value B parameter is determined in the D19 cell. And its contents will be viewed: \u003d Cut (B4: B14; A4: A14). Thus necessary to construct linear regression, the parameters M and B will be maintained accordingly in cells C19, D19;

next we enter into the C4 cell formula of linear regression in the form: \u003d $ C * A4 + $ d. In this formula, the C19 and D19 cell is recorded with absolute references (the cell's address should not change with possible copying). An absolute reference sign can be fill either from the keyboard or using the F4 key, after setting the cursor on the cell's address. Taking advantage of the filling marker, copy this formula in the range of C4 cells: C17. We obtain a desired series of data (Fig. 12). Due to the fact that the number of applications is an integer, you should set the number of the window of the cell format of the numerical format with the number of decimal characters 0.

2 . Now we construct a linear regression given by the equation:

y \u003d MX + B

using the linear function.

For this:

we enter in the C20: D20 cell range function as a linear formula: \u003d (Linene (B4: B14; A4: A14)). As a result, we obtain in the C20 cell the value M parameter, and in the D20 cell - the value of the parameter B;

we introduce into the D4 cell formula: \u003d $ C * A4 + $ D;

copy this formula using a filling marker in the range of cells D4: D17 and we get a desired data series.

3 . We build an exponential regression having an equation:

using the LGRFPrib function, it is performed similarly:

in the range of cells C21: D21, we enter the function of the LGRFPRIBL as the formula of the array: \u003d (LGRFPRIBLIN (B4: B14; A4: A14)). In this case, in the C21 cell, the value M parameter will be determined, and in the D21 cell - the value of the parameter B;

the formula is introduced into the E4 cell: \u003d $ D * $ C ^ A4;

using the filling marker, this formula is copied to the E4 cell range: E17, where there will be a number of data for exponential regression (see Fig. 12).

In fig. 13 shows a table where the functions we use with the necessary ranges of cells are visible, as well as formulas.

Value R. 2 called coefficient of determination.

The task of constructing a regression dependence is to find the vector of coefficients M model (1) in which the coefficient R takes the maximum value.

To assess the significance of R, the Fisher's Fischer's F-Criteria is used, calculated by the formula

where n. - sample size (number of experiments);

k - the number of model coefficients.

If f exceeds some critical value for data n. and k. and adopted trust probability, then the value of R is considered essential. Tables of critical values \u200b\u200bF are given in reference books on mathematical statistics.

Thus, the significance of R is determined not only by its value, but also by the relationship between the amount of experiments and the number of coefficients (parameters) of the model. Indeed, the correlation ratio for n \u003d 2 for a simple linear model is 1 (after 2 points on the plane can always be carried out the only straight). However, if the experimental data are random values, trust such a value of R with great caution. Usually, for obtaining significant R and reliable regression, they strive to ensure that the number of experiments significantly exceeds the number of model coefficients (N\u003e K).

To build a linear regression model, it is necessary:

1) Prepare a list of N strings and M columns containing experimental data (column comprising an output value Y.must be either first or the last on the list); For example, we take the previous task data by adding a column with the name "period number", number the number of periods from 1 to 12. (these will be values H.)

2) Contact the Data / Data Analysis / Regression menu

If the "Data Analysis" item in the "Service" menu is missing, you should contact the "add-in" of the same menu and check the "Analysis Package" checkbox.

3) In the "Regression" dialog box:

· Input interval Y;

· Input interval X;

· The output interval is the upper left cell of the interval in which the results of the calculations will be placed (it is recommended to place on a new work sheet);

4) Press "OK" and analyze the results.

3.5. Least square method

The first work in which the foundations of the least squares method are laid, was performed by Legandrom in 1805. The article "New Methods for Definition of Orbits Comet", he wrote: "After all the terms of the task are fully utilized, it is necessary to determine the coefficients so that their errors are necessary were the smallest possible. The simplest way to achieve this is the method that consumes the minimum of the sum of the squares of the error.

Suppose on the basis of the experiment, it is required to establish the functionality of the valuey from x :. Ipow the experiment receivedn. Values y. With the corresponding values \u200b\u200bof the argumentx.. If the experimental points are located on the coordinate plane as in the figure, then, knowing that an error occurred during the experiment, it can be assumed that the dependence is linear in nature, i.e.y.= aX.+ b.. This method does not impose restrictions on the type of function, i.e. It can be applied to any functional dependency.

From the point of view, experimentally, it is more natural to assume that the sequence of counting countsfixed in advance, i.e. is an independent variable, acceptance - dependent variable. This is especially clearly visible, if understandments of the time that the most widely has places of technical applications. But this is only a very common case. For example, it is necessary to carry out a classification of some samples in size. Then an independent variable will be the sample number, dependent - its individual size.

The method of smallest squares is described in detail in a variety of educational and scientific publications, especially in terms of approximation of electric and radio engineering functions, as well as in books on probability theory and mathematical statistics.

Friend drawing. Dotted lines show that there may be not only because of the proceeding procedures, but because of the inaccuracy of the task of an independent variable. In the selected form of the function it remains to select the incoming parameters in it.a.and b.It is true that the number of parameters can be more than two, which is characteristic only for linear functions. In general, we will assume

.(1)

It is required to choose coefficientsa., b., c.... so that the condition is

. (2)

Find values a., b., c...., contacting the left side (2) in a minimum. To do this, we define stationary points (points in the first derivative turns into zero) by differentiate the left part (2) bya., b., c.:

(3)

etc. The initial system of equations contains as many as evils as unknowna., b., c..... It is impossible to solve such a system in general, so it is necessary to specify at least approximately, a specific type of function. Consider two cases: linear and quadratic functions.

Linear Function .

Consider the sum of the squares of the difference differences and function values \u200b\u200bat the respective points:

(4)

We will select the parametersa.and b.so that this amount has the smallest meaning. Thus, it is asked for finding valuesa.and b.in which the function has a minimum, i.e. on the study of the function of two independent variablesa.and b. at a minimum. To do this, usea.and b.:

;

(5)

Substituting the experimental data and, we obtain a system of two linear equations with two unknowna.and b.. By deciding this system, we will be able to record the function.

Correct that with the values \u200b\u200bfounda.and b. has a minimum. For this we find, and:

, , .

Hence,

− = ,

>0,

those. A sufficient minimum condition is made for the function of two variables.

Quadratic function .

Suppose in the experiment, the values \u200b\u200bof the function are obtained at points. Also, on the basis of a priori information, it is assumed that the function is quadratic:

It is required to find factorsa., b.and c..Imem

- Function of three variablesa., b., c..

In this case, the system (3) takes the form:

Or:

Deciding this system of linear equations, we define unknowna., b., c..

Example. Let the four values \u200b\u200bof the desired function obtained on the basis of the experimenty \u003d (x ) With four values \u200b\u200bof the argument, which are shown in the table: