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

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

2、”图标；或者在插入”菜单中打开图表（H）图表向导的图标为的。选中数据后，数据变为蓝色（图2）。MicrosoftEhcel-连维10年是大积雪评度和灌幽面积的数据匕.国）文件旧捐羯d）视图插入工）格式®J工具X）数据皿菌口但L今19后1号口甯亶一，嗫芝在外HUE二=最大枳雪深度内米）ABcD1年份最大积雪深度冗怵）灌溉面积y（千田）2197128.63197210.419.345197319M2L21g.640.5X586197E26.448.97197623.445S197713.E29.29197816.73411019792446.711198019.137.41图表向导-4步

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

4、，可见数据分析选项（见图5）:图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值的输入区域是不一样的：前者包括数据标志:最大积雪深度x（米）灌溉面积y（千亩）后者不包括。这一点务请注意（图8）。图8-1包括数据标志图8-2不包括数

5、据标志3.再后，确定，取得回归结果（图9）。|三餐尊建田国曾志rA>i）由胃口南二华后和却,|gimi|口启马占1（30电X"工力射川D四二网r丁，BJH五亘五/犬制闾±.k二Ju1=_DCDEFMIK1IT12345&7E二FTIF.丁回归统计Kultit-leRiJ兆乂1户RSam.sre0仃81口收Mjuftc1R;露空。0.3112料在误专1把空可魏肯1:上差:才年日asMMCT：”.辛其丁*-：，1Fg101L113H1:1171Wig!2i.上df监H占FElenlfka时1-回归分析1，1及4别”皿蜀耍51，1匚E.铝0321小RK苣差81G.l

6、f-'7；O42.OIXidF总计97E%罪1二|HCoefficient存相猥差:t£tmt-Pfalue-LeezIJbpcr艮95,艮95,施ti«r尔.母土：1.能躅将整1.船91抑0,233粥¥-1方涿4皈£小财-1：的M六打器01景却增隔度“血.1町期16.胡颔鹤10一般处.5K必施.L5韩1MT韩.£然砌L5剑第.2探加工陶£m口红ETTFUT冢州情史铁圆和r装斗L过还血-IT3I2S3El.二：!：.:-.<!：.,3*L宜时TL2M强45一山口2仃-olm鹏酊：550.21T5&-l.sirsi

7、6,7?8T?0.22120T25.3XB72.3691277:6羽度1,467F9029g北北懿M0幽幽1。H氏口圣。*止?6/3版至平方和|,»»15叮诘早上ir.，1二，:口生式二乂雷帖钟版/坨工，PMtlh_1I上厂口旗后卬中、二I口四回植."言.图9线性回归结果4.最后，读取回归结果如下：截距：a2.356;斜率：b1.813;相关系数：R0.989;测定系数：R20.979;F值：F371.945;t值：t19.286;标准离差（标准误差）：s1.419；回归平方和：SSr748.854；剩余平方和：SSe16.107；y的误差平方和即总平方和：SSt

8、764.961。5.建立回归模型，并对结果进行检验模型为：?2.3561.813x至于检验，R、R2、F值、t值等均可以直接从回归结果中读出。实际上，R0.9894160.632Ro.o58,检验通过。有了R值，F值和t值均可计算出来。F值的计算公式和结果为:R20.9894162371.9455.32F0.05,8(1R2)2(10.989416)1011显然与表中的结果一样。T值的计算公式和结果为:0.97941610.979416.101119.2862.306t0.05,8回归结果中给出了残差（图10）据此可以计算标准离差。首先求残差的平方n100.174 16.107,于是标准（yi

9、出）2,然后求残差平方和Si21.724i1离差为n2(yi?)2i116.1071.419于是DWn(i i i)2i 2n2i 1_22(1.911 1.313)(0.417 0.833)(1.313)2( 1.911)20.41720.751取显然,0.05, k1 , n 10 (显然 v 10 1 18 ）,查表得dl0.94 , du 1.29DW=0.751 dl 0.94 ,可见有序列正相关，预测的结果令人怀疑观测值瞿溉面积y残差:残差平方129.9您4-1.31283811.723544标准离差s221,21082-L9108173.651222L418923905_340.

10、79036-0.29036450.084312q36.07677-0.47676970.22T30S峋的均值550.21755-1.317554L7359490.03S342702644.77879Q2212091650439331726,830872.36912775.612766832.632221.467780292.154379945,866540.33345652。694651036.983230.416769730.173697残差平方和16.106762.013345sy1.41936.530.03881015%0.10.15图10y的预测值及其相应的残差等进而，可以计算DW值（

11、参见图11）,计算公式及结果为好差L9残差2-1U残差之差残差之差的平方-1.312838-L910817二。.5979738893575F8752-L910817元90克5L6204525012625g6630T-0.290365-0.476什7,186405232003474691-0.47677-L317554-0.8407243050.70691S24S-1.317554Q.22120922. 36912770.22120921.5387631942. 3677921682. 36912771. 4677803-0. 9013474070. 8124271492.1479孺5414,

12、613554Q591.46778Q30.8334565-0.534323773Q,4023666490.33345650.41676g7-0.4166867830717：3627875DWft0,4167697残差之差的平方和12.094878128750919图11利用残差计算DW值利用Excel快速估计模型的方法:2 .用鼠标指向图4中的数据点列，单击右键，出现如下选择菜单（图12）:灌溉面积八千亩）0102030图122.点击添加趋势线？"，弹出如下选择框（图13）:图133 .在分析类型”中选择线性（L）”，然后打开选项单（图14）:图144 .在选择框中选中显示公式（E1和

13、显示R平方值？”（如图14）,确定，立即得到回归结果如下（图15）:图表标题0102030灌溉面积丫（千亩）一线性（灌溉面积y（千亩）图15在图15中，给出了回归模型和相应的测定系数即拟合优度。顺便说明残差分析：如果在图8中选中残差图（D）”，则可以自动生成残差图（图12）XVariable1ResidualPlot-320-2X Variable 11差 n残0-1图16图 17):回归分析原则上要求残差分布是无趋势的，如果在图中添加趋势线，则趋势线应该是与轴平行的，且测定系数很小。事实上，添加趋势线的结果如下（XVariable1ResidualPloty=-9E-15x+2E-13R2=

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

15、1980年之后（图18）。ABCDEF11年份最大积雪*剽费咨）灌溉面积式千可计算值Coefficients2197115.22W.6|1llnrtercept2.3564379293197210.419.8最大积雪深度x（米）L8129210654197321,240.5518.6瓯66197326.44S-9715764458197713.52丸2916.7341102446.71119801%137.i121闻27,3图182.将光标至于图18所示的D2单元格中，按等于号，”，点击F2单元格（对应于截距a=2.356）,按F4键，按加号，点击F3单元格（对应于斜率b=1.812）,按F4

16、键，按乘号“*'；点击B2单元格（对应于自变量xi）,于是得到表达式“二$F$2+$F$3*B2”（图19）,相当于表达式?iab*xi,回车，立即得到?29.9128,即1971年灌溉面积的计算值。LABCDBF1年份最大积雪深度密立灌溉面积近千西请算值Coefficients21971：15-22.5=$F就书F$3由2|2.3564379293197210.419.3最大积雪深度式米）1.8129210654197321.240.55197413.635.6619T526.44氏97197623.4458197713.529.29197816,734.110197rg2446.7

17、1119901瓦137.412193127.51-图193.将十字光标标至于D2单元格的右下角，当粗十字变成细十字以后，按住鼠标左键，往下一拉，各年份的灌溉面积的计算值立即出现，其中1981年对应的D12单元格的52.212即我们所需要的预测数据，即有？1152.212千亩（图20）。1ABDEF|1年份最大积雪深度始冏灌溉面积y（千直1计算值Coef-ficients2197115.223,629.913Inrtercept2.356437929319721。.今19.321,211最大积雪深度K休）1.8129210654197321.240.540.795197418.635.636.0

18、77619752S.448.950.2167197623.%4544.7798197713.529.226.S319197816.73虫132.6321019792446.745.S67n1980电137.436.98312198127.552.212图204.进一步地，如果可以测得1982年及其以后各年份的数据，输入单元格B13及其下面的单元格中，在D13及其以下的单元格中，立即出现预测数值。例如，假定1982年的最大积雪深度为x1223.7米，可以算得?1245.323千亩；1983年的最大积雪深度为X1315.7,容易得到？1331.819千亩（图21）1ABCDEF11年份最大积雪深度

19、£米）灌溉面积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.96711198019.137,436.98312198127.552.21213193223.745,32314193315.7

20、30.819图21预测结果（19811983）最后大家思考一下为什么DW检验对本例中的问题未必有效?2.利用Excel进行多元线性回归分析【例】某省工业产值、农业产值、固定资产投资对运输业产值的影响分析。Excel2000的操作方法与一元线性回归分析大同小异：第一步，录入数据（图1）ABCDEF1序号年份工业产值n农业产值kN固定黄产投资必运输业产值y21137057.8227.0514.54工0332197158.OS28.89IB.833.443197259.1533.0212.263.8854197363.8335,2312.873.9651974G5.3G24.9411.653.227

21、6197567.2632.9512.87*7627197666.9230.3510.S3.59g8197767.7938.710.934.0310g19TB75,6547.9914.71L34：1110197g80.5754.1817.564.6512ii198079.0250.7320.32也731312198180.5259.8518.675.0141319S2S6-3S6L5725.345.59151983近4870.9725.06&,0116151984109,71SI,5429,697.0317161985126.594.014S.8S10.03181713SC138.E91

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

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

24、0.00402,b30.090694,R0.994296,_2R0.988625,s0.335426,F405.5799,tbi2.940648,tb20.28629,tb33.489706。根据残差数据，不难计算DW值，方法与一元线性回归完全一样。根据回归系数可以建立如下多元线性模型：?1.00440.55326x10.00402x20.090694x3由于X2的回归系数b2的符号与事理不符，b2的t检验值为负，b2的绝对值很小,可以判定，自变量之间可能存在多重共线性问题。ABCDEFGHI：L1SUHMAYOUTPUT23回归统计4Multiple0.994296RSquare0.9886

25、25eAdjusted氏9861877标港误差0.335426Sg观测值18ID方差分析iidfSS胞FjnificancE?F12回口分析3136.895845.6319240A5f9gL71E-1413用差141.575144011125114总计17138.470915lbCoefficieiT标准误差tSiatRvalueLcwer95%Upper9渊坪艮95.的艮95,0117Intercept-L00440.643156-L56168i).140679-2.383840*3750312383840.375031IB工业产值¥0.0553260.018S149406480.

26、0107430.0149730.09567S0,0149730.09567819在北产值'-0.0U4U20.014029-0.28629U.778B46-0.C34110.026073-0.U3乳二U0260720固定小Q,0906940,您9893.4斯U6Q,OO35U80.0349530.146435D.0349530.1464352122232iRESIDUALOLJTPUTFRObfkBILITYOUTPJT2526观测值J运输业产理标性戏差E分比排值输业产道y2713.40457-0.31457-1,033432.7777783.092323.617595-0.2176-

27、0.力兆5&3333333,222933.2473930.6326072.07825413.888893.4aAOz1r7。011MALTO1fli44Q亡4M4卜卜叵三绪里/京曲数据（Sheer2/Snr?et3/图4第一次回归结果3 .剔除异常变量X2（农业产值），用剩余的自变量XI、X3与y回归（图5）,回归步骤无非是重复上述过程（参见图6,注意这里没设数据标志”），最后给出的回归结果（图7）。ABcDE1序号年份匚业产值观固定资产投资如运输业产值产21197057.8214,543.093219715B.0516.833.443197259.1512,263.835417363

28、.8312.87396519n65.3611.653.227&197567.2612.873.768T197666.9210.33.59981*767.7910.93L0310917875,6514.711,弘111。197rg80.5717.56L651211198079.0220.324,79131Z18180.5218.675.011316286,S825.345,55L514：198395,4825,066.0116151584=10S.7129,690317IS185126.543.8610.0318171加6138.394即910.S319181987160.5660.9812.9图5剔除异常变量农业产值（x2）图6回归对话框的设置（不包括数据标志）从图7中容易读出回归结果：2_a0.89889,bi0.051328,00.091229,R0.994263,R0.988558,s0.324999,F647.973,tbi4.200968,33.6322