Office: (Office 2013) Regressionsgerade berechnen bei leeren Zellen

Hallo,

ich möchte Regressionsgeraden berechnen für eine ganze Reihe von Datensätzen. Jedoch funktioniert RGP nicht, da manche Zellen keine Daten enthälten, also so in der Art, wie diese Tabelle, nur umfangreicher.


Land______2008____________2009____________2010 ________ 2011
Land 1____0,469712472_____0,462296384_____0,466533609___0,469245511
Land 2____0,480881756_____0,307048284_____0,317450497_____________
Land 3___________________0,318697377___________________0,32020274


In einer weiteren Spalte hinter 2011 sollte dann die Steigung berechnet werden für die Regressionsgerade des jeweiligen Landes.

Wir haben da jetzt einige Stunden gegoogelt und die Excel-Hilfe befragt, ohne eine Lösung zu finden. :-( Die Felder mit Null zu befüllen oder ähnliches wäre matematisch falsch, das alles manuell zurecht zu schieben geht angesicht der Menge an Datensätzen nicht.

Grüße

F-Projekt

P. S.: Das Problem wurde unter http://www. office-loesung.de /ftopic142759_0_0_asc.php beschreiben, ohne dass eine Lösung gefunden wurde.

:)

 

Hier noch ein Bsp., wie Daten aussehen können. abfall2.png

Dann funktioniert der RGP-Befehl nicht mehr.

 

Hallo F-Projekt,

die vorbereitete Formel von Excel "RGP()" sieht nicht vor mit Lücken oder gar Fehlerwerten zu arbeiten. Daher muss die Formel selber gebastelt werden.

Da ich mich mit Regressionsgeraden nicht auskenne, beschreibe bitte ganz genau (für einen Laien) was Du tatsächlich berechnet haben willst (am besten gib mir schon eine funktionierende Formel, die halt nur nicht bei Deinen Lücken funktioniert).
Dann versuche ich Dir Deine Formel lückentauglich zu machen.

In der Excelhilfe finde ich als Erklärung "y = mx + b". Nur wenn ich mir Deine Aufgabe anschaue finde ich nur x. Aber wo ist m und b? Bitte erkläre mir so genau wie möglich, was Du berechnet haben möchtest!!! Je besser Du das erklärst desto besser kann ich Dir helfen. Denn ich bin mir sicher, dass man das auch mit einer komplizierten eigenen Formel lösen kann.

LG Ingo

P.S.: Übrigens Deinen Link kann ich nicht öffnen.

 

Hallo Ingo,

danke für die Antwort. Links werden hier im Forum nicht angezeigt - ich tippe mal drauf, um Spamern den Spaß zu nehmen.

RGP gibt, soweit ich das verstanden habe die Steigung der Gerade aus, also das m in deiner Formel. Alles andere ist nicht weiter von Bedeutung am Ende. x sind die Jahre, y die zugehörigen Werte.

Man kann auch ein ganzes Array an div. Werten ausgeben. https://support.office.com/de-ch/art...a-fa7abf772b6d

Über Formeln wie die in der Anleitung von MS angegebene

"=RGP(E2:E12;A2*biggrin.gif*12;WAHR;WAHR)"
als Bsp. sind wir nicht hinaus gekommen.

Das entscheidene ist, dass ein neues viurtuelles Array gebildet werden müsste, in dem nacheinander weg nur die x-y-Wertpaare drin sind, bei denen es für y eine Angabe gibt. Und das müsste man dann an den RGP-Befehl übergeben an Stelle von "E2:E12;A2*biggrin.gif*12" in dem Bsp.

Grüße

F-Projekt

 

Hallo F-Projekt,

Deine Formelvorgabe:

PHP:

         =RGP(E2:E12;A2:D12;WAHR;WAHR)


 

Hallo F-Projekt,

ich habe da mal etwas vorbereitet.
In Spalte O habe ich eine RGP Formel so gebaut, wie ich glaube, dass Du sie benötigst.
In Spalte P habe ich die RGP Formel selber gebaut und das so, dass fehlende Eingaben in den Datenspalten C bis N dann auch unberücksichtigt bleiben.

Ist es das, was Du gesucht hast?

LG Ingo

 

Hallo Ingo,

danke für die Mühen.

Also, wenn ich ein Bsp. nehme, in dem alles ausgefüllt ist, reicht die Formel

=RGP(C2:N2;$C$1:$N$1;WAHR)

Da kommt der gleiche Wert für die Steigung raus, wie wenn ich eine Grafik erstelle und mir dort die Regressionsgerade anzeigen lasse. Das funktioniert auch, wenn zwischendrin eine ganze Spalte fehlt, also z. B. das Jahr 2000 nicht vorkommt. Wenn eine Zelle leer ist, klappt es schon nicht mehr. In der Grafik wird aber weiterhin eine Regressionsgerade angezeigt.

Wenn ich in deiner Tabelle zahlen lösche kann ich etwas berechnen und der Wert stimmt überein mit dem Wert der Regressionsgerade in der Grafik zu der entsprechenden Zeile.

