Die Methode der kleinsten Quadrate (LSM) basiert auf der Minimierung der Summe der quadratischen Abweichungen der ausgewählten Funktion von den zu untersuchenden Daten. In diesem Artikel approximieren wir die verfügbaren Daten mit einer linearen Funktionj = a x + b .

Methode der kleinsten Quadrate(Englisch) Normal Am wenigsten Quadrate , OLS) ist eine der grundlegenden Methoden der Regressionsanalyse im Hinblick auf die Schätzung unbekannter Parameter Regressionsmodelle nach Beispieldaten.

Betrachten Sie die Approximation durch Funktionen in Abhängigkeit von nur einer Variablen:

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

Notiz: In diesem Artikel werden Fälle der Approximation durch ein Polynom 3. bis 6. Grades betrachtet. Hier wird die Approximation durch ein trigonometrisches Polynom betrachtet.

Lineare Abhängigkeit

Uns interessiert die Beziehung von 2 Variablen X und j. Da gibt es eine Vermutung j hängt von der X nach dem linearen Gesetz j = Axt + b. Um die Parameter dieser Beziehung zu bestimmen, machte der Forscher Beobachtungen: Für jeden Wert von x i wurde eine Messung von y i durchgeführt (siehe Beispieldatei). Lassen Sie dementsprechend 20 Wertepaare (х i ; y i) sein.

Notiz: Wenn die Änderung Schritt für Schritt durch X ist konstant, dann zu bauen Streudiagramme kann verwendet werden, wenn nicht, dann müssen Sie den Diagrammtyp verwenden gepunktet .

Aus dem Diagramm ist ersichtlich, dass die Beziehung zwischen den Variablen nahezu linear ist. Um zu verstehen, welche der vielen geraden Linien die Beziehung zwischen Variablen am "richtigsten" beschreibt, ist es notwendig, das Kriterium zu bestimmen, nach dem die Linien verglichen werden.

Als solches Kriterium verwenden wir den Ausdruck:

wo ŷ ich = a * x ich + b ; n – Anzahl der Wertepaare (in unserem Fall n=20)

Der obige Ausdruck ist die Summe der quadrierten Abstände zwischen den beobachteten Werten von y i und ŷ i und wird oft als SSE bezeichnet ( Summe von kariert Fehler (Reste), Summe der quadrierten Fehler (Residuen)) .

Methode der kleinsten Quadrate ist, eine solche Linie auszuwählen ŷ = Axt + b, für die der obige Ausdruck den Mindestwert annimmt.

Notiz: Jede Linie im zweidimensionalen Raum wird eindeutig durch die Werte von 2 Parametern bestimmt: a (Steigung) und b (Verschiebung).

Es wird angenommen, dass je kleiner die Summe der quadrierten Abstände ist, desto besser nähert sich die entsprechende Linie den verfügbaren Daten an und kann weiter verwendet werden, um die Werte von y aus der Variablen x vorherzusagen. Es ist klar, dass selbst wenn in Wirklichkeit keine Beziehung zwischen den Variablen besteht oder die Beziehung nicht linear ist, das LSM immer noch die „beste“ Linie auswählen wird. Das LSM sagt also nichts über das Vorhandensein einer realen Beziehung von Variablen aus, die Methode erlaubt Ihnen lediglich, solche Parameter der Funktion auszuwählen a und b , für die der obige Ausdruck minimal ist.

Nachdem Sie nicht sehr komplexe mathematische Operationen durchgeführt haben (siehe für weitere Details), können Sie die Parameter berechnen a und b :

Wie aus der Formel ersichtlich ist, der Parameter a ist das Verhältnis von Kovarianz und , also in MS EXCEL, um den Parameter zu berechnen a Sie können die folgenden Formeln verwenden (siehe Beispiel Dateiblatt Linear):

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

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

Auch um den Parameter zu berechnen a Sie können die Formel = verwenden NEIGUNG(C26:C45;B26:B45). Für Parameter b Formel = verwenden INTERCUT(C26:C45;B26:B45) .

Und schließlich ermöglicht Ihnen die Funktion RGP(), beide Parameter gleichzeitig zu berechnen. Um eine Formel einzugeben LINEST(C26:C45;B26:B45) Wählen Sie 2 Zellen hintereinander aus und drücken Sie STRG + VERSCHIEBUNG + EINGABE(siehe Artikel über). Die linke Zelle gibt den Wert zurück a , zur Rechten b .

Notiz: Um nicht mit der Eingabe herumzuspielen Array-Formeln Sie müssen zusätzlich die Funktion INDEX() verwenden. Formel = INDEX(LINEST(C26:C45,B26:B45),1) oder einfach = LINEST(C26:C45;B26:B45) gibt den Parameter zurück, der für die Steigung der Linie verantwortlich ist, d.h. a . Formel = INDEX(LINEST(C26:C45,B26:B45),2) gibt den Parameter zurück, der für den Schnittpunkt der Linie mit der Y-Achse verantwortlich ist, d.h. b .

Nach Berechnung der Parameter, Streudiagramm Linie gezogen werden kann.

Eine andere Möglichkeit, eine gerade Linie mit der Methode der kleinsten Quadrate zu zeichnen, ist das Diagrammwerkzeug Trendlinie. Wählen Sie dazu das Diagramm aus, wählen Sie aus dem Menü Registerkarte „Layout“., in Gruppenanalyse klicken Trendlinie, dann Lineare Näherung .

Durch Aktivieren des Kontrollkästchens "Gleichung im Diagramm anzeigen" im Dialogfeld können Sie sicherstellen, dass die oben gefundenen Parameter mit den Werten im Diagramm übereinstimmen.

Notiz: Damit die Parameter übereinstimmen, muss der Diagrammtyp . Tatsache ist, dass beim Erstellen eines Diagramms Plan x-Achsenwerte können nicht vom Benutzer festgelegt werden (der Benutzer kann nur Beschriftungen angeben, die die Position der Punkte nicht beeinflussen). Anstelle von X-Werten wird die Folge 1 verwendet; 2; 3; … (für Kategorienummerierung). Daher, wenn Gebäude Trendlinie auf dem Typendiagramm Plan, dann werden die Werte dieser Folge anstelle der tatsächlichen Werte von X verwendet, was zu einem falschen Ergebnis führt (es sei denn natürlich, die tatsächlichen Werte von X stimmen nicht mit der Folge 1; 2 überein ; 3; ...).

