利用Excel进行线性回归的分析报告.docx

文档内容1. 利用 Excel 进行一元线性回归分析2. 利用 Excel 进行多元线性回归分析1. 利用 Excel 进行一元线性回归分析第一步，录入数据以连续 10 年最大积雪深度和灌溉面积关系数据为例予以说明录入结果见下图（图 1）图 1第二步，作散点图（H）”图表向导的图标为选中数据后，数据变为蓝色如图 2 所示，选中数据（包括自变量和因变量），点击“ 图表向导” 图标； 或者在“ 插入” 菜单中打开“ 图表（图 2）文档大全图 2点击“图表向导”以后，弹出如下对话框（图 3）：图 3在左边一栏中选中“ XY 散点图”，点击“完成”按钮，立即出现散点图的原始形式（图 4）：60504030灌溉面积y(千亩)201000102030灌溉面积y(千亩)图 4第三步，回归观察散点图，判断点列分布是否具有线性趋势只有当数据具有线性分布特征时，才能采用线性回归分析方法从图中 可以看出，本例数据具有线性分布趋势，可以进行线性回归回归的步骤如下：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 值的输入区域是不一样的：前者包括数据标志：最大积雪深度 灌溉面积x(米) y(千亩)后者不包括这一点务请注意（图 8）图 8-1 包括数据“标志”图 8-2 不包括数据“标志”3. 再后，确定，取得回归结果（图 9）图 9 线性回归结果4. 最后，读取回归结果如下：截距： a = 2.356 ；斜率： b = 1.813 ；相关系数： R = 0.989 ；测定系数： R 2 = 0.979 ；F 值： F = 371.945 ；t 值： t = 19.286 ；标准离差（标准误差）： s = 1.419 ；回归平方和： SSr = 748.854 ；剩余平方和： SSe = 16.107 ；y 的误差平方和即总平方和： SSt = 764.9615. 建立回归模型，并对结果进行检验模型为： yˆ = 2.356 + 1.813x至于检验， R、R2、F 值、t 值等均可以直接从回归结果中读出实际上， R = 0.989416 > 0.632 = R值，F 值和 t 值均可计算出来。

F 值的计算公式和结果为：0.05,8，检验通过有了 RF = 1R 2 =(1 - R 2 )0.98941621 (1 - 0.9894162 )= 371.945 > 5.32 = F0.05,8n - k - 1 10 - 1 - 1显然与表中的结果一样T 值的计算公式和结果为：t = R= 0.979416= 19.286 > 2.306 = t0.05,81 - 0.97941610 -1 -1回归结果中给出了残差（ 图 10），据此可以计算标准离差1 - R 2n - k -1首 先 求 残 差 的 平 方 e2 = ( yi i- yˆi)2 ， 然 后 求 残 差 平 方 和S = n=10e 2i1Sv16.1078i=1= 1.724 + L + 0.174 = 16.107 ，于是标准离差为1n - k -1ni=1( y - yˆ )2iis = =于是= = 1.419s = 1.419= 0.0388 < 10 ~ 15% = 0.1 ~ 0.15y 36.53图 10y 的预测值及其相应的残差等进而，可以计算 DW 值（参见图 11），计算公式及结果为nDW =(eii=2- e )2i-1= (-1.911 + 1.313)2 + L + (0.417 - 0.833)2 = 0.751n e 2ii=1(-1.313)2+ (-1.911)2+ L + 0.4172取a = 0.05 ， k = 1 ， n = 10 （显然 v = 10 -1 -1 = 8 ），查表得 dl= 0.94 ，d = 1.29 。

显然，DW=0.751 < du l= 0.94 ，可见有序列正相关，预测的结果令人怀疑图 11 利用残差计算 DW 值利用 Excel 快速估计模型的方法：2. 用鼠标指向图 4 中的数据点列，单击右键，出现如下选择菜单（图 12）：图 122. 点击“添加趋势线 ”，弹出如下选择框（图 13）：图 133. 在“分析类型”中选择“线性(L)”，然后打开选项单（图 14）：图 144. 在选择框中选中“显示公式(E)”和“显示 R 平方值”（如图 14），确定，立即得到回归结果如下（图15）：图表标题605040y = 1.8129x + 2.3564R2= 0.9789灌溉面积y(千亩)302010线性 (灌溉面积y(千亩))00102030图 15在图 15 中，给出了回归模型和相应的测定系数即拟合优度顺便说明残差分析：如果在图 8 中选中“残差图(D)”，则可以自动生成残差图（图 12）X Variable 1 Residual Plot差残3210-1 0-2-351015202530X Variable 1图 16回归分析原则上要求残差分布是无趋势的，如果在图中添加趋 势线，则趋势线应该是与 x 轴平行的，且测定系数很小。

事实上，添加趋势线的结果如下（图 17）：X Variable 1 Residual Plot3210-1 0-2-3y = -9E-15x + 2E-13R2= 1E-27差残51015202530X Variable 1图 17可见残差分布图基本满足回归分析的要求预测分析虽然 DW 检验似乎不能通过，但这里采用的变量相关分析，与纯粹的时间序列分析不同（时间序列分析应该以时间为 自变量）从残差图看来，模型的序列似乎并非具有较强的自 相关性，因为残差分布相当随机因此，仍有可能进行预测分 析现在假定：有人在 1981 年测得最大积雪深度为 27.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 单元格（对应于自变量 x ），于是得到表达式“=$F$2+$F$3*B2”（图 19）,1相当于表达式 yˆ1= a + b * x1，回车，立即得到 yˆ1= 29.9128 ，即 1971年灌溉面积的计算值。

图 193. 将十字光标标至于 D2 单元格的右下角，当粗十字变成细十字以后，按住鼠标左键，往下一拉，各年份的灌溉面积的 计算值立即出现，其中 1981 年对应的 D12 单元格的52.212y即我们所需要的预测数据，即有 ˆ = 52.212 千亩（图 20）11图 204. 进一步地，如果可以测得 1982 年及其以后各年份的数据，输入单元格 B13 及其下面的单元格中，在 D13 及其以下的单元格中，立即出现预测数值例如，假定 1982 年的最大积雪深度为 x12= 23.7 米，可以算得 yˆ12= 45.323 千亩；1983 年的最大积雪深度为x13= 15.7 ，容易得到 yˆ13= 31.819 千亩（图 21）图 21 预测结果（1981－1983）最后大家思考一下为什么 DW 检验对本例中的问题未必有效？2. 利用 Excel 进行多元线性回归分析【例】某省工业产值、农业产值、固定资产投资对运输业产值 的影响分析Excel 2000 的操作方法与一元线性回归分析大同小异： 第一步，录入数据（图 1）图 1 录入的原始数据第二步，数据分析1. 沿着主菜单的“工具（ T）”→“数据分析（ D）…” 路径打开“数据分析”对话框，选择“回归”，然后“确定”，弹出“回归”分析对话框，对话框的各选项与一元线性 回归基本相同（图 2）。

下面只说明 x 值的设置方法：首先，将光标置于“X 值输入区域（X）。