中文Excel2007教程第6章

第6章数据管理6.1数据清单6.2数据排序6.3筛选数据6.4分类汇总数据6.5数据的合并计算6.6制作学生成绩统计表6.7制作分班表过关练习中文Excel2007教程第6章共72页，您现在浏览的是第1页!6.1数据清单

所谓数据清单，就是包含有关数据的一系列工作表数据行。它具备数据库的多种管理功能，是Excel中常用的工具。数据清单中的行相当于数据库中的记录，行标题相当于记录名。数据清单中的列相当于数据库中的字段，列标题相当于数据库中的字段

名称。中文Excel2007教程第6章共72页，您现在浏览的是第2页!

6.1.1建立数据清单规则

为了能够发挥数据清单分析和管理数据的强大功能，在建立数据清单时应该注意以下5点：

（1）数据清单至少要有一行文字作为区分数据类型的标志，标志之下是连续的表格数据区。

（2）清单中同一列各个单元格内容的性质都相同，是文本均为文本格式，是数值则均为数值格式。中文Excel2007教程第6章共72页，您现在浏览的是第3页!

6.1.2建立数据清单

数据清单至少要有一行文字作为标题行，在标题行的下面是连续的表格数据区，这是数据清单与普通表格的不同之处。使用数据清单，可以方便地实现数据添加、删除、排序、筛选、分类汇总以及一些分析操作，如图6.1.1所示为一个典型的数据清单。其中，数据清单的行为字段名称，其余每一列均为字段，每一行均为记录。图6.1.1数据清单中文Excel2007教程第6章共72页，您现在浏览的是第4页!6.2数据排序

数据排序是按照一定的规则对数据进行整理和重新排列，从而为数据的进一步处理做好准备。Excel2007为用户提供多种数据清单的排序方法，允许按一列、多列和行进行排序，也允许用用户自己定义的规则进行排序。

6.2.1常规排序

按常规排序可以将数据清单中的列标记作为关键字进行排序，其具体操作步骤如下：

（1）选中要进行排序的列中的任意一个单元格。中文Excel2007教程第6章共72页，您现在浏览的是第5页!（4）单击“排序”按钮，则可按照所选排序方式进行排序。如图6.2.3所示为按字母Z～A的顺序降序排序后的效果。图6.2.3按字母降序排序中文Excel2007教程第6章共72页，您现在浏览的是第6页!（2）选择刚刚键入的区域，单击“Office”按钮，在弹出的列表框中单击“Excel选项”按钮，在弹出的“Excel选项”对话框中单击“编辑自定义列表”按钮，弹出“自定义序列”对话框，如图6.2.4所示。图6.2.4“自定义序列”对话框中文Excel2007教程第6章共72页，您现在浏览的是第7页!2．排序

排序的具体操作步骤如下：

（1）选择单元格区域中的一列数据，或者确保活动单元格在表列中。

（2）在“开始”选项卡上的“编辑”组中单击“排序和筛选”按钮，在弹出的菜单中选择“自定义排序”命令，弹出“排序”对话框，如图6.2.5所示。

（3）在该对话框中设置排序的主要关键字、排序依据以及排序次序等参数，单击“确定”按钮，完成排序操作。如图6.2.6所示为按出生年月先后进行排序的效果。中文Excel2007教程第6章共72页，您现在浏览的是第8页!

6.3筛选数据

筛选数据可显示满足指定条件的行，并隐藏不希望显示的行。筛选数据之后，对于筛选过的数据子集，不用重新排列或移动就可以复制、查找、编辑、设置格式、制作图表或打印。Excel2007提供了两种筛选数据的方法，即自动筛选和高级筛选。使用自动筛选可以创建按列表值、按格式和按条件3种筛选类型。中文Excel2007教程第6章共72页，您现在浏览的是第9页!自动筛选的具体操作步骤如下：

（1）选定要筛选的数据清单中的任意一个单元格，打开“数据”选项卡，在“排序和筛选”组中单击“筛选”按钮，即可看到每列旁边有一个下三角按钮，如图6.3.1所示。图6.3.1显示下拉列表框中文Excel2007教程第6章共72页，您现在浏览的是第10页!（4）单击“确定”按钮，筛选结果如图6.3.4所示。

