The least squares method (LSM) is based on minimizing the sum of squared deviations of the selected function from the data under study. In this article, we approximate the available data using a linear functiony = a x + b .

Least square method(English) Ordinary Least Squares , OLS) is one of the basic methods of regression analysis in terms of estimating unknown parameters regression models according to sample data.

Consider approximation by functions depending on only one variable:

• Linear: y=ax+b (this article)
• : y=a*Ln(x)+b
• : y=a*x m
• : y=a*EXP(b*x)+c
• : y=ax 2 +bx+c

Note: Cases of approximation by a polynomial from the 3rd to the 6th degree are considered in this article. Approximation by a trigonometric polynomial is considered here.

Linear dependency

We are interested in the relationship of 2 variables X and y. There is an assumption that y depends on X according to the linear law y = ax + b. To determine the parameters of this relationship, the researcher made observations: for each value of x i, a measurement of y i was made (see example file). Accordingly, let there be 20 pairs of values ​​(х i ; y i).

Note: If the change step by X is constant, then to build scatterplots can be used, if not, then you need to use the chart type dotted .

It is obvious from the diagram that the relationship between the variables is close to linear. To understand which of the many straight lines most "correctly" describes the relationship between variables, it is necessary to determine the criterion by which the lines will be compared.

As such a criterion, we use the expression:

where ŷ i = a * x i + b ; n – number of pairs of values ​​(in our case n=20)

The above expression is the sum of the squared distances between the observed values ​​of y i and ŷ i and is often denoted as SSE ( sum of squared Errors (Residuals), sum of squared errors (residuals)) .

Least square method is to select such a line ŷ = ax + b, for which the above expression takes the minimum value.

Note: Any line in two-dimensional space is uniquely determined by the values ​​of 2 parameters: a (slope) and b (shift).

It is believed that the smaller the sum of the squared distances, the better the corresponding line approximates the available data and can be further used to predict the values ​​of y from the variable x. It is clear that even if in reality there is no relationship between the variables or the relationship is nonlinear, then the least squares will still select the “best” line. Thus, the LSM does not say anything about the presence of a real relationship of variables, the method simply allows you to choose such function parameters a and b , for which the above expression is minimal.

Having done not very complex mathematical operations (see for more details), you can calculate the parameters a and b :

As can be seen from the formula, the parameter a is the ratio of covariance and , so in MS EXCEL to calculate the parameter a You can use the following formulas (see example file sheet Linear):

= COVAR(B26:B45;C26:C45)/ VAR.G(B26:B45) or

= COVARIATION.B(B26:B45;C26:C45)/VAR.B(B26:B45)

Also to calculate the parameter a you can use the formula = SLOPE(C26:C45;B26:B45). For parameter b use formula = INTERCUT(C26:C45;B26:B45) .

And finally, the LINEST() function allows you to calculate both parameters at once. To enter a formula LINEST(C26:C45;B26:B45) select 2 cells in a row and press CTRL + SHIFT + ENTER(see article about). The left cell will return the value a , on the right b .

Note: To not mess with input array formulas you will need to additionally use the INDEX() function. Formula = INDEX(LINEST(C26:C45,B26:B45),1) or just = LINEST(C26:C45;B26:B45) will return the parameter responsible for the slope of the line, i.e. a . Formula = INDEX(LINEST(C26:C45,B26:B45),2) will return the parameter responsible for the intersection of the line with the Y axis, i.e. b .

After calculating the parameters, scatterplot line can be drawn.

Another way to draw a straight line using the least squares method is the chart tool trend line. To do this, select the diagram, select from the menu Layout tab, in group Analysis click trend line, then Linear approximation .

By checking the box "show equation in the diagram" in the dialog box, you can make sure that the parameters found above match the values ​​in the diagram.

Note: In order for the parameters to match, the chart type must be . The fact is that when constructing a diagram Schedule x-axis values ​​cannot be set by the user (the user can only specify labels that do not affect the location of the points). Instead of X values, the sequence 1 is used; 2; 3; … (for category numbering). Therefore, if building trend line on the type diagram Schedule, then the values ​​of this sequence will be used instead of the actual values ​​of X, which will lead to an incorrect result (unless, of course, the actual values ​​of X do not match the sequence 1; 2; 3; ...).

