利用Excel2000进行多元线性回归分析

1、3利用Excel2000进行多元线性回归分析【例】某省工业产值、农业产值、固定资产投资对运输业产值的影响分析Excel2000的操作方法与一元线性回归分析大同小异：第一步，录入数据（图1）ABCDEF1序号年份工业产值1农业产值KN固定黄产投资必 运输业产值y21137057.8227.0514.54工 0332197155.0520.9916.623.443197259.1533.0212.263.88541973除8335.2312.873,965197465.3624.9411.633.2276197567.2632.9512.873.76S7197666.9230,35_10.03.5

2、9q8197767.7gS8.710,93L03109197875.6547.9914.714.34：11101979E0.5754.1917.564.&512L1198079.0258.7320.32kT81312198180.5259.8518.675.041413198286.88G4.5725.345.59151983皈 4870.972A06&,01151519S4109.7101.5429.697.0317161985126.594.014S.8S10.03181713B6138.E9103.23钝，910.83图 1 录入的原始数据第二步，数据分析沿着主菜单的“工

3、具（T）”一“数据分析（D）”路径打开“数据分析”对话框，选择“回归”，然后“确定”，弹出“回归”分析对话框，对话框的各选项与一元线性回归基本相同（图2）。下面只说明x值的设置方法：首先，将光标置于“X值输入区域（X）”中（图2）;然后，从图1所示的C1单元格起，至E19止，选中用作自变量全部数据连同标志，这时“X值输入区域（X）”的空白栏中立即出现“$C$1:$E$19”一一当然，也可以通过直接在“X值输入区域（X）”的空白栏中输入“$C$1:$E$19”的办法实现这一步骤。注意：与一元线性回归的设置一样，这里数据范围包括数据标志：工业产值x1农业产值x2固定资产投资x3运输业产值y故对话框

4、中一定选中标志项（图3）。如果不设“标志”项，则“X值输入区域（X）”的空白栏中应为“$C$2:$E$19，Y值输入区域（工）”的空白栏中则是“$F$2:$F$19。否则，计算结果不会准确。图 2x 值以外的各项设置图 3 设置完毕后的对话框（包括数据标志）完成上述设置以后，确定，立即给出回归结果。由于这里的“输出选项”选中了“新工作表组（E）”（图3）,输出结果在出现在新建的工作表上（图4）。从图4的“输出摘要（SUMMARYOUTPUT）”中可以读出：s=0.335426,F=405.5799,tb1=2.940648,tb2=-0.28629,? ?ritb3=3.489706。根据残差

5、数据，不难计算DW值，方法与一元线性回归完全一样。根据回归系数可以建立如下多元线性模型：y?-1.0044-0.55326x1-0.00402x20.090694x3由于X2的回归系数b2的符号与事理不符，b2的t检验值为负，b2的绝对值很小,可以判定，自变量之间可能存在多重共线性问题。1ABCDEFGHISUMMARYOUTPUT23回归统计gMultiple0.9942965RSq?are0.9886256Adjusted0.9861871标准误差0.3354268观测值13910方差分析11dfSSMSFtnificanceF12回归分析3 136.8958 45.63192405.57

6、997.TIE-1413我差141,5751440.1125114总计1713S.4701516CQeFficierv标准误差tStatP-valueL删巳r95见郎“95而诲95.限9匕417Intercept_1,00440.643156 -1.561680.140679-2.383840.375031-2.383840.37503113工业产值0.0553260.0188142.9406480.0107430.0149730.0956780.0149730,09567819农业产值-0.004020.014029 -0.286290.778846-0.034110.026073-0.03

7、4110.02607320固定资汽0.0906940.025989 3.4897060.0036080.0349530.1464350.0349530.146435212223剔除异常变量X2（农业产值），用剩余的自变量刈、治与y回归（图5）,回归步骤无非是重复上述过程（参见图6,注意这里没设数据“标志”），最后给出的回归结果（图7）。a=-1.0044,bi=0.053326,b2-0.00402,b3=0.090694,R=0.994296,2R=0.988625RESIDUALOUTPUTPROBABILITYOUTPUT2526观测值J运输业产残差标准残差3分比排僮输业产值y2713*

8、40457C31457-1,033432.7777783.09282 3.617595-0.2176-0.714B58.3333333.22293 3.2473930.6326072.07825413,888893,4空,/I,q仁仁日1r7aq.nOdT门9*711/lA7/IO1RH4+1%回归结果/原始数据/Shoe12/Sheet3/111图 4 第一次回归结果1A1BCDE1序号年份工业产值xl固定资产投资x3运输业产值y21197057.8214.543.0932197158.0516.033.443197259.1512.263.8854197363,8312.S73.9J651

9、97465.36IL653.22116197567.2612.573.7687197666.9210.83.5998197767.7910.934.03109197875.6514.714.341110197980.5717.564.651211198079.0220.324.781312198180.52675.04J1413198286.8825.345.591514198395.4825.066.01116151984109.7129.697.03-117161985126.5蚀8610.0318171986138.894&910.83j19181987160.5660.9812

10、.9J图 5 剔除异常变量“农业产值（x2）图 6 回归对话框的设置（不包括数据标志）从图7中容易读出回归结果：_2a=-0.89889b=0.051328b_,=0.091229R=0.994263R=0.988558I3s=0.324999,F=647.973,tb1=4.200968,tb3=3.632285。bIb3显然，相对于第一次回归结果，回归系数的符号正常，检验参数F值提高了，标准误差s值降低了，t值检验均可通过。相关系数R有所降低，这也比较正常一一一般来说，增加变量数目通常提供复相关系数，减少变量则降低复相关系数。回归结果可以接受，建立二元回归模型如下：y=0.051328x1

11、-0.091229x3-0.89889或者运输业产值=0.051328*工业产值+0.091229*固定资产投资0.89889ABCDEFGHI1SUMMARYOUTPUT23rit4Multiple0.9942635RSquare0.9885586Ac标Justed0.9870337潴误差0.3249998观测值18910方差分析1112回归分析df2SS136.8865MS68.44326 F647.9B73mificance2.75E-15;F13我差15 1.5843660.10562414总计17 13S,47091516Coefficien-标1隹误差tStatP-valueLow

12、er95%UoDer95%麻95,OdzfI95.V17Intercept_0.B9SS90.510714-1.760070.09S766-1.987460.189667-1,987460.1896671EXVariabl0.0513280.0122184.2009630.0007710.0252860.0773710.0252860,07737119XVariabl0.0912290.0251163.6322850.0024570.0376950.1447630.0376950.14476320212223 RESIDUALOUTPUTPROBABILITYOUTPUT2425观测值预测Y残差标准残差目分值跄T261： 95368-0.30537-1