Formel aus Trendlinie und Achsen vertauschen

[Frank Vellner]

Liebe NG,

ich habe eine Tabelle mit 2 Zahlenreihen. Nun möchte ich in Excel 2007
grafisch feststellen, welche Art der Näherung am besten passt. Dann
möchte ich die Sache mit der errechneten Formel (Gleichung der
Trendlinie) in der Tabelle darstellen.(apropos: muss man die Formel
wirklich abschreiben oder bekommt man die auch irgendwie in die
Tabelle?).

Wie muss ich die Tabelle aufbauen, damit ich das richtige Ergebnis
sehe? Wie kann ich x und y vertauschen, um die umgekehrte Relation als
Formel zu sehen?

Beispiel:
A | B
100 | 2
200 | 3,94
300 | 5,82
400 | 7,64
500 | 9,4
600 | 11,1
700 | 12,74
800 | 14,32
900 | 15,84
1000 | 17,3


Im Diagramm steht auf der horizontalen Achse die A-Werte (ich bekomme
sie nicht auf die vertikale Achse). Die Trendlinie als Polynom lautet
y=-0,03x^2+2,03x

Egal, wie ich die Formel nutze oder umgechne - ich kommt damit nie von
A auf B oder umgekehrt. Wie kann ich die richtigen Formeln (für beide
Richtungen) in Excel herausfinden?

Vielleicht wäre die Lösung einfach, wenn es mir gelingen würde, die
Y-Achse auf die A-Werte zu beziehen. Aber auf dem Ribbon Layout kann
ich unter "Achsen" gar keinen Bezug auswählen ;-(

Viele Grüße
Frank

[Alexander Wolff]

Post by Frank Vellner
Egal, wie ich die Formel nutze oder umgechne - ich kommt damit nie von
A auf B oder umgekehrt. Wie kann ich die richtigen Formeln (für beide
Richtungen) in Excel herausfinden?

Die Funktion kann nur gleich bleiben. Warum? Weil Du sonst für ein x zwei y
erhieltest - es gibt aber nur einen Funktionswert. Daher ist es eine rein
grafische Frage, die "Umstellung einer Konvention" sozusagen. Du brauchst
ein Tool, welches Dir das Koordinatensystem an der 45°-Linie (Steigung 100%)
spiegelt.

[Thomas Ramel]

Grüezi Frank

Post by Frank Vellner
ich habe eine Tabelle mit 2 Zahlenreihen. Nun möchte ich in Excel 2007
grafisch feststellen, welche Art der Näherung am besten passt. Dann
möchte ich die Sache mit der errechneten Formel (Gleichung der
Trendlinie) in der Tabelle darstellen.(apropos: muss man die Formel
wirklich abschreiben oder bekommt man die auch irgendwie in die
Tabelle?).

JA, das kannst Du auch mit den Funktionen TREND() RGP() und RKP() tun,
je nach Funktion der Trendlinie die Du haben möchtest.
Wenn Du den Grad deines Polynoms kennst kannst Du anschliessend auch
alle Koeffizienten mit RGP() herausfiltern ohne, dass Du eine
Trenlinie im Diagramm darstellen musst.

Post by Frank Vellner
Wie muss ich die Tabelle aufbauen, damit ich das richtige Ergebnis
sehe? Wie kann ich x und y vertauschen, um die umgekehrte Relation als
Formel zu sehen?
A | B
      100 | 2
      200 | 3,94
      300 | 5,82
      400 | 7,64
      500 | 9,4
      600 | 11,1
      700 | 12,74
      800 | 14,32
      900 | 15,84
      1000 | 17,3
Im Diagramm steht auf der horizontalen Achse die A-Werte (ich bekomme
sie nicht auf die vertikale Achse). Die Trendlinie als Polynom lautet
y=-0,03x^2+2,03x

Die Genauigkeit der Darstellung dieser Formel ist gerade bei Polynomen
eminent wichtig.

Klicke mal die dargestellte Formel an, wähle 'Trendlinie formatieren'
und dort im Register 'Zahlen' legst Du das Zahlenformat mit 20
Nachkommastellen fest.

Das aber tue bitte erst wenn Du die Zeilen unten gelesen und umgesetzt
hast ;-)

Post by Frank Vellner
Egal, wie ich die Formel nutze oder umgechne - ich kommt damit nie von
A auf B oder umgekehrt. Wie kann ich die richtigen Formeln (für beide
Richtungen) in Excel herausfinden?

Das alles zeigt mir, dass Du ein Linien-Diagramm verwendest - verwende
für solche Relationen *unbedingt!* ein XY-Punkte-Diagramm.
Nur dann stimmt die Funktion der Trenlinie auch mit den Werten der X-
Achse überein.

Mit dem XY-Punktediagramm kannst Du auch die Datenreihen vertauschen,
wenn Du das möchtest.

Post by Frank Vellner
Vielleicht wäre die Lösung einfach, wenn es mir gelingen würde, die
Y-Achse auf die A-Werte zu beziehen. Aber auf dem Ribbon Layout kann
ich unter "Achsen" gar keinen Bezug auswählen ;-(

Ja, weil Du ein Linien-Diagramm verwendest... ;-)


--
Mit freundlichen Grüssen

Thomas Ramel
- MVP für MS-Excel -

[Frank Vellner]

Moin Thomas,

Post by Thomas Ramel
JA, das kannst Du auch mit den Funktionen TREND() RGP() und RKP() tun,
je nach Funktion der Trendlinie die Du haben möchtest.
Wenn Du den Grad deines Polynoms kennst kannst Du anschliessend auch
alle Koeffizienten mit RGP() herausfiltern ohne, dass Du eine
Trenlinie im Diagramm darstellen musst.

urps, ich habe nun die Hilfe zu RGP() durchgelesen, aber ich begreife
es nicht. Wenn ich bei den genannten Beispielwerten als Lösung ein
Polynom 2. Grades vermute - was muss ich eingeben, um an die
Koeffizienten zu kommen?

Egal was ich ausprobiere, RGP liefert mir eine Zahl. Ich verstehe
weder, wie ich der Funktion den Grad mitteile, noch wie sie mir die
Koeffizienten zurückgibt. In der Hilfe lese ich
=RGP(Y_Werte, X_Werte^SPALTE($A:$C))
aber auch daraus werde ich leider nicht schlau. Vielleicht habe ich ein
Brett vor dem Kopf oder zu wenig Erfahung mit Statistik.

Post by Thomas Ramel
Klicke mal die dargestellte Formel an, wähle 'Trendlinie formatieren'
und dort im Register 'Zahlen' legst Du das Zahlenformat mit 20
Nachkommastellen fest.

ok, das klappt.

Post by Thomas Ramel
Das alles zeigt mir, dass Du ein Linien-Diagramm verwendest

stimmt

Post by Thomas Ramel
- verwende
für solche Relationen *unbedingt!* ein XY-Punkte-Diagramm.
Nur dann stimmt die Funktion der Trenlinie auch mit den Werten der X-
Achse überein.

aha, wie dumm von mir - so kommen korrekte Formeln heraus!

Post by Thomas Ramel
Mit dem XY-Punktediagramm kannst Du auch die Datenreihen vertauschen,
wenn Du das möchtest.

aber das geht nur, wenn ich die Spalten der Tabelle vertausche und
daraus einen neuen Chart berechne, gell? In einem bestehenden mal eben
schnell vertauschen, geht nicht?

Post by Thomas Ramel

Post by Frank Vellner
Vielleicht wäre die Lösung einfach, wenn es mir gelingen würde, die
Y-Achse auf die A-Werte zu beziehen. Aber auf dem Ribbon Layout kann
ich unter "Achsen" gar keinen Bezug auswählen ;-(

Ja, weil Du ein Linien-Diagramm verwendest... ;-)

stimmt, aber auch als x-y-Diagramm sehe ich keine Möglichkeit die
Bezüge zu ändern.

Aber du hast mir schon mal sehr geholfen - ich komme jetzt wenigstens
an korrekte Formeln, die ich abschreiben und verwenden kann!

Viele Grüße
Frank

[Thomas Ramel]

Grüezi Frank

Frank Vellner schrieb am 09.11.2009

Post by Frank Vellner

Post by Thomas Ramel
JA, das kannst Du auch mit den Funktionen TREND() RGP() und RKP() tun,
je nach Funktion der Trendlinie die Du haben möchtest.
Wenn Du den Grad deines Polynoms kennst kannst Du anschliessend auch
alle Koeffizienten mit RGP() herausfiltern ohne, dass Du eine
Trenlinie im Diagramm darstellen musst.

urps, ich habe nun die Hilfe zu RGP() durchgelesen, aber ich begreife
es nicht. Wenn ich bei den genannten Beispielwerten als Lösung ein
Polynom 2. Grades vermute - was muss ich eingeben, um an die
Koeffizienten zu kommen?
Egal was ich ausprobiere, RGP liefert mir eine Zahl. Ich verstehe
weder, wie ich der Funktion den Grad mitteile, noch wie sie mir die
Koeffizienten zurückgibt. In der Hilfe lese ich
=RGP(Y_Werte, X_Werte^SPALTE($A:$C))
aber auch daraus werde ich leider nicht schlau. Vielleicht habe ich ein
Brett vor dem Kopf oder zu wenig Erfahung mit Statistik.

Du musst einen Bereich markieren (das Beispiel oben wäre wohl für ein
Polynom dritten Grades), sagen wir mal A1:D10, die Formel eintragen und mit
STRG+Umschalt+Return als Matrixfomel abschliessen.

IN der ersten Zeile sollten dann deine Koeffizienten stehen, in den
weiteren noch mehr Angaben und statistische Ergebnisse.

...anhand eines konkreten Beispiels würde es leichter fallen, das Ganze zu
erklären - Du kennst ja meine Mail-Adresse... ;-)

Post by Frank Vellner

Post by Thomas Ramel
Klicke mal die dargestellte Formel an, wähle 'Trendlinie formatieren'
und dort im Register 'Zahlen' legst Du das Zahlenformat mit 20
Nachkommastellen fest.

ok, das klappt.

Post by Thomas Ramel
Das alles zeigt mir, dass Du ein Linien-Diagramm verwendest

stimmt

Post by Thomas Ramel
- verwende
für solche Relationen *unbedingt!* ein XY-Punkte-Diagramm.
Nur dann stimmt die Funktion der Trenlinie auch mit den Werten der X-
Achse überein.

aha, wie dumm von mir - so kommen korrekte Formeln heraus!

Fein, wenn das nun soweit passt :-)

Post by Frank Vellner

Post by Thomas Ramel
Mit dem XY-Punktediagramm kannst Du auch die Datenreihen vertauschen,
wenn Du das möchtest.

aber das geht nur, wenn ich die Spalten der Tabelle vertausche und
daraus einen neuen Chart berechne, gell? In einem bestehenden mal eben
schnell vertauschen, geht nicht?

Doch, wenn Du das Diagramm anklichst und dann im Ribbon: 'Entwurf' auf
[Datenauswählen] klickst, kannst du den Datenbereich bearbeiten.
Vertausche hier ganz einfach die Bezüge zu den X und Y Werte mit
Copy/Paste.

Post by Frank Vellner

Post by Thomas Ramel

Post by Frank Vellner
Vielleicht wäre die Lösung einfach, wenn es mir gelingen würde, die
Y-Achse auf die A-Werte zu beziehen. Aber auf dem Ribbon Layout kann
ich unter "Achsen" gar keinen Bezug auswählen ;-(

Ja, weil Du ein Linien-Diagramm verwendest... ;-)

stimmt, aber auch als x-y-Diagramm sehe ich keine Möglichkeit die
Bezüge zu ändern.

Siehe oben....

....und das mit dem direkten Ermitteln der Koeffizienten kriegen wir auch
noch hin :-)




Mit freundlichen Grüssen
Thomas Ramel

[Frank Vellner]

Moin Thomas,

Post by Thomas Ramel
Du musst einen Bereich markieren (das Beispiel oben wäre wohl für ein
Polynom dritten Grades), sagen wir mal A1:D10, die Formel eintragen

ok, ich habe jetzt genau diesen Bereich markiert. Dann habe ich dort
=RGP(A14:A23;B14:B23;WAHR;WAHR) als Matrixformel eingetragen. In den
Zellen ist
die geschweifte Klammer für Matrixformeln. Die Daten aus dem genannten
Beispiel befinden sich entsprechend in A14:A23 und B14:B23.

Im Bereich A1:D10 steht nun 40 mal die gleiche Matrixformel. Als
Ergebnis steht in A1 der Wert 58,70652264, in Spalte B der
Wert -37,65229163 und in C und D jeweils "#NV". Bis Zeile 5 sind in A
und B noch statistische Werte, dann kommt NV.

Nun habe ich also die Parameter für eine Lineare Regression.

Laut grafischer Analyse müsste aber eine quadratische Gleichung: y =
0,6078x2 + 46,887x + 5,2328 herauskommen. Wie bringe ich RGP dazu, die
Parameter eines von mir gewünschten Grades des Polynoms zu berechnen?

Post by Thomas Ramel
Doch, wenn Du das Diagramm anklichst und dann im Ribbon: 'Entwurf' auf
[Datenauswählen] klickst, kannst du den Datenbereich bearbeiten....

ich befinde mich dann im Fenster namens "Datenquelle auswählen". Oben
im Diagrammbereich ist der Bezug auf die Rohdaten. Diese bekommen im
Blatte einen Laufrahmen. Dann kommen die Legendeneinträge (Reihen) und
die Horizontale Achsenbeschritung (Rubrik)

Post by Thomas Ramel
Vertausche hier ganz einfach die Bezüge zu den X und Y Werte mit
Copy/Paste.

das finde ich leider nicht. Wo kann ich dort copy/paste machen?

Viele Grüße
Frank

[Thomas Ramel]

Grüezi Frank

Frank Vellner schrieb am 10.11.2009

Post by Frank Vellner

Post by Thomas Ramel
Du musst einen Bereich markieren (das Beispiel oben wäre wohl für ein
Polynom dritten Grades), sagen wir mal A1:D10, die Formel eintragen

ok, ich habe jetzt genau diesen Bereich markiert. Dann habe ich dort
=RGP(A14:A23;B14:B23;WAHR;WAHR) als Matrixformel eingetragen. In den
Zellen ist
die geschweifte Klammer für Matrixformeln. Die Daten aus dem genannten
Beispiel befinden sich entsprechend in A14:A23 und B14:B23.
Im Bereich A1:D10 steht nun 40 mal die gleiche Matrixformel. Als
Ergebnis steht in A1 der Wert 58,70652264, in Spalte B der
Wert -37,65229163 und in C und D jeweils "#NV". Bis Zeile 5 sind in A
und B noch statistische Werte, dann kommt NV.
Nun habe ich also die Parameter für eine Lineare Regression.

Ja, das ist korrekt - Du muss der Formel noch mitteilen, dass sie ein
Polynom x-ten Grades ausgeben soll.

Post by Frank Vellner
Laut grafischer Analyse müsste aber eine quadratische Gleichung: y =
0,6078x2 + 46,887x + 5,2328 herauskommen. Wie bringe ich RGP dazu, die
Parameter eines von mir gewünschten Grades des Polynoms zu berechnen?

=RGP(B14:B23;A14:A23^SPALTE(A:C);WAHR;WAHR)

und dann als Matrixformel abschliessen.

In der ersten Zeile findest Du dann die 4 Koeffizienten.

Post by Frank Vellner