Nun, bei der Arbeit haben sie sich zur Inspektion gemeldet, der Artikel wurde zu Hause für die Konferenz geschrieben - jetzt können Sie im Blog schreiben. Während ich meine Daten verarbeitete, wurde mir klar, dass ich nicht anders konnte, als über ein sehr cooles und notwendiges Add-In in Excel zu schreiben, das . Der Artikel widmet sich also diesem speziellen Add-In, und ich werde Ihnen anhand eines Anwendungsbeispiels davon erzählen Methode der kleinsten Quadrate(LSM) zur Suche nach unbekannten Koeffizienten der Gleichung in der Beschreibung experimenteller Daten.

So aktivieren Sie das Add-On „Suche nach einer Lösung“

Lassen Sie uns zunächst herausfinden, wie Sie dieses Add-on aktivieren.

1. Gehen Sie zum Menü „Datei“ und wählen Sie „Excel-Optionen“

2. Wählen Sie im erscheinenden Fenster „Nach einer Lösung suchen“ und klicken Sie auf „Los“.

3. Setzen Sie im nächsten Fenster ein Häkchen vor den Punkt „Nach einer Lösung suchen“ und klicken Sie auf „OK“.

4. Das Add-In ist aktiviert – nun ist es im Menüpunkt „Daten“ zu finden.

Methode der kleinsten Quadrate

Nun kurz zu Methode der kleinsten Quadrate (LSM) und wo es angewendet werden kann.

Nehmen wir an, wir haben einen Datensatz, nachdem wir ein Experiment durchgeführt haben, bei dem wir die Auswirkungen des X-Werts auf den Y-Wert untersucht haben.

Wir wollen diesen Einfluss mathematisch beschreiben, damit wir später diese Formel anwenden können und wissen, dass wenn wir den Wert von X um so viel ändern, wir den Wert von Y so und so bekommen ...

Nehmen wir ein supereinfaches Beispiel (siehe Bild).

Kein Wunder, dass die Punkte wie auf einer Geraden hintereinander liegen und wir daher davon ausgehen, dass unsere Abhängigkeit durch eine lineare Funktion y=kx+b beschrieben wird. Gleichzeitig sind wir sicher, dass wenn X gleich Null ist, der Wert von Y auch gleich Null ist. Das bedeutet, dass die Funktion, die die Abhängigkeit beschreibt, noch einfacher wird: y=kx (denken Sie an den Schullehrplan).

Im Allgemeinen müssen wir den Koeffizienten k finden. Das werden wir damit machen MNC mit dem Add-On "Suche nach einer Lösung".

Die Methode ist (hier - Achtung: Sie müssen darüber nachdenken), dass die Summe der quadrierten Differenzen zwischen den experimentell erhaltenen und den entsprechenden berechneten Werten minimal war. Das heißt, wenn X1=1, der tatsächlich gemessene Wert Y1=4,6 und das berechnete y1=f (x1) 4 ist, ist das Quadrat der Differenz (y1-Y1)^2=(4-4,6)^2= 0,36 . Das Folgende ist dasselbe wie das Folgende: Wenn X2=2, der tatsächlich gemessene Wert Y2=8,1 und das berechnete y2 8 ist, ist das Quadrat der Differenz (y2-Y2)^2=(8-8,1)^ 2 = 0,01. Und die Summe all dieser Quadrate sollte so klein wie möglich sein.

Beginnen wir also mit der Schulung zur Verwendung von LSM und Excel-Add-Ins „Suche nach Lösung“ .

Anwendung des Add-Ins Lösung finden

1. Wenn Sie das Add-on „Suche nach einer Lösung“ nicht aktiviert haben, kehren Sie zu Schritt zurück So aktivieren und aktivieren Sie das Add-On „Suche nach einer Lösung“. 🙂

2. Geben Sie in Zelle A1 den Wert „1“ ein. Diese Einheit ist die erste Annäherung an den realen Wert des Koeffizienten (k) unserer funktionalen Abhängigkeit y=kx.

3. In Spalte B haben wir die Werte des Parameters X, in Spalte C - die Werte des Parameters Y. In die Zellen der Spalte D geben wir die Formel ein: „Faktor k mal Wert von X“. Geben Sie beispielsweise in Zelle D1 „=A1*B1“ ein, in Zelle D2 „=A1*B2“ und so weiter.

4. Wir glauben, dass der Koeffizient k gleich eins ist und die Funktion f (x) \u003d y \u003d 1 * x die erste Annäherung an unsere Lösung ist. Wir können die Summe der quadrierten Differenzen zwischen den gemessenen Werten von Y und den mit der Formel y=1*x berechneten berechnen. Wir können das alles manuell machen, indem wir die entsprechenden Zellbezüge in die Formel treiben: "=(D2-C2)^2+(D3-C3)^2+(D4-C4)^2... etc. Am Ende haben wir irren und verstehen, dass wir viel Zeit verloren haben. In Excel gibt es für die Berechnung der Summe der quadrierten Differenzen eine spezielle Formel, "SUMQDIFF", die alles für uns erledigt. Geben wir sie in Zelle A2 ein und setzen Sie die Anfangsdaten: Bereich der gemessenen Werte Y (Spalte C) und Bereich der berechneten Y-Werte (Spalte D).

4. Die Summe der Differenzen der Quadrate wurde berechnet – gehen Sie nun auf die Registerkarte „Daten“ und wählen Sie „Nach einer Lösung suchen“.

5. Wählen Sie im angezeigten Menü die Zelle A1 als zu ändernde Zelle aus (die mit dem Koeffizienten k).

