Wie kann ich eine absteigende Nummernreihe in Excel regressieren?

819
jcollum

Ich möchte diese Daten verwenden, um mit Excel eine Gleichung abzuleiten. 

300 13 310 12.6 320 12.2 330 11.8 340 11.4 350 11 360 10.8 370 10.6 380 10.4

Wenn x steigt, sinkt y. Scheint einfach. Wenn ich jedoch eine polynomiale Regression für diese Daten durchführe, funktioniert die von ihr erzeugte Gleichung nicht, obwohl die Trendlinie ziemlich genau mit den Daten übereinstimmt. Die Gleichung lautet 0.0096x2 - 0.4181x + 13.341Wenn ich x-Werte in diese Gleichung einstecke, steigen die Zahlen! Also stimmt hier etwas nicht.

Meine Schritte:

  • Platziere beide Nummernserien in Excel
  • wähle das zweite Set (13, 12.6 ...)
  • Plotten Sie ein Liniendiagramm
  • Legen Sie den ersten Satz als X-Achsenbeschriftung fest
  • Wählen Sie Series1 aus und fügen Sie eine Polynom (2) -Trendlinie hinzu, zeigen Sie die Gleichung an und zeigen Sie das R-Quadrat an

Daraus ergibt sich die obige Gleichung mit einem R ^ 2-Wert von 0,9955. Wenn ich diese Gleichung verwende, werden jedoch keine Ausgaben für diese Eingaben erzeugt.

Klar mache ich etwas falsch.

Edit: oder ist es Excel? Hier ist der Graph dieser Gleichung (oben):Gleichungsgraph

Offensichtlich tendiert das nicht für den Bereich 300-390 nach unten .

Hier ist die wirkliche Gleichung, die zu diesen Daten passt:

Quadratische Anpassung: y = a + bx + cx ^ 2 Koeffizientendaten:
a = 4,53E + 01 b = -1,66E-01 c = 1,95E-04

Vielen Dank CurveExpert 1.4.

3
Es ist tendenziell nach unten gerichtet, wenn Sie den Teil der gezeichneten Kurve betrachten, in dem x <0 ist. Wenn Sie alle Werte nach unten benötigen, versuchen Sie eine lineare Anpassung. Mark vor 13 Jahren 0
Wenn Sie die X-Werte in die Gleichungen eintragen und 3 signifikante Zahlen zeigen, unterscheiden sich die Y-Werte für meine Lösung und CurveExpert nur für x = 320 und meine sind näher. Daher müssen wir signifikantere Zahlen verwenden und sehen, welche Fehler in den Lösungen enthalten sind. Ich habe die vollständige Fehleranalyse vergessen Mark vor 13 Jahren 0
@ Mark: Haben Sie sich die Grafiken angesehen, die ich gepostet habe? Diese sind niemals nach unten gerichtet. Die Ableitung von x ^ 2 + c ist immer positiv oder Null. jcollum vor 13 Jahren 0
Die Ableitung von x ^ 2 + c ist 2x und für x <0 negativ Mark vor 13 Jahren 0
und ist auf der linken Seite nach unten - die Kurvenanpassung zeigt nur einen kleinen Teil der Cuve Mark vor 13 Jahren 0
Ack, du hast recht. Trotzdem steigt die von Excel erzeugte Gleichung immer über den Wertebereich (300-390), den ich in die Kurvenanpassung eingegeben habe. Also macht es etwas falsch. jcollum vor 13 Jahren 0
Ich habe versucht, dasselbe in Excel zu tun. Wenn Sie die quadratische Anpassung nur für die Y-Werte berechnen, erhalten Sie genau Ihre Anpassung. Es scheint also, dass der X-Bereich bei der Berechnung der Anpassung selbst ignoriert wird Ivo Flipse vor 13 Jahren 0

2 Antworten auf die Frage

2
Mark

Ich gab diese ein und bekam das Ergebnis als

y = 0,000186x ^ 2 - 0,160247x + 44,385628 mit R ^ 2 = 0,995

also eine gute passform

Ich habe x und y als Spaltenüberschriften eingegeben.
Dann war die erste Spalte die x-Werte und die zweite Spalte das y

Wählen Sie beide Spalten aus

Diagramm einfügen - Geigenachsen

Trendlinie hinzufügen.

Hochrechnung für den Bereich 0: 500Vergrößerte den Bereich aus den Daten

