利用Excel进行数据分组和制作统计图表（可编辑）

利用Excel进行数据分组和制作统计图表
实训指导书课程名称统计学
实训班级10财务管理4、10、14班
任课教师张翠凤二零一一年十月实训指导书实训项目：Excel在统计整理中的应用
实训目的：Excel提供了大量的统计图形供用户根据需要和图形功能选择使用，其中绝大多数是统计图，能够较好地展示数据的分布形态，直观形象地描述现象的数量规律性。

通过本实训达到使学生熟练运用Excel图表工具的目的。

实训内容：利用Excel进行数据分组和制作统计图表
实训要求：熟悉Excel的基本功能，掌握利用Excel进行分组与汇总、编制统计表、绘制统计图的基本方法，熟练在计算机上进行操作。

实训方法：机房老师边演示边讲解，学生动手操作。

一、利用Excel进行统计分组、编制次数分布表及绘制统计图的基本思路（一）输入数据并排序
Excel可以根据用户的要求对数据清单的行或列中的数据进行排序。

排序时，Excel将利用指定的排序顺序重新排行、列或各单元格，从而使数据的规则性更加简洁地表现出来。

（二）选择分组形式
根据所学知识，分析数据特点，选择合适的分组形式，确定组距和组限。

（三）进行分组并编制次数分布表
利用Excel的主要功能和工具实现分组，并计算次数、频率等，得到次数分布表的基本结构。

（四）绘制统计图
根据次数分布表的基本结构资料绘制统计图，并根据需要对次数分布表和次数分布图进行调整。

各种图的做法大同小异，这里主要以柱形图和饼图为例，介绍制作统计图的工作步骤。

（五）输出结果
将整理好的统计表和统计图按要求格式打印输出。

二、实例说明Excel操作步骤
用Excel进行统计分组、编制次数分布表主要有两种方法：一是利用频数分布函数FREQUENCY；二是利用数据分析中的“直方图”工具。

方法一：频数分布函数是Excel的一个工作表函数，是编制次数分布的主要工具。

具体操作方法见教材P24。

这里不再细述，同学们按照教材进行练习。

方法二：直方图工具。

直方图工具是Excel中进行数据分析的一个常用工具。

本指导书主要是就直方图工具的操作使用来说明。

直方图工具的使用要求：直方图工具在“工具”中“数据分析”
里，如果Excel初次使用尚未安装数据分析功能，必须依次选择“工具”、“加载宏”，在安装光盘中加载数据“分析工具库”。

加载成功后，可以在“工具”下拉菜单中看到“数据分析”选项，里面有“直方图”等多种统计数据分析工具，安装完毕就使用了。

如图1、图2、图3所示。

图1 安装加载宏图2 添加分析工具库选项图3 数据分析选项【实训举例1】某车间50名工人加工零件日产量资料如下：
122 124 129 139 106 134 127 123 118
112
117 130 122 125 117 112 134 127 123
119
108 131 125 117 122 113 122 123 127
135
133 126 122 118 108 137 114 121 128
124
111 118 123 126 133 115 139 128 124
121
要求：根据资料编制组距为5的分配数列，编制次数分布表并绘制次数分布直方图。

操作步骤：（本例介绍直方图工具的操作方法）
第一步，数据输入、排序及分组。

启动Excel，新建一个工作簿命名为“数据整理1”。

在工作表上的（C2：C51）矩形区域内输入50
名工人的日产量数据资料，选中数据资料，进行数据排序，并进行分组。

将分组情况及各组上限值分别列出。

见图1―1。

注意：因为连续式分组中上组限不在内原则，所以这里输入的上限值是非常接近于分组列举的上限值，但不能等于源数据中任何一个数，并将这些值按升序排列。

图1―1 输入50名工人产量数据、分组及各组上限值第二步，选择“直方图”工具。

在工具菜单中单击“数据分析”选项，从其对话框的分析工具列表中选择“直方图”，打开“直方图”对话框，如图1―2所示。

图1―2 工具―数据分析―直方图
第三步，填写“直方图”各项。

如图1―3所示。

注意：“直方图”中各项可直接输入，也可将光标放在应填写项目中，然后拖动鼠标选中对应区域，则各项自动填写。

本例中采用输入方法，其中：
输入区域：在此输入待分析数据区域的单元格范围。

本例中输入$C$2：$C$51。

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

本例中输入$E$2:$E$8，
输出区域：选择输入区域，可以直接选择一个区域，也可以直接输入一个单元格代表输出区域的左上角，推荐只输人一个单元格本例为$E$11 ，因为往往事先并不知道具体的输出区域有多大。

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

图1―3 直方图对话框
第四步，选择图表输出，单击“确定”，显示如图1―4所示。

图1―4 频数分布和直方图草图
第五步，图形调整。

应当注意，图1―4实际上是一个“草图”，还需要做如下调整和修改，以达到既方便阅读又美观大方的要求：（1）将分组数据复制粘贴到“接收”区域，则直方图X轴数据变为分组数据。

如图1―5所示。

图1―5 显示分组的直方图（2）在图上任一点右键单击出现包含“绘图区格式”等多项内容的窗口，进行图形编辑调整。

如“源数据”中“系列”名称改为“人数”；“图表选项”中输入标题名称等（该项也可直接将图上的“直方图”“接收”“频率”处左键单击选中，改为对应的文字）；“绘图区格式”中清除灰色背景等。

（3）在任一直方条上用鼠标右键单击，弹出“数据系列格式”对话框，在对话框中选择“选项”标签，把间距宽度改为0，单击“确定”即可得到直方图。