Well, at work they reported to the inspection, the article was written at home for the conference - now you can write in the blog. While I was processing my data, I realized that I could not help but write about a very cool and necessary add-in in Excel, which is called . So the article will be devoted to this particular add-in, and I will tell you about it using an example of using least squares method(LSM) to search for unknown coefficients of the equation in the description of experimental data.

How to enable the add-on "search for a solution"

First, let's figure out how to enable this add-on.

1. Go to the "File" menu and select "Excel Options"

2. In the window that appears, select "Search for a solution" and click "go".

3. In the next window, put a checkmark in front of the "search for a solution" item and click "OK".

4. The add-in is activated - now it can be found in the "Data" menu item.

Least square method

Now briefly about least squares method (LSM) and where it can be applied.

Let's say we have a data set after we have performed some experiment where we studied the effects of the X value on the Y value.

We want to describe this influence mathematically, so that later we can use this formula and know that if we change the value of X by so much, we will get the value of Y such and such ...

Let's take a super-simple example (see picture).

No brainer that the points are located one after another as if in a straight line, and therefore we safely assume that our dependence is described by a linear function y=kx+b. At the same time, we are sure that when X is equal to zero, the value of Y is also equal to zero. This means that the function describing the dependence will be even simpler: y=kx (remember the school curriculum).

In general, we have to find the coefficient k. This is what we will do with MNC using the "search for a solution" add-on.

The method is to (here - attention: you need to think about it) the sum of the squared differences between the experimentally obtained and the corresponding calculated values ​​was minimal. That is, when X1=1 the actual measured value Y1=4.6, and the calculated y1=f (x1) is 4, the square of the difference will be (y1-Y1)^2=(4-4.6)^2=0.36 . Same with the following: when X2=2, the actual measured value Y2=8.1, and the calculated y2 is 8, the square of the difference will be (y2-Y2)^2=(8-8.1)^2=0.01. And the sum of all these squares should be as small as possible.

So, let's start training on the use of LSM and Excel add-ins "search for solution" .

Application of add-in find solution

1. If you didn’t enable the “search for a solution” add-on, then return to step How to enable the add-on "search for a solution" and enable 🙂

2. In cell A1, enter the value "1". This unit will be the first approximation to the real value of the coefficient (k) of our functional dependence y=kx.

3. In column B we have the values ​​of the parameter X, in column C - the values ​​of the parameter Y. In the cells of column D we enter the formula: “coefficient k multiplied by the value X”. For example, in cell D1, enter "=A1*B1", in cell D2, enter "=A1*B2", and so on.

4. We believe that the coefficient k is equal to one and the function f (x) \u003d y \u003d 1 * x is the first approximation to our solution. We can calculate the sum of squared differences between the measured values ​​of Y and those calculated using the formula y=1*x. We can do all this manually by driving the appropriate cell references into the formula: "=(D2-C2)^2+(D3-C3)^2+(D4-C4)^2... etc. In the end we are mistaken and understand that we have lost a lot of time.In Excel, for calculating the sum of squared differences, there is a special formula, "SUMQDIFF", which will do everything for us.Let's enter it in cell A2 and set the initial data: the range of measured values ​​Y (column C) and range of calculated Y values ​​(column D).

4. The sum of the differences of the squares was calculated - now go to the "Data" tab and select "Search for a solution".

5. In the menu that appears, select cell A1 as the cell to be changed (the one with the coefficient k).

6. As the target, select cell A2 and set the condition "set equal to the minimum value." Remember that this is the cell where we calculate the sum of the squared differences between the calculated and measured values, and this amount should be minimal. We press "execute".

7. Coefficient k is selected. Now it can be seen that the calculated values ​​are now very close to the measured ones.

P.S.

In general, of course, for the approximation of experimental data in Excel, there are special tools that allow you to describe the data using a linear, exponential, power and polynomial function, so you can often do without add-ons "Search for a solution". I talked about all these methods of approximation in my article, so if you are interested, take a look. But when it comes to some exotic function with one unknown coefficient or optimization problems, then here superstructure as well as possible.

