LSM für eine Funktion aus zwei Variablen. Annäherung experimenteller Daten. Methode der kleinsten Quadrate. Praktische Implementierung von LSM für lineare Abhängigkeit von einem nicht programmierbaren Rechner

Beispiel.

Experimentelle Daten zu den Werten von Variablen X Und bei sind in der Tabelle angegeben.

Durch ihre Ausrichtung ergibt sich die Funktion

Benutzen Methode der kleinsten Quadrate, approximieren Sie diese Daten mit einer linearen Abhängigkeit y=ax+b(Optionen finden A Und B). Finden Sie heraus, welche der beiden Linien besser ist (im Sinne der Methode der kleinsten Quadrate), um die experimentellen Daten auszurichten. Fertige eine Zeichnung an.

Die Essenz der Methode der kleinsten Quadrate (LSM).

Das Problem besteht darin, die linearen Abhängigkeitskoeffizienten zu finden, für die die Funktion zweier Variablen gilt A Und B nimmt den kleinsten Wert an. Das heißt, angesichts der Daten A Und B die Summe der quadrierten Abweichungen der experimentellen Daten von der gefundenen Geraden wird am kleinsten sein. Das ist der Sinn der Methode der kleinsten Quadrate.

Somit reduziert sich die Lösung des Beispiels darauf, das Extremum einer Funktion zweier Variablen zu finden.

Ableitung von Formeln zum Finden von Koeffizienten.

Ein System aus zwei Gleichungen mit zwei Unbekannten wird erstellt und gelöst. Partielle Ableitungen einer Funktion nach Variablen finden A Und B, setzen wir diese Ableitungen mit Null gleich.

Wir lösen das resultierende Gleichungssystem mit einer beliebigen Methode (z. B Substitutionsmethode oder ) und erhalten Sie Formeln zum Ermitteln der Koeffizienten mithilfe der Methode der kleinsten Quadrate (LSM).

Mit Daten A Und B Funktion nimmt den kleinsten Wert an. Der Beweis dieser Tatsache ist gegeben.

Das ist die ganze Methode der kleinsten Quadrate. Formel zum Finden des Parameters A enthält die Summen , , und den Parameter N- Menge experimenteller Daten. Es wird empfohlen, die Werte dieser Summen separat zu berechnen. Koeffizient B nach Berechnung gefunden A.

Es ist Zeit, sich an das ursprüngliche Beispiel zu erinnern.

Lösung.

In unserem Beispiel n=5. Wir füllen die Tabelle aus, um die Berechnung der Beträge zu erleichtern, die in den Formeln der erforderlichen Koeffizienten enthalten sind.

Die Werte in der vierten Zeile der Tabelle werden durch Multiplikation der Werte der 2. Zeile mit den Werten der 3. Zeile für jede Zahl erhalten ich.

Die Werte in der fünften Zeile der Tabelle werden durch Quadrieren der Werte der 2. Zeile für jede Zahl erhalten ich.

Die Werte der letzten Spalte der Tabelle sind die Summen der Werte über die Zeilen hinweg.

Um die Koeffizienten zu ermitteln, verwenden wir die Formeln der Methode der kleinsten Quadrate A Und B. Wir ersetzen darin die entsprechenden Werte aus der letzten Spalte der Tabelle:

Somit, y=0,165x+2,184 ist die gewünschte Näherungsgerade.

Es bleibt abzuwarten, welche der Zeilen y=0,165x+2,184 oder eine bessere Annäherung an die Originaldaten, d. h. eine Schätzung mithilfe der Methode der kleinsten Quadrate vorzunehmen.

Schätzung des Fehlers der Methode der kleinsten Quadrate.

Dazu müssen Sie die Summen der quadrierten Abweichungen der Originaldaten von diesen Linien berechnen Und , ein kleinerer Wert entspricht einer Linie, die sich den Originaldaten im Sinne der Methode der kleinsten Quadrate besser annähert.

Seitdem, dann die Zeile y=0,165x+2,184 nähert sich den Originaldaten besser an.

Grafische Darstellung der Methode der kleinsten Quadrate (LSM).

In den Charts sieht alles gut aus. Die rote Linie ist die gefundene Linie y=0,165x+2,184, die blaue Linie ist , die rosa Punkte sind die Originaldaten.

Wozu dient es, wozu dienen all diese Näherungen?

Ich persönlich verwende es, um Datenglättungsprobleme, Interpolations- und Extrapolationsprobleme zu lösen (im ursprünglichen Beispiel könnte man aufgefordert werden, den Wert des beobachteten Werts zu ermitteln). j bei x=3 oder wann x=6 nach der MNC-Methode). Aber wir werden später in einem anderen Abschnitt der Website mehr darüber sprechen.

Nachweisen.

Also das, wenn es gefunden wird A Und B Da die Funktion den kleinsten Wert annimmt, muss an dieser Stelle die Matrix der quadratischen Form des Differentials zweiter Ordnung für die Funktion vorliegen war eindeutig positiv. Zeigen wir es.

Es hat viele Anwendungsmöglichkeiten, da es eine näherungsweise Darstellung einer gegebenen Funktion durch andere, einfachere Funktionen ermöglicht. LSM kann bei der Verarbeitung von Beobachtungen äußerst nützlich sein und wird aktiv verwendet, um einige Größen aus den Messergebnissen anderer zu schätzen, die zufällige Fehler enthalten. In diesem Artikel erfahren Sie, wie Sie Berechnungen der kleinsten Quadrate in Excel implementieren.

Darstellung des Problems anhand eines konkreten Beispiels

Angenommen, es gibt zwei Indikatoren X und Y. Darüber hinaus hängt Y von X ab. Da OLS für uns aus Sicht der Regressionsanalyse von Interesse ist (in Excel werden seine Methoden mithilfe integrierter Funktionen implementiert), sollten wir sofort fortfahren ein bestimmtes Problem betrachten.

Sei also X die Verkaufsfläche eines Lebensmittelgeschäfts, gemessen in Quadratmetern, und Y der Jahresumsatz, definiert in Millionen Rubel.

Es ist erforderlich, eine Prognose darüber zu erstellen, welchen Umsatz (Y) das Geschäft erzielen wird, wenn es über die eine oder andere Verkaufsfläche verfügt. Offensichtlich nimmt die Funktion Y = f (X) zu, da der Hypermarkt mehr Waren verkauft als der Stand.

Ein paar Worte zur Richtigkeit der für die Vorhersage verwendeten Ausgangsdaten

Nehmen wir an, wir haben eine Tabelle mit Daten für n Filialen erstellt.

Laut mathematischer Statistik sind die Ergebnisse mehr oder weniger korrekt, wenn die Daten von mindestens 5-6 Objekten untersucht werden. Auch „anomale“ Ergebnisse können nicht verwendet werden. Insbesondere eine kleine Elite-Boutique kann einen Umsatz erzielen, der um ein Vielfaches höher ist als der Umsatz großer Outlets der „Masmarket“-Klasse.

Die Essenz der Methode

Die Tabellendaten können auf der kartesischen Ebene als Punkte M 1 (x 1, y 1), ... M n (x n, y n) angezeigt werden. Nun reduziert sich die Lösung des Problems auf die Auswahl einer Näherungsfunktion y = f (x), die einen Graphen aufweist, der möglichst nahe an den Punkten M 1, M 2, .. M n verläuft.

Natürlich können Sie ein Polynom hohen Grades verwenden, aber diese Option ist nicht nur schwierig zu implementieren, sondern einfach falsch, da sie nicht den Haupttrend widerspiegelt, der erkannt werden muss. Die sinnvollste Lösung besteht darin, nach einer Geraden y = ax + b zu suchen, die den experimentellen Daten und genauer gesagt den Koeffizienten a und b am besten entspricht.

Genauigkeitsbewertung

Für jede Näherung ist die Beurteilung ihrer Genauigkeit von besonderer Bedeutung. Bezeichnen Sie mit e i die Differenz (Abweichung) zwischen den funktionalen und experimentellen Werten für den Punkt x i , d. h. e i = y i - f (x i).

Um die Genauigkeit der Näherung zu beurteilen, können Sie natürlich die Summe der Abweichungen verwenden, d. h. bei der Auswahl einer Geraden für eine ungefähre Darstellung der Abhängigkeit von X von Y sollte der Linie der Vorzug gegeben werden, die den kleinsten Wert hat die Summe e i an allen betrachteten Punkten. Allerdings ist nicht alles so einfach, denn neben positiven Abweichungen gibt es praktisch auch negative.

Sie können das Problem mithilfe der Abweichungsmodule oder ihrer Quadrate lösen. Die letztere Methode ist die am weitesten verbreitete. Es wird in vielen Bereichen eingesetzt, unter anderem in der Regressionsanalyse (in Excel erfolgt die Implementierung über zwei integrierte Funktionen) und hat sich seit langem als effektiv erwiesen.

Methode der kleinsten Quadrate

Wie Sie wissen, gibt es in Excel eine integrierte Autosum-Funktion, mit der Sie die Werte aller Werte berechnen können, die sich im ausgewählten Bereich befinden. Somit hindert uns nichts daran, den Wert des Ausdrucks (e 1 2 + e 2 2 + e 3 2 + ... e n 2) zu berechnen.

In mathematischer Notation sieht das so aus:

Da ursprünglich die Entscheidung getroffen wurde, mit einer geraden Linie zu approximieren, gilt:

Die Aufgabe, eine Gerade zu finden, die eine bestimmte Beziehung zwischen X und Y am besten beschreibt, läuft also darauf hinaus, das Minimum einer Funktion zweier Variablen zu berechnen:

Dies erfordert die Gleichsetzung partieller Ableitungen in Bezug auf die neuen Variablen a und b mit null und die Lösung eines primitiven Systems, das aus zwei Gleichungen mit zwei Unbekannten der Form besteht:

Nach einfachen Transformationen, einschließlich der Division durch 2 und der Manipulation der Summen, erhalten wir:

Wenn wir es beispielsweise mit der Cramer-Methode lösen, erhalten wir einen stationären Punkt mit bestimmten Koeffizienten a * und b * . Dies ist das Minimum, d.h. um vorherzusagen, welchen Umsatz das Geschäft für eine bestimmte Fläche haben wird, eignet sich die Gerade y = a * x + b*, die für das jeweilige Beispiel ein Regressionsmodell darstellt. Natürlich können Sie damit nicht das genaue Ergebnis finden, aber es hilft Ihnen, eine Vorstellung davon zu bekommen, ob sich der Kauf eines Geschäfts auf Kredit für einen bestimmten Bereich auszahlt.

So implementieren Sie die Methode der kleinsten Quadrate in Excel

Excel verfügt über eine Funktion zur Berechnung des Wertes der kleinsten Quadrate. Es hat die folgende Form: TREND (bekannte Y-Werte; bekannte X-Werte; neue X-Werte; Konstante). Wenden wir die Formel zur Berechnung des OLS in Excel auf unsere Tabelle an.

Geben Sie dazu in der Zelle, in der das Ergebnis der Berechnung nach der Methode der kleinsten Quadrate in Excel angezeigt werden soll, das Zeichen „=“ ein und wählen Sie die Funktion „TREND“. Füllen Sie im sich öffnenden Fenster die entsprechenden Felder aus und markieren Sie Folgendes:

Bereich bekannter Werte für Y (in diesem Fall Daten für den Umsatz);
Bereich x 1 , …x n , d. h. die Größe der Verkaufsfläche;
und bekannte und unbekannte Werte von x, für die Sie die Größe des Umsatzes ermitteln müssen (Informationen zu deren Position auf dem Arbeitsblatt finden Sie unten).

Darüber hinaus gibt es in der Formel eine logische Variable „Const“. Wenn Sie in das entsprechende Feld 1 eingeben, bedeutet dies, dass Berechnungen unter der Annahme durchgeführt werden sollten, dass b \u003d 0 ist.

Wenn Sie die Prognose für mehr als einen x-Wert kennen müssen, sollten Sie nach Eingabe der Formel nicht die Eingabetaste drücken, sondern die Kombination „Umschalttaste“ + „Strg“ + „Eingabetaste“ („Eingabetaste“) eingeben. ) auf der Tastatur.

Einige Eigenschaften

Die Regressionsanalyse kann auch für Dummköpfe zugänglich sein. Die Excel-Formel zur Vorhersage des Wertes eines Arrays unbekannter Variablen – „TREND“ – kann auch von denen verwendet werden, die noch nie von der Methode der kleinsten Quadrate gehört haben. Es reicht aus, nur einige Merkmale seiner Arbeit zu kennen. Insbesondere:

Wenn Sie den Bereich bekannter Werte der Variablen y in einer Zeile oder Spalte platzieren, wird jede Zeile (Spalte) mit bekannten Werten von x vom Programm als separate Variable wahrgenommen.
Wenn der Bereich mit bekanntem x im TREND-Fenster nicht angegeben ist, betrachtet das Programm sie bei Verwendung der Funktion in Excel als ein Array bestehend aus ganzen Zahlen, deren Anzahl dem Bereich mit den angegebenen Werten entspricht der Variablen y.
Um ein Array von „vorhergesagten“ Werten auszugeben, muss der Trendausdruck als Arrayformel eingegeben werden.
Wenn keine neuen x-Werte angegeben werden, betrachtet die TREND-Funktion diese als gleich den bekannten. Wenn sie nicht angegeben sind, wird Array 1 als Argument verwendet; 2; 3; 4;…, was dem Bereich mit bereits angegebenen Parametern y entspricht.
Der Bereich, der die neuen x-Werte enthält, muss die gleichen oder mehr Zeilen oder Spalten haben wie der Bereich mit den angegebenen y-Werten. Mit anderen Worten, es muss proportional zu den unabhängigen Variablen sein.
Ein Array mit bekannten x-Werten kann mehrere Variablen enthalten. Wenn wir jedoch nur von einem sprechen, ist es erforderlich, dass die Bereiche mit den angegebenen Werten von x und y angemessen sind. Bei mehreren Variablen ist es erforderlich, dass der Bereich mit den angegebenen y-Werten in eine Spalte oder eine Zeile passt.

FORECAST-Funktion

Die Umsetzung erfolgt über mehrere Funktionen. Eine davon heißt „PREDICTION“. Es ähnelt TREND, d. h. es gibt das Ergebnis von Berechnungen nach der Methode der kleinsten Quadrate an. Allerdings nur für ein X, für das der Wert von Y unbekannt ist.

Jetzt kennen Sie die Excel-Formeln für Dummies, mit denen Sie den Wert des zukünftigen Wertes eines Indikators anhand eines linearen Trends vorhersagen können.

Die Methode der kleinsten Quadrate ist aufgrund ihrer Eigenschaften eine der gebräuchlichsten und am weitesten entwickelten Einfachheit und Effizienz von Methoden zur Schätzung linearer Parameter. Gleichzeitig ist bei der Verwendung eine gewisse Vorsicht geboten, da die damit erstellten Modelle möglicherweise eine Reihe von Anforderungen an die Qualität ihrer Parameter nicht erfüllen und daher die Muster der Prozessentwicklung nicht „gut“ widerspiegeln.

Betrachten wir das Verfahren zur Schätzung der Parameter eines linearen ökonometrischen Modells mithilfe der Methode der kleinsten Quadrate genauer. Ein solches Modell kann in allgemeiner Form durch Gleichung (1.2) dargestellt werden:

y t = a 0 + a 1 x 1 t +...+ a n x nt + ε t .

Die Anfangsdaten bei der Schätzung der Parameter a 0 , a 1 ,..., a n ist der Wertevektor der abhängigen Variablen j= (y 1 , y 2 , ... , y T)“ und die Wertematrix unabhängiger Variablen

wobei die erste Spalte, bestehend aus Einsen, dem Koeffizienten des Modells entspricht.

Die Methode der kleinsten Quadrate erhielt ihren Namen aufgrund des Grundprinzips, dass die auf ihrer Grundlage erhaltenen Parameterschätzungen Folgendes erfüllen müssen: Die Quadratsumme des Modellfehlers sollte minimal sein.

Beispiele für die Lösung von Problemen mit der Methode der kleinsten Quadrate

Beispiel 2.1. Das Handelsunternehmen verfügt über ein Netzwerk von 12 Filialen, deren Aktivitäten in der Tabelle dargestellt sind. 2.1.

Die Unternehmensleitung möchte wissen, wie die Größe des Jahresberichts von der Verkaufsfläche des Ladens abhängt.

Tabelle 2.1

Shop-Nummer	Jahresumsatz, Millionen Rubel	Handelsfläche, tausend m 2

Lösung der kleinsten Quadrate. Bezeichnen wir - den Jahresumsatz des -ten Ladens, Millionen Rubel; - Verkaufsfläche des -ten Ladens, Tausend m 2.

Abb.2.1. Streudiagramm für Beispiel 2.1

Bestimmen Sie die Form der funktionalen Beziehung zwischen den Variablen und erstellen Sie ein Streudiagramm (Abb. 2.1).

Anhand des Streudiagramms können wir schließen, dass der Jahresumsatz positiv von der Verkaufsfläche abhängt (d. h. y nimmt mit dem Wachstum von zu). Die am besten geeignete Form der funktionalen Verbindung ist − linear.

