利用Excel进行线性回归分析汇总

1、文档内容1.利用Excel进行一元线性回归分析2.利用Excel进行多元线性回归分析1.利用Excel进行一元线性回归分析第一步，录入数据以连续10年最大积雪深度和灌溉面积关系数据为例予以说明。录入结果见下图（图1）。第二步，作散点图如图2所示，选中数据（包括自变量和因变量），点击“图表向导”图标；或者在“插入”菜单中打开“图表（H）”。图表向导的图标为血。选中数据后，数据变为蓝色（图2）。18匚A1年份2197131972419735197461975719768197791978101979111980B1=最大积雪棵度讥B最大积雪棵度或米）灌溉面积y（千亩）图恚商导-4步骤之1-图表类型

2、?|>|子團表粪型：标唯类型自走义类型|團恚类型心:'開形形线圏_9环达面泡荘条折饬凰雷曲气Mlr空吐lalwlgl!散点阴-比较成对的数值取消在左边一栏中选中“XY散点图”，点击4）：图3完成”按钮，立即出现散点图的原始形式（图MicrosoftExcel-连续ID年摄大积雪深度和灌陽面积的数据b塹文件®褊辑视图插入格式工具数据）窗口迪HSEi噩龜H葛艺底斛束Ml52 197115.23 197210.44 197321.25 197418.66 197526.47 197623.48 197713.59 197816.710 197924n198o|19.1图2点击

3、“图表向导”以后，弹出如下对话框（图3）：灌溉面积y（千亩）图4第三步，回归观察散点图，判断点列分布是否具有线性趋势。只有当数据具有线性分布特征时，才能采用线性回归分析方法。从图中可以看出，本例数据具有线性分布趋势，可以进行线性回归。回归的步骤如下：1.首先，打开“工具”下拉菜单，可见数据分析选项（见图5）：图5用鼠标双击“数据分析”选项，弹出“数据分析”对话框（图6）：图62.然后，选择“回归”，确定，弹出如下选项表（图7）图7进行如下选择：X、Y值的输入区域（B1:B11,C1:C11），标志，置信度（95%）,新工作表组，残差，线性拟合图（图8-1）。或者：X、Y值的输入区域（B2:B1

4、1，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；测定系数：R2二0.979；F值：F=371.945；t值：t二19.286；标准离差(标准误差)：s二1.419；回归平方和：SSr二748.854；剩余

5、平方和：SSe二16.107；y的误差平方和即总平方和：SSt二764.961。5.建立回归模型，并对结果进行检验模型为：y二2.356+1.813x至于检验，R、R2、F值、t值等均可以直接从回归结果中读出。实际上,R二0.989416>0.632二R，检验通过。有了R值，F值和t值均可计算出来。0.05,8F值的计算公式和结果为：FR2k1(1-R2)nk1显然与表中的结果一样。T值的计算公式和结果为：Rt=0.98941621=371.945>5.32=F10-1-1（i9894162）0W0.979416=19.286>2.306=t1-0.979416°.

6、05,810-1-1回归结果中给出了残差（图10），据此可以计算标准离差。首先求残差的平方=(y-y)2，然后求残差平方和S=S1082=1.724+0.174=16.107，于是标准i=1i离差为于是n11s飞n-k-l一yi-yi)2飞vS=i=1匹=1.419s1.419-=0.0388<1015%=0.10.15y36.53观测值翟溉面积y残差残差平方129.91284-1.31283811.723544标推离差£221.21082-1.9108173.6512221.418923905340.79036-0.29036450.084312436.07677-0.476

7、76970.227309訂y的均值550.21755-1.3175541.7359490.038842702644.778790.221209160.048933726.830872.36912775.612766832.632221.467780292.154379945.866540.833456520.694651036.983230.416769730.173697残差平方和16.106762.013345图10y的预测值及其相应的残差等进而，可以计算DW值（参见图11），计算公式及结果为工(£-£)2DW_ii-1_(-1.911+1.313)2+.+(0.417

8、-0.833)2DW=-=2_=0.751工£2ii=1取a=0.05，k=1，n=10（显然v=10-1-1=8），查表得d=0.94，d=1.29l(-1.313)2+(-1.911)2+0.4172残差19残差210残差之差残差之差的平方-1.312333-1.910817-0.5979788890.357578752-1.910817-0.2903651.6204525012.625866307-0.290365-0.47677-0.1864052320.03474691-0.47677-1.317554-0.8407843050.706918248-1.3175540.22

9、120921.5387631942.3677921680.22120922.36912772.1479185414.6135540592.36912771.4677803-0.9013474070.3124271491.46778030.8334565-0.6343237730.4023666490.83345650.4167697-0.4166867830.173627875DWffi0.4167697残差之差的平方和12.094878120.750919u显然，DW=0.751<d=0.94，可见有序列正相关，预测的结果令人怀疑。/图11利用残差计算DW值利用Excel快速估计模型的