Add-in "search for a solution" can be used for other tasks, the main thing is to understand the essence: there is a cell where we select a value, and there is a target cell in which a condition is set for selecting an unknown parameter.
That's all! In the next article I will tell a fairy tale about a vacation, so in order not to miss the release of the article,

4.1. Using built-in functions

calculation regression coefficients carried out using the function

LINEST(Values_y; Values_x; Konst; statistics),

Values_y- array of y values,

Values_x- optional array of values x if array X omitted, it is assumed that this is an array (1;2;3;...) of the same size as Values_y,

Konst- a boolean value that indicates whether the constant is required b was equal to 0. If Konst has the meaning TRUE or omitted, then b calculated in the usual way. If the argument Konst is FALSE, then b is assumed to be 0 and the values a are chosen so that the relation y=ax.

Statistics- a boolean value that indicates whether additional regression statistics are required to be returned. If the argument Statistics has the meaning TRUE, then the function LINEST returns additional regression statistics. If the argument Statistics has the meaning FALSE or omitted, then the function LINEST returns only the coefficient a and permanent b.

It must be remembered that the result of the functions LINEST() is a set of values ​​- an array.

For calculation correlation coefficient function is used

CORREL(Array1;Array2),

returning the values ​​of the correlation coefficient, where Array1- array of values y, Array2- array of values x. Array1 and Array2 must be the same size.

EXAMPLE 1. Addiction y(x) is presented in the table. Build regression line and calculate correlation coefficient.

y		0.5		1.5		2.5		3.5
x		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

Let's enter a table of values ​​into MS Excel sheet and build a scatter plot. The worksheet will take the form shown in Fig. 2.

In order to calculate the values ​​of the regression coefficients a and b select cells A7:B7, let's turn to the function wizard and in the category Statistical choose a function LINEST. Fill in the dialog box that appears as shown in Fig. 3 and press OK.

As a result, the calculated value will appear only in the cell A6(Fig. 4). For a value to appear in a cell B6 you need to enter edit mode (key F2) and then press the key combination CTRL+SHIFT+ENTER.

To calculate the value of the correlation coefficient per cell C6 the following formula was introduced:

C7=CORREL(B3:J3;B2:J2).

Knowing the regression coefficients a and b calculate the values ​​of the function y=ax+b for given x. To do this, we introduce the formula

B5=$A$7*B2+$B$7

and copy it to the range С5:J5(Fig. 5).

Let's plot the regression line on the diagram. Select the experimental points on the chart, right-click and select the command Initial data. In the dialog box that appears (Fig. 5), select the tab Row and click on the button Add. Fill in the input fields, as shown in Fig. 6 and press the button OK. A regression line will be added to the experimental data plot. By default, its graph will be displayed as dots not connected by smoothing lines.

To change the appearance of the regression line, perform the following steps. Right-click on the points depicting the line graph, select the command Chart type and set the type of scatter plot, as shown in Fig. 7.

The line type, color and thickness can be changed as follows. Select the line on the diagram, press the right mouse button and select the command in the context menu Data Series Format… Next, make settings, for example, as shown in Fig. eight.

As a result of all the transformations, we get a graph of experimental data and a regression line in one graphic area (Fig. 9).

4.2. Using a trend line.

The construction of various approximating dependencies in MS Excel is implemented as a chart property - trend line.

EXAMPLE 2. As a result of the experiment, some tabular dependence was determined.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Select and build an approximating dependence. Build graphs of tabular and fitted analytical dependencies.

The solution of the problem can be divided into the following stages: input of initial data, construction of a scatter plot and addition of a trend line to this plot.

Let's consider this process in detail. Let's enter the initial data into the worksheet and plot the experimental data. Next, select the experimental points on the chart, right-click and use the command Add l trend line(Fig. 10).

The dialog box that appears allows you to build an approximating dependence.

The first tab (Fig. 11) of this window indicates the type of approximating dependence.

The second one (Fig. 12) defines the construction parameters:

the name of the approximating dependence;

Forecast forward (backward) on n units (this parameter determines how many units forward (backward) it is necessary to extend the trend line);

whether to show the point of intersection of the curve with the line y=const;

whether to show the approximating function on the diagram or not (show the equation on the diagram parameter);

Whether to place the value of the standard deviation on the diagram or not (the parameter put the value of the approximation reliability on the diagram).

Let us choose a polynomial of the second degree as an approximating dependence (Fig. 11) and derive an equation describing this polynomial on the graph (Fig. 12). The resulting diagram is shown in fig. 13.

Similarly, with trend lines you can choose the parameters of such dependencies as

linear y=a∙x+b,

logarithmic y=a ln(x)+b,

exponential y=a∙eb,

power y=a x b,

polynomial y=a∙x 2 +b∙x+c, y=a∙x 3 +b∙x 2 +c∙x+d and so on, up to and including the 6th degree polynomial,

Linear filtering.

4.3. Using the analysis of options tool: Finding a solution.

Of considerable interest is the implementation in MS Excel of the selection of parameters of the functional dependence by the least squares method using the option analysis tool: Search for a solution. This technique allows you to choose the parameters of a function of any kind. Let's consider this possibility on the example of the following problem.

EXAMPLE 3. As a result of the experiment, the dependence z(t) presented in the table

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Select dependency coefficients Z(t)=At 4 +Bt 3 +Ct 2 +Dt+K by the least squares method.

This problem is equivalent to the problem of finding the minimum of a function of five variables

Consider the process of solving the optimization problem (Fig. 14).

Let the values BUT, AT, FROM, D and To stored in cells A7:E7. Calculate the theoretical values ​​of the function Z(t)=At4+Bt3+Ct2+Dt+K for given t(B2:J2). To do this, in the cell B4 enter the value of the function at the first point (cell B2):

B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7.

Copy this formula into the range С4:J4 and get the expected value of the function at points, the abscissas of which are stored in cells B2:J2.

To cell B5 we introduce a formula that calculates the square of the difference between the experimental and calculated points:

B5=(B4-B3)^2,

and copy it to the range С5:J5. In a cell F7 we will store the total quadratic error (10). To do this, we introduce the formula:

F7 = SUM(B5:J5).

Let's use the command Service®Search for a solution and solve the optimization problem without constraints. Fill in the appropriate input fields in the dialog box shown in Fig. 14 and press the button Run. If a solution is found, the window shown in Fig. fifteen.

The result of the decision block will be the output to the cells A7:E7parameter values functions Z(t)=At4+Bt3+Ct2+Dt+K. In cells B4:J4 we get expected function value at starting points. In a cell F7 will be kept total squared error.

You can display the experimental points and the fitted line in the same graphic area if you select the range B2:J4, call Chart Wizard, and then format the appearance of the resulting graphs.

Rice. 17 displays the MS Excel worksheet after the calculations have been made.

Which finds the widest application in various fields of science and practice. It can be physics, chemistry, biology, economics, sociology, psychology and so on and so forth. By the will of fate, I often have to deal with the economy, and therefore today I will arrange for you a ticket to an amazing country called Econometrics=) … How do you not want that?! It's very good there - you just have to decide! …But what you probably definitely want is to learn how to solve problems least squares. And especially diligent readers will learn to solve them not only accurately, but also VERY FAST ;-) But first general statement of the problem+ related example:

Let indicators be studied in some subject area that have a quantitative expression. At the same time, there is every reason to believe that the indicator depends on the indicator. This assumption can be both a scientific hypothesis and based on elementary common sense. Let's leave science aside, however, and explore more appetizing areas - namely, grocery stores. Denote by:

– retail space of a grocery store, sq.m.,
- annual turnover of a grocery store, million rubles.

It is quite clear that the larger the area of ​​the store, the greater its turnover in most cases.

Suppose that after conducting observations / experiments / calculations / dancing with a tambourine, we have at our disposal numerical data:

With grocery stores, I think everything is clear: - this is the area of ​​the 1st store, - its annual turnover, - the area of ​​the 2nd store, - its annual turnover, etc. By the way, it is not at all necessary to have access to classified materials - a fairly accurate assessment of the turnover can be obtained using mathematical statistics. However, do not be distracted, the course of commercial espionage is already paid =)

Tabular data can also be written in the form of points and depicted in the usual way for us. Cartesian system .

Let's answer an important question: how many points are needed for a qualitative study?

The bigger, the better. The minimum admissible set consists of 5-6 points. In addition, with a small amount of data, “abnormal” results should not be included in the sample. So, for example, a small elite store can help out orders of magnitude more than “their colleagues”, thereby distorting the general pattern that needs to be found!