Post by Thomas Ramel
Doch, wenn Du das Diagramm anklichst und dann im Ribbon: 'Entwurf' auf
[Datenauswählen] klickst, kannst du den Datenbereich bearbeiten....

ich befinde mich dann im Fenster namens "Datenquelle auswählen". Oben
im Diagrammbereich ist der Bezug auf die Rohdaten. Diese bekommen im
Blatte einen Laufrahmen. Dann kommen die Legendeneinträge (Reihen) und
die Horizontale Achsenbeschritung (Rubrik)

Klicke auf die betreffende Datenreihe in der Auswahl unten links und dann
auf den Button [bearbeiten]

Hier siehst Du dann die Zuordnung der Daten zu den einzelnen Achsen.

Post by Frank Vellner

Post by Thomas Ramel
Vertausche hier ganz einfach die Bezüge zu den X und Y Werte mit
Copy/Paste.

das finde ich leider nicht. Wo kann ich dort copy/paste machen?

...und da klappt das dann auch mit Copy/Paste.


Mit freundlichen Grüssen
Thomas Ramel

[Frank Vellner]

Moin Thomas,

Post by Thomas Ramel
Ja, das ist korrekt - Du muss der Formel noch mitteilen, dass sie ein
Polynom x-ten Grades ausgeben soll.

Post by Frank Vellner
Laut grafischer Analyse müsste aber eine quadratische Gleichung: y =
0,6078x2 + 46,887x + 5,2328 herauskommen. Wie bringe ich RGP dazu,
die Parameter eines von mir gewünschten Grades des Polynoms zu
berechnen?

=RGP(B14:B23;A14:A23^SPALTE(A:C);WAHR;WAHR)

urps, das war eine schwere Geburt... Es hat geklappt! Vielen Dank für
deine Geduld!

Damit ich RGP() beim nächsten Mal ganz alleine anwenden kann, müsste
ich die Sache noch ein bischen besser verstehen. Da ich schon
Matrixformeln nicht wirklich verstehe und mir auch nicht klar ist,
warum ich hier "Spalte" (die im Beispiel ja "1" zurückgibt) verwenden
sollte, wird mein Verständnis nicht arg weit gehen.

Aber das macht nix - ich möchte das ja hier nicht als
Nachhilfeunterricht missbrauchen. Mir geht es nun nur noch um eine
"schematische" Anwendung. Ist das so korrekt:

RGP hat 4 Eingabemöglichkeiten die ich jetzt als 1-4 bezeichne.Könnte
man RGP für Dummies so definieren:

1. Bereich der zu errechnenden Werte
2. Bereich der gegebenen Werte mit dem Exponent Spalte() wobei sich
Spalte auf den Bereich bezieht, wo ich die Koeffizienten des Polynoms
haben möchte (ohne die Konstante b) A:B bewirkt also 2. Grad, A:D 4.
Grad
3. Da ist mir nicht klar, ob die Einstellung "Wahr" grundsätzlich
sinnoller ist (also b=0)
4. Wenn ich mehr statistische Angaben haben möchte auf wahr

Post by Thomas Ramel
Klicke auf die betreffende Datenreihe in der Auswahl unten links und
dann auf den Button [bearbeiten]
Hier siehst Du dann die Zuordnung der Daten zu den einzelnen Achsen.

auch so - da hatte ich wohl zu früh aufgegeben. Vielen Dank - das
Tauschen klappt nun super!

Viele Grüße
Frank

[Thomas Ramel]

Grüezi Frank

Frank Vellner schrieb am 11.11.2009

Post by Frank Vellner

Post by Thomas Ramel
Ja, das ist korrekt - Du muss der Formel noch mitteilen, dass sie ein
Polynom x-ten Grades ausgeben soll.

Post by Frank Vellner
Laut grafischer Analyse müsste aber eine quadratische Gleichung: y =
0,6078x2 + 46,887x + 5,2328 herauskommen. Wie bringe ich RGP dazu,
die Parameter eines von mir gewünschten Grades des Polynoms zu
berechnen?

