Office: Trendlinie: Parameter in Zelle holen (Ex97)

Hallo allerseits

Ich habe durch verschiedene Datenreihen Trendlinien zu legen. Hierbei kann man sich für alle verfügbaren Typen die Formel im Diagramm anzeigen lassen.

z.B. für ein Polynom 2. Grades ergibt sich für die Trendlinie:

y = 2.34 * x2 + 1.12 * x + 1.32

Die Zahlen 2.34, 1.12 und 1.32 benötige ich zum Weiterrechnen und möchte sie daher in einer Zelle haben.

Welche Möglichkeiten habe ich, dies zu bewerkstelligen (ausser natürlich abtippen) ?

Vielen Dank für jede Hilfe
Florian

:)

 

Moin, Florian,

ohne ein Beispiel mit Basiswerten erscheint mir nur der Hinweis auf die Funktion RGP in der Online-Hilfe sowie auf die englischsprachige Seite von John Walkenbach zum Thema Chart Trendline Formulas als hilfreich (für die Übersetzung entweder die Mappe VBALISTE.xls auf der eigenen Festplatte oder den Download Funktionsbezeichnungen Excel, erstellt von Rainer Beckerbauer aka Ramses).

 

Hallo,

für eine Vielzahl von Funktionen kannst Du dir die Koeffizienten direkt berechnen lassen. Bei Polynomen geht das mit der RGP-Funktion bis zur 16. Ordnung. Sollte für die meisten Anwendungen reichen ;-)

Das ist IMHO wesentlich eleganter, als sie aus der Grafik auszulesen.

Welche Funktionstypen benötigst Du?

CU

Johannes

 

Hallo Jinx

Vielen Dank für die Angabe des Links auf den "Excel Developer Tip". Ich habe jedoch Mühe, die Formeln hier zu interpretieren.

Ich habe es mal mit folgender Zahlenreihe ausprobiert:

1 2
2 3.9
3 6
4 8.2
5 10
6 12.05
7 14.5
8 16.2
9 18
10 20

Erstelle ich eine Grafik und lege ich eine Trendlinie rein (polynomisch, 2. Grades), so gibt mir Excel als Gleichung folgendes raus:

y = -0.0102x2 + 2.1286x - 0.2283

(Bem: x2 = x hoch 2)

Ich schaffe es jedoch nicht, mit den Formeln des Developer Tips aus obenstehenden Zahlenreihen die gleichen Zahlen als Koeffizienten zu erzeugen.

Weisst Du allenfalls Rat ?

Gruss und Danke
Florian

 

Moin, Florian,

meinst Du etwas in der folgenden Art:

	A	B	C	D	E	F	G
1	X	Aktueller Y-Wert	Vorhergesagter Y-Wert		c2	c1	b
2	1,0	2,00	1,890		-0,010227273	2,128560606	-0,22833
3	2,0	3,90	3,988
4	3,0	6,00	6,065
5	4,0	8,20	8,122		y=(c2x2) + (c1x) +b
6	5,0	10,00	10,159
7	6,0	12,05	12,175
8	7,0	14,50	14,170
9	8,0	16,20	16,146
10	9,0	18,00	18,100
11	10,0	20,00	20,035

[/quote]

C2 : =($E*A2^2)+($F*A2)+$G
E2 : {=RGP(B2:B11;A2:A11^{1.2})}
F2 : {=RGP(B2:B11;A2:A11^{1.2})}
G2 : {=RGP(B2:B11;A2:A11^{1.2})}
C3 : =($E*A3^2)+($F*A3)+$G
C4 : =($E*A4^2)+($F*A4)+$G
C5 : =($E*A5^2)+($F*A5)+$G
C6 : =($E*A6^2)+($F*A6)+$G
C7 : =($E*A7^2)+($F*A7)+$G
C8 : =($E*A8^2)+($F*A8)+$G
C9 : =($E*A9^2)+($F*A9)+$G
C10 : =($E*A10^2)+($F*A10)+$G
C11 : =($E*A11^2)+($F*A11)+$G

Excel Tabellen einfach im Web darstellen

 

Hallo jinx