Ähh? Ich habe einfach getan, was Sie gesagt haben, und eine andere Gleichung erhalten. Der Punkt ist jedoch: x ^ 2 + x + c ist kein absteigender Graph. Siehe hier: "http://www.wolframalpha.com/input/?i=x^2+%2B+x&a=*MC.~-_*Graph-" jcollum vor 13 Jahren 1
OK, Wolfram-Links werden vermasselt. Sie müssen es kopieren und einfügen. jcollum vor 13 Jahren 0
@wilson: ahh, was ist der Trick dort? [] jcollum vor 13 Jahren 0
behoben: [wolframalpha.com/input/?i=x^2+%2B+x&a=*MC.~-_*Graph-((ttp://wolframalpha.com/input/?i=x%5E2+%2B + x & a = * MC. ~ -_ * Graph-) wilson vor 13 Jahren 0
@jcollum, `[Linkbeschreibung] (http: // ...)`, und ersetzen Sie `^` durch `% 5E` wilson vor 13 Jahren 0
Ich habe ein Polyfit dritter Ordnung verwendet, berechnet in Python, und ich kann @ Marks Ergebnisse vollständig sichern. Ich werde sie zu seiner Antwort als Beweis hinzufügen. Abgerundet wurde die Formel: `polyfit ==> 0,00019 * x ** 2 + -0.165 * x + 45,25` Ivo Flipse vor 13 Jahren 0
Beachten Sie auch die mittlere Grafik, in der die angepasste Gleichung als Parabel dargestellt wird Mark vor 13 Jahren 0
2
KronoS

Ich weiß nicht, wie Sie diese Gleichung in Excel erhalten haben, da dies die folgende Gleichung ist:

enter image description here

Stellen Sie sicher, dass der Polynomgrad 2 ist und Sie keine Abschnitte oder Vorhersagen in die Trendlinienerstellung eingegeben haben.

Eine andere Sache, sagten Sie, x steigt und y nimmt ab. Das bedeutet, dass Sie eine exponentielle Kurvenanpassung vornehmen sollten (es sei denn, es handelt sich um eine lineare Abnahme, die es nicht ist):

enter image description here

Es ist wichtig, dass die Kurvenanpassung wirklich nur in den Arbeiten beachten Grenzen von Daten, die Sie eingegeben haben. Sie können damit die zukünftigen Werte nicht genau vorhersagen. Sie können Werte nur zwischen den Grenzen Ihrer ursprünglichen Daten schätzen.

Der Grund, warum ich die Verwendung eines Exponentials vorschlage, liegt in einem Verständnis des "Trends" der Daten und der Berechnung dieser "Trendgleichungen" durch Computer. Angenommen, ich habe 3 Datenpunkte und erstellt eine Polynomfunktion, die perfekt zu den Daten passt:

enter image description here

Wenn ich jedoch mehr Datenpunkte nehme, liegen diese außerhalb der ursprünglichen Datenpunkte. (Ja, ich verstehe, dass Excel NIE eine Funktion wie diese machen würde, aber es ist ein Punkt zu betonen.) Bei der Analyse von Daten muss man eine Entscheidung treffen, die auf dem basiert, was sie wissen.

Auch wenn mein R-Wert niedriger ist als Ihr (um lediglich 0,01), ist die Kenntnis der Tatsache, dass die Daten mit zunehmender x-Zunahme abnehmen, die Exponentialfunktion zu einer besseren Wahl, weil Sie BEREITS WISSEN. Eine lineare Anpassung wäre in der obigen Grafik die bessere Wahl. Dies ist der Hauptunterschied zwischen Extrapolation und Interpolation .

Sie haben den Teil meines Beitrags verpasst, an dem ich die von CurveExpert erzeugte Kurvengleichung eingefügt habe. Diese Gleichung basierte auf x ^ 2 und passte zu den Daten. Außerdem hatte meine ursprüngliche Gleichung aus Excel einen besseren R ^ 2-Wert als die von Ihnen erzeugte Gleichung. Sie haben einen korrekten Bereich, aber ich sehe die Relevanz nicht. jcollum vor 13 Jahren 0
bearbeitete Antwort in Antwort @jcollum KronoS vor 13 Jahren 0
OK. Aber es kommt immer noch nicht auf die Frage an, warum Excel a) sagt, dass es eine Gleichung mit einem hohen R-Wert hat, die in keiner Weise zu den Daten passt. Die von CurveExpert generierte Gleichung generiert die Daten in diesem Bereich, die von Excel generierte Gleichung jedoch nicht. jcollum vor 13 Jahren 0
Daten nehmen ab, da x Zunahme nicht exponentiell bedeutet - die Wahl des Polynoms oder Exponentials ist das Modell, das den Daten zugrunde liegt und nicht unbedingt nur aus den Daten ausgewählt wird. Ich kann Parabeln konstruieren, bei denen mit zunehmendem x das y abnimmt - zB y = x ^ 2 oder y = -bx + c Mark vor 13 Jahren 0
@Mark richtig, aber im Allgemeinen und IMO ist Exponential besser für die Situation geeignet. KronoS vor 13 Jahren 0
@Kronos - sicher wissen wir nicht genug, um zu sagen, ob Exponential besser ist - ich würde linear ohne weitere Informationen annehmen - Excel gibt y = -0.0337x + 22.991 mit R ^ 2 = 0,9796, was eine gute Passform ist Mark vor 13 Jahren 0
Linear ist nicht korrekt, da sich die Steigung von 0,4 auf 0,2 ändert. Sowohl Polynom als auch Exponential passen am besten, aber IMO, Exponential ist in dieser Situation das Beste. KronoS vor 13 Jahren 0

Verwandte Probleme