Verfahren von kleinsten Quadraten in Excel-Beispiel. Lineare gepaarte Regressionsanalyse. Einige Wörter über die Richtigkeit der zur Vorhersage verwendeten Quelldaten

Das kleinste Quadrate-Verfahren ist ein mathematisches Verfahren zum Erstellen einer linearen Gleichung, die am besten dem Satz von zwei Zahlenreihen entspricht. Der Zweck dieser Methode besteht darin, den gesamten quadratischen Fehler zu minimieren. Das Excel-Programm verfügt über Werkzeuge, mit denen dieses Verfahren beim Berechnen angewendet werden kann. Lass uns umgehen, wie es fertig ist.

Die kleinste Quadrate-Methode (MNC) ist eine mathematische Beschreibung der Abhängigkeit von einer Variablen aus dem zweiten. Es kann beim Vorhersagen verwendet werden.

Ermöglichung der Add-On-"Lösungssuche"

Um MNC in Excel verwenden zu können, müssen Sie das Add-On einschalten "Lösung Lösung"was standardmäßig deaktiviert ist.

Jetzt eine Funktion Suche nach Lösungen. Excel ist aktiviert und seine Werkzeuge erschienen auf dem Band.

Bedingungen des Problems

Wir beschreiben die Verwendung von MNK in einem bestimmten Beispiel. Wir haben zwei Zahlenreihen x. und y. Die Reihenfolge davon ist in dem Bild unten dargestellt.

Die genaueste Abhängigkeit kann die Funktion beschreiben:

Zur gleichen Zeit ist es bekannt, dass wann x \u003d 0. y. Auch gleich 0 . Daher kann diese Gleichung durch Sucht beschrieben werden y \u003d nx. .

Wir müssen die Mindestsumme der Felder des Unterschieds finden.

Entscheidung

Wenden Sie uns zur Beschreibung der direkten Anwendung der Methode.

Wie Sie sehen, ist die Verwendung des kleinsten quadratischen Methodenverfahrens ein ziemlich kompliziertes mathematisches Verfahren. Wir zeigten es im einfachsten Beispiel in Aktion, und es gibt wesentlich komplexere Fälle. Das Microsoft Excel Toolkit ist jedoch so konzipiert, dass er die Berechnungen vereinfacht.

Die kleinste Quadrate-Methode (MNC) bezieht sich auf den Bereich der Regressionsanalyse. Es hat viele Anwendungen, da Sie ermöglicht, eine ungefähre Darstellung der angegebenen Funktion von anderen einfacher durchzuführen. MNA kann bei der Verarbeitung von Beobachtungen äußerst nützlich sein, und wird aktiv verwendet, um einige Werte gemäß den Ergebnissen der Messungen anderer zufälliger Fehler zu bewerten. In diesem Artikel erfahren Sie, wie Sie Berechnungen mithilfe der Methode der kleinsten Quadrate in Excel implementieren.

Festlegen der Aufgabe in einem bestimmten Beispiel

Angenommen, es gibt zwei Indikatoren X und Y. Und Y hängt von X ab. Da MNK an uns aus der Sicht der Regressionsanalyse (in Excel ist, werden seine Methoden mit eingebetteten Funktionen implementiert), dann ist es notwendig, sofort zu gehen die Berücksichtigung einer bestimmten Aufgabe.

Also, sei x der Handelsbereich des Lebensmittelgeschäfts, gemessen in Quadratmetern und y-jährlicher Umsatz, ermittelt in Millionen von Rubel.

Es ist erforderlich, eine Prognose zu erstellen, in der der Handel (y) im Laden ist, wenn er einen oder einen anderen Einkaufszimmern hat. Natürlich nimmt die Funktion Y \u003d F (x) zunimmt, da der Hypermarkt weitere Produkte verkauft als ein Stall.

Einige Wörter über die Richtigkeit der zur Vorhersage verwendeten Quelldaten

Angenommen, wir haben eine Tabelle, die nach Daten für N-Shops gebaut wurde.

Nach mathematischen Statistiken sind die Ergebnisse mehr oder weniger korrekt, wenn Daten für mindestens 5-6 Objekte untersucht werden. Darüber hinaus ist es unmöglich, "anomale" Ergebnisse zu verwenden. Insbesondere kann eine Elite-kleine Boutique ein Umsatz in Zeiten haben, der zuletzt größer ist als der Handelsumsatz großer Handelspunkte der Maseret-Klasse.

Die Essenz der Methode

Tabellendaten können auf einer decartesischen Ebene in Form von Punkten M 1 (x 1, y 1), ... m n (x n, y) dargestellt werden. Nun wird die Lösung des Problems auf die Auswahl der angenähernden Funktion y \u003d f (x) reduziert, die ein Diagramm aufweist, das so nahe wie möglich an den Punkten M 1, M 2, .. M N.

Natürlich ist es möglich, ein Polynom mit hohem Grad zu verwenden, aber diese Option ist jedoch nicht nur schwieriger, sondern ist einfach falsch, da er den Haupttrend nicht widerspiegelt, dass es notwendig ist, erkannt zu werden. Die angemessenste Lösung ist die Suche nach einem direkten Y \u003d AX + B, der die experimentellen Daten am besten bringt, und genauer der Koeffizienten - A und B.

Genauigkeitsschätzung

In jeder Annäherung erwirbt er besondere Bedeutung, um seine Genauigkeit zu beurteilen. Bezeichnen Sie von E I die Differenz (Abweichung) zwischen den funktionalen und experimentellen Werten für den Punkt X I, d. H. E i \u003d y i - f (x i).

Es ist offensichtlich, dass ein Betrag von Abweichungen verwendet werden kann, um die Genauigkeit der Annäherung zu schätzen, dh wenn Sie die gerade Linie für eine ungefähre Darstellung x von y auswählen, müssen Sie den kleinsten Wert der Höhe des EI in Alle unter Berücksichtigung von Punkten. Es ist jedoch nicht alles so einfach, da wir zusammen mit positiven Abweichungen fast anwesend und negativ sein werden.

Sie können das Problem mit Abweichungsmodulen oder deren Quadrate lösen. Die letzte Methode wurde weit verbreitet. Es wird in vielen Bereichen eingesetzt, einschließlich der Regressionsanalyse (in Excel wird seine Implementierung mit zwei eingebauten Funktionen durchgeführt) und hat sich seit langem seine Wirksamkeit bewiesen.

Am wenigsten quadratische Methode.

In Excel gibt es, wie bekannt, eine integrierte autosie-Funktion, mit der Sie die Werte aller in dem speziellen Bereich befindlichen Werte berechnen können. So wird uns nichts beeinträchtigen, um den Wert des Ausdrucks zu berechnen (E 1 2 + E 2 2 + E 3 2 + ... E N 2).

In mathematischer Platte hat es das Formular:

Da es ursprünglich beschlossen wurde, sich mit einer geraden Linie zu nähern, haben wir:

Somit wird die Aufgabe, eine gerade Linie zu finden, die die spezifische Abhängigkeit der Werte von X und Y am besten beschreibt, auf die Berechnung der Mindestfunktion von zwei Variablen reduziert:

Dazu ist es notwendig, nach neuen Variablen A und B Null zu nutzen, und lösen das primitive System, das aus zwei Gleichungen mit 2 unbekannten Arten besteht:

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

Lösung, zum Beispiel durch das Gerät, erhalten wir einen stationären Punkt mit einigen Koeffizienten A * und B *. Dies ist ein Minimum, dh zur Vorhersage, der der Handel an einem bestimmten Bereich am Laden befindet, ist die gerade Linie y \u003d a * x + b * geeignet, was ein Regressionsmodell für ein Beispiel ist, das diskutiert wird. Natürlich erlaubt es nicht, ein genaues Ergebnis zu finden, wird jedoch dazu beitragen, eine Vorstellung davon zu erhalten, ob der Kauf eines konkreten Bereichs erworben wird.

So implementieren Sie die Methode der kleinsten Quadrate in Excel

In "Excel" gibt es eine Funktion zum Berechnen des Werts von MNA. Es hat das folgende Formular: "Trend" (bekannt. Werte Y; bekannt. X-Werte; neue Werte x; const.). Wenden Sie die MNC-Berechnung-Formel in Excel an unseren Tisch an.

Dazu in der Zelle, in der das Ergebnis der Berechnung der kleineren Quadrate in Excel angezeigt werden sollte, führen wir das Zeichen "\u003d" ein, das die Funktion "Trend" auswählen. Füllen Sie im offenen Fenster die entsprechenden Felder aus, hervorheben:

• bereich bekannter Werte für y (in diesem Fall Daten zum Umsatz);
• range X 1, ... x N, d. H. Die Werte des Einzelhandelsraums;
• und bekannte und unbekannte X-Werte, für die es erforderlich ist, die Größe des Umsatzes herauszufinden (Informationen zu ihrem Standort im Arbeitsblatt, siehe unten).

Darüber hinaus hat die Formel eine logische Variable "const". Wenn Sie in das entsprechende Feld 1 eingehen, bedeutet dies, dass die Berechnungen vorgenommen werden sollen, glauben Sie, dass B \u003d 0 glaubt.

Wenn Sie die Prognose für mehr als einen Wert x lernen müssen, sollten Sie nach der Eingabe der Formel nicht auf die "ENTER" klicken, aber Sie müssen die Kombination "SHIFT" + "CONTROL" + "ENTER" wählen. Tastatur.

Einige Eigenschaften

Die Regressionsanalyse kann auch an Teekannen erreichbar sein. Excel's Formel zur Vorhersage des Werts eines Arrays unbekannter Variablen - "Trend" - selbst solche, die noch nie von der Methode der kleinsten Quadrate gehört haben, können verwendet werden. Es reicht aus, nur um einige Merkmale ihrer Arbeit zu kennen. Insbesondere:

• Wenn Sie den Bereich bekannter Werte der Variablen y in einer Zeile oder Spalte anordnen, wird jede Zeile (Säule) mit bekannten X-Werten vom Programm als separate Variable wahrgenommen.
• Wenn das Fenster "Trend" keinen Bereich mit bekanntem X angibt, dann wird das Programm bei der Verwendung der Funktion in Excel es als ein Array, das aus Ganzzahlen besteht, in Betracht, deren Anzahl des Bereichs mit den angegebenen Werten entspricht Der Variablen y.
• Um ein Array von "vorhergesagten" Werten am Ausgang zu erhalten, muss der Ausdruck zum Berechnen des Trends als Array-Formel verabreicht werden.
• Wenn die neuen X-Werte nicht angegeben sind, hält die Funktion "Trend" der Berühmte der berühmten. Wenn sie nicht angegeben sind, wird ein Array von 1 als Argument genommen; 2; 3; 4; ..., das mit einem Bereich mit bereits angegebenen Parametern y angemessen ist.
• Ein Bereich mit neuen Werten x sollte aus den gleichen oder mehr Zeilen oder Spalten als Bereich mit den angegebenen Y-Werten bestehen. Mit anderen Worten, es muss angemessen zu unabhängigen Variablen sein.
• In einem Array mit bekannten X-Werten können mehrere Variablen enthalten sein. Wenn wir jedoch nur um eins sprechen, ist es jedoch erforderlich, dass die Bereiche mit den angegebenen X- und Y-Werten anteilig sind. Bei mehreren Variablen ist es erforderlich, dass der Bereich mit den angegebenen Werten des Y in einer Spalte oder in einer Reihe untergebracht ist.

Predicz-Funktion.