6. Wählen Sie als Ziel die Zelle A2 aus und stellen Sie die Bedingung „auf den Mindestwert setzen“ ein. Denken Sie daran, dass dies die Zelle ist, in der wir die Summe der quadrierten Differenzen zwischen den berechneten und gemessenen Werten berechnen, und dieser Betrag sollte minimal sein. Wir drücken "Ausführen".

7. Koeffizient k wird ausgewählt. Nun zeigt sich, dass die errechneten Werte nun sehr nah an den gemessenen liegen.

P.S.

Generell gibt es natürlich für die Approximation von Versuchsdaten in Excel spezielle Tools, die es erlauben, die Daten mit einer Linear-, Exponential-, Potenz- und Polynomfunktion zu beschreiben, sodass man oft darauf verzichten kann Add-Ons "Suche nach einer Lösung". Ich habe in meinem Artikel über all diese Annäherungsmethoden gesprochen. Wenn Sie also interessiert sind, werfen Sie einen Blick darauf. Aber wenn es um eine exotische Funktion geht mit einem unbekannten Koeffizienten oder Optimierungsprobleme, dann hier Überbau so gut wie möglich.

Add-In "Suche nach einer Lösung" für andere Aufgaben verwendet werden kann, ist die Hauptsache, das Wesentliche zu verstehen: Es gibt eine Zelle, in der wir einen Wert auswählen, und es gibt eine Zielzelle, in der eine Bedingung für die Auswahl eines unbekannten Parameters festgelegt ist.
Das ist alles! Im nächsten Artikel werde ich ein Märchen über einen Urlaub erzählen, also um die Veröffentlichung des Artikels nicht zu verpassen,

4.1. Verwendung integrierter Funktionen

Berechnung Regressionskoeffizienten mit der Funktion durchgeführt

NÄCHSTES(Werte_y; Werte_x; Konst; Statistiken),

Werte_y- Array von y-Werten,

Werte_x- optionales Array von Werten x wenn Array X weggelassen, wird davon ausgegangen, dass dies ein Array (1;2;3;...) der gleichen Größe wie ist Werte_y,

Konst- ein boolescher Wert, der angibt, ob die Konstante erforderlich ist b gleich 0 war. Wenn Konst hat die bedeutung WAHR oder weggelassen, dann b wie üblich berechnet. Wenn das Argument Konst ist dann FALSCH b wird als 0 angenommen und die Werte a sind so gewählt, dass die Relation y=ax.

Statistiken- ein boolescher Wert, der angibt, ob zusätzliche Regressionsstatistiken zurückgegeben werden müssen. Wenn das Argument Statistiken hat die bedeutung WAHR, dann die Funktion NÄCHSTES gibt zusätzliche Regressionsstatistiken zurück. Wenn das Argument Statistiken hat die bedeutung FALSCH oder weggelassen, dann die Funktion NÄCHSTES gibt nur den Koeffizienten zurück a und dauerhaft b.

Es muss daran erinnert werden, dass das Ergebnis der Funktionen RGP() ist eine Reihe von Werten - ein Array.

Zur Berechnung Korrelationskoeffizient Funktion verwendet wird

KORREL(Array1;Array2),

Rückgabe der Werte des Korrelationskoeffizienten, wo Array1- Array von Werten j, Array2- Array von Werten x. Array1 und Array2 müssen gleich groß sein.

BEISPIEL 1. Sucht j(x) ist in der Tabelle dargestellt. Bauen Regressionslinie und berechnen Korrelationskoeffizient.

j		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

Lassen Sie uns eine Wertetabelle in ein MS Excel-Blatt eingeben und ein Streudiagramm erstellen. Das Arbeitsblatt nimmt die in Abb. 2.

Um die Werte der Regressionskoeffizienten zu berechnen a und b Zellen auswählen A7:B7, Wenden wir uns dem Funktionsassistenten und der Kategorie zu Statistisch wähle eine Funktion NÄCHSTES. Füllen Sie das erscheinende Dialogfeld wie in Abb. 3 und drücken Sie OK.

Als Ergebnis wird der berechnete Wert nur in der Zelle angezeigt A6(Abb. 4). Damit ein Wert in einer Zelle erscheint B6 Sie müssen in den Bearbeitungsmodus wechseln (Taste F2) und drücken Sie dann die Tastenkombination STRG+UMSCHALT+EINGABETASTE.

Um den Wert des Korrelationskoeffizienten pro Zelle zu berechnen C6 folgende Formel wurde eingeführt:

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

Kenntnis der Regressionskoeffizienten a und b Berechnen Sie die Werte der Funktion j=Axt+b für gegeben x. Dazu führen wir die Formel ein

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

und in den Bereich kopieren С5:J5(Abb. 5).

Lassen Sie uns die Regressionslinie in das Diagramm einzeichnen. Wählen Sie die experimentellen Punkte auf dem Diagramm aus, klicken Sie mit der rechten Maustaste und wählen Sie den Befehl aus Ausgangsdaten. Wählen Sie im erscheinenden Dialogfenster (Abb. 5) die Registerkarte aus Reihe und klicken Sie auf die Schaltfläche Hinzufügen. Füllen Sie die Eingabefelder aus, wie in Abb. 6 und drücken Sie die Taste OK. Dem experimentellen Datenplot wird eine Regressionslinie hinzugefügt. Standardmäßig wird sein Diagramm als Punkte angezeigt, die nicht durch Glättungslinien verbunden sind.

Führen Sie die folgenden Schritte aus, um das Aussehen der Regressionslinie zu ändern. Klicken Sie mit der rechten Maustaste auf die Punkte, die das Liniendiagramm darstellen, und wählen Sie den Befehl aus Diagramm Typ und stellen Sie die Art des Streudiagramms ein, wie in Abb. 7.

Der Linientyp, die Farbe und die Dicke können wie folgt geändert werden. Markieren Sie die Linie im Diagramm, drücken Sie die rechte Maustaste und wählen Sie den Befehl im Kontextmenü Datenreihenformat… Nehmen Sie als nächstes Einstellungen vor, wie zum Beispiel in Abb. acht.

Als Ergebnis aller Transformationen erhalten wir ein Diagramm mit experimentellen Daten und eine Regressionslinie in einem Diagrammbereich (Abb. 9).

4.2. Verwenden einer Trendlinie.

Der Aufbau verschiedener approximativer Abhängigkeiten in MS Excel ist als Diagrammeigenschaft implementiert - Trendlinie.

BEISPIEL 2. Als Ergebnis des Experiments wurde eine gewisse tabellarische Abhängigkeit bestimmt.

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

Wählen und erstellen Sie eine approximative Abhängigkeit. Erstellen Sie Diagramme tabellarischer und angepasster analytischer Abhängigkeiten.

Die Lösung des Problems kann in die folgenden Schritte unterteilt werden: Eingabe von Anfangsdaten, Erstellung eines Streudiagramms und Hinzufügen einer Trendlinie zu diesem Diagramm.

Betrachten wir diesen Prozess im Detail. Lassen Sie uns die Anfangsdaten in das Arbeitsblatt eingeben und die experimentellen Daten grafisch darstellen. Wählen Sie als Nächstes die experimentellen Punkte auf dem Diagramm aus, klicken Sie mit der rechten Maustaste und verwenden Sie den Befehl Hinzufügen l Trendlinie(Abb. 10).

Das angezeigte Dialogfeld ermöglicht es Ihnen, eine Näherungsabhängigkeit zu erstellen.

Die erste Registerkarte (Abb. 11) dieses Fensters zeigt die Art der Näherungsabhängigkeit an.

Die zweite (Abb. 12) definiert die Konstruktionsparameter:

der Name der approximierenden Abhängigkeit;

Prognose vorwärts (rückwärts) ein n Einheiten (dieser Parameter bestimmt, um wie viele Einheiten vorwärts (rückwärts) die Trendlinie verlängert werden muss);

ob der Schnittpunkt der Kurve mit der Linie angezeigt werden soll y=konst;

ob die Näherungsfunktion im Diagramm angezeigt werden soll oder nicht (zeigen Sie die Gleichung im Diagrammparameter);

Ob der Wert der Standardabweichung in das Diagramm eingetragen werden soll oder nicht (der Parameter trägt den Wert der Approximationszuverlässigkeit in das Diagramm ein).

Wählen wir als Näherungsabhängigkeit ein Polynom zweiten Grades (Abb. 11) und leiten eine Gleichung her, die dieses Polynom auf dem Graphen beschreibt (Abb. 12). Das resultierende Diagramm ist in Abb. 1 dargestellt. dreizehn.

Ebenso mit Trendlinien Sie können die Parameter solcher Abhängigkeiten wie wählen

linear j=a∙x+b,

logarithmisch j=ein ln(x)+b,

exponentiell j=a∙eb,

Energie j=ein x b,

Polynom j=a∙x 2 +b∙x+c, j=a∙x 3 +b∙x 2 +c∙x+d und so weiter, bis einschließlich des Polynoms 6. Grades,

Lineare Filterung.

4.3. Verwenden des Analyse-Optionen-Tools: Eine Lösung finden.

Von großem Interesse ist die Implementierung in MS Excel der Auswahl von Parametern der funktionalen Abhängigkeit nach der Methode der kleinsten Quadrate unter Verwendung des Optionsanalysewerkzeugs: Suche nach einer Lösung. Mit dieser Technik können Sie die Parameter einer beliebigen Funktion auswählen. Betrachten wir diese Möglichkeit am Beispiel des folgenden Problems.

BEISPIEL 3. Als Ergebnis des Experiments ist die Abhängigkeit z(t) in der Tabelle dargestellt

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

Abhängigkeitskoeffizienten auswählen Z(t) = At ​​4 + Bt 3 + Ct 2 + Dt + K nach der Methode der kleinsten Quadrate.

Dieses Problem ist äquivalent zu dem Problem, das Minimum einer Funktion von fünf Variablen zu finden

Betrachten Sie den Prozess zur Lösung des Optimierungsproblems (Abb. 14).

Lassen Sie die Werte SONDERN, BEIM, Mit, D und Zu in Zellen gespeichert A7:E7. Berechnen Sie die theoretischen Werte der Funktion Z(t)=At4+Bt3+Ct2+Dt+K für gegeben t(B2:J2). Dazu in der Zelle B4 Geben Sie den Wert der Funktion am ersten Punkt ein (Zelle B2):

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

Kopieren Sie diese Formel in den Bereich С4:J4 und erhalte den erwarteten Wert der Funktion an Punkten, deren Abszissen in Zellen gespeichert sind B2:J2.

Zur Zelle B5 Wir führen eine Formel ein, die das Quadrat der Differenz zwischen den experimentellen und den berechneten Punkten berechnet:

B5=(B4-B3)^2,

und in den Bereich kopieren С5:J5. In einer Zelle F7 Wir speichern den gesamten quadratischen Fehler (10). Dazu führen wir die Formel ein:

F7 = SUMME(B5:J5).

Lassen Sie uns den Befehl verwenden Service®Lösung suchen und löse das Optimierungsproblem ohne Nebenbedingungen. Füllen Sie die entsprechenden Eingabefelder in dem in Abb. 14 und drücken Sie die Taste Lauf. Wenn eine Lösung gefunden wird, erscheint das in Abb. fünfzehn.

Das Ergebnis des Entscheidungsblocks ist die Ausgabe an die Zellen A7:E7Parameterwerte Funktionen Z(t)=At4+Bt3+Ct2+Dt+K. In Zellen B4:J4 wir bekommen erwarteter Funktionswert an Startpunkten. In einer Zelle F7 wird beibehalten totaler quadratischer Fehler.

Sie können die experimentellen Punkte und die Anpassungslinie im selben Grafikbereich anzeigen, wenn Sie den Bereich auswählen B2:J4, Forderung Diagramm-Assistent, und formatieren Sie dann das Erscheinungsbild der resultierenden Diagramme.

Reis. 17 zeigt das MS Excel-Arbeitsblatt an, nachdem die Berechnungen durchgeführt wurden.

Welches die breiteste Anwendung in verschiedenen Bereichen der Wissenschaft und Praxis findet. Das können Physik, Chemie, Biologie, Wirtschaftswissenschaften, Soziologie, Psychologie und so weiter und so weiter sein. Durch den Willen des Schicksals muss ich mich oft mit der Wirtschaft auseinandersetzen, und deshalb arrangiere ich heute für Sie ein Ticket in ein erstaunliches Land namens Ökonometrie=) … Wie willst du das nicht?! Es ist sehr gut dort - Sie müssen sich nur entscheiden! …Aber was Sie wahrscheinlich auf jeden Fall wollen, ist zu lernen, wie man Probleme löst kleinsten Quadrate. Und besonders fleißige Leser werden lernen, sie nicht nur genau, sondern auch SEHR SCHNELL zu lösen ;-) Aber zuerst allgemeine Problemstellung+ zugehöriges Beispiel:

Lassen Sie Indikatoren in einigen Fachgebieten untersuchen, die einen quantitativen Ausdruck haben. Gleichzeitig gibt es allen Grund zu der Annahme, dass der Indikator vom Indikator abhängt. Diese Annahme kann sowohl eine wissenschaftliche Hypothese sein als auch auf elementarem gesunden Menschenverstand beruhen. Lassen wir die Wissenschaft jedoch beiseite und erkunden appetitlichere Bereiche – nämlich Lebensmittelgeschäfte. Bezeichnen durch:

– Verkaufsfläche eines Lebensmittelgeschäfts, qm,
- Jahresumsatz eines Lebensmittelgeschäfts, Millionen Rubel.

Es ist ganz klar, dass je größer die Ladenfläche ist, desto größer ist in den meisten Fällen der Umsatz.

Angenommen, wir haben nach Beobachtungen / Experimenten / Berechnungen / Tanzen mit einem Tamburin numerische Daten zur Verfügung:

Bei Lebensmittelgeschäften ist meines Erachtens alles klar: - das ist die Fläche des 1. Ladens, - sein Jahresumsatz, - die Fläche des 2. Ladens, - sein Jahresumsatz usw. Übrigens ist es überhaupt nicht erforderlich, Zugang zu Verschlusssachen zu haben - eine ziemlich genaue Einschätzung des Umsatzes kann mit erhalten werden mathematische Statistik. Lassen Sie sich jedoch nicht ablenken, der Kurs der Wirtschaftsspionage ist bereits bezahlt =)

Auch tabellarische Daten können in Form von Punkten geschrieben und wie bei uns gewohnt dargestellt werden. Kartesisches System .

Lassen Sie uns eine wichtige Frage beantworten: Wie viele Punkte braucht man für eine qualitative Studie?

Je mehr desto besser. Der minimal zulässige Satz besteht aus 5-6 Punkten. Darüber hinaus sollten bei einer geringen Datenmenge „auffällige“ Ergebnisse nicht in die Stichprobe aufgenommen werden. So kann beispielsweise ein kleiner Elite-Laden um Größenordnungen mehr aushelfen als „ihre Kollegen“, wodurch das allgemeine Muster, das gefunden werden muss, verzerrt wird!

Wenn es ganz einfach ist, müssen wir eine Funktion auswählen, Plan die so nah wie möglich an den Punkten vorbeigeht . Eine solche Funktion wird aufgerufen Annäherung (Annäherung - Annäherung) oder theoretische Funktion . Im Allgemeinen erscheint hier sofort ein offensichtlicher "Anwärter" - ein Polynom hohen Grades, dessen Graph ALLE Punkte durchläuft. Aber diese Option ist kompliziert und oft einfach falsch. (weil sich der Chart die ganze Zeit „windet“ und den Haupttrend schlecht widerspiegelt).

Die gewünschte Funktion muss also hinreichend einfach sein und gleichzeitig die Abhängigkeit adäquat widerspiegeln. Wie Sie sich vorstellen können, wird eine der Methoden zum Auffinden solcher Funktionen aufgerufen kleinsten Quadrate. Lassen Sie uns zuerst seine Essenz auf allgemeine Weise analysieren. Lassen Sie eine Funktion die experimentellen Daten approximieren:


Wie ist die Genauigkeit dieser Annäherung zu bewerten? Lassen Sie uns auch die Unterschiede (Abweichungen) zwischen den experimentellen und funktionalen Werten berechnen (wir studieren die Zeichnung). Der erste Gedanke, der mir in den Sinn kommt, ist zu schätzen, wie groß die Summe ist, aber das Problem ist, dass die Unterschiede negativ sein können. (Zum Beispiel, ) und Abweichungen als Ergebnis einer solchen Summierung heben sich gegenseitig auf. Als Abschätzung der Genauigkeit der Näherung bietet es sich daher an, die Summe zu nehmen Module Abweichungen:

oder in gefalteter Form: (plötzlich, wer kennt es nicht: - das ist das Summensymbol und - die Hilfsvariable - „Zähler“, der Werte von 1 bis annimmt).

Indem wir die experimentellen Punkte mit unterschiedlichen Funktionen annähern, erhalten wir unterschiedliche Werte von , und es ist offensichtlich, dass diese Funktion genauer ist, wenn diese Summe kleiner ist.

Eine solche Methode existiert und wird aufgerufen Methode des kleinsten Moduls. In der Praxis ist es jedoch viel weiter verbreitet. Methode der kleinsten Quadrate, bei dem mögliche negative Werte nicht durch den Modul, sondern durch Quadrieren der Abweichungen eliminiert werden:

, wonach sich die Bemühungen auf die Auswahl einer solchen Funktion richten, die die Summe der quadrierten Abweichungen ist war so klein wie möglich. Daher der Name der Methode.

Und jetzt kehren wir zu einem weiteren wichtigen Punkt zurück: Wie oben angemerkt, sollte die ausgewählte Funktion recht einfach sein - aber es gibt auch viele solcher Funktionen: linear , hyperbolisch, exponentiell, logarithmisch, quadratisch usw. Und natürlich möchte ich hier sofort "das Betätigungsfeld reduzieren". Welche Klasse von Funktionen für die Forschung wählen? Primitive, aber effektive Technik:

