第8章 分析和管理数据.pdf

《第8章 分析和管理数据.pdf》由会员分享，可在线阅读，更多相关《第8章 分析和管理数据.pdf（20页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、109第 8 章 分析和管理数据通过本章，你应当学会：（1）排序。（2）筛选。（3）分类汇总。（4）合并计算。在 Excel 中制作好表格后，可以方便地对数据进行分析和管理，如按设置的条件对数据进行排序、筛选，按位置或类别合并计算数据、汇总数据等。8.1 数据的排序排序是统计工作中经常涉及的一项工作，在 Excel 中可以将数据按单个条件进行排序，还可以按多个条件或自定义条件进行排序。8.1.1 按单个条件进行排序如果需要将数据按某一字段进行排序，此时可以使用按单个条件进行排序的方法。按单个条件进行排序，操作步骤如下：（1）打开“计算机应用2班成绩表”工作簿，选择A2:I16单元格区域，选择“

2、数据”选项卡，单击“排序和筛选”组中的“排序”按钮，如图 8-1-1 所示。图 8-1-1Excel 2007 中文版实用教程（2）打开“排序”对话框，在“列”栏下的“主要关键字”下拉列表框中选择需要排序的列，这里选择“总分”，在“排序依据”下拉列表框中选择“数值”选项，在“次序”下拉列表框中选择“降序”选项，表示以总分从高到低进行排序，单击“确定”按钮，如图8-1-2 所示。图 8-1-2（3）此时表格中按总分从高到低进行排序，如图 8-1-3 所示。图 8-1-3如果创建的表格中没有表头，则在“排序”对话框的“主要关键字”下拉列表框中将显示“列A”、“列 B”和“列 C”等选项，此时只需选

3、择其中某个选项即可进行该列的排序。在需要进行排序的列中，选择要排序的单元格后，单击“数据”选项卡“排序与筛选”组中的“升序”按钮或“降序”按钮，可以对该列中的数据从低到高或从高到低进行排序。8.1.2 按多个条件排序当按单个条件进行排序时，有两名学生的总分都是487分，此时可以再按其他条件进行排序，即按多个条件进行排序。按多个条件排序，操作步骤如下：（1）打开“计算机应用 2 班成绩表”工作簿，选择 A2:I16 单元格，选择“数据”选项卡，单击“排序和筛选”组中的“排序”按钮，如图 8-1-4 所示。第 8 章 分析和管理数据图 8-1-4（2）打开“排序”对话框，单击“添加条件”按钮，在“

4、列”栏下的“主要关键字”下拉列表框中选择“总分”，在“排序依据”下拉列表框中选择“数值”选项，在“次序”下拉列表框中选择“降序”选项，表示首先以总分从高到低进行排序。（3）在“次要关键字”下拉列表框中选择“专业英语”，在“排序依据”下拉列表框中选择“数值”选项，在“次序”下拉列表框中选择“降序”选项，表示当总分成绩相同时，以专业英语成绩从高到低进行排序，单击“确定”按钮，如图 8-1-5 所示。图 8-1-5（4）此时表格中显示排序后的结果如图 8-1-6 所示。图 8-1-6在对数据进行排序时，当“主要关键字”和“次要关键字”两项都相同时，可以再以“第三关键字”进行排序。Excel 2007

5、 中文版实用教程在进行升序排列时，当需要排序的对象是数字时就从最小的负数到最大的正数进行排序；若是字母则按 A Z 的顺序进行排序；若为逻辑值则 FLASE 排在 TRUE 之前；若是空格则排在最后，降序排序的结果与升序排序的结果相反。8.1.3 自定义排序在Excel中除了上述的基本排序功能外，还可以按自定义的条件进行排序。如果需要按大专、本科、硕士、博士进行学历排序时，需要先将这些数据定义为序列，然后进行自定义排序。自定义条件排序，操作步骤如下：（1）打开“员工信息表”工作簿，选择“数据”选项卡，单击“排序和筛选”组中的“排序”按钮，如图 8-1-7 所示。（2）打开“排序”对话框，单击其

6、中的“选项”按钮，打开“排序选项”对话框，在“方向”栏中选中“按列排序”单选按钮，在“方法”栏中选中“笔划排序”单选按钮，单击“确定”按钮，如图 8-1-8 所示。图 8-1-7图 8-1-8（3）返回到“排序”对话框，在“列”栏下的“主要关键字”下拉列表框中选择“学历”，在“排序依据”下拉列表框中选择“数值”选项，在“次序”下拉列表框中选择“自定义序列”选项，如图 8-1-9 所示。图 8-1-9第 8 章 分析和管理数据（4）打开“自定义序列”对话框，在“输入序列”文本框中按顺序输入需要定义的数据，这里输入“大专、本科、硕士、博士”，单击“添加”按钮，将输入的序列添加到“自定义序列”列表框

7、中，如图 8-1-10 所示，然后单击“确定”按钮返回“排序”对话框。图 8-1-10（5）在“排序”对话框中单击“确定”按钮，此时表格中将显示所需的排序结果，如图 8-1-11 所示。8.2 数据的筛选Excel 提供了数据筛选功能，通过该功能可以选择性地在大型数据库中只显示满足某一个或某几个条件的记录。筛选有 3 种方式：自动筛选、自定义筛选和高级筛选，下面分别介绍。8.2.1 自动筛选如果想在工作表中只显示满足给定条件的数值，可以使用自动筛选功能。自动筛选，操作步骤如下：（1）打开“计算机应用 2 班成绩表”工作簿，选择表头所在的 A1:I1 单元格区域，选择“数据”选项卡，单击“排序和

8、筛选”组中的“筛选”按钮，如图 8-2-1 所示。图 8-1-11Excel 2007 中文版实用教程（4）此时筛选出“C 语言”成绩高于平均值的 8 个学生记录，如图 8-2-4 所示。图 8-2-4图 8-2-3图 8-2-2图 8-2-1（2）在工作表表头各字段右侧均出现下拉按钮，如图 8-2-2 所示。（3）单击“C 语言”右侧的下拉按钮，在弹出的下拉列表中选择“数字筛选/高于平均值”命令，如图 8-2-3 所示。第 8 章 分析和管理数据若要退出工作表中的筛选状态，只需再次单击“筛选”按钮即可。8.2.2 自定义筛选当需要设置更多条件进行筛选时，可以通过“自定义自动筛选方式”对话框进

9、行设置，从而得到更为准确的筛选结果。自定义筛选，操作步骤如下：（1）打开“计算机应用 2 班成绩表”工作簿，选择表头所在的 A1:I1 单元格区域，选择“数据”选项卡，单击“排序和筛选”组中的“筛选”按钮，如图 8-2-5 所示。（2）单击“总分”右侧的下拉按钮，在弹出的下拉列表中选择“数字筛选/大于或等于”命令，如图 8-2-6 所示。图 8-2-5图 8-2-6Excel 2007 中文版实用教程图 8-2-8在“自定义自动筛选方式”对话框中输入筛选条件时，可以使用“?”代表单个的任意字符，用“*”代表任意多个字符。8.2.3 高级筛选高级筛选是通过已经设置好的条件来对工作表中的数据进行筛

10、选。高级筛选需要在工作表中无数据的地方指定一个区域用于存放筛选条件，这个区域就是条件区域。高级筛选，操作步骤如下：（1）打开“计算机应用 2 班成绩表”工作簿，在空白单元格中建立条件区域，并输入筛选条件，如图 8-2-9 所示。图 8-2-9图 8-2-7（3）打开“自定义自动筛选方式”对话框，在“总分”栏下的第一个下拉列表框中选择“大于”选项，在其右侧的文本框中输入“470”，选择“与”单选按钮，在“与”单选按钮下的第一个下拉列表框中选择“小于”选项，在其右侧的文本框中输入“500”，单击“确定”按钮，如图 8-2-7 所示。（4）此时筛选出总分在 470 到 500 分之间的记录，如图 8

11、-2-8 所示。第 8 章 分析和管理数据图 8-2-10（3）打开“高级筛选”对话框，在“方式”栏下选中“在原有区域显示筛选结果”单选按钮，在“列表区域”文本框中默认选择A1:I16 单元格区域为筛选区域，单击“条件区域”文本框中的按钮，如图8-2-11所示，缩小“高级筛选”对话框。图 8-2-11（4）选择前面建立的条件区域 J6:K7，单击按钮，如图 8-2-12 所示。还原“高级筛选”对话框。图 8-2-12（5）在“高级筛选”对话框中单击“确定”按钮，如图 8-2-13 所示。图 8-2-13（6）此时工作表中显示出数据库成绩大于 85 且专业英语成绩大于 85 的所有学生记录，如图

12、 8-2-14 所示。图 8-2-14（2）选择“数据”选项卡，单击“排序和筛选”组中的“高级”按钮，如图 8-2-10 所示。Excel 2007 中文版实用教程（2）打开“排序”对话框，在“列”栏下的“主要关键字”下拉列表框中选择“所属部门”，在“排序依据”下拉列表框中选择“数值”选项，在“次序”下拉列表框中选择“升序”选项，单击“确定”按钮，如图 8-3-2 所示。图 8-3-1在“高级筛选”对话框中，选择“将筛选结果复制到其他位置”单选按钮后，下面的“复制到”文本框被激活，单击该文本框后的按钮，选择将筛选结果复制到的位置，然后单击按钮，再单击“确定”按钮可将选择出的记录复制到表格中的其

13、他位置。在“高级筛选”对话框中，若勾选“选择不重复的记录”复选框，当有多行满足条件时，只显示或复制惟一的行，而排除重复的行。8.3 数据的分类汇总分类汇总对数据库中指定的字段进行分类，然后统计同一类记录的有关信息。统计的内容可以由用户指定，也可以统计同一类记录的记录条数，还可以对某些数值段求和、求平均值、求极值等。8.3.1 创建分类汇总在创建分类汇总之前，应先对要分类汇总的数据进行排序，即将同类的数据排列在一起。创建分类汇总，操作步骤如下：（1）打开“1 月员工销售业绩表”工作薄，选择所属部门所在的列，选择“数据”选项卡，在“排序和筛选”组中单击“排序”按钮，如图 8-3-1 所示。第 8

14、章 分析和管理数据（3）此时工作表按“所属部门”排序，结果如图 8-3-3 所示。（4）选择“数据”选项卡，单击“分级显示”组中的“分类汇总”按钮，打开“分类汇总”对话框，在“分类字段”下拉列表框中选择“所属部门”选项，在“汇总方式”下拉列表框中选择“求和”选项，在“选定汇总项”列表框中勾选“销售额”复选框，单击“确定”按钮，如图 8-3-4 所示。图 8-3-4（5）此时得到各所属部门的销售额汇总数据，如图 8-3-5 所示。图 8-3-5图 8-3-3图 8-3-2Excel 2007 中文版实用教程（2）此时隐藏“销售 2 部”的详细数据，只显示其汇总结果，如图 8-3-7 所示。图 8

15、-3-6图 8-3-7若不先对数据进行排序操作，则在执行分类汇总操作后，Excel 2007 只会对连续相同的数据进行汇总。在“分类汇总”对话框中，针对不同的汇总类型，可以在“汇总方式”下拉列表框中选择“平均值”和“标准偏差”等汇总方式。在“分类汇总”对话框中，勾选“每组数据分页”复选框，则将得到分页显示的分类汇总结果。8.3.2 显示或隐藏分类汇总为了方便查看数据，可将分类汇总后暂时不需要使用的数据隐藏起来，减少界面的占用空间。当需要查看隐藏的数据时，可再将其显示。隐藏及显示分类汇总，操作步骤如下：（1）打开“1 月员工销售业绩表”工作薄，单击“销售 2 部 汇总”左侧的“折叠”按钮，如图

16、8-3-6 所示。第 8 章 分析和管理数据（3）用同样的方法可以隐藏销售 1 部和销售 3 部的详细数据，完成后如图 8-3-8 所示。（4）单击各所属部门左侧的“展开”按钮，即可显示所隐藏的详细数据，如图8-3-9所示。图 8-3-88.3.3 删除分类汇总查看完分类汇总的数据后，有时需要删除分类汇总，使表格还原至以前的状态。删除分类汇总，操作步骤如下：（1）打开“1月员工销售业绩表”工作薄，选择“数据”选项卡，在“分级显示”组中单击“分类汇总”按钮，如图 8-3-10 所示。图 8-3-9图 8-3-10（2）在打开的“分类汇总”对话框中单击“全部删除”按钮，如图 8-3-11 所示。图

17、 8-3-11Excel 2007 中文版实用教程8.4 合 并 计 算通过合并计算，可以对来自一张或多张工作表中的数据进行汇总，并建立合并计算表，存放合并计算结果的工作表称为“目标工作表”，接收合并数据并参与合并计算的区域称为“源区域”，合并计算的方法有两种：即按位置合并计算和按分类合并计算。8.4.1 按位置合并计算按位置合并计算数据时，要求在所有源区域中的数据被同样排列，也就是每一个工作表中的记录名称和字段名称均在相同的位置上。按位置合并计算，操作步骤如下：（1）打开“1 季度员工销售业绩表”工作簿，在“1 月”、“2 月”和“3 月”工作表中已输入相应月份的数据，如图 8-4-1 所示

18、。图 8-4-1（2）选择“1 季度”工作表，选择 E3:E17 单元格区域为目标区域，如图 8-4-2 所示。图 8-3-12（3）此时工作表还原到以前的状态，如图 8-3-12 所示。第 8 章 分析和管理数据图 8-4-2（3）选择“数据”选项卡，单击“数据工具”组中的“合并计算”按钮，如图 8-4-3 所示。图 8-4-3（4）打开“合并计算”对话框，在“函数”下拉列表框中选择“求和”函数，单击“引用位置”文本框后的按钮，如图 8-4-4 所示。图 8-4-4（5）选择“1 月”工作表，选择 E3:E17 单元格区域，单击按钮还原对话框，如图8-4-5 所示。图 8-4-5Excel

19、2007 中文版实用教程（6）在“合并计算”对话框中单击“添加”按钮，此时选择的区域被添加到了“所有引用位置”列表框中，如图 8-4-6 所示。图 8-4-6（7）用同样的方法分别将“2月”和“3月”工作表的 E3:E17 单元格区域添加到“所有引用位置”列表框中，单击“确定”按钮，如图8-4-7 所示。图 8-4-7（8）此时在“1 季度”工作表的 E3:E17 单元格区域中将显示合并计算的结果，如图8-4-8 所示。图 8-4-8在“合并计算”对话框的“函数”下拉列表框中可以选择“求和”、“最大值”、“最小值”和“乘积”等函数作为合并计算的函数。在“合并计算”对话框的“所有引用位置”列表框

20、中选择某项后，单击“删除”按钮可以删除该选项。第 8 章 分析和管理数据8.4.2 按分类合并计算如果各月份公司所属员工姓名不尽相同，所放位置也不一定相同时，同样可以使用合并计算功能来完成汇总工作，但此时不能使用前面介绍的按位置合并计算，而应使用按分类合并计算数据。按分类合并计算，操作步骤如下：（1）打开“1 季度销售表”工作簿，选择“1 季度”工作表，选择 A2 单元格，将其作为目标区域的起始单元格，如图 8-4-9 所示。图 8-4-9（2）选择“数据”选项卡，单击“数据工具”组中的“合并计算”按钮，如图 8-4-10 所示。图 8-4-10（3）打开“合并计算”对话框，在“函数”下拉列表

21、框中选择“求和”函数，单击“引用位置”文本框后的按钮，如图8-4-11所示。图 8-4-11（4）选择“1 月”工作表，选择 A2:E17 单元格区域，单击按钮还原对话框，如图8-4-12 所示。Excel 2007 中文版实用教程图 8-4-12图 8-4-13图 8-4-14图 8-4-15（5）在“合并计算”对话框中单击“添加”按钮，此时选择的单元格区域被添加到“所有引用位置”列表框中，如图 8-4-13 所示。（6）用同样的方法分别将“2 月”和“3 月”工作表的 A2:E17 单元格区域添加到“所有引用位置”列表框中，勾选“首行”和“最左列”复选框，单击“确定”按钮，如图 8-4-1

22、4 所示。（7）此时在“1 季度”工作表的单元格区域将显示合并计算后的结果，如图 8-4-15 所示。第 8 章 分析和管理数据图 8-4-16（8）在 B、C、D 列单元格区域内输入相应的数据，完成后如图 8-4-16 所示。按类合并计算数据时，必须包含行或列标志。若分类标志在顶端，应在“合并计算”对话框中勾选“首行”复选框；若分类标志在最左列，则应勾选“最左列”复选框，也可以同时勾选两个复选框。此外，标志还要注意区分大小写，如果分别以大小写输入同样的拼写，将被系统视为不同的标准。8.5 小 结本章主要介绍了 Excel 分析和管理数据的功能，其中对数据进行排序、筛选和分类汇总是管理表格常用

23、的操作。通过本章的学习，读者应能掌握数据管理的基本方法和技巧，轻松地管理表格中的数据。8.6 练 习填空题（1）排序有 3 种方式：、。（2）在进行升序排序时，当需要排序的对象是数字时就从最小的负数到最大的正数进行排序；若是字母则按的顺序进行排序；若为逻辑值则 FLASE 排在 TRUE；若是空格则排在。（3）筛选有 3 种方式：、。Excel 2007 中文版实用教程（4）在“分类汇总”对话框中勾选复选框，则将得到分页显示的分类汇总结果。（5）按位置合并计算数据时，要求，也就是每一个工作表中的记录名称和字段名称均在相同的位置上。简答题（1）如何进行排序操作？（2）如何进行筛选操作？（3）合并计算的方法有哪两种？它们各自有什么特点？上机练习（1）打开“计算机应用 2 班成绩表”工作簿，如图 8-6-1 所示。图 8-6-1（2）按“数据库”成绩从高到低进行排序，若数据库成绩相同时，再以专业英语成绩进行降序排序。（3）筛选出“管理信息系统”成绩在 80 分到 90 分之间的所有记录。（4）通过高级筛选功能筛选出“管理信息系统”成绩大于 80 分，并且“专业英语”成绩大于 80 分的记录。