10、方法:2.用鼠标指向图4中的数据点列，单击右键，出现如下选择菜单（图12）:图122点击“添加趋势线®”，弹出如下选择框（图13）:图133.在“分析类型”中选择“线性（L）”，然后打开选项单（图14）:图144.在选择框中选中“显示公式(E)”和“显示R平方值®”(如图14)，确定，立即得到回在图15中，给出了回归模型和相应的测定系数即拟合优度。顺便说明残差分析：如果在图8中选中“残差图(D)”，则可以自动生成残差图(图12)。回归分析原则上要求残差分布是无趋势的，如果在图中添加趋势线，则趋势线应该是与X轴平行的，且测定系数很小。事实上，添加趋势线的结果如下（图17）：X

11、Variable1ResidualPlot图17可见残差分布图基本满足回归分析的要求。预测分析虽然DW检验似乎不能通过，但这里采用的变量相关分析，与纯粹的时间序列分析不同（时间序列分析应该以时间为自变量）。从残差图看来，模型的序列似乎并非具有较强的自相关性，因为残差分布相当随机。因此，仍有可能进行预测分析。现在假定：有人在1981年测得最大积雪深度为27.5米，他怎样预测当年的灌溉面积？下面给出Excel2000的操作步骤：2.在图9所示的回归结果中，复制回归参数（包括截距和斜率），然后粘帖到图1所示的原始数据附近；并将1981年观测的最大积雪深度27.5写在1980年之后（图18）。aBIC

12、DEIF1年份最大积雪深度茨米）灌溉面积y（千亩）计算值Coefficients2197115.220.6intercept2.3564379293197210.419.3量丈积雪深度孟（米）1.0129210654197321.240.5519741S.635.66197526.44S.97197623.4458197713.529.29197S16.734.11019792446.711198019.137.412198127.5图182.将光标至于图18所示的D2单元格中，按等于号“二”，点击F2单元格（对应于截距a=2.356），按F4键，按加号“+”，点击F3单元格（对应于斜率b=1

13、.812），按F4键，按乘号“*”，点击B2单元格（对应于自变量X），于是得到表达式,$F$2+$F$3*B2”（图19），相当于表达式y=a+b*x,回车，立即得到y=29.9128，即1971年灌溉面111积的计算值。LaBCD1EF1年份最大积雪深度吃1勺灌漑面积y（千旬计算值Coefficients21971L12：28.6=$F$2-F$3*B2|2.3564=379293197210.4：19.3最大积雪深度孟（米）1.8129210654197321.240.5519741S.635.66197526.4：48.97197623.4：458197713.529.29197016.

14、734.110197924：46.71119S019.137.41219S127.5图193.将十字光标标至于D2单元格的右下角，当粗十字变成细十字以后，按住鼠标左键，往下一拉，各年份的灌溉面积的计算值立即出现，其中1981年对应的D12单元格的52.212即我们所需要的预测数据，即有$=52.212千亩（图20）。图201止BCDEF1年份最大积雪深度臥米：）灌穆面积巩千应）计算值Coefficients217115.22S.629.913In.tercept2.3564379293197210.419.321.211最丈积雪深度n（米）1.8129210654197321.240.540.

15、795197418.635.636.077619阳26.44S.950.2187197623.44544.7798197713.529.226.031勺197816.734.132.6321019792446.745.86711198019.137.436.9B312198127.552.2121319B223.745.3231419S315.730.B194.进一步地，如果可以测得1982年及其以后各年份的数据，输入单元格B13及其下面的单元格中，在D13及其以下的单元格中，立即出现预测数值。例如，假定1982年的最大积雪深度为x=23.7米，可以算得y=45.323千亩；1983年的最大积

16、雪深度为1212x=15.7，容易得到y=31.819千亩（图21）。4313图21预测结果（19811983）最后大家思考一下为什么DW检验对本例中的问题未必有效?2.利用Excel进行多元线性回归分析例】某省工业产值、农业产值、固定资产投资对运输业产值的影响分析。Excel2000的操作方法与一元线性回归分析大同小异第一步，录入数据（图1）。AIBCDEF1序号年份工业产值xl农业产值沱固定资产投资幻运输业产值y21197057.S227.0514.543.0932197150.052S.S916.833.4：43197259.1533.0212.263.SS54197363.S335.2

17、312.873.965197465.3624.9411.653.2276197567.2632.9512.S73.7687197666.9230.3510.03.5990197767.7938.710.934.03109197S75.6547.9914.714.341110197980.5754.1017.564.651211190079.025S.7320.324.7S131219S1S0.5259.S510.675.04141319S2S6.SS64.5725.345.591514：19S395.4S70.9725.066.01161519S4109.71S1.5429.697.03171

18、61905126.594.014=3.8610.03181719S613S.S9103.2340.910.S3图1录入的原始数据第二步，数据分析1.沿着主菜单的“工具（T）J“数据分析（D）.”路径打开“数据分析”对话框，选择“回归”，然后“确定”，弹出“回归”分析对话框，对话框的各选项与一元线性回归基本相同（图2）。下面只说明x值的设置方法：首先，将光标置于“X值输入区域（X）”中（图2）；然后，从图1所示的C1单元格起，至E19止，选中用作自变量全部数据连同标志，这时“X值输入区域（X）”的空白栏中立即出现“$C$1:$E$19”当然，也可以通过直接在“X值输入区域（X）'啲空白栏

19、中输入“$C$1:$E$19”的办法实现这一步骤。注意：与一元线性回归的设置一样，这里数据范围包括数据标志：工业产值农业产值固定资产投资运输业产x1x2x3值y故对话框中一定选中标志项（图3）。如果不设“标志”项，则“X值输入区域（X）”的空白栏中应为“$C$2:$E$19”，“Y值输入区域（Y）”的空白栏中则是“$F$2:$F$19”。否则，计算结果不会准确。图3设置完毕后的对话框（包括数据标志）图2x值以外的各项设置2.完成上述设置以后，确定，立即给出回归结果。由于这里的“输出选项”选中了“新工作表组（P）”（图3）,输出结果在出现在新建的工作表上（图4）。从图4的“输出摘要（SUMMAR

20、YOUTPUT）”中可以读出：a=1.0044,b=0.053326,b=-0.00402,b=0.090694,R二0.994296,123R2=0.988625，s=0.335426，F=405.5799，t=2.940648，t=-0.28629，b1b2t=3.489706。b3根据残差数据，不难计算DW值，方法与一元线性回归完全一样。根据回归系数可以建立如下多元线性模型：y=-1.0044+0.55326x-0.00402x+0.090694x123由于x2的回归系数b2的符号与事理不符，b2的t检验值为负，b2的绝对值很小,可以判定，自变量之间可能存在多重共线性问题。ABCDEFG

21、HI1SUMMARYOUTPUT23回归统计4Multiple0.9942965RSquare0.9886256Adjusted0.9861877标港误差0.3354268观测值18910方差分析11dfSSMSFnificanceF12回归分析3136.895845.63192405.57997.71E-1413残差141.5751440.1125114总计17138.47091516Coefficien-标准误差tStatP-valueLower95%Upper95%T限95.0止限95.017Intercept-1.00440.643156-1.561680.140679-2.38384

22、0.375031-2.383840.37503118工业产值0.0553260.0188142.9406480.0107430.0149730.0956780.0149730.09567819农业产值-0.004020.014029-0.286290.778846-0.034110.026073-0.034110.02607320固定资产扌0.0906940.0259893.4897060.0036080.0349530.1464350.0349530.14643521222324RESIDUALOUTPUTPROBABILITYOUTPUT2526观测值J运输业产残差标准残差百分比排苍M输业

23、产值¥2713.40457-0.31457-1.033432.7777783.092823.617595-0.2176-0.714858.3333333.222933.2473930.6326072.07825413.888893.4.-1匚匚Q7CQriQ/17QQ711.ir-i7.-io1门.-1.-1.-1.-1.-1匚门H4卜I回归？吉果f原始数据/Sheet2/Sheet3/|图4第一次回归结果3剔除异常变量x2（农业产值），用剩余的自变量&x3与y回归（图5）,回归步骤无非是重复上述过程（参见图6，注意这里没设数据“标志”），最后给出的回归结果（图7）。ABCD

24、E1序号年份工业产值xl固定资产投资垃运输业产值y21197057.0214.543.093219715S.0516.S33.4：43197259.1512.263.0054197363.S312.S73.965197465.3611.653.2276197567.2612.S73.7687197666.9210.03.5990197767.7910.934.03109197075.6514.714.34：1110197980.5717.564.65121119S079.0220.324.7S131219S1SO.52IS.675.04141319S2S6.SS25.345.59151419S

25、395.4S25.066.01161519S4109.7129.697.03171619S5126.543.S610.03181719S613S.S94S.910.S319IS19S7160.5660.9S12.9图5剔除异常变量“农业产值（x2）”图6回归对话框的设置（不包括数据标志）从图7中容易读出回归结果：a=0.89889,b=0.051328,b=0.091229,R二0.994263,R2二0.988558,13s=0.324999，F=647.973，t=4.200968，t=3.632285。b1b3显然，相对于第一次回归结果，回归系数的符号正常，检验参数F值提高了，标准误差s值降低了，t值检验均可通过。相关系数R有所降低，这也比较正常般来说，增加变量数目通常提供复相关系数，减少变量则降低复相关系数。回归结果可以接受，建立二元回归模型如下：y二0.051328x+0.09122