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

1、文档内容1 .利用Excel进行一元线性回归分析2 .利用Excel进行多元线性回归分析1.利用Excel进行一兀线性回归分析第一步，录入数据以连续10年最大积雪深度和灌溉面积关系数据为例予以说明。录入结果见下图（图1）|ABCID1年份最大积雪深度虱米）灌溉面积y（千亩）2197115.228.63197210.419.34197321.240.55197418.635.66197526.442.97197623.4458197713.529.29197816.734.11019792446.711198019.137.4图1第二步，作散点图如图2所示，选中数据（包括自变量和因变量），点击图

2、表向导”图标；或者在插入”菜单中打开图表（H）图表向导的图标为选中数据后，数据变为蓝色（图2）。0MicrosoftExcftl-连续1。年昆士积雪深度和灌温闾枳的数据bB1;JK最大枳雪深度工。A_BCD因文件更）编辑口视圄5插入夏）格式也）工具数据窗口诂口昌昌国电喳-匾工或斛总数历1年份最大积雪深度式米）灌溉面积贯千亩）2197115,228.63197210.419.34197321.240.5_5_197418.63QJ66197526.448.97197623*4458197713.529.29197816.734.11019792446.711198019.1迎4图2点击图表向导”

3、以后，弹出如下对话框（图3）:图表向导-4步骤之1-图表类型标准类型I自定义类型I圉去类型:MH匹。囱tel?:图的理理谶图在条折排EJ图S囹3环达面泡腓雷曲气按下不放用查看示例回取一I上步|产二步）I一成。I图3在左边一栏中选中“XY散点图”，点击完成”按钮，立即出现散点图的原始形式（图4）:灌溉面积y（千亩）图4第三步，回归观察散点图，判断点列分布是否具有线性趋势。只有当数据具有线性分布特征时，才能采用线性回归分析方法。从图中可以看出，本例数据具有线性分布趋势，可以进行线性回归。回归的步骤如下：1,首先，打开工具”下拉菜单，可见数据分析选项（见图5）:濯溉面积的数据匕式工具数据窗口世）帮助

4、国）-八手拼写）FT;宋体Lp自动保存一,共享工作簿打一E1霍溉保护也）联机协作QJ）规划求解W）-.自定义一选项（&）向导卜更新加载宏链接一图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值的输入区域是不一样的：前者包括数据标志:最大积雪深度

5、x（米）灌溉面积y（千亩）后者不包括。这一点务请注意（图8）。3.图8-1包括数据标志图8-2不包括数据标志再后，确定，取得回归结果（图9）。.JllPlotODOo510州房大科寄星境*咪J谑溉面枳T+亩）他测双概IW”千百）AgcDE1smiiAEfocrtpur2回归里it3MultipleR0.-9894164RSq.i_i.are0.-9789445AdjustedRSqUrare0.9763126标准误整L-4189247观测恒ID8方差分析-W1T片一即心1T4E,廖位rn.BWE5,n箕器-OES16.1O&F6042.013519764.掂1归主计1314ICcefficie

6、n-标港误卷tStaiRvalueLower95*Vtetr95*下1限95一篦上限95.515Intercept2.35643S1.327875931.2891671.-1.858651246,57153-1.3586546w5715301It.最大积雪深度近景）1.129210.Ci940c网g19.28SS8工.3-0自159615G7962.0296911.596150S2.0296-9131?RESIDUALOUTPUTIS观谢ta雷找面五然,J129.乳2病-1.312E3S12022L210E2-1.41081721340.79036-0.2弼那第22436.07677-o.ir

7、erew23sE0.21755-1.317554上三6M.77ST952212091625726.83087丸369127T2683Z63222L4677802927945,86654CL83345652281036.983230.416769732j生差平方和-ii-A.=图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；剩余平方和：SSe=16.107；y的误差平方和即总平方

8、和：SSt=764.961。5 .建立回归模型，并对结果进行检验模型为：夕=2.3561.813x至于检验，R、R2、F值、t值等均可以直接从回归结果中读出。实际上，R=0.989416a0.632=Ro.05,8,检验通过。有了R值，F值和t值均可计算出来。F值的计算公式和结果为:R2n1(i2)0.989416210-1-1_2(1-0.989416)=371.945A5.32=F0,05,8显然与表中的结果一样。T值的计算公式和结果为:R1-R2.n-k-10.9794161-0.979416,10-1-1=19.286a2.306f,05,8回归结果中给出了残差（图10）,据此可以计算

9、标准离差。首先求残差的平方=（yi-贸）2离差为nJ02然后求残差平万和S=；i=1.7240.174=16.107i4于是标准n2(y-？)i1花产=1419n(r-I)2DW=i-2-2ri1取=0.05,k=1,n=10于是s1.419二-0.0388：二1015%=0.10.15y36.53观测值瞿溉面积V残差残差平方129.91284-1.31283811.723544标准离差s221.21082-L9io81T3.6512221.418923905340.79036-0.29036450.084312436.07677-0.47676970.227309s/y的均值550.2175