- Der einfachste Weg, Punkte zu zeichnen auf der Zeichnung und analysieren Sie ihre Position. Wenn sie dazu neigen, in einer geraden Linie zu sein, dann sollten Sie suchen Gerade Gleichung mit optimalen Werten und . Mit anderen Worten, die Aufgabe besteht darin, SOLCHE Koeffizienten zu finden - damit die Summe der quadrierten Abweichungen am kleinsten ist.

Wenn sich die Punkte beispielsweise entlang befinden Hyperbel, dann ist klar, dass die lineare Funktion eine schlechte Annäherung ergibt. In diesem Fall suchen wir nach den „günstigsten“ Koeffizienten für die Hyperbelgleichung - diejenigen, die die kleinste Quadratsumme ergeben .

Beachten Sie nun, dass wir in beiden Fällen darüber sprechen Funktionen zweier Variablen, deren Argumente sind gesuchte Abhängigkeitsoptionen:

Und im Wesentlichen müssen wir ein Standardproblem lösen - zu finden Minimum einer Funktion von zwei Variablen.

Erinnern Sie sich an unser Beispiel: Angenommen, die "Shop"-Punkte befinden sich tendenziell auf einer geraden Linie und es gibt allen Grund, an das Vorhandensein zu glauben lineare Abhängigkeit Umsätze aus dem Handelsbereich. Lassen Sie uns SOLCHE Koeffizienten "a" und "be" finden, damit die Summe der quadrierten Abweichungen war der kleinste. Alles wie gewohnt - zuerst partielle Ableitungen 1. Ordnung. Entsprechend Linearitätsregel Sie können direkt unter dem Summensymbol unterscheiden:

Falls Sie diese Informationen für eine Hausarbeit oder eine Hausarbeit verwenden möchten, bin ich für den Link im Quellenverzeichnis sehr dankbar, solche ausführlichen Berechnungen finden Sie nirgendwo:

Lassen Sie uns ein Standardsystem erstellen:

Wir kürzen jede Gleichung um eine „Zwei“ und „zerlegen“ zusätzlich die Summen:

Notiz : Analysieren Sie selbstständig, warum "a" und "be" aus dem Summensymbol herausgenommen werden können. Formal geht das übrigens mit der Summe

Lassen Sie uns das System in einer "angewendeten" Form umschreiben:

Danach beginnt der Algorithmus zur Lösung unseres Problems zu zeichnen:

Kennen wir die Koordinaten der Punkte? Wir wissen. Summen können wir finden? Leicht. Wir komponieren die einfachsten System aus zwei linearen Gleichungen mit zwei Unbekannten("a" und "beh"). Wir lösen das System zum Beispiel Cramers Methode, was zu einem stationären Punkt führt . Überprüfung hinreichende Bedingung für ein Extremum, können wir an dieser Stelle die Funktion verifizieren erreicht genau Minimum. Die Überprüfung ist mit zusätzlichen Berechnungen verbunden und wird daher hinter den Kulissen gelassen. (ggf. kann der fehlende Frame angeschaut werden). Wir ziehen das abschließende Fazit:

Funktion die beste Weise (zumindest im Vergleich zu jeder anderen linearen Funktion) bringt experimentelle Punkte näher . Grob gesagt verläuft sein Graph so nah wie möglich an diesen Punkten. Traditionell Ökonometrie die resultierende Näherungsfunktion wird auch aufgerufen gepaarte lineare Regressionsgleichung .

Das betrachtete Problem ist von großer praktischer Bedeutung. In der Situation mit unserem Beispiel ist die Gleichung ermöglicht es Ihnen, vorherzusagen, welche Art von Umsatz ("yig") wird im Laden mit dem einen oder anderen Wert der Verkaufsfläche sein (die eine oder andere Bedeutung von "x"). Ja, die resultierende Prognose wird nur eine Prognose sein, aber in vielen Fällen wird sie sich als ziemlich genau herausstellen.

Ich werde nur ein Problem mit "echten" Zahlen analysieren, da es keine Schwierigkeiten gibt - alle Berechnungen befinden sich auf dem Niveau des Schullehrplans in den Klassen 7-8. In 95 Prozent der Fälle werden Sie gebeten, nur eine lineare Funktion zu finden, aber ganz am Ende des Artikels werde ich zeigen, dass es nicht mehr schwierig ist, die Gleichungen für die optimale Hyperbel, den Exponenten und einige andere Funktionen zu finden.

Tatsächlich bleibt es, die versprochenen Leckereien zu verteilen - damit Sie lernen, solche Beispiele nicht nur genau, sondern auch schnell zu lösen. Wir studieren den Standard sorgfältig:

Aufgabe

Als Ergebnis der Untersuchung der Beziehung zwischen zwei Indikatoren wurden die folgenden Zahlenpaare erhalten:

Finden Sie mit der Methode der kleinsten Quadrate die lineare Funktion, die die empirische Funktion am besten annähert (erfahren) Daten. Fertigen Sie eine Zeichnung an, auf der Sie in einem rechtwinkligen kartesischen Koordinatensystem experimentelle Punkte und einen Graphen der Annäherungsfunktion darstellen . Ermitteln Sie die Summe der quadrierten Abweichungen zwischen empirischen und theoretischen Werten. Finden Sie heraus, ob die Funktion besser ist (nach der Methode der kleinsten Quadrate) ungefähre experimentelle Punkte.

Beachten Sie, dass „x“-Werte natürliche Werte sind und dies eine charakteristische bedeutungsvolle Bedeutung hat, auf die ich später noch eingehen werde. aber sie können natürlich gebrochen sein. Darüber hinaus können je nach Inhalt einer bestimmten Aufgabe sowohl die „X“- als auch die „G“-Werte ganz oder teilweise negativ sein. Nun, wir haben eine „gesichtslose“ Aufgabe bekommen, und wir beginnen damit Entscheidung:

Wir finden die Koeffizienten der optimalen Funktion als Lösung des Systems:

Im Sinne einer kompakteren Schreibweise kann auf die Variable „Zähler“ verzichtet werden, da bereits klar ist, dass von 1 bis summiert wird.