Informationen für weitere Berechnungen sind in der Tabelle dargestellt. 2.2. Mithilfe der Methode der kleinsten Quadrate schätzen wir die Parameter des linearen einfaktoriellen ökonometrischen Modells

Tabelle 2.2

Auf diese Weise,

Daher erhöht sich bei einer Vergrößerung der Handelsfläche um 1 Tausend m 2 unter sonst gleichen Bedingungen der durchschnittliche Jahresumsatz um 67,8871 Millionen Rubel.

Beispiel 2.2. Die Unternehmensleitung stellte fest, dass der Jahresumsatz nicht nur von der Verkaufsfläche des Ladens abhängt (siehe Beispiel 2.1), sondern auch von der durchschnittlichen Besucherzahl. Die relevanten Informationen sind in der Tabelle dargestellt. 2.3.

Tabelle 2.3

Lösung. Bezeichnen - die durchschnittliche Anzahl der Besucher des Ladens pro Tag, tausend Menschen.

Bestimmen Sie die Form der funktionalen Beziehung zwischen den Variablen und erstellen Sie ein Streudiagramm (Abb. 2.2).

Basierend auf dem Streudiagramm können wir schließen, dass der Jahresumsatz positiv mit der durchschnittlichen Anzahl der Besucher pro Tag zusammenhängt (d. h. y wird mit dem Wachstum von zunehmen). Die Form der funktionalen Abhängigkeit ist linear.

Reis. 2.2. Streudiagramm zum Beispiel 2.2

Tabelle 2.4

Im Allgemeinen ist es notwendig, die Parameter des zweifaktoriellen ökonometrischen Modells zu bestimmen

y t \u003d a 0 + a 1 x 1 t + a 2 x 2 t + ε t

Die für weitere Berechnungen erforderlichen Informationen sind in der Tabelle aufgeführt. 2.4.

Lassen Sie uns die Parameter eines linearen zweifaktoriellen ökonometrischen Modells mithilfe der Methode der kleinsten Quadrate schätzen.

Auf diese Weise,

Die Auswertung des Koeffizienten = 61,6583 zeigt, dass bei sonst gleichen Bedingungen bei einer Vergrößerung der Handelsfläche um 1 Tausend m 2 der Jahresumsatz um durchschnittlich 61,6583 Millionen Rubel steigen wird.

Methode der kleinsten Quadrate

Methode der kleinsten Quadrate ( MNK, OLS, gewöhnliche kleinste Quadrate) - eine der grundlegenden Methoden der Regressionsanalyse zur Schätzung unbekannter Parameter von Regressionsmodellen aus Beispieldaten. Die Methode basiert auf der Minimierung der Summe der Quadrate der Regressionsresiduen.

Es ist zu beachten, dass die Methode der kleinsten Quadrate selbst als Methode zur Lösung eines Problems in einem beliebigen Bereich bezeichnet werden kann, wenn die Lösung aus einem bestimmten Kriterium zur Minimierung der Quadratsumme einiger Funktionen der unbekannten Variablen besteht oder ein bestimmtes Kriterium erfüllt. Daher kann die Methode der kleinsten Quadrate auch zur ungefähren Darstellung (Approximation) einer bestimmten Funktion durch andere (einfachere) Funktionen verwendet werden, wenn eine Menge von Größen gefunden wird, die Gleichungen oder Einschränkungen erfüllen, deren Anzahl die Anzahl dieser Größen übersteigt , usw.

Die Essenz des MNC

Lassen Sie ein (parametrisches) Modell der probabilistischen (Regressions-)Abhängigkeit zwischen der (erklärten) Variablen j und viele Faktoren (erklärende Variablen) X

Wo ist der Vektor unbekannter Modellparameter?

- Zufälliger Modellfehler.

Lassen Sie es auch Beispielbeobachtungen der Werte der angegebenen Variablen geben. Sei die Beobachtungszahl (). Dann sind die Werte der Variablen in der -ten Beobachtung. Dann ist es für gegebene Werte der Parameter b möglich, die theoretischen (Modell-)Werte der erklärten Variablen y zu berechnen:

Der Wert der Residuen hängt von den Werten der Parameter b ab.

Die Essenz von LSM (gewöhnlich, klassisch) besteht darin, solche Parameter b zu finden, für die die Summe der Quadrate der Residuen (eng. Restquadratsumme) wird minimal sein:

Im Allgemeinen kann dieses Problem durch numerische Methoden der Optimierung (Minimierung) gelöst werden. In diesem Fall spricht man von nichtlineare kleinste Quadrate(NLS oder NLLS – Englisch. Nichtlineare kleinste Quadrate). In vielen Fällen kann eine analytische Lösung erhalten werden. Um das Minimierungsproblem zu lösen, ist es notwendig, die stationären Punkte der Funktion zu finden, indem man sie nach den unbekannten Parametern b differenziert, die Ableitungen mit Null gleichsetzt und das resultierende Gleichungssystem löst:

Wenn die zufälligen Fehler des Modells normalverteilt sind, die gleiche Varianz aufweisen und nicht miteinander korrelieren, sind die Parameterschätzungen der kleinsten Quadrate dieselben wie die Schätzungen der Maximum-Likelihood-Methode (MLM).

LSM im Fall eines linearen Modells

Die Regressionsabhängigkeit sei linear:

Lassen j- Spaltenvektor von Beobachtungen der erklärten Variablen und - Matrix von Beobachtungen von Faktoren (Zeilen der Matrix - Vektoren von Faktorwerten in einer bestimmten Beobachtung, nach Spalten - Vektor von Werten eines bestimmten Faktors in allen Beobachtungen) . Die Matrixdarstellung des linearen Modells hat die Form:

Dann sind der Vektor der Schätzungen der erklärten Variablen und der Vektor der Regressionsresiduen gleich

dementsprechend ist die Summe der Quadrate der Regressionsresiduen gleich

Wenn wir diese Funktion nach dem Parametervektor differenzieren und die Ableitungen mit Null gleichsetzen, erhalten wir ein Gleichungssystem (in Matrixform):

Die Lösung dieses Gleichungssystems ergibt die allgemeine Formel für die Schätzungen der kleinsten Quadrate für das lineare Modell:

Für analytische Zwecke erweist sich die letzte Darstellung dieser Formel als nützlich. Wenn die Daten im Regressionsmodell zentriert, dann hat in dieser Darstellung die erste Matrix die Bedeutung der Kovarianzmatrix der Faktoren der Stichprobe und die zweite ist der Vektor der Kovarianzen der Faktoren mit abhängiger Variable. Wenn darüber hinaus die Daten auch normalisiert beim SKO (das heißt letztendlich standardisiert), dann hat die erste Matrix die Bedeutung der Stichprobenkorrelationsmatrix von Faktoren, der zweite Vektor - der Vektor der Stichprobenkorrelationen von Faktoren mit der abhängigen Variablen.

Eine wichtige Eigenschaft von LLS-Schätzungen für Modelle mit einer Konstante- Die Linie der konstruierten Regression verläuft durch den Schwerpunkt der Stichprobendaten, d. h. die Gleichheit ist erfüllt:

Insbesondere im Extremfall, wenn der einzige Regressor eine Konstante ist, stellen wir fest, dass die OLS-Schätzung eines einzelnen Parameters (der Konstante selbst) gleich dem Mittelwert der zu erklärenden Variablen ist. Das heißt, das arithmetische Mittel, das für seine guten Eigenschaften aus den Gesetzen der großen Zahlen bekannt ist, ist auch eine Schätzung der kleinsten Quadrate – es erfüllt das Kriterium für die minimale Summe der quadratischen Abweichungen davon.

Beispiel: einfache (paarweise) Regression

Bei der gepaarten linearen Regression werden die Berechnungsformeln vereinfacht (Sie können auf Matrixalgebra verzichten):

Eigenschaften von OLS-Schätzungen

Zunächst stellen wir fest, dass bei linearen Modellen die Schätzungen der kleinsten Quadrate lineare Schätzungen sind, wie aus der obigen Formel hervorgeht. Für unverzerrte OLS-Schätzungen ist es notwendig und ausreichend, die wichtigste Bedingung der Regressionsanalyse zu erfüllen: Die mathematische Erwartung eines von den Faktoren abhängigen Zufallsfehlers muss gleich Null sein. Diese Voraussetzung ist insbesondere erfüllt, wenn

die mathematische Erwartung zufälliger Fehler ist Null und
Faktoren und Zufallsfehler sind unabhängige Zufallsvariablen.

