上机实训指导手册——利用Excel进行数据分组和制作统计图表
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Excel可以根据用户的要求对数据清单的行或列中的数据进行排序。排序时，Excel将利用指定的排序顺序重新排行、列或各单元格，从而使数据的规则性更加简洁地表现出来。
（二）选择分组形式
根据所学知识，分析数据特点，选择合适的分组形式，确定组距和组限。
（三）进行分组并编制次数分布表
利用Excel的主Fra Baidu bibliotek功能和工具实现分组，并计算次数、频率等，得到次数分布表的基本结构。
（3）在任一直方条上用鼠标右键单击，弹出“数据系列格式”对话框，在对话框中选择“选项”标签，把间距宽度改为0，单击“确定”即可得到直方图。
（4）将单元格E19中的“其他”清除，直方图中的“其他”直条消失。
图1—6即是调整过的直方图。
注意：在图上右键单击出现的窗口中，打开“图表类型”，可以直接选择其他类型的图：如“折线图”、“平滑直线图”等。
图1—2工具—数据分析—直方图
第三步，填写“直方图”各项。如图1—3所示。
注意：“直方图”中各项可直接输入，也可将光标放在应填写项目中，然后拖动鼠标选中对应区域，则各项自动填写。
本例中采用输入方法，其中：
输入区域：在此输入待分析数据区域的单元格范围。本例中输入$C$2：$C$51。
接收区域（可选）：在此输入接收区域的单元格范围，该区域应包含一组可选的用来计算频数的边界值，即各分组的上限值。本例中输入$E$2:$E$8，
图4—4
图4—5
同样方法完成金融资产累计散点图。
第五步，绘制曲线图：
右键单击散点图中某一个散点，出现“数据系列格式”对话框，打开，在线形和数据系列格式进行设置，在“线形”项的下方有“平滑线”，打√选中，点确定，绘制出曲线图。如图4—6、图4—7所示。
图4—6
图4—7
三、作业布置
P39练习3、练习4
按照上述操作方法及教材P24和P33介绍的次数计算方法和统计图制作方法，利用Excel图表工具完成作业，并以打印稿格式上交。
注意：因为连续式分组中上组限不在内原则，所以这里输入的上限值是非常接近于分组列举的上限值，但不能等于源数据中任何一个数，并将这些值按升序排列。
图1—1输入50名工人产量数据、分组及各组上限值
第二步，选择“直方图”工具。在工具菜单中单击“数据分析”选项，从其对话框的分析工具列表中选择“直方图”，打开“直方图”对话框，如图1—2所示。
111118123126133115139128124121
要求：根据资料编制组距为5的分配数列，编制次数分布表并绘制次数分布直方图。
操作步骤：（本例介绍直方图工具的操作方法）
第一步，数据输入、排序及分组。启动Excel，新建一个工作簿命名为“数据整理1”。在工作表上的（C2：C51）矩形区域内输入50名工人的日产量数据资料，选中数据资料，进行数据排序，并进行分组。将分组情况及各组上限值分别列出。见图1—1。
图4—3
第四步，输入添加源数据，绘制散点图：
在绘图区某一点右键单击出现对话框，打开其中“数据源”选项，在“数据源”对话框之“系列”项中，点“添加”，出现“系列2”，及“X轴和Y轴数据范围”，点击其右边小图标出现“源数据—x值”对话框，然后将鼠标放在数据表中要选的数据上（本例为居民户、月收入两栏数据），下拉选中（注意不要选文字，只选数据部分），关闭对话框；同样方法选中Y数据范围，关闭。则源数据对话框中出现X轴、Y轴数据范围，同时图中显示其散点。如图4—4、图4—5所示。
图4—1
第二步，输入标题及坐标表示内容：
在图上某点右键单击，出现含“绘图区格式”等多项内容的对话框，分别进行系列名称、标题名称等项目输入。如图4—2所示。
图4—2
第三步，修改坐标刻度：
右键单击X轴的轴线，出现“坐标轴格式”，打开“刻度”对话框，将最大值改为100，刻度间数值改为20，X轴坐标变为0-100；然后以同样方法修改Y轴坐标。如图4—3。
图1—4频数分布和直方图草图
第五步，图形调整。应当注意，图1—4实际上是一个“草图”，还需要做如下调整和修改，以达到既方便阅读又美观大方的要求：
（1）将分组数据复制粘贴到“接收”区域，则直方图X轴数据变为分组数据。如图1—5所示。
图1—5显示分组的直方图
（2）在图上任一点右键单击出现包含“绘图区格式”等多项内容的窗口，进行图形编辑调整。如“源数据”中“系列”名称改为“人数”；“图表选项”中输入标题名称等（该项也可直接将图上的“直方图”“接收”“频率”处左键单击选中，改为对应的文字）；“绘图区格式”中清除灰色背景等。
图1 安装加载宏
图2添加分析工具库选项
图3数据分析选项
【实训举例1】某车间50名工人加工零件日产量资料如下：
122124129139106134127123118112
117130122125117112134127123119
108131125117122113122123127135
133126122118108137114121128124
方法一：频数分布函数是Excel的一个工作表函数，是编制次数分布的主要工具。具体操作方法见教材P24。这里不再细述，同学们按照教材进行练习。
方法二：直方图工具。直方图工具是Excel中进行数据分析的一个常用工具。本指导书主要是就直方图工具的操作使用来说明。
直方图工具的使用要求：直方图工具在“工具”中“数据分析”里，如果Excel初次使用尚未安装数据分析功能，必须依次选择“工具”、“加载宏”，在安装光盘中加载数据“分析工具库”。加载成功后，可以在“工具”下拉菜单中看到“数据分析”选项，里面有“直方图”等多种统计数据分析工具，安装完毕就使用了。如图1、图2、图3所示。
第二步，选中绘图数据范围A2:C5，单击工具栏选项中图表向导，出现“图表向导—4步骤之1—图表类型”，选“饼图”，如图3—2。
图3—2图表类型
第三步，点“下一步”，出现“图表向导—4步骤之2—图表源数据”，数据区域将自动显示“=Sheet1!$A$2:$C$5”,在系列“名称”栏输入“某高校教师职称结构图”，如图3—3。
【实训举例4】根据教材P39习题3绘制洛伦茨曲线图。
操作步骤：启动Excel 2003，新建一个工作簿命名为“绘制洛伦茨曲线图”。基本步骤是先绘出散点图，再连接为曲线图。下面是具体步骤。
第一步，建立坐标系：
因为累计百分比都是100%，故先建立两组0-100的数据组：0,20,40,60,80,100。选中数据组，在图标选项中选择散点图，则出来草图4—1：在这张图中坐标显示可能是不符合要求的，需要调整。如本例中显示为0-120。
（四）绘制统计图
根据次数分布表的基本结构资料绘制统计图，并根据需要对次数分布表和次数分布图进行调整。各种图的做法大同小异，这里主要以柱形图和饼图为例，介绍制作统计图的工作步骤。
（五）输出结果
将整理好的统计表和统计图按要求格式打印输出。
二、实例说明Excel操作步骤
用Excel进行统计分组、编制次数分布表主要有两种方法：一是利用频数分布函数FREQUENCY；二是利用数据分析中的“直方图”工具。
图3—3图表源数据
第四步，点“下一步”，出现“图表向导—4步骤之3—图表选项”，在“数据标志”选项中，选择要在图形中显示的数据标签，如图3—4。
图3—4图表选项
第五步，点“下一步”，出现“图表向导—4步骤之4—图表位置”，选择系统默认位置“作为其中的对象插入”，点“完成”，在工作表中出现已初步完成的饼图，如图3—5。
图2—2“图表向导”对话框
第三步，在图表类型中选择柱形图，然后在子图表类型中选择一种类型，这里选用系统默认的方式，然后单击下一步按钮，打开图表数据源对话框，如图2—3所示。
图2—3“图表数据源”对话框
第四步，在图表数据源对话框中填入数据所在的区域，单击下一步按钮，即可得到图形，如图2—4所示。
图2—4“图表选项”对话框
图1—6调整过的直方图
【实训举例2】根据我国2001—2005年国内生产总值资料绘制柱状图。
操作步骤：启动Excel 2003，新建一个工作簿Bookl。
第一步，把数据输入到工作表中，如图2—1所示。
图2—1我国2001—2005年国内生产总值构成
第二步，选中某一单元格，单击插入菜单，选择图标选项，弹出图表向导对话框（此步骤也可通过直接点工具栏选项中“图表向导”），如图2—2所示。
图3—5完成的饼图
对于一张新创建的统计图，还可以继续对图表类型、数据源、图表选项、位置四个创建步骤进行重新调整、修改。此外，对于数据系列格式、绘图区格式、图表区格式、坐标轴格式、坐标轴标题格式、图表标题格式、图例格式、网格线格式等可进行编辑，达到修正或美化统计图的目的。由于篇幅问题，对该部分不作详述。
实训内容：利用Excel进行数据分组和制作统计图表
实训要求：熟悉Excel的基本功能，掌握利用Excel进行分组与汇总、编制统计表、绘制统计图的基本方法，熟练在计算机上进行操作。
实训方法：机房老师边演示边讲解，学生动手操作。
一、利用Excel进行统计分组、编制次数分布表及绘制统计图的基本思路
（一）输入数据并排序
输出区域：选择输入区域，可以直接选择一个区域，也可以直接输入一个单元格(代表输出区域的左上角)，推荐只输人一个单元格(本例为$E$11)，因为往往事先并不知道具体的输出区域有多大。如果输出表将覆盖已有的数据，Excel会自动确定输出区域的大小并显示信息。
图1—3直方图对话框
第四步，选择图表输出，单击“确定”，显示如图1—4所示。
第五步，在图表选项对话框中依次填入需要的项目，单击下一步和完成按钮，调整后即可得柱状图，如图2—5所示。
图2—5柱形图
【实训举例3】根据某高校教师职称分布情况绘制饼图。
操作步骤：启动Excel 2003，新建一个工作簿命名为“绘制统计图—饼图”。
第一步，把原始数据输入到工作表中，如图3—1所示。
图3—1教师职称结构
注意：只交完成表格及图，过程中的步骤图表不用显示，各人自己留存。
作业格式见下页
统计学上机实训作业
班级姓名各人手写签名学号任课老师张翠凤
作业一：绘制洛伦茨曲线。
作业二：利用Excel编制次数分布数列并绘制次数分布图。
格式同作业一要求。
实训指导书
课程名称统计学
实训班级10财务管理4、10、14班
任课教师张翠凤
二零一一年十月
实训指导书
实训项目：Excel在统计整理中的应用
实训目的：Excel提供了大量的统计图形供用户根据需要和图形功能选择使用，其中绝大多数是统计图，能够较好地展示数据的分布形态，直观形象地描述现象的数量规律性。通过本实训达到使学生熟练运用Excel图表工具的目的。
（二）选择分组形式
根据所学知识，分析数据特点，选择合适的分组形式，确定组距和组限。
（三）进行分组并编制次数分布表
利用Excel的主Fra Baidu bibliotek功能和工具实现分组，并计算次数、频率等，得到次数分布表的基本结构。
（3）在任一直方条上用鼠标右键单击，弹出“数据系列格式”对话框，在对话框中选择“选项”标签，把间距宽度改为0，单击“确定”即可得到直方图。
（4）将单元格E19中的“其他”清除，直方图中的“其他”直条消失。
图1—6即是调整过的直方图。
注意：在图上右键单击出现的窗口中，打开“图表类型”，可以直接选择其他类型的图：如“折线图”、“平滑直线图”等。
图1—2工具—数据分析—直方图
第三步，填写“直方图”各项。如图1—3所示。
注意：“直方图”中各项可直接输入，也可将光标放在应填写项目中，然后拖动鼠标选中对应区域，则各项自动填写。
本例中采用输入方法，其中：
输入区域：在此输入待分析数据区域的单元格范围。本例中输入$C$2：$C$51。
接收区域（可选）：在此输入接收区域的单元格范围，该区域应包含一组可选的用来计算频数的边界值，即各分组的上限值。本例中输入$E$2:$E$8，
图4—4
图4—5
同样方法完成金融资产累计散点图。
第五步，绘制曲线图：
右键单击散点图中某一个散点，出现“数据系列格式”对话框，打开，在线形和数据系列格式进行设置，在“线形”项的下方有“平滑线”，打√选中，点确定，绘制出曲线图。如图4—6、图4—7所示。
图4—6
图4—7
三、作业布置
P39练习3、练习4
按照上述操作方法及教材P24和P33介绍的次数计算方法和统计图制作方法，利用Excel图表工具完成作业，并以打印稿格式上交。
注意：因为连续式分组中上组限不在内原则，所以这里输入的上限值是非常接近于分组列举的上限值，但不能等于源数据中任何一个数，并将这些值按升序排列。
图1—1输入50名工人产量数据、分组及各组上限值
第二步，选择“直方图”工具。在工具菜单中单击“数据分析”选项，从其对话框的分析工具列表中选择“直方图”，打开“直方图”对话框，如图1—2所示。
111118123126133115139128124121
要求：根据资料编制组距为5的分配数列，编制次数分布表并绘制次数分布直方图。
操作步骤：（本例介绍直方图工具的操作方法）
第一步，数据输入、排序及分组。启动Excel，新建一个工作簿命名为“数据整理1”。在工作表上的（C2：C51）矩形区域内输入50名工人的日产量数据资料，选中数据资料，进行数据排序，并进行分组。将分组情况及各组上限值分别列出。见图1—1。
图4—3
第四步，输入添加源数据，绘制散点图：
在绘图区某一点右键单击出现对话框，打开其中“数据源”选项，在“数据源”对话框之“系列”项中，点“添加”，出现“系列2”，及“X轴和Y轴数据范围”，点击其右边小图标出现“源数据—x值”对话框，然后将鼠标放在数据表中要选的数据上（本例为居民户、月收入两栏数据），下拉选中（注意不要选文字，只选数据部分），关闭对话框；同样方法选中Y数据范围，关闭。则源数据对话框中出现X轴、Y轴数据范围，同时图中显示其散点。如图4—4、图4—5所示。
图4—1
第二步，输入标题及坐标表示内容：
在图上某点右键单击，出现含“绘图区格式”等多项内容的对话框，分别进行系列名称、标题名称等项目输入。如图4—2所示。
图4—2
第三步，修改坐标刻度：
右键单击X轴的轴线，出现“坐标轴格式”，打开“刻度”对话框，将最大值改为100，刻度间数值改为20，X轴坐标变为0-100；然后以同样方法修改Y轴坐标。如图4—3。
图1—4频数分布和直方图草图
第五步，图形调整。应当注意，图1—4实际上是一个“草图”，还需要做如下调整和修改，以达到既方便阅读又美观大方的要求：
（1）将分组数据复制粘贴到“接收”区域，则直方图X轴数据变为分组数据。如图1—5所示。
图1—5显示分组的直方图
（2）在图上任一点右键单击出现包含“绘图区格式”等多项内容的窗口，进行图形编辑调整。如“源数据”中“系列”名称改为“人数”；“图表选项”中输入标题名称等（该项也可直接将图上的“直方图”“接收”“频率”处左键单击选中，改为对应的文字）；“绘图区格式”中清除灰色背景等。
图1 安装加载宏
图2添加分析工具库选项
图3数据分析选项
【实训举例1】某车间50名工人加工零件日产量资料如下：
122124129139106134127123118112
117130122125117112134127123119
108131125117122113122123127135
133126122118108137114121128124
方法一：频数分布函数是Excel的一个工作表函数，是编制次数分布的主要工具。具体操作方法见教材P24。这里不再细述，同学们按照教材进行练习。
方法二：直方图工具。直方图工具是Excel中进行数据分析的一个常用工具。本指导书主要是就直方图工具的操作使用来说明。
直方图工具的使用要求：直方图工具在“工具”中“数据分析”里，如果Excel初次使用尚未安装数据分析功能，必须依次选择“工具”、“加载宏”，在安装光盘中加载数据“分析工具库”。加载成功后，可以在“工具”下拉菜单中看到“数据分析”选项，里面有“直方图”等多种统计数据分析工具，安装完毕就使用了。如图1、图2、图3所示。
第二步，选中绘图数据范围A2:C5，单击工具栏选项中图表向导，出现“图表向导—4步骤之1—图表类型”，选“饼图”，如图3—2。
图3—2图表类型
第三步，点“下一步”，出现“图表向导—4步骤之2—图表源数据”，数据区域将自动显示“=Sheet1!$A$2:$C$5”,在系列“名称”栏输入“某高校教师职称结构图”，如图3—3。
【实训举例4】根据教材P39习题3绘制洛伦茨曲线图。
操作步骤：启动Excel 2003，新建一个工作簿命名为“绘制洛伦茨曲线图”。基本步骤是先绘出散点图，再连接为曲线图。下面是具体步骤。
第一步，建立坐标系：
因为累计百分比都是100%，故先建立两组0-100的数据组：0,20,40,60,80,100。选中数据组，在图标选项中选择散点图，则出来草图4—1：在这张图中坐标显示可能是不符合要求的，需要调整。如本例中显示为0-120。
（四）绘制统计图
根据次数分布表的基本结构资料绘制统计图，并根据需要对次数分布表和次数分布图进行调整。各种图的做法大同小异，这里主要以柱形图和饼图为例，介绍制作统计图的工作步骤。
（五）输出结果
将整理好的统计表和统计图按要求格式打印输出。
二、实例说明Excel操作步骤
用Excel进行统计分组、编制次数分布表主要有两种方法：一是利用频数分布函数FREQUENCY；二是利用数据分析中的“直方图”工具。
图3—3图表源数据
第四步，点“下一步”，出现“图表向导—4步骤之3—图表选项”，在“数据标志”选项中，选择要在图形中显示的数据标签，如图3—4。
图3—4图表选项
第五步，点“下一步”，出现“图表向导—4步骤之4—图表位置”，选择系统默认位置“作为其中的对象插入”，点“完成”，在工作表中出现已初步完成的饼图，如图3—5。
图2—2“图表向导”对话框
第三步，在图表类型中选择柱形图，然后在子图表类型中选择一种类型，这里选用系统默认的方式，然后单击下一步按钮，打开图表数据源对话框，如图2—3所示。
图2—3“图表数据源”对话框
第四步，在图表数据源对话框中填入数据所在的区域，单击下一步按钮，即可得到图形，如图2—4所示。
图2—4“图表选项”对话框
图1—6调整过的直方图
【实训举例2】根据我国2001—2005年国内生产总值资料绘制柱状图。
操作步骤：启动Excel 2003，新建一个工作簿Bookl。
第一步，把数据输入到工作表中，如图2—1所示。
图2—1我国2001—2005年国内生产总值构成
第二步，选中某一单元格，单击插入菜单，选择图标选项，弹出图表向导对话框（此步骤也可通过直接点工具栏选项中“图表向导”），如图2—2所示。
图3—5完成的饼图
对于一张新创建的统计图，还可以继续对图表类型、数据源、图表选项、位置四个创建步骤进行重新调整、修改。此外，对于数据系列格式、绘图区格式、图表区格式、坐标轴格式、坐标轴标题格式、图表标题格式、图例格式、网格线格式等可进行编辑，达到修正或美化统计图的目的。由于篇幅问题，对该部分不作详述。
实训内容：利用Excel进行数据分组和制作统计图表
实训要求：熟悉Excel的基本功能，掌握利用Excel进行分组与汇总、编制统计表、绘制统计图的基本方法，熟练在计算机上进行操作。
实训方法：机房老师边演示边讲解，学生动手操作。
一、利用Excel进行统计分组、编制次数分布表及绘制统计图的基本思路
（一）输入数据并排序
输出区域：选择输入区域，可以直接选择一个区域，也可以直接输入一个单元格(代表输出区域的左上角)，推荐只输人一个单元格(本例为$E$11)，因为往往事先并不知道具体的输出区域有多大。如果输出表将覆盖已有的数据，Excel会自动确定输出区域的大小并显示信息。
图1—3直方图对话框
第四步，选择图表输出，单击“确定”，显示如图1—4所示。
第五步，在图表选项对话框中依次填入需要的项目，单击下一步和完成按钮，调整后即可得柱状图，如图2—5所示。
图2—5柱形图
【实训举例3】根据某高校教师职称分布情况绘制饼图。
操作步骤：启动Excel 2003，新建一个工作簿命名为“绘制统计图—饼图”。
第一步，把原始数据输入到工作表中，如图3—1所示。
图3—1教师职称结构
注意：只交完成表格及图，过程中的步骤图表不用显示，各人自己留存。
作业格式见下页
统计学上机实训作业
班级姓名各人手写签名学号任课老师张翠凤
作业一：绘制洛伦茨曲线。
作业二：利用Excel编制次数分布数列并绘制次数分布图。
格式同作业一要求。
实训指导书
课程名称统计学
实训班级10财务管理4、10、14班
任课教师张翠凤
二零一一年十月
实训指导书
实训项目：Excel在统计整理中的应用
实训目的：Excel提供了大量的统计图形供用户根据需要和图形功能选择使用，其中绝大多数是统计图，能够较好地展示数据的分布形态，直观形象地描述现象的数量规律性。通过本实训达到使学生熟练运用Excel图表工具的目的。