Die Regressionsanalyse in Excel wird mit mehreren Funktionen implementiert. Einer von ihnen heißt "Prognosts". Es ist dem "Trend" ähnlich, das heißt das Ergebnis von Berechnungen mit der Methode der kleinsten Quadrate. Nur für ein X, für das y unbekannt ist.

Jetzt kennen Sie die Formel in Excel für Teekannen, sodass den Wert des zukünftigen Werts eines oder eines anderen Indikators gemäß dem linearen Trend vorhersagen können.

Eine der Methoden zum Untersuchen von stochastischen Krawatten zwischen den Zeichen ist eine Regressionsanalyse.
Die Regressionsanalyse ist die Schlussfolgerung der Regressionsgleichung, mit der es einen Durchschnittswert einer zufälligen Variablen (Zeichenergebnis) gibt, wenn der Wert von anderen (oder anderen) Variablen (Faktoren) bekannt ist. Es enthält die folgenden Schritte:

1. auswahl des Kommunikationsformulars (Art der analytischen Regressionsgleichung);
2. schätzung der Parameter der Gleichung;
3. bewertung der Qualität der analytischen Regressionsgleichung.

Meistens, um die Verwendung von Parametern zu schätzen methode von kleinsten Quadraten (MNC).
Die Methode der kleinsten Quadrate ergibt die besten (wohlhabenden, effizienten und entriegelten) Schätzungen der Parameter der Regressionsgleichung. Aber nur, wenn bestimmte Voraussetzungen relativ zu einem zufälligen Begriff (U) und einer unabhängigen Variablen (x) durchgeführt werden (siehe Hintergründe des MNC).

Das Problem der Auswertung der Parameter der Linearpaar-Gleichung durch die Methode der kleinsten Quadrate Es besteht folgendermaßen: Um solche Schätzungen der Parameter zu erhalten, bei denen die Summe der Quadrate der Abweichungen der tatsächlichen Werte des effektiven Signs - y i auf den berechneten Werten minimal ist.
Formal kriterium mnk. Sie können so schreiben: .

Klassifizierung von Methoden der kleinsten Quadrate

1. Am wenigsten quadratische Methode.
2. Die maximale wahrheitsgemäße Methode (für ein normales klassisches lineares Regressionsmodell wird die Normalität der Regressionsrückstände verschoben).
3. Die generalisierte Methode der kleineren Quadrate von Omna wird im Falle einer Autokorrelation von Fehlern und bei der Heterosdastizität verwendet.
4. Die Methode der suspendierten kleinsten Quadrate (ein besonderer Fall von Omna mit heter-visasiten Rückständen).

Wir veranschaulichen die Essenz klassische kleinste quadratische Methode grafisch grafisch. Dazu konstruieren wir einen Punktplan gemäß den Beobachtungen (x i, y i, i \u003d 1; n) in einem rechteckigen Koordinatensystem (ein solches Punktdiagramm wird als Korrelationsfeld bezeichnet). Wir werden versuchen, eine gerade Linie zu wählen, die den Punkten des Korrelationsfelds am nächsten liegt. Gemäß dem Mindestquadrate-Verfahren wird die Leitung so ausgewählt, dass die Summe der Quadrate der vertikalen Abstände zwischen den Punkten des Korrelationsfelds und dieser Linie minimal sein würde.

Mathematische Aufzeichnung dieser Aufgabe: .
Die Werte von y i und x i \u003d 1 ... n sind uns bekannt, dies sind Beobachtungsdaten. In der Funktion S sind sie Konstanten. Variablen in dieser Funktion sind die gewünschten Parameterschätzungen - ,. Um ein Minimum an den 2-variablen Funktionen zu finden, ist es erforderlich, die privaten Derivate dieser Funktion für jeden der Parameter zu berechnen und sie null zu gleichen, d. H. .
Infolgedessen erhalten wir ein System von 2 normalen linearen Gleichungen:
Lösung dieses Systems finden wir die gewünschten Parameterschätzungen:

Die Richtigkeit der Berechnung der Parameter der Regressionsgleichung kann durch den Vergleich von Beträgen (vielleicht etwas Diskrepanz aufgrund der Rundungsberechnungen) getestet werden.
Um die Parameterschätzungen zu berechnen, können Sie Tabelle 1 erstellen.
Regressionskoeffizientzeichen Zeigt die Kommunikationsrichtung an (wenn B\u003e 0, Linie direkt ist, wenn b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Formal ist der Wert des Parameters A der Durchschnittswert von y mit x gleich Null. Wenn der Signator nicht hat und keinen Nullwert haben kann, dann die obige Interpretation des Parameters und macht keinen Sinn.

Abschätzung der Dichtheit der Kommunikation zwischen den Zeichen Es wird mit dem Linearpaar-Korrelationskoeffizienten - R x, Y durchgeführt. Es kann von der Formel berechnet werden: . Darüber hinaus kann der Koeffizient der linearen Paarungskorrelation durch den Regressionskoeffizienten B bestimmt werden: .
Der Bereich zulässiger Werte des linearen Koeffizienten der Paarkorrelation von -1 bis +1. Das Korrelationskoeffizientenzeichen weist auf die Richtung der Kommunikation hin. Wenn R x, y\u003e 0, dann ist die Verbindung gerade; Wenn R x, y<0, то связь обратная.
Wenn dieser Koeffizient nahe an einem liegt, kann die Verbindung zwischen den Merkmalen als recht schließen linear interpretiert werden. Wenn sein Modul gleich einer Einheit Ê R x, y ê \u003d 1 ist, ist die Verbindung zwischen den Schildern funktionell linear. Wenn die Zeichen x und y linear unabhängig sind, dann ist R x, y nahe bei 0.
Um R x zu berechnen, kann Y auch Tabelle 1 verwenden.

Um die Qualität der erhaltenen Regressionsgleichung zu beurteilen, wird der theoretische Bestimmungskoeffizient berechnet - R 2 YX:

,
wobei D 2 die Dispersion von y ist; von der Regressionsgleichung erläutert;
e 2 - Rest (unerklärliche Regressionsgleichung) Dispersion Y;
s 2 y ist eine totale (komplette) Dispersion Y.
Der Bestimmungskoeffizient kennzeichnet den Anteil der Variation (Dispersion) des effektiven Vorzeichens y, der durch die Regression (und folglich den Faktor X) in der allgemeinen Variation (Dispersion) y erläutert wird. Der Bestimmungskoeffizient R 2 yX nimmt Werte von 0 bis 1 an. Dementsprechend kennzeichnet der Wert von 1-R 2 yX den Fraktion der Dispersion Y, die durch den Einfluss anderer nicht berechtigter Faktoren in das Modell und den Spezifikationsfehlern verursacht wird.
Mit gepaarter linearer Regression R 2 yx \u003d R 2 yX.

Welches ist die breitere Anwendung in verschiedenen Bereichen der Wissenschaft und in der praktischen Tätigkeit. Es kann Physik, Chemie, Biologie, Wirtschaft, Soziologie, Psychologie und so weiter sein, so weiter. Der Wille des Schicksals muss oft mit der Wirtschaft umgehen, und deshalb werde ich Ihnen heute einen Junior in einem erstaunlichen Land ausführen Ökonometrisch \u003d) ... Wie willst du es nicht?! Es gibt sehr gut - Sie müssen nur entscheiden! ... aber hier ist die Tatsache, dass Sie wahrscheinlich definitiv wollen - es ist zu lernen, die Aufgaben zu lösen methode der kleinsten Quadrate. Und vor allem fleißige Leser lernen, sie nicht nur unverkennbar zu lösen, sondern auch sehr schnell ;-) aber zuerst allgemeine Einstellung der Aufgabe + Verwandtes Beispiel:

Angenommen, in einem bestimmten Themenbereich werden die Indikatoren, die einen quantitativen Ausdruck aufweisen, untersucht. In diesem Fall gibt es jedem Grund, zu glauben, dass der Indikator vom Indikator abhängt. Diese Hilfe kann sowohl eine wissenschaftliche Hypothese als auch sowohl eine wissenschaftliche Hypothese als auch auf einem elementaren gesunden Menschenverstand sein. Lassen Sie jedoch die Wissenschaft beiseite und erkunden Sie weitere appethizierende Bereiche - nämlich Lebensmittelgeschäfte. Bezeichnen von:

- Einkaufsviertel des Lebensmittelgeschäfts, qm,
- Jahresumsatz des Lebensmittelgeschäfts, Millionen Rubel.

Es ist klar, dass das größere der Bereich des Ladens, desto mehr in den meisten Fällen gibt es mehr den Umsatz.

Angenommen, dass nach der Durchführung von Beobachtungen / Experimenten / Zählungen / Tänze mit einem Tamburin in unserer Verfügung numerische Daten sind:

Mit Gästen glaube ich, dass alles klar ist: - Dies ist der Bereich des 1. Ladens, - sein Jahresumsatz, - die Gegend des 2. Ladens, - der Jahresumsatz usw. Übrigens ist es nicht notwendig, den Zugang zu geheimen Materialien überhaupt zu haben - eine ziemlich genaue Schätzung des Umsatzes kann mittels eingeholt werden mathematische Statistiken.. Wir sind jedoch nicht abgelenkt, der Kurs der kommerziellen Spionage ist bereits bezahlt \u003d)

Tabar-Daten können auch in Form von Punkten verfasst und in das übliche für uns beschrieben werden. kartesisches System. .

Antwort auf eine wichtige Frage: wie viele Punkte sind für hochwertige Forschung erforderlich?

Je mehr desto besser. Das minimal zulässige Set besteht aus 5-6 Punkten. Darüber hinaus können die "abnormalen" Ergebnisse mit einer kleinen Datenmenge nicht in die Probe einbezogen werden. So kann zum Beispiel ein kleiner Elite-Speicher mehr "ihre Kollegen" helfen, um das Gesamtmuster zu verzerren, das zum Finden erforderlich ist!

Wenn Sie einfach nur eine Funktion auswählen müssen, zeitplan das geht so nahe an Punkte . Diese Funktion wird aufgerufen angenähert (Annäherung - Annäherung) oder theoretische Funktion. . Im Allgemeinen erscheint hier sofort ein offensichtlicher "Antragsteller" - ein hoher Grad, dessen Zeitplan alle Punkte durchläuft. Diese Option ist jedoch kompliziert und oft falsch (weil der Zeitplan die ganze Zeit "Loop" sein wird und den Haupttrend schlecht widerspiegelt).

Somit sollte die gewünschte Funktion ziemlich einfach sein und gleichzeitig die Abhängigkeit angemessen widerspiegeln. Wie schätzen Sie, eine der Methoden, solche Funktionen zu finden und heißt methode der kleinsten Quadrate. Zuerst werden wir es im Allgemeinen analysieren. Lassen Sie einige Funktion die experimentellen Daten bringen:


Wie schätzt man die Genauigkeit dieser Annäherung? Berechnen und Unterschiede (Abweichungen) zwischen experimentellen und funktionalen Werten (Die Zeichnung lernen). Der erste Gedanke, der in den Sinn kommt, ist, zu bewerten, wie groß der Betrag ist, aber das Problem ist, dass die Unterschiede negativ sein können (z.B, ) Und Abweichungen infolge dieser Summation werden miteinander getrennt. Als Schätzung der Genauigkeit der Annäherung ist es daher geeignet, den Betrag zu akzeptieren module Abweichungen:

oder in der verdrehten Form: (Plötzlich weiß jemand nicht: - Dies ist das Summensymbol und die Hilfsvariable "Zähler", die Werte von 1 an ergreift).

Annäherung an experimentelle Punkte mit verschiedenen Funktionen erhalten wir verschiedene Werte und offensichtlich, wo dieser Betrag kleiner ist als die Funktion und genauer.

Diese Methode existiert und wird es genannt methode von mindestens Modulen. In der Praxis erhielt er jedoch viel mehr Vertrieb am wenigsten quadratische Methode.in denen mögliche negative Werte nicht vom Modul beseitigt werden, sondern der Bau von Abweichungen im Quadrat:

, danach werden die Bemühungen an die Auswahl einer solchen Funktion gerichtet, so dass die Summe der Abweichungen der Quadrate Es war so wenig wie möglich. Eigentlich der Name der Methode.

Und jetzt kehren wir zu einem anderen wichtigen Punkt zurück: Wie oben erwähnt, sollte die ausgewählte Funktion ziemlich einfach sein - aber es gibt auch viele solche Funktionen: linear , hyperbolisch, exponentiell, logarithmisch, quadratisch usw. Und natürlich würde es sofort gerne "das Tätigkeitsbereich reduzieren". Welche Art von Funktionen zur Forschung wählen? Primitive, aber effektiver Empfang:

- Die am einfachsten zu porträtierenden Punkte In der Zeichnung und analysieren ihre Position. Wenn sie dazu neigen, in einer geraden Linie platziert zu werden, sollten Sie suchen gleichung direkt mit optimalen Werten und. Mit anderen Worten, die Herausforderung besteht darin, solche Koeffizienten zu finden - so dass die Summe der Abweichungsquadienten der kleinste war.

Wenn sich die Punkte zum Beispiel befinden, hyperballEs ist nicht klar, dass die lineare Funktion eine schlechte Näherung ergibt. In diesem Fall suchen wir nach den meisten "profitablen" Koeffizienten für die Hyperbolegleichung - diejenigen, die die Mindestsumme von Quadraten geben .

Nun, dass wir in beiden Fällen wir sprechen funktionen von zwei Variablenderen Argumente ist parameter der gewünschten Abhängigkeiten:

Und im Wesentlichen müssen wir die Standardaufgabe lösen - um zu finden mindestfunktion von zwei Variablen.

Erinnern Sie sich an unser Beispiel: Angenommen, der "Store" -Punkte neigen dazu, sich in einer geraden Linie zu befinden, und es gibt jeden Grund, das anzunehmen lineare Abhängigkeit. Rohstoffumsatz vom Einkaufsviertel. Wir werden solche Koeffizienten "A" und "Sein" zur Summe der Abweichungsquadienten finden Es war das kleinste. Alles ist wie üblich - zuerst private Derivate der 1. Ordnung. Gemäß regel der Linearität Sie können direkt unter dem Betragsymbol unterscheiden:

Wenn Sie diese Informationen für einen Aufsatz oder Kursen verwenden möchten, ist ich für den Link in der Liste der Quellen sehr dankbar, dass solche detaillierten Berechnungen ein wenig finden, wo:

Lassen Sie uns ein Standardsystem machen:

Wir reduzieren jede Gleichung auf der "Deuce" und zusätzlich "Collapse" -Betrags:

Hinweis : Analysieren Sie unabhängig, warum "A" und "SE" aus dem Summensymbol herausgenommen werden kann. Übrigens kann es mit dem Betrag formal erfolgen

Schreiben Sie das System im Formular "Angewandten" neu aus:

Danach wird der Algorithmus der Lösung unserer Aufgabe gestartet:

Koordinaten von Punkten kennen wir? Wir wissen. Menge Können wir finden? Leicht. Machen Sie einfacher System von zwei linearen Gleichungen mit zwei unbekannten("A" und "Sein"). System löst zum Beispiel, cramer-MethodeInfolgedessen erhalten wir einen stationären Punkt. Überprüfung ein ausreichender Zustand von ExtremumSie können sicherstellen, dass an diesem Punkt die Funktion Erreicht genau. minimum. Die Prüfung ist mit zusätzlichen Berechnungen verbunden und lassen Sie es daher für die Szenen (Falls erforderlich, kann der fehlende Rahmen angezeigt werden). Wir machen die endgültige Schlussfolgerung:

Funktion die beste Art (zumindest im Vergleich zu einer anderen linearen Funktion) Bindet experimentelle Punkte . In grobem Gespräch geht ihr Zeitplan so nahe wie möglich an diesen Punkten. In der Tradition. Ökonometrie. Die resultierende Näherungsfunktion wird ebenfalls aufgerufen gleichung der gepaarten linearen Regression .

Das in Betracht gezogene Problem hat einen tollen praktischen Wert. In einer Situation mit unserem Beispiel der Gleichung Ermöglicht das Vorhersagen, was Handelsumsatz ist ("Igarek") wird im Laden sein, mit einem anderen Wert des Handelsbereichs (Tom oder andere Bedeutung "x"). Ja, die resultierende Prognose ist nur eine Prognose, aber in vielen Fällen ist es ziemlich genau.

Ich werde nur eine Aufgabe mit "echten" Zahlen herausfinden, da es keine Schwierigkeiten gibt - alle Berechnungen auf der Ebene des Schulprogramms 7-8-Klasse. In 95 Prozent der Fälle werden Sie eingeladen, eine lineare Funktion zu finden, aber am Ende des Artikels werde ich zeigen, dass es nicht schwieriger ist, die Gleichungen optimaler Hyperocher, Aussteller und andere Funktionen zu finden.

In der Tat bleibt es, die versprochenen Brötchen zu verteilen - so dass Sie gelernt haben, solche Beispiele nicht nur genau, sondern auch schnell zu lösen. Erlernen Sie sorgfältig den Standard:

Eine Aufgabe

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

