实验二用EXCEL进行相关与回归分析

1、实验二 用EXCEL进行相关及回归分析实验目的：用EXCELS行相关及回归分析 实验内容：1、相关分析2、回归分析实验步骤：采用下面的例子进行相关和回归分析。【例1】10个学生身高和体重的情况如下表，要求对身高和体重作相关和回归分析。学生身高（公分）体重（公斤）11715321675631776441544951695561756671635281524791725810160501.用excels行相关分析首先把有关数据输入 EXCEL勺单元格中，如图1-20所示 用EXCEL进行相关分析有散点图、计算相关系数，另一种是图1-20 EXCEL数据集(1) 作散点图(2) 利用函数计算相关系数

2、在EXCE冲，提供了两个计算两个变量之间相关系数的方法， CORRE函数和PERS0函数，这两个函数是等价的，这里介绍用 CORRE函数计算相关系数：第一步：单击任一个空白单元格，单击插入菜单，选择函数 选项，打开粘贴函数对话框，在函数分类中选择统计，在函数名 中选择CORREL单击确定后，出现 CORRE对话框。第二步：在 arrayl中输入B2： B11，在array2中输入 C2：C11，即可在对话框下方显示出计算结果为，如图1-21所示。pCOMEL心TBtl= 171；167；177；154HreyC |C2 ：C11玉1 二 f53;56:&4；虧二 0. S9602T60

3、5 返回两组数佰的相关系数.第二爼数值单走楮区域-型计算錯果二3.89&027305| 确定 jQ取消 |图1-21 CORREL对话框及输入结果(3) 用相关系数宏计算相关系数第一步：单击工具菜单，选择数据分析选项，在数据分析选 项中选择相关系数，弹出相关系数对话框，如图1-22所示：辐入区械：|mi11分爼万式:存屋列©广虚疔血F标吉泣丁第一竹（13取消1帮肋也J 1停塚比区腆d -制工作養爼吵厂硼工体暉他；FFSl|iJJl图1-22相关系数对话框第二步：在输入区域输入$B$1:$C$1,分组方式选择逐列，选择标志位于第一行，在输出区域中输入 $E$1，单击确定，得输出

4、结果如图1-23所示图1-23 相关分析输出结果在上面的输出结果中，身高和体重的自相关系数均为 1,身高 和体重的相关系数为，和用函数计算的结果完全相同。2.用EXCEL进行回归分析EXCEL进行回归分析同样分函数和回归分析宏两种形式，其 提供了 9个函数用于建立回归模型和预测。这 9个函数分别是:INTERCEPT SLOPE RSQ FORECAST返回线性回归模型的截距 返回线性回归模型的斜率 返回线性回归模型的判定系数 返回一元线性回归模型的预测 值STE YXTRENDGROWTH计算估计的标准误 计算线性回归线的趋势值 返回指数曲线的趋势值LINEST返回线性回归模型的参数LOGE

5、ST返回指数曲线模型的参数用函数进行回归分析比较麻烦，我们这里介绍使用回归分析 宏进行回归分析。第一步：单击工具菜单，选择数据分析选项，出现数据分析 对话框，在分析工具中选择回归，如图1-24所示。图1-24数据分析对话框第二步：单击确定按钮，弹出回归对话框，在 Y值输入区域 输入$B$2: $B$11，在X值输入区域输入$C$2: $C$11,在输出选 项选择新工作表组，如图1-25所示。图1-25 回归对话框第三步：单击确定按钮，得回归分析结果如图Hr11tUK* ikJ Nh九任 i +S# 口 iJ=1 ZJ »* 掰卫丨 10 口 5 | 申助阳 iLJlU山厶I 4伴|辑

6、也i1-26所示A I“ | |IMMIA R Y nt; rr«r r bb1 r - !i«e N 1= |i fe*1<s IZ1> 1n 1G J inm*J 1 Trf ! 14M u I 1: a |»I I >J 1yR T|j iri. - -h fl ftPfl 1-p*>r貝I Li. I Ti ID-7 7 H J； 2峠 *1.1-1# 4 *丹1 1/ 1 1 ft 1 j 5芒叫耳13.£ O-p 11 >1 »四忌甲E3r卩 if l卍卄Lif-1 7TVFT «J 1I r

7、T15 -3 3 - Z-B 3 ?A *3 rtf - a H 3 >s'a . s a b so. o-n-43出崑鼻1 7 9* 7 14 1Il 总.2 |i TRCrK._f±*SzAi1 ,C f f f1c u 幵>n -中上："»t; E *t 督口一斗门 “ -.jL. rrsijr r* ¥ GX厂讣耳莊尸*也=in*n- fb_ ”n-* *f n 4 a s *- L- * >fl9 Il 4 号 2"ft,. ? ri ih > * t1 *1.勇段斗叩”专pOs"1 J Hj

8、丿尺斗此1畔M V ! « H! «z p r.i tr 1 'i -、*r. _ y 11 n 11 -.i .iB n e i 11 « i 中 r 3 V t 已11 _ iF« *ik lb fIT .' L =- *-.1 cZ ' r 111 /ft恃 - J 1| 4 j 11p - nQ "finn1»l Hr F图1-26 EXCEL回归分析结果在上面的输出结果中， 第一部分为汇总统计， MultipleR 指复 相关系数， RSquare 指判定系数， Adjusted 指调整的判定系数，

9、标准误差指估计的标准误， 观测值指样本容量； 第二部分为方差 分析，df指自由度，SS指平方和，MS指均方，F指F统计量， Significaneeof F指p值；第三部分包括：Intercept 指截距，Coefficient 指系数， tstat 指 t 统计量。实验三 用EXCEL进行预测实验目的：用EXCEL进行预测实验内容：一、用移动平均法进行预测二、用指数平滑法进行预测三、趋势预测法进行预测实验步骤：【例13-13 ：某煤矿某年1-11月份采煤量如下表:月份丿量月份丿量172839410511612一、用移动平均法进行预测具体步骤：第一步：将原始数据录入到单元格区域 A2: A12

10、,如图13-31所示:2事'|口呎譬 I 1 1 L i t L1234E-r Tnnn.FII1J1B 1:. i « < 1 L ' / ! Ill" - ! /1 - 11 1 1 1 1 1in HLAi图13-31EXCEL数据集第二步：选择菜单条上的“工具”一一“数据分析”命令，弹出如图13-32所示的对话框:图13-32数据分析对话框第三步：在“分析工具”框中选择“移动平均”，单击“确定”按钮，弹出移动平均对话框，相应作如下输入，即可得到如图13-33所示的对话框：(1) 在“输出区域”内输入：$A$2: $A$12,即原始数据所在的 单

11、元格区域。(2) 在“间隔”内输入：3，表示使用三步移动平均法。(3) 在“输出区域”内输入：B2，即将输出区域的左上角单元 格定义为B2。(4) 选择“图表输出”复选框和“标准误差”复选框。13-33移动平均对话框第四步：单击“确定”按钮，便可得到移动平均结果，如图13-34所示：分析：在图中，B4： B12对应的数据即为三步移动平均的预测值;单元格区域C6: C12即为标准误差。13-34移动平均分析结果二、用指数平滑法进行预测：第一步：将原始数据输入到单元格 B2: B12;第二步：选择菜单条上的“工具”一一“数据分析”命令， 弹出如图13-35所示的对话框:方差分析；可朿目对丙寺甘靳

12、盯養牡迈：无重复双因素分祈 相关系数檢方差描删计翱軍均图13-35数据分析对话框第三步：在“分析工具”中选择“指数平滑”，单击“确定”按钮，弹出一个对话框，作相应输入，即可得到如图13-36所示的对话框；13-36指数平滑对话框第四步：单击“确定”按钮，即可得到指数平滑结果，如图13-37所示:3 U1O 1 19b I'D* V怖* 卩彳吐1¥-00 Ort l= A q L" i s p R b-TLJlniHk V-M- d 3I 3 d-M&: . a 4 7 1-,心札下 rL-l Ta-ETFI+g 丹刖 iRFTETEM图13-37指数平滑结果

13、三、趋势预测法进行预测第一步：把相关数据输入到EXCEL中，其中月份输入A1 A11单元格，月产量输入 B1 B11单元格，如图13-38所示:图13-38EXCEL数据集第二步：在工作表中选择一个空的单元格。在这里我们选择D2单元格。第三步：选择插入下拉菜单。第四步：选择函数选项。第五步：当函数对话框出现时：在函数类别框中选择统计，在函数名字中选择 FORECAS预测），如图13-39所示：OE3II窗謝分半画彖尹5迴I -FIJEEBISV-一 II,"”：OfLE 匚2TfKMUlUEWUXQRDWTHKAKJflEAfl讥氏厂liiTl：吕T尸口RECAST （%巧 S口世口