10、5-L317554L7359490.038842702644.778790.221209160.048933726.830872.36912775.612766832.632221.467780292.154379945.866540.833456520.694651036.983230.416769730.173697残差平方和16,106762.013345图10y的预测值及其相应的残差等进而，可以计算DW值（参见图11）,计算公式及结果为_2_2(-1.9111.313)+(0.417-0.833)_0751(-1.313)2(-1.911)20.4172一.(显然v=1011=8),查

11、表得dl=0.94,du=1.29。显然，DW=0.751dl=0.94,可见有序列正相关，预测的结果令人怀疑。残差1-9残差2-10残差之差残差之差的平方|_-1.312838-1.910817-0.5979788890.357578752-1.91081T0.290365L6204525012.62586630T-0.290365-0r6-0.1864052320.03474691-0.47677T.317554-0,8407843050.706918248匚1.3175540.22120921.5387631942.3677921680.22120922.36912772.1479185

12、414.61355如59,36912771二76703二0,9013474070.21242714T.46773030.8334565-0,6343237730.402366649叵83345650.41676914).4166867830.17362787耶W值,0.4167697,一残差之差的平方和二2094878120,750919图11利用残差计算DW值利用Excel快速估计模型的方法:2 .用鼠标指向图4中的数据点列，单击右键，出现如下选择菜单（图12）:灌溉面积近千亩）图122.点击添加趋势线?”,弹出如下选择框（图13）:图133 .在分析类型”中选择线性（L）”，然后打开选项单

13、（图14）:图144 .在选择框中选中显示公式（E1和显示R平方值？”（如图14）,确定，立即得到回归结果如下（图15）:图表标题灌溉面积y（千亩）一线性（灌溉面积y（千亩）图15在图15中，给出了回归模型和相应的测定系数即拟合优度。顺便说明残差分析：如果在图8中选中残差图（D）”，则可以自动生成残差图（图12）XVariable1ResidualPlot32-.为1一差残0111.11- 10-510152025*30- 2- 3LXVariable1图16回归分析原则上要求残差分布是无趋势的，如果在图中添加趋势线，则趋势线应该是与轴平行的，且测定系数很小。事实上，添加趋势线的结果如下（图1

14、7）:05101520巴XVariable1ResidualPlot321差0残0-1-2-3y=-9E-15x+2E-13R2=1E-2751015202530XVariable1图17可见残差分布图基本满足回归分析的要求。预测分析虽然DW检验似乎不能通过，但这里采用的变量相关分析，与纯粹的时间序列分析不同（时间序列分析应该以时间为自变量）。从残差图看来，模型的序列似乎并非具有较强的自相关性，因为残差分布相当随机。因此，仍有可能进行预测分析。现在假定：有人在1981年测得最大积雪深度为27.5米，他怎样预测当年的灌溉面积？下面给出Excel2000的操作步骤：2.在图9所示的回归结果中，复制

15、回归参数（包括截距和斜率），然后粘帖到图1所示的原始数据附近；并将1981年观测的最大积雪深度27.5写在1980年之后（图18）。FaBCDEF1年份最大积雪深度式米）灌溉面积vt千亩计算值Coefficients2197115.228.1llntercept2.3564379293197210.419.3最大积雪深度（米）1.8129210654197321.240.55197418.635.66197526.44&97197623.4458197713.529.29197816.734.11019792446,711198019.137,412198127,5图182.将光标至于图18所

16、示的D2单元格中，按等于号，”，点击F2单元格（对应于截距a=2.356-），按F4键，按加号，点击F3单元格（对应于斜率b=1.812-），按F4键，按乘号“*；点击B2单元格（对应于自变量Xi）,于是得到表达式“二$F$2+$F$3*B2（图19）,相当于表达式？1=a+b*Xi,回车，立即得到%=29.9128,即1971年灌溉面积的计算值。1AcDEF1年份最大积雪深度修松灌溉面积孤千亩）计算值Coefficients21971L15.2：28.亦$F$2+$F$3怕22.3564379293197210.419.3最大积雪深度K（米）1.8129210654197321.240.55

17、19741B.635.66197526.448.97197623.4458197713.529.29197816.734.110匚19792446.711198019.137.412198127.5图193.将十字光标标至于D2单元格的右下角，当粗十字变成细十字以后，按住鼠标左键，往下一拉，各年份的灌溉面积的计算值立即出现，其中1981年对应的D12单元格的52.212即我们所需要的预测数据，即有%=52.212千亩（图20）。LaBCD1EF1年份最大积雪深度双米）灌溉面积贝千面计算值Coefficients2197115.228.629.913Intercept2.35643792a319

18、7210.419.32L211最大积雪深度x（米）1.812921065419732L240.540.795197418.635.636.0776197526.448.950.2187197623.445弧,7798197713.529.226.8319197816.7见.132,6321019792446.745.86711198019.137,436.98312198127,552,212图204.进一步地，如果可以测得1982年及其以后各年份的数据，输入单元格B13及其下面的单元格中，在D13及其以下的单元格中，立即出现预测数值。例如，假定1982年的最大积雪深度为x12=23.7米，可

19、以算得？12=45.323千亩；1983年的最大积雪深度为X13=15.7,容易得到出3=31.819千亩（图21）LABCDEF11年份最大积雪深度米）灌溉面积yC千即计算值Coefficients2197115.22S,52913Intexcept2.356437929319Tz10.419.321.211最大积雪深度强（木）L8129210654197321.240.540,795197418.635.633.0776197526.448.950.2187197623.445的.779S197713.E29.22&.931919T815.734.13Z.632101979244工745.

20、96711198019.137,436.98312198127.552.21213193223.745,32314193315.730.819图21预测结果（1981-1983）最后大家思考一下为什么DW检验对本例中的问题未必有效?2.利用Excel进行多元线性回归分析【例】某省工业产值、农业产值、固定资产投资对运输业产值的影响分析。Excel2000的操作方法与一元线性回归分析大同小异：第一步，录入数据（图1）ABCDEIF11序号年份工业产值xl农业产值工2固定资产投资蛭运输业产值21197057.8227.0514.543.0932197158.0528.8916.833.4431972

21、59.1533.0212.263.885197363.8335.2312,373,965197465.36124.9411.653.2276197567.2632.9512.873.7687197666.92130.3510.83.5998197767.7938.710.93L03109197875.654,9914.714.341110197980.57154.1817,564.651211198079.0258.7320.32L781312198130,5259.8518.675.041413198286.88164.5725.345.591151419S3阳4870.9725.066.0

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

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

24、的输出摘要（SUMMARYOUTPUT）”中可以读出：a=-1.0044,bi=0.053326,b2=-0.00402,b3=0.090694,R=0.994296,_2R=0.988625,s=0.335426,F=405.5799,3=2.940648,tb2-0.28629,tb3=3.489706。根据残差数据，不难计算DW值，方法与一元线性回归完全一样。根据回归系数可以建立如下多元线性模型：?-1.00440.55326x1-0.00402x20.090694x3由于X2的回归系数b2的符号与事理不符，b2的t检验值为负，b2的绝对值很小，可以判定，自变量之间可能存在多重共线性问题

25、。AJLBC1DE1F1GHII1SUMMARYOUTPUT23回归统计9Multiple0.9942965RSquare0.9886256Adjusted0.9361S7T1标准误差0.3354268观测值118910方差分析11dfSSKSFmificanct:F12回归分析31&S95845.63192405.57gg7.TIE-14131残差14L5751440.11251U总计1L13&15|16Ccieffi已门”标准误差17|Intercept-1.00440.643156tSlat-1.56168P-valueLower95%0,140679-2.38384Upper95%0.

26、375031Fl限95.同上限9V-2.383840.37503118工业产值f0.0553260.0188142.940648Q.0107430.0149730.0956780.0149730.09567816农业产值y-0Q0402Q：014Q29-0.28629Q,行8846F034110.026073-0.034110.02607320固定资刑0.0906940.0259893.4897060.0036080.0349530.1464350.0349530.14643521221232班解工DUALOUTPUTPROBABILITYOUTPUT2526观网IffiJ运输业产残差标准残差

27、百分比排僮输业产值y2713,40457-0.31457-1,033432.777775-1092823.617595-0.2176-0.7148518.3333333.222933,2473930.6326072,07825413.888893.4望日一“/Lq.RU中!性二._一一一力。11;小,MwM可归结至/直蛤座塘&上巳日2芥正巳七芸7|*|图4第一次回归结果3 .剔除异常变量X2（农业产值），用剩余的自变量Xi、X3与y回归（图5）,回归步骤无非是重复上述过程（参见图6,注意这里没设数据标志”），最后给出的回归结果（图7）。A1BCDE1序号年份工业产值xl固定资产投资x3运输业产

28、值y2ri197057.8214.543.09n3二2197158.0516.83414r3197259.1512.263.88J5rQ197363.S312,873.9n65197465.36IL653,2207.6197567.2612.873.7687197666.921683.5998197767.7910.934.03109197875.6514.71L34111rio197980.5717.564.6512rii198079.0220.324.7813r121981SO.5218.675.04J1413198286.8825.345.5915rI*19S395.4825.066.oin16ris1984109.7129.697.0317r161985126.5电3.8610.03118171986138.8948.910.8319181987160.5660.9812.9J图5剔除异常变量农业产值（x2）图6回归对话框的设置（不包括数据标志）从图7中容易读出回归结果：_2a=-0.89889,匕=0.051328,h=0.091229,R=0.994263,R=0.988558,s=0.324999,F=647.973,tb