Zum Inhalt springen
Stairway

Regressionsformel direkt in Excel anzeigen

Empfohlene Beiträge

Stairway

Hallo,

 

ich würde gerne in Excel eine simple Regression durchführen (bzw. tue dies im Scatterplot-Diagramm direkt), benötige den Output aber auch als Formel in Excel (d.h. die Formel der Regressionsgeraden). Ich habe das vor einiger Zeit schon gemacht und da hat auch alles funktioniert, heute wirft mir Excel aber ständig ein "#Wert" aus.

 

Kann jemand helfen? Die X- und Y-Datenreihe ist z.B. wie folgt:

 

3,6	26,9
1,8	23,3
1,0	#NV
2,9	2,6
2,8	11,2
3,2	#NV
1,7	#NV
1,5	19,8
1,4	#NV
2,7	19,6
3,8	19,7
0,7	1,4
4,7	19,7
0,9	11,9
3,3	17,6
#NV	#NV
1,3	8,4
3,3	23,5
0,5	3,4
3,4	22,8

 

Beste Grüße

Stair

Diesen Beitrag teilen


Link zum Beitrag
Norbert-54

Bei mir funktioniert es.

Anbei die Excel Datei mit Chart.

 

Viele Grüße

 

Norbert

 

LinReg.xls

Diesen Beitrag teilen


Link zum Beitrag
vanity

