统计学excel实验答案版.docx
《统计学excel实验答案版.docx》由会员分享，可在线阅读，更多相关《统计学excel实验答案版.docx（26页珍藏版）》请在冰点文库上搜索。
统计学excel实验答案版
Excel在统计学中的应用
用Excel搜集与整理数据
用Excel搜集数据
统计数据的收集是统计工作过程的基础性环节，方法有多种多样，其中以抽样调查最有代表性。
在抽样调查中，为保证抽样的随机性，需要取得随机数字，所以我们在这里介绍一下如何用Excel生成随机数字并进行抽样的方法。
需要提醒的是，在使用Excel进行实习前，电脑中的Excel需要完全安装，所以部分同学电脑中的office软件需要重新安装，否则实习无法正常进行。
本书中例题全部用Excel2007完成。
使用Excel进行抽样，首先要对各个总体单位进行编号，编号可以按随机原则，也可以按有关标志或无关标志，具体可参见本书有关抽样的章节，编号后，将编号输入工作表。
1.我们假定统计总体有200个总体单位，总体单位的编号从1到200，输入工作表后如图10-1所示：
图10-1总体各单位编号表
各总体单位的编号输入完成后，可按以下步骤进行操作：
第一步：
选择数据分析选项（如果你使用的是Excel2003，单击工具菜单，若无数据分析选项，可在工具菜单下选择加载宏，在弹出的对话框中选择分析工具库，便可出现数据分析选项；如果你使用的是Excel2007，点击左上角Office标志图标，Excel选项，加载项，在下面的管理下拉列表中选择“Excel加载项”，转到，勾选“分析工具库”，确定。
），打开数据分析对话框，从中选择抽样。
如图10-2所示。
图10-2数据分析对话框
第二步：
单击抽样选项，确定后弹出抽样对话框。
如图10-3：
图10-3抽样对话框
第三步：
在输入区域框中输入总体单位编号所在的单元格区域，在本例是$A$1:
$J$20，系统将从A列开始抽取样本，然后按顺序抽取B列至J列。
如果输入区域的第一行或第一列为标志项（横行标题或纵列标题），可单击标志复选框。
第四步：
选择“随机模式”，样本数为10。
在抽样方法项下，有周期和随机两种抽样模式。
“周期”模式即所谓的等距抽样（或机械抽样），采用这种抽样方法，需将总体单位数除以要抽取的样本单位数，求得取样的周期间隔。
如我们要在200个总体单位中抽取10个，则在“间隔”框中输入20；如果在200个总体单位中抽取24个，则在“间隔”框中输入8（如果不能整除，则取整数）。
“随机模式”适用于纯随机抽样、分类抽样、整群抽样和阶段抽样。
采用纯随机抽样，只需在“样本数”框中输入要抽取的样本单位数即可；若采用分类抽样，必须先将总体单位按某一标志分类编号，然后在每一类中随机抽取若干单位，这种抽样方法实际是分组法与随机抽样的结合；整群抽样也要先将总体单位分类编号，然后按随机原则抽取若干类作为样本，对抽中的类的所有单位全部进行调查。
由于本例中没有分类或分群的标志，所以无法进行分类或整群抽样，只适用于等距抽样和纯随机抽样。
本例中选择“随机模式”，即选择了纯随机抽样方法进行抽样。
第四步：
指定输出区域，在这里我们输入$A$22：
$A$31，单击确定后，即可得到抽样结果，如图10-4：
图10-4等距抽样结果
10.1.2用Excel进行统计分组
用Excel进行统计分组有两种方法，一是利用FREQUENCY函数；二是利用数据分析中的“直方图”工具。
我们介绍一下后者的操作方法。
【例10-2】某生产车间50名工人日加工零件数（单位：
个）资料输入工作表，如图10-5所示：
图10-5工人月产量资料
然后按以下步骤操作。
第一步：
分组标志输入。
分组前要先输入分组标志，选定一个区域（直方图菜单中称这个区域为接收区域），输入每一组的上限值，即在$A$7:
$A$13位置输入110，115，120，125，130，135，140。
第二步：
单击数据分析选项，从其对话框的分析工具列表中选择直方图，打开直方图对话框。
如图10-6所示：
图10-6直方图对话框
第三步：
在输入区域输入$A$1:
$J$5，输入区域指等分析数据的单元格区域。
在接收区域输入$A$7:
$A$13。
接收区域指的是分组标志所在的区域，即第一步输入的分组标志上限值。
第四步：
选择输出选项，可选择输入区域、新工作表组或新工作薄。
我们在这里选择输入区域，可以直接选择一个区域，也可以直接输入一个单元格（代表输出区域的左上角），这里我们推荐只输入一个单元格（本例为A16），因为我们往往事先并不知道具体的输出区域有多大。
第五步：
选择图表输出，可以得到直方图；选择累计百分率，系统将在直方图上添加累积频率折线；选择柏拉图，可得到按降序排列的直方图（本例中，由于没有选择柏拉图，所以不是降序排列）。
第五步：
按确定按钮，可得输出结果如图10-7：
图10-7频数分布和直方图
应当注意，上图实际上是一个条形图，而不是直方图，若要把它变成直方图，可按如下操作：
用鼠标左键单击直条，然后右键单击，在弹出的快捷菜单中选取“设置数据系列格式”，弹出数据系列格式对话框，如图10-8所示：
图10-8数据系列格式对话框
在对话框中将分类间距设置成0%，按关闭后即可得到直方图，如图10-9所示：
图10-9调整后的直方图
10.1.3用Excel作统计图
Excel提供的统计图有多种，包括柱形图、条形图、折线图、饼图、散点图、面积图、环形图、雷达图、曲面图、气泡图、股价图、圆柱图、圆锥图等，各种图的作法大同小异。
【例10-3】我们这里采用浙江省2008年各市GDP资料，看一下如何作饼图。
首先把数据输入到工作表中，如图10-10所示：
图10-10浙江省2008年各市GDP
按以下步骤可作出饼图：
第一步：
选中某一单元格，单击插入菜单，Excel2007菜单显示如图10-11：
图10-11插入图表菜单
（1）
第二步：
在图表类型中选择饼图，再选择二维饼图，如图10-12所示：
图10-12插入图表菜单
（2）
第三步：
单击选择数据菜单，在图表数据输入区域（D）中输入$A$1:
$B$12选择在源数据对话框中填入数据所在区域，如图10-13：
图10-13源数据对话框
第四步：
单击确定后得到如下饼图，如图10-14所示：
图10-14饼图
10.1.4用Excel作散点图
散点图是观察两个变量之间关系程度最为直观的工具之一，利用EXCEL的图表向导，可以非常方便的创建并且改进一个散点图，也可以在一个图表中同时显示两个以上变量之间的散点图。
【例10-4】我们这里采用我国1981到2000年的人均GDP和人均居民消费资料，看一下如何作散点图。
首先把数据输入到工作表中，如图10-15所示：
图10-15我国1981到2000年的人均GDP和人均居民消费资料
具体步骤如上例子相似，选择插入菜单，选择散点图，得散点图如图10-16：
图10-16散点图
10.2用EXCEL计算描述统计量
EXCEL中用于计算描述统计量的方法有两种，函数方法和描述统计工具的方法。
10.2.1用函数计算描述统计量
常用的描述统计量有众数、中位数、算术平均数、调和平均数、几何平均数、极差、四分位差、标准差、方差、标准差系数等。
一般来说，在Excel中求这些统计量，未分组资料可用函数计算，已分组资料可用公式计算。
这里我们仅介绍如何用函数计算。
【例10-5】：
已知某生产车间50名工人日加工零件数（单位：
个）输入表格后如图10-17：
图10-17工人日加工零件数
根据上面表格中的数据，利用函数求一些统计指标。
（1）算术平均数
单击任一单元格，输入“=AVERAGE（A1:
J5）”，回车后得算术平均数为122.74。
（2）众数
单击任一空单元格，输入“=MODE（A1:
J5）”，回车后即可得众数为123
（3）中位数
仍采用上面的例子，单击任一空单元格，输入“=MEDIAN（A1：
J5）”，回车后得中位数为123。
（4）标准差
单击任一单元格，输入“=STDEV（A1：
J5）”，回车后得标准差为7.74。
10.2.2描述统计工具量的使用
仍使用上面的例子，我们已经把数据输入到A1：
A30单元格，然后按以下步骤操作：
第一步：
数据菜单中选择数据分析，从其对话框中选择描述统计，按确定后打开描述统计对话框，如图10-18所示：
图10-18描述统计对话框
第二步：
在输入区域中输入$A$1:
$J$5，在输出区域中选择$B$14，其他复选框可根据需要选定，选择汇总统计，可给出一系列描述统计量；选择平均数置信度，会给出用样本平均数估计总体平均数的置信区间；第K大值和第K小值会给出样本中第K个大值和第K个小值。
分组方式选择“逐行”，则每行分别进行统计，这里有5行，则有5组统计结果。
第三步：
单击确定，可得输出结果，如图10-19所示：
图10-19描述统计输出结果
10.3用EXCEL进行时间序列分析
10.3.1测定增长量和平均增长量
【例10-6】：
根据我国1998-2007年国内生产总值，计算逐期增长量、累计增长量和平均增长量。
原始数据如图10-20，计算结果如图10-21。
图10-20我国1998-2007年国内生产总值
计算步骤如下：
第一步：
计算逐期增长量：
在C3中输入公式：
=B3-B2，并用鼠标拖曳将公式复制到C3：
C11区域。
第二步：
计算累计增长量：
在D3中输入公式：
=B3-$B$2，并用鼠标拖曳公式复制到D3：
D11区域。
第三步：
计算平均增长量（水平法）：
在C13中输入公式：
=（B11-B2）/9，按回车键，即可得到平均增长量。
图10-21用EXCEL计算增长量和平均增长量资料及结果
10.3.2测定发展速度和平均发展速度
【例10-7】：
以我国1998-2007年国内生产总值为例，说明如何计算定基发展速度、环比发展速度和平均发展速度。
如图10-22：
图10-22用EXCEL计算发展速度和平均发展速度资料及结果
第一步：
计算定基发展速度：
在C3中输入公式：
=B3/$B$2，并用鼠标拖曳将公式复制到C3：
C11区域。
第二步：
计算环比发展速度：
在D3中输入公式：
=B3/B2，并用鼠标拖曳将公式复制到D3：
D11区域。
第三步：
计算平均发展速度（水平法）：
选中D13单元格，单击插入菜单，选择函数选项，出现插入函数对话框后，选择GEOMEAN（返回几何平均值）函数，在数值区域中输入D3：
D11即可。
10.3.3计算长期趋势
【例10-8】：
我们用我国1988~2007年国内生产总值资料来说明如何用移动平均法计算长期趋势。
如图10-23：
图10-23用EXCEL计算长期趋势资料及结果
第一步：
计算三项移动平均：
在C3中输入“=（B2+B3+B4）/3”，并用鼠标拖曳将公式复制到C4：
C20区域。
第二步：
计算四项移动平均：
在D4中输入“=SUM（B2：
B5）/4”，并用鼠标拖曳将公式复制到D5：
D20区域。
第三步：
计算二项移正平均数：
在E4中输入“=（D4+D5）/2”，并用公式拖曳将公式复制到E5：
E19区域。
10.3.4计算季节变动
【例10-9】：
利用某种商品四年分季度的销售额资料，说明如何用移动平均趋势剔除法测定季节变动。
如图10-24：
图10-24用EXCEL计算季节变动资料
第一步：
按图上的格式在A列输入年份，在B列输入季别，在C列输入销售额。
第二步：
计算四项移动平均：
在D3中输入“=SUM（C2：
C4）/4”，并用鼠标拖曳将公式复制到D3：
D15区域。
第三步：
计算趋势值（即二项移动平均）T：
在E4中输入“=（D3+D4）/2”，并用鼠标拖曳将公式复制到E4：
E15区域。
第四步：
剔除长期趋势，即计算Y/T：
在F4中输入“=C4/E4”，并用鼠标拖曳将公式复制到F4：
F15区域。
第五步：
重新排列F4：
F15区域中的数字，使同季的数字位于一列，共排成四列。
（见图3-6）。
第六步：
计算各年同季平均数：
在B25单元格中输入公式：
=average（B22:
B24）；在C25中输入公式=average（C22:
C24）；在D25中输入公式=average（D21:
23）；在E25中输入公式=average（E21:
E23）。
第七步：
计算调整系数：
在B27中输入公式：
=4/sum（B25:
E25）
第八步：
计算季节比率：
在B26中输入公式：
=B25*$B$27，并用鼠标拖曳将公式复制到单元格区域B26：
E26，就可以得到季节比率的值，具体结果见图10-25：
图10-25用EXCEL计算季节变动结果
10.4用EXCEL进行指数分析
10.4.1用Excel计算总指数
【例10-10】：
设某粮油连锁店1998年和1999年三种商品的零售价格和销售量资料如图10-26。
试分别以基期销售量和零售价格为权数，计算三种商品的价格综合指数和销售量综合指数。
图10-26用EXCEL计算总指数资料及结果
计算步骤：
第一步：
计算各个p0q0：
在G3中输入“=C3*E3”，并用鼠标拖曳将公式复制到G3：
G5区域。
第二步：
计算各个p0*q1：
有H3中输入“=D3*E3”，并用鼠标拖曳将公式复制到H3：
H5区域。
第三步：
计算各个p1*q1：
有I3中输入“=D3*F3”，并用鼠标拖曳将公式复制到I3：
I5区域。
第四步：
计算Σp0q0和Σp0q1：
选定G3：
G5区域，单击工具栏上的“Σ”按钮，在G6出现该列的求和值。
选定H3：
H5区域，单击工具栏上的“Σ”按钮，在H6出现该列的求和值。
选定HI：
I5区域，单击工具栏上的“Σ”按钮，在I6出现该列的求和值。
第五步：
计算生产量综合指数Kq=Σp0q1/Σp0q0：
在B8中输入“=H6/G6”便可得到生产量综合指数。
第六步：
计算价格综合指数Kp=Σp1q1/Σp0q1：
在B9中输入“=I6/H6”便可得到价格综合指数。
注意：
在输入公式的时候，不要忘记等号，否则就不会出现数值。
10.4.2用Excel计算平均指数
现以生产量平均指数为例，说明加权算术平均法的计算方法。
【例10-11】：
仍以上例资料，计算生产量平均指数和价格平均指数。
如图10-27。
图10-27用EXCEL计算平均指数资料及结果
计算步骤：
第一步：
计算个体指数k=q1/q0：
在I3中输入“=D3/C3”。
并用鼠标拖曳将公式复制到I3：
I5区域。
第二步：
计算k*p0q0并求和。
在K3中输入“=I3*G3”并用鼠标拖曳将公式复制到K3：
K5区域。
选定K3：
K5区域，单击工具栏上的：
“Σ”按钮，在K6列出现该列的求和值。
第三步：
计算生产量平均指数：
在B7中输入“=K6/G5”即得到所求的值。
价格平均指数计算步骤略。
10.5用EXCEL进行相关与回归分析
【例10-12】：
中国居民人均消费支出与人均GDP（元/人）如表10-1所示：
表10-1中国居民人均消费支出与人均GDP计量单位：
元
年份
人均居民消费
人均GDP
CONSP
GDPP
1978
395.8
675.1
1979
437
716.9
1980
464.1
763.7
1981
501.9
792.4
1982
533.5
851.1
1983
572.8
931.4
1984
635.6
1059.2
1985
716
1185.2
1986
746.5
1269.6
1987
788.3
1393.6
1988
836.4
1527
1989
779.7
1565.9
1990
797.1
1602.3
1991
861.4
1727.2
1992
966.6
1949.8
1993
1048.6
2187.9
1994
1108.7
2436.1
1995
1213.1
2663.7
1996
1322.8
2889.1
1997
1380.9
3111.9
1998
1460.6
3323.1
1999
1564.4
3529.3
2000
1690.8
3789.7
10.5.1用EXCEL进行相关分析
首先把有关数据输入EXCEL的单元格中。
用EXCEL进行相关分析有两种方法，一是利用相关系数函数，另一种是利用相关分析宏。
（1）利用函数计算相关系数
在EXCEL中，提供了两个计算两个变量之间相关系数的方法，CORREL函数和PERSON函数，这两个函数是等价的，这里我们介绍用CORREL函数计算相关系数：
第一步：
单击任一个空白单元格，单击插入菜单，选择函数选项，打开粘贴函数对话框，在函数分类中选择统计，在函数名中选择CORREL，单击确定后，出现CORREL对话框。
图10-27EXCEL数据集
第二步：
在array1中输入B4：
B26，在array2中输入C4：
C26，即可在对话框下方显示出计算结果为0.996。
如图10-28和图10-29所示：
图10-28CORREL对话框及输入结果
图10-29EXCEL数据集
（2）用相关系数宏计算相关系数
第一步：
单击数据菜单，单击数据分析按钮，在数据分析选项中选择相关系数，弹出相关系数对话框，如图10-30和图10-31所示：
图10-30EXCEL数据集
图10-31相关系数对话框
第二步：
在输入区域输入$B$3:
$C$26，分组方式选择逐列，选择标志位于第一行，在输出区域中输入$E$5，单击确定，得输出结果如图10-32：
图10-32相关分析输出结果
在上面的输出结果中，身高和体重的自相关系数均为1，身高和体重的相关系数为0.996，和用函数计算的结果完全相同。
10.5.2用EXCEL进行回归分析
EXCEL进行回归分析同样分函数和回归分析宏两种形式，其提供了9个函数用于建立回归模型和预测。
这9个函数分别是：
INTERCEPT返回线性回归模型的截距
SLOPE返回线性回归模型的斜率
RSQ返回线性回归模型的判定系数
FORECAST返回一元线性回归模型的预测值
STEYX计算估计的标准误
TREND计算线性回归线的趋势值
GROWTH返回指数曲线的趋势值
LINEST返回线性回归模型的参数
LOGEST返回指数曲线模型的参数
用函数进行回归分析比较麻烦，我们这里介绍使用回归分析宏进行回归分析。
第一步：
单击工具菜单，选择数据分析选项，出现数据分析对话框，在分析工具中选择回归，如图10-33：
图10-33数据分析对话框
第二步：
单击确定按钮，弹出回归对话框，在Y值输入区域输入$B$2：
$B$11，在X值输入区域输入$C$2：
$C$11，在输出选项选择新工作表组，如图10-34所示：
图10-34回归对话框
升级会员 