Polynomische Regression in Excel ist fehlerhaft

Christian_b26585 · 11. November 2019 um 09:03

Hallo,

ich habe (mal wieder) ein Problem mit Excel (2007). Ich möchte aus ein paar Messwerten einen Graphen plotten und eine Regressionslinie durch die Punkte legen. Mit der so entstandenen Regressionsfunktion möchte ich Werte zwischen den Messwerten interpolieren.

Das funktioniert auch alles wunderbar bis auf die Tatsache, dass Excel selbst für die Messwerte, aus denen die Funktion erstellt wurde, eindeutig falsche Werte ausgibt.

Also, meine Messwerte sind:

1000 3750000
1500 5000000
2000 6200000
2500 8300000
3000 11800000

Ich füge dann ein Punkt(XY)-Diagramm ein und lege eine polynomische Regressionsfunktion 3. Grades darüber. Die Funktion liegt auch sehr schön über den Werten… trotzdem: Wenn ich die Funktion in eine Zelle kopiere und als variablen x-Wert zum Beispiel 3000 (also einen der Messwerte) wähle, gibt mir Excel einen deutlich zu hohen Wert aus, wie man unschwer am Diagramm ablesen kann.

Interessanterweise scheint das Problem zu verschwinden, wenn ich niedrigere Messwerte benutze, also z.B. meine X- und Y-Werte je durch 1000 teile.

Freue mich über alle Anregungen und Antworten

Gruß Chris

Fritz_Weinel_ce4a38 · 11. November 2019 um 09:04

Hallo Chris,
Deine Regressionsfornel lautet:
y = 0,001x3 - 4,2571x2 + 8486,9x - 1E+06
Damit werden die Werte mit 11 bis 13% zu hoch berechnet.
Lösung:
Klicke die Formel im Diagramm mit der rechten Maustaste an
und gehe auf „Datenbeschriftung formatieren“.
Die Formel ist mit „Standard“ formatiert.
Ändere die Formatierung auf „Wissenschaft“ mit z.B 3 Stellen nach dem Komma.
Dann lautet die Formel
y = 9,667E-04x3 - 4,257E+00x2 + 8,487E+03x - 1,440E+06
Mit diesen (genaueren) Werten betragen die Abweichungen nur noch maximal 0,6%

Gruß Fritz

ich habe (mal wieder) ein Problem mit Excel (2007). Ich möchte
aus ein paar Messwerten einen Graphen plotten und eine
Regressionslinie durch die Punkte legen. Mit der so
entstandenen Regressionsfunktion möchte ich Werte zwischen den
Messwerten interpolieren.

Das funktioniert auch alles wunderbar bis auf die Tatsache,
dass Excel selbst für die Messwerte, aus denen die Funktion
erstellt wurde, eindeutig falsche Werte ausgibt.

Also, meine Messwerte sind:

1000 3750000
1500 5000000
2000 6200000
2500 8300000
3000 11800000

Ich füge dann ein Punkt(XY)-Diagramm ein und lege eine
polynomische Regressionsfunktion 3. Grades darüber. Die
Funktion liegt auch sehr schön über den Werten… trotzdem:
Wenn ich die Funktion in eine Zelle kopiere und als variablen
x-Wert zum Beispiel 3000 (also einen der Messwerte) wähle,
gibt mir Excel einen deutlich zu hohen Wert aus, wie man
unschwer am Diagramm ablesen kann.

Interessanterweise scheint das Problem zu verschwinden, wenn
ich niedrigere Messwerte benutze, also z.B. meine X- und
Y-Werte je durch 1000 teile.

Freue mich über alle Anregungen und Antworten

Gruß Chris

Christian_b26585 · 11. November 2019 um 09:05

Vielen Dank Fritz… funktioniert wunderbar

Reinhard_fb8cb7 · 11. November 2019 um 09:05

Mit diesen (genaueren) Werten betragen die Abweichungen nur
noch maximal 0,6%

Hallo Fritz,

ich finde eine Abweichung von 0,6% als für zu groß.

Woher kommt die?
Von Formeln? Von Excel?

Übrigens habe ich davon mathematisch Null Ahnung. Mich interessiert es nur Excelmäßig.

Also erklärs mir bitte Mathearm

Gruß
Reinhard

Fritz_Weinel_ce4a38 · 11. November 2019 um 09:05

ich finde eine Abweichung von 0,6% als für zu groß.

Woher kommt die?
Von Formeln? Von Excel?

Gruß
Reinhard

Hallo Reinhard,
das Problem ist die Mathematik.
Wenn Du, wie in unserem Fall, 5 Punkte durch ein Polynom 3. Grades annähern willst, können nicht alle Punkte genau auf diesem Polynom liegen.
Dafür ist bei n = 5 Punkten mindestens ein Polynom 4. Grades (n-1) notwendig.
In unserem Beispiel wäre die Regressionsformel
y = -3E-07*x^4 + 3,36667E-03*x^3 - 1,1125E+01*x^2 + 1,67583E+04*x - 4,95E+06
Damit werden die Abweichungen kleiner als 0,001% (Rechengenauigkeit).
Gruß Fritz

Thomas_Ramel · 11. November 2019 um 09:05

Grüezi Christian

Also, meine Messwerte sind:

1000 3750000
1500 5000000
2000 6200000
2500 8300000
3000 11800000

Ich füge dann ein Punkt(XY)-Diagramm ein und lege eine
polynomische Regressionsfunktion 3. Grades darüber. Die
Funktion liegt auch sehr schön über den Werten… trotzdem:
Wenn ich die Funktion in eine Zelle kopiere und als variablen
x-Wert zum Beispiel 3000 (also einen der Messwerte) wähle,
gibt mir Excel einen deutlich zu hohen Wert aus, wie man
unschwer am Diagramm ablesen kann.

Interessanterweise scheint das Problem zu verschwinden, wenn
ich niedrigere Messwerte benutze, also z.B. meine X- und
Y-Werte je durch 1000 teile.

Wei Du bereits erfahren hast, liegt das an der Genauingkeit mit der Du die Koeffizienten in der Gleichung im Diagramm darstellen lässt. Ich wähle da in aller Regel 20 Stellen, denn da kommt meinstens gut hin - Das Bestimmtheitsmass (kannst Du in der Trenlininen-Definition ebenfalls ausgeben lassen) liegt bei 0.99, was auf eine sehr gute Korrelation der errechneten Werte schliessen lässt.

Stehen deine X/Y-Werte in den Zellen A2:B6, dann kannst Du die Koeffizienten und das Bestimmtheitsmass auch wie folgt direkt in Excel-Zellen ausgeben lassen:

Markiere E1:H6 und gib dann die folgende Formel ein (für ein Polynoim 3.Grades):

=RGP(B2:B6;A2:A6^{0\1\2\3};0;1)

Schliesse nun diese Eingabe mit STRG+Umschalt+Returen als Matrix-Formel ab.

IN B2:H2 findest Du nun die exakten Koeffizienten wieder und in E4 das Bestimmtheitsmass.

Die Bedeutung der weiteren Werte im Bereich findest Du in der Online-Hilfe zur Funktion RGP().

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Fritz_Weinel_ce4a38 · 11. November 2019 um 09:05

Frage
Grüezi Thomas,
was ich Dich schon immer mal fragen wollte:
Wofür steht eigentlich „MVP“ ??

Wikipedia:
Die Abkürzung MVP steht für:
* Mecklenburg-Vorpommern, inoffizielle Abkürzung für das deutsche Bundesland
* Memelländische Volkspartei, eine Partei im Memelland
* Effektive Populationsgröße (Minimum Viable Population), kleinste überlebensfähige Population in der Biologie
* Most Valuable Player, im Sport eine Auszeichnung für einen Spieler
* Microsoft MVP (Most Valuable Professional) eine Auszeichnung der Firma Microsoft
* Model View Presenter ein Architekturmuster ähnlich dem MVC in der Software-Entwicklung
* Minimum-Varianz-Portfolio, ein Begriff aus der Portfoliotheorie
* Montel Vontavious Porter, ein US-amerikanischer Wrestler
* Multi-View Process Modelling, eine Prozess-Modellierungssprache

Unter der Annahme, dass Du ein Microsoft MVP bist, wäre meine Frage, wie man zu einer solchen Auszeichnung kommt.

Mit neugierigem Gruß
Fritz

Thomas_Ramel · 11. November 2019 um 09:06

Grüezi Fritz

was ich Dich schon immer mal fragen wollte:
Wofür steht eigentlich „MVP“ ??

* Microsoft MVP (Most Valuable Professional) eine
Auszeichnung der Firma Microsoft

Hier liegst Du genau richtig

http://mvp.support.microsoft.com/
https://mvp.support.microsoft.com/profile=BEA41F2F-E…

Unter der Annahme, dass Du ein Microsoft MVP bist, wäre meine
Frage, wie man zu einer solchen Auszeichnung kommt.

Hmm, wie soll ich das sagen - etwas so wie die Jungfrau zum Kind…

Ich bin seit ca. 2000 in Micorosoft NewsGroups ‚unterwegs‘ und tue das, was ich auch hier bei W-W-W tue, ich beantworte Fragen von Anwendern insbesondere zu Excel.
Im Jahre 2002 wurde ich dann von Microsoft als MVP ernannt und darf diese Auszeidhnung seit dann führen.

…hilft dies, deine Neugier etwas zu stillen…?

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Fritz_Weinel_ce4a38 · 11. November 2019 um 09:06

Grüezi Thomas,
Danke für die Antwort,
meine Neugier ist gestillt.
Mit freundlichem Gruß
Fritz