Polynom-Trendlinie Funktion für Vorfaktoren

[TheLuzl]

Guten Morgen,

ich bin momentan schon leicht verzweifelt auf der Suche nach einer Funktion in Excel (falls es diese gibt).

Mein Problem ist Folgendes:

Ich habe als X-Werte Perioden 1-12 und die dazugehörigen Y-Werte sind die Abverkäufe eines Produktes. Ich hab ein Diagramm erstellt und mir die Trendlinie anzeigen lassen. Die Trendlinie mit dem höchsten R^2 ist ein Polynom 6.Grades. Meine Frage ist jetzt, ob es eine Funktion in Excel gibt, welche anhand meiner X-&Y-Werte mir die Parameter vor den jeweiligen X^1,2,3,.. -Werten ausgibt (womöglich in einem Array), welche dann denen aus der Trendlinie entsprechen. Mit RGP habe ich das bisher nur zu einer linearen Funktion gefunden.

Kurz gesagt, will ich die Trendlinie ohne manuelles Copy-Paste in Excel für Weiterrechnungen verwenden und finde leider nicht die passende Funktion..

Bin für jede Hilfestellung sehr dankbar! :)

[Fennek]

Hallo,

wenn man eine Trendlinie einfügt, gibt es die Option "Formel anzeigen". In einem kleinen Test ging das auch bei Polynomen.

mfg

[TheLuzl]

Ja das stimmt.

Mein Problem ist aber, dass ich gerne diese Formel mit Hilfe einer Funktion auf meinem Tabellenblatt habe, bzw. die Vorfaktoren der verschiedenen X^1,2... So dass ich mit Hilfe dieses Arrays automatisch weiterrechnen kann und nicht immer die Formel nach jeder Datenmengenveränderung manuell kopieren muss, weil sich dadurch natürlich auch die Trendlinie verändert.

Danke!

[Fennek]

Hallo,

als "proof-of-concept" kann man die Formel mit wenigen Zeilen vba auslesen.

Code:

Sub Makro1()
'
Dim Ch As ChartObject
Dim WS As Worksheet

Set WS = ActiveSheet
Set Ch = WS.ChartObjects(1)

Debug.Print Ch.Chart.FullSeriesCollection(1).Trendlines(1).DataLabel.Text
End Sub

Ehrlich gesagt, ich habe ziemliche Problem Charts mit vba anzusprechen. Ich hoffe, dass deine VBA-Kenntnisse recht gut sind, um den gezeigten Code auf deine Problemstellung umzusetzen.

mfg

[TheLuzl]

Hallo,

leider gehen meine VBA-Kenntnisse gegen 0. Wenn ich den Quelltext dennoch richtig verstehe, versuchst du da die Trendlinie auf die Arbeitsmappe abzubilden oder?

Gibt es keine Funktion wie RGP oder ähnliches, weche die Koeffizienten ausspuckt? Bei einer linearen Funktion wäre es RGP(Y-WErte;X-Werte,1,1), wobei Felder markiert sein müssen und man mit Strg+Shift+Enter die Zelle verlässt. (siehe  https://www.youtube.com/watch?v=bZzoWRp5TYo 1:45   ) Gibt es da eine Möglichkeit das für eine Polynomfunktion anzuwenden?   EDIT: Hab gerade gemerkt, dass man RGP auch immer manuell machen muss wenn man die Datenreihe verändert. Gibt es da noch eine die sich "automatisch" anpasst?

Vielen Dank!

[Fennek]

Hallo,

der vba-Code liest das Textfeld im Chart aus und übergibt die Formel in eine Stringvariable. Es ist möglich, aber etwas "fieselig", daraus die Parameter auszulesen und in eine Formel, sowohl in vba als auch im Arbeitsblatt, zu übergeben. Aber dazu bedarf es schon ganz ordentlicher vba-Kenntnisse.

Ich kenne keine Formel in xl, die das optimale Polynom berechnet, handgestrickt mag es gehen.

Also bleibt nur ein "good luck".

mfg

[Ego]

Hallo Luz,

ich habe RGP zwar noch nicht genutzt, aber nach dem Hilfetext kann man diese Funktion auch für Polynome einsetzen.

Die X-Werte sind dann mehrspaltig. In einer Spalte x, in einer x^2 .....


Und das sich eine Excel-Standard-Funktion nicht anpasst, wenn sich ein Wert ändert habe ich noch nicht gehört.

[Fennek]

Hallo,

wie man aus dem Chart die Funktion auslesen kann, steht bereits oben. Hier ist der Code ergänzt, dass der Text in eine funktionierende Formel umgewandelt wird:

Code:

Sub Makro1()
'
Dim Ch As ChartObject
Dim WS As Worksheet

Set WS = ActiveSheet
Set Ch = WS.ChartObjects(1)

iFormel = Ch.Chart.FullSeriesCollection(1).Trendlines(1).DataLabel.Text
iFormel = Right(iFormel, Len(iFormel) - 2)
iFormel = Replace(iFormel, "x2", "x^2") ' weitere Zeile für x³, x^4 usw
iFormel = Replace(iFormel, "x", " * x")

iFormel = Replace(iFormel, "x", "ZS(-1)")

Debug.Print iFormel

Range("M2:M17").FormulaR1C1Local = iFormel
End Sub

mfg

PS: nach meinem Verständnis liefert rgp eine linerare REgression, auch eine multiple, d.h. y = a*(x1) + b*(x2) + konst

[TheLuzl]

Guten Morgen,

Und das sich eine Excel-Standard-Funktion nicht anpasst, wenn sich ein Wert ändert habe ich noch nicht gehört.

Sorry, ich hab in der falschen Zeile etwas verändert, weshalb sich dann natürlich das RGP nicht weiter verändert hat. Ich saß gestern, glaub ich, einfach zu lange davor. :20:

ich habe RGP zwar noch nicht genutzt, aber nach dem Hilfetext kann man diese Funktion auch für Polynome einsetzen.

Ja, dass habe ich mir auch gedacht, allerdings war ich bisher noch nicht so schlau um herauszufinden, wie genau ich dann die Formel schreiben muss um die Koeffizienten des Polynoms herauszufinden. Haben Sie da eine Idee? Oder bin ich schon wieder falsch und es ist wie Fennek schrieb:

PS: nach meinem Verständnis liefert rgp eine linerare REgression, auch eine multiple, d.h. y = a*(x1) + b*(x2) + konst

 eine lineare Regression bei verschiedenen X? Ich habe allerdings ein und das selbe X welches zum Rechnen benutzt wird.
@Fennek: Danke! Ich werde mir das heute mal angucken :)

[TheLuzl]

Hier einmal meine Arbeitsmappe. Das Makro hab ich noch ein Laufzeitfehler. Evtl hat einer von euch eine Idee, wie ich den wegbekomme.

Und falls noch jemand eine Idee mittels RGP-Funktion bei Polynomen hat, wobei die X's immer das Selbe sind, würde ich mich darüber auch freuen!

Vielen Dank!