Die kleinere Quadrate-Methode findet eine lineare Funktion, die am besten empirisch bringt (erfahren) Daten. Machen Sie eine Zeichnung, auf der sich im kartesischen rechteckigen Koordinatensystem experimentellen Punkte und Graphen der Annäherung anbaut . Finden Sie die Summe der Felder von Abweichungen zwischen empirischen und theoretischen Werten. Finden Sie heraus, ob die Funktion besser ist (aus Sicht der Methode der kleinsten Quadrate) Wenden Sie die experimentellen Punkte an.

Beachten Sie, dass "ICS" -Werte natürlich sind, und es hat eine charakteristische sinnvolle Bedeutung, die ich ein wenig später erzählen werde; Sie können jedoch natürlich fraktioniert sein. Je nach Inhalt einer Aufgabe als "ICX", und die "ignorierbaren" Werte können die "ignorierbaren" Werte vollständig oder teilweise negativ sein. Nun, wir haben eine "fachlose" Aufgabe, und wir beginnen damit entscheidung:

Die optimalen Funktionskoeffizienten finden als Lösung des Systems:

Um eine kompaktere Aufnahme zu erstellen, kann die Variable "Zähler" weggelassen werden, da klar ist, dass die Summation von 1 bis.

Die Berechnung der erforderlichen Beträge ist bequemer, um in tabellarischer Form anzuordnen:


Berechnungen können an der Mikrokalkulatorie durchgeführt werden, aber es ist viel besser, Excel - und schneller und ohne Fehler zu verwenden; Wir sehen ein kurzes Video an:

So erhalten wir folgende system:

Hier können Sie die zweite Gleichung für 3 und von der 1. Gleichung bis zum Subtrahieren des 2.. Aber dieses Glück - in der Praxis ist das System öfter nicht begabt, und in solchen Fällen spart cramer-Methode:
Das System hat also eine einzige Lösung.

Einen Scheck durchführen. Ich verstehe, dass ich nicht will, aber warum die Fehler vermissen, wo sie nicht absolut vermisst werden können? Ersetzen Sie die Lösung des linken Teils jeder Systemgleichung:

Gleiche Teile der jeweiligen Gleichungen werden erhalten, es bedeutet, dass das System korrekt gelöst wird.

Somit die gewünschte Näherungsfunktion: - von alle linearen Funktionen Experimentelle Daten nähert sich den besten.

Im Gegensatz zu gerade Abhängigkeit des Geschäftsumsatzes von seinem Platz, der gefundene Abhängigkeit ist inverse. (das Prinzip von "desto mehr - der weniger")und diese Tatsache wird sofort von negativ erkannt winkelkoeffizient. Funktion sagt uns, dass mit einer Erhöhung eines bestimmten Indikators auf 1 Einheit der Wert des abhängigen Indikators abnimmt im mittleren0,65 Einheiten. Wie sie sagen, desto höher ist der Preis von Buchweizen, desto weniger wird es verkauft.

Um einen Diagramm einer angenähernde Funktion aufzubauen, finden wir zwei seiner Werte:

und zeichnen Sie eine Zeichnung:


Gebaute Linie aufgerufen trendlinie (nämlich - Linie des linearen Trends, d. H. Im Allgemeinen Fall ist der Trend nicht unbedingt eine gerade Linie). Alle bekannten Ausdrucks "sei in Trend", und ich denke, dass dieser Begriff keine zusätzlichen Kommentare braucht.

Berechnen Sie die Summe der Felder von Abweichungen zwischen empirischen und theoretischen Werten. Geometrisch - Dies ist die Summe der Quadrate der Länge der "Himbeer" -Segmente (von denen zwei so klein sind, dass sie nicht einmal sichtbar sind).

Berechnungen lassen uns in der Tabelle:


Sie können wieder manuell erfolgen, nur für den Fall, dass ich ein Beispiel für den ersten Punkt mitbringen werde:

Aber viel effizienter, um eine bekannte Weise zu erfüllen:

Wiederholen Sie einmal noch einmal: was bedeutet das Ergebnis? Von alle linearen Funktionen Funktion Der Indikator ist der kleinste, das heißt in seiner Familie die beste Annäherung. Und hier, übrigens ist die endgültige Frage des Problems nicht zufällig: Was ist, wenn die vorgeschlagene exponentielle Funktion? Wird es besser sein, die experimentellen Punkte zu bringen?

Wir finden den angemessenen Betrag der Felder von Abweichungen - um zu unterscheiden, ich werde ihren Brief "Epsilon" angeben. Die Technik ist genau das Gleiche:


Und wieder zu jeder Brandberechnung für den 1. Punkt:

In Excel verwenden wir Standard-Funktion EXP. (Die Syntax kann in Exele-Hilfe angezeigt werden).

Ausgabe: Die exponentielle Funktion bringt daher die experimentellen Punkte schlimmer als direkt .

Es sei aber darauf hingewiesen, dass "schlechter" ist heißt nicht, Was ist falsch. Wird jetzt ein Diagramm dieser exponentiellen Funktion gebaut - und er geht auch in der Nähe von Punkten - Ja, so dass ohne eine analytische Studie und es schwer zu sagen ist, was eine Funktion genauer ist.

Bei dieser Entscheidung ist abgeschlossen, und ich kehre in die Frage der natürlichen Werte des Arguments zurück. In verschiedenen Studien, in der Regel, wirtschaftliche oder soziologische, natürliche "ICES" numerische Monate, Jahre oder andere gleiche Zeitintervalle. Betrachten Sie zum Beispiel eine solche Aufgabe.

4.1. Mit eingebauten Funktionen verwenden

Berechnung rezessionskoeffizienten mit der Funktion durchgeführt

Linol(Werte_Y.; Werte_x.; Angelegenheit; statistiken),

Werte_Y. - Array von y-Werten

