管理Excel工作表中的数据

1、第十三章 管理Excel工作表中的数据 l掌握对数据进行简单和多关键字排序的方法。l掌握对数据进行自动筛选、按条件筛选和高级筛选的方法。l掌握对数据进行简单分类汇总、多重分类汇总和嵌套分类汇总的方法，以及分级显示数据，或取消分类汇总操作。l掌握对数据按位置和分类进行合并计算的方法能力目标能力目标l能够对够工作表中的数据进行排序。l能够根据需要从工作表中找出满足一定条件的数据，其他数据隐藏。l能够根据需要对工作表中的数据分门别类地进行统计处理，如进行求和、求平均值、统计个数、求最大值（最小值）等计算，并且分级显示汇总的结果。l能够根据需要将各单独工作表中的数据合并计算（如求和、求平均值等）到一个

2、主工作表中。 知识目标知识目标本章任务列表l任务一任务一 数据排序数据排序 l任务二任务二 数据筛选数据筛选 l任务三任务三 分类汇总分类汇总 l任务四任务四 合并计算合并计算 任务一 数据排序一一简单排序简单排序 二二多关键字排序多关键字排序 排序是对工作表中的数据进行重新组织排序是对工作表中的数据进行重新组织安排的一种方式。在安排的一种方式。在Excel中可以对一列或多中可以对一列或多列中的数据按文本、数字以及日期和时间进列中的数据按文本、数字以及日期和时间进行排序，还可以按自定义序列（如大、中、行排序，还可以按自定义序列（如大、中、小）进行排序。小）进行排序。 一、简单排序 简单排序是指

3、对数据表中的单列数据按照简单排序是指对数据表中的单列数据按照Excel默认的升序或降序的方式排列。单击要进默认的升序或降序的方式排列。单击要进行排序的列中的任一单元格，再单击行排序的列中的任一单元格，再单击“数据数据”选选项卡上项卡上“排序和筛选排序和筛选”组中组中“升序升序”按钮或按钮或“降降序序”按钮，所选列即按升序或降序方式进行排序。按钮，所选列即按升序或降序方式进行排序。 在Excel中，不同数据类型的默认排序方式如下:升序排序升序排序l数字数字：按从最小的负数到最大的正数进行排序。l日期日期：按从最早的日期到最晚的日期进行排序。l文本文本：按照特殊字符、数字（09）、小写英文字母（a

4、z）、大写英文字母（AZ）、汉字（以拼音排序）排序。l逻辑值逻辑值：FALSE排在TRUE之前。l错误值错误值：所有错误值（如#NUM!和#REF!）的优先级相同。l空白单元格空白单元格：总是放在最后。降序排序降序排序:与升序排序的顺序相反。 当工作表中的单元格引用了其他单元格内的数据时，当工作表中的单元格引用了其他单元格内的数据时，有可能因排序的关系，使公式的引用地址错误，从而使有可能因排序的关系，使公式的引用地址错误，从而使工作表中的数据不正确。工作表中的数据不正确。 二、多关键字排序 多关键字排序就是对工作表中的数据按两个或两个以上的关多关键字排序就是对工作表中的数据按两个或两个以上的关

5、键字进行排序。在此排序方式下，为了获得最佳结果，要排序的键字进行排序。在此排序方式下，为了获得最佳结果，要排序的单元格区域应包含列标题。单元格区域应包含列标题。 对多个关键字进行排序时，在主要关键字完全相同的情况下，对多个关键字进行排序时，在主要关键字完全相同的情况下，会根据指定的次要关键字进行排序；在次要关键字完全相同的情会根据指定的次要关键字进行排序；在次要关键字完全相同的情况下，会根据指定的下一个次要关键字进行排序，依次类推。况下，会根据指定的下一个次要关键字进行排序，依次类推。 任务实施排序2011年春季成绩表数据 先按总分降序排序，先按总分降序排序，“总分总分”相同的，按相同的，按“

6、关系管理关系管理”降序排降序排序序,若仍然相同，按若仍然相同，按“经济法经济法”升序排序升序排序任务二 数据筛选 l一、自动筛选一、自动筛选l二、按条件筛选二、按条件筛选 l三、高级筛选三、高级筛选 l四、取消筛选四、取消筛选 在对工作表数据进行处理时，有时需要从工作在对工作表数据进行处理时，有时需要从工作表中找出满足一定条件的数据，这时可以用表中找出满足一定条件的数据，这时可以用Excel的数据筛选功能显示符合条件的数据，而将不符合的数据筛选功能显示符合条件的数据，而将不符合条件的数据隐藏起来。条件的数据隐藏起来。Excel提供了自动筛选、按提供了自动筛选、按条件筛选和高级筛选三种筛选方式，

7、无论使用哪种条件筛选和高级筛选三种筛选方式，无论使用哪种方式，要进行筛选操作，数据表中必须有列标签。方式，要进行筛选操作，数据表中必须有列标签。 一、自动筛选 自动筛选一般用于简单的筛选，筛选时将不需要自动筛选一般用于简单的筛选，筛选时将不需要显示的记录暂时隐藏起来，只显示符合条件的记录。显示的记录暂时隐藏起来，只显示符合条件的记录。 筛选出成绩表中筛选出成绩表中“国国际贸易际贸易”成绩大于等成绩大于等于于90分的记录分的记录 二、按条件筛选 在在Excel中，还可按用户自定的筛选条件筛选出符中，还可按用户自定的筛选条件筛选出符合需要的数据。先进行筛选操作，然后单击列标题右侧合需要的数据。先进

8、行筛选操作，然后单击列标题右侧的筛选箭头，在打开的筛选列表选择所需选项并在打开的筛选箭头，在打开的筛选列表选择所需选项并在打开的对话框中进行设置。的对话框中进行设置。 筛选出“平均分”大于80小于90的记录 如果所筛选的列中记录为文本型或日期型数据，如果所筛选的列中记录为文本型或日期型数据，筛选列表中的筛选列表中的“数字筛选数字筛选”项会变成项会变成“文本筛选文本筛选”或或“日期筛选日期筛选”，其操作与，其操作与“数字筛选数字筛选”相似。此外，相似。此外，如果对工作表中的单元格填充了颜色，还可以按颜色如果对工作表中的单元格填充了颜色，还可以按颜色对工作表进行筛选。对工作表进行筛选。 三、高级筛

9、选 高级筛选用于条件较复杂的筛选操作，其筛选结果高级筛选用于条件较复杂的筛选操作，其筛选结果可显示在原数据表格中，不符合条件的记录被隐藏起来，可显示在原数据表格中，不符合条件的记录被隐藏起来，也可以在新的位置显示筛选结果，不符合条件的记录同也可以在新的位置显示筛选结果，不符合条件的记录同时保留在数据表中，从而便于进行数据的对比。时保留在数据表中，从而便于进行数据的对比。 要通过多个条件来筛选单元格区域，应首先在选定要通过多个条件来筛选单元格区域，应首先在选定工作表中的指定区域创建筛选条件，然后单击工作表中的指定区域创建筛选条件，然后单击“数据数据”选项卡上选项卡上“排序和筛选排序和筛选”组中的

10、组中的“高级高级”按钮，打开按钮，打开“高级筛选高级筛选”对话框，接下来分别选择要筛选的单元格对话框，接下来分别选择要筛选的单元格区域、筛选条件区域和保存筛选结果的目标区域。区域、筛选条件区域和保存筛选结果的目标区域。 在高级筛选中，筛选条件又可分为多条件筛选和多在高级筛选中，筛选条件又可分为多条件筛选和多选一条件筛选两种，下面分别说明。选一条件筛选两种，下面分别说明。 1多条件筛选多条件筛选多条件筛选是指查找出同时满足多个多条件筛选是指查找出同时满足多个条件的记录。条件的记录。 条件的标题条件的标题必须与筛选必须与筛选区域的标题区域的标题保持一致保持一致“级别级别”为良，且为良，且“就业指就

11、业指导导”成绩大于成绩大于80的记录的记录 确认列表区域确认列表区域的的单元格引用是否单元格引用是否正确，如果不正正确，如果不正确，重新在工作确，重新在工作表中进行选择表中进行选择 2多选一条件筛选多选一条件筛选 多选一条件筛选是指在查找时只要满足几个多选一条件筛选是指在查找时只要满足几个条件当中的一个，记录就会显示出来。多选一条条件当中的一个，记录就会显示出来。多选一条件筛选的操作与多条件筛选类似，只是需要将条件筛选的操作与多条件筛选类似，只是需要将条件输入在不同的行中。件输入在不同的行中。 “级别级别”为良，或者为良，或者“就业指导就业指导”成绩大成绩大于于80的记录的记录 若在若在“高级

12、筛选高级筛选”对话框选中对话框选中“将筛选结果将筛选结果复制到其他位置复制到其他位置”单选钮，则单选钮，则“复制到复制到”选项变为选项变为可用，此时可在该编辑框中单击，然后在工作表单可用，此时可在该编辑框中单击，然后在工作表单击要放置筛选结果单元格区域的左上角单元格。击要放置筛选结果单元格区域的左上角单元格。 四、取消筛选 对于不再需要的筛选可以将其取消。若要取消对于不再需要的筛选可以将其取消。若要取消在数据表中对某一列进行的筛选，可以单击该列列在数据表中对某一列进行的筛选，可以单击该列列标签单元格右侧的筛选按钮，在展开的列表中选择标签单元格右侧的筛选按钮，在展开的列表中选择“全选全选”复选框

13、，然后单击复选框，然后单击“确定确定”按钮。此时筛按钮。此时筛选按钮上的筛选标记消失，该列所有数据显示出来。选按钮上的筛选标记消失，该列所有数据显示出来。 若要取消在工作表中对所有列进行的筛选，可若要取消在工作表中对所有列进行的筛选，可单击单击“数据数据”选项卡上选项卡上“排序和筛选排序和筛选”组中的组中的“清清除除”按钮按钮 ，此时筛选标记消失，所有列数据显，此时筛选标记消失，所有列数据显示出来；若要删除工作表中的三角筛选箭头，可单示出来；若要删除工作表中的三角筛选箭头，可单击击“数据数据”选项卡上选项卡上“排序和筛选排序和筛选”组中的组中的“筛选筛选”按钮按钮 。任务实施筛选商品促销折算表

14、数据 下面将下面将“商品促销折算表商品促销折算表”中中“类别类别”为为A，“销售销售量量”大于大于190，“折后总额折后总额”大于大于2000的记录筛选出来。的记录筛选出来。 输入筛选条件，打开输入筛选条件，打开“高级筛选高级筛选”对话框，选择筛选对话框，选择筛选结果的放置方式，最后设置筛选项。结果的放置方式，最后设置筛选项。 确认列表区域确认列表区域的单元格引用是否正确，的单元格引用是否正确，如果不正确，重新在工作表中进行选择如果不正确，重新在工作表中进行选择 将筛选结将筛选结果复制到果复制到其他位置其他位置 筛选出筛选出“类类别别”为为A，“销售量销售量”大于大于190，“折后总额折后总额

15、”大于大于2000的的记录记录任务三 分类汇总 l一、简单分类汇总一、简单分类汇总l二、多重分类汇总二、多重分类汇总 l三、嵌套分类汇总三、嵌套分类汇总 l四、分级显示数据四、分级显示数据 l五、取消分类汇总五、取消分类汇总 分类汇总是把数据表中的数据分类汇总是把数据表中的数据分门别类地进行统计处理，无需建分门别类地进行统计处理，无需建立公式，立公式，Excel将会自动对各类别将会自动对各类别的数据进行求和、求平均值、统计的数据进行求和、求平均值、统计个数、求最大值（最小值）和总体个数、求最大值（最小值）和总体方差等多种计算，并且分级显示汇方差等多种计算，并且分级显示汇总的结果，从而增加了工作

16、表的可总的结果，从而增加了工作表的可读性，使用户能更快捷地获得需要读性，使用户能更快捷地获得需要的数据并做出判断。的数据并做出判断。 分类汇总分为简单分类汇总、多重分类汇总和嵌套分类汇分类汇总分为简单分类汇总、多重分类汇总和嵌套分类汇总三种方式。无论哪种方式，要进行分类汇总的数据表的第一总三种方式。无论哪种方式，要进行分类汇总的数据表的第一行必须有列标签，而且在分类汇总之前必须先对数据进行排序，行必须有列标签，而且在分类汇总之前必须先对数据进行排序，以使得数据中拥有同一类关键字的记录集中在一起，然后再对以使得数据中拥有同一类关键字的记录集中在一起，然后再对记录进行分类汇总操作。记录进行分类汇总

17、操作。一、简单分类汇总 简单分类汇总指对数据表中的某一列以一种简单分类汇总指对数据表中的某一列以一种汇总方式进行分类汇总。汇总方式进行分类汇总。 先对要进行分类汇总操作的列进行排序，然先对要进行分类汇总操作的列进行排序，然后打开后打开“分类汇总分类汇总”对话框进行设置。对话框进行设置。 设置分类汇总项设置分类汇总项汇总出进货表中不同经手人汇总出进货表中不同经手人所进货物的数量和金额总计所进货物的数量和金额总计 在在“分类字段分类字段”下拉列表进行选择时，该字段必须是已经排下拉列表进行选择时，该字段必须是已经排序的字段，如果选择没有排序的列标题作为分类字段，最后的分类序的字段，如果选择没有排序的

18、列标题作为分类字段，最后的分类结果是不正确的。结果是不正确的。 此外，在此外，在“分类汇总分类汇总”对话框中做设置时，注意在对话框中做设置时，注意在“选定汇总选定汇总项项”列表框中选择的汇总项要与列表框中选择的汇总项要与“汇总方式汇总方式”下拉列表中选择的汇下拉列表中选择的汇总方式相符合。例如，文本是不能进行平均值计算的。总方式相符合。例如，文本是不能进行平均值计算的。 二、多重分类汇总 对工作表中的某列数据选择两种或两种以上的分类汇对工作表中的某列数据选择两种或两种以上的分类汇总方式或汇总项进行汇总，就叫多重分类汇总，也就是说，总方式或汇总项进行汇总，就叫多重分类汇总，也就是说，多重分类汇总

19、每次用的多重分类汇总每次用的“分类字段分类字段”总是相同的，而汇总总是相同的，而汇总方式或汇总项不同，而且第方式或汇总项不同，而且第2次汇总运算是在第次汇总运算是在第1次汇总运次汇总运算的结果上进行的。算的结果上进行的。 第第1次分类次分类汇总结果汇总结果设置第设置第2次次分类汇总项分类汇总项 注意在注意在“分类汇总分类汇总”对话框中取消对话框中取消“替换当前替换当前分类汇总分类汇总”复选框，否则新创建的分类汇总将替换已复选框，否则新创建的分类汇总将替换已存在的分类汇总。此外，选择存在的分类汇总。此外，选择“每组数据分页每组数据分页”复选复选框，可使每个分类汇总自动分页。框，可使每个分类汇总自

20、动分页。第第2次分类汇总结果：汇总次分类汇总结果：汇总不同不同“经手人经手人”所进货物单所进货物单价、数量和金额的最大值价、数量和金额的最大值 三、嵌套分类汇总 嵌套分类汇总是指在一个已经建立了分类汇总的嵌套分类汇总是指在一个已经建立了分类汇总的工作表中再进行另外一种分类汇总，两次分类汇总的工作表中再进行另外一种分类汇总，两次分类汇总的字段是不相同的，其他项可以相同，也可以不同。字段是不相同的，其他项可以相同，也可以不同。 在建立嵌套分类汇总前首先对工作表中需要进行在建立嵌套分类汇总前首先对工作表中需要进行分类汇总的字段进行多关键字排序，排序的主要关键分类汇总的字段进行多关键字排序，排序的主要

21、关键字应该是第字应该是第1级汇总关键字，排序的次要关键字应该是级汇总关键字，排序的次要关键字应该是第第2级汇总关键字，其他的依次类推。级汇总关键字，其他的依次类推。 有几套分类汇总就需要进行几次分类汇总操作，有几套分类汇总就需要进行几次分类汇总操作，第第2次汇总是在第次汇总是在第1次汇总的结果上进行操作的，第次汇总的结果上进行操作的，第3次次汇总操作是在第汇总操作是在第2次汇总的结果上进行的，依次类推。次汇总的结果上进行的，依次类推。 在进货表中分别按在进货表中分别按“经手人经手人”和和“进货进货日期日期”对进货的数量和金额进行汇总。对进货的数量和金额进行汇总。 对工作表进行对工作表进行多关键

22、字排序多关键字排序 设置嵌套分设置嵌套分类汇总项类汇总项按按“经手人经手人”和和“进货日期进货日期”对对进货的数量和金进货的数量和金额进行汇总结果额进行汇总结果 四、分级显示数据 对工作表中的数据执行分类汇总后，对工作表中的数据执行分类汇总后，Excel会自动按汇总会自动按汇总时的分类分级显示数据。时的分类分级显示数据。l分级显示明细数据：分级显示明细数据：在分级显示符号中单击所需级别的数字，在分级显示符号中单击所需级别的数字，较低级别的明细数据会隐藏起来。较低级别的明细数据会隐藏起来。l隐藏与显示明细数据：隐藏与显示明细数据：单击工作表左侧的折叠按钮可以隐藏单击工作表左侧的折叠按钮可以隐藏原

