Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1532to1536
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
Inhaltsverzeichnis

Lineare Regression mit gewichteten Fehlerquadraten

Lineare Regression mit gewichteten Fehlerquadraten
17.01.2017 12:26:43
Bernd
Lineare Regression mit gewichteten Fehlerquadraten
Hallo Forum,
nun möchte ich mich an die Programmierung von Funktionen zum ersten Mal wagen und benötige bitte etwas Hilfe von euch.
Die Funktion soll die Steigung a der Geraden und den Achsenabschnitt b berechnen mit Hilfe des Solvers  dies bedeutet, dass die Funktion eventuell den Solver enthält. Wenn dies ein K.O Kriterium ist für die per VBA zu programmierende Funktion, dann benötige ich ein Makro.
Mir ist klar, dass die Funktion jedes Mal neu rechnet, wenn ich in der Tabelle irgendetwas verändere.
Daher würde ich erst am Ende die Funktion einbauen in meine Tabelle und das Ganze dann durchrechnen lassen.
Ich möchte eine Funktion programmieren für eine Lineare Regression mit (im Beispiel exponentiell) gewichteten Fehlerquadraten.
In der beigefügten Datei ist ein komplettes Vergleichsbeispiel gezeigt. Ich möchte ein Funktion programmieren für die Berechnungen in den Spalten G bis K.
Dabei soll die Anzahl der Funktionswerte variabel sein. Die Variable nenne ich einmal N für die Anzahl der Funktionswerte.
Eine weitere Variable ist der Gewichtungsfaktor alfa für die exponentielle Gewichtung der Fehlerquadrate. Im Beispiel ist alfa = 0,5 in der Zelle I4.
Jetzt muss ich noch erklären, warum ich eine Funktion möchte. Das beigefügte Beispieltabellenblatt ist ein Beispiel aus einer langen Datenreihe.
Die x-Werte laufen in der Datenreihe von 1 bis z.B. 5000 und befinden sich in einer Spalte. Die y Werte der Funktion, durch die eine Regressionsgerade gelegt werden soll, befinden sich rechts daneben in der Nachbarspalte.
Diese beiden Spalten müssen übergeben werden in die Funktion (Array-Felde?, Achtung, damit habe ich auch keine Erfahrung). Dabei ist es wichtig, zu beachten, dass die Steigung a der Geradengleichung als auch der Achsenabschnitt b der Geradengleichung in der gleichen Zeile ausgegeben werden soll, in der auch der letzte Funktionswert steht.
Im beigefügten Beispiel sollte also z.B. die Steigung der Geraden in der Zelle L15 und der Achsenabschnitt in der Zelle M15 ausgegeben werden.
https://www.herber.de/bbs/user/110639.xlsx

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: warum nicht die normale xl-Fnkt
17.01.2017 14:05:02
Fennek
Hallo,
xl bietet sowohl bei den Funktionen "RGN()" als auch im mitgelieferten "Analyse-PAck" linerare Regression an. Weshalb dann den Solver?
mfg
(andere Programm-Pakete, z.B. SPSS sind wesentlich besser geeignet)
AW: warum nicht die normale xl-Fnkt
17.01.2017 16:33:55
Bernd
Hallo Fennek,
wenn du meinen Beitrag genau liest, wirst du feststellen, dass ich die RGN-Funktion hier nicht nutzen kann, weil diese gerade nicht eine exponentielle Gewichtung der einzelnen Fehlerquadrate erlaubt.
Vielleicht bin ich auch nicht tief genug in Excel, dann wäre ich dir dankbar, wenn du mir in meiner Beispieldatei zeigst, wie du das Problem lösen würdest.
Beste Grüße
Bernd
Anzeige
Recherche
17.01.2017 17:44:21
Michael
Hi,
suche: excel lineare regression gewichtet
und finde: http://www.spreadsheet-validierung.de/excel-gewichtete-lineare-regression/
Ich habe die Datei mal runtergeladen, den Bereich und das Makro in eine leere Excel-Datei kopiert (wegen des neuen Formats), den Button neu "verdrahtet" - und es läuft brav.
Allerdings kommt in B19 ein schlechterer Wert als in der Original-Datei raus, was daran liegen mag:
Userbild
Um die Simplex-Methode zu verwenden, habe ich zu wenig Ahnung von der Sache (die geht nicht vom Fleck weg).
Schöne Grüße,
Michael
Anzeige
AW: Recherche
17.01.2017 18:27:06
Bernd
Hallo Michael,
den Link kannte ich schon. Das ist aber ein normales Makro und keine Funktion, die ich eigentlich möchte. Die Daten per Makro aus der großen Datenbank in ein solches Tabellenblatt kopieren und dann den Solver laufen lassen und die Solverwerte für a und b dann wiederum kopieren - das kann ich auch ohne Hilfe.
Wie gesagt, ich möchte lernen, wie man eine Funktion programmiert.
Dennoch vielen Dank für deine Mühe!
Beste Grüße
Bernd
AW: Recherche
17.01.2017 19:21:04
Michael
Hi,
entscheidend ist also der Abschnitt
Die x-Werte [] von 1 bis z.B. 5000 in einer Spalte. Die y Werte [] rechts daneben. [] Diese beiden Spalten müssen übergeben werden in die Funktion (Array-Felde?, Achtung, damit habe ich auch keine Erfahrung). Dabei ist es wichtig, zu beachten, dass die Steigung a der Geradengleichung als auch der Achsenabschnitt b der Geradengleichung in der gleichen Zeile ausgegeben werden soll, in der auch der letzte Funktionswert steht.
Ich mag vom Fleck weg nicht beurteilen, ob der Solver *grundsätzlich* überhaupt innerhalb einer Function verwendbar ist.
Wenn Du aber nicht weißt, was ein Array ist, wird es schwierig, erst Recht, als die "normalen" Funktionen nur in *einer* Zelle einen Wert zurückgeben, Du aber *zwei* Werte gleichzeitig errechnen willst (der eine geht ja nicht ohne den anderen), so daß Du eine "Array-Funktion" schreiben mußt, die beide Ergebnisse in beide Zellen gleichzeitig "zurückgibt".
Alles machbar, aber (für Dich) nicht in 5 Minuten erledigt.
Recherche Array: http://www.online-excel.de/excel/singsel_vba.php?f=152
Recherche Matrixformel: http://www.online-excel.de/excel/singsel.php?f=26
Probiere mal was Einfaches:
Function mal_simpel(x, y)
Dim a, b, i&, j&
Dim ergebnis#(1 To 1, 1 To 2)
'Stop
a = x
If IsArray(a) Then
For i = LBound(a) To UBound(a)
For j = LBound(a, 2) To UBound(a, 2)
ergebnis(1, 1) = ergebnis(1, 1) + a(i, j)
Next
Next
Else
ergebnis(1, 1) = a
End If
b = y
If IsArray(b) Then
For i = LBound(b) To UBound(b)
For j = LBound(b, 2) To UBound(b, 2)
ergebnis(1, 2) = ergebnis(1, 2) + b(i, j)
Next
Next
Else
ergebnis(1, 2) = b
End If
mal_simpel = ergebnis
End Function

das mußt Du als "Matrixformel" eingeben!
Die Ausgabe erfolgt standardmäßig in zwei Zellen nebeneinander, falls Du es untereinander benötigst, nimmst Du zusätzlich MTrans, siehe auch Datei: https://www.herber.de/bbs/user/110654.xlsm
Viel Spaß,
Michael
Anzeige
AW: Recherche
17.01.2017 22:12:50
Bernd
Hallo Michael,
vielen Dank für deine Hinweise, aber ich muss mir das die nächsten Tage mal in Ruhe ansehen.
Es geht aber auf jeden Fall in die richtige Richtung, in die ich will.
Deine Funktion gibt mir als Werte die Funktionswerte wieder aus, ist das richtig?
Sprich dein Beispiel hat noch nichts mit der Regression zu tun, die programmiert werden soll?
BG
Bernd
AW: Recherche
19.01.2017 05:34:03
Michael
Hi,
was heißt die Funktionswerte "wieder aus"?
Nein, es liest die angegebenen zwei Bereiche ein, summiert jeden einzeln und gibt die beiden Summen in zwei Zellen nebeneinander aus.
Naja, es ist halt ein Grundgerüst für eine "Function", was die Ein- und Ausgabe relativ zum Tabellenblatt angeht.
Am leichtesten ist es nachvollziehbar, wenn Du das Kommentarzeichen vor dem Stop wegmachst; dann springt Excel bei jeder Neuberechnung der Formel in den Code, so daß Du ihn mit F8 zeilenweise durchgehen kannst.
Schöne Grüße,
Michael
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge