Methode der kleinsten Quadrate in Excel. Regressionsanalyse

Methode der kleinsten Quadrate (LSM) bezieht sich aufBereich der Regressionsanalyse. Es hat viele Verwendungszwecke, da es eine ungefähre Darstellung einer bestimmten Funktion durch andere einfachere Funktionen ermöglicht. OLS kann bei der Verarbeitung von Beobachtungen äußerst nützlich sein und wird aktiv verwendet, um einige Werte aus den Ergebnissen von Messungen anderer zu schätzen, die zufällige Fehler enthalten. In diesem Artikel erfahren Sie, wie Sie Berechnungen der kleinsten Quadrate in Excel implementieren.

Problemstellung an einem konkreten Beispiel

Angenommen, es gibt zwei Exponenten X und Y.Darüber hinaus hängt Y von X ab. Da MNCs aus der Sicht der Regressionsanalyse für uns von Interesse sind (in Excel werden die Methoden mithilfe integrierter Funktionen implementiert), lohnt es sich, sofort auf ein bestimmtes Problem einzugehen.

Also sei X die Verkaufsfläche des Lebensmittelgeschäfts, gemessen in Quadratmetern, und Y der Jahresumsatz, definiert in Millionen Rubel.

Es ist eine Prognose erforderlich, welcher Umsatz (Y)wird im Geschäft sein, wenn es eine oder andere Verkaufsfläche hat. Offensichtlich steigt die Funktion Y = f (X), da der Hypermarkt mehr Waren verkauft als der Stand.

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

Angenommen, wir haben eine Tabelle, die gemäß den Daten für n Geschäfte erstellt wurde.

Laut mathematischer Statistik ergeben sich die Ergebnisseist mehr oder weniger korrekt, wenn die Daten von mindestens 5-6 Objekten untersucht werden. Außerdem können keine "abnormalen" Ergebnisse verwendet werden. Insbesondere in einer kleinen Elite-Boutique kann der Umsatz um ein Vielfaches höher sein als in großen Einzelhandelsgeschäften der „Masmarket“ -Klasse.

Das Wesen der Methode

Die Tabellendaten können auf der kartesischen Ebene als Punkte M dargestellt werden₁ (mit₁y₁), ... M_Herr (mit_Herry_Herr) Die Lösung des Problems beschränkt sich nun auf die Auswahl der Näherungsfunktion y = f (x), bei der ein Graph so nah wie möglich an den Punkten M verläuft_1, M_{2, ..}M_Herr.

Natürlich können Sie das Polynom high verwendenGrad, aber diese Option ist nicht nur schwierig zu implementieren, sondern einfach falsch, da sie nicht den Haupttrend widerspiegelt, der erkannt werden muss. Die vernünftigste Lösung besteht darin, die gerade Linie y = ax + b zu finden, die die experimentellen Daten und genauer die Koeffizienten a und b am besten approximiert.

Genauigkeitsbewertung

In jeder Näherung ist die Schätzung seiner Genauigkeit von besonderer Bedeutung. Mit e bezeichnen_und Differenz (Abweichung) zwischen funktionalen und experimentellen Werten für Punkt x_undd.h._und = y_und - f (x_und).

Offensichtlich, um die Genauigkeit der Näherung zu schätzenDie Summe der Abweichungen kann verwendet werden, d. h., wenn die gerade Linie für eine ungefähre Darstellung der Abhängigkeit von X von Y gewählt wird, sollte diejenige mit dem kleinsten Wert der Summe e bevorzugt werden_und an allen betrachteten Stellen. Allerdings ist nicht alles so einfach, da neben positiven Abweichungen auch praktisch negative vorhanden sein werden.

Sie können das Problem mit den Abweichungsmodulen lösenoder ihre Quadrate. Die letztere Methode wurde am weitesten verbreitet. Es wird in vielen Bereichen verwendet, einschließlich der Regressionsanalyse (in Excel wird es mit zwei integrierten Funktionen implementiert) und hat sich seit langem als wirksam erwiesen.

Methode der kleinsten Quadrate

Wie Sie wissen, gibt es in Excel eine integrierteAutosummenfunktion, mit der Sie die Werte aller Werte im ausgewählten Bereich berechnen können. Daher hindert uns nichts daran, den Wert des Ausdrucks zu berechnen (e₁² + e₂² + e₃²+ ... e_Herr²).

In einer mathematischen Notation hat dies die Form:

Da die Entscheidung getroffen wurde, zunächst eine gerade Linie zu verwenden, haben wir:

Das Problem, die Linie zu finden, die die spezifische Abhängigkeit der Größen X und Y am besten beschreibt, besteht also darin, die Minimalfunktion zweier Variablen zu berechnen:

Dazu müssen wir partielle Ableitungen in Bezug auf die neuen Variablen a und b auf Null setzen und ein primitives System lösen, das aus zwei Gleichungen mit 2 Unbekannten der Form besteht:

Nach einigen einfachen Transformationen, einschließlich Teilen durch 2 und Manipulieren der Summen, erhalten wir:

Wenn wir es zum Beispiel 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, wie hoch der Umsatz des Geschäfts in einem bestimmten Bereich sein wird. Die gerade Linie y = a^*x + b^*Darstellen eines Regressionsmodells fürdas fragliche Beispiel. Natürlich können Sie das genaue Ergebnis nicht finden, aber es hilft Ihnen, eine Vorstellung davon zu bekommen, ob sich der Kauf auf das Guthaben des Geschäfts in einem bestimmten Bereich auszahlt.

Implementieren einer Methode der kleinsten Quadrate in Excel

"Excel" hat eine Funktion zur Berechnung des Wertesnach OLS. Es hat die folgende Form: "TREND" (bekannte Werte von Y; bekannte Werte von X; neue Werte von X; const.). Wir wenden die Formel zur Berechnung von OLS in Excel auf unsere Tabelle an.

Dazu in der Zelle, in der es sein sollDas Ergebnis der Berechnung nach der Methode der kleinsten Quadrate wird in Excel angezeigt. Geben Sie das Zeichen "=" ein und wählen Sie die Funktion "TREND". Füllen Sie im folgenden Fenster die entsprechenden Felder aus und markieren Sie Folgendes:

• der Bereich bekannter Werte für Y (in diesem Fall Daten für den Umsatz);
• Bereich x₁, ... x_Herrd.h. die Größe der Verkaufsfläche;
• bekannte und unbekannte x-Werte, für die Sie die Größe des Warenumsatzes ermitteln müssen (Informationen zu deren Position auf dem Arbeitsblatt siehe unten).

Zusätzlich enthält die Formel die logische Variable "Const". Wenn Sie in das entsprechende Feld 1 eingeben, bedeutet dies, dass Berechnungen durchgeführt werden sollen, vorausgesetzt, dass b = 0 ist.

Wenn Sie die Vorhersage für mehr als einen Wert von x kennen müssen, sollten Sie nach der Eingabe der Formel nicht auf "Enter" klicken, sondern die Kombination "Shift" + "Control" + "Enter" auf der Tastatur eingeben.

Einige Funktionen

Möglicherweise ist sogar eine Regressionsanalyse verfügbar.Teekannen. Die Excel-Formel zur Vorhersage des Werts eines Arrays unbekannter Variablen - "TREND" - kann auch von Personen verwendet werden, die noch nie von der Methode der kleinsten Quadrate gehört haben. Kennen Sie nur einige Merkmale ihrer Arbeit. Insbesondere:

• Wenn Sie einen Bereich bekannter Werte eingebeny-Variable in einer Zeile oder Spalte, dann wird jede Zeile (Spalte) mit bekannten x-Werten vom Programm als separate Variable wahrgenommen.
• Wenn im "TREND" -Fenster der Bereich mitWenn Sie die Funktion in Excel verwenden, betrachtet das Programm sie als ein Array, das aus ganzen Zahlen besteht, deren Anzahl dem Bereich mit den angegebenen Werten der Variablen y entspricht.
• Um ein Array von "vorhergesagten" Werten am Ausgang zu erhalten, muss der Ausdruck zur Berechnung des Trends als Array-Formel eingegeben werden.
• Wenn keine neuen x-Werte angegeben werden, dann die Funktion"TREND" hält sie für ebenso berühmt. Wenn sie nicht angegeben werden, wird Array 1 als Argument verwendet. 2; 3; 4; ..., was dem Bereich mit den bereits eingestellten Parametern y entspricht.
• Der Bereich mit den neuen x-Werten solltebestehen aus den gleichen oder mehreren Zeilen oder Spalten wie ein Bereich mit vorgegebenen y-Werten. Mit anderen Worten, es muss eine proportionale unabhängige Variable sein.
• In einem Array mit bekannten x-Wertenenthält mehrere Variablen. Wenn wir jedoch nur von einem sprechen, ist es erforderlich, dass die Bereiche mit den angegebenen Werten von x und y verhältnismäßig sind. Bei mehreren Variablen muss der Bereich mit den angegebenen Werten von y in eine Spalte oder in eine Zeile passen.

Funktion vorhersagen

Регрессионный анализ в Excel реализуется с mit mehreren Funktionen. Eine davon heißt „PREDICTION“. Es ähnelt "TRENDS", das heißt, es erzeugt das Ergebnis von Berechnungen unter Verwendung der Methode der kleinsten Quadrate. 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 zukünftigen Wert eines bestimmten Indikators anhand eines linearen Trends vorhersagen können.