统计学实验指导书EXCEL.docx
《统计学实验指导书EXCEL.docx》由会员分享，可在线阅读，更多相关《统计学实验指导书EXCEL.docx（34页珍藏版）》请在冰点文库上搜索。
统计学实验指导书EXCEL
EXCEL实验指导书
注意：
由于简装版的EXCEL中没有“数据分析”菜单，因此需要进行以下操作步骤来添加该菜单，前提是计算机中必须有OFFICE2010的原始安装软件，否则下列操作无法完成。
“文件”——“选项”——“加载项”——EXCEL加载项“转到”——“加载宏”——选择“分析工具库”——确定。
完成以上操作后“数据”菜单中出现“数据分析”则操作成功。
实验一利用Excel对数据进行描述统计分析
一、实验目的
1、掌握Excel的基本知识
2、学会应用Excel创建表格，输入和编辑信息
3、熟练运用excel的公式和函数求各种统计指标
4、利用Excel的分析工具对数据进行描述性统计
5、掌握组距式变量数列的编制原理
二、实验要求
1、掌握Excel的基本操作方法
2、通过练习，能够独立运用Excel进行数据整理和数据分析
3、掌握利用Excel对数据进行分组编制的基本操作方法；
三、实验内容
1、分别用函数和数据分析工具计算这31个地区人口的总和、平均值、中位数、众数、标准差。
表1-12008年全国各地区人口统计
地区
总人口（万人）
地区
总人口（万人）
北京
1695
山东
9417
天津
1176
河南
9429
河北
6989
湖北
5711
山西
3411
湖南
6380
内蒙
2414
广东
9544
辽宁
4315
广西
4816
吉林
2734
海南
854
黑龙江
3825
重庆
2839
上海
1888
四川
8138
江苏
7677
贵州
3793
浙江
5120
云南
4543
安徽
6135
西藏
287
福建
3604
陕西
3762
青海
554
宁夏
618
新疆
2131
甘肃
2628
江西
4400
2、根据抽样调查，某月X市50户居民购买消费品支出资料如下（单位：
元）：
表1-2某月X市50户居民购买消费品支出
830
880
1230
1100
1180
1580
1210
1460
1170
1080
1050
1100
1070
1370
1200
1630
1250
1360
1270
1420
1180
1030
870
1150
1410
1170
1230
1260
1380
1510
1010
860
810
1130
1140
1190
1260
1350
930
1420
1080
1010
1050
1250
1160
1320
1380
1310
1270
1250
根据以上数据，以900、1000、1100、1200、1300、1400、1500、1600为组限，对居民户月消费支出额编制组距式变量数列，并计算居民户月消费支出额的累计频数和频率。
同时对分组资料用各种统计图的形式来加以表现。
四、实验步骤
1．用EXCEL函数计算这31个地区人口的总和、平均值、中位数、众数、标准差
（1）如图1.1所示，在工作表中输入数据。
（2）熟悉统计常用函数及其功能
函数
功能
函数
功能
函数
功能
COUNT
记数
NORMSINV
Z值计算
MEDIAN
中位数
MAX
最大值
ABS
绝对值
MODE
众数
MIN
最小值
CHIINV
卡方值
QUARTILE
四分位数
SUM
求和
TINV
T值计算
VAR
方差
SUMSQ
平方和
FINV
F值计算
STDEV
标准差
AVERAGE
平均值
SQRT
平方根
GEOMEAN
几何平均数
（3）使用函数进行统计计算。
（以求数据的总和为例）
1选定E2单元格，单击工具栏上的“公式——插入函数”命令，出现下图1.2所示对话框，在“选择类别”后的对话框中选择“全部”，在“选择函数”中选择求和函数“SUM”，然后点击确定。
②将鼠标置于“Mumber1”右段的编辑框内，在工作表中选择B2：
B32的区域，单击“确定”按钮，如下图1.3所示。
③则在E2单元格中显示数据“130827”为所求的数据总和。
④使用同样的方法分别求的表中数据的平均值、中位数、众数、标准差为下图1.4所示。
2、用分析工具试计算这31个地区人口的总和、平均值、中位数、众数、标准差。
（1）点击“数据—数据分析”，打开“数据分析”对话框，如下图1.5所示。
（2）选择“描述统计”功能，单击“确定”，系统打开描述统计对话框，如下图所示。
选定数据的输入区域为B2：
B32单元格；分组方式选择“逐列”；选定一个输出区域为D9单元格；再选定“汇总统计”复选框，如图1.6。
（3）系统输出2008年我国各地区人口描述统计的计算结果如图1.7所示。
（4）在系统的输出中，“平均”即均值X，反映了全国各地区人口的平均水平；“标准误差”为样本均值的标准差，反映了用平均值代表性的大小，本例中标准误差的值很大，说明平均值的代表性差，各地区的人口数量差异比较大；“众数”即出现次数最多的标志值，由于本例中31个标志值互不相同，故没有众数；“标准差”为总体标准差；“方差”为总体方差；本例中峰度的值大于零，表示分布比正态分布更集中在平均数周围，分布呈尖峰状态；偏度的值大于零，说明分布呈正偏斜，即大部分标志值是大于平均值的。
2、对居民户月消费支出额编制组距式变量数列，
（1）数据输入。
分别输入“居民消费品支出”和组限。
如图1.8所示
（2）执行菜单命令[数据]→[数据分析]，调出“数据分析”对话框，选择“直方图”选项，调出“直方图”对话框。
（3）在“直方图”对话框中，输入相关数据，见下图1.9。
输入区域：
$A$1:
$J$5
接收区域：
$A$7:
$A$14，接收区域的数值应按升序排列
输出区域：
$B$7（为输出结果左上角单元格地址）
选中“标志”复选框。
柏拉图：
选中此复选框，可以在输出表中同时按降序排列频率数据。
如果此复选框被清除，Excel将只按升序来排列数据。
累积百分比：
选中此复选框，可以在输出表中添加一列累积百分比数值，并同时在直方图表中添加累积百分比折线。
如果清除此选项，则会省略累积百分比。
图表输出：
选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。
本例中选中“累积百分比”和“图表输出”两个复选框。
（4）实验结果见下图1.10
（5）由图中可知，对分组资料进行整理后可得如下结果
表1-3某月X市50户居民购买消费品支出分组资料
组名
居民购买消费品支出分组
居民户数
1
900以下
5
2
900～1000
1
3
1000～1100
10
4
1100～1200
10
5
1200～1300
10
6
1300～1400
7
7
1400～1500
4
8
1500～1600
2
9
1600以上
1
实验二Excel图表制作及编辑
一、实验目的
1．掌握Excel的图表创建
2．熟悉Excel图表编辑技巧
二、实验要求
1．掌握Excel的基本图表创建操作方法。
2．通过练习，能够独立运用Excel图表进行数据整理和数据分析。
三．实验内容
1．统计图表的建立；
2．学习、练习Excel的图表编辑；
四．实验步骤：
1、统计图表的创建与编辑，以表2-1为例，建立柱形图。
表2-12005—2009年我国普通高等教育、中等职业教育及普通高中招生人数（万人）
年份
普通高等教育
中等职业教育
普通高中
2005
504
656
878
2006
546
748
871
2007
566
810
840
2008
608
812
837
2009
640
874
830
（1）将表2-1中的数据输入Excel表格中，如下图所示
（2）移动鼠标到工具栏“插入”按钮，点击图表中的
窗口后，
从中选择想要的图表类型，用鼠标单击该图形，如下图所示。
（3）如下图所示出现空白图形，在图形的任何位置点击鼠标右键，在菜单中点击“选择数据”，给图形添加横轴和纵轴的数据。
（4）点击“选择数据”按钮出现如图所示的对话框，在“图例项”下的“添加”中分别将
“普通高等教育”“中等职业教育”“普通高中”三个系列的“系列名称”“系列值”添加到图中。
在“水平轴标签”中“编辑”年份
（6）如图2.6所示，在图表工具中选择图表布局类型，并分别修改“标题”、“横轴”、“纵轴
（７）在如图所示，用鼠标右键单击图中某个系列的图标，出现如图所示的编辑对话框，点击“添加数据标签”，在图中显示每个系列的数值。
（８）图表基本内容完成以后，需要修改某部分内容时，将鼠标移动到所修改部位，点击鼠标右键，在所列菜单中进行相应的修改工作，最终建立的图表如下图所示
2、利用下表中的数据制作饼形图
表2-2桂林电子科技大学信息科技学院教师职称分布情况表
职称
比例
高级职称
30%
硕士学位教师
35.8%
本科学位教师
34.2%
（1）将表2-2中的数据输入Excel表格中，如图所示
（2）移动鼠标到工具栏“插入”按钮，点击图表中的
窗口后，
从中选择想要的图表类型，用鼠标单击该图形，如下图所示。
（3）如图所示，出现“选择源数据”窗口后，选择“图表数据区域”中选择要建立图表的数据区域A2:
B4，再单击下一步按钮。
（4）如图所示，在图表工具中选择图表布局类型“布局1”。
（5）修改“图表标题”，并移动图中的数据位置，得到最终的饼形图如下：
实验三Excel区间估计
一、实验目的:
掌握Excel区间估计方法。
二、实验流程：
1.进入Excel.
2.使用直方图进行初步分析。
3.利用“描述统计”计算平均数和标准差。
4.进行区间估计。
三、实验内容
以下数据为50辆汽车传动系统出现故障时所行驶的实际里程数。
85092
32609
59465
77437
32534
64090
32464
59902
39323
89641
94219
116803
92857
63436
65605
85861
64342
61978
67998
59817
101769
95774
121352
69568
74276
66998
40001
72069
25066
77098
69922
35662
74425
67202
118444
53500
79294
64544
86813
116269
37831
89431
73341
85288
138114
53402
85586
82256
77539
88798
求曾经出现过传动系统问题的汽车总体中在出现传动系统问题时所行驶里程均值的95%置信区间。
四、实验步骤
1、首先将分析数据输入Excel工作表Sheet1单元格A1:
J5。
如图所示：
2、利用“函数”分别计算平均值和标准差（步骤同实验一），得到计算结果为平均里程为73342.1，标准偏差为24899.9。
3、进行区间估计
[1].单击存放结果的单元格E7。
[2].在单元格E7输入公式：
“=73342.1-CONFIDENCE（0.05,24899.9,50）”，其中函数CONFIDENCE功能为返回均值的置信区间，其使用方法同实验一中所讲函数的使用方法相同，见下图。
然后再按Enter键，得到结果为66440.34。
[2].在在单元格E8输入公式：
“=73342.1+CONFIDENCE（0.05,24899.9,50）”，按Enter键，得到结果为80243.86。
得到区间估计结果为80243.86。
如图所示
实验四一元线性性回归模型
一、实验目的
1．掌握用excel一次性算出回归模型参数的方法和步骤；
2．正确分析输出结果并得出正确的回归模型。
二、实验内容：
某地区1994年到2002年的人均收入和商品零售总额的数据如下：
表4.1地区1994年到2002年的人均收入和商品零售总额
年份
人均收入X（元）
商品零售总额（Y亿元）
1994
450
26
1995
550
32
1996
680
44
1997
730
62
1998
810
70
1999
930
89
2000
1050
103
2001
1160
115
2002
1250
128
试配合适当的回归方程；若2003年该地区人均收入为1300元，试估计2003年商品零售总额为多少？
三、操作步骤
1．在excel的工作表中输入如表4.1所示的人均收入X和商品零售总额Y的样本数据。
2．点击主菜单中的“插入”菜单，在弹出的子菜单中点击“图表”选项，在标准类型选项下选择“XY散点图”，点击下一步，在弹出对话框的输入区域，拖动鼠标选择人均收入X和商品零售总额Y的样本值；系列产生在，选择列；得到散点图如图4.1所示。
由图4.1可以看出人均收入X和商品零售总额Y之间存在很明显的线性关系，可以建立一元线性回归模型。
3．点击主菜单中的“数据”菜单，在弹出的子菜单中点击“数据分析”在出现的数据分析对话框中选择回归，如图4.2所示。
图4.2应用excel软件求回归分析相关参数
4．点击图4.2所示对话框中的确定，弹出“回归”对话框，在Y值输入区域，拖动鼠标选择Y样本值C2:
C10，在X值输入区域，拖动鼠标选择X样本值B2:
B10,“置信度”输入95%，“输出区域”为A12，如图4.3所示。
图4.3应用excel软件求回归分析相关参数
5．点击图4.3所示中的确定，弹出回归分析有关参数的窗口，如图4.4所示。
图4.4应用excel软件求回归分析相关参数
四、结果分析
“回归统计”中MultipleR为复相关系数；RSquare为可决系数R2=0.988786
由图4.4的输出结果，可以得到本例中的回归系数为aˆ=-38.68166,bˆ=，0.1336577
故所求回归方程为Yˆ=-38.68166+0.1336577X，由于相关系数r=0.99
可知两变量是高度相关的，说明该回归模型和回归方程合理反映了人均收入和商品零售总额的相
关关系，可以用来进行预测和控制。
当人均收入X=1300元时，用回归方程可以估计2003年商品零售总额为135.07335。
实验五长期趋势分析
一、实验目的
1、掌握用移动平均法、趋势方程法和利用函数进行长期趋势分析的方法与步骤；
2、测定发展速度和平均发展速度、增长量和平均增长量。
二、实验内容：
1、表5.1所示为我国2001-2009年国内生产总值，请根据所给数据，分别用移动平均法、趋势方程法和利用函数对2010年的国民生产总值进行预测。
表5.1所示为我国2001-2009年国内生产总值
年份
国内生产总值（亿元）
2001
109655
2002
120333
2003
135823
2004
159878
2005
184937
2006
216314
2007
265810
2008
314045
2009
335353
2、计算发展速度和平均发展速度、增长量和平均增长量的值
三、实验步骤：
1.移动平均法
（1）在excel的工作表中输入如表5.1所示的年份和国民国民生产总值的样本数据。
点击“数据—数据分析—移动平均”，出现数据分析对话框，如图5.1所示。
图5.1数据分析对话框
（2）点击“确定”按钮，出现“移动平均”对话框，在其中设置各个参数，输入区域为：
$B$2:
$B$10；输出区域为：
$C$2；选中“图表输出”复选框。
如图5.2所示。
图5.2移动平均对话框
（3）．点击“确定”按钮，即可得到经过移动平均之后的预测数值和绘制的长期趋势图，如图5.3所示
图5.3用移动平均法得到的长期趋势线
2、趋势方程法
（1）点击主菜单中的“数据”菜单，在弹出的子菜单中点击“数据分析”，在出现的数据分析对话框中选择“回归”。
弹出“回归”对话框，在Y值输入区域，拖动鼠标选择国内生产总值B2:
B10，在X值输入区域，拖动鼠标选择年份值A2:
A10,“置信度”输入95%，“输出区域”为C2，如图5.4所示。
图5.4应用excel软件求回归分析相关参数
（2）点击图5.4所示中的确定，弹出回归分析有关参数的窗口及模型拟合值图，如下图5.5所示。
（3）由表中数据可得直线趋势方程为Yˆ=-59956611.72+30005.63333X，将X=2010带入趋势方程可得，2010年我国国内生产总值的预测值为354711.2733
3、利用函数进行趋势预测
（1）打开“公式”菜单，选择“插入函数”，在“函数分类”中选择“全部”，在“函数名”中选择“FORECAST”,按确定键，如下图5.5所示。
（2）在弹出的“函数参数”对话框中的“X”中输入预测期的时间代码“2010”；
在“KNOWN-YS”中输入国内生产总值的样本数据；在“KNOWN-XS”中输入时间代码；点击“确定”按钮。
如下图5.6所示。
（3）最后返回的数值“354711.3”即为对2010年我国国内生产总值的预测值。
4、测定增长量和平均增长量
根据1995-2001年河北省国内生产总值，计算逐期增长量、累计增长量和平均增长量。
如图5.7所示：
图5.7用EXCEL计算增长量和平均增长量资料及结果
计算步骤如下：
第一步：
在A列输入年份，在B列输入国内生产总值。
第二步：
计算逐期增长量：
在C3中输入公式：
=B3-B2，并用鼠标拖曳将公式复制到C3：
C8区域。
第三步：
计算累计增长量：
在D3中输入公式：
=B3-$B$2，并用鼠标拖曳公式复制到D3：
D8区域。
第四步：
计算平均增长量（水平法）：
在C10中输入公式：
=（B8-B2）/5，按回车键，即可得到平均增长量。
5、测定发展速度和平均发展速度
以1995-2001年河北省国内生产总值为例，说明如何计算定基发展速度、环比发展速度和平均发展速度。
如图5.8所示：
图5.8用EXCEL计算发展速度和平均发展速度资料及结果
计算步骤如下：
第一步：
在A列输入年份，在B列输入国内生产总值。
第二步：
计算定基发展速度：
在C3中输入公式：
=B3/$B$2，并用鼠标拖曳将公式复制到C3：
C8区域。
第三步：
计算环比发展速度：
在D3中输入公式：
=B3/B2，并用鼠标拖曳将公式复制到D3：
D8区域。
第四步：
计算平均发展速度（水平法）：
选中C10单元格，单击插入菜单，选择函数选项，出现插入函数对话框后，选择GEOMEAN（返回几何平均值）函数，在数值区域中输入D3：
D8即可。
实验六Excel综合指数计算实验
一、实验目的
1、掌握综合指数的计算原理；
2、学会应用Excel软件进行综合指数计算。
二、实验要求：
1、复习综合指数的计算及Excel的基本操作方法；
2、认真观察实验结果，记录结果；并对实验结果作简要分析总结。
三、实验内容
下表是五种商品的销售资料：
商品销售额计算表
商品
类别
计量
单位
商品价格（元）
销售量
销售额（百元）
大米
猪肉
食盐
服装
电视机
百公斤
公斤
500克
件
台
300.0
18.0
1.0
100.0
4500.0
360.0
20.0
0.8
130.0
4300.0
2400
84000
10000
24000
510
2600
95000
15000
23000
612
7200
15120
100
24000
22950
9360
19000
120
29900
26316
7800
17100
150
23000
27540
8640
16800
80
31200
21930
合计
—
—
—
—
—
69370
84696
75590
78650
要求计算拉氏价格指数和销售量指数。
四、实验步骤和内容
1、输入相关数据，如附图6.1所示。
2、在F2单元格中输入公式“=B2*D2”，在G2单元格中输入公式“=C2*D2”，在H2单元格中输入公式“=E2*B2”。
3、选定F2：
H6单元格，执行菜单命令：
[开始]→[填充]→[向下填充]，将F2、G2、H2单元格中的公式复制到它们下面四行的单元格中。
4、在B8单元格中输入公式“=sum（G2:
G6）/sum（F2:
F6）”，在B9单元格中输入公式“=sum（H2:
H6）/sum（F2:
F6）”。
即可得到如下结果图6.2
图6.2
五、实验结果
销售量拉氏指数为113.38%，报告期与基期相比较，五种商品销售量有增有减，平均来讲上升了13.38%；价格的拉氏指数为108.97%，报告期与基期相比较，五种商品价格有增有减，平均来讲上升了8.9%。
升级会员 