Lineare Regression mit VBA
|
Da alle Kalkulations-Programme die Funktionen
↑ STEIGUNG, ACHSENABSCHNITT,
TREND anbieten, besteht normalerweise kein Bedarf nach VBA-Funktionen
zum gleichen Zweck.
Das ist nur dann sinnvoll, wenn die Funktionen innerhalb
von VBA verwendet werden sollen. Das Beispiel zeigt eine Version, die zu
Demo-Zwecken sowohl als Excel-'Benutzerdefinierte Funktion', als auch innerhalb
von VBA verwendet werden kann.
|
●
Jede Gerade (auch die Ausgleichsgerade) benötigt 2 Werte zu ihrer
Beschreibung.
●
Da VBA-Funktionen nur einen Wert an Excel zurückgeben können,
werden die beiden Hilfs-Funktionen vba_linreg_k, vba_linreg_d
als Excel-'Benutzerdefinierte (Hilfs)-Funktionen' verwendet.
●
Das Programm vba_calc_linreg führt die eigentliche
Berechnung durch und kann sowohl von anderen VBA-Programmen als auch von Excel
(mit den genannten Hilfs-Funktionen) verwendet werden.
|
Excel-Interface
Die rechts vorgestellten Funktionen dienen als Excel-Interface, d.h. zur
Übergabe der bekannten Datenpaare von Excel an VBA und zur Übergabe
der Ergebnisse (k und d der Ausgleichsgeraden) an Excel.
Man kann alle rechts angeführten Programme weglassen, wenn die
lineare Regression nur innerhalb von VBA verwendet wird.
•
Die Namen aller von außen (Excel) sichtbaren
Variablen und Programme erhalten zur eindeutigen Unterscheidung das
Prefix vba_linreg
►
Die Funktionen vba_linreg_k, vba_linreg_d können
als Excel Funktionen verwendet werden. Sie verwenden die gleichen Argumente
(Y-Bereich, X-Bereich) wie die entsprechenden Standard-Funktionen
STEIGUNG, ACHSENABSCHNITT und sollten auch die gleichen Ergebnisse liefern.
Beide Funktionen verwenden die interne (private) Funktion
vba_xy_data zur Übergabe der Datenpaare und ↓
vba_calc_linreg zur Berechnung der Ausgleichsgeraden.
►
Das private Programm vba_xy_data speichert die aus
Excel übergebenen Datenpaare in den globalen
Arrays xa,ya
|
Function vba_linreg_k(range_y As Range, range_x As Range) As Double
Call vba_xy_data(range_x, range_y)
Call vba_calc_linreg
vba_linreg_k = linreg_k
End Function
Function vba_linreg_d(range_y As Range, range_x As Range) As Double
Call vba_xy_data(range_x, range_y)
Call vba_calc_linreg
vba_linreg_d = linreg_d
End Function
Private Sub vba_xy_data(xr As Range, yr As Range)
Dim i As Integer
linreg_n = xr.Count
If yr.Count < linreg_n Then linreg_n = yr.Count
ReDim linreg_xa(linreg_n)
ReDim linreg_ya(linreg_n)
For i = 1 To linreg_n
linreg_xa(i) = xr(i).Value
linreg_ya(i) = yr(i).Value
Next
End Sub
|
Der rechts vorgestellte Code genügt für die VBA-interne Anwendung.
►
Alle Daten sind in globalen Variablen und Arrays organisiert, z.B. die
bekannten DatenPaare (xa,ya), die Ausgleichsgerade
(k,d) und die Mittelwerte (xm,ym)
Vor der VBA-internen Anwendung müssen die bekannten Datenpaare in die
Arrays xa,ya eingetragen werden.
►
Das private Programm vba_calc_linreg führt die
Berechnung der Ausgleichsgeraden durch. Die Ergebnisse
(Mittelwerte, k,d) werden in die globalen Variablen
eingetragen und daher nicht zurückgegeben.
►
Für reine VBA-interne Anwendung kann man alternativ ein
Array zur Rückgabe
verwenden. Diese Version ist jedoch nicht Excel-kompatibel.
Fortgeschrittene AnwenderInnen können das Beispiel so modifizieren,
dass man keine globalen Variablen benötigt.
●
Ein Vorteil der VBA-Anwendung ist, dass man die Ausgleichsgerade nur 1mal
berechnen muss, nicht wie für Excel 2mal (getrennt für k und d).
|
Dim linreg_n As Integer
Dim linreg_xa(), linreg_ya() As Double
Dim linreg_k, linreg_d, linreg_xm, linreg_ym As Double
Private Sub vba_calc_linreg()
Dim i As Integer
Dim sx, sy, sxy, sxx As Double
sx = 0
sy = 0
sxy = 0
sxx = 0
For i = 1 To linreg_n
sx = sx + linreg_xa(i)
sy = sy + linreg_ya(i)
sxy = sxy + linreg_xa(i) * linreg_ya(i)
sxx = sxx + linreg_xa(i) * linreg_xa(i)
Next i
linreg_xm = sx / linreg_n
linreg_ym = sy / linreg_n
linreg_k = (sxy - linreg_n * linreg_xm * linreg_ym) / (sxx - linreg_n * linreg_xm * linreg_xm)
linreg_d = linreg_ym - linreg_k * linreg_xm
End Sub
|
Algorithmen