If it’s quite simple, we need to choose a function , schedule which passes as close as possible to the points . Such a function is called approximating (approximation - approximation) or theoretical function . Generally speaking, here immediately appears an obvious "pretender" - a polynomial of high degree, the graph of which passes through ALL points. But this option is complicated, and often simply incorrect. (because the chart will “wind” all the time and poorly reflect the main trend).

Thus, the desired function must be sufficiently simple and at the same time reflect the dependence adequately. As you might guess, one of the methods for finding such functions is called least squares. First, let's analyze its essence in a general way. Let some function approximate the experimental data:


How to evaluate the accuracy of this approximation? Let us also calculate the differences (deviations) between the experimental and functional values (we study the drawing). The first thought that comes to mind is to estimate how big the sum is, but the problem is that the differences can be negative. (for example, ) and deviations as a result of such summation will cancel each other out. Therefore, as an estimate of the accuracy of the approximation, it suggests itself to take the sum modules deviations:

or in folded form: (suddenly, who doesn’t know: is the sum icon, and is an auxiliary variable-“counter”, which takes values ​​from 1 to ).

By approximating the experimental points with different functions, we will obtain different values ​​of , and it is obvious that where this sum is smaller, that function is more accurate.

Such a method exists and is called least modulus method. However, in practice it has become much more widespread. least square method, in which possible negative values ​​are eliminated not by the modulus, but by squaring the deviations:

, after which efforts are directed to the selection of such a function that the sum of the squared deviations was as small as possible. Actually, hence the name of the method.

And now we return to another important point: as noted above, the selected function should be quite simple - but there are also many such functions: linear , hyperbolic, exponential, logarithmic, quadratic etc. And, of course, here I would immediately like to "reduce the field of activity." What class of functions to choose for research? Primitive but effective technique:

- The easiest way to draw points on the drawing and analyze their location. If they tend to be in a straight line, then you should look for straight line equation with optimal values ​​and . In other words, the task is to find SUCH coefficients - so that the sum of the squared deviations is the smallest.

If the points are located, for example, along hyperbole, then it is clear that the linear function will give a poor approximation. In this case, we are looking for the most “favorable” coefficients for the hyperbola equation - those that give the minimum sum of squares .

Now notice that in both cases we are talking about functions of two variables, whose arguments are searched dependency options:

And in essence, we need to solve a standard problem - to find minimum of a function of two variables.

Recall our example: suppose that the "shop" points tend to be located in a straight line and there is every reason to believe the presence linear dependence turnover from the trading area. Let's find SUCH coefficients "a" and "be" so that the sum of squared deviations was the smallest. Everything as usual - first partial derivatives of the 1st order. According to linearity rule you can differentiate right under the sum icon:

If you want to use this information for an essay or a term paper, I will be very grateful for the link in the list of sources, you will not find such detailed calculations anywhere:

Let's make a standard system:

We reduce each equation by a “two” and, in addition, “break apart” the sums:

Note : independently analyze why "a" and "be" can be taken out of the sum icon. By the way, formally this can be done with the sum

Let's rewrite the system in an "applied" form:

after which the algorithm for solving our problem begins to be drawn:

Do we know the coordinates of the points? We know. Sums can we find? Easily. We compose the simplest system of two linear equations with two unknowns("a" and "beh"). We solve the system, for example, Cramer's method, resulting in a stationary point . Checking sufficient condition for an extremum, we can verify that at this point the function reaches precisely minimum. Verification is associated with additional calculations and therefore we will leave it behind the scenes. (if necessary, the missing frame can be viewed). We draw the final conclusion:

Function the best way (at least compared to any other linear function) brings experimental points closer . Roughly speaking, its graph passes as close as possible to these points. In tradition econometrics the resulting approximating function is also called paired linear regression equation .

The problem under consideration is of great practical importance. In the situation with our example, the equation allows you to predict what kind of turnover ("yig") will be at the store with one or another value of the selling area (one or another meaning of "x"). Yes, the resulting forecast will be only a forecast, but in many cases it will turn out to be quite accurate.