技巧：自动筛选也可以对多个字段进行叠加筛选，只要先在一个字段中设置筛选条件，然后在筛选结果中再对另一个字段设置筛选条件即可。图6.3.4筛选结果中文Excel2007教程第6章共72页，您现在浏览的是第11页!（2）在列标签下面的行中键入所要匹配的条件，如图6.3.6所示。图6.3.6输入筛选条件中文Excel2007教程第6章共72页，您现在浏览的是第12页!（5）在“列表区域”文本框中输入要筛选的区域，也可以用鼠标直接在工作表中选定。在“条件区域”文本框中输入筛选条件的区域。

（6）如果要筛选重复的记录，则选中“选择不重复的记录”复选框。

（7）单击“确定”按钮，筛选后的结果将显示在工作表中，如图6.3.8所示。图6.3.7“高级筛选”对话框图6.3.8筛选结果中文Excel2007教程第6章共72页，您现在浏览的是第13页!

6.4分类汇总数据

分类汇总是分析数据库中数据的一个有力工具，它可以自动计算数据清单中的分类汇总和总计算。如果要插入分类汇总，就必须先对要分类汇总的数据字段进行排序。

6.4.1简单分类汇总

在插入分类汇总前要确保分类汇总的数据为数据清单的格式：行的每一列都有标志，并且同一列中应包含相似的数据，在数据清单中不应有空行或空列。插入分类汇总的具体操作步骤如下：中文Excel2007教程第6章共72页，您现在浏览的是第14页!（3）单击“确定”按钮，排序后的列表如图6.4.3所示。图6.4.3排序后的列表中文Excel2007教程第6章共72页，您现在浏览的是第15页!为数据清单添加自动分类汇总时，Excel自动将数据清单分级显示（在工作表左侧出现分级显示符号），以便用户根据需要查看其结构。例如，只显示分类汇总和总计的汇总，可单击行数值旁的分级显示符号中的符号，如图6.4.6所示。或者单击和符号来显示或隐藏单个分类汇总的明细数据行。图6.4.6显示分类汇总和总计汇总中文Excel2007教程第6章共72页，您现在浏览的是第16页!（2）在“数据”选项卡中的“排序和筛选”选项区中单击“分类汇总”按钮，弹出“分类汇总”对话框。在“分类字段”下拉列表中选择要分类汇总的列，这里选择“类别”选项；在“汇总方式”下拉列表中选择所需的用于计算分类汇总的汇总函数，这里选择“求和”选项；在“选定汇总项”下拉列表中选中进行分类汇总的复选框，这里选中“定价”复选框。

（3）在插入嵌套分类汇总时，为防止覆盖已存在的分类汇总，可取消选中“替换当前分类汇总”复选框，如图6.4.7所示。中文Excel2007教程第6章共72页，您现在浏览的是第17页!

6.4.3清除分类汇总

进行分类汇总后，要恢复到汇总之前的状态时，其具体操作步骤如下：

（1）选中要取消分类汇总数据清单中的任意单元格。

（2）在“数据”选项卡中的“排序和筛选”选项区中单击“分类汇总”按钮，弹出“分类汇总”对话框。

（3）在该对话框中单击“全部删除”按钮即可。中文Excel2007教程第6章共72页，您现在浏览的是第18页!（2）在“数据”选项卡中的“数据工具”选项区中单击“合并计算”按钮，弹出如图6.5.1所示的“合并计算”对话框。图6.5.1“合并计算”对话框中文Excel2007教程第6章共72页，您现在浏览的是第19页!（4）如果在所选择的数据单元格中包含标题，则在“标签位置”栏中选择相应的选项。如果选择了标签，则对所有的数据按照类别进行合并计算；如果没有选择标签，则只能按照位置进行合并计算。

（5）如果想使合并计算的结果随时可以更新，则选中“创建指向源数据的链接”复选框。

（6）设置完成后单击“确定”按钮，完成合并计算。中文Excel2007教程第6章共72页，您现在浏览的是第20页!（1）新建一个空白的工作表，单击选中其A1单元格，然后在“数据”选项卡中的“数据工具”选项区中单击“合并计算”按钮，弹出如图6.5.3所示的“合并计算”对话框。

（2）在“函数”下拉列表框中选择“求和”，在“引用位置”文本框中逐次输入两张工作表中要合并的数据的单元格区域地址，并单击“添加”按钮将其添加到“所有引用位置”列表框中；在“标签位置”栏中选中“创建指向源数据的链接”复选框。中文Excel2007教程第6章共72页，您现在浏览的是第21页!6.6制作学生成绩统计表

用计算机管理学生成绩的主要目的是为了统计、排序、查询和打印的方便。本例制作学生成绩统计表，有以下几点要求：计算学生成绩总分并按总分的成绩进行排序；计算单科成绩的平均分，平均成绩保留2位小数；设置条件筛选。最终效果如图6.6.1所示。图6.6.1效果图中文Excel2007教程第6章共72页，您现在浏览的是第22页!（3）选中G3单元格，在“公式”选项卡中的“函数库”选项区中单击“自动求和”按钮，此时的效果如图6.6.3所示。按“Enter”键显示计算结果，如图6.6.4所示。

（4）选中G3单元格，移动光标至单元格边框右下角，当光标变成形状时，单击并拖动鼠标到G10单元格中，释放鼠标左键，即可计算出其余同学的总分，如图6.6.5所示。中文Excel2007教程第6章共72页，您现在浏览的是第23页!（5）将鼠标指针放在B11单元格内，在地址栏输入“=”号，并在地址栏上编辑如图6.6.6所示的公式。按“Enter”键显示计算结果，如图6.6.7所示。图6.6.7计算语文平均分图6.6.6编辑公式中文Excel2007教程第6章共72页，您现在浏览的是第24页!（7）选中B11:F11单元格，并单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”命令，弹出“设置单元格格式”对话框，打开“数字”选项卡。在“分类”列表中选择“数值”选项，并把“小数位数”设为2位，如图6.6.9所示。图6.6.9“数字”选项卡中文Excel2007教程第6章共72页，您现在浏览的是第25页!（9）选中A2:H11单元格区域，在“开始”选项卡的“编辑”组中单击“排序和筛选”按钮，在弹出的菜单中选择“自定义排序”命令，弹出“排序”对话框，如图6.6.11所示。图6.6.11“排序”对话框中文Excel2007教程第6章共72页，您现在浏览的是第26页!（11）在名次列的H3和H4单元格内输入“1”和“2”，表示名和第二名。然后选中这两个单元格再拖动填充柄至H10单元格，它将自动填充单元格H5～H10区域为3～8的自然数。

（12）在工作表中对数据进行查找，选中“总分”列单元格。

（13）在“开始”选项卡中的“编辑”选项区中单击“排序和筛选”按钮，在弹出的下拉菜单中选择“筛选”命令，此时，被选定数据区域标题栏下方将显示下拉箭头，如图6.6.13所示。

（14）单击，弹出其下拉菜单，如图6.6.14所示。中文Excel2007教程第6章共72页，您现在浏览的是第27页!（15）取消选中“全选”复选框，选择“数字筛选”→“自定义筛选”命令，弹出“自定义自动筛选方式”对话框，如图6.6.15所示。图6.6.15“自定义自动筛选方式”对话框中文Excel2007教程第6章共72页，您现在浏览的是第28页!（20）选中整个数据区域，在“数据”选项卡中的“排序和筛选”选项区中单击“高级”按钮，弹出“高级筛选”对话框。单击“列表区域”右侧的“缩小对话框”按钮，选中A2～H11单元格。用同样的方法在“条件区域”中选择B14～D15单元格，如图6.6.17

所示。图6.6.16条件格式图6.6.17设置筛选条件中文Excel2007教程第6章共72页，您现在浏览的是第29页!6.7制作分班表

本例利用本章学习的排序及自动筛选功能，为某个学校制作一个简单的分班表。要求各班男女生搭配平均，总分分布合理，效果如图6.7.1所示。图6.7.1效果图图6.7.1效果图（续）中文Excel2007教程第6章共72页，您现在浏览的是第30页!（2）选中数据区域内的任意单元格，在“数据”选项卡中的“排序和筛选”选项区中单击“排序”按钮，弹出“排序”对话框，如图6.7.3所示。图6.7.3“排序”对话框中文Excel2007教程第6章共72页，您现在浏览的是第31页!（5）如果要为本年级分3个班，则按照1，2，3，2，3，1，2，1，3的顺序在班级字段中进行分班，如图6.7.6所示。图6.7.6在“班级”字段中输入内容中文Excel2007教程第6章共72页，您现在浏览的是第32页!（7）选中“J”列，单击下三角按钮，弹出如图6.7.8所示的下拉菜单。

（8）在该下拉菜单中选择“数字筛选”→“自定义筛选”命令，弹出“自定义自动筛选方式”对话框，在对话框中设置筛选条件，如图6.7.9所示。图6.7.8下拉菜单图6.7.9“自定义自动筛选方式”对话框中文Excel2007教程第6章共72页，您现在浏览的是第33页!过关练习

填空题

1．在Excel2007中，数据清单是指__________，在对数据清单进行管理时，可以把数据清单看成是一个__________。

2．在Excel2007中，一个工作表中一次可以使用__________个数据清单。

3．在Excel2007中，可以按照__________或__________排序方式进行排序，用户也可以根据需要自定义普通排序方式。中文Excel2007教程第6章共72页，您现在浏览的是第34页!选择题

1．在Excel2007中，用户不可以按照（）进行排序。

（A）文本 （B）数字

（C）日期和时间 （D）单元格

2．下面不是Excel2007中的筛选功能的是（）。

（A）筛选文本 （B）筛选数字

（C）筛选日期和时间 （D）全错中文Excel2007教程第6章共72页，您现在浏览的是第35页!简答题

1．如何排序工作表中的数据？

2．自动筛选和高级筛选有哪些不同之处？

3．怎样进行数据的合并计算？

4．简述自定义序列的操作过程和方法？中文Excel2007教程第6章共72页，您现在浏览的是第36页!2．题图6.2所示是某单位的工资表（只给出了部分工作表数据），现要进行工资调整了，调整的方法是工龄20年以上的增加500元；工龄10年到20年的增加原工资的15%；工龄2年到10年的增加180元；其他人员增加100元。另外，按每人7个月计算，补发增加工资部分，将增加部分按科室进行分类汇总。题图6.2中文Excel2007教程第6章共72页，您现在浏览的是第37页!（3）Excel通过空白行与列将数据清单与工作表中其他部分区分开来。当选定此范围内任意单元格时，Excel自动为数据清单定义范围。

（4）尽管Excel允许在一张工作表中建立多个数据清单，但为了清晰明了，最好在一张工作表中仅建立一个数据清单。

（5）在单元格的开始处不要插入多余的空格。中文Excel2007教程第6章共72页，您现在浏览的是第38页!在工作表中创建数据清单，其具体操作步骤如下：

（1）选定当前工作簿中的某个工作表用于创建数据清单。

（2）在要创建数据清单的单元格区域的行输入各列的标题名，例如“姓名”、“性别”、“民族”、“出生年月”等。

（3）在各列标题下方的单元格区域中输入数据内容，例如学生姓名、性别、出生年月等。

（4）设置标题名称和字段名称的表格边框、字体格式等，然后保存数据清单。中文Excel2007教程第6章共72页，您现在浏览的是第39页!（2）在“开始”选项卡中的“编辑”选项区中单击“排序和筛选”按钮，弹出其下拉菜单，如图6.2.1所示。

（3）当用户选择“升序”或“降序”命令，将会弹出如图6.2.2所示的提示框，提示用户是否调整选中字母列对应的数据。图6.2.1排序和筛选下拉菜单图6.2.2提示框中文Excel2007教程第6章共72页，您现在浏览的是第40页!6.2.2自定义排序

在Excel中，用户还可以按自己定义的顺序进行排序。Excel2007提供内置的星期和年月自定义序列，用户还可以创建自己的自定义序列。

1．自定义序列

创建自定义序列的具体操作步骤如下：

（1）在单元格区域中，按照需要的顺序从上到下输入要排序的值。中文Excel2007教程第6章共72页，您现在浏览的是第41页!（3）在“自定义序列”列表框中选择所需的一种序列，或者在“输入序列”列表框中输入用户自定义的序列，单击“导入”按钮，然后单击“确定”按钮，即可完成自定义序列的创建。

