利用Excel进行线性回归分析

1、文档内容1.用电子表格做线性回归分析2.用电子表格进行多元线性回归分析1.用电子表格做线性回归分析第一步是输入数据以连续10年最大积雪深度与灌溉面积的关系数据为例。输入结果见下图(图1)。图1第二步是制作散点图如图2所示，选择数据(包括自变量和因变量)，点击“图表向导”图标；或者在“插入”菜单中打开“图表(h)”。图表向导的图标是。当数据被选中时，它变成蓝色(图2)。图2点击“图表向导”后，弹出如下对话框(图3):图3在左栏选择“XY散点图”，点击“完成”按钮，散点图的原始形式将立即出现(图4):图4第三步是返回观察散点图，判断点和列的分布是否呈线性趋势。只有当数据具有线性分布特征时，才能采用

2、线性回归分析。从图中可以看出，本例中的数据具有线性分布趋势，可以进行线性回归。回归步骤如下：1.首先，打开“工具”下拉菜单，查看数据分析选项(参见图5):图5用鼠标双击数据分析选项，弹出数据分析对话框(图6):图62.然后，选择“回归”并确认，弹出如下选项列表(图7):图7进行以下选择：x和y值的输入区域(B1:B11、C1:C11)、符号、置信度(95%)、新工作表组、残差和线性拟合图(图8-1)。或者：x和y值的输入区域(B2:B11，C2:C11)，置信度(95%)，新工作表组，残差，线性拟合图(图8-2)。注意：当选择数据标志和不选择数据标志时，x和y值的输入区域不同：前者包括数据标志

3、；最大积雪深度x(米)灌溉面积y(千亩)后者不包括。请注意这一点(图8)。图8-1包括数据“标志”图8-2不包括数据“标志”3.之后，确认并获得回归结果(图9)。图9线性回归结果4.最后，阅读回归结果如下：拦截：坡度：相关系数：测量系数：f值：t值：标准偏差(标准误差):回归平方和：剩余方块的总和：y的误差平方和是平方和：5.建立回归模型并检验结果该模型是：至于检验，R、R2、F和T可以直接从回归结果中读出。实际上，测试通过了。有了r值，f值和t值都可以计算。f值的公式和结果如下：这显然与表中的结果相同。t值的公式和结果如下：回归结果中给出了残差(图10)，从中可以计算出标准偏差。首先，对残差

4、进行平方，然后对残差平方求和，因此标准偏差为因此.图10y预测值及其对应的残差此外，可以计算DW值(见图11)，并且计算公式和结果如下拿着，(显然)，查一下表。显然，DW=0.751，这表明序列之间存在正相关，并且预测结果令人怀疑。图11通过残差计算数据仓库值用电子表格快速估算模型的方法；2.用鼠标指向图4中的数据点列，右键单击，出现以下选择菜单(图12):图122.点击添加趋势线，弹出如下选择框(图13):图133.在“分析类型”中选择“线性”，然后打开菜单(图14):图144.在选择框中选择“显示公式(e)”和“显示r平方值”(如图14所示)，确认后，立即得到如下回归结果(图15):图15

5、在图15中，给出了回归模型和相应的测量系数，即拟合优度。顺便提及，残差分析：如果在图8中选择了“残差图(d)”，则可以自动生成残差图(图12)。图16原则上，回归分析要求残差分布没有趋势。如果趋势线被添加到图表中，趋势线应该平行于X轴，并且测量系数非常小。事实上，添加趋势线的结果如下(图17):图17可以看出，残差分布图基本满足回归分析的要求。预测分析虽然DW测试似乎失败了，但这里使用的变量相关性分析不同于纯粹的时间序列分析(时间序列分析应该把时间作为自变量)。从残差图来看，模型的序列似乎没有很强的自相关性，因为残差分布是相当随机的。因此，仍有可能进行预测分析。现在假设有人在1981年测量了2

6、7.5米的最大积雪深度。他是如何预测那一年的灌溉面积的？对于Excel 2000，给出了以下步骤：2.在图9所示的回归结果中，复制回归参数(包括截距和斜率)，然后将它们粘贴到图1所示的原始数据附近；1981年观测到的最大积雪深度为27.5米，是在1980年以后记录的(图18)。图182.将光标放在图18所示的D2单元格中，按等号“=”，单击F2单元格(对应截距a=2.356)，按F4，按加号 ，单击F3单元格(对应斜率b=1.812)，按F4，按乘法符号“*”，然后单击B2单元格(对应自变量x1)图193.将十字光标标记在D2单元格的右下角。当粗十字变成细十字时，按住鼠标左键并向下拉，每年灌溉

7、面积的计算值会立即出现，其中对应的D12单元格为1981年52.212也就是说，我们需要的预测数据，也就是数千英亩(图20)。图204.此外，如果可以测量1982年及以后年份的数据，将它们输入单元格B13及以下，预测值将立即出现在单元格D13及以下。例如，假设1982年的最大积雪深度为米，它可以计算为数千英亩；1983年，最大积雪深度为1000亩(图21)。图21预测结果(1981-1983)最后，让我们思考一下为什么在这个例子中，数据仓库测试可能对这个问题无效。2.用电子表格进行多元线性回归分析【例】分析了某省工业产值、农业产值和固定资产投资对交通产值的影响。Excel 2000的运算方法类

8、似于线性回归分析：步骤1:输入数据(图1)。图1输入的原始数据第二步是数据分析1.沿着主菜单中的“工具(t)”“数据分析(D)”路径打开“数据分析”对话框，选择“回归”，然后选择“确定”，弹出“回归”分析对话框，对话框的选项与一维线性回归基本相同(图2)。以下仅描述x值的设置方法：首先，将光标放在“X值输入区(X)”(图2)；然后，从图1至E19所示的单元C1，所有用作独立变量的数据与标志一起被选择。此时，“$ C $ 1: $ E $ 19”立即出现在“x值输入区(x)”的空白列中。当然，您也可以直接在“x值输入区(x)”的空白栏中输入“$ c $”。注意：与线性回归的设置一样，这里的数据范

9、围包括数据标记：工业产值x1农业产值x2固定资产投资x3运输产值y因此，必须在对话框中选择标志项(图3)。如果没有“标志”项，则“$C$2:$E$19”应在“X值输入区(X)”的空白列中，“F$2:$F$19”应在“Y值输入区(Y)”的空白列中。否则，计算结果将不准确。图2除X值之外的设置图3设置后的对话框(包括数据标志)2.完成上述设置后，立即确认并给出回归结果。由于在这里的“输出选项”中选择了“新工作表组”(图3)，输出结果现在出现在新创建的工作表中(图4)。它可以从图4中的“汇总输出”中读取：，根据残差数据，计算离散小波值并不困难，其方法与线性回归完全相同。根据回归系数，可以建立以下多元线性模型：由于X2回归系数b2的符号与事实不符，B2的T检验值为负，B2的绝对值很小，可以判断自变量之间可能存在多重共线性。图4第一次回归的结果3.剔除异常变量x2(农业产值)，用剩余的自变量x1和x3用Y回归(图5)。回归步骤只不过是重复上述过程(参见图6，注意这里没有数据“标志”)，最后给出回归结果(图7)。图5剔除异常变量“农业产值(x2)”图6回归对话框的设置(不包括数据标志)回归