用Excel进行数据整理与统计作图

上机实习一用Excel进行数据整理与统计作图
一、Excel数据分析程序的安装
运用Excel进行数据处理和统计分析时，Excel中必须安装Excel数据分析程序，即在Excel的“工具”菜单中应出现“数据分析”的命令选项，并将所要计算分析的变量按列（按行）输入。

当进入Microsoft Excel时，如果在“工具”菜单中没有“数据分析”命令，必须首先安装“分析工具库”。

其步骤为：
1．点击“工具”菜单中的“加载宏”，（见图1-9）
图1-9 “工具”菜单中的“加载宏”选项
2．在弹出的“加载宏”对话框中勾选“分析工具库”，（见图1-10）
图1-10 “加载宏”对话框中勾选“分析工具库”
3．点击确定。

此时即完成了Excel数据分析程序的安装，在Excel的“工具”菜单中就会出现“数据分析”的命令选项。

二、统计作图
这里我们以制作条形图为例，介绍用Excel软件进行统计作图的主要步骤。

例1.2根据2000年我国人口普查数据得到的我国6周岁以上人口按受教育程度分组形成的频数分布表：
试利用上表数据，建立Excel 数据集，并制作相应的垂直条形图。

解：现列出利用上表数据制作条形图的主要步骤：
1. 在Excel 中输入上表中的数据，建立如图1-11所示的数据集；
2. 选择“插入”下拉菜单的“图表”子菜单（见图1-11），进入图表向导；
图1-11 Excel 数据集 图1-12 “图表源数据”之“系列”对话框
3. 选择图表类型为 “柱形图”，再单击“下一步”；
4. 进入图表源数据窗口，确定用于制作图表的数
据区，在“数据区域”中选定“人数”数据（B1:B6），选定“系列产生在”为“列”；
5
．单击顶端“系列”标签（见图
1
-12），在“分类（X ）轴标志”空白区域，单击右端的
，回到数据集中用
鼠标选定数据值(A2:A6)，再单击右端（见图1-13），即回到“图表源数据”对话框；
图1-13
6. 单击“下一步”，就可对图表选项如标题、网格线、坐标轴等作选择(图1-14)；
图1-14 图表选项窗口 图1-15 单击“完成”后得到的条形图
7. 单击“完成”即可得到图1-15所
示的结果；
8. 在得到该条形图后，一般还需对坐标轴的字体大小、图例的取舍、
图形的大小等进行编辑调整，其方
法是：将光标移向需调整的区域，单击右键，进入编辑窗口，对相关项目进行重新选择，点击“确定”，即可得到如下列图1-16所示的条形图。

图1-16 经过调整后的垂直条形图
对于其他统计图形，如饼图（圆形图）、折线图、累积折线图、线图（XY 散点图）等的制作步骤与上述条形图的制作基本类似。

三、定量数据的频数分布表与直方图的生成
这里我们以处理下列例1.3数据为例，介绍如何用Excel 编制频数分布表并生成直方图。

对于定性数据频数分布表的生成方法，与此类似。

例1.3 现有20名学生的某门课成绩如下所示，
74 93 62 88 86 51 97 73 77 81 85 67 92 60 84 80 78 90 85 81
试对这些成绩数据编制频数分布表，并生成相应的直方图。

解：现列出用Excel 编制频数分布表并生成直方图的主要步骤：
1. 将上列成绩数据输入Excel 中，建立Excel 数据集（参见图1-17）；
2.
对成绩按组距=10进行分组，在数据表的空白列输入分组的边界值（这里主要是各组的组上限），并按升序排列，作为制作直方图的“接收区域”。

注意Excel 编制频数分布表时各组计算频数将包含组上限，故取组上限为59、69、79、89、100（见图1-17）。

图1-17 “数据分析”对话框
3. 选择“工具”下拉菜单的“数据分析”子菜单，用鼠标双击数据分析对话框中的“直
方图”选项（图1-17）；
（注意：Excel 中必须已经先按“工具/加载宏”的命令，安装了“分析工具库”后，“工具” 下拉菜单中才有“数据分析”子菜单。

