编程技术、软件应用与系统模拟

(Programming, Applicaiton and Simulation)


		本站目录
		首页 ASP/Access/IIS DELPHI/PASCAL PASCAL高级编程 C语言编程实例 WORD Excel MATLAB MINITAB讲座 Windows DOS SAS 生物系统模拟土壤水分剖析器其他


		镜像站点
		主站北美镜象站欧洲镜象站(1) 欧洲镜象站(2)

座右铭:
只做有益人类的事
不做有害人类的事

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

董占山

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