14、.耳奇）诵诅一需卿生 冋归 祖台出诩 冋一个干茴弭HI恒nLSIFTESTCi AW MALI 丄 ST -MIMATW G-AHMAOT -JEOMEAH图13-39粘贴函数对话框第六步：单击确定按钮，出现预测对话框，在x中输入12,在 know-y's 中输入 B1: B11，在 know-x's 中输入 A1: All,如图13-40所示：1£ 1121£Knowrc-y*| B1 h 1J1<9 03:3 g 9. 12 tKtlcmwviti_m=:|A1 All1 ； 2 ；3； 4：5： B：7： S=0.3065*15455 通过-，来

15、线性回归坝合歿返回一呼预训值.KIWS& i丛滿®鼎种啊合百織尸钿的点疾中诜HI酌-则己丸的*満.计耳结果=y. 3yt5S4h*5b图13-40FORCAST寸话框第七步：单击确定按钮，预测结果出现在D2单元格中，如图13-41所示:图13-41趋势预测法预测结果实验四用EXCEL进行时间序列分析实验目的：用EXCEL进行时间序列分析实验步骤：一、测定增长量和平均增长量【例13-5 ：根据1995-2001年河北省国内生产总值，计算逐期增长量、累计增长量和平均增长量。如图13-16图13-16用EXCEI计算增长量和平均增长量资料及结果计算步骤如下：第一步：在A列输入年份，

16、在B列输入国内生产总值。第二步：计算逐期增长量：在 C3中输入公式：=B3-B2,并用 鼠标拖曳将公式复制到 C3: C8区域。第三步：计算累计增长量：在 D3中输入公式：=B3-$B$2,并 用鼠标拖曳公式复制到 D3: D8区域。第四步：计算平均增长量(水平法)：在C10中输入公式：= (B8-B2) /5，按回车键，即可得到平均增长量。二、测定发展速度和平均发展速度【例13-6 ：以1995-2001年河北省国内生产总值为例，说 明如何计算定基发展速度、环比发展速度和平均发展速度。如图13-17图13-17用EXCEL计算发展速度和平均发展速度资料及结果第一步：在A列输入年份，在B列输入

17、国内生产总值。第二步：计算定基发展速度：在 C3中输入公式：=B3/$B$2， 并用鼠标拖曳将公式复制到 C3: C8区域。第三步：计算环比发展速度：在 D3中输入公式：=B3/B2，并 用鼠标拖曳将公式复制到 D3: D8区域。第四步：计算平均发展速度（水平法）：选中C10单元格，单 击插入菜单，选择函数选项，出现插入函数对话框后，选择 GEOMEAN返回几何平均值）函数，在数值区域中输入D3: D8即可。三、计算长期趋势【例13-7 ：我们用某企业某年12个的总产值资料来说明如- .STS.47 3(5-f Z333 33E33r-ULIIE _1 rj暑十 n wpi=tiEi平吗 四

18、tiB 平坤 _ 口苗尸 平 研H三3 IEB322>G1 S fa.i %峠 i 、f lZEi社 H I *专口二g吕” 3鬧二盹 EtJ I *-、百匚| 阳ftyitl Ijfik-i Hmi45rE .吕333 3S3SU-* F隅 ：jR7 0专£亿!=；召宇3 .See. bE JI O5E7.3S3 333S聞P 布E £ J. 5厂空36图13-18用EXCELS算长期趋势资料及结果第一步：在A列输入月份，在B列输入总产值。第二步：计算三项移动平均：在 C3中输入“ =(B2+B3+B413 ”，并用鼠标拖曳将公式复制到 C3: C12区域。第三步：

19、计算四项移动平均：在 D4中输入“ =SUM( B2: B5) /4 ”，并用鼠标拖曳将公式复制到 D4: D12区域。第四步：计算二项移正平均数：在E4中输入“ =(D4+D5 /2 ”， 并用公式拖曳将公式复制到 E4： E11区域。四、计算季节变动【例13-8 ：利用某种商品五年分季度的销售额资料，说明如何用移动平均趋势剔除法测定季节变动。如图13-19图13-19用EXCEL计算季节变动资料第一步：按图上的格式在 A列输入年份，在B列输入季别， 在C列输入销售收入。第二步：计算四项移动平均：在 D3中输入“ =SUM(C2: C4) /4 ”，并用鼠标拖曳将公式复制到 D3: D19区

20、域。第三步：计算趋势值(即二项移动平均)T:在E4中输入“=(D3+D4 /2 ”，并用鼠标拖曳将公式复制到 E4: E19区域。第四步：剔除长期趋势，即计算Y/T :在F4中输入“ =C4/E4”， 并用鼠标拖曳将公式复制到 F4: F19区域。第五步：重新排列F4: F19区域中的数字，使同季的数字位 于一列，共排成四列。第六步：计算各年同季平均数：在B29单元格中输入公式: =average(B25:B28)；在 C29中输入公式=average(C25:C28)；在D29中输入公式=average(D24:27)；在 E29中输入公式=average(E24:E27)。第七步：计算调整

21、系数：在B31中输入公式：=4/sum(B29:E29)第八步：计算季节比率：在 B30中输入公式：=B29*$B$31,并用鼠标拖曳将公式复制到单元格区域B30： E30,就可以得到-Jl-Mil季节比率的值，具体结果见图13-20 :MKta le . ilhi -Th - li zh 5 rJSm黏 网血 中 5*yHtn net.:十 ri| *4 里 J IM2324ZE严即zso:一IN琳孟响事ErOH SB9T3 = U.C嗚已瓷忘 u ii dt-13 d ml 7- i2-J, - d冇叮“f52Sheet 1 FiJIS1 P二：于苹亠牛毕MF1 . 1 47?TH2ila

22、 -iA.iCf . 1 r1- LB31ddBSB d 3EB&O41. iDMaiQflfiV 4 i K Q 174311 17 n-. 91 WU j-'i' 1 JD0>.<137 311 fj'S 3 li£!. 3 D. -ai .1J-! ! ! OGL 1 . 11GQ »,. d 1 1 J1f 'I! ErfS H UWI图13-20用EXCELS算季节变动结果指数分析实验目的：用EXCELS行指数分析实验步骤：指数分析法是研究社会经济现象数量变动情况的一种统计分 析法。指数有总指数及平均指数之分，在

23、这一节我们介绍如何用 Excel进行指数分析及因素分析。一、用Excel计算总指数【例13-9 ：图中是某企业甲、乙、丙三种产品的生产情况，以基期价格p作为同度量因素，计算生产量指数。如图13-21图13-21用EXCEL计算总指数资料及结果计算步骤：第一步：计算各个pOqO：在G2中输入“ =C2*D2，并用鼠标 拖曳将公式复制到 G2: G4区域。第二步：计算各个p0*q1 :有H2中输入“ =C2*F2”并用鼠 标拖曳将公式复制到 H2: H4区域。第三步：计算艺pOqO和艺p0q1 :选定 G2: G4区域，单击工 具栏上的“艺”按钮，在 G5出现该列的求和值。选定 H2: H4区 域

24、，单击工具栏上的“艺”按钮，在 H5出现该列的求和值。第四步：计算生产量综合指数lq=艺pOql/艺pOqO :在C6中输入“ =H5/G5”便可得到生产量综合指数注意：在输入公式的时候，不要忘记等号，否则就不会出现 数值。二、用Excel计算平均指数现以生产量平均指数为例，说明加权算术平均法的计算方法。【例13-10 ：图中的A1: A4区域内是某企业生产情况的统 计资料，我们要以基期总成本为同度量因素， 计算生产量平均指 数。如图13-22图13-22用EXCELS算平均指数资料及结果计算步骤:第一步：计算个体指数 k=q1/qO :在F2中输入“ =D2/C2”。 并用鼠标拖曳将公式复制到 F2： F4区域。第二步：计算k*pOqO并求和。在G2中输入“ =F2*E2”并用 鼠标拖曳将公式复制到 G2: G4区域。选定G2: G4区域，单击工 具栏上的：“艺”按钮，在 G5列出现该列的求和值。第三步：计算生产量平均指数：在C7中输入“ =G5/E5”即得到所求的值。三、用Excel进行因素分析【例13-11 ：我们还用上面的例子，有关资料如图13-23怦 V IPH- r i I4WW in W