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 计量单位：元