Vielen Dank für Deine schnelle Hilfe; Deine beigefügten Formeln bringen Licht ins Dunkel.

Gruss und Dank
Florian

 

Hallo nochmal

Ich versuche gerade, jinx' Formeln umzusetzen, habe jedoch ein Problem:
dass die RGP-Funktion eine Matrix-Funktion ist, ist mir klar und auch, dass da geschweifte Klammern entstehen, die beim Editieren verschwinden.
Kummer hingegen bereiten mit die geschweiften Klammern nach dem ^-Zeichen: macht man diese von Hand ? ich hab's versucht und dann auch 1.2 eingegeben, aber da entstehen Werte, die einerseits nicht mit dem Muster von jinx übereinstimmen (also auch nicht mit den Koeffizienten einer eingefügten grafischen Trendlinie), andererseits für b ein #NV liefern.

Was mache ich bloss falsch ? Allenfalls würde mir ein angehängtes xls-File weiterhelfen.

Vielen Dank und Gruss
Florian

 

Moin, Florian,

Du markierst in dem gegebenen Beispiel die Zellen E2:G2, gibst dann die Formel ohne geschweifte Klammer ein und drückst zum Abschluß gleichzeitig die Kombination UMSCHALT + STRG + EINGABE - Excel erzeugt dann die geschweifte Klammer automatisch und als Hinweis, dass es sich um eine Array-Formel handelt.

Es ist bei dieser Formel auch nur möglich, den gesamten Bereich zu verändern, Änderungen von Einzelzellen werden zurückgewiesen...

Und wenn es gar nicht klappt, kann ich auch die Mappe anhängen - das wird aber nicht nötig sein...

 

Hi Jinx

Ich probiere und probiere, komme aber auf keinen grünen Zweig.

Ich habe Dir mal mein xls-File angehängt, siehst Du, was ich falsch mache ?

Gruss und Dank
Florian

 

Moin, Florain,

irgendwie sah die Formel nicht so aus, wie ich sie angegeben hatte - die geänderte Mappe kannst Du hier downloaden...

Moderatorenanmerkung: Änderung des Servernamens für die Verfügbarkeit.

 

Hi Jinx

alle meine Probleme sind hiermit gelöst.

Nochmals besten Dank
Florian

 

Hi Du!
Ich habe praktisch das gleiche hier diskutierte Problem, nur mit einem Polynom 3. Grades. Ich denke, deshalb muß ich statt RGP, RKP verwenden, oder? Es funktioniert jedoch nicht. Das hier besprochene Problem kann ich nachvollziehen, aber wofür ist in der Berechnungsformel eigentlich das : ^{1.2} , bzw woher kommt das?? Ich glaube darin liegt mein Problem.
Ich hoffe, Du kannst mich aufklären.
Besten Dank schon jetzt!
Dein Praktikant

 

Hi Praktikant,

nein, RGP ist auch dafür die richtige Funktion. Für eine kubische Gleichung musst du allerdings mit {1.2.3} rechnen lassen und die Größe der Ergebnismatrix anpassen. Wozu die Matrix {1.2.3} dient, fragst du? Nun, bildlich gesprochen wird die polynomische Regression als multivariate lineare Regression gerechnet.

Der Funktionswert y wird dann so berechnet:

Y = az + by + cx + d,

wobei z x^3 substituiert und y für x^2 steht.

Alles klar?

Johannes

 

Wow, es funktioniert!!!
Erstmal vielen Dank! Ich hätte noch zwei klitzekleine Fragen:
Kann ich (wie im Diagramm) bei der Berechnung einen konstanten Wert berücksichtigen (einen festen y-Achsenschnittpunkt)??
Desweiteren möchte ich gerne zur Verbesserung der Genauigkeit der Trendliniengleichung die Wertetabelle erweitern. Wie kann ich hinbekommen, daß, wenn ich Werte zur Tabelle hinzufüge, die Gleichung automatisch neu berechnet wird?
Gruß und Danke, Dein Praktikant

 

Eins habe ich noch vergessen:
Wie kann ich mir denn R² ausgeben lassen??
Dein Praktikant