Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
896to900
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
896to900
896to900
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Potenzielle Trendlinie

Potenzielle Trendlinie
14.08.2007 10:34:00
Marian
Hallo,
ich habe schon einige gute Tipps von dieser Seite genommen, leider jedoch keine Antwort auf die folgende Frage gefunden.
In dem Diagramm habe ich mit der Funktion Trendlinie setzen eine potenzielle Trendlinie eingezeichnet. Diese hat folgende Form: y=0.126 * x ^1.442 , R2=0.963
Mit welcher Funktion erhalte ich diese Parameter in einer Excel-Zelle (inkl. dem R2)?
Für die lineare und exponentielle Rechnung konnte ich die Funktion RGP bzw. RKP verwenden. Gibt es etwas ähnliches für Potenzen?
Vielen Dank für hilfreiche Tipps.
Gruss MM

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Potenzielle Trendlinie
14.08.2007 13:38:00
ingUR
Hallo, @MM,
mir ist keine EXCEL-Funktion zur Bestimmung der Parameter der potenziellen Trendline bekannt, so dass ich nur den Weg über den "Solver" vorschlagen kann, bei dem die Lösung durch die beste Annährung der Regression gesucht wird.
In der einfachsten Variante belegst Du zwei Zellen mit den zu schätzenden Parameter a und b für die Gleichen y* = a * xb. Ich habe hier die Zelen C1 und C2 dafür verwnedet.
In der Hilfsspalte C werden nun die Formel der Potenzgleichung in die Zeilen der gegebenen Y.Werte eingetragen.
Die quadrierten Differenzen zwischen den Werten für y und y* werden mit der Zellenformel uin der Spalte D berechnet. Die Summe aus diesen Werten wird in der Zelle D1 ermittelt.
Die beste Anpassung nach der Methode der Summe kleinsten Fehlerquadrate ist erzielt, wenn man ein Parameterparr {a; b} findet, für die der Zelleninhalt D1 am kleinsten ist. Diese Aufgabe kann man durch den Solver erledigen lassen, sofern man nicht einen geeigneten Algorithmus für eine VBA-Phrpzedur zur findung des Minimums hat.
Das Bestimmtheitsmaß R2 kannst Du einfach mit dem Quadrat der Korrelationtzahl r aus beiden Reihen, der gegebenen y-Werte und der geschätzten y*-Werte, bestimmen.
 ABCDE
1a 0,906168995,1692332140,77208126
2b 0,55273478  
3xyy*  
410,784836010,906168990,014721693 

Formeln der Tabelle
ZelleFormel
D1=SUMME(D4:D20)
E1=KORREL(B4:B20;C4:C20)^2
C4=$C$1*POTENZ(A4;$C$2)
D4=(B4-C4)^2

 ABCDE
1a 0,906168995,1692332140,77208126
2b 0,55273478  
3xyy*  
410,784836010,906168990,014721693 
521,191050241,329226350,019092639 
631,755821421,663147270,008588498 
741,887858541,949793830,00383598 
852,604665892,205739530,159142245 
962,524535392,439610280,007212275 
1072,487721082,656588410,028516174 
1182,527376722,86008170,110692602 
1293,09780023,052475720,002054309 
13103,739034573,235519140,253527788 
14114,148790813,410540020,545014219 
15122,440516363,578575651,295178953 
16132,634689453,740454941,222717326 
17144,842010163,896852980,893322099 
18154,672935554,048328420,390134073 
19163,733947734,19534990,212891959 
20174,389211044,338315250,002590382 
21     


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Da es vielleicht noch bessere Vorschläge gibt, markiere ich die Frage noch als offen. Das Kapseln in Ausgabe der Parameter in eine VBA-Prozedur ist möglich, jedoch der Algorithmus zur Minimumbestimmung könnte als VBA-Code Zeitprobleme bereiten.
Gruß,
Uwe

Anzeige
AW: Potenzielle Trendlinie
14.08.2007 19:08:33
Marian
Hallo Uwe,
vielen Dank für diese schnelle und ausführliche Lösung.
Die Parameter weichen zwar bei mir von denen in der Grafik ab, aber dies liegt sicher in der "noch" zu geringen Anzahl an Werten.
Ich bevorzuge nun für die Weiterverarbeitung diese Lösung und werde auch die lineare und exponentielle Regression mit dieser Variante lösen.
Gruss,
Marian

AW: Potenzielle Trendlinie
14.08.2007 21:15:00
ingUR
Hallo, Marian,
danke für die Rückmeldung, die mir gelegenheit gibt, gleich auch ein falschgesetztes Attribut hinzuweisen, denn statt «Methode der Summe kleinsten Fehlerquadrate» muß natürlich geschreiben stehen: «Methode der kleinsten Summe der Fehlerquadrate», denn die Summe wird minimiert.
Bezüglcih der PArameterabweichung kann es auch an den Optionseinstellungen des "Solver"s liegen, die für die Iterationsschrittzahl standardmäßig 100 Schritte vorsieht und dann die Minimumsuche abbricht.
Warum Du allerdings nun auch die lineare und exponentielle Regression mit diesem umständlichen Verfahren lösen willst, wo es dafür doch die Zellenformeln gibt, verstehe ich nicht ganz. Bitte beachte, das die Zellenformel über die Möglichkeit verfügt, einen Bereich (Matrix) von Ergebniswerten für Parameter und Kenngrößen zu füllen. Die Ausgabegrößen sind in der Hilfe beschrieben und auch die Matrixgröße, so dass der Bereich markiert und mit der Eingabe des RGP-Formel bzw. RKP-Formel als Matrixforrmel(!) gefüllt werden kann.
Gruß,
Uwe

Anzeige
AW: Potenzielle Trendlinie
14.08.2007 21:46:38
Marian
Hallo Uwe,
danke für deine Hinweise.
Ich möchte dir ein paar Details zu meiner Arbeit nicht vorenthalten: Die Diagramme stellen den Zusammenhang von Aufwand für die Softwareentwicklung und deren Funktionsumfang dar. Zukünftig sollte nun eine Funktion ermittelt werden, die die Aufwandsschätzung möglichst genau wiedergibt. Da jedoch im Moment noch zu wenig Daten vorhanden sind, kann noch nicht gesagt werden, um was für eine Funktion (linear, potenziell, exponentiell) es sich handelt.
Es wird jedoch die Funktion "gewinnen", die das grösste Bestimmtheitsmass hat. Deshalb denke ich ist es wichtig, dass alle Funktionen mit derselben Vorgehensweise berechnet werden. (Auch bei der exponetiellen Regression erhalte ich andere Parameter und eine bessere Angleichung an die vorhandenen Punkte mit der "neuen, etwas umständlicheren Methode".)
Ein weiterer Punkt für diese Entscheidung ist die Nachvollziehbarkeit der Berechnungen, da ich einerseits nicht mehr zuständig sein werde für die weitere Erhebung der Daten und andererseits diese Berechnungen evtl. dann in einer Applikation eingebunden werden. (und zudem: nicht jedermann weiss, wie die Ergebnis-Matrix der RGP, RKP Formel zu interpretieren ist ... brauchte auch meine Zeit dafür ;-) ) ... daher bin ich dir sehr dankbar, für diese doch etwas aufwändigere, jedoch einsichtigere Lösung.
nochmals vielen dank und gruss
Marian
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige