利用Excel进行统计数据的整理范文.docx

1、利用Excel进行统计数据的整理范文第二章 利用Excel进行统计数据的整理本章主要讲解如何利用Excel进行统计整理。通过本章的学习，学生应掌握如下内容：利用Excel进行数据排序与筛选、统计分组统计数据的透视分析、图表的绘制等。第一节 利用Excel进行排序与分组一、利用Excel进行统计数据的排序与筛选利用Excel进行统计数据的排序利用Excel进行数据的排序是以数据清单中一个或几个字段为关键字，对整个数据清单的行或列重新进行排列。排序时，Excel将利用指定的排序顺序重新排列行、列或各单元格。对于数字型字段，排序按数值的大小进行；对于字符型字段，排序按ASC码大小进行；中文字段按拼音

2、或笔画进行。通过排序，可以清楚地反映数据之间的大小关系，从而使数据的规则性更加简洁地表现出来。【例2.1】某大学二年级某班一个组学生期末考试成绩如表2-1所示，请按某一课程成绩排序。表2-1 某毕业班学生毕业就业情况表 单位：分学号姓名统计学英语数学经济学管理学3101李双林78808873813102马贵初89755678703103张三丰92486084663104陈迁和66747266743105欧阳梅80635559823106叶建斌52717563753107沈汉杰77888480803108黄伶俐96907790813109戈思思63676674663110刘婷婷827190667

3、8对某一课程成绩排序（按单字段排序），最简单的方法是，将表2-1中的数据复制到Excel工作表中，然后直接点击工具栏上的升序排序按钮“”或降序排序按钮“”即可。比如对英语成绩排序，只要单击该字段下任一单元格，或单击该字段的列标，再单击升序排序按钮或降序排序按钮，就可完成英语课程成绩字段的升序排序或降序排序。图2-1 选择列标时“排序警告”对话框需要说明的是：当单击的是字段下某一单元格时，直接点击工具栏上的升序排序按钮或降序排序按钮即可完成排序工作；而当单击的是该字段的列标时，点击工具栏上的升序排序按钮或降序排序按钮后会跳出一个“排序警告”对话框（见图2-1），不用理会，直接单击排序按钮，也可完

4、成排列工作（见图2-2）。这种排序方法简便快捷。图2-2 对英语成绩按升序排序的结果对该组学生课程成绩排序也可按多字段方式排序，其操作步骤如下：单击英语字段下任一单元格。单击菜单栏上的“数据”中的“排序”选项，弹出“排序”对话框，如图2-3所示。图2-3 “多字段排序方式”对话框在“排序”对话框中，在“主要关键字”中点开下拉按钮“”，在下拉列表中选择“英语（分）”；在“次要关键字”中点开下拉按钮“”，在下拉列表中选择“数学（分）”；在“第三关键字”中点开下拉按钮“”，在下拉列表中选择“经济学（分）”；在右边的单选按钮中都选择中“升序”。单击“确定”按钮，即可得到排序结果。需要说明的是，这个排序

5、结果与“按单字段排序”的结果一致，原因在于Excel按某一字段排序的“联动性”，即在按某一字段排序时，其他字段跟随该字段进行调整，而不会单独分离。当然，如果进一步设置，还可单击“排序”对话框中的“选项”按钮，在弹出的“排序选项”对话框（如图2-4所示）中进行详细设置。不过可以肯定的是，不管如何设置，结果将与按单字段排序的结果如出一辙。图2-4 “排序选项”对话框利用Excel进行统计数据的筛选利用Excel进行数据筛选，可以把符合要求的数据集中在一起把不符合要求的数据隐藏起来。数据的筛选包括自动筛选和高级筛选两项功能。自动筛选自动筛选是一种快速的筛选方法，可以方便地将满足条件的数据显示在工作表

6、上，将不满足条件的数据隐藏起来。如要进行自动筛选，只要单击想自动筛选字段下某一单元格，如要对英语成绩进行自动筛选，则只需选定英语成绩字段下某一单元格，点开菜单栏中“数据”中的“筛选”项下的“自动筛选”即可，点击后的结果如图2-5所示。图2-5 打开“自动筛选”功能在“自动筛选”界面下，点开下拉按钮“”，在自动筛选的下拉列表中又包括全部、前个筛选、自定义三种。全部筛选。由于全部筛选是将数据清单中全部数据列入其中，因此，该筛选与不筛选并没有什么区别。前个筛选。根据需要，确定前几项的个数。比如，英语成绩中只要列出前5名，则只需在选定英语成绩字段下某一单元格，输入“前5项”（见图2-6），点击“确定”

7、按钮即可（见图2-7）。图2-6 “自动筛选前10个”对话框图2-7 前5个排序结果示意图自定义筛选。在“自动筛选”界面下，点开下拉按钮“”，在自动筛选的下拉列表中选择“自定义”选项，出现“自定义自动筛选方式”对话框（见图2-8）。图2-8 “自定义自动筛选方式”对话框在“自定义自动筛选方式”对话框中，可以设置两组筛选条件。第一组筛选条件由关系运算符构成条件表达式，其中左侧运算符有“等于”、“不等于”、“大于”、“大于或等于”、“小于”、“小于或等于”、“始于”、“并非起始于”、“止于”、“并非结束于”、“包含”、“不包含”等。在对话框右侧，可以按下拉按钮选择值，也可以直接键入数据。两组条件之

8、间可以是“与”或者是“或”的关系。高级筛选高级筛选是一种可以满足指定条件的筛选方法。以例2.1为例，查找英语成绩不及格、经济学成绩高于80的同学。筛选步骤如下：设定条件区域。将单元格A1：G1的内容复制到A13：G13的单元格，在单元格D14，F14中分别输入“80”，如图2-9所示。A1：G12为数据清单区域，A13：G14为条件区域，一般用一空行将两区域分隔。条件区域第一行为字段名，第二行及以下各行为条件值。同一行条件之间为“与”的关系，不同行条件之间为“或”的关系，可采用的条件符号有“”、“”、“”、“”。图2-9 数据清单区域与条件区域单击数据清单中任一单元格如D2，打开菜单栏中的“数

9、据”，在“筛选”项下选择“高级筛选”，打开“高级筛选”对话框。在“列表区域”中输入“$A$1：$G$11”，在条件区域中输入“$A$13：$G$14”，如图2-10所示。图2-10 “高级筛选”对话框单击“确定”按钮，显示筛选结果，如图211所示。图211 高级筛选结果如果想将筛选的结果复制到其他位置，可以在“高级筛选”对话框中的“方式”选项下选择“将筛选结果复制到其他位置”，激活“条件区域”下的“复制到”选项，输入指定位置左上角的单元格行列号，再单击“确定”按钮即可。如果对重复记录不想全部显示，可以选中对话框中的“选择不重复的记录”复选框，则如果有重复记录，就只显示一条。若要退出高级筛选，可

10、在“数据”菜单中再次选择“筛选全部显示”命令，即可恢复原来的数据清单。二、利用Excel进行统计分组用Excel进行统计分组和编制频数分布表有两种方法，一是函数法；二是利用数据分析中的“直方图”工具。函数法在Excel中利用函数进行统计分组和编制频数分布表可利用COUNTIF（）和FREQUENCY（）等函数，但要根据变量值的类型不同而选择不同的函数。当分组标志是品质标志时应使用COUNTIF（）函数；当分组标志是数量标志时应使用FREQUENCY（）函数。COUNTIF（）函数COUNTIF（）函数的语法构成是：COUNTIF（区域，条件）。具体使用方法举例如下。【例2.2】已知某学院某系某

11、毕业班学生共有30人，他们的毕业就业情况如表2-2。表2-2 某毕业班学生毕业就业情况表学生编号性别年龄工作单位学生编号性别年龄工作单位1男24事业单位16男23企业2男21企业17男23国家机关3女22事业单位18女19企业4女23事业单位19男22事业单位5男21企业20女22企业6男21企业21男22企业7女22国家机关22女20自主创业8女20企业23男20企业9男23事业单位24女23企业10女23企业25女23企业11女24企业26男24事业单位12男21企业27女21企业13女23企业28男20国家机关14男23事业单位29女20企业15女20企业30男21企业试用Excel编制

12、此调查数据的频数分布表。首先将数据输入Excel单元格中，其次观察数据的类型个数，在工作表中的空余位置列出各组名称，如图2-12所示。图2-12 某毕业班学生毕业就业情况资料具体操作步骤如下：将上述资料输入Excel工作表；在单元格D2中输入“工作单位性质”，在E2中输入“学生人数”，在D3：D6区域中依次输入“国家机关”、“事业单位”、“企业”、“自主创业”，表示分组方式，同时这也可以表示分组组限，如图2-12所示。选择单元格E3至E6区域，在“插入”菜单中单击“函数”选项，打开图2-13 “插入函数”对话框“插入函数”对话框选项，打开“插入函数”对话框；在“或选择类别”列表中选择“统计”，

13、在“选择函数”列表中选择“COUNTIF”。如图2-13所示。单击“确定”按钮，Excel弹出“函数参数”对话框。在数据区域“Range”中输入单元格B2：B31，在数据接受区间“Criteria”中输入单元格D3：D6，如图2-14所示。图2-14 “函数参数”对话框由于频数分布是数组操作，所以，此处不能直接单击“确定”按钮，而应按“Ctrl+Shift”组合键，同时按回车键，得到频数分布。如图2-15所示。图2-15 频数分布结果另外，直接利用Excel函数公式也可以得到同样结果。用鼠标选定单元格D3:D6，注意不要释放选定区域。在D3单元格中输入频数分布函数公式：“=COUNTIF(B2

14、:B31，D3:D6)”。在这个公式中，数据区域为B2:B31，接收区间为D3:D6，按“Ctrl+Shift”组合键，同时按回车键，得到频数分布与上面相同。FREQUENCY（）函数频数分布函数FEQUENCY（）是指可以对一列垂直数组返回某个区域中数据的频数分布。其语法形式为：FREQUENCY(Data_array,Bins_array)其中：Data_array为用来编制频数分布的数据，Bins_array为频数或次数的接收区间。具体使用方法举例如下。【例2.3】已知某班50名学生英语成绩如表2-3所示。表2-3 某班学生英语成绩表学号成绩（分）学号成绩（分）0310178031267

15、503102890312784031039203128770310466031296603105800313090031065203131730310777031327803108960313384031096303134660311082031355903111800313663031127503137800311348031389003114740313974031156303140660311671031418103117880314270031189003143660311967031447403120710314582031218803146750312256031478003123

16、6003148810312472031496603125550315078请用Excel编制此调查数据的频数分布表。具体操作步骤如下：将上述资料输入Excel工作表；在单元格D2中输入“分组”，在E2中输入“分组组限”，在单元格F2中输入“频数”；在D3：D7区域中依次输入“60以下”，“6070”，“7080”，“8090”，“90100”，表示分组方式，但是这还不能作为频数接收区间；在E3：E7区域中依次输入59，69，79，89，100，表示分组组限，作为频数接收区间，它们分别表明60分以下的人数，60分以上、70 分以下的人数等，这与前列分组方式是一致的，如图2-16所示。图2-16

17、学生英语成绩资料选择单元格F3至F7区域，在“插入”菜单中单击“函数”选项，打开“插入函数”对话框；在“或选择类别”列表中选择“统计”，在“选择函数”列表中选择“FREQUENCY”，如图2-17所示。图2-17 “插入函数”对话框单击“确定”按钮，Excel弹出“函数参数”对话框。在数据区域“Data_array”中输入单元格“B2：B51”，在数据接受区间“Bins_array”中输入单元格“E2：E6”，在对话窗口中可以看到其相应的频数是5，11，16，13，5，如图2-18所示。图2-18 “频数分布”对话框由于频数分布是数组操作，所以，此处不能直接单击“确定”按钮，而应按“Ctrl+

18、Shift”组合键，同时按回车键，得到频数分布，如图2-19所示。 图2-19 频数分布结果另外，直接利用Excel函数公式也可以得到同样结果。用鼠标选定单元格“F3:F7”，注意不要释放选定区域。在F3单元格中输入频数分布函数公式：FREQUENCY(B2:B51,E3:E7)。在这个公式中，数据区域为“B2:B51”，接收区间为“E3:E7”，按“Ctrl+Shift”组合键，同时按回车键，得到频数分布与上面相同。利用“直方图”工具进行统计分组直方图分析工具是一个用于确定数据的频数分布、累计频数分布，并提供直方图的分析模块。它在给定工作表中数据单元格区域和接收区间的情况下，计算数据的频数和

19、累积频数。【例2.4】仍以“表23某班学生英语成绩表”为例，试编制此调查数据的频数分布表。具体操作步骤如下：第一，在“工具”菜单中，单击“数据分析”选项，弹出“数据分析”对话框，如图2-20所示。图2-20 “数据分析”对话框注意：如果用户在Excel的“工具”菜单中没有找到“数据分析”选项，说明用户安装Excel不完整，必须在Excel中重新安装“分析工具库”的内容。具体安装方法如下。在“工具”菜单中，单击“加载宏”选项。选中“分析工具库”和“分析工具库-VBA函数”复选框，单击“确定”按钮，将会引导用户进行安装，如图2-21所示。图2-21 “加载宏”对话框如果用户在安装Excel时选择的

20、是“典型安装”，则需要使用CD-ROM进行安装，如果用户在安装Excel时选择的是“完全安装”，则Excel会从硬盘中直接进行安装。无论是何种情况，安装完毕后，“数据分析”选项会自动出现在Excel的工具菜单中。第二，在“分析工具”列表框中，单击“直方图”分析工具，则会弹出“直方图”对话框，如图2-22所示。图2-22 “直方图”对话框第三，选择输入选项。输入区域：在此输入待分析数据区域的单元格引用；接收区域：表示分组标志所在的区域，在此输入接收区域的单元格引用，该区域应包含一组可选的用来定义接收区间的边界值，这些值应当按升序排列，如本例中的“分组组限”。关于这一点，与前面所讲的FREQUEN

21、CY函数一致。在“输入区域”中，输入“$B$10：$B$59”；选好接收区域的内容“$E$2：$E$7”。第四，选择输出选项。输出选项中可选择输出区域、新工作表或新工作簿。在这里选择输出区域，可以直接选择一个区域，也可以直接输出一个单元格，该单元格代表输出区域的左上角，这里常常只输入一个单元格，如本例中$I$11，因为我们往往事先并不知道具体的输出区域有多大。输出选项中还有以下选项：柏拉图：选中此复选框，可以在输出表中同时按降序排列频率数据。如果此复选框被清除，Excel将只按升序来排列数据。累积百分比：选中此复选框，可以在输出表中添加一列累积百分比数值，并同时在直方图表中添加累积百分比折线。

22、如果清除此选项，则会省略累积百分比。图表输出：选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。本例中，我们选中“累积百分率”和“图表输出”两个复选框。第五，单击“确定”按钮，可得输出结果，如图2-23所示。 图2-23 频数分布和直方图注意：在默认的直方图中，柱形彼此分开，如果要将其连接起来，操作步骤如下：单击某个柱形，单击鼠标右键，在弹出菜单中，选择“数据系列格式”选项，弹出“数据系列格式”对话框，如图2-24所示。图2-24 “数据系列格式”对话框在对话框中选择“选项”标签，将间距宽度从150改成0，点上“依数据点分色”，再单击“确定”按钮，得到直方图如图2-25所示。图2-25

23、调整后的直方图第二节 利用Excel进行统计数据的透视分析Excel提供了数据透视表和数据透视图的功能，下面分别就数据透视表与数据透视图的操作进行举例说明。一、数据透视表数据透视表将排序、筛选及分类汇总功能结合起来，对数据清单或各数据重新组织和计算，并以多种不同的形式显示出来。利用数据透视图，可以更直观地显示数据。下面就Excel中如何操作数据透视表进行举例介绍。【例2.5】为了验证不同肥料对不同品种水稻的使用效果，某农场对三块地的糯谷稻、籼稻和杂交稻分别施用化肥、有机肥与复合肥等三种不同的肥料，获得每亩产量样本数据如表2-4所示。利用数据透视表比较不同地块、不同水稻品种、不同种类肥料的每亩不