Werte_x.- Optionale Werteartigkeit x.Wenn ein Array h. weggelassen, wird angenommen, dass dieses Array (1; 2; 3; ...) der gleichen Größe wie Werte_Y.,

Angelegenheit- ein logischer Wert, der angibt, ob er konstant erforderlich ist b. war gleich 0. Wenn Angelegenheit hat die Bedeutung. WAHR oder dann weggelassen b. Es wird auf übliche Weise berechnet. Wenn das Argument Angelegenheit Es ist dann eine Lüge, dann b. verlässt sich gleich 0 und Werte eIN. ausgewählt, so dass das Verhältnis y \u003d Axt.

Statistiken- ein logischer Wert, der angibt, ob zusätzliche Regressionsstatistiken zurückgegeben werden sollen. Wenn das Argument Statistiken hat die Bedeutung. WAHR, dann Funktion. Linol Gibt zusätzliche Regressionsstatistik zurück. Wenn das Argument Statistiken hat die Bedeutung. FALSCH oder weggelassen, dann die Funktion Linol Gibt nur den Koeffizienten zurück eIN. und konstant b..

Es muss daran erinnert werden, dass das Ergebnis der Funktionen Linene ()ist viele Werte - ein Array.

Zur Berechnung korrelationskoeffizient Verwendete Funktion

Kornel(Massiv1.;Massiv2.),

rückgabewerte des Korrelationskoeffizienten wo Massiv1. - Anordnung der Werte y., Massiv2. - Anordnung der Werte x.. Massiv1. und Massiv2. Muss eine Dimension sein.

Beispiel 1.. Sucht y.(x.) In der Tabelle präsentiert. Bauen regressionslinie und berechnen korrelationskoeffizient..

Wir führen die Wertetabelle in das MS Excel-Blatt ein und bauen einen Punktplan auf. Die Arbeitsliste ergreift die in Fig. 1 dargestellte Ansicht. 2

Um die Werte der Regressionskoeffizienten zu berechnen aberund b.highlights A7: B7, Wenden Sie sich an den Master der Funktionen und in der Kategorie Statistisch Wählen Sie ein Feature aus Linol. Füllen Sie das aufgetretene Dialogfeld, wie in FIG. 3 und klick OK..

Infolgedessen erscheint der berechnete Wert nur in der Zelle A6. (Abb.4). Damit der Wert in der Zelle erscheinen kann B6. Sie müssen den Bearbeitungsmodus eingeben (Schlüssel F2)und drücken Sie dann die Tastenkombination STRG + SHIFT + ENTER.

Um den Wert des Korrelationskoeffizienten in der Zelle zu berechnen C6. Die folgende Formel wurde eingeführt:

C7 \u003d Corvela (B3: J3; B2: J2).

Die Regressionskoeffizienten kennen aberund b. Berechnen Sie die Werte der Funktion y.=aXT.+b. Für angegeben. x.. Dazu führen wir die Formel vor

B5 \u003d $ A $ 7 * B2 + $ B $ 7

und kopiere es in den Bereich C5: J5.(Abb. 5).

Ich werde die Regressionslinie im Diagramm gezeigt haben. Markieren Sie die experimentellen Punkte in der Grafik, klicken Sie mit der rechten Maustaste und wählen Sie den Befehl aus Ausgangsdaten. Im Dialogfeld, das angezeigt wird (Abb. 5), wählen Sie die Registerkarte aus Reihe und klicken Sie auf die Schaltfläche Hinzufügen. Füllen Sie die Eingabefelder aus, wie in Fig. 2 gezeigt. 6 und drücken Sie die Taste OK. Regressionszeile wird dem experimentellen Datenplan hinzugefügt. Standardmäßig wird der Zeitplan in Form von Punkten dargestellt, die nicht durch Glättungslinien angeschlossen sind.

Feige. 6.

Um die Art der Regressionszeile zu ändern, führen Sie die folgenden Schritte aus, um die folgenden Schritte auszuführen. Klicken Sie mit der rechten Maustaste auf die Punkte, die den Zeilenplan darstellen, wählen Sie den Befehl aus Art des Diagrammsund stellen Sie die Art des Punktdiagramms ein, wie in Fig. 2 gezeigt. 7.

Linientyp, seine Farbe und die Dicke können wie folgt geändert werden. Wählen Sie die Zeile im Diagramm aus, klicken Sie auf die rechte Maustaste und wählen Sie den Befehl im Kontextmenü aus. Format der Datenserie ... Nehmen Sie zum Beispiel Installationen, beispielsweise wie in FIG. acht.

Als Ergebnis aller Transformationen erhalten wir ein Diagramm der experimentellen Daten- und Regressionslinie in einem Grafikbereich (Abb. 9).

4.2. Mit einer Trendlinie.

Der Bau verschiedener Annäherungsabhängigkeiten in MS Excel wird als Diagrammeigenschaften implementiert - trendlinie.

Beispiel 2.. Infolge des Experiments wurde eine tabellarische Abhängigkeit bestimmt.

Wählen Sie die Annäherung an die Annäherung an. Erstellen Sie die grafische tabellarische und ausgewählte analytische Abhängigkeit.

Die Problemlösung kann in die folgenden Schritte unterteilt werden: Eingabe der Quelldaten, auf den Bau eines Punktzeitpunkts und Hinzufügen einer Trendlinie zu dieser Grafik.

Betrachten Sie diesen Prozess im Detail. Wir führen die Quelldaten in das Arbeitsblatt ein und konstruieren ein Diagramm von experimentellen Daten. Wählen Sie anschließend die experimentellen Punkte in der Grafik aus, klicken Sie mit der rechten Maustaste und verwenden Sie den Befehl Hinzufügenl. trend und Trend (Abb. 10).

Mit dem angezeigten Dialogfeld können Sie eine angenähertende Abhängigkeit aufbauen.

Auf der ersten Registerkarte (Abb. 11) dieses Fensters ist die Art der angenähernden Abhängigkeit angegeben.

Auf der zweiten (Abb. 12) definiert die Parameter der Konstruktion:

· Der Name der angenähernden Abhängigkeit;

· Vorhersage (Rücken) auf n. Einheiten (Dieser Parameter bestimmt, für welche Anzahl der vorstehenden Einheiten (zurück) die Trendlinie erweitern muss);

· Zeigen Sie, ob der Kreuzungspunkt der Kurve mit einer geraden Linie ist y \u003d const.;

· Zeigen Sie eine Näherungsfunktion in einem Diagramm oder nicht (Parameter, um die Gleichung des Diagramms anzuzeigen);

· Hat das Diagramm der Standardabweichung oder nicht (der Parameter auf dem Diagramm den Wert der Genauigkeit der Annäherung an).

Wir wählen ein Polynom mit einem zweiten Grad als angenähernde Abhängigkeit (Abb. 11) und entfernen Sie die Gleichung, die dieses Polynom auf dem Diagramm beschreibt (Abb. 12). Das resultierende Diagramm ist in Fig. 4 dargestellt. 13

Ähnlich mit Hilfe trendlinien Sie können die Parameter solcher Abhängigkeiten wählen

· Linear y.=ein ∙ x.+b.,

· Logarithmisch. y.=ein ∙ ln.(x.)+b.,

· Exponential y.=ein ∙ e b,

· Leistung y.=ein ∙ x b,

· Polynom y.=ein ∙ x. 2 +b ∙ X.+c., y.=ein ∙ x. 3 +b ∙ X. 2 +c ∙ x + d Und so weiter, bis zum 6. Grad Polynom inklusive,

· Lineare Filterung.

4.3. Mit einem entscheidenden Bloc

Wesentliches Interesse ist die Implementierung von Parametern in MS Excel mit den kleineren Quadraten mit dem entscheidenden Block. Mit dieser Technik können Sie die Parameter der Funktion jeglicher Art auswählen. Betrachten Sie diese Funktion im Beispiel der folgenden Aufgabe.

Beispiel 3.. Infolge des Experiments, der in der Tabelle dargestellt, präsentiert die Abhängigkeit Z (t)

Nehmen Sie die Suchtkoeffizienten ab Z (t) \u003d bei 4 + Bt 3 + CT 2 + DT + K Die Methode der kleinsten Quadrate.

Diese Aufgabe entspricht der Aufgabe, eine Mindestfunktion von fünf Variablen zu finden.

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

Bedeutungen lassen. ABER, IM, VON, D. und ZU In Zellen gelagert A7: E7.. Berechnen Sie theoretische Werte der Funktion Z.(t.)=Bei 4 + bt 3 + ct 2 + dt + k Für angegeben. t.(B2: J2.). Um dies in der Zelle zu tun B4. Wir geben den Wert der Funktion in den ersten Punkt ein (Zelle B2.):

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

Kopieren Sie diese Formel in den Bereich C4: J4. Und wir erhalten den erwarteten Wert der Funktion an Punkten, deren Abszwürde in den Zellen gespeichert sind B2: J2..

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

B5 \u003d (b4-b3) ^ 2,

und kopiere es in den Bereich C5: J5.. In der Zelle F7. Wir speichern einen totalen quadratischen Fehler (10). Dazu führen wir die Formel ein:

F7 \u003d Beträge (B5: J5).

Wir verwenden das Team SERVICE®POISK-Lösungen. und lösen Sie das Problem der Optimierung ohne Einschränkungen. Füllen Sie die entsprechenden Eingabefelder in dem in Fig. 1 gezeigten Dialogfeld aus. 14 und drücken Sie die Taste Ausführen. Wenn die Lösung gefunden wird, wird das in FIG. fünfzehn.

Das Ergebnis des entscheidenden Blocks ist die Schlussfolgerung in der Zelle A7: E7.parameterwerte Funktionen Z.(t.)=Bei 4 + bt 3 + ct 2 + dt + k. In Zellen B4: J4. Erhalten erwartete Wertfunktion. An den Ausgangspunkten. In der Zelle F7. wird gespeichert totaler quadratischer Fehler.

Pictulieren Sie die experimentellen Punkte und die ausgewählte Zeile in einem Grafikbereich kann sein, wenn der Bereich zugewiesen wird B2: J4.Anruf Master-ChartUnd formatieren Sie dann das Erscheinungsbild der erhaltenen Grafiken.

Feige. 17 Zeigt die MS Excel-Arbeitsliste nach den ausgeführten Berechnungen an.

5. Liste der referenzen

1. Alekseev E. R., Chesnokova o.v., Lösung von Problemen der Berechnung der Mathematik in Mathcad12-Paketen, Matlab7, Maple9. - NT Press, 2006.-596C. : Il. - (Lernprogramm)

2. Alekseev E.R., Chesnokova o.v., E.A. Rudchenko, Scilab, Solving Engineering und mathematische Aufgaben. -M., Binom, 2008.-260s.

3. Berezin I.S., Lamine N.P., Berechnungsmethoden. - M.: Wissenschaft, 1966.-632C.

4. GARNAV A.YU. Verwenden Sie MS Excel und VBA in Wirtschaft und Finanzen. - SPB.: BHV - Petersburg, 1999.-332C.

5. Demidovich B.P., Maron und A., Shuvalova v.z., numerische Methoden zur Analyse. - M.: Wissenschaft, 1967.-368С.

6. Mais G., Korn T., Mathematikverzeichnis für Wissenschaftler und Ingenieure. - M., 1970, 720s.

7. Alekseev E.R., Chesnokova o.v. Methodische Anweisungen zur Durchführung von Laborarbeit in MS Excel. Für Studenten aller Spezialitäten. Donetsk, Donntu, 2004. 112 p.