提示：用户只能基于值（文本、数字、日期或时间）创建自定义序列，而不能基于格式（单元格颜色、字体颜色和图标）创建自定义序列。自定义序列的最大长度为255个字符，并且个字符不得以数字开头。中文Excel2007教程第6章共72页，您现在浏览的是第42页!技巧：在使用Excel进行排序时，无论是按升序还是按降序排列，空白单元格总是被排在最后，其他单元格中的数据在两种排序方式中显示的顺序相反。图6.2.5“排序”对话框图6.2.6按出生年月先后排序中文Excel2007教程第6章共72页，您现在浏览的是第43页!

6.3.1自动筛选

自动筛选是按照选定的内容进行筛选，适用于简单条件。它为用户提供在大量数据记录的数据清单中快速查找符合条件记录的功能。自动筛选一次只能对工作表中的一个区域应用筛选。如果用户要在其他数据清单中使用筛选，则需要删除本次筛选，然后才可以在其他数据清单中重新进行筛选。中文Excel2007教程第6章共72页，您现在浏览的是第44页!（2）选中“H”列，单击下三角按钮，弹出如图6.3.2所示的下拉菜单。

（3）在该下拉菜单中选择“自定义筛选”命令，弹出“自定义自动筛选方式”对话框，在该对话框中选择筛选条件，如图6.3.3所示。图6.3.2下拉菜单图6.3.3“自定义自动筛选方式”对话框中文Excel2007教程第6章共72页，您现在浏览的是第45页!

6.3.2高级筛选

如果要通过复杂的条件来筛选单元格区域，就要使用高级筛选命令。

高级筛选的具体操作步骤如下：

（1）在可用做条件区域的区域上方插入至少3个空白行。条件区域必须具有列标签，确保在条件值与区域之间至少留有一个空白行，如图6.3.5所示。图6.3.5插入空白行中文Excel2007教程第6章共72页，您现在浏览的是第46页!（3）打开“数据”选项卡，在“排序和筛选”组中单击“高级”按钮，弹出“高级筛选”对话框，如图6.3.7所示。

（4）在“方式”组中选择筛选结果显示的位置。如果选中“在原有区域显示筛选结果”单选按钮，结果将在原数据清单位置显示；如果选中“将筛选结果复制到其他位置”单选按钮，并在“复制到”文本框中选定要复制到的区域，筛选后的结果将显示在其他区域，其结果与原工作表并存。中文Excel2007教程第6章共72页，您现在浏览的是第47页!技巧：如果要取消对某列的筛选，单击该列中的“筛选”按钮，在弹出的菜单中选择“从‘评委7’中清除筛选”命令，或者单击“排序和筛选”组中的“清除”按钮即可。中文Excel2007教程第6章共72页，您现在浏览的是第48页!（1）选定要分类汇总的工作表中的任意单元格，如图6.4.1所示。

（2）排序要分类汇总的列表。在“数据”选项卡中的“排序和筛选”选项区中单击“排序”按钮，弹出“排序”对话框，在该对话框中设置如图6.4.2所示的参数。图6.4.1选定单元格图6.4.2设置排序参数中文Excel2007教程第6章共72页，您现在浏览的是第49页!（4）在“数据”选项卡中的“排序和筛选”选项区中单击“分类汇总”按钮，弹出“分类汇总”对话框，在该对话框中设置参数如图6.4.4所示。

（5）单击“确定”按钮，分类汇总后的表格如图6.4.5所示。图6.4.4设置后的“分类汇总”对话框图6.4.5分类汇总后的表格中文Excel2007教程第6章共72页，您现在浏览的是第50页!技巧：若要在工作表中进行分类汇总操作，必须先将数据排序，以便将要进行分类汇总的行组合在一起，然后，为包含数字的列计算分类汇总。

6.4.2嵌套分类汇总

所谓嵌套分类汇总，是指将更小分组的分类汇总插入到现有的分类汇总组中，其操作步骤如下：

（1）打开一个数据清单，单击数据清单中的任意单元格。中文Excel2007教程第6章共72页，您现在浏览的是第51页!（4）设置完成后，单击“确定”按钮，效果如图6.4.8所示。图6.4.7设置后的“分类汇总”对话框图6.4.8嵌套分类汇总中文Excel2007教程第6章共72页，您现在浏览的是第52页!6.5数据的合并计算

如果数据分别存储在多张工作表中，且每张工作表中的数据都很多，那么将所有数据复制到同一张工作表中进行计算可能会很麻烦，数据量也比较大。应用Excel2007提供的合并计算功能可以很方便地完成该操作。合并计算的具体操作步骤如下：

（1）打开存储数据的所有工作表，然后在要合并计算的工作表中单击选择一个单元格。中文Excel2007教程第6章共72页，您现在浏览的是第53页!（3）在“函数”下拉列表框中可以设置合并计算的类型，包括“求和”、“计数”、“方差”等；然后将输入焦点置于“引用位置”文本框中，在张工作表中选择要合并计算数据的所有单元格，返回“合并计算”对话框后单击“添加”按钮，完成组数据的添加，并在“所有引用位置”列表框中显示；再按照同样的方法把其他工作表中的数据单元格添加到“所有引用位置”列表框中。中文Excel2007教程第6章共72页，您现在浏览的是第54页!例如，要将如图6.5.2所示的两张工作表中的数据合并到一个空白的工作表，其操作步骤如下：图6.5.2合并计算工作表中文Excel2007教程第6章共72页，您现在浏览的是第55页!（3）单击“确定”按钮，完成合并计算，结果如图6.5.4所示。

从图6.5.4中可以看到，两张工作表按照字段的类别进行了合并计算，并用分级显示方式给出了原来单元格数据的链接，以备随时更新。图6.5.3“合并计算”对话框设置图6.5.4合并计算结果中文Excel2007教程第6章共72页，您现在浏览的是第56页!操作步骤如下：

（1）启动Excel2007，新建工作簿。

（2）在工作表行输入表名“三年级二班考试成绩统计表”。在第二行输入项目名称，并输入各学生的资料，如图6.6.2所示。图6.6.2输入相关数据后的工作表中文Excel2007教程第6章共72页，您现在浏览的是第57页!图6.6.4计算结果图6.6.3输入公式图6.6.5计算出其余同学的总分中文Excel2007教程第6章共72页，您现在浏览的是第58页!（6）选中B11单元格，参照步骤（4）的方法，计算出其他科目的平均分，如图6.6.8所示。图6.6.8计算其他科目的平均分中文Excel2007教程第6章共72页，您现在浏览的是第59页!（8）单击“确定”按钮，平均分的小数位数被设置成了2位，如图6.6.10所示。图6.6.10取两位小数位中文Excel2007教程第6章共72页，您现在浏览的是第60页!（10）在该对话框中的“主要关键字”下拉列表中选择“总分”，在“排序依据”下拉列表中选择“数值”，在“次序”列表中选择“降序”，单击“确定”按钮，排序后的效果如图6.6.12所示。图6.6.12按学生总分排序中文Excel2007教程第6章共72页，您现在浏览的是第61页!图6.6.13显示下拉箭头图6.6.14下拉菜单中文Excel2007教程第6章共72页，您现在浏览的是第62页!（16）在该对话框中选择要作为筛选依据的数字，在此选择总分大于420分的成绩。

（17）设置好后，单击“确定”按钮，效果如图6.6.1上图

所示。

（18）采用同样的方法也可对其他科目的成绩进行筛选。

（19）若要进行高级筛选，可在表格的空白处创建条件区域，如图6.6.16所示。中文Excel2007教程第6章共72页，您现在浏览的是第63页!（21）单击“确定”按钮，最终效果如图6.6.1下图所示。

注意：在进行筛选之后，工作表中只显示筛选结果。如果要显示以前的所有数据，在“开始”选项卡中的“编辑”选项区中单击“排序和筛选”按钮，在弹出的下拉菜单中选择“筛选”命令即可

恢复。中文Excel2007教程第6章共72页，您现在浏览的是第64页!操作步骤如下：

（1）在Excel工作表中输入内容并计算总分，如图6.7.2所示。图6.7.2输入工作表内容中文Excel2007教程第6章共72页，您现在浏览的是第65页!（3）在该对话框中的“主要关键字”下拉列表中选择“总分”，在“排序依据”下拉列表中选择“数值”，在“次序”下拉列表中选择“升序”，单击“确