自动产生直线回归依变量的预测值和置信区间的函数

test2000

看到一篇不错的应用,分享大家

在Excel自动产生直线回归依变量的预测值和置信区间的函数



董占山



下面给出一个可以自动计算直线回归，并根据给定的自变量来预测依变量的值，同时给出其置信区间。函数的用法为：

yhat(Y, X, XNew, CI, alpha) 其中，Y是依变量的数据, X为自变量的数据，可以有多列组成, XNew为用来预测依变量的自变量取值, CI为布尔型变量，决定是否计算依变量的置信区间，默认值是true, alpha为统计检验的概率水平，默认值为0.05。

该函数是一个矩阵函数，它返回至少一列多行数据，如果计算置信区间，返回3列多行数据。

Option Explicit

Option Base 1



Function yhat(y, X, XNew, Optional CI As Boolean = True, Optional alpha As Single = 0.05)

' this function can be called from worksheet

' Y - the dependent variable

' X - the independent variable

' XNew - the x values for estiamting y values

' CI - a boolean variable, true for calculating confidence interval of Y

' alpha - a single real, defaut value is 0.05, for T test

'

' Output

'   if calculate the confidence interval, it is a n-by-3 matrix

'      first column is y hat values

'      second column is the lower CI

'      third column is the upper CI

'   otherwise it is n row vector including yhat values

'

    Dim YPred, m, b, n, df, sres, sxx, xbar, t, i, temp

    With WorksheetFunction

        m = .Index(.LinEst(y, X, True, True), 1, 1)

        b = .Index(.LinEst(y, X, True, True), 1, 2)

        n = .Count(X)

        df = .Index(.LinEst(y, X, True, True), 4, 2)

        sres = .Index(.LinEst(y, X, True, True), 3, 2)

        sxx = .DevSq(X)

        xbar = .Average(X)

        t = .TInv(alpha, df)

    End With

    n = WorksheetFunction.Count(XNew)

    If CI Then

        ReDim YPred(n, 3)

        For i = 1 To n

            YPred(i, 1) = m * XNew(i) + b

            temp = t * sres * Sqr(1 / n + (XNew(i) - xbar) ^ 2 / sxx)

            YPred(i, 2) = YPred(i, 1) + temp

            YPred(i, 3) = YPred(i, 1) - temp

        Next i

    Else

        ReDim YPred(n)

        For i = 1 To n

            YPred(i) = m * XNew(i) + b

        Next i

    End If

    yhat = YPred

End Function

joyark

發問連附件好一些