Die zweite Bedingung – die Bedingung exogener Faktoren – ist grundlegend. Wenn diese Eigenschaft nicht erfüllt ist, können wir davon ausgehen, dass fast alle Schätzungen äußerst unbefriedigend sind: Sie sind nicht einmal konsistent (d. h. selbst eine sehr große Datenmenge ermöglicht in diesem Fall keine qualitativen Schätzungen). Im klassischen Fall wird im Gegensatz zu einem Zufallsfehler eine stärkere Annahme über den Determinismus von Faktoren getroffen, was automatisch bedeutet, dass die exogene Bedingung erfüllt ist. Im allgemeinen Fall reicht es für die Konsistenz der Schätzungen aus, die Exogenitätsbedingung zusammen mit der Konvergenz der Matrix zu einer nicht singulären Matrix bei einer Erhöhung der Stichprobengröße bis ins Unendliche zu erfüllen.

Damit die Schätzungen der (üblichen) kleinsten Quadrate neben Konsistenz und Unvoreingenommenheit auch effektiv sind (die besten in der Klasse der linearen erwartungstreuen Schätzungen), müssen zusätzliche Eigenschaften eines Zufallsfehlers erfüllt werden:

Diese Annahmen können für die Kovarianzmatrix des Zufallsfehlervektors formuliert werden

Ein lineares Modell, das diese Bedingungen erfüllt, heißt klassisch. OLS-Schätzungen für die klassische lineare Regression sind erwartungstreue, konsistente und effizienteste Schätzungen in der Klasse aller linearen erwartungstreuen Schätzungen (in der englischen Literatur wird die Abkürzung manchmal verwendet). Blau (Bester linearer, unvermittelter Schätzer) ist die beste lineare unverzerrte Schätzung; in der heimischen Literatur wird häufiger der Satz von Gauß-Markov zitiert). Wie leicht zu zeigen ist, ist die Kovarianzmatrix des Koeffizientenschätzungsvektors gleich:

Verallgemeinerte kleinste Quadrate

Die Methode der kleinsten Quadrate ermöglicht eine weitreichende Verallgemeinerung. Anstatt die Summe der Quadrate der Residuen zu minimieren, kann man eine positiv definite quadratische Form des Residuenvektors minimieren, bei der es sich um eine symmetrische positiv definite Gewichtsmatrix handelt. Gewöhnliche kleinste Quadrate sind ein Sonderfall dieses Ansatzes, bei dem die Gewichtsmatrix proportional zur Identitätsmatrix ist. Wie aus der Theorie der symmetrischen Matrizen (oder Operatoren) bekannt ist, gibt es für solche Matrizen eine Zerlegung. Daher kann das angegebene Funktional wie folgt dargestellt werden, das heißt, dieses Funktional kann als Summe der Quadrate einiger transformierter „Residuen“ dargestellt werden. Somit können wir eine Klasse von Methoden der kleinsten Quadrate unterscheiden – LS-Methoden (Least Squares).

Es ist bewiesen (Theorem von Aitken), dass für ein verallgemeinertes lineares Regressionsmodell (bei dem der Kovarianzmatrix zufälliger Fehler keine Einschränkungen auferlegt werden) die Schätzungen der sogenannten am effektivsten (in der Klasse der linearen unverzerrten Schätzungen) sind. verallgemeinertes OLS (OMNK, GLS – Generalized Least Squares)- LS-Methode mit einer Gewichtsmatrix gleich der inversen Kovarianzmatrix zufälliger Fehler: .

Es kann gezeigt werden, dass die Formel für die GLS-Schätzungen der Parameter des linearen Modells die Form hat

Die Kovarianzmatrix dieser Schätzungen ist jeweils gleich

Tatsächlich liegt das Wesen des OLS in einer bestimmten (linearen) Transformation (P) der Originaldaten und der Anwendung der üblichen kleinsten Quadrate auf die transformierten Daten. Der Zweck dieser Transformation besteht darin, dass für die transformierten Daten die Zufallsfehler bereits die klassischen Annahmen erfüllen.

Gewichtete kleinste Quadrate

Im Fall einer diagonalen Gewichtsmatrix (und damit der Kovarianzmatrix zufälliger Fehler) haben wir die sogenannten gewichteten kleinsten Quadrate (WLS – Weighted Least Squares). In diesem Fall wird die gewichtete Quadratsumme der Residuen des Modells minimiert, d. h. jede Beobachtung erhält ein „Gewicht“, das umgekehrt proportional zur Varianz des Zufallsfehlers in dieser Beobachtung ist: . Tatsächlich werden die Daten durch Gewichtung der Beobachtungen transformiert (Dividierung durch einen Betrag, der proportional zur angenommenen Standardabweichung der Zufallsfehler ist), und die normale Methode der kleinsten Quadrate wird auf die gewichteten Daten angewendet.

Einige spezielle Anwendungsfälle von LSM in der Praxis

Lineare Näherung

Stellen Sie sich den Fall vor, dass als Ergebnis der Untersuchung der Abhängigkeit einer bestimmten skalaren Größe von einer bestimmten skalaren Größe (dies kann beispielsweise die Abhängigkeit der Spannung von der Stromstärke sein: , wobei ein konstanter Wert der Widerstand des Leiters ist). ) wurden diese Größen gemessen, wodurch die Werte und ihre entsprechenden Werte ermittelt wurden. Messdaten sollten in einer Tabelle erfasst werden.

Tisch. Messergebnisse.

Messung Nr.
1
2
3
4
5
6

Die Frage lautet wie folgt: Welcher Wert des Koeffizienten kann gewählt werden, um die Abhängigkeit am besten zu beschreiben? Nach der Methode der kleinsten Quadrate sollte dieser Wert so sein, dass er die Summe der quadrierten Abweichungen der Werte von den Werten darstellt

war minimal

Die Summe der quadratischen Abweichungen hat ein Extremum – ein Minimum, was uns die Verwendung dieser Formel ermöglicht. Lassen Sie uns den Wert des Koeffizienten anhand dieser Formel ermitteln. Dazu transformieren wir seine linke Seite wie folgt:

Mit der letzten Formel können wir den Wert des Koeffizienten ermitteln, der im Problem benötigt wurde.

Geschichte

Bis zum Beginn des 19. Jahrhunderts. Wissenschaftler hatten keine bestimmten Regeln zum Lösen eines Gleichungssystems, in dem die Anzahl der Unbekannten geringer ist als die Anzahl der Gleichungen; Bis zu diesem Zeitpunkt wurden je nach Art der Gleichungen und dem Einfallsreichtum der Rechner bestimmte Methoden verwendet, und daher kamen verschiedene Rechner, ausgehend von denselben Beobachtungsdaten, zu unterschiedlichen Schlussfolgerungen. Gauß (1795) wird die erste Anwendung der Methode zugeschrieben, und Legendre (1805) entdeckte sie unabhängig und veröffentlichte sie unter ihrem modernen Namen (fr. Methode des mindersten Streits ). Laplace bezog die Methode auf die Wahrscheinlichkeitstheorie, und der amerikanische Mathematiker Adrain (1808) befasste sich mit ihren probabilistischen Anwendungen. Die Methode ist weit verbreitet und wurde durch weitere Forschungen von Encke, Bessel, Hansen und anderen verbessert.

Alternative Verwendung von MNCs

Die Idee der Methode der kleinsten Quadrate kann auch in anderen Fällen verwendet werden, die nicht direkt mit der Regressionsanalyse zusammenhängen. Tatsache ist, dass die Quadratsumme eines der gebräuchlichsten Näherungsmaße für Vektoren ist (die euklidische Metrik in endlichdimensionalen Räumen).

Eine Anwendung ist das „Lösen“ linearer Gleichungssysteme, bei denen die Anzahl der Gleichungen größer ist als die Anzahl der Variablen

wobei die Matrix nicht quadratisch, sondern rechteckig ist.

Ein solches Gleichungssystem hat im allgemeinen Fall keine Lösung (sofern der Rang tatsächlich größer ist als die Anzahl der Variablen). Daher kann dieses System nur in dem Sinne „gelöst“ werden, dass ein solcher Vektor gewählt wird, um den „Abstand“ zwischen den Vektoren und zu minimieren. Dazu können Sie das Kriterium zur Minimierung der Summe der quadrierten Differenzen des linken und rechten Teils der Gleichungen des Systems anwenden, also . Es lässt sich leicht zeigen, dass die Lösung dieses Minimierungsproblems zur Lösung des folgenden Gleichungssystems führt

Beispiel.

Experimentelle Daten zu den Werten von Variablen X Und bei sind in der Tabelle angegeben.

Durch ihre Ausrichtung ergibt sich die Funktion