Für alles eine Datei, die ähnlich ist wie die Original-Daten. mit allen Fällen (a) vorne und hinten fehlen Daten b) vorne fehlen Daten c) hinten fehlen Daten d) mitten drin fehlen Daten)

Mappe1.xlsx

Wenn ich dort in M2

Code:

mit STRG + SHIFT+ENTER eintrage, kommt aber etwas völlig sinnfreies raus, nämlich immer der gleiche Wert für mehrere Zeilen. Auch ein grafisch erstelltes Diagramm ergibt keinen Sinn.
Mappe2.xlsx

Vielleicht habe ich auch nur irgendwo ein Denkfehler, aber aktuell bringt das jetzt gerade nicht so arg viel, was ich da gemacht habe. :-(

Grüße

F-Projekt

 

Hallo F-Projekt,

alle Deine Fälle a bis d deckt meine Formel ab.
Je weniger Werte, desto ungenauer wird die Steigung, aber das ist exakt übereinstimmend mit RGP().
Und wenn nur ein oder kein Wert vorhanden ist, kann meine Formel natürlich nicht funktionieren. Macht ja auch keinen Sinn. RGP() kann das auch nicht.

Für mich ist die Aufgabe gelöst, bist Du auch der Meinung?

LG Ingo

 

Hallo Ingo,

jetzt hat sich meine Fortführung des Posts mit deiner Antwort überschnitten. Ich hab's noch nicht auf den eigenen Fall übertragen können ...

Grüße und Danke für die Mühen.

Grüße

F-Projekt

 

Hallo F-Projekt,

meine Formel ist 3-zeilig! Du hast aber nur die 3. Zeile in Deine Tabelle übertragen. Außerdem hast Du die Spalten nicht angepasst.

Formel für Mappe1.xlsx M2:

PHP:

         =SUMME(  WENN(ISTZAHL($B2:$L2);(SUMME(WENN(ISTZAHL($B2:$L2);$B$1:$L$1))/ANZAHL($B2:$L2))-($B$1:$L$1))*
(WENN(ISTZAHL($B2:$L2);(SUMME(                         $B2:$L2)                   /ANZAHL($B2:$L2))-($B2:$L2))))/
SUMME(  WENN(ISTZAHL($B2:$L2);(SUMME(WENN(ISTZAHL($B2:$L2);$B$1:$L$1))/ANZAHL($B2:$L2))-($B$1:$L$1))^2)


 

Hallo Ingo,

interessant. Wusste ich nicht, dass es mehrzeilige Formeln gibt. Jetzt kommt etwas sinnvolleres raus (zumindestens sieht es so aus). Wirklich Danke für deine Mühen.

 

Hallo F-Projekt,

dann brauchst Du nun kein WENNFEHLER mehr drumherum zu bauen.
M2:

PHP:

         =WENN(ANZAHL($B2:$L2)<5;"Achtung weniger als 5 Werte";
SUMME(  WENN(ISTZAHL($B2:$L2);(SUMME(WENN(ISTZAHL($B2:$L2);$B$1:$L$1))/ANZAHL($B2:$L2))-($B$1:$L$1))*
(WENN(ISTZAHL($B2:$L2);(SUMME(                         $B2:$L2)                   /ANZAHL($B2:$L2))-($B2:$L2))))/
SUMME(  WENN(ISTZAHL($B2:$L2);(SUMME(WENN(ISTZAHL($B2:$L2);$B$1:$L$1))/ANZAHL($B2:$L2))-($B$1:$L$1))^2))


 

Hallo Ingo,

das sieht gut aus! An die Bewertungsfunktion hatte ich nicht mehr gedacht, weil ich das aus anderen Foren nicht kenne. Da schreib ich immer Danke in einen Beitrag. Auch dir Danke für die Mühen!

LG

F-Projekt

 

Hallo Ingo,

ich habe das stichprobenartig mal getestet, ob damit die gleichen Werte raus kommen wie, wenn man eine Grafik erstellt für die Spalten B-J und eine Ausgleichsgerade zeichnen lässt und die Formel sich anzeigen lässt in der Grafik.
Wenn man Zeile 99 nimmt, dann die Werte (ca. -15,6 berechnet vs. ca. -9,4) gar nicht mehr überein. Wenn nur mal eine Spalte fehlt, kommt auf beiden Wegen der gleiche Wert raus, wenn man die Grafik über den Bereich erstellte der von der ersten bis zur letzten befüllten Zelle geht in der jeweiligen Zeile.

Eigentlich sollten die Wege doch immer den gleichen Wert ergeben. Naja, das ist dann wohl eher eine mathematische Fragestellung.

Grüße

F-Projekt

 

Hallo F-Projekt,

RGP rechnet über die kleinsten Quadrate die Abweichung.
Wenn Du das grafisch ermittelst und mit RGP vergleichst, sind Unterschiede logisch.

LG Ingo

P.S.: Danke für die positive Bewertung!