实验二用EXCEL进行相关与回归分析.docx
《实验二用EXCEL进行相关与回归分析.docx》由会员分享,可在线阅读,更多相关《实验二用EXCEL进行相关与回归分析.docx(16页珍藏版)》请在冰豆网上搜索。
实验二用EXCEL进行相关与回归分析
实验二用EXCEL进行相关与回归分析
实验目的:
用EXCEL进行相关与回归分析
实验内容:
1、相关分析
2、回归分析
实验步骤:
采用下面的例子进行相关和回归分析。
【例1】10个学生身高和体重的情况如下表,要求对身高和体重作相关和回归分析。
学生
身高(公分)
体重(公斤)
1
2
3
4
5
6
7
8
9
10
171
167
177
154
169
175
163
152
172
160
53
56
64
49
55
66
52
47
58
50
1.用EXCEL进行相关分析
首先把有关数据输入EXCEL的单元格中,如图1-20所示。
用EXCEL进行相关分析有散点图、计算相关系数,另一种是利用相关分析宏。
图1-20EXCEL数据集
(1)作散点图
(2)利用函数计算相关系数
在EXCEL中,提供了两个计算两个变量之间相关系数的方法,CORREL函数和PERSON函数,这两个函数是等价的,这里介绍用CORREL函数计算相关系数:
第一步:
单击任一个空白单元格,单击插入菜单,选择函数选项,打开粘贴函数对话框,在函数分类中选择统计,在函数名中选择CORREL,单击确定后,出现CORREL对话框。
第二步:
在array1中输入B2:
B11,在array2中输入C2:
C11,即可在对话框下方显示出计算结果为0.896,如图1-21所示。
图1-21CORREL对话框及输入结果
(3)用相关系数宏计算相关系数
第一步:
单击工具菜单,选择数据分析选项,在数据分析选项中选择相关系数,弹出相关系数对话框,如图1-22所示:
图1-22相关系数对话框
第二步:
在输入区域输入$B$1:
$C$1,分组方式选择逐列,选择标志位于第一行,在输出区域中输入$E$1,单击确定,得输出结果如图1-23所示。
图1-23相关分析输出结果
在上面的输出结果中,身高和体重的自相关系数均为1,身高和体重的相关系数为0.896,和用函数计算的结果完全相同。
2.用EXCEL进行回归分析
EXCEL进行回归分析同样分函数和回归分析宏两种形式,其提供了9个函数用于建立回归模型和预测。
这9个函数分别是:
INTERCEPT
返回线性回归模型的截距
SLOPE
返回线性回归模型的斜率
RSQ
返回线性回归模型的判定系数
FORECAST
返回一元线性回归模型的预测值
STEYX
计算估计的标准误
TREND
计算线性回归线的趋势值
GROWTH
返回指数曲线的趋势值
LINEST
返回线性回归模型的参数
LOGEST
返回指数曲线模型的参数
用函数进行回归分析比较麻烦,我们这里介绍使用回归分析宏进行回归分析。
第一步:
单击工具菜单,选择数据分析选项,出现数据分析对话框,在分析工具中选择回归,如图1-24所示。
图1-24数据分析对话框
第二步:
单击确定按钮,弹出回归对话框,在Y值输入区域输入$B$2:
$B$11,在X值输入区域输入$C$2:
$C$11,在输出选项选择新工作表组,如图1-25所示。
图1-25回归对话框
第三步:
单击确定按钮,得回归分析结果如图1-26所示。
图1-26EXCEL回归分析结果
在上面的输出结果中,第一部分为汇总统计,MultipleR指复相关系数,RSquare指判定系数,Adjusted指调整的判定系数,标准误差指估计的标准误,观测值指样本容量;第二部分为方差分析,df指自由度,SS指平方和,MS指均方,F指F统计量,SignificanceofF指p值;第三部分包括:
Intercept指截距,Coefficient指系数,tstat指t统计量。
实验三用EXCEL进行预测
实验目的:
用EXCEL进行预测
实验内容:
一、用移动平均法进行预测
二、用指数平滑法进行预测
三、趋势预测法进行预测
实验步骤:
【例13-13】:
某煤矿某年1-11月份采煤量如下表:
月份
产量
月份
产量
1
2
3
4
5
6
9.03
9.06
9.12
8.73
8.94
9.30
7
8
9
10
11
12
9.15
9.36
9.45
9.30
9.24
一、用移动平均法进行预测
具体步骤:
第一步:
将原始数据录入到单元格区域A2:
A12,如图13-31所示:
图13-31EXCEL数据集
第二步:
选择菜单条上的“工具”——“数据分析”命令,弹出如图13-32所示的对话框:
图13-32数据分析对话框
第三步:
在“分析工具”框中选择“移动平均”,单击“确定”按钮,弹出移动平均对话框,相应作如下输入,即可得到如图13-33所示的对话框:
(1)在“输出区域”内输入:
$A$2:
$A$12,即原始数据所在的单元格区域。
(2)在“间隔”内输入:
3,表示使用三步移动平均法。
(3)在“输出区域”内输入:
B2,即将输出区域的左上角单元格定义为B2。
(4)选择“图表输出”复选框和“标准误差”复选框。
13-33移动平均对话框
第四步:
单击“确定”按钮,便可得到移动平均结果,如图13-34所示:
分析:
在图中,B4:
B12对应的数据即为三步移动平均的预测值;单元格区域C6:
C12即为标准误差。
13-34移动平均分析结果
二、用指数平滑法进行预测:
第一步:
将原始数据输入到单元格B2:
B12;
第二步:
选择菜单条上的“工具”——“数据分析”命令,弹出如图13-35所示的对话框:
图13-35数据分析对话框
第三步:
在“分析工具”中选择“指数平滑”,单击“确定”按钮,弹出一个对话框,作相应输入,即可得到如图13-36所示的对话框;
13-36指数平滑对话框
第四步:
单击“确定”按钮,即可得到指数平滑结果,如图13-37所示:
图13-37指数平滑结果
三、趋势预测法进行预测
第一步:
把相关数据输入到EXCEL中,其中月份输入A1-A11单元格,月产量输入B1-B11单元格,如图13-38所示:
图13-38EXCEL数据集
第二步:
在工作表中选择一个空的单元格。
在这里我们选择D2单元格。
第三步:
选择插入下拉菜单。
第四步:
选择函数选项。
第五步:
当函数对话框出现时:
在函数类别框中选择统计,在函数名字中选择FORECAST(预测),如图13-39所示:
图13-39粘贴函数对话框
第六步:
单击确定按钮,出现预测对话框,在x中输入12,在know-y’s中输入B1:
B11,在know-x’s中输入A1:
A11,如图13-40所示:
图13-40FORCAST对话框
第七步:
单击确定按钮,预测结果出现在D2单元格中,如图13-41所示:
图13-41趋势预测法预测结果
实验四用EXCEL进行时间序列分析
实验目的:
用EXCEL进行时间序列分析
实验步骤:
一、测定增长量和平均增长量
【例13-5】:
根据1995-2001年河北省国内生产总值,计算逐期增长量、累计增长量和平均增长量。
如图13-16
图13-16用EXCEL计算增长量和平均增长量资料及结果
计算步骤如下:
第一步:
在A列输入年份,在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列输入国内生产总值。
第二步:
计算定基发展速度:
在C3中输入公式:
=B3/$B$2,并用鼠标拖曳将公式复制到C3:
C8区域。
第三步:
计算环比发展速度:
在D3中输入公式:
=B3/B2,并用鼠标拖曳将公式复制到D3:
D8区域。
第四步:
计算平均发展速度(水平法):
选中C10单元格,单击插入菜单,选择函数选项,出现插入函数对话框后,选择GEOMEAN(返回几何平均值)函数,在数值区域中输入D3:
D8即可。
三、计算长期趋势
【例13-7】:
我们用某企业某年12个的总产值资料来说明如何用移动平均法计算长期趋势。
如图13-18
图13-18用EXCEL计算长期趋势资料及结果
第一步:
在A列输入月份,在B列输入总产值。
第二步:
计算三项移动平均:
在C3中输入“=(B2+B3+B4)/3”,并用鼠标拖曳将公式复制到C3:
C12区域。
第三步:
计算四项移动平均:
在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区域。
第三步:
计算趋势值(即二项移动平均)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)。
第七步:
计算调整系数:
在B31中输入公式:
=4/sum(B29:
E29)
第八步:
计算季节比率:
在B30中输入公式:
=B29*$B$31,并用鼠标拖曳将公式复制到单元格区域B30:
E30,就可以得到季节比率的值,具体结果见图13-20:
图13-20用EXCEL计算季节变动结果
指数分析
实验目的:
用EXCEL进行指数分析
实验步骤:
指数分析法是研究社会经济现象数量变动情况的一种统计分析法。
指数有总指数与平均指数之分,在这一节我们介绍如何用Excel进行指数分析与因素分析。
一、用Excel计算总指数
【例13-9】:
图中是某企业甲、乙、丙三种产品的生产情况,以基期价格p作为同度量因素,计算生产量指数。
如图13-21
图13-21用EXCEL计算总指数资料及结果
计算步骤:
第一步:
计算各个p0q0:
在G2中输入“=C2*D2”,并用鼠标拖曳将公式复制到G2:
G4区域。
第二步:
计算各个p0*q1:
有H2中输入“=C2*F2”,并用鼠标拖曳将公式复制到H2:
H4区域。
第三步:
计算Σp0q0和Σp0q1:
选定G2:
G4区域,单击工具栏上的“Σ”按钮,在G5出现该列的求和值。
选定H2:
H4区域,单击工具栏上的“Σ”按钮,在H5出现该列的求和值。
第四步:
计算生产量综合指数Iq=Σp0q1/Σp0q0:
在C6中输入“=H5/G5”便可得到生产量综合指数
注意:
在输入公式的时候,不要忘记等号,否则就不会出现数值。
二、用Excel计算平均指数
现以生产量平均指数为例,说明加权算术平均法的计算方法。
【例13-10】:
图中的A1:
A4区域内是某企业生产情况的统计资料,我们要以基期总成本为同度量因素,计算生产量平均指数。
如图13-22
图13-22用EXCEL计算平均指数资料及结果
计算步骤:
第一步:
计算个体指数k=q1/q0:
在F2中输入“=D2/C2”。
并用鼠标拖曳将公式复制到F2:
F4区域。
第二步:
计算k*p0q0并求和。
在G2中输入“=F2*E2”并用鼠标拖曳将公式复制到G2:
G4区域。
选定G2:
G4区域,单击工具栏上的:
“Σ”按钮,在G5列出现该列的求和值。
第三步:
计算生产量平均指数:
在C7中输入“=G5/E5”即得到所求的值。
三、用Excel进行因素分析
【例13-11】:
我们还用上面的例子,有关资料如图13-23
图13-23用EXCEL进行因素分析资料及结果
进行因素分析的计算步骤如下:
第一步:
计算各个p0*q0和∑p0q0:
在G2中输入“C2*D2”,并用鼠标拖曳将公式复制到G2:
G4区域。
选定G2:
G4区域,单击工具栏上的“∑”按钮,在G5出现该列的求和值。
第二步:
计算各个p0*q1和∑p0*q1:
在H2中输入“=C2*F2”,并用鼠标拖曳将公式复制到H2:
H4区域。
选定H2:
H6区域,单击工具栏上的“∑”按钮,在H5出现该列的求和值。
第三步:
计算各个p1*q1和∑p1*q1:
在I2中输入“=E2*F2”,并用鼠标拖曳将公式复制到I2:
I4区域。
选定I2:
I4区域,单击工具栏上的“∑”按钮,在I5出现该列的求和值。
第四步:
计算总成本指数:
在C6中输入“=I5/G5”,即求得总成本指数。
第五步:
计算产量指数:
在C7中输入“=H5/G5”,即得产量指数
第六步:
计算单位成本指数:
在C8中输入“=I5/H5”即求得单位成本指数