Die Essenz der Methode der kleinsten Quadrate (LSM).

Somit reduziert sich die Lösung des Beispiels darauf, das Extremum einer Funktion zweier Variablen zu finden.

Ableitung von Formeln zum Finden von Koeffizienten.

Ein System aus zwei Gleichungen mit zwei Unbekannten wird erstellt und gelöst. Partielle Ableitungen von Funktionen finden nach Variablen A Und B, setzen wir diese Ableitungen mit Null gleich.

Wir lösen das resultierende Gleichungssystem mit einer beliebigen Methode (z. B Substitutionsmethode oder Cramers Methode) und erhalten Sie Formeln zum Ermitteln der Koeffizienten mithilfe der Methode der kleinsten Quadrate (LSM).

Mit Daten A Und B Funktion nimmt den kleinsten Wert an. Der Beweis dieser Tatsache ist gegeben unterhalb des Textes am Ende der Seite.

Das ist die ganze Methode der kleinsten Quadrate. Formel zum Finden des Parameters A enthält die Summen ,,, und den Parameter N- Menge experimenteller Daten. Es wird empfohlen, die Werte dieser Summen separat zu berechnen. Koeffizient B nach Berechnung gefunden A.

Es ist Zeit, sich an das ursprüngliche Beispiel zu erinnern.

Lösung.

In unserem Beispiel n=5. Wir füllen die Tabelle aus, um die Berechnung der Beträge zu erleichtern, die in den Formeln der erforderlichen Koeffizienten enthalten sind.

Die Werte in der vierten Zeile der Tabelle werden durch Multiplikation der Werte der 2. Zeile mit den Werten der 3. Zeile für jede Zahl erhalten ich.

Die Werte in der fünften Zeile der Tabelle werden durch Quadrieren der Werte der 2. Zeile für jede Zahl erhalten ich.

Die Werte der letzten Spalte der Tabelle sind die Summen der Werte über die Zeilen hinweg.

Um die Koeffizienten zu ermitteln, verwenden wir die Formeln der Methode der kleinsten Quadrate A Und B. Wir ersetzen darin die entsprechenden Werte aus der letzten Spalte der Tabelle:

Somit, y=0,165x+2,184 ist die gewünschte Näherungsgerade.

Es bleibt abzuwarten, welche der Zeilen y=0,165x+2,184 oder eine bessere Annäherung an die Originaldaten, d. h. eine Schätzung mithilfe der Methode der kleinsten Quadrate vorzunehmen.

Schätzung des Fehlers der Methode der kleinsten Quadrate.

Seitdem, dann die Zeile y=0,165x+2,184 nähert sich den Originaldaten besser an.

Grafische Darstellung der Methode der kleinsten Quadrate (LSM).

In den Charts sieht alles gut aus. Die rote Linie ist die gefundene Linie y=0,165x+2,184, die blaue Linie ist , die rosa Punkte sind die Originaldaten.

In der Praxis wird bei der Modellierung verschiedener Prozesse – insbesondere wirtschaftlicher, physikalischer, technischer, sozialer – häufig die eine oder andere Methode zur Berechnung der Näherungswerte von Funktionen aus ihren bekannten Werten an einigen Fixpunkten verwendet.

Probleme bei der Approximation solcher Funktionen treten häufig auf:

bei der Erstellung von Näherungsformeln zur Berechnung der Werte der charakteristischen Größen des untersuchten Prozesses anhand der als Ergebnis des Experiments erhaltenen Tabellendaten;

in numerischer Integration, Differentiation, Lösung von Differentialgleichungen usw.;

wenn es notwendig ist, die Werte von Funktionen an Zwischenpunkten des betrachteten Intervalls zu berechnen;

bei der Ermittlung der Werte der charakteristischen Größen des Prozesses außerhalb des betrachteten Intervalls, insbesondere bei der Prognose.

Wenn zur Modellierung eines bestimmten, durch eine Tabelle spezifizierten Prozesses eine Funktion konstruiert wird, die diesen Prozess anhand der Methode der kleinsten Quadrate näherungsweise beschreibt, spricht man von einer Näherungsfunktion (Regression) und die Aufgabe, Näherungsfunktionen selbst zu konstruieren, wird als Näherungsfunktion (Regression) bezeichnet ein Approximationsproblem sein.

In diesem Artikel werden die Möglichkeiten des MS Excel-Pakets zur Lösung solcher Probleme erläutert. Darüber hinaus werden Methoden und Techniken zum Konstruieren (Erstellen) von Regressionen für tabellarisch gegebene Funktionen (die die Grundlage der Regressionsanalyse bilden) vorgestellt.

Es gibt zwei Möglichkeiten, Regressionen in Excel zu erstellen.

Hinzufügen ausgewählter Regressionen (Trendlinien) zu einem Diagramm, das auf der Grundlage einer Datentabelle für das untersuchte Prozessmerkmal erstellt wurde (nur verfügbar, wenn ein Diagramm erstellt wurde);

Mithilfe der integrierten Statistikfunktionen des Excel-Arbeitsblatts können Sie Regressionen (Trendlinien) direkt aus der Quelldatentabelle abrufen.

Trendlinien zu einem Diagramm hinzufügen

Für eine Datentabelle, die einen bestimmten Prozess beschreibt und durch ein Diagramm dargestellt wird, verfügt Excel über ein effektives Regressionsanalysetool, mit dem Sie:

Bauen Sie auf der Grundlage der Methode der kleinsten Quadrate auf und fügen Sie dem Diagramm fünf Arten von Regressionen hinzu, die den untersuchten Prozess mit unterschiedlichem Genauigkeitsgrad modellieren.

Fügen Sie dem Diagramm eine Gleichung der konstruierten Regression hinzu.

Bestimmen Sie den Grad der Übereinstimmung der ausgewählten Regression mit den im Diagramm angezeigten Daten.

Basierend auf den Diagrammdaten können Sie mit Excel lineare, polynomische, logarithmische, exponentielle und exponentielle Regressionstypen erhalten, die durch die Gleichung gegeben sind:

y = y(x)

wobei x eine unabhängige Variable ist, die oft die Werte einer Folge natürlicher Zahlen (1; 2; 3; ...) annimmt und beispielsweise einen Countdown der Zeit des untersuchten Prozesses (Merkmale) erzeugt .

1 . Die lineare Regression eignet sich gut zur Modellierung von Merkmalen, die mit einer konstanten Rate zunehmen oder abnehmen. Dies ist das einfachste Modell des untersuchten Prozesses. Es ist nach der Gleichung aufgebaut:

y=mx+b

wobei m der Tangens der Steigung der linearen Regression an die x-Achse ist; b - Koordinate des Schnittpunkts der linearen Regression mit der y-Achse.

2 . Eine polynomielle Trendlinie eignet sich zur Beschreibung von Merkmalen, die mehrere unterschiedliche Extremwerte (Höchst- und Tiefstwerte) aufweisen. Die Wahl des Grades des Polynoms wird durch die Anzahl der Extrema des untersuchten Merkmals bestimmt. Somit kann ein Polynom zweiten Grades durchaus einen Prozess beschreiben, der nur ein Maximum oder Minimum hat; Polynom dritten Grades - nicht mehr als zwei Extrema; Polynom vierten Grades - nicht mehr als drei Extrema usw.

In diesem Fall wird die Trendlinie nach der Gleichung aufgebaut:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

wobei die Koeffizienten c0, c1, c2,... c6 Konstanten sind, deren Werte während der Konstruktion bestimmt werden.

3 . Die logarithmische Trendlinie wird erfolgreich bei der Modellierung von Merkmalen eingesetzt, deren Werte sich zunächst schnell ändern und sich dann allmählich stabilisieren.

y = c ln(x) + b

4 . Die Leistungstrendlinie liefert gute Ergebnisse, wenn die Werte der untersuchten Abhängigkeit durch eine ständige Änderung der Wachstumsrate gekennzeichnet sind. Ein Beispiel für eine solche Abhängigkeit kann ein Diagramm der gleichmäßig beschleunigten Bewegung eines Autos sein. Wenn die Daten Null- oder negative Werte enthalten, können Sie keine Leistungstrendlinie verwenden.

Es ist nach der Gleichung aufgebaut:

y = cxb

wobei die Koeffizienten b, c Konstanten sind.

5 . Eine exponentielle Trendlinie sollte verwendet werden, wenn die Änderungsrate der Daten kontinuierlich zunimmt. Für Daten, die Nullwerte oder negative Werte enthalten, ist diese Art der Näherung ebenfalls nicht anwendbar.

Es ist nach der Gleichung aufgebaut:

y=cebx

wobei die Koeffizienten b, c Konstanten sind.

Bei der Auswahl einer Trendlinie berechnet Excel automatisch den Wert von R2, der die Genauigkeit der Näherung kennzeichnet: Je näher der R2-Wert bei eins liegt, desto zuverlässiger nähert sich die Trendlinie dem untersuchten Prozess an. Bei Bedarf kann der Wert von R2 jederzeit im Diagramm angezeigt werden.

Bestimmt durch die Formel:

So fügen Sie einer Datenreihe eine Trendlinie hinzu:

Aktivieren Sie das auf Basis der Datenreihe erstellte Diagramm, d. h. klicken Sie in den Diagrammbereich. Das Element „Diagramm“ wird im Hauptmenü angezeigt.

Nachdem Sie auf diesen Punkt geklickt haben, erscheint auf dem Bildschirm ein Menü, in dem Sie den Befehl Trendlinie hinzufügen auswählen sollten.

Die gleichen Aktionen lassen sich leicht umsetzen, wenn Sie mit der Maus über das Diagramm fahren, das einer der Datenreihen entspricht, und mit der rechten Maustaste klicken. Wählen Sie im angezeigten Kontextmenü den Befehl Trendlinie hinzufügen. Auf dem Bildschirm erscheint das Dialogfeld „Trendlinie“ mit geöffneter Registerkarte „Typ“ (Abb. 1).

Danach benötigen Sie:

Wählen Sie auf der Registerkarte „Typ“ den erforderlichen Trendlinientyp aus (standardmäßig ist „Linear“ ausgewählt). Geben Sie für den Polynomtyp im Feld Grad den Grad des ausgewählten Polynoms an.

1 . Das Feld „Aufgebaut auf Serie“ listet alle Datenreihen im betreffenden Diagramm auf. Um eine Trendlinie zu einer bestimmten Datenreihe hinzuzufügen, wählen Sie ihren Namen im Feld „Aufgebaut auf Reihe“ aus.

Bei Bedarf können Sie auf der Registerkarte Parameter (Abb. 2) die folgenden Parameter für die Trendlinie festlegen:

Ändern Sie den Namen der Trendlinie im Feld Name der angenäherten (geglätteten) Kurve.

Legen Sie im Feld „Prognose“ die Anzahl der Perioden (vorwärts oder rückwärts) für die Prognose fest.

Zeigen Sie die Gleichung der Trendlinie im Diagrammbereich an, wofür Sie das Kontrollkästchen aktivieren sollten, um die Gleichung im Diagramm anzuzeigen.

den Wert der Näherungszuverlässigkeit R2 im Diagrammbereich anzeigen, für den Sie das Kontrollkästchen aktivieren sollten, den Wert der Näherungszuverlässigkeit (R^2) in das Diagramm eintragen;

Legen Sie den Schnittpunkt der Trendlinie mit der Y-Achse fest, wofür Sie das Kontrollkästchen für den Schnittpunkt der Kurve mit der Y-Achse an einem Punkt aktivieren sollten;

Klicken Sie auf die Schaltfläche „OK“, um das Dialogfeld zu schließen.

Es gibt drei Möglichkeiten, mit der Bearbeitung einer bereits erstellten Trendlinie zu beginnen:

Verwenden Sie den Befehl „Ausgewählte Trendlinie“ aus dem Menü „Format“, nachdem Sie die Trendlinie ausgewählt haben.

Wählen Sie im Kontextmenü den Befehl „Trendlinie formatieren“, der durch einen Rechtsklick auf die Trendlinie aufgerufen wird.

durch Doppelklick auf die Trendlinie.

Auf dem Bildschirm erscheint das Dialogfeld „Trendlinie formatieren“ (Abb. 3), das drei Registerkarten enthält: Ansicht, Typ, Parameter, und der Inhalt der letzten beiden stimmt vollständig mit den ähnlichen Registerkarten des Dialogfelds „Trendlinie“ überein (Abb. 1-2). ). Auf der Registerkarte „Ansicht“ können Sie den Linientyp, seine Farbe und Stärke festlegen.

Um eine bereits erstellte Trendlinie zu löschen, wählen Sie die zu löschende Trendlinie aus und drücken Sie die Entf-Taste.

Die Vorteile des betrachteten Regressionsanalysetools sind:

die relative Leichtigkeit, eine Trendlinie in Diagrammen darzustellen, ohne dafür eine Datentabelle zu erstellen;

eine ziemlich umfangreiche Liste der vorgeschlagenen Trendlinientypen, und diese Liste enthält die am häufigsten verwendeten Regressionstypen;

die Möglichkeit, das Verhalten des untersuchten Prozesses für eine beliebige (im Rahmen des gesunden Menschenverstandes) Anzahl von Schritten vorwärts und rückwärts vorherzusagen;

die Möglichkeit, die Gleichung der Trendlinie in analytischer Form zu erhalten;

die Möglichkeit, bei Bedarf eine Einschätzung der Zuverlässigkeit der Näherung zu erhalten.

Zu den Nachteilen zählen folgende Punkte:

die Konstruktion einer Trendlinie wird nur durchgeführt, wenn ein Diagramm vorhanden ist, das auf einer Reihe von Daten basiert;

Der Prozess der Generierung von Datenreihen für das untersuchte Merkmal auf der Grundlage der dafür erhaltenen Trendliniengleichungen ist etwas unübersichtlich: Die gewünschten Regressionsgleichungen werden bei jeder Änderung der Werte der ursprünglichen Datenreihe aktualisiert, jedoch nur innerhalb des Diagrammbereichs , während die Datenreihe, die auf der Grundlage des alten Liniengleichungstrends gebildet wurde, unverändert bleibt;

Wenn Sie in PivotChart-Berichten die Diagrammansicht oder den zugehörigen PivotTable-Bericht ändern, bleiben vorhandene Trendlinien nicht erhalten. Das bedeutet, dass Sie vor dem Zeichnen von Trendlinien oder einer anderen Formatierung eines PivotChart-Berichts sicherstellen müssen, dass das Layout des Berichts Ihren Anforderungen entspricht.

Trendlinien können zu Datenreihen hinzugefügt werden, die in Diagrammen wie Diagrammen, Histogrammen, flachen, nicht normalisierten Flächendiagrammen, Balken-, Streu-, Blasen- und Aktiendiagrammen dargestellt werden.

Sie können Datenreihen in 3D-, Standard-, Radar-, Kreis- und Donut-Diagrammen keine Trendlinien hinzufügen.

Verwenden integrierter Excel-Funktionen

Excel bietet außerdem ein Regressionsanalysetool zum Zeichnen von Trendlinien außerhalb des Diagrammbereichs. Zu diesem Zweck können eine Reihe statistischer Arbeitsblattfunktionen verwendet werden, mit denen Sie jedoch nur lineare oder exponentielle Regressionen erstellen können.

Excel verfügt über mehrere Funktionen zum Erstellen einer linearen Regression, insbesondere:

TREND;

Steigung und Schnitt.

Sowie mehrere Funktionen zur Konstruktion einer exponentiellen Trendlinie, insbesondere:

LGRFPca.

Es ist zu beachten, dass die Techniken zum Erstellen von Regressionen mithilfe der Funktionen TREND und GROWTH praktisch identisch sind. Das Gleiche gilt für das Funktionspaar LINEST und LGRFPRIBL. Für diese vier Funktionen werden beim Erstellen einer Wertetabelle Excel-Funktionen wie Array-Formeln verwendet, was den Prozess der Regressionserstellung etwas unübersichtlicher macht. Wir stellen außerdem fest, dass die Konstruktion einer linearen Regression unserer Meinung nach am einfachsten mit den Funktionen SLOPE und INTERCEPT zu implementieren ist, wobei die erste von ihnen die Steigung der linearen Regression und die zweite das von der Regression abgeschnittene Segment bestimmt auf der y-Achse.

Die Vorteile des integrierten Funktionstools für die Regressionsanalyse sind:

ein ziemlich einfacher Prozess der gleichen Art der Bildung von Datenreihen des untersuchten Merkmals für alle integrierten statistischen Funktionen, die Trendlinien festlegen;

eine Standardtechnik zum Konstruieren von Trendlinien basierend auf den generierten Datenreihen;

die Fähigkeit, das Verhalten des untersuchten Prozesses für die erforderliche Anzahl von Schritten vorwärts oder rückwärts vorherzusagen.

Zu den Nachteilen gehört die Tatsache, dass Excel keine integrierten Funktionen zum Erstellen anderer (außer linearer und exponentieller) Arten von Trendlinien hat. Dieser Umstand erlaubt es oft nicht, ein ausreichend genaues Modell des untersuchten Prozesses zu wählen und realitätsnahe Prognosen zu erhalten. Darüber hinaus sind bei Verwendung der Funktionen TREND und GROW die Gleichungen der Trendlinien nicht bekannt.