23、始数据，此时该按钮变为，单击该按钮将显示组中的原始原始数据，此时该按钮变为，单击该按钮将显示组中的原始数据。数据。l清除分级显示：清除分级显示：不需要分级显示时，可以根据需要将其部分不需要分级显示时，可以根据需要将其部分或全部的分级删除。方法是选择要取消分级显示的行，然后或全部的分级删除。方法是选择要取消分级显示的行，然后单击单击“数据数据”选项卡上选项卡上“分级显示分级显示”组中的组中的“取消组取消组合合”“清除分级显示清除分级显示”项，可取消部分分级显示；要取消全项，可取消部分分级显示；要取消全部分级显示，可单击分类汇总工作表中的任意单元格，然后部分级显示，可单击分类汇总工作表中的任意单元

24、格，然后单击单击“数据数据”选项卡上选项卡上“分级显示分级显示”组中的组中的“取消组取消组合合”“清除分级显示清除分级显示”项即可。项即可。五、取消分类汇总 要取消分类汇总，可打开要取消分类汇总，可打开“分类汇总分类汇总”对话框，对话框，单击单击“全部删除全部删除”按钮。删除分类汇总的同时，按钮。删除分类汇总的同时，Excel会删除与分类汇总一起插入到列表中的分级显示。会删除与分类汇总一起插入到列表中的分级显示。 任务实施汇总初二（1）班成绩表数据 下面对下面对“初二（初二（1）班成绩表）班成绩表”进行进行“姓名姓名”和和“类类别别”字段的嵌套分类汇总。字段的嵌套分类汇总。 任务四 合并计算

25、l一、按位置合并计算 l二、按分类合并计算 合并计算是指用来汇总一个或多个源区域中数据的方法。合合并计算是指用来汇总一个或多个源区域中数据的方法。合并计算不仅可以进行求和汇总，还可以进行求平均值、计数统计并计算不仅可以进行求和汇总，还可以进行求平均值、计数统计和求标准差等运算，利用它可以将各单独工作表中的数据合并计和求标准差等运算，利用它可以将各单独工作表中的数据合并计算到一个主工作表中。其中，单独工作表可以与主工作表在同一算到一个主工作表中。其中，单独工作表可以与主工作表在同一个工作簿中，也可位于其他工作簿中。个工作簿中，也可位于其他工作簿中。 要想合并计算数据，首先必须为合并数据定义一个目

26、标区，要想合并计算数据，首先必须为合并数据定义一个目标区，用来显示合并后的信息，此目标区域可位于与源数据相同的工作用来显示合并后的信息，此目标区域可位于与源数据相同的工作表中，也可在另一个工作表中；其次，需要选择要合并计算的数表中，也可在另一个工作表中；其次，需要选择要合并计算的数据源，此数据源可以来自单个工作表、多个工作表或多个工作簿。据源，此数据源可以来自单个工作表、多个工作表或多个工作簿。 Excel提供了两种合并计算数据的方法：一是按位置合并计提供了两种合并计算数据的方法：一是按位置合并计算；二是按分类合并计算。算；二是按分类合并计算。 一、按位置合并计算 按位置合并计算要求源区域中的

27、数据使用相同的行按位置合并计算要求源区域中的数据使用相同的行标签和列标签，并按相同的顺序排列在工作表中，且没标签和列标签，并按相同的顺序排列在工作表中，且没有空行或空列。有空行或空列。 下面将同一工作簿的多张工作表中的成绩汇总到一下面将同一工作簿的多张工作表中的成绩汇总到一张工作表中。张工作表中。 放置合并放置合并计算结果计算结果的工作表的工作表 在在“网页设计网页设计”工作表中选工作表中选择要进行合并计算的单元格择要进行合并计算的单元格区域，然后释放鼠标左键返区域，然后释放鼠标左键返回回“合并计算合并计算”对话框，然对话框，然后单击后单击“添加添加”按钮按钮 添加添加“市场信息市场信息学学”工作表数据工作表数据区域区域 用同样的方法用同样的方法添加其他工作添加其他工作表数据区域表数据区域 各科成绩都各科成绩都合并计算到合并计算到“成绩汇总成绩汇总”工作表中了工作表中了 二、按分类合并计算 当源区域中的数据没有相同的组织结构，但有相当源区域中的数据没有相同的组织结构，但有相同的行标签或列标签时，可采用按分类合并计算方式同的行标签或列标签时，可采用按分类合并计算方式进行汇总。进行汇总。 放置合并放置合并计算结果计算结果的工作表的工作表一月和二月各书店销售的一月和二月各书店销售的图书种类不尽相同，但它图书种类不尽相同，但它们有相同的列标签们有相同的列标签 按分类合并计算数据时，