Microsoft Excel

Herbers Excel/VBA-Archiv

Funktion "Schätzer" mit polynomischer Trendlinie

Betrifft: Funktion "Schätzer" mit polynomischer Trendlinie von: Ralf
Geschrieben am: 09.07.2013 13:43:27

Hallo Forum,

ich möchte aus einem großen Datenpool mit der Schätzfunktion einen Wert in der Zukunft berechnen.
Konkret soll das so funktionieren:
Ich habe von ausgewählten Devisen einen Tageskurs der letzten Monate. Daraus habe ich ein Diagramm erstellt und eine polynomische Trendlinie eingebaut.
Diese hat z. B. die Formel: y = -1E-05x^2 + 0,8046x - 16016
Excel bietet mir eine Schätzfunktion. Diese bezieht sich aber leider nur auf den linearen Trend und ist somit recht ungenau.
Gibt es eine Möglichkeit die o. g. Formel dafür zu verwenden?

Vielen Dank im Voraus für Eure Hilfe.

Viele Grüße

Ralf

  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: Luschi
Geschrieben am: 09.07.2013 14:04:08

Hallo Ralf,

schau mal, ob Dir das hier weiter hilft:

http://www.excelformeln.de/formeln.html?welcher=266

Gruß von Luschi
aus klein-Paris


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: Ralf
Geschrieben am: 09.07.2013 14:13:18

Hallo Luschi,

danke für die schnelle Rückmeldung und den Tipp.

Hm, nicht ganz.
Die Formel ergibt sich aus dem Diagramm und diese ist somit dynamisch. Es kommen tägliche neue Daten hinzu, die rauf oder runter gehen und daher die Formel leicht verändern. Eine statische Formel im Excel einzugeben, dass kriege ich noch halbwegs hin, aber vorausschauend die polynomische Formel aus dem Diagramm auszulesen und im Excel als Berechnungsformel einzusetzen, dass schaffe ich nicht.

Daher lasse ich das Thema offen.

Viele grüße

Ralf


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: fcs
Geschrieben am: 09.07.2013 23:15:49

Hallo Ralf,

polynomische Trendlinien sind zwar manchmal gut geeignet einen Satz existierender Datenpunkte relativ gut zu beschreiben.
Zukünftige Werte der Trendline aus den im Diagramm angezeigten Polynom zu berechnen ist aber meistens extrem ungenau, weil die Y-Werte des Polynoms für größere/zukünftige X-Werte schnell ansteigen oder abfallen, da der größte Exponent des Polynoms dominant wird.

Wenn du die im Diagramm angezeigte Formel in der Tabelle für Berechnungen verwenden willst, dann muss du den angezeigten Formeltext so formatieren, dass die Zahlen im wissenschaftlichen Format mit mindestens 6 evtl. sogar mehr Nachkommastellen angezeigt werden.

Theoretisch könnte man den Text der Polynomtrendformel aus dem Diagramm kopieren, in eine Zelle einfügen und dann in der Zelle
X3 durch *A2^3
X2 durch *A2^2
X durch *A2
Y = durch =
ersetzen. A2 wäre hier die Zelle in der ein ggf. zukünftiges Datum steht.

Diese Aktion könnte man per Makro programmieren. Es ist aber schon etwas mühselig im Diagramm das korrekte Objekt anzusprechen, um an den Formeltext zu kommen.

Gruß
Franz


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: Ralf
Geschrieben am: 10.07.2013 06:11:26

Hallo Franz,

danke für die Rückmeldung.
Was schlägst Du vor, um einen zukünftigen Wert zu "schätzen"? Hast Du Erfahrung mit solchen Datenreihen?

Vielen Dank im Voraus für eine Rückmeldung.

Viele Grüße

Ralf


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: fcs
Geschrieben am: 10.07.2013 07:55:49

Hallo Ralf,

es gibt halt Daten, die sich so unwillkürlich ändern, dass man ihren zukünftigen Trend nicht mit "einfachen" Ausgleichspolynomen nicht vorhersehen.

Wenn du hier weiterkommen willst, dann musst du dich wohl mit jenen Diagrammen beschäftigen die von Börsenjournalisten verwendet werden, um daraus dann weitere Entwicklung des Kurses abzuleiten. Ich kenne mich da nicht aus und mehr als eine Bandbreite für den zukünftigen Kurs kann man daraus auch nicht ableiten.

Gruß
Franz


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: Ralf
Geschrieben am: 10.07.2013 08:05:52

Hallo Franz,

die polynomische Trendlinie ergibt für meine Zwecke die bestmögliche Näherung als Schätzung. Ich bin nicht an der Börse beschäftigt und kenne demzufolge die ganzen Nebenkriegsschauplätze, die solche Daten beeinflussen, nicht.

Die von Dir erwähnte Bandbreite würde mir da ausreichen. Es soll eine Schätzung sein, nichts weiter. Das der hochgerechnete Wert falsch sein kann bzw. wird, ist mir durchaus bewusst. Es ist aber immer noch besser, als den Wert mit der linearen Formel zu ermitteln. Der ist nämlich richtig weit weg.

Viele Grüße

Ralf


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: fcs
Geschrieben am: 10.07.2013 11:53:44

Hallo Ralf,

ich weiss nicht, ob die Trendlinien in Excel 2007 diese Option schon ermöglichen.

Unter Excel 2010 kann man eine Trendlinie in einem XY-Punktdiagramm so einrichten, dass der Trend auch für größere/zukünftige X-Werte mit angezeigt wird.

Evtl. reicht das ja schon für dich als Richtwert.
Ansonsten bist du für die quadratische Trendfunktion ja schon auf dem Weg zu einer Lösung.

Gruß
Franz


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: bst
Geschrieben am: 10.07.2013 09:36:55

Auch Hallo,

lade Dir mal die Beispielmappe von http://www.excelformeln.de/formeln.html?welcher=269

Die 3 Parameter einer polynomischen Trendlinie 2. Ordnung bekommst Du (nebeneinander) via:

{=RGP(y-Werte;x-Werte^{0.1.2};0;0)}

Den y-Wert zu einem Neuen-x-Wert bestimme via:

=TREND(y-Werte;x-Werte^{1.2};Neuen-x-Wert^{1.2})

HTH, Bernd


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: Ralf
Geschrieben am: 10.07.2013 09:46:28

Hallo Bernd,

danke für den Tipp.
WOW!
Da muss ich mal schauen, ob ich das hinbekomme.
Ich lasse das Thema vorerst mal offen.

Viele Grüße

Ralf


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: Ralf
Geschrieben am: 10.07.2013 11:24:32

Hallo Bernd,

ich habe die Formeln jetzt mal mit meinen Bezügen angepasst.
Es erscheint aber in allen Zellen "#Wert".
Wie müssen die runden Klammern für {1.2} erstellt werden?
Ich habe diese jetzt mal mit der Tastatur (Alt Gr 7 bzw. Alt Gr 0) eingegeben, ohne zu wissen, ob dies richtig ist.

Hm, alles sehr komisch.

Viele Grüße

Ralf


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: bst
Geschrieben am: 10.07.2013 12:08:29

Nochmals Hallo,

die erste Formel ist eine Matrix-Formel. Dazu musst Du zuerst 3 nebeneinander liegende Zellen selektieren, dann die Formel "=RGP(y-Werte;x-Werte^{0.1.2};0;0)" eingeben und am Ende mit STRG+SHIFT+ENTER bestätigen.

Die geschweiften Klammern zeigt Excel dann von alleine an.

Wenn Du da #Wert bekommst steht in Deinen x-Werten eine 0. Das mag diese Formel wohl nicht. Überschreibe die einfach mal mit 0,0001 oder so.

Die Trend-Formel funktioniert hier in Excel 2003 auch mit einer 0 in den x-Werten.

cu, Bernd


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: Ralf
Geschrieben am: 10.07.2013 12:23:25

Hallo Bernd,

den Abschluss der Matrix Formel mit STRG+SHIFT+ENTER kenne ich.
Meine Frage ist, was die gescheiften Klammern in diesem Bereich {0.1.2} zu sagen haben, und ob ich diese einfach mit der Tastatur eingeben kann? Die Formel ist mir schon etwas schleierhaft, da die Zellen C17 bis C23 in der genannten Beispieldatei alle die gleiche Formel (mit identischen Zellbezügen) enthalten, aber ein unterschiedliches Ergebnis herauskommt.

Ich werde die Mappe nachher mal hochladen.

Viele Grüße

Ralf


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: Ralf
Geschrieben am: 10.07.2013 12:50:12

Hallo Forum,

hier habe ich mal eine Beispieldatei kreiert:

https://www.herber.de/bbs/user/86289.xlsx

Vielen Dank im Voraus für eine Rückmeldung.

Viele Grüße

Ralf


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: bst
Geschrieben am: 10.07.2013 13:39:04

Hi,

das ist die direkte Eingabe einer Matrix. Und ja, diese geschweiften Klammern musst Du manuell eingeben. Wie Du das mit der Tastatur machst ist egal.

={1.2.3}

liefert 3 Spalten mit den Werten 1,2,3.

={1;2;3}

liefert 3 Zeilen mit den Werten 1,2,3.

Da Deine Daten nebeneinander und nicht (wie sich das eigentlich gehört) untereinander stehen brauchst Du hier wohl ein:

{=MTRANS(RGP(O3:IV3;O2:IV2^{0;1;2};0;0))}