Es ist zu beachten, dass es sich die Autoren nicht zum Ziel gesetzt haben, den Verlauf der Regressionsanalyse mit unterschiedlichem Vollständigkeitsgrad darzustellen. Seine Hauptaufgabe besteht darin, anhand konkreter Beispiele die Fähigkeiten des Excel-Pakets bei der Lösung von Approximationsproblemen aufzuzeigen; demonstrieren, über welche effektiven Tools Excel zum Erstellen von Regressionen und Prognosen verfügt; veranschaulichen, wie relativ einfach solche Probleme selbst von einem Benutzer gelöst werden können, der nicht über umfassende Kenntnisse der Regressionsanalyse verfügt.

Beispiele für die Lösung spezifischer Probleme

Erwägen Sie die Lösung spezifischer Probleme mit den aufgeführten Tools des Excel-Pakets.

Aufgabe 1

Mit einer Tabelle mit Daten zum Gewinn eines Kraftverkehrsunternehmens für 1995-2002. Sie müssen Folgendes tun.

Erstellen Sie ein Diagramm.

Fügen Sie dem Diagramm lineare und polynomische (quadratische und kubische) Trendlinien hinzu.

Erhalten Sie mithilfe der Trendliniengleichungen tabellarische Daten zum Gewinn des Unternehmens für jede Trendlinie für 1995-2004.

Erstellen Sie eine Gewinnprognose für das Unternehmen für 2003 und 2004.

Die Lösung des Problems

Im Zellbereich A4:C11 des Excel-Arbeitsblattes tragen wir das in Abb. dargestellte Arbeitsblatt ein. 4.

Nachdem wir den Zellbereich B4:C11 ausgewählt haben, erstellen wir ein Diagramm.

Wir aktivieren das erstellte Diagramm und fügen mit der oben beschriebenen Methode nach Auswahl des Trendlinientyps im Dialogfeld „Trendlinie“ (siehe Abb. 1) abwechselnd lineare, quadratische und kubische Trendlinien zum Diagramm hinzu. Öffnen Sie im selben Dialogfeld die Registerkarte „Parameter“ (siehe Abb. 2), geben Sie im Feld „Name der annähernden (geglätteten) Kurve“ den Namen des hinzuzufügenden Trends ein und legen Sie im Feld „Prognose vorwärts für: Perioden“ fest der Wert 2, da geplant ist, eine Gewinnprognose für zwei Jahre im Voraus zu erstellen. Um die Regressionsgleichung und den Wert der Näherungszuverlässigkeit R2 im Diagrammbereich anzuzeigen, aktivieren Sie die Kontrollkästchen Gleichung auf dem Bildschirm anzeigen und platzieren Sie den Wert der Näherungszuverlässigkeit (R^2) auf dem Diagramm. Für eine bessere visuelle Wahrnehmung ändern wir den Typ, die Farbe und die Dicke der gezeichneten Trendlinien, wofür wir die Registerkarte „Ansicht“ des Dialogfelds „Trendlinienformat“ verwenden (siehe Abb. 3). Das resultierende Diagramm mit hinzugefügten Trendlinien ist in Abb. dargestellt. 5.

Um tabellarische Daten zum Gewinn des Unternehmens für jede Trendlinie für 1995-2004 zu erhalten. Verwenden wir die Gleichungen der in Abb. dargestellten Trendlinien. 5. Geben Sie dazu in den Zellen des Bereichs D3:F3 Textinformationen über den Typ der ausgewählten Trendlinie ein: Linearer Trend, Quadratischer Trend, Kubischer Trend. Geben Sie als Nächstes die lineare Regressionsformel in Zelle D4 ein und kopieren Sie diese Formel mithilfe der Füllmarkierung mit relativen Bezügen auf den Zellbereich D5:D13. Es ist zu beachten, dass jede Zelle mit einer linearen Regressionsformel aus dem Zellbereich D4:D13 eine entsprechende Zelle aus dem Zellbereich A4:A13 als Argument hat. Ebenso wird für die quadratische Regression der Zellbereich E4:E13 gefüllt, und für die kubische Regression wird der Zellbereich F4:F13 gefüllt. Daher wurde eine Prognose für den Gewinn des Unternehmens für die Jahre 2003 und 2004 erstellt. mit drei Trends. Die resultierende Wertetabelle ist in Abb. dargestellt. 6.

Aufgabe 2

Erstellen Sie ein Diagramm.

Fügen Sie dem Diagramm logarithmische, exponentielle und exponentielle Trendlinien hinzu.

Leiten Sie die Gleichungen der erhaltenen Trendlinien sowie die Werte der Näherungszuverlässigkeit R2 für jede von ihnen her.

Erhalten Sie mithilfe der Trendliniengleichungen tabellarische Daten zum Gewinn des Unternehmens für jede Trendlinie für 1995-2002.

Erstellen Sie anhand dieser Trendlinien eine Gewinnprognose für das Unternehmen für 2003 und 2004.

Die Lösung des Problems

Wenn wir der bei der Lösung von Problem 1 angegebenen Methodik folgen, erhalten wir ein Diagramm mit hinzugefügten logarithmischen, exponentiellen und exponentiellen Trendlinien (Abb. 7). Darüber hinaus füllen wir unter Verwendung der erhaltenen Trendliniengleichungen die Wertetabelle für den Gewinn des Unternehmens aus, einschließlich der prognostizierten Werte für 2003 und 2004. (Abb. 8).

Auf Abb. 5 und Abb. Es ist ersichtlich, dass das Modell mit logarithmischem Trend dem niedrigsten Wert der Näherungszuverlässigkeit entspricht

R2 = 0,8659

Die höchsten Werte von R2 entsprechen Modellen mit einem Polynomtrend: quadratisch (R2 = 0,9263) und kubisch (R2 = 0,933).

Aufgabe 3

Mit einer Tabelle mit Daten zum Gewinn eines Kraftverkehrsunternehmens für 1995-2002, die in Aufgabe 1 angegeben ist, müssen Sie die folgenden Schritte ausführen.

Erhalten Sie Datenreihen für lineare und exponentielle Trendlinien mit den Funktionen TREND und GROW.

Erstellen Sie mithilfe der Funktionen TREND und WACHSTUM eine Gewinnprognose für das Unternehmen für 2003 und 2004.

Erstellen Sie für die Ausgangsdaten und die empfangenen Datenreihen ein Diagramm.

Die Lösung des Problems

Nutzen wir das Arbeitsblatt von Aufgabe 1 (siehe Abb. 4). Beginnen wir mit der TREND-Funktion:

Wählen Sie den Zellbereich D4:D11 aus, der mit den Werten der TREND-Funktion gefüllt werden soll, die den bekannten Daten zum Gewinn des Unternehmens entsprechen;

Rufen Sie den Befehl „Funktion“ aus dem Menü „Einfügen“ auf. Wählen Sie im angezeigten Dialogfeld „Funktionsassistent“ die Funktion TREND aus der Kategorie „Statistisch“ aus und klicken Sie dann auf die Schaltfläche „OK“. Der gleiche Vorgang kann durch Drücken der Schaltfläche (Funktion „Einfügen“) in der Standardsymbolleiste ausgeführt werden.

Geben Sie im angezeigten Dialogfeld „Funktionsargumente“ den Zellbereich C4:C11 in das Feld „Known_values_y“ ein. im Feld Known_values_x – der Bereich der Zellen B4:B11;

Um die eingegebene Formel in eine Matrixformel umzuwandeln, verwenden Sie die Tastenkombination + + .

Die Formel, die wir in die Formelleiste eingegeben haben, sieht folgendermaßen aus: =(TREND(C4:C11;B4:B11)).

Dadurch wird der Zellbereich D4:D11 mit den entsprechenden Werten der TREND-Funktion gefüllt (Abb. 9).

Erstellung einer Prognose des Unternehmensgewinns für 2003 und 2004. notwendig:

Wählen Sie den Zellbereich D12:D13 aus, in den die von der TREND-Funktion vorhergesagten Werte eingegeben werden.

Rufen Sie die TREND-Funktion auf und geben Sie im angezeigten Dialogfeld „Funktionsargumente“ in das Feld „Known_values_y“ ein: den Zellbereich C4:C11; im Feld Known_values_x – der Bereich der Zellen B4:B11; und im Feld New_values_x – der Zellbereich B12:B13.

Wandeln Sie diese Formel mit der Tastenkombination Strg + Umschalt + Eingabetaste in eine Arrayformel um.

Die eingegebene Formel sieht wie folgt aus: =(TREND(C4:C11;B4:B11;B12:B13)) und der Zellbereich D12:D13 wird mit den vorhergesagten Werten der TREND-Funktion gefüllt (siehe Abb. 9).

Ebenso wird eine Datenreihe mit der Funktion GROWTH gefüllt, die bei der Analyse nichtlinearer Abhängigkeiten verwendet wird und genauso funktioniert wie ihr lineares Gegenstück TREND.

Abbildung 10 zeigt die Tabelle im Formelanzeigemodus.

Für die Ausgangsdaten und die erhaltenen Datenreihen gilt das in Abb. dargestellte Diagramm. elf.

Aufgabe 4

Mit einer Datentabelle zum Eingang von Leistungsanträgen beim Dispositionsdienst eines Kraftverkehrsunternehmens für den Zeitraum vom 1. bis 11. Tag des laufenden Monats sind folgende Maßnahmen durchzuführen.

Erhalten Sie Datenreihen für die lineare Regression: mit den Funktionen SLOPE und INTERCEPT; mit der LINEST-Funktion.

Rufen Sie mit der Funktion LYFFPRIB eine Datenreihe für die exponentielle Regression ab.

Erstellen Sie mit den oben genannten Funktionen eine Prognose über den Eingang von Bewerbungen beim Versanddienst für den Zeitraum vom 12. bis 14. Tag des aktuellen Monats.

Erstellen Sie für die ursprüngliche und die empfangene Datenreihe ein Diagramm.

Die Lösung des Problems

Beachten Sie, dass im Gegensatz zu den Funktionen TREND und GROW keine der oben aufgeführten Funktionen (SLOPE, INTERCEPTION, LINEST, LGRFPRIB) Regressionen sind. Diese Funktionen spielen nur eine Hilfsrolle und bestimmen die notwendigen Regressionsparameter.

Bei linearen und exponentiellen Regressionen, die mit den Funktionen SLOPE, INTERCEPT, LINEST und LGRFINB erstellt wurden, ist das Aussehen ihrer Gleichungen immer bekannt, im Gegensatz zu den linearen und exponentiellen Regressionen, die den Funktionen TREND und GROWTH entsprechen.

1 . Erstellen wir eine lineare Regression mit der folgenden Gleichung:

y=mx+b

unter Verwendung der Funktionen SLOPE und INTERCEPT, wobei die Steigung der Regression m durch die Funktion SLOPE und der konstante Term b durch die Funktion INTERCEPT bestimmt wird.

Dazu führen wir folgende Aktionen durch:

Geben Sie die Quelltabelle im Bereich der Zellen A4:B14 ein;

Der Wert des Parameters m wird in Zelle C19 bestimmt. Wählen Sie aus der Kategorie „Statistik“ die Funktion „Steigung“ aus. Geben Sie den Zellbereich B4:B14 in das Feld „known_values_y“ und den Zellbereich A4:A14 in das Feld „known_values_x“ ein. Die Formel wird in Zelle C19 eingegeben: =SLOPE(B4:B14;A4:A14);

Mit einer ähnlichen Methode wird der Wert des Parameters b in Zelle D19 bestimmt. Und sein Inhalt wird so aussehen: = INTERCEPT(B4:B14;A4:A14). Somit werden die Werte der Parameter m und b, die zum Aufbau einer linearen Regression erforderlich sind, jeweils in den Zellen C19, D19 gespeichert;

Dann geben wir die lineare Regressionsformel in Zelle C4 in der Form ein: = $ C * A4 + $ D. In dieser Formel werden die Zellen C19 und D19 mit absoluten Bezügen geschrieben (die Zelladresse sollte sich bei eventuellem Kopieren nicht ändern). Das absolute Referenzzeichen $ kann entweder über die Tastatur oder mit der Taste F4 eingegeben werden, nachdem der Cursor auf der Zellenadresse platziert wurde. Kopieren Sie diese Formel mithilfe des Füllpunkts in den Zellbereich C4:C17. Wir erhalten die gewünschte Datenreihe (Abb. 12). Da es sich bei der Anzahl der Anfragen um eine Ganzzahl handelt, sollten Sie das Zahlenformat auf der Registerkarte „Zahl“ des Fensters „Zellenformat“ mit der Anzahl der Dezimalstellen auf 0 setzen.

2 . Lassen Sie uns nun eine lineare Regression erstellen, die durch die Gleichung gegeben ist:

y=mx+b

mit der LINEST-Funktion.

Dafür:

Geben Sie die LINEST-Funktion als Arrayformel in den Zellbereich C20:D20 ein: =(LINEST(B4:B14;A4:A14)). Als Ergebnis erhalten wir den Wert des Parameters m in Zelle C20 und den Wert des Parameters b in Zelle D20;

Geben Sie die Formel in Zelle D4 ein: =$C*A4+$D;

Kopieren Sie diese Formel mithilfe der Füllmarkierung in den Zellbereich D4:D17 und erhalten Sie die gewünschte Datenreihe.

3 . Wir erstellen eine exponentielle Regression mit der Gleichung:

Mit Hilfe der Funktion LGRFPRIBL wird dies auf ähnliche Weise durchgeführt:

Geben Sie im Bereich der Zellen C21:D21 die Funktion LGRFPRIBL als Arrayformel ein: =( LGRFPRIBL (B4:B14;A4:A14)). In diesem Fall wird der Wert des Parameters m in Zelle C21 und der Wert des Parameters b in Zelle D21 bestimmt;

die Formel wird in Zelle E4 eingegeben: =$D*$C^A4;

Mithilfe der Füllmarkierung wird diese Formel in den Zellbereich E4:E17 kopiert, wo sich die Datenreihe für die exponentielle Regression befindet (siehe Abb. 12).

Auf Abb. 13 zeigt eine Tabelle, in der wir die von uns verwendeten Funktionen mit den erforderlichen Zellbereichen sowie Formeln sehen können.

Wert R 2 angerufen Bestimmungskoeffizient.

Die Aufgabe der Konstruktion einer Regressionsabhängigkeit besteht darin, den Vektor der Koeffizienten m des Modells (1) zu finden, bei dem der Koeffizient R den Maximalwert annimmt.

Zur Beurteilung der Signifikanz von R wird der nach der Formel berechnete Fisher-F-Test verwendet

Wo N- Stichprobengröße (Anzahl der Experimente);

k ist die Anzahl der Modellkoeffizienten.

Wenn F einen kritischen Wert für die Daten überschreitet N Und k und dem akzeptierten Konfidenzniveau, dann wird der Wert von R als signifikant angesehen. Tabellen mit kritischen Werten von F sind in Nachschlagewerken zur mathematischen Statistik enthalten.

Somit wird die Signifikanz von R nicht nur durch seinen Wert bestimmt, sondern auch durch das Verhältnis zwischen der Anzahl der Experimente und der Anzahl der Koeffizienten (Parameter) des Modells. Tatsächlich beträgt das Korrelationsverhältnis für n=2 für ein einfaches lineares Modell 1 (durch 2 Punkte auf der Ebene kann man immer eine einzelne gerade Linie zeichnen). Wenn es sich bei den experimentellen Daten jedoch um Zufallsvariablen handelt, sollte einem solchen Wert von R mit großer Vorsicht vertraut werden. Um ein signifikantes R und eine zuverlässige Regression zu erhalten, wird normalerweise darauf abgezielt, sicherzustellen, dass die Anzahl der Experimente die Anzahl der Modellkoeffizienten (n>k) deutlich übersteigt.

Um ein lineares Regressionsmodell zu erstellen, müssen Sie:

1) Bereiten Sie eine Liste mit n Zeilen und m Spalten vor, die die experimentellen Daten enthält (Spalte mit dem Ausgabewert). Y muss entweder an erster oder letzter Stelle in der Liste stehen); Nehmen wir zum Beispiel die Daten der vorherigen Aufgabe und fügen eine Spalte namens „Periodennummer“ hinzu, in der die Anzahl der Perioden von 1 bis 12 nummeriert wird. (Das sind die Werte.) X)

2) Gehen Sie zum Menü Daten/Datenanalyse/Regression

Wenn der Punkt „Datenanalyse“ im Menü „Extras“ fehlt, dann sollten Sie zum Punkt „Add-Ins“ desselben Menüs gehen und das Kontrollkästchen „Analysepaket“ aktivieren.

3) Stellen Sie im Dialogfeld „Regression“ Folgendes ein:

Eingabeintervall Y;

Eingabeintervall X;

Ausgabeintervall – die obere linke Zelle des Intervalls, in dem die Berechnungsergebnisse platziert werden (es wird empfohlen, es auf einem neuen Arbeitsblatt zu platzieren);