24、同产量情况。表2-4 三个地块、三个水稻品种、三种不同施肥方案的农作物每亩产量情况表单位：千克/亩地块水稻品种肥料种类每亩产量地块一糯谷稻化肥518地块一糯谷稻有机肥526地块一糯谷稻复合肥525地块一籼稻化肥509地块一籼稻有机肥523地块一籼稻复合肥528地块一杂交稻化肥521地块一杂交稻有机肥519地块一杂交稻复合肥532地块二糯谷稻化肥526地块二糯谷稻有机肥519地块二糯谷稻复合肥536地块二籼稻化肥531地块二籼稻有机肥543地块二籼稻复合肥533地块二杂交稻化肥528地块二杂交稻有机肥529地块二杂交稻复合肥542地块三糯谷稻化肥522地块三糯谷稻有机肥537地块三糯谷稻复合肥5

25、27地块三籼稻化肥534地块三籼稻有机肥553地块三籼稻复合肥546地块三杂交稻化肥551地块三杂交稻有机肥548地块三杂交稻复合肥563检验操作步骤将表2-4数据复制到Excel工作表中。单击单元格F2，在“数据”菜单中选择“数据透视表和数据透视图”选项，弹出“数据透视表和数据透视图向导3步骤之1”对话框，如图2-26所示。在“请指定待分析数据的数据源类型”中选择默认的“Microsoft Office Excel数据列表或数据库”，在“所需创建的报表类型”中选择默认的“数据透视表”。图2-26 “数据透视表和数据透视图向导3步骤之1”对话框点击“下一步”按钮，弹出“数据透视表和数据透视图向

26、导3步骤之2”对话框，如图2-27所示。在“选定区域”中选择“数据透视表及透视图！$A$1:$D$28”。图2-27 “数据透视表和数据透视图向导3步骤之2”对话框点击“下一步”按钮，弹出“数据透视表和数据透视图向导3步骤之3”对话框，如图2-28所示。在“数据透视表显示位置”中选择“现有工作表”，并选择好操作好的数据透视表的输出位置“数据透视表及透视图！$F$2”。图2-28 “数据透视表和数据透视图向导3步骤之3”对话框在图2-28中有“布局”与“选项”按钮，可以用“布局”按钮来调整工作表的分布，用“选项”按钮来确定页面上的各项设置，在此只介绍“布局”按钮。点击“布局”按钮，弹出“数据透视

27、表和数据透视图向导布局”，点中右边的“地块”字段，按住鼠标左键，将它拖到左边的“行”区；点中右边的“水稻品种”字段，按住鼠标左键，也将它拖到左边的“行”区；点中右边的“肥料种类”字段，按住鼠标左键，将它拖到上面的“列”区；点中右边的“每亩产量”字段，按住鼠标左键，将它拖到中部的“数据”区域中，如图2-29所示。但在“数据”区域中，“每亩产量”字段前显示的是“求和项”，这是Excel的默认项。图2-29 “数据透视表和数据透视图向导布局”对话框由于要求“每亩产量”，因此，需要改变汇总方式，可双击“数据”区域中的字段“求和项：每亩产量”，打开“数据透视表字段”对话框，在“汇总方式”下拉菜单中点中“

28、平均值”字段，如图2-30所示。“数据透视表字段”对话框的汇总方式有多种，如“求和”、“计数”、“平均值”、“最大值”、“最小值”、“乘积”、“数值计数”等，可根据需要进行选择。如果需要不同的数据显示方式，可点击“数据透视字段”上的“选项”按钮，打开下半部的“数据显示方式”，如图2-31所示。下拉按钮里面包括“普通”、“差异”、“百分比”、“差异百分比”、“按某一字段汇总”、“占同行数据总和的百分比”、“占同列数据总和的百分比”、“占总和的百分比”、“指数”等，可根据需要进行选择。图2-30 “数据透视表字段”对话框（一）图2-31 “数据透视表字段”对话框（二）“汇总方式”与“数据显示方式”选择好之后，点击“数据透视表字段”对话框上的“确定”按钮，再点击“数据透视表和数据透视图向导布局”对话框中的“确定”按钮，则布局调整完毕。单击“数据透视表和数据透视图向导3步骤之3”对话框上的“完成”按钮，则生成不同品种的水稻使用不同种类的肥料亩产单向表，如图2-32所示。图2-32 不同品种的水稻使用不同种类的肥料亩产单向表从图2-32中可以看出，每亩产量是按照“地块”、“水稻品种”、“肥料种类”的顺序进行分类汇总的，如果在步骤中改