Dein Problem sind wohl die nicht definierten Werte (#NV) in der Tabelle. Die Ermittlung der Regressionsgeraden im Diagramm kommt wohl noch gerade damit zurecht (da sie dort aussortiert werden), aber bei Anwendung der zugehörigen Formeln (in OpenOffice Steigung und Achsenabschnitt) scheitert es dann daran.

 

Die nicht vollständig definierten Wertepaare einfach eliminieren, sie tragen nichts zu den gewünschten Werten bei.

Diesen Beitrag teilen


Link zum Beitrag
elcapitano
· bearbeitet von elcapitano

also ich habe die NV() alle rausgeschmissen, dann klappts gut mit der RGP-Funktion.

Hier nur aufpassen, ist ne Matrixformel, d.h. bei Änderungen mit strg+shift+enter bestätigen und beim eingeben immer schon die benötigte Anzahl an Zellen markieren.

 

gruss

Captain

 

Edit:

sorry, Koeffizientenzuordnung war falsch rum...jetzt passts...

Regression.xlsx

Diesen Beitrag teilen


Link zum Beitrag
Stairway

Die nicht vollständig definierten Wertepaare einfach eliminieren, sie tragen nichts zu den gewünschten Werten bei.

 

Das geht leider nicht, es sind 4.000+ Datenpaare über mehrere Quartale.

 

Irgendjemand eine Idee?

Diesen Beitrag teilen


Link zum Beitrag
Chemstudent

Die nicht vollständig definierten Wertepaare einfach eliminieren, sie tragen nichts zu den gewünschten Werten bei.

 

Das geht leider nicht, es sind 4.000+ Datenpaare über mehrere Quartale.

 

Irgendjemand eine Idee?

 

Wieso geht das nicht?

 

Du kannst doch mit Excel und der Filterfunktion schlichtweg alle #NV werte ausblenden.

 

Allerdings hatte ich das Problem mit #NV Werten bisher noch nie. Die Formel der Regressiosngerade konnte - trotz #NV - immer angezeigt werden.

Diesen Beitrag teilen


Link zum Beitrag
Stairway

Durch "Suchen -> Ersetzen" bekomme ich zwar die #NV`s weg, aber Excel kann auch nicht mit leeren Zellen umgehen. Die Wertepaare werden dann nicht mehr aus der gleiche Zeile bezogen (verrutscht um eine Zeile...)

Diesen Beitrag teilen


Link zum Beitrag
ghost_69

Durch "Suchen -> Ersetzen" bekomme ich zwar die #NV`s weg, aber Excel kann auch nicht mit leeren Zellen umgehen. Die Wertepaare werden dann nicht mehr aus der gleiche Zeile bezogen (verrutscht um eine Zeile...)

 

Wie wäre es mit Minitab ?

 

Damit arbeite ich in der Firma

habe ich aber nicht auf meinem Rechner at Home.

 

Das geht ratz fatz und ist einfacher als Excel,

zwei Datenreihen, dazu nur x und y Achse auswählen

und fertig kommt ein Diagramm

und die dazu gehörige Formel.

 

Hier ein Beispiel:

 

post-3119-0-74005200-1368207581_thumb.jpg

 

Man kann natürlich auch noch mehr einstellen

und auch andere Dinge auswerten,

wie z.B. Anova, Boxplot, Pareto, Histogramme,

Warscheinlichkeitsnetz, Einzelwertdiagramm,

verschiedene Regelkarten um Prozesse zu kontrollieren

und um Abweichungen schnell anhand von Graphiken

darzustellen.

 

Man muss nur wissen was man da tut.

 

Ghost_69 :-

Diesen Beitrag teilen


Link zum Beitrag
Stairway

Hi ghost,

 

ich habe auch Erfahrungen mit anderen Statistikprogrammen wie z.B. eViews. Kommt denn Minitab mit dem Problem der n/a`s bzw. leeren Zellen zurecht?

 

Beste Grüße

Diesen Beitrag teilen


Link zum Beitrag
ghost_69
· bearbeitet von ghost_69

Hi ghost,

 

ich habe auch Erfahrungen mit anderen Statistikprogrammen wie z.B. eViews. Kommt denn Minitab mit dem Problem der n/a`s bzw. leeren Zellen zurecht?

 

Beste Grüße

 

Das kann ich gerne mal ab Mittwoch in auf der Arbeit ausprobieren,

ich hatte da noch keine leeren Stellen.

 

Einfach löschen geht da wohl nicht ?

 

Ich weiß das Minitab bei einigen Operationen immer die gleiche Anzahl von Daten in den Spalten braucht

sonst macht das Programm nichts.

 

Für eine Regression braucht man doch immer zwei Punkte

sonst können die in dem Diagramm gar nicht aufgezeigt werden.

 

Das eine hängt doch von dem anderen ab,

ich glaube nicht das Minitab ohne Daten arbeitet.

 

Wenn Du über 4000 Datenpaare hast,

wie kommen denn die leeren Stellen zustande ?

 

Ghost_69 :-

Diesen Beitrag teilen


Link zum Beitrag
Stairway

Hi Ghost,

 

das liegt daran, dass in manchen Jahren schlicht keine Daten vorliegen, die Datenbank (hier Bloomberg) ist nicht ganz vollständig. Es sind leider nicht nur 4.000 Datensätze, sondern 4.000 Datensätze à 40 Quartale, d.h. von Hand kann ich die Leerzellen unmöglich löschen.

 

Beste Grüße

Diesen Beitrag teilen


Link zum Beitrag
Schinzilord

Hi stairway,

Wenn dein Problem weiterbesteht, kann ich es mir gerne mal mit octave/matlab anschauen und dir ein script schreiben. Dann liest es z.b. Eine datei.dat ein (n/a sind egal sein), und dir spuckt es alles Relevante aus. Wenn du Interesse hast, sag Bescheid.

 

Ich ziehe mir die daten automatisch von finance yahoo, nur kürzel und zeitraum, auch gleich 30 Aktien auf einen schlag, dann die ganze weiterverarbeitung automatisch.

Diesen Beitrag teilen


Link zum Beitrag
Schinzilord

Ich hab mal ein bestehendes octave Script leicht modifiziert, es liest dir (fast) beliebige Dateien (z.B. .csv) ein und gibt alpha, beta, R^2 und sonstige Statistiken wie Fehlerintervalle sowohl auf dem Bildschirm als auch in eine Datei zurück.

#NV muss man vorher mit NAN ersetzen, ist aber auch für große Dateien kein Problem. Sonst spielt die größe keine Rolle.

Wenn du z.B. 40 separate Dateien hast mit einer fortlaufenden Nummerierung, kann man diese auch automatisch einlesen (mit bisserl Skriptaufwand).

Diesen Beitrag teilen


Link zum Beitrag
Chemstudent

Durch "Suchen -> Ersetzen" bekomme ich zwar die #NV`s weg, aber Excel kann auch nicht mit leeren Zellen umgehen. Die Wertepaare werden dann nicht mehr aus der gleiche Zeile bezogen (verrutscht um eine Zeile...)

 

Ich meinte nicht "Suchen -> Ersetzen" sondern die Filterfunktion von Excel.

Markiere die gewünschte Spalte, und klicke auf "Sortieren und Filtern" (oben rechts, Excel 2007). Anschließend "Filtern" auswählen.

In der ersten Zelle der Spalte sollte nun ein kleiner Pfeil auftauchen.

Klicke darauf und du hast die möglichkeit einen Filter zu setzen. In deinem Fall also einfach den Haken bei #NV raus.

 

Lediglich in der ersten Zeile steht nun noch ggf. ein NV, was leicht zu korrigieren ist.

Die so gefilterten Daten lassen sich einfach kopieren, wobei jedoch dann die ausgeblendeten Daten nicht mit kopiert werden, man sie also "endgültig" wegbekommt.

 

post-8776-0-37701800-1368439845_thumb.png

 

post-8776-0-10612600-1368439854_thumb.png

 

post-8776-0-92802200-1368439858_thumb.png

Diesen Beitrag teilen


Link zum Beitrag
ghost_69

Hallo Stairway

 

Ich habe die Daten in das Programm kopiert, das schaut so aus:

 

post-3119-0-03509700-1368628720_thumb.jpg

 

Das Programm hat sofort die fehlenden Fehler bemerkt.

 

Dann bin ich auf Regression gegangen und habe mit den Diagrammen ausgewält:

 

post-3119-0-86284000-1368628714_thumb.jpg

 

post-3119-0-15482200-1368628711_thumb.jpg

 

Also das Programm meldet 15 Fälle die verwendet werden können

und 5 Fälle die fehlende Werte enthalten und die nicht mit einbezogen wurden.

 

die Formel dafür ist:

1 = 1,05 + 0,0925 * 2

 

und 36,6% Warscheinlichkeit um die Formel auf andere Werte zu berechnen,

also eher gering.

 

und dann noch die umgekehrte Regression:

 

post-3119-0-44659700-1368628704_thumb.jpg

 

post-3119-0-83153800-1368628699_thumb.jpg

 

Ghost_69 :-

Diesen Beitrag teilen


Link zum Beitrag

Erstelle ein Benutzerkonto oder melde dich an, um zu kommentieren

Du musst ein Benutzerkonto haben, um einen Kommentar verfassen zu können

Benutzerkonto erstellen

Neues Benutzerkonto für unsere Community erstellen. Es ist einfach!

Neues Benutzerkonto erstellen

Anmelden

Du hast bereits ein Benutzerkonto? Melde dich hier an.

Jetzt anmelden

×
×
  • Neu erstellen...