Bequemer ist es, die benötigten Mengen tabellarisch zu berechnen:


Berechnungen können auf einem Mikrorechner durchgeführt werden, aber es ist viel besser, Excel zu verwenden - sowohl schneller als auch fehlerfrei. Sehen Sie sich ein kurzes Video an:

Somit erhalten wir folgendes System:

Hier kannst du die zweite Gleichung mit 3 multiplizieren und die 2. von der 1. Gleichung Term für Term subtrahieren. Aber das ist Glück - in der Praxis sind Systeme oft nicht begabt, und in solchen Fällen spart es Cramers Methode:
, also hat das System eine eindeutige Lösung.

Lassen Sie uns einen Check machen. Ich verstehe, dass ich das nicht möchte, aber warum Fehler überspringen, wo man sie absolut nicht übersehen kann? Setzen Sie die gefundene Lösung in die linke Seite jeder Gleichung des Systems ein:

Es werden die richtigen Teile der entsprechenden Gleichungen erhalten, was bedeutet, dass das System richtig gelöst ist.

Damit ist die gesuchte Näherungsfunktion: – von alle linearen Funktionen experimentelle Daten werden dadurch am besten angenähert.

Im Gegensatz zu gerade Abhängigkeit des Umsatzes des Ladens von seiner Fläche, die gefundene Abhängigkeit ist umkehren (Prinzip "je mehr - desto weniger"), und diese Tatsache wird sofort durch das Negativ offenbart Winkelkoeffizient. Funktion teilt uns mit, dass bei einer Erhöhung eines bestimmten Indikators um 1 Einheit der Wert des abhängigen Indikators abnimmt im mittleren um 0,65 Einheiten. Wie sie sagen, je höher der Buchweizenpreis, desto weniger wird verkauft.

Um die Näherungsfunktion darzustellen, finden wir zwei ihrer Werte:

und führe die Zeichnung aus:


Die konstruierte Linie wird aufgerufen Trendlinie (nämlich eine lineare Trendlinie, d.h. im allgemeinen Fall ist ein Trend nicht unbedingt eine gerade Linie). Jeder kennt den Ausdruck „im Trend sein“, und ich denke, dass dieser Begriff keiner weiteren Erläuterung bedarf.

Berechnen Sie die Summe der quadrierten Abweichungen zwischen empirischen und theoretischen Werten. Geometrisch ist dies die Summe der Quadrate der Längen der „roten“ Segmente (zwei davon sind so klein, dass man sie nicht einmal sehen kann).

Fassen wir die Berechnungen in einer Tabelle zusammen:


Sie können wieder manuell ausgeführt werden, nur für den Fall, dass ich ein Beispiel für den 1. Punkt gebe:

aber es ist viel effizienter, den bereits bekannten Weg zu gehen:

Wiederholen wir: was bedeutet das Ergebnis? Aus alle linearen Funktionen Funktion der Exponent ist der kleinste, das heißt, er ist die beste Annäherung in seiner Familie. Und hier ist übrigens die letzte Frage des Problems nicht zufällig: Was wäre, wenn die vorgeschlagene Exponentialfunktion Ist es besser, die experimentellen Punkte anzunähern?

Lassen Sie uns die entsprechende Summe der quadratischen Abweichungen finden - um sie zu unterscheiden, werde ich sie mit dem Buchstaben "Epsilon" bezeichnen. Die Technik ist genau die gleiche:


Und nochmal für jede Brandberechnung für den 1. Punkt:

In Excel verwenden wir die Standardfunktion EXP (Syntax finden Sie in der Excel-Hilfe).

Fazit: , also approximiert die Exponentialfunktion die experimentellen Punkte schlechter als die Gerade .

Aber es sollte hier angemerkt werden, dass "schlimmer" ist heißt noch nicht, Was ist falsch. Jetzt habe ich einen Graphen dieser Exponentialfunktion gebaut - und sie geht auch nahe an den Punkten vorbei - so sehr, dass es ohne eine analytische Studie schwierig ist zu sagen, welche Funktion genauer ist.

Damit ist die Lösung abgeschlossen, und ich komme auf die Frage nach den natürlichen Werten des Arguments zurück. In diversen Studien werden in der Regel wirtschafts- oder soziologische Monate, Jahre oder andere gleiche Zeitintervalle mit natürlichen „X“ nummeriert. Betrachten Sie zum Beispiel ein solches Problem.

Die Methode der kleinsten Quadrate ist ein mathematisches Verfahren zum Erstellen einer linearen Gleichung, die am ehesten mit einem Satz von zwei Zahlenreihen übereinstimmt. Der Zweck dieser Methode besteht darin, den quadratischen Gesamtfehler zu minimieren. Excel verfügt über Tools, mit denen diese Methode in Berechnungen angewendet werden kann. Mal sehen, wie es gemacht wird.

Verwenden der Methode in Excel

o Aktivieren des Solver-Add-ons

o Aufgabenbedingungen

o Entscheidung

Verwenden einer Methode in Excel

Die Methode der kleinsten Quadrate (LSM) ist eine mathematische Beschreibung der Abhängigkeit einer Variablen von einer anderen. Es kann für Prognosen verwendet werden.

Aktivieren Sie das Solver-Add-In

Um OLS in Excel zu verwenden, müssen Sie das Add-In aktivieren "Suche nach einer Lösung", die standardmäßig deaktiviert ist.

1. Gehen Sie zur Registerkarte "Datei".

2. Klicken Sie auf den Namen des Abschnitts "Optionen".

3. Stoppen Sie im sich öffnenden Fenster die Auswahl auf dem Unterabschnitt "Add-Ons".

4. Im Block "Steuerung", das sich am unteren Rand des Fensters befindet, stellen Sie den Schalter auf die Position "Excel-Add-Ins"(falls es einen anderen Wert hat) und klicken Sie auf die Schaltfläche "Gehen...".

5. Ein kleines Fenster öffnet sich. Setzen Sie ein Häkchen neben die Option "Suche nach einer Lösung". Klicken Sie auf die Schaltfläche OK.