=RGP(B14:B23;A14:A23^SPALTE(A:C);WAHR;WAHR)

urps, das war eine schwere Geburt... Es hat geklappt! Vielen Dank für
deine Geduld!

Na, aber gerne doch :-)

Post by Frank Vellner
Damit ich RGP() beim nächsten Mal ganz alleine anwenden kann, müsste
ich die Sache noch ein bischen besser verstehen. Da ich schon
Matrixformeln nicht wirklich verstehe und mir auch nicht klar ist,
warum ich hier "Spalte" (die im Beispiel ja "1" zurückgibt) verwenden
sollte, wird mein Verständnis nicht arg weit gehen.

RGP gibt normalerweise einfach einen Trend zurück mit Steigung und
Achsabschnitt.

Sobald es um Polynome geht muss das der Funktion mitgeteilt werden, indem
die X-Werte entsprechend potenziert werden - je nach Grad des Polynoms
eben.

Post by Frank Vellner
Aber das macht nix - ich möchte das ja hier nicht als
Nachhilfeunterricht missbrauchen. Mir geht es nun nur noch um eine
RGP hat 4 Eingabemöglichkeiten die ich jetzt als 1-4 bezeichne.Könnte
1. Bereich der zu errechnenden Werte

Hmmm nicht die zu errechnenden, sondern die bestehenden Y-Werte sind das.
Anhand der ermittelten Koeffizienten lassen sich dann weitere Y-Werte zu
neuen X-Werten berechnen.

Post by Frank Vellner
2. Bereich der gegebenen Werte

Ganz einfach die gegebenen X-Werte.

Post by Frank Vellner
mit dem Exponent Spalte() wobei sich
Spalte auf den Bereich bezieht, wo ich die Koeffizienten des Polynoms
haben möchte

Hier habe ich dich noch ein wenig verwirrt, fürchte ich.

Die Funktion benötigt eigentlich eine Matrix mit den Exponenten z.B. in
folgender Form:

^{1\2\3\4}

Da sich der Spaltentrenner je nach Ländereinstellungen unterscheidet ist
die Variante mit SPALTE(A:D) allgemein gültig.
Durch die Auswertung als Matrix (eben weil die Formel mit
STRG+Umschalt+Return abgeschlossen wird) ergibt sich dann diese Matrix.

Post by Frank Vellner
(ohne die Konstante b) A:B bewirkt also 2. Grad, A:D 4. Grad

Das ist wieder korrekt - der Bereich der mit der Formel abgedeckt werden
muss ist also immer eine Spalte breiter als der Grad des gesuchten
Polynoms.

Es ist im Übrigen auch möglich einzelne der Koeffizienten in je eine Zeile
zu holen indem die Ergebnis-Matrix entsprechend ausgelesen wird.

Post by Frank Vellner
3. Da ist mir nicht klar, ob die Einstellung "Wahr" grundsätzlich
sinnoller ist (also b=0)

Das hängt von der erwarteten und gesuchten Funktion ab.
Wenn Du in den Einstellungen der Trendlinie im Diagramm den Schnittpunkt
bei '0' wählst musst Du hier FALSCH eingeben, dann wird die Konstante b = 0
gesetzt und die Koeffizienten verändern sich.
Einfach mal ausprobieren und die Koeffizienten vergleichen.

Post by Frank Vellner
4. Wenn ich mehr statistische Angaben haben möchte auf wahr

Ja, das ist korrekt.

Post by Frank Vellner

Post by Thomas Ramel
Klicke auf die betreffende Datenreihe in der Auswahl unten links und
dann auf den Button [bearbeiten]
Hier siehst Du dann die Zuordnung der Daten zu den einzelnen Achsen.

auch so - da hatte ich wohl zu früh aufgegeben. Vielen Dank - das
Tauschen klappt nun super!

Na, auch das freut mich sehr :-)


Mit freundlichen Grüssen
Thomas Ramel