In Deiner Datei habe ich überall Bezugsfehler, auch hat Dein Diagramm keinen Bezug zu einem Range. Dort stehen die Daten direkt drin?

Oder liegt das daran dass ich dies Datei ins xl 2003 Format konvertiert habe??

cu, Bernd


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: Ralf
Geschrieben am: 10.07.2013 13:52:25

Hallo Bernd,

die geschweiften klammern innerhalb der Formel habe ich mittels Tastatur eingegeben. Die äußeren mit Strg+Shift+Enter.
Die Formel {=MTRANS(RGP(O3:IV3;O2:IV2^{0;1;2};0;0))} steht genau so in der Zelle.

Die Bezugsfehler kommen wohl aus der Konvertierung ins xls Format. Ich habe dies gerade mal probiert, und habe eine Warnmeldung bekommen (Screenshot in der Beispielmappe):

https://www.herber.de/bbs/user/86291.xlsx

Die Bezüge sollten alle sauber sein.

Trotzdem funktioniert die Formel nicht.

Viele Grüße

Ralf


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: bst
Geschrieben am: 10.07.2013 15:32:27

Hi,

ich habe hier wieder nur Bezugsfehler. Kannst Du das nicht mal im 2003 Format abspeichern?

cu, Bernd


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: bst
Geschrieben am: 10.07.2013 15:34:04

Nochmals Hallo,

kann es sein dass Du da mehr als 256 Spalten benutzt?

Dann kann ich das hier in Excel 2003 denn niemals lesen ...

cu, Bernd


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: Ralf
Geschrieben am: 10.07.2013 15:39:09

Hallo Bernd,

ich habe die Datei mal bereinigt:

https://www.herber.de/bbs/user/86295.xls

Viele Grüße

Ralf


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: bst
Geschrieben am: 10.07.2013 15:44:04

Hi Ralf,

ersetze in den {} die Punkte durch Strichpunkte, dann geht das auch.

cu, Bernd


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: Ralf
Geschrieben am: 10.07.2013 15:48:41

Hallo Bernd,

hm, habe ich gemacht.
Es steht trotzdem noch #WERT in den Zellen.
Könntest Du das mal bitte in der Beispieldatei ändern? Geht das bei Dir?

Bitte um Rückmeldung.

Viele Grüße

Ralf


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: bst
Geschrieben am: 10.07.2013 15:58:27

Hi,

siehe Anhang. https://www.herber.de/bbs/user/86296.xls

cu, Bernd


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: Ralf
Geschrieben am: 10.07.2013 16:05:35

Hallo Bernd,

vielen Dank!
Ich muss jetzt mal prüfen, woran es bei mir liegt, dass ich die Zahlenwerte nicht angezeigt bekomme.
Alles sehr merkwürdig.
Ich schaue wahrscheinlich schon zu lange drauf und sehe es nicht mehr.

Nochmals vielen Dank für Deine Unterstützung.

Viele Grüße

Ralf


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: Ralf
Geschrieben am: 10.07.2013 16:18:24

Hallo Bernd,

jetzt habe noch etwas festgestellt.
Die Daten werden täglich erweitert. Ich müsste somit die Bezüge täglich ändern.
Ich habe in den beiden dargestellten Zellen die befüllten Bereiche für die x- und die y-Achse mit einer Formel ermittelt. Kann man diese automatische Aktualisierung noch einbauen?

https://www.herber.de/bbs/user/86297.xls

Vielen Dank fürs Feintuning.

Viele Grüße

Ralf


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: bst
Geschrieben am: 11.07.2013 08:45:13

Morgen Ralf,

benutze Excel-Namen mit Bereich.Verschieben, z.B.:

L15: =ANZAHL(O2:IV2)

Definierte Namen:
Anzahl_Werte =Diagramm!$L$15
x_Werte =BEREICH.VERSCHIEBEN(Diagramm!$O$2;0;0;1;Anzahl_Werte)
y_Werte =BEREICH.VERSCHIEBEN(Diagramm!$O$2;1;0;1;Anzahl_Werte)

Damit kannst Du dann z.B. den Kurs hoch rechen:
L39: =TREND(y_Werte;x_Werte^{1;2};$L$28^{1;2})

Und, Du kannst das auch im Diagramm benutzen.

https://www.herber.de/bbs/user/86303.xls

cu, Bernd


  

Betrifft: AW: Funktion "Schätzer" mit polynomischer Trendlinie von: Ralf
Geschrieben am: 11.07.2013 10:48:22

Hallo Bernd,

wow, super Arbeit.
Das funktioniert jetzt hoffentlich wie gewünscht. Ich werde dies in den nächsten Tagen testen.

Viele Grüße

Ralf


 

Beiträge aus den Excel-Beispielen zum Thema "Funktion "Schätzer" mit polynomischer Trendlinie"