Jetzt die Funktion Eine Lösung finden in Excel ist aktiviert, und seine Werkzeuge werden in der Multifunktionsleiste angezeigt.

Lektion: Eine Lösung in Excel finden

Bedingungen des Problems

Lassen Sie uns die Anwendung von LSM an einem konkreten Beispiel beschreiben. Wir haben zwei Zahlenreihen x und j, deren Sequenz im Bild unten dargestellt ist.

Diese Abhängigkeit lässt sich am genauesten durch die Funktion beschreiben:

Gleichzeitig ist das bekannt x=0 j auch gleich 0 . Daher kann diese Gleichung durch die Abhängigkeit beschrieben werden y=nx.

Wir müssen die minimale Summe der Quadrate der Differenz finden.

Entscheidung

Fahren wir mit der Beschreibung der direkten Anwendung der Methode fort.

1. Links vom ersten Wert x zahl eintragen 1 . Dies ist der ungefähre Wert des ersten Werts des Koeffizienten n.

2. Rechts neben der Spalte j füge eine weitere Spalte hinzu nx. In die erste Zelle dieser Spalte schreiben wir die Formel zur Multiplikation des Koeffizienten n in die Zelle der ersten Variablen x. Gleichzeitig machen wir die Verknüpfung zum Feld mit dem Koeffizienten absolut, da sich dieser Wert nicht ändert. Wir klicken auf die Schaltfläche Eingeben.

3. Kopieren Sie diese Formel mit dem Ausfüllkästchen in den gesamten Bereich der Tabelle in der Spalte darunter.

4. In einer separaten Zelle berechnen wir die Summe der Differenzen der Quadrate der Werte j und nx. Klicken Sie dazu auf die Schaltfläche "Funktion einfügen".

5. Im geöffneten "Funktionsassistent" Suche nach einem Eintrag "SUMMKVRAZN". Wählen Sie es aus und klicken Sie auf die Schaltfläche OK.

6. Das Argumente-Fenster öffnet sich. Auf dem Feld "Array_x" j. Auf dem Feld "Array_y" Geben Sie einen Bereich von Spaltenzellen ein nx. Um Werte einzugeben, setzen Sie einfach den Cursor in das Feld und wählen Sie den entsprechenden Bereich auf dem Blatt aus. Klicken Sie nach der Eingabe auf die Schaltfläche OK.

7. Wechseln Sie zur Registerkarte "Daten". Auf dem Menüband in der Toolbox "Analyse" klicken Sie auf die Schaltfläche "Suche nach einer Lösung".

8. Das Parameterfenster des Werkzeugs wird geöffnet. Auf dem Feld "Zielfunktion optimieren" Geben Sie die Adresse der Zelle mit der Formel an "SUMMKVRAZN". Im Parameter "Vor" Achten Sie darauf, den Schalter auf die Position zu stellen "Minimum". Auf dem Feld "Zellen wechseln" Geben Sie die Adresse mit dem Wert des Koeffizienten an n. Klicken Sie auf die Schaltfläche "Finde eine Lösung".

9. Die Lösung wird in der Koeffizientenzelle angezeigt n. Dieser Wert ist das kleinste Quadrat der Funktion. Wenn das Ergebnis den Benutzer zufriedenstellt, klicken Sie auf die Schaltfläche OK in einem zusätzlichen Fenster.

Wie Sie sehen können, ist die Anwendung der Methode der kleinsten Quadrate ein ziemlich kompliziertes mathematisches Verfahren. Wir haben es anhand des einfachsten Beispiels in Aktion gezeigt, aber es gibt viel komplexere Fälle. Das Microsoft Excel-Toolkit ist jedoch darauf ausgelegt, die Berechnungen so weit wie möglich zu vereinfachen.

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

Allgemeine Bestimmungen

Je kleiner die Zahl im Betrag, desto besser wird die Gerade (2) gewählt. Als Merkmal für die Genauigkeit der Auswahl einer Geraden (2) können wir die Summe der Quadrate nehmen

Die Mindestbedingungen für S sind

	(6)
	(7)

Die Gleichungen (6) und (7) können in der folgenden Form geschrieben werden:

	(8)
	(9)

Aus den Gleichungen (8) und (9) ist es einfach, a und b aus den experimentellen Werten x i und y i zu finden. Die durch die Gleichungen (8) und (9) definierte Gerade (2) wird die durch die Methode der kleinsten Quadrate erhaltene Gerade genannt (dieser Name betont, dass die Summe der Quadrate S ein Minimum hat). Die Gleichungen (8) und (9), aus denen die Gerade (2) bestimmt wird, werden Normalgleichungen genannt.

Es ist möglich, eine einfache und allgemeine Methode zum Erstellen von Normalgleichungen anzugeben. Unter Verwendung der experimentellen Punkte (1) und der Gleichung (2) können wir das Gleichungssystem für a und b aufschreiben

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

Multiplizieren Sie den linken und rechten Teil jeder dieser Gleichungen mit dem Koeffizienten bei der ersten Unbekannten a (d. h. x 1 , x 2 , ..., x n) und addieren Sie die resultierenden Gleichungen, was zur ersten Normalgleichung (8) führt.

Wir multiplizieren die linke und rechte Seite jeder dieser Gleichungen mit dem Koeffizienten der zweiten Unbekannten b, d.h. durch 1, und addieren Sie die resultierenden Gleichungen, was zur zweiten Normalgleichung (9) führt.

Diese Methode, um Normalgleichungen zu erhalten, ist allgemein: Sie eignet sich beispielsweise für die Funktion

ist ein konstanter Wert und muss aus experimentellen Daten bestimmt werden (1).

Das Gleichungssystem für k kann geschrieben werden:

Finden Sie die Linie (2) mit der Methode der kleinsten Quadrate.

Entscheidung. Wir finden:

X i = 21, y i = 46,3, x i 2 = 91, x i y i = 179,1.

Wir schreiben die Gleichungen (8) und (9)91a+21b=179,1,

21a+6b=46,3, ab hier finden wir
a = 0,98 b = 4,3.