I will analyze just one problem with "real" numbers, since there are no difficulties in it - all calculations are at the level of the school curriculum in grades 7-8. In 95 percent of cases, you will be asked to find just a linear function, but at the very end of the article I will show that it is no more difficult to find the equations for the optimal hyperbola, exponent, and some other functions.

In fact, it remains to distribute the promised goodies - so that you learn how to solve such examples not only accurately, but also quickly. We carefully study the standard:

A task

As a result of studying the relationship between two indicators, the following pairs of numbers were obtained:

Using the least squares method, find the linear function that best approximates the empirical (experienced) data. Make a drawing on which, in a Cartesian rectangular coordinate system, plot experimental points and a graph of the approximating function . Find the sum of squared deviations between empirical and theoretical values. Find out if the function is better (in terms of the least squares method) approximate experimental points.

Note that "x" values ​​are natural values, and this has a characteristic meaningful meaning, which I will talk about a little later; but they, of course, can be fractional. In addition, depending on the content of a particular task, both "X" and "G" values ​​can be fully or partially negative. Well, we have been given a “faceless” task, and we start it solution:

We find the coefficients of the optimal function as a solution to the system:

For the purposes of a more compact notation, the “counter” variable can be omitted, since it is already clear that the summation is carried out from 1 to .

It is more convenient to calculate the required amounts in a tabular form:


Calculations can be carried out on a microcalculator, but it is much better to use Excel - both faster and without errors; watch a short video:

Thus, we get the following system:

Here you can multiply the second equation by 3 and subtract the 2nd from the 1st equation term by term. But this is luck - in practice, systems are often not gifted, and in such cases it saves Cramer's method:
, so the system has a unique solution.

Let's do a check. I understand that I don’t want to, but why skip mistakes where you can absolutely not miss them? Substitute the found solution into the left side of each equation of the system:

The right parts of the corresponding equations are obtained, which means that the system is solved correctly.

Thus, the desired approximating function: – from all linear functions experimental data is best approximated by it.

Unlike straight dependence of the store's turnover on its area, the found dependence is reverse (principle "the more - the less"), and this fact is immediately revealed by the negative angular coefficient. Function informs us that with an increase in a certain indicator by 1 unit, the value of the dependent indicator decreases average by 0.65 units. As they say, the higher the price of buckwheat, the less sold.

To plot the approximating function, we find two of its values:

and execute the drawing:


The constructed line is called trend line (namely, a linear trend line, i.e. in the general case, a trend is not necessarily a straight line). Everyone is familiar with the expression "to be in trend", and I think that this term does not need additional comments.