） 4． 进入“直方图”对话框，其主要选项为（参见图1-18）：
图1-18 “直方图”对话框
（1）输入区域：选定要处理的数据区域，这里为成绩数据范围（A1:A21）；
（2）接收区域：选定作为分组边界值（主要是各组上限）的数据范围，这里是第2 步建立的C4:C9单元格范围；
（3）标志：因输入数据区域的第一行是标志项“成绩”，故选定该项；
（4）累积百分比：选定时频数分布表的结果中将有累积百分比数值，并在直方图中出现累积百分比折线图。

（5）图表输出：选定时将在输出频数分布表的同时，生成直方图。

5．根据需要选定“直方图”对话框中的选项后，单击“确定”即得初步结果（图1-19）。

图1-19 频数分布表和直方图的初步结果
6.在频数分布表的结果中删除“其他”所在行（第7行），则图中“其他”及对应部
分也就消失。

7.在直方图中双击任一直条，即可进入“数据系列格式”，点击“选项”标签，将“间
距宽度”的值150改为0（图1-20），还可以点击“数据标志”标签选定“显示值”，再单击“确定”即可得到直条间无间隔的直方图。

8. 对直方图的大小和字体大小等作适当调整，就可得到图1-21所示的直方图。

如果用已有的频数分布表数据来生成直方图，则可以按照本节一统计作图中垂直条形图的制作步骤先生成条形图，再应用上面第6、7步即可得到直方图。

上机训练题一
1．在1997年我国的国内生产总值中，第一产业为13969亿元，第二产业为36770亿元，第三产业为24033亿元，试用Excel来绘制1997年我国的国内生产总值各产业产值的条形图和圆形图（饼图）。

2. 现从某高校在校男大学生中随机抽取40人，测得其身高为（单位：cm）
176 168 176 180 184 167 168 164 167 172
174 173 177 170 168 177 170 172 173 160
171 176 163 175 158 161 172 172 172 179
163 169 178 181 166 178 176 171 172 157
取组距为5，最小组下限为155，试用Excel来生成频数分布表和直方图；
上机实习二用Excel计算常用描述统计量前面我们介绍了测度数据集中趋势和离散程度的常用统计量，以及如何利用函数公式来计算各自的结果。

在Excel中，还可以由“工具→数据分析”中的“描述统计”一次性产生以上的常用统计量，此时需将所要计算的变量数据放在一列（或一行）。

下面我们将结合例题来给出用Excel来计算常用统计量的主要步骤。

例2.3现有20名学生的某门课成绩如下所示，
74 93 62 88 86 51 97 73 77 81 85 67 92 60 84 80 78 90 85 81
试对这些成绩数据用Excel来计算常用统计量。

Excel求解：现给出用Excel由“工具→数据分析”中的“描述统计”来一次性计算这些成绩数据的常用统计量的主要步骤。

1. 在Excel中将20名学生成绩数据集输入成一行，选择“工具”下拉菜单的“数据分
析”子菜单，用鼠标双击“数据分析”对话框中的“描述统计”选项。

（见图2-2）
2.进入“描述统计”对话框（见图2-3），选定主要选项：
（1）输入区域：选定要处理的数据区域，这里为成绩数据范围（A1:T1）；
图2-2
（2）分组方式：选定“逐行”；
（3）标志位于第一行：因输入数据区域的第一列不是标志，故不选定该项；（4）输出选项：选定“新工作表组”；
（5）汇总统计：必须选定，该选项将给出全部描述性统计量；
（6）单击“确定”；
由此即可得到20名学生成绩数据的描述性统计量计算结果（图2-4）。

图2-3 “描述统计”对话框图2-4 “描述统计”的计算结果
其中前面介绍的常用统计量用加色显示，其对应结果由下列表2-1给出。

表2-1 Excel计算的常用统计量结果表
上机训练题二
1. 现随机抽取10人，测得其血清中锌含量为（单位： mol/L）
16.8, 24.2, 17.4, 10.4, 15.8, 12.1, 16.6, 20.4, 17.1, 19.6
试用Excel一次性计算其锌含量的中位数、样本均值、样本方差、标准差、变异系数和极差。

2. 现从某高校在校男大学生中随机抽取40人，测得其身高为（单位：cm）
177 168 176 180 184 167 168 164 167 172
175 173 177 170 168 177 170 172 173 160
171 176 163 175 158 161 172 172 172 179
163 169 178 181 166 178 176 171 172 157
试计算身高数据的描述统计量，其均值、标准差、变异系数和极差是多少？
3.已知某城市居民家庭月人均收入分组数据如下表所示。