（4）将单元格E19中的“其他”清除，直方图中的“其他”直条消失。

图1―6即是调整过的直方图。

注意：在图上右键单击出现的窗口中，打开“图表类型”，可以直接选择其他类型的图：如“折线图”、“平滑直线图”等。

图1―6 调整过的直方图
【实训举例2】根据我国2001―2005年国内生产总值资料绘制
柱状图。

操作步骤：启动Excel 2003，新建一个工作簿Bookl。

第一步，把数据输入到工作表中，如图2―1所示。

图2―1 我国2001―2005年国内生产总值构成
第二步，选中某一单元格，单击插入菜单，选择图标选项，弹出图表向导对话框（此步骤也可通过直接点工具栏选项中“图表向导”），如图2―2所示。

图2―2 “图表向导”对话框
第三步，在图表类型中选择柱形图，然后在子图表类型中选择一种类型，这里选用系统默认的方式，然后单击下一步按钮，打开图表数据源对话框，如图2―3所示。

图2―3 “图表数据源”对话框第四步，在图表数据源对话框中填入数据所在的区域，单击下一步按钮，即可得到图形，如图2―4所示。

图2―4 “图表选项”对话框
第五步，在图表选项对话框中依次填入需要的项目，单击下一步和完成按钮，调整后即可得柱状图，如图2―5所示。

图2―5 柱形图
【实训举例3】根据某高校教师职称分布情况绘制饼图。

操作步骤：启动Excel 2003，新建一个工作簿命名为“绘制统计图―饼图”。

第一步，把原始数据输入到工作表中，如图3―1所示。

图3―1教师职称结构
第二步，选中绘图数据范围A2:C5，单击工具栏选项中图表向导，
出现“图表向导―4步骤之1―图表类型”，选“饼图”，如图3―2。

图3―2 图表类型
第三步，点“下一步”，出现“图表向导―4步骤之2―图表源数据”，数据区域将自动显示“ Sheet1!$A$2:$C$5”,在系列“名称”栏输入“某高校教师职称结构图”，如图3―3。

图3―3 图表源数据第四步，点“下一步”，出现“图表向导―4步骤之3―图表选项”，在“数据标志”选项中，选择要在图形中显示的数据标签，如图3―4。

图3―4 图表选项
第五步，点“下一步”，出现“图表向导―4步骤之4―图表位置”，选择系统默认位置“作为其中的对象插入”，点“完成”，在工作表中出现已初步完成的饼图，如图3―5。

图3―5 完成的饼图对于一张新创建的统计图，还可以继续对图表类型、数据源、图表选项、位置四个创建步骤进行重新调整、修改。

此外，对于数据系列格式、绘图区格式、图表区格式、坐标轴格式、坐标轴标题格式、图表标题格式、图例格式、网格线格式等可进行编辑，达到修正或美化统计图的目的。

由于篇幅问题，对该部分不作详述。

【实训举例4】根据教材P39习题3绘制洛伦茨曲线图。

操作步骤：启动Excel 2003，新建一个工作簿命名为“绘制洛伦茨曲线图”。

基本步骤是先绘出散点图，再连接为曲线图。

下面是具体步骤。

第一步，建立坐标系：
因为累计百分比都是100%，故先建立两组0-100的数据组：
0,20,40,60,80,100。

选中数据组，在图标选项中选择散点图，则出来草图4―1：在这张图中坐标显示可能是不符合要求的，需要调整。

如本例中显示为0-120。

图4―1
第二步，输入标题及坐标表示内容：
在图上某点右键单击，出现含“绘图区格式”等多项内容的对话框，分别进行系列名称、标题名称等项目输入。

如图4―2所示。

图4―2
第三步，修改坐标刻度：
右键单击X轴的轴线，出现“坐标轴格式”，打开“刻度”对话框，将最大值改为100，刻度间数值改为20 ，X轴坐标变为0-100；然后以同样方法修改Y轴坐标。

如图4―3。

图4―3
第四步，输入添加源数据，绘制散点图：
在绘图区某一点右键单击出现对话框，打开其中“数据源”选项，在“数据源”对话框之“系列”项中，点“添加”，出现“系列2”，及“X轴和Y轴数据范围”，点击其右边小图标出现“源数据―x值”对话框，然后将鼠标放在数据表中要选的数据上（本例为居民户、月收入两栏数据），下拉选中（注意不要选文字，只选数据部分），关闭对话框；同样方法选中Y数据范围，关闭。

则源数据对话框中出现X 轴、Y轴数据范围，同时图中显示其散点。

如图4―4、图4―5所示。

图4―4 图4―5
同样方法完成金融资产累计散点图。

第五步，绘制曲线图：
右键单击散点图中某一个散点，出现“数据系列格式”对话框，打开，在线形和数据系列格式进行设置，在“线形”项的下方有“平滑线”，打√选中，点确定，绘制出曲线图。

如图4―6、图4―7所示。

图4―6 图4―7
三、作业布置
P39练习3、练习4
按照上述操作方法及教材P24和P33介绍的次数计算方法和统计图制作方法，利用Excel图表工具完成作业，并以打印稿格式上交。

注意：只交完成表格及图，过程中的步骤图表不用显示，各人自己留存。

作业格式见下页统计学上机实训作业班级姓名各人手写签名学号任课老师张翠凤
作业一：绘制洛伦茨曲线。

作业二：利用Excel编制次数分布数列并绘制次数分布图。

格式同作业一要求。

2 此处：源数据资料表格及题目要求。

注意表格格式。

此处：
将你绘制好的图表输出在这里。

注意图表格式的调整。