Excel在统计学中的应用(2-6)

Excel在统计学中的应用统计分析常用的软件有SAS、SPSS和Excel等，其中Excel应用较为普遍。

本附录主要介绍Excel在统计中的应用，并用实例操作的形式进行介绍。

一、用Excel作数据的频率分布表和直方图利用Excel处理数据，可以建立频率分布表和条形图。

一般统计数据有两大类，即定性数据和定量数据。

定性数据用代码转化为定量数据后再处理，这里就不涉及了，下面主要以定量数据为例来说明如何利用Excel进行分组，并作频率分布表和直方图。

［资料］现有某管理局下属40个企业产值计划完成百分比资料如下：97、123、119、112、113、117、105、107、120、107、125、142、103、115、119、88、115、158、146、126、108、110、137、136、108、127、118、87、114、105、117、124、129、138、100、103、92、95、127、104(1)据此编制分布数列（提示：产值计划完成百分比是连续变量）；(2)计算向上累计频数（率）；(3)画出次数分布直方图。

［步骤］第1步：打开Excel界面，输入40个企业的数据，从上到下输入A列（也可分组排列）。

第2步：选择“工具”下拉菜单，如附图1－1。

附图1－1第3步：选择“数据分析”选项，如果没有该功能则要先行安装。

“数据分析”的具体安装方法，选择“工具”下拉菜单中“加载宏”，在出现的选项中选择“分析工具库”，并“确定”就可自动安装。

附图1－2第4步：在分析工具中选择“直方图”，如附图1－2。

附图1－3第5步：当出现“直方图”对话框时，在“输入区域”方框内键入A2:A41或$A$2：$A$41（“$”符号起到固定单元格坐标的作用，表示的是绝对地址），40个数据已输入该区域内，如果是分组排列的，就应选择整个分组区域。

在“接收区域”方框内键入C2：C9或$C$2：$C$9，所有数据分成8组（主要根据资料的特点，决定组数、组距和组限），把各组的上限输入该区域内。

在“输出区域”方框内键入E2或$E$2，也可重新建表在其他位置。

对话框中，还选择“累积百分率”、“图表输出”（如附图1－3）。

最后：点“确定”，就可得到结果。

对话框内主要选项的含义如下：输入区域：在此输入待分析数据区域的单元格范围。

接收区域（可选）：在此输入接收区域的单元格范围，该区域应包含一组可选的用来计算频数的边界值。

这些值应当按升序排列。

只要存在的话，Excel 将统计在各个相邻边界值之间的数据出现的次数。

如果省略此处的接收区域，Excel 将在数据组的最小值和最大值之间创建一组平滑分布的接收区间。

标志：如果输入区域的第一行或第一列中包含标志项，则选中此复选框；如果输入区域没有标志项，则清除此该复选框，Excel 将在输出表中生成适宜的数据标志。

输出区域：在此输入计算结果显示的单元格地址。

如果不输入具体位置将覆盖已有的数据，Excel 会自动确定输出区域的大小并显示信息。

柏拉图：选中此复选框，可以在输出表中同时显示按升序、降序排列频率数据。

如果此复选框被清除，Excel 将只按升序来排列数据。

累积百分比：选中此复选框，可以在输出结果中添加一列累积百分比数值，并同时在直方图表中添加累积百分比折线。

如果清除此选项，则会省略以上结果。

图表输出：选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。

［结果］有关结果如附图1－4。

完整的结果通常包括三列和一个频数分布图，第一列是数值的区间范围，第二列是数值分布的频数（不是频率），第三列是频数分布的累积百分比。

附图1－4直方图是用矩形的宽度和高度来表示频数分布的图形。

绘制直方图时，将所研究的变量放在横轴上，频数、频率放在纵轴上。

每组的频数、频率在图上就是一个长方形，长方形的底在横轴上，宽度是组距，长方形的高就是对应的频数或频率。

应当注意，上图实际上是一个条形图，而不是直方图，若要把它变成直方图，可按如下操作：用鼠标左键单击图中任一直条形，然后右键单击，在弹出的快捷菜单中选取“数据系列”格式，弹出数据系列格式对话框。

在对话框中选择“选项”标签，把“分类间距”宽度改为0，按确定后即可得到直方图，如附图1－5所示。

附图1－5二、用Excel作常用统计图Excel有较强的作图功能，可根据需要选择各类型的图形。

Excel提供的统计图有多种，包括柱形图、条形图、折线图、饼图、散点图、面积图、环形图、雷达图、曲面图、气泡图、股价图、圆柱图、圆锥图等，各种图的作法大同小异。

（一）饼图的绘制饼图也称圆形图，是用圆形及圆内扇形的面积来表示数值大小的图形。

饼图主要用于表示总体中各组成部分所占的比例，对于研究结构性问题十分有用。

［资料］据中国互联网络信息中心2006年6月底的统计，我国目前网民的年龄分布如下表（附表2－1），根据资料利用Excel绘制饼图。

我国目前网民的年龄分布结构表先把数据输入到工作表中，如附图2－1所示，可按下面的步骤操作。

第1步：选择“插入”下拉菜单，选择“图表”。

第2步：在图表类型中选择“饼图”，然后在子图表类型中选择一种类型，这里我们选用系统默认的方式。

然后单击下一步按钮，打开源数据对话框。

如附图2－2。

附图2－1附图2－2第3步：在图表源数据对话框中填入数据所在区域，单击下一步，在图表选项中，对“标题”、“图例”和“数据标志”适当处理。

如果要对图形修改，可用鼠标双击图表，然后用鼠标双击需要修改的部分，并进行修改。

［结果］即可得如附图2－3所示的饼图。

附图2－3（二）折线图的绘制折线图主要用于比较几类数据变动的方向和趋势，表现数据在不同时期发展变化的不同趋势。

［资料］根据我国2001－2005年外贸货物进出口总额资料（如附表2－2），绘制折线图，描述我国近年来货物进出口额的变化趋势。

［步骤］第1步：资料输入工作表后，选择“插入”下拉菜单，再选择“图表”。

第2步：在图表类型中选择“折线图”，然后在子图表类型中选择一种类型，这里我们选用如附图2－4的方式。

然后单击下一步按钮，打开源数据对话框。

附图2－4第3步：在源数据对话框中，“数据区域”中输入相关资料（可用用鼠标点击并框定数据区域）。

再在“系列”的“分类（x）轴标志”区域输入年份区域，如附图2－5。

第4步：资料输入后的下一步，进入“图表选项”。

分别对“标题”、“坐标轴”、“网格线”、“图例”、“数据标志”和“数据表”等选项进行设置，当然设置各选项时根据需要进行取舍。

最后点“完成”，就在工作表中得到折线图。

附图2－5［结果］经过上述各步骤，在工作表中得到折线图，如附图2－6所示。

附图2－6三、用Excel 计算描述统计量 我们学习了平均指标，也掌握了测定数据的集中趋势和离散程度的常用统计量，下面将利用Excel 来计算这些统计量。

为了说明该方法，仍用实例操作。

（一）利用“数据分析”功能计算 ［资料］设某班40名学生《统计学》考试成绩分别为：66 89 88 84 86 87 75 73 72 68 75 82 97 58 81 54 79 76 95 7671 60 90 65 76 72 76 85 89 92 64 57 83 81 78 77 72 61 70 81对该班学生的考试成绩进行描述统计分析。

［步骤］第1步：在Excel的工作表界面中，输入40个学生的成绩数据，从上到下输入A列，放入区域“A1：A40”的单元格中。

第2步：选择“工具”下拉菜单，再选择“数据分析”选项。

第3步：在分析工具中选择“描述统计”，如附图3－1。

附图3－1第4步：当出现对话框时，在“输入区域”方框内键入A1：A40(或用鼠标选择这区域)，在“输出选项”中选择输出区域(在此选择C4)，再选择“汇总统计”(该选项给出全部描述统计量)；最后：选择确定。

［结果］其计算结果如附图3－2所示。

附图3－2［结果分析］（二）利用“统计函数”工具计算描述统计量除上述“数据分析”功能计算外，还可采用Excel的函数工具计算。

仍以40名学生《统计学》考试成绩为例进行计算。

［步骤］第1步：在Excel的工作表界面中，输入40个学生的成绩数据，从上到下输入A列，放入区域“A1：A40”的单元格中。

第2步：选择“插入”下拉菜单，再选择“函数”选项，如附图3－3。

附图3－3第3步：出现的“插入函数”界面中，在“或选择类别”选项中，选“统计”。

再在“选择函数”中，选“A VERAGE”（算术平均数），点击确定如附图3－4。

第4步：出现“A VERAGE函数参数”界面中，在“Number1”中键入A1：A40(或用鼠标选择这区域)，然后点“确定”，就能得出“算术平均数＝76.525”。

附图3－4重复上述各步骤，还可计算“调和平均数”、“几何平均数”、“样本标准差”和“总体标准差”等统计量，只是要分别选择相应的函数。

常用描述统计量函数如附表3－2所示。

EXCEL中常用描述统计量函数对照表四、用Excel进行随机抽样使用Excel进行抽样，首先要对各个总体单位进行编号，编号可以按随机原则，也可以按有关标志或无关标志，具体可参见教材有关抽样的章节，编号后，将编号输入工作表。

［资料］假定有80个总体单位，每个总体单位给一个编号，共有从1到80个编号，输入工作表后如附图4－1所示：附图4－1［步骤］输入各总体单位的编号后，可按以下步骤操作：第1步：单击工具菜单，选择“数据分析”选项，打开“数据分析”对话框，从中选择“抽样”，如附图4－2所示。

附图4－2第2步：单击“抽样”选项，弹出“抽样”对话框，如附图4－3。

附图4－3第3步：在输入区域框中输入总体单位编号所在的单元格区域，在本例是$A$1:$H$10，系统将从A列开始抽取样本，然后按顺序抽取B列至H列。

如果输入区域的第一行或第一列为标志项（横行标题或纵列标题），可单击标志复选框。

第4步：在抽样方法项下，有周期和随机两种抽样模式：“周期”模式即所谓的等距抽样，采用这种抽样方法，需将总体单位数除以要抽取的样本单位数，求得取样的周期间隔。

如我们要在80个总体单位中抽取10个，则在“间隔”框中输入8。

“随机模式”适用于纯随机抽样、分类抽样、整群抽样和阶段抽样。

采用纯随机抽样，只需在“样本数”框中输入要抽取的样本单位数即可；若采用分类抽样，必须先将总体单位按某一标志分类编号，然后在每一类中随机抽取若干单位，这种抽样方法实际是分组法与随机抽样的结合；整群抽样也要先将总体单位分类编号，然后按随机原则抽取若干类作为样本，对抽中的类的所有单位全部进行调查。

可以看出，此例的编号输入方法，只适用于等距抽样和纯随机抽样。

第5步：指定输出区域，在这里我们输入$A$12，单击确定后，即可得到抽样结果。

［结果］8个随机抽取的样本编号就显示在区域“A12：A19”单元格中。