Excel2000 应用案例之三十七

4. 使用函数求解线性回归模型

    在实际预测中，Excel 2000除可以利用回归分析工具外，还可以采用函数来进行预测分析。Excel提供的函数包括：
    LINEST：线性估计最小二乘法
    TREND：趋势分析
    FORECAST：线性趋势预测
    INTERCEPT：线性回归截距分析
    SLOPE：线性回归斜率分析
    LOGEST：曲线估计回归分析
    GROWTH：成长分析
    STEYX：预测值的标准误差
    下面只介绍线性估计最小二乘法LINEST函数的使用方法。
    假设多元线性回归模型为：
   
    式中 为自变量； 为因变量； 为回归系数。利用LINEST函数可以求出上述回归模型，LINEST函数语法格式如下：
    LINEST（因变量Y，自变量X，常数项C，统计量S）
    其中
    因变量Y代表因变量 所在的数据区域；
    自变量X代表自变量 所在的数据区域；
    常数项C是一个逻辑常量，若其值为假FALSE，则强制上述多元线性回归模型中的常数项为0，即 ；若其值为真TRUE（默认值），则常数项 不为0，即根据给定数据进行具体计算；
    统计量S是一个逻辑量，若其值为假FALSE（默认值），则函数只返回各个回归系数，函数返回的数据格式为 ；若其值为真TRUE，则函数返回各个回归系数与统计检验信息，函数返回的数据格式如下表所示。
   

计算结果					数据含义
		…			回归系数
		…			回归系数的标准误差
					复可决系数 、因变量标准误差
					统计量、自由度
					回归平方和 、残差平方和

    对上面的预测某企业经济效益的例子，现在用LINEST函数来建立回归模型。具体操作过程如下：
    在工作表中输入原始数据。
    为输出数据选定足够的区域。若在返回回归系数的同时，也返回有关统计检验的信息，则本例可以选定区域。
    向F2单元格输入公式：“=LINEST(B2:B13,C2:D13,TRUE,TRUE)”。
    按[Ctrl]+[Shift]+[Enter]复合键结束输入。
    这时，函数将计算结果的返回数据存放区域F2:H6中，如图8－16所示。
   

图8－15

    对照上表可知，回归系数分别为 =116.8099， =4.1818， =26.0212，回归系数的标准误差分别为 =9.1517， =0.5541， =1.3856，复可决系数 =0.9976、因变量标准误差 =9.3039， 统计量=1905.7886、自由度 =9，残差平方和 =779.0677，回归平方和 =329941.8490。
    将这些计算结果与由回归工具所得的结果（图8－10）比较可以看出，使用LINEST函数与利用回归工具进行回归分析可得到一致的结论。
    若只需返回各个回归系数，则本例可以指定区域F8:H8；且向F8单元格输入的公式为：“=LINEST(B2:B13,C2:D13)”，按[Ctrl]+[Shift]+[Enter]复合键结束输入。其计算结果见图8－16中的区域F8:H8。