上机实训指导手册——利用Excel进行数据分组和制作统计图表.doc

实训指导书课程名称 统计学 实训班级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名工人加工零件日产量资料如下：122124129139106134127123118112 117130122125117112134127123119108131125117122113122123127135133126122118108137114121128124111118123126133115139128124121要求：根据资料编制组距为5的分配数列，编制次数分布表并绘制次数分布直方图。操作步骤：（本例介绍直方图工具的操作方法）第一步，数据输入、排序及分组。启动Excel，新建一个工作簿命名为“数据整理1”。在工作表上的（C2：C51）矩形区域内输入50名工人的日产量数据资料，选中数据资料，进行数据排序，并进行分组。将分组情况及各组上限值分别列出。见图11。注意：因为连续式分组中上组限不在内原则，所以这里输入的上限值是非常接近于分组列举的上限值，但不能等于源数据中任何一个数，并将这些值按升序排列。图11 输入50名工人产量数据、分组及各组上限值第二步，选择“直方图”工具。在工具菜单中单击“数据分析”选项，从其对话框的分析工具列表中选择“直方图”，打开“直方图”对话框，如图12所示。图12 工具数据分析直方图第三步，填写“直方图”各项。如图13所示。注意：“直方图”中各项可直接输入，也可将光标放在应填写项目中，然后拖动鼠标选中对应区域，则各项自动填写。本例中采用输入方法，其中： 输入区域：在此输入待分析数据区域的单元格范围。本例中输入$C$2：$C$51。接收区域（可选）：在此输入接收区域的单元格范围，该区域应包含一组可选的用来计算频数的边界值，即各分组的上限值。本例中输入$E$2:$E$8，输出区域：选择输入区域，可以直接选择一个区域，也可以直接输入一个单元格(代表输出区域的左上角)，推荐只输人一个单元格(本例为$E$11)，因为往往事先并不知道具体的输出区域有多大。如果输出表将覆盖已有的数据，Excel 会自动确定输出区域的大小并显示信息。图13 直方图对话框第四步，选择图表输出，单击“确定”，显示如图14所示。图14 频数分布和直方图草图第五步，图形调整。应当注意，图14实际上是一个“草图”，还需要做如下调整和修改，以达到既方便阅读又美观大方的要求：（1）将分组数据复制粘贴到“接收”区域，则直方图X轴数据变为分组数据。如图15所示。图15 显示分组的直方图（2）在图上任一点右键单击出现包含“绘图区格式”等多项内容的窗口，进行图形编辑调整。如“源数据”中“系列”名称改为“人数”；“图表选项”中输入标题名称等（该项也可直接将图上的“直方图”“接收”“频率”处左键单击选中，改为对应的文字）；“绘图区格式”中清除灰色背景等。（3）在任一直方条上用鼠标右键单击，弹出“数据系列格式”对话框，在对话框中选择“选项”标签，把间距宽度改为0，单击“确定”即可得到直方图。（4）将单元格E19中的“其他”清除，直方图中的“其他”直条消失。图16即是调整过的直方图。注意：在图上右键单击出现的窗口中，打开“图表类型”，可以直接选择其他类型的图：如“折线图”、“平滑直线图”等。图16 调整过的直方图【实训举例2】根据我国20012005年国内生产总值资料绘制柱状图。操作步骤：启动Excel 2003，新建一个工作簿Bookl。第一步，把数据输入到工作表中，如图21所示。 图21 我国20012005年国内生产总值构成第二步，选中某一单元格，单击插入菜单，选择图标选项，弹出图表向导对话框（此步骤也可通过直接点工具栏选项中“图表向导”），如图22所示。图22 “图表向导”对话框第三步，在图表类型中选择柱形图，然后在子图表类型中选择一种类型，这里选用系统默认的方式，然后单击下一步按钮，打开图表数据源对话框，如图23所示。图23 “图表数据源”对话框第四步，在图表数据源对话框中填入数据所在的区域，单击下一步按钮，即可得到图形，如图24所示。图24 “图表选项”对话框第五步，在图表选项对话框中依次填入需要的项目，单击下一步和完成按钮，调整后即可得柱状图，如图25所示。图25 柱形图【实训举例3】根据某高校教师职称分布情况绘制饼图。操作步骤：启动Excel 2003，新建一个工作簿命名为“绘制统计图饼图”。第一步，把原始数据输入到工作表中，如图31所示。图31教师职称结构第二步，选中绘图数据范围A2:C5，单击工具栏选项中图表向导，出现“图表向导4步骤之1图表类型”，选“饼图”，如图32。图32 图表类型第三步，点“下一步”，出现“图表向导4步骤之2图表源数据”，数据区域将自动显示“=Sheet1!$A$2:$C$5”,在系列“名称”栏输入“某高校教师职称结构图”，如图33。图33 图表源数据第四步，点“下一步”，出现“图表向导4步骤之3图表选项”，在“数据标志”选项中，选择要在图形中显示的数据标签，如图34。图34 图表选项第五步，点“下一步”，出现“图表向导4步骤之4图表位置”，选择系统默认位置“作为其中的对象插入”，点“完成”，在工作表中出现已初步完成的饼图，如图35。图35 完成的饼图对于一张新创建的统计图，还可以继续对图表类型、数据源、图表选项、位置四个创建步骤进行重新调整、修改。此外，对于数据系列格式、绘图区格式、图表区格式、坐标轴格式、坐标轴标题格式、图表标题格式、图例格式、网格线格式等可进行编辑，达到修正或美化统计图的目的。由于篇幅问题，对该部分不作详述。【实训举例4】 根据教材P39习题3绘制洛伦茨曲线图。操作步骤：启动Excel 2003，新建一个工作簿命名为“绘制洛伦茨曲线图”。基本步骤是先绘出散点图，再连接为曲线图。下面是具体步骤。第一步，建立坐标系：因为累计百分比都是100%，故先建立两组0-100的数据组：0,20,40,60,80,100。选中数据组，在图标选项中选择散点图，则出来草图41：在这张图中坐标显示可能是不符合要求的，需要调整。如本例中显示为0-120。图41第二步，输入标题及坐标表示内容：在图上某点右键单击，出现含“绘图区格式”等多项内容的对话框，分别进行系列名称、标题名称等项目输入。如图42所示。图42第三步，修改坐标刻度：右键单击X轴的轴线，出现“坐标轴格式”，打开“刻度”对话框，将最大值改为100，刻度间数值改为20 ，X轴坐标变为0-100；然后以同样方法修改Y轴坐标。如图43。图43第四步，输入添加源数据，绘制散点图：在绘图区某一点右键单击出现对话框，打开其中“数据源”选项，在“数据源”对话框之“系列”项中，点“添加”，出现“系列2”，及“X轴和Y轴数据范围”，点击其右边小图标出现“源数据x值”对话框，然后将鼠标放在数据表中要选的数据上（本例为居民户、月收入两栏数据），下拉选中（注意不要选文字，只选数据部分），关闭对话框；同样方法选中Y数据范围，关闭。则源数据对话框中出现X轴、Y轴数据范围，同时图中显示其散点。如图44、图45所示。图44图45同样方法完成金融资产累计散点图。第五步，绘制曲线图：右键单击散点图中某一个散点，出现“数据系列格式”对话框，打开，在线形和数据系列格式进行设置，在“线形”项的下方有“平滑线”，打选中，点确定，绘制出曲线图。如图46、图47所示。图46图47三、作业布置P39练习3、练习4按照上述操作方法及教材P24和P33介绍的次