AW: Berechnung von linearen Trends
07.09.2006 13:12:56
linearen
Hallo Andreas,
ist ein Code den ich mir mal gebastelt hatte, um lineare Trends auszurechnen wie es das Diagramm auch macht.
Sub Regressionsanalyse()
On Error GoTo fehlerweg
ActiveWorkbook.Sheets(1).Select
n = Cells(Cells(2, 1).End(xlDown).Row, 1).Row - 1
ReDim xarray(n)
ReDim yarray(n)
For j = 1 To n
xarray(j) = Cells(j + 1, 1).Value
yarray(j) = Cells(j + 1, 2).Value
mwx = mwx + xarray(j)
mwy = mwy + yarray(j)
Next j
mwx = mwx / n
mwy = mwy / n
stxy = 0
stx = 0
sty = 0
For j = 1 To n
stxy = stxy + ((xarray(j) - mwx) * (yarray(j) - mwy))
stx = stx + ((xarray(j) - mwx) * (xarray(j) - mwx))
sty = sty + ((yarray(j) - mwy) * (yarray(j) - mwy))
Next j
stx = Sqr(Abs(stx * (1 / (n - 1))))
sty = Sqr(Abs(sty * (1 / (n - 1))))
stxy = stxy * (1 / (n - 1))
sx = 0
sy = 0
sxy = 0
sx2 = 0
sy2 = 0
For j = 1 To n
tx = xarray(j)
ty = yarray(j)
sx = sx + tx
sy = sy + ty
sxy = sxy + tx * ty
sx2 = sx2 + tx * tx
sy2 = sy2 + ty * ty
Next j
Cells(13, 6) = sx
Cells(14, 6) = sy
Cells(15, 6) = sxy
Cells(16, 6) = sx2
Cells(17, 6) = sy2
Cells(18, 6) = n
B = (n * sxy - sx * sy) / (n * sx2 - sx * sx)
A = (sy / n) - (B * (sx / n))
R = (n * sxy - sx * sy) / Sqr(Abs(n * sx2 - sx * sx) * Abs(n * sy2 - sy * sy))
R2 = R * R
K = 1 / (n - 1) * ((sxy - (n * (sx / n) * (sy / n))))
Cells(20, 6) = B
Cells(21, 6) = A
Cells(22, 6) = R
Cells(23, 6) = R2
Cells(24, 6) = K
Range("F20:F24").Select
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Interior.ColorIndex = 4
Exit Sub
fehlerweg:
MsgBox "Falsche Eingabe der Parameter!" & vbLf & vbLf & "Bitte überprüfen Sie ihre Eingaben.", vbExclamation, "Fehlerdialog", 0, 0
End Sub
Gruß Sylvio