用Excel做线性回归分析

1、用Excel进行一元线性回归分析Excel功能强大，利用它的分析工具和函数，可以进行各种试验数据的多元线性回归分 析。本文就从最简单的一元线性回归入手.在数据分析中，对于成对成组数据的拟合是经常遇到的，涉及到的任务有线性描述，趋势 预测和残差分析等等。很多专业读者遇见此类问题时往往寻求专业软件，比如在化工中经常用 到的Origin和数学中常见的MATLAB等等。它们虽很专业，但其实使用Excel就完全够用 了。我们已经知道在Excel自带的数据库中已有线性拟合工具，但是它还稍显单薄，今天我们 来尝试使用较为专业的拟合工具来对此类数据进行处理。文章使用的是2000版的软件，我在其中的一些步骤也添

2、加了 2007版的注解.1利用Excel2000进行一元线性回归分析首先录入数据.以连续10年最大积雪深度和灌溉面积关系数据为例予以说明。录入结果见下图（图 1）。ABC1_ D1年份最大积雪深度就米）罹溉面积冗千亩）2197115. 228. 63197210. 419. 34197321. 240. 55197413. 635. 66197526. 44沁7197623. 4458197713. 529. 29197816. 734. 11019792446. 71119800 137. 4图1第二步，作散点图如图2所示，选中数据（包括自变量和因变量），点击“图表向导”图标；或者在 “插入

3、”菜单中打开“图表（H）（excel2007）”。图表向导的图标为地。选中数据后，数 据变为蓝色（图2）。Hi crosoft Em cel -连续1U年最大积雪薛度和谴岛面税的数据h亶|文件 编辑田）视图（V）插A（l）格式 工具（1）数冤如 菌口心|芽13占昌虫助程|d”|邕匡&斜71微区BlS最大积雪深度H米）|_ AB1C D1年份最大积雪深度或米）榷溉面积四千亩）2197115. 228. 63197210. 419. 34197321. 240. 5519741S. 6366197526. 448. 97197623. 4458197713. 529. 2g197816. 734.

4、 11019792446. 711198019. 137. 41图2点击“图表向导”以后，弹出如下对话框（图3）：|图表茴导- 4步骤之1 -图表类型图表类型E:标卷类型自定义类型|莅下不放可登看示例。I囹图囹H形姓图1环达面泡 拄条折饼圆雷曲气 MM 也 s/，lalwlgl!囹图图圈莅下不放可登看示例。I囹图囹H形姓图1环达面泡 拄条折饼圆雷曲气 MM 也 s/，lalwlgl!囹图图圈图3在左边一栏中选中“XY散点图”，点击“完成”按钮，立即出现散点图的原始形式（图 4）：灌溉面积y（千亩）图4第三步，回归观察散点图，判断点列分布是否具有线性趋势。只有当数据具有线性分布特征时，才 能采用

5、线性回归分析方法。从图中可以看出，本例数据具有线性分布趋势，可以进行线性 回归。回归的步骤如下:首先，打开“工具”下拉菜单，可见数据分析选项（见图5）（2007为”数据”右端 的”数据分析”）:nil海面积的数据t式也）工具（I数据 窗口如 帮助N ,乎拼写FT E宋体 TOC o 1-5 h z 声自动保存物.d共享工作薄Qp.E灌溉 保护* 联机协作规划求解世）自定义.选项 向导地）数据分析如.更新加裁麦链接d.用鼠标双击“数据分析”选项，弹出“数据分析”对话框（图6）：图6然后，选择“回归”，确定，弹出如下选项表:图7进行如下选择：X、Y值的输入区域（B1:B11, C1:C11），标志

6、，置信度（95%）, 新工作表组，残差，线性拟合图。或者：X、Y值的输入区域（B2:B11，C2:C11），置信度（95%），新工作表组，残 差，线性拟合图。注意：选中数据“标志”和不选“标志”，X、Y值的输入区域是不一样的：前者包 括数据标志：最大积雪深度x（米）灌溉面积y（千亩）后者不包括。这一点务请注意。图8-1包括数据“标志”图8-2不包括数据“标志”再后，确定，取得回归结果（图9）。FG H I JLSUMKART OUTPUT234MiJtiple R0.9B94165R Square0. 9789446Adjijste-d R S qiiareL.牟，一；17标准炭1.31892

7、48观测值109J.w.件,与危；球：m。-n-2 3.11 .11项.4iM：I濯冶面可疽千症:予测谁溉面积r?.r 如最烦雪探度丫 .、；，.岱EirU-二一上 /1点1，止.：一二T4快获如371. 94535. 42032E-081J1U.2. 01335J7&1 9611516nr.- . i -t杯畔差T If-PalueLower 95U阮CT勺洒卜R 源1没爬洒.仍17Intercept1一3.郭美黛-一石:：:也5门531819如、理冰. ：i wo. i?： - -?1勺一弥二；，.7F-C?-一仲IL*,1,596151:.：|一202122=CT-FT2324观测值龄：

8、&叫:差25129. 91284-1.3120426221. 21082-1.91DS22711,厂 1T.28i36. 0767T-07677寡F50. 21755Qfi H 回Q结果/Shi田心 9 7Q7.：itlS-.FHl 0.632 = R0 05 8，检验通过。有了 R值，F值和t值均可计算出来。F值的计算公式和结果为：.戏R 20.9894162F = = 371.945 5.32 = F(1 R 2)(1 0.9894162)0.05,8 n k 110 1 1显然与表中的结果一样。t值的计算公式和结果为：t=1 - R 2 n k -1回归结果中给出了残差（R 0.9794

9、16 t=1 - R 2 n k -1回归结果中给出了残差（：1 0.9794160.05,8:10-1 -1图10)，据此可以计算标准离差。首先求残差的平方i =18 2 = ( - )2，然后求残差平方和S =罗 2 = 1.724 + + 0.174 = i =1离差为1=1.419s = 旗(y y )2 = ，一S =1.419n k-1 . ， i v 18e qe q)2DW = -i=2乙2is1.419-= -=0.0388 10 15% = 0.10.15y 36.53观测值薯溉面积y残差残差平方129.91284-1.31283811.723544标准窗差s221.210

10、82-1.910S173.6512221.41S923905340.79036-0.29036450.084312436.07677-0.47676970.227309放的均值550.21755-1.3175541.7359490.03SS42702644.778790. 221209160.048933726.E30872. 36912775.612766S32.632221.467780292.15437945.E66540.833456520.694651036.933230. 416769730.173697残差平方和16.106762. 013345图10 y的预测值及其相应的残差等

11、进而，可以计算DW值（参见图11），计算公式及结果为=0.751(1.911 +1.313)2 + + (0.417 0.833)2(1.313)2 + (1.911)2 + + 0.417=0.751i=1取a= 0.05，k = 1 , n = 10 （显然 v = 10 1 1 = 8 ），查表得 d = 0.94 , d = 1.29显然，DW=0.751 d = 0.94，可见有序列正相关，预测的结果令人怀疑。U*1 932 10残差之差残差之差的平方-1.312838-1. 910817-0.5979788890.357578752-1.910817-0. 2903651.6204

12、525012.625866307-0.290365-0. 4花-0.1864052320.03474691-0.47677-1. 317554-0.8407843050.706918248-1.3175540. 22120921.5387631942.3677921680.22120922. 36912772.1479185414.6135540592.36912771.4677803-0.9013474070. 8124271491.46778030. 8334565-0.6343237730.4023666490.83345650. 4167697-0.4166S67330.173627875LW值0.4167697残差之差的平方和12. 0948781