用Excel做线性回归分析.docx

用Excel进行一元线性回归分析Excel功能强大，利用它的分析工具和函数，可以进行各种试验数据的多元线性回归 分析本文就从最简单的一元线性回归入手.在数据分析中，对于成对成组数据的拟合是经常遇到的，涉及到的任务有线性描述，趋势 预测和残差分析等等很多专业读者遇见此类问题时往往寻求专业软件，比如在化工中经常用 到的Origin和数学中常见的 MATLAB等等它们虽很专业，但其实使用Excel就完全够用了我们已经知道在 Excel自带的数据库中已有线性拟合工具，但是它还稍显单薄，今天我们 来尝试使用较为专业的拟合工具来对此类数据进行处理文章使用的是2000版的软件 我在其中的一些步骤也添加了2007版的注解.1利用Excel2000进行一元线性回归分析首先录入数据.以连续10年最大积雪深度和灌溉面积关系数据为例予以说明录入结果见下图（图 1）ABCD1年份最大积雪深度城米）灌溉面积式干田）2197115.224 6 口3r 197210. 419. S\4r 197321.2'40.5二5197418. 635. 6\6197526.442.017197623. 4458197713.529. 29197816. 734, 1]101979244G. T11198019. 137. 4图1第二步，作散点图如图2所示，选中数据（包括自变量和因变量），点击“图表向导”图标；或者在“插入”菜单中打开“图表（H） （excel2007）”。

图表向导的图标为 选中数据后，数 据变为蓝色（图2）脸5口二狂七%—1 -连维10年是大积雪漂度和灌源面积的数据h宜1文件® 情羯a）视图⑨ 插入① 格式®J工具豆）数据皿 葡口但□ L019后1号口甯亶一，▼嗫芝在外HUE二=最大枳雪深度内米）ABcD1年份最大积雪深度冗怵）灌溉面积y（千田）2197128. 63197210. 419. 345197319M2L21g. 640.5X586197E26.448. 97197623. 445S197713. E29. 29197816. 734 11019792446. 711198019.137. 41图表向导- 4步骤之1 -图表类型图2标准类型I自定义类型I圉去类型©:子图表类型①:散点图口比技成汇的数值图的 理理谶图 在条折排EJ图S囹 3环达面泡 腓雷曲气MH匹囱tel?:点击“图表向导”以后，弹出如下对话框（图3）：回 取一 I按下手放用苴着示例第I上步|小二步）I 一成I图3在左边一栏中选中“ XY散点图”，点击“完成”按钮，立即出现散点图的原始形式 （图 4）:灌溉面积y（千亩）60图4第三步，回归观察散点图，判断点列分布是否具有线性趋势。

只有当数据具有线性分布特征时，才 能采用线性回归分析方法从图中可以看出，本例数据具有线性分布趋势，可以进行线性 回归回归的步骤如下：⑴首先，打开“工具”下拉菜单，可见数据分析选项（ 见图5） （2007为“数据”右端 的“数据分析"）：图5用鼠标双击“数据分析”选项，弹出“数据分析”对话框（图 6）:图6⑵然后，选择“回归”，确定，弹出如下选项表:图7进行如下选择：X、Y值的输入区域（B1:B11, C1:C11）,标志，置信度（95%）, 新工作表组，残差，线性拟合图或者：X、Y值的输入区域（B2:B11 , C2:C11）,置信度（95%）,新工作表组，残 差，线性拟合图注意：选中数据“标志”和不选“标志”，X、Y值的输入区域是不一样的：前者包括数据标志：最大积雪深度x（米）灌溉面积y（千亩） 后者不包括这一点务请注意图8-1 包括数据“标志”图8-2 不包括数据“标志”⑶再后，确定，取得回归结果（图9）LoifTPJi,23口匚线计4Mal ti!?lc ROL 9B94155R 3 JLLfll rI.9T8944&Al - Ji? led K Li 41alJe1.9703127标港屋主L 乳 BK4S秘i 1儡10EfG . H 1 J6Q4D2OQ£fl展大枳雪深辰xt米。

1.正「叫t ?lm•准空面巩四千宦,■在如涯就工科「千面？11.最大以雪深度工保）LLdfSSpS.1 irjuflLisjiCs F12同归介桁1748,酮2m.S. 420： .11-0813铤16.U67601：币「N力t网.9U15”曲目ft"tv 杯;tt/差 t Si ar P-i^lrje Lwrr I限 Pf&ner描 F 限 签，趾旧 的，菁17Tntcreeps2 3W 381.8278761.2891670.2333€3& 57153-1.858P56T门腌1819品人翼言深位”卡〕-'''："0. 09^00219.2 M1位ETEL成为5叶随工必盯11.59€1E11.0296912i21或tESTDHAI out?pit鳖2F硒直香祗而枳y瞪差1毁父后彳-1. 3I2H42fi221, 21082-1, ^103227310, 79036-O.2U036闵、4热 07677T.，1fgqc50. '17^5--, 517553」，！一、同晅结出/氐工心回尸济ri］，」图9线性回归结果⑷最后，读取回归结果如下：截距：a 2.356;斜率：b 1.813;相关系数：R 0.989;测定系数：R2 0.979; F 值：F 371.945。

⑸建立回归模型，并对结果进行检验模型为：? 2.3561.813x至于检验R 0.989416,R、0.632R2和R0.05,8 ，F值可以直接从回归结果中读出实际上，检验通过有了 R值，F值和t值均可计算出来F值的计算公式和结果为:n k 1显然与表中的结果一样1 R2) _ 20.989416 371.945 5.32 Fo.05,81 2(1 0.9894162)10 1 1t值的计算公式和结果为:R1 R20.9794161 0.97941619.286 2.306 t0.05,8n k 1回归结果中给出了残差（10图 10),1 1据此可以计算标准离差首先求残差的平方（yi贸）2,然后求残差平方和 sio2 _i 1.72410.174 16.107,于是标准离差为s1c 16.107 …cS . 1.419v . 8于是观测值瞿溉面积V残差:残差平方129. 91284-1. 31283811.723544标准离差白 221.21082-1. 9105173. 6512221, 418923905340, 79036-0. 29036450. 084312P436, 0767T-0.4Y57697。

・ 2273C9的的增值1550. 21755-1.317554k7359490. 038842702644. 778790. 221209160.048933726. 830872. 36912775.612766832. 632221. 467780292.154379g45. 866540. 833455520.694651036. 983230. 416769730-1)3697我差平方和16.106762. 013345]sy1.41936.530.0388 10 ~15% 0.1 ~ 0.15图10 y的预测值及其相应的残差等进而，可以计算DW值（参见图11）,计算公式及结果为取显然,DW0.05,n(i i 1)2 i 2n 2i 1___22(1.911 1.313)(0.417 0.833)222(1.313)( 1.911)0.4170.751残差1-9残差2 - 10残差之差残差之差的平方-1. 312838-1. 910817-0. 5979788890. 357578752-L 910817-0. 2903651.6204525012. 625866307-0.290365-Q, 47677-0.1864052320. 03474691-0.47677-L 317554-0. 3407843050. 706918248-1.3175540. 22120921.5387631942. 3677921680.22120922. 36912772.1479185414. 6135540592.36912771. 4677803-0. 9013474070. 8124271491.46778030. 8334565-0.6343237730. 4023666非0.83345650. 4167697-0.4166867S30. 173627875DW值.0. 4167697残差之差的平方和12. 094878120. 750919k 1 , n 10 (显然 v 10 1 1 8),查表得 dl 0.94, du 1.29DW=0.751 dl 0.94,可见有序列正相关，预测的结果令人怀疑图1。