利用Excel进行统计数据的整理.docx
《利用Excel进行统计数据的整理.docx》由会员分享，可在线阅读，更多相关《利用Excel进行统计数据的整理.docx（47页珍藏版）》请在冰点文库上搜索。
利用Excel进行统计数据的整理
第二章利用Excel进行统计数据的整理
本章主要讲解如何利用Excel进行统计整理。
通过本章的学习，学生应掌握如下内容：
利用Excel进行数据排序与筛选、统计分组统计数据的透视分析、图表的绘制等。
第一节利用Excel进行排序与分组
一、利用Excel进行统计数据的排序与筛选
㈠利用Excel进行统计数据的排序
利用Excel进行数据的排序是以数据清单中一个或几个字段为关键字，对整个数据清单的行或列重新进行排列。
排序时，Excel将利用指定的排序顺序重新排列行、列或各单元格。
对于数字型字段，排序按数值的大小进行；对于字符型字段，排序按ASCⅡ码大小进行；中文字段按拼音或笔画进行。
通过排序，可以清楚地反映数据之间的大小关系，从而使数据的规则性更加简洁地表现出来。
【例2.1】某大学二年级某班一个组学生期末考试成绩如表2-1所示，请按某一课程成绩排序。
表2-1某毕业班学生毕业就业情况表单位：
分
学号
姓名
统计学
英语
数学
经济学
管理学
3101
李双林
78
80
88
73
81
3102
马贵初
89
75
56
78
70
3103
张三丰
92
48
60
84
66
3104
陈迁和
66
74
72
66
74
3105
欧阳梅
80
63
55
59
82
3106
叶建斌
52
71
75
63
75
3107
沈汉杰
77
88
84
80
80
3108
黄伶俐
96
90
77
90
81
3109
戈思思
63
67
66
74
66
3110
刘婷婷
82
71
90
66
78
对某一课程成绩排序（按单字段排序），最简单的方法是，将表2-1中的数据复制到Excel工作表中，然后直接点击工具栏上的升序排序按钮“
”或降序排序按钮“
”即可。
比如对英语成绩排序，只要单击该字段下任一单元格，或单击该字段的列标，再单击升序排序按钮或降序排序按钮，就可完成英语课程成绩字段的升序排序或降序排序。
图2-1选择列标时“排序警告”对话框
需要说明的是：
当单击的是字段下某一单元格时，直接点击工具栏上的升序排序按钮或降序排序按钮即可完成排序工作；而当单击的是该字段的列标时，点击工具栏上的升序排序按钮或降序排序按钮后会跳出一个“排序警告”对话框（见图2-1），不用理会，直接单击排序按钮，也可完成排列工作（见图2-2）。
这种排序方法简便快捷。
图2-2对英语成绩按升序排序的结果
对该组学生课程成绩排序也可按多字段方式排序，其操作步骤如下：
⑴单击英语字段下任一单元格。
⑵单击菜单栏上的“数据”中的“排序”选项，弹出“排序”对话框，如图2-3所示。
图2-3“多字段排序方式”对话框
⑶在“排序”对话框中，在“主要关键字”中点开下拉按钮“
”，在下拉列表中选择“英语（分）”；在“次要关键字”中点开下拉按钮“
”，在下拉列表中选择“数学（分）”；在“第三关键字”中点开下拉按钮“
”，在下拉列表中选择“经济学（分）”；在右边的单选按钮中都选择中“升序”。
⑷单击“确定”按钮，即可得到排序结果。
需要说明的是，这个排序结果与“按单字段排序”的结果一致，原因在于Excel按某一字段排序的“联动性”，即在按某一字段排序时，其他字段跟随该字段进行调整，而不会单独分离。
当然，如果进一步设置，还可单击“排序”对话框中的“选项”按钮，在弹出的“排序选项”对话框（如图2-4所示）中进行详细设置。
不过可以肯定的是，不管如何设置，结果将与按单字段排序的结果如出一辙。
图2-4“排序选项”对话框
㈡利用Excel进行统计数据的筛选
利用Excel进行数据筛选，可以把符合要求的数据集中在一起把不符合要求的数据隐藏起来。
数据的筛选包括自动筛选和高级筛选两项功能。
⒈自动筛选
自动筛选是一种快速的筛选方法，可以方便地将满足条件的数据显示在工作表上，将不满足条件的数据隐藏起来。
如要进行自动筛选，只要单击想自动筛选字段下某一单元格，如要对英语成绩进行自动筛选，则只需选定英语成绩字段下某一单元格，点开菜单栏中“数据”中的“筛选”项下的“自动筛选”即可，点击后的结果如图2-5所示。
图2-5打开“自动筛选”功能
在“自动筛选”界面下，点开下拉按钮“
”，在自动筛选的下拉列表中又包括全部、前×个筛选、自定义三种。
⑴全部筛选。
由于全部筛选是将数据清单中全部数据列入其中，因此，该筛选与不筛选并没有什么区别。
⑵前×个筛选。
根据需要，确定前几项的个数。
比如，英语成绩中只要列出前5名，则只需在选定英语成绩字段下某一单元格，输入“前5项”（见图2-6），点击“确定”按钮即可（见图2-7）。
图2-6“自动筛选前10个”对话框
图2-7前5个排序结果示意图
⑶自定义筛选。
在“自动筛选”界面下，点开下拉按钮“
”，在自动筛选的下拉列表中选择“自定义”选项，出现“自定义自动筛选方式”对话框（见图2-8）。
图2-8“自定义自动筛选方式”对话框
在“自定义自动筛选方式”对话框中，可以设置两组筛选条件。
第一组筛选条件由关系运算符构成条件表达式，其中左侧运算符有“等于”、“不等于”、“大于”、“大于或等于”、“小于”、“小于或等于”、“始于”、“并非起始于”、“止于”、“并非结束于”、“包含”、“不包含”等。
在对话框右侧，可以按下拉按钮选择值，也可以直接键入数据。
两组条件之间可以是“与”或者是“或”的关系。
⒉高级筛选
高级筛选是一种可以满足指定条件的筛选方法。
以[例2.1]为例，查找英语成绩不及格、经济学成绩高于80的同学。
筛选步骤如下：
⑴设定条件区域。
将单元格A1：
G1的内容复制到A13：
G13的单元格，在单元格D14，F14中分别输入“<60”和“>80”，如图2-9所示。
A1：
G12为数据清单区域，A13：
G14为条件区域，一般用一空行将两区域分隔。
条件区域第一行为字段名，第二行及以下各行为条件值。
同一行条件之间为“与”的关系，不同行条件之间为“或”的关系，可采用的条件符号有“＞”、“＜”、“≥”、“≤”。
图2-9数据清单区域与条件区域
⑵单击数据清单中任一单元格如D2，打开菜单栏中的“数据”，在“筛选”项下选择“高级筛选”，打开“高级筛选”对话框。
在“列表区域”中输入“$A$1：
$G$11”，在条件区域中输入“$A$13：
$G$14”，如图2-10所示。
图2-10“高级筛选”对话框
⑶单击“确定”按钮，显示筛选结果，如图2－11所示。
图2－11高级筛选结果
⑷如果想将筛选的结果复制到其他位置，可以在“高级筛选”对话框中的“方式”选项下选择“将筛选结果复制到其他位置”，激活“条件区域”下的“复制到”选项，输入指定位置左上角的单元格行列号，再单击“确定”按钮即可。
如果对重复记录不想全部显示，可以选中对话框中的“选择不重复的记录”复选框，则如果有重复记录，就只显示一条。
若要退出高级筛选，可在“数据”菜单中再次选择“筛选——全部显示”命令，即可恢复原来的数据清单。
二、利用Excel进行统计分组
用Excel进行统计分组和编制频数分布表有两种方法，一是函数法；二是利用数据分析中的“直方图”工具。
㈠函数法
在Excel中利用函数进行统计分组和编制频数分布表可利用COUNTIF（）和FREQUENCY（）等函数，但要根据变量值的类型不同而选择不同的函数。
当分组标志是品质标志时应使用COUNTIF（）函数；当分组标志是数量标志时应使用FREQUENCY（）函数。
⒈COUNTIF（）函数
COUNTIF（）函数的语法构成是：
COUNTIF（区域，条件）。
具体使用方法举例如下。
【例2.2】已知某学院某系某毕业班学生共有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编制此调查数据的频数分布表。
首先将数据输入Excel单元格中，其次观察数据的类型个数，在工作表中的空余位置列出各组名称，如图2-12所示。
图2-12某毕业班学生毕业就业情况资料
具体操作步骤如下：
⑴将上述资料输入Excel工作表；在单元格D2中输入“工作单位性质”，在E2中输入“学生人数”，在D3：
D6区域中依次输入“国家机关”、“事业单位”、“企业”、“自主创业”，表示分组方式，同时这也可以表示分组组限，如图2-12所示。
⑵选择单元格E3至E6区域，在“插入”菜单中单击“函数”选项，打开
图2-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:
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某班学生英语成绩表
学号
成绩（分）
学号
成绩（分）
03101
78
03126
75
03102
89
03127
84
03103
92
03128
77
03104
66
03129
66
03105
80
03130
90
03106
52
03131
73
03107
77
03132
78
03108
96
03133
84
03109
63
03134
66
03110
82
03135
59
03111
80
03136
63
03112
75
03137
80
03113
48
03138
90
03114
74
03139
74
03115
63
03140
66
03116
71
03141
81
03117
88
03142
70
03118
90
03143
66
03119
67
03144
74
03120
71
03145
82
03121
88
03146
75
03122
56
03147
80
03123
60
03148
81
03124
72
03149
66
03125
55
03150
78
请用Excel编制此调查数据的频数分布表。
具体操作步骤如下：
⑴将上述资料输入Excel工作表；在单元格D2中输入“分组”，在E2中输入“分组组限”，在单元格F2中输入“频数”；在D3：
D7区域中依次输入“60以下”，“60～70”，“70～80”，“80～90”，“90～100”，表示分组方式，但是这还不能作为频数接收区间；在E3：
E7区域中依次输入59，69，79，89，100，表示分组组限，作为频数接收区间，它们分别表明60分以下的人数，60分以上、70分以下的人数等，这与前列分组方式是一致的，如图2-16所示。
图2-16学生英语成绩资料
⑵选择单元格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+Shift”组合键，同时按回车键，得到频数分布，如图2-19所示。
图2-19频数分布结果
另外，直接利用Excel函数公式也可以得到同样结果。
用鼠标选定单元格“F3:
F7”，注意不要释放选定区域。
在F3单元格中输入频数分布函数公式：
FREQUENCY（B2:
B51,E3:
E7）。
在这个公式中，数据区域为“B2:
B51”，接收区间为“E3:
E7”，按“Ctrl+Shift”组合键，同时按回车键，得到频数分布与上面相同。
㈡利用“直方图”工具进行统计分组
直方图分析工具是一个用于确定数据的频数分布、累计频数分布，并提供直方图的分析模块。
它在给定工作表中数据单元格区域和接收区间的情况下，计算数据的频数和累积频数。
【例2.4】仍以“表2－3某班学生英语成绩表”为例，试编制此调查数据的频数分布表。
具体操作步骤如下：
第一，在“工具”菜单中，单击“数据分析”选项，弹出“数据分析”对话框，如图2-20所示。
图2-20“数据分析”对话框
注意：
如果用户在Excel的“工具”菜单中没有找到“数据分析”选项，说明用户安装Excel不完整，必须在Excel中重新安装“分析工具库”的内容。
具体安装方法如下。
⑴在“工具”菜单中，单击“加载宏”选项。
⑵选中“分析工具库”和“分析工具库-VBA函数”复选框，单击“确定”按钮，将会引导用户进行安装，如图2-21所示。
图2-21“加载宏”对话框
如果用户在安装Excel时选择的是“典型安装”，则需要使用CD-ROM进行安装，如果用户在安装Excel时选择的是“完全安装”，则Excel会从硬盘中直接进行安装。
⑶无论是何种情况，安装完毕后，“数据分析”选项会自动出现在Excel的工具菜单中。
第二，在“分析工具”列表框中，单击“直方图”分析工具，则会弹出“直方图”对话框，如图2-22所示。
图2-22“直方图”对话框
第三，选择输入选项。
输入区域：
在此输入待分析数据区域的单元格引用；
接收区域：
表示分组标志所在的区域，在此输入接收区域的单元格引用，该区域应包含一组可选的用来定义接收区间的边界值，这些值应当按升序排列，如本例中的“分组组限”。
关于这一点，与前面所讲的FREQUENCY函数一致。
在“输入区域”中，输入“$B$10：
$B$59”；选好接收区域的内容“$E$2：
$E$7”。
第四，选择输出选项。
输出选项中可选择输出区域、新工作表或新工作簿。
在这里选择输出区域，可以直接选择一个区域，也可以直接输出一个单元格，该单元格代表输出区域的左上角，这里常常只输入一个单元格，如本例中$I$11，因为我们往往事先并不知道具体的输出区域有多大。
输出选项中还有以下选项：
柏拉图：
选中此复选框，可以在输出表中同时按降序排列频率数据。
如果此复选框被清除，Excel将只按升序来排列数据。
累积百分比：
选中此复选框，可以在输出表中添加一列累积百分比数值，并同时在直方图表中添加累积百分比折线。
如果清除此选项，则会省略累积百分比。
图表输出：
选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。
本例中，我们选中“累积百分率”和“图表输出”两个复选框。
第五，单击“确定”按钮，可得输出结果，如图2-23所示。
图2-23频数分布和直方图
注意：
在默认的直方图中，柱形彼此分开，如果要将其连接起来，操作步骤如下：
⑴单击某个柱形，单击鼠标右键，在弹出菜单中，选择“数据系列格式”选项，
弹出“数据系列格式”对话框，如图2-24所示。
图2-24“数据系列格式”对话框
⑵在对话框中选择“选项”标签，将间距宽度从150改成0，点上“依数据点分色”，再单击“确定”按钮，得到直方图如图2-25所示。
图2-25调整后的直方图
第二节利用Excel进行统计数据的透视分析
Excel提供了数据透视表和数据透视图的功能，下面分别就数据透视表与数据透视图的操作进行举例说明。
一、数据透视表
数据透视表将排序、筛选及分类汇总功能结合起来，对数据清单或各数据重新组织和计算，并以多种不同的形式显示出来。
利用数据透视图，可以更直观地显示数据。
下面就Excel中如何操作数据透视表进行举例介绍。
【例2.5】为了验证不同肥料对不同品种水稻的使用效果，某农场对三块地的糯谷稻、籼稻和杂交稻分别施用化肥、有机肥与复合肥等三种不同的肥料，获得每亩产量样本数据如表2-4所示。
利用数据透视表比较不同地块、不同水稻品种、不同种类肥料的每亩不同产量情况。
表2-4三个地块、三个水稻品种、三种不同施肥方案的农作物每亩产量情况表
单位：
千克/亩
地块
水稻品种
肥料种类
每亩产量
地块一
糯谷稻
化肥
518
地块一
糯谷稻
有机肥
526
地块一
糯谷稻
复合肥
525
地块一
籼稻
化肥
509
地块一
籼稻
有机肥
523
地块一
籼稻
复合肥
528
地块一
杂交稻
化肥
521
地块一
杂交稻
有机肥
519
地块一
杂交稻
复合肥
532
地块二
糯谷稻
化肥
526
地块二
糯谷稻
有机肥
519
地块二
糯谷稻
复合肥
536
地块二
籼稻
化肥
531
地块二
籼稻
有机肥
543
地块二
籼稻
复合肥
533
地块二
杂交稻
化肥
528
地块二
杂交稻
有机肥
529
地块二
杂交稻
复合肥
542
地块三
糯谷稻
化肥
522
地块三
糯谷稻
有机肥
537
地块三
糯谷稻
复合肥
527
地块三
籼稻
化肥
534
地块三
籼稻
有机肥
553
地块三
籼稻
复合肥
546
地块三
杂交稻
化肥
551
地块三
杂交稻
有机肥
548
地块三
杂交稻
复合肥
563
⒈检验操作步骤
⑴将表2-4数据复制到Excel工作表中。
⑵单击单元格F2，在“数据”菜单中选择“数据透视表和数据透视图”选项，弹出“数据透视表和数据透视图向导——3步骤之1”对话框，如图2-26所示。
在“请指定待分析数据的数据源类型”中选择默认的“MicrosoftOfficeExcel数据列表或数据库”，在“所需创建的报表类型”中选择默认的“数据透视表”。
图2-26“数据透视表和数据透视图向导——3步骤之1”对话框
⑶点击“下一步”按钮，弹出“数据透视表和数据透视图向导——3步骤之2”对话框，如图2-27所示。
在“选定区域”中选择“数据透视表及透视图！
$A$1:
$D$28”。
图2-27“数据透视表和数据透视图向导——3步骤之2”对话框
⑷点击“下一步”按钮，弹出“数据透视表和数据透视图向导——3步骤之3”对话框，如图2-28所示。
在“数据透视表显示位置”中选择“现有工作表”，并选择好操作好的数据透视表的输出位置“数据透视表及透视图！
$F$2”。
图2-28“数据透视表和数据透视图向导——3步骤之3”对话框
在图2-28中有“布局”与“选项”按钮，可以用“布局”按钮来调整工作表的分布，用“选项”按钮来确定页面上的各项设置，在此只介绍“布局”按钮。
⑸点击“布局”按钮，弹出“数据透视表和数据透视图向导——布局”，点中右边的“地块”字段，按住鼠标左键，将它拖到左边的“行”区；点中右边的“水稻品种”字段，按住鼠标左键，也将它拖到左边的“行”区；点中右边的“肥料种类”字段，按住鼠标左键，将它拖到上面的“列”区；点中右边的“每亩产量”字段，按住鼠标左键，将它拖到中部的“数据”区域中，如图2-29所示。
但在“数据”区域中，“每亩产量”字段前显示的是“求和项”，这是Excel的默认项。
图2-29“数据透视表和数据透视图向导——布局”对话框
⑹由于要求“每亩产量”，因此，需要改变汇总方式，可双击“数据”区域中的字段“求和项：
每亩产量”，打开“数据透视表字段”对话框，在“汇总方式”下拉菜单中点中“平均值”字段，如图2-30所示。
“数据透视表字段”对话框的汇总方式有多种，如“求和”、“计数”、“平均值”、“最大值”、“最小值”、“乘积”、“数值计数”等，可根据需要进行选择。
如果需要不同的数据显示方式，可点击“数据透视字段”上的“选项”按钮，打开下半部的“数据显示方式”，如图2-31所示。
下拉按钮里面包括“普通”、“差异”、“百分比”、“差异百分比”、“按某一字段汇总”、“占同行数据总和的百分比”、“占同列数据总和的百分比”、“占总和的百分比”、“指数”等，可根据需要进行选择。
图2-30“数据透视表字段”对话框
（一）
图2-31“数据透视表字段”对话框
（二）
⑺“汇总方式”与“数据显示方式”选择好之后，点击“数据透视表字段”对话框上的“确定”按钮，再点击“数据透视表和数据透视图向导——布局”对话框中的“确定”按钮，则布局调整完毕。
⑻单击“数据透视表和数据透视图向导——3步骤之3”对话框上的“完成”按钮，则生成不同品种的水稻使用不同种类的肥料亩产单向表，如图2-32所示。
图2-32不同品种的水稻使用不同种类的肥料亩产单向表
从图2-32中可以看出，每亩产量是按照“地块”、“水稻品种”、“肥料种类”的顺序进行
升级会员 