Calculate the sum of squared deviations between empirical and theoretical values. Geometrically, this is the sum of the squares of the lengths of the "crimson" segments (two of which are so small you can't even see them).

Let's summarize the calculations in a table:


They can again be carried out manually, just in case I will give an example for the 1st point:

but it is much more efficient to do the already known way:

Let's repeat: what is the meaning of the result? From all linear functions function the exponent is the smallest, that is, it is the best approximation in its family. And here, by the way, the final question of the problem is not accidental: what if the proposed exponential function will it be better to approximate the experimental points?

Let's find the corresponding sum of squared deviations - to distinguish them, I will designate them with the letter "epsilon". The technique is exactly the same:


And again for every fire calculation for the 1st point:

In Excel, we use the standard function EXP (Syntax can be found in Excel Help).

Conclusion: , so the exponential function approximates the experimental points worse than the straight line .

But it should be noted here that "worse" is doesn't mean yet, what is wrong. Now I built a graph of this exponential function - and it also passes close to the points - so much so that without an analytical study it is difficult to say which function is more accurate.

This completes the solution, and I return to the question of the natural values ​​of the argument. In various studies, as a rule, economic or sociological, months, years or other equal time intervals are numbered with natural "X". Consider, for example, such a problem.

The least squares method is a mathematical procedure for constructing a linear equation that most closely matches a set of two series of numbers. The purpose of this method is to minimize the total squared error. Excel has tools that can be used to apply this method in calculations. Let's see how it's done.

Using the Method in Excel

o Enabling the Solver add-on

o Task conditions

o Decision

Using a Method in Excel

The method of least squares (LSM) is a mathematical description of the dependence of one variable on another. It can be used for forecasting.

Enable the Solver add-in

In order to use OLS in Excel, you need to enable the add-in "Search for a Solution", which is disabled by default.

1. Go to the tab "File".

2. Click on the name of the section "Options".

3. In the window that opens, stop the selection on the subsection "Add-ons".

4. In the block "Control", which is located at the bottom of the window, set the switch to the position "Excel Add-Ins"(if it has a different value) and click on the button "Go...".

5. A small window opens. Put a checkmark next to the option "Search for a Solution". Click on the button OK.

Now the function Finding a Solution in Excel is activated, and its tools appear on the ribbon.

Lesson: Finding a Solution in Excel

Conditions of the problem

Let us describe the application of LSM on a specific example. We have two rows of numbers x and y, the sequence of which is shown in the image below.

This dependence can most accurately be described by the function:

At the same time, it is known that x=0 y also equal 0 . Therefore, this equation can be described by the dependence y=nx.

We have to find the minimum sum of squares of the difference.

Solution

Let us proceed to the description of the direct application of the method.

1. To the left of the first value x put a number 1 . This will be the approximate value of the first value of the coefficient n.

2. To the right of the column y add another column nx. In the first cell of this column we write the formula for multiplying the coefficient n to the cell of the first variable x. At the same time, we make the link to the field with the coefficient absolute, since this value will not change. We click on the button Enter.

3. Using the fill handle, copy this formula to the entire range of the table in the column below.

4. In a separate cell, we calculate the sum of the differences of the squares of the values y and nx. To do this, click on the button "Insert Function".

5. In the opened "Function Wizard" looking for an entry "SUMMKVRAZN". Select it and click on the button OK.

6. The arguments window opens. In field "Array_x" y. In field "Array_y" enter a range of column cells nx. In order to enter values, simply place the cursor in the field and select the appropriate range on the sheet. After entering, click on the button OK.

7. Go to the tab "Data". On the ribbon in the toolbox "Analysis" click on the button "Search for a Solution".

8. The tool's parameters window opens. In field "Optimize objective function" specify the address of the cell with the formula "SUMMKVRAZN". In parameter "Before" be sure to set the switch to the position "Minimum". In field "Changing Cells" specify the address with the value of the coefficient n. Click on the button "Find a solution".

9. The solution will be displayed in the coefficient cell n. It is this value that will be the least square of the function. If the result satisfies the user, then click on the button OK in an additional window.

As you can see, the application of the least squares method is a rather complicated mathematical procedure. We have shown it in action with the simplest example, but there are much more complex cases. However, the Microsoft Excel toolkit is designed to simplify the calculations as much as possible.

http://multitest.semico.ru/mnk.htm

General provisions

The smaller the number in absolute value, the better the straight line (2) is chosen. As a characteristic of the accuracy of the selection of a straight line (2), we can take the sum of squares

The minimum conditions for S will be

	(6)
	(7)

Equations (6) and (7) can be written in the following form:

	(8)
	(9)

From equations (8) and (9) it is easy to find a and b from the experimental values ​​x i and y i . The line (2) defined by equations (8) and (9) is called the line obtained by the least squares method (this name emphasizes that the sum of squares S has a minimum). Equations (8) and (9), from which the straight line (2) is determined, are called normal equations.

It is possible to indicate a simple and general way of compiling normal equations. Using experimental points (1) and equation (2), we can write down the system of equations for a and b

y 1 \u003d ax 1 +b,
y2=ax2+b, ...		(10)
yn=axn+b,

We multiply the left and right parts of each of these equations by the coefficient at the first unknown a (i.e. x 1 , x 2 , ..., x n) and add the resulting equations, as a result we get the first normal equation (8).

We multiply the left and right sides of each of these equations by the coefficient of the second unknown b, i.e. by 1, and add the resulting equations, resulting in the second normal equation (9).

This method of obtaining normal equations is general: it is suitable, for example, for the function

is a constant value and it must be determined from experimental data (1).

The system of equations for k can be written:

Find the line (2) using the least squares method.

Solution. We find:

X i =21, y i =46.3, x i 2 =91, x i y i =179.1.

We write equations (8) and (9)91a+21b=179.1,

21a+6b=46.3, from here we find
a=0.98 b=4.3.