Gewichtete Trendlinie
Excel erstellt Streudiagramm für Gruppen von Paarwerten. Es bietet auch die Möglichkeit, eine Trendlinie und Formel für die Trendlinie zu erstellen. Es erzeugt auch Blasendiagramme, die ein mit jedem Wert bereitgestelltes Gewicht berücksichtigen. Das Gewicht hat jedoch keinen Einfluss auf die Trendlinie oder Formel. Hier ist ein Beispielsatz von Werten mit ihren Zuordnungen und Gewichten.
%Vor%Bei der Excel-Trendlinie hat das Mapping für Wert 5 zu viel Einfluss auf die Formel. Gibt es eine Möglichkeit, eine Formel zu erstellen, die die jeweiligen Gewichte widerspiegelt?
Als Hilfe habe ich einen gewichteten Durchschnitt für fünf aufeinanderfolgende Werte eingeführt. Aber ist das ein besserer Weg?
2 Antworten
Mit Daten in A2: C7, basierend auf der Standard-Formel für die gewichtete kleinste Quadrate, können Sie versuchen:
%Vor%wird mit STRG + UMSCHALT + EINGABE in E2: F2 oder einem beliebigen 2x1-Bereich eingegeben. Dies liefert auch {1.1353, 1.4412}.
Für Rsquared können Sie Folgendes eingeben:
%Vor%Erklärung der Formel
Betrachten Sie zuerst eine normale Regression von y auf X mit RGP. Wenn const = TRUE, ist die Regressormatrix die erweiterte Matrix, die aus einer Spalte von Einsen besteht, gefolgt von den Regressorsäulen, d. H. X '= (1, X). Wenn const = FALSE, ist die Regressor-Matrix einfach X, so dass die Regression mit einer Spalte von Einsen die gleichen Schätzungen liefert wie ohne eine Spalte von Einsen zu laufen und const = TRUE zu setzen.
Betrachten Sie nun eine gewichtete Kleinste-Quadrate-Regression. Die Regression ist jetzt Wy auf WX '= (W1, WX) wobei W die diagonale Matrix ist, die aus der Quadratwurzel der Gewichte besteht. Da es keine Spalte von Einsen gibt, müssen wir const = FALSE setzen und zwei Spalten in der Regressormatrix verwenden.
Rsquared-Berechnung
Das Setzen von stats auf TRUE in der RGP-Ausgabe der ersten Formel erhalten wir in der dritten und fünften Zeile:
%Vor%Beachten Sie, dass diese Werte nicht zentrierte Versionen (u) sind, da const = FALSE (weitere Informationen finden Sie in der MS-Hilfe zu RGP). Für die zentrierten Versionen (c) müssen wir den gewichteten Durchschnitt wie folgt subtrahieren:
%Vor% Aktualisieren
Basierend auf der zusätzlichen Information, dass Sie Zehntausende von Zeilen haben, ist hier eine VBA-UDF, die die Aufgabe (einschließlich der R2)
Wie im Screenshot unten gezeigt, bietet es dieselben m , x und r2 Werte wie mein erweiterter Datensatz in meiner ursprünglichen Antwort
Anfangsantwort
Erweitern Sie einfach Ihre Datenreihe um Ihre Gewichtungsfaktoren
Anstatt also zu versuchen, 6 Paare zu zeichnen, verwenden Sie das Verhältnis von Ihrem höchsten zum niedrigsten, um die Punkte zu wiederholen
d. Grafik
%Vor% 
Tags und Links excel graph excel-formula curve-fitting least-squares