中文Excel 2007教程 第6章.ppt

1、第6章 数据管理,6.1 数据清单 6.2 数据排序 6.3 筛选数据 6.4 分类汇总数据 6.5 数据的合并计算 6.6 制作学生成绩统计表 6.7 制作分班表 过关练习,6.1 数据清单所谓数据清单，就是包含有关数据的一系列工作表数据行。它具备数据库的多种管理功能，是Excel中常用的工具。数据清单中的行相当于数据库中的记录，行标题相当于记录名。数据清单中的列相当于数据库中的字段，列标题相当于数据库中的字段名称。,6.1.1 建立数据清单规则为了能够发挥数据清单分析和管理数据的强大功能，在建立数据清单时应该注意以下5点：（1）数据清单至少要有一行文字作为区分数据类型的标志，标志之下是连续

2、的表格数据区。（2）清单中同一列各个单元格内容的性质都相同，是文本均为文本格式，是数值则均为数值格式。,（3）Excel通过空白行与列将数据清单与工作表中其他部分区分开来。当选定此范围内任意单元格时，Excel自动为数据清单定义范围。（4）尽管Excel允许在一张工作表中建立多个数据清单，但为了清晰明了，最好在一张工作表中仅建立一个数据清单。（5）在单元格的开始处不要插入多余的空格。,6.1.2 建立数据清单数据清单至少要有一行文字作为标题行，在标题行的下面是连续的表格数据区，这是数据清单与普通表格的不同之处。使用数据清单，可以方便地实现数据添加、删除、排序、筛选、分类汇总以及一些分析操作，如

3、图6.1.1所示为一个典型的数据清单。其中，数据清单的第一行为字段名称，其余每一列均为字段，每一行均为记录。,图6.1.1 数据清单,在工作表中创建数据清单，其具体操作步骤如下：（1）选定当前工作簿中的某个工作表用于创建数据清单。（2）在要创建数据清单的单元格区域的第一行输入各列的标题名，例如“姓名”、“性别”、“民族”、“出生年月”等。（3）在各列标题下方的单元格区域中输入数据内容，例如学生姓名、性别、出生年月等。（4）设置标题名称和字段名称的表格边框、字体格式等，然后保存数据清单。,6.2 数据排序数据排序是按照一定的规则对数据进行整理和重新排列，从而为数据的进一步处理做好准备。Excel

4、 2007为用户提供多种数据清单的排序方法，允许按一列、多列和行进行排序，也允许用用户自己定义的规则进行排序。 6.2.1 常规排序按常规排序可以将数据清单中的列标记作为关键字进行排序，其具体操作步骤如下：（1）选中要进行排序的列中的任意一个单元格。,（2）在“开始”选项卡中的“编辑”选项区中单击“排序和筛选”按钮，弹出其下拉菜单，如图6.2.1所示。（3）当用户选择“升序”或“降序”命令，将会弹出如图6.2.2所示的提示框，提示用户是否调整选中字母列对应的数据。,图6.2.1 排序和筛选下拉菜单 图6.2.2 提示框,（4）单击“排序”按钮，则可按照所选排序方式进行排序。如图6.2.3所示为

5、按字母ZA的顺序降序排序后的效果。,图6.2.3 按字母降序排序,6.2.2 自定义排序在Excel中，用户还可以按自己定义的顺序进行排序。Excel 2007提供内置的星期和年月自定义序列，用户还可以创建自己的自定义序列。1自定义序列创建自定义序列的具体操作步骤如下：（1）在单元格区域中，按照需要的顺序从上到下输入要排序的值。,（2）选择刚刚键入的区域，单击“Office”按钮，在弹出的列表框中单击“Excel选项”按钮，在弹出的“Excel选项”对话框中单击“编辑自定义列表”按钮，弹出“自定义序列”对话框，如图6.2.4所示。,图6.2.4 “自定义序列”对话框,（3）在“自定义序列”列表

6、框中选择所需的一种序列，或者在“输入序列”列表框中输入用户自定义的序列，单击“导入”按钮，然后单击“确定”按钮，即可完成自定义序列的创建。提示：用户只能基于值（文本、数字、日期或时间）创建自定义序列，而不能基于格式（单元格颜色、字体颜色和图标）创建自定义序列。自定义序列的最大长度为255个字符，并且第一个字符不得以数字开头。,2排序排序的具体操作步骤如下：（1）选择单元格区域中的一列数据，或者确保活动单元格在表列中。（2）在“开始”选项卡上的“编辑”组中单击“排序和筛选”按钮，在弹出的菜单中选择“自定义排序”命令，弹出“排序”对话框，如图6.2.5所示。（3）在该对话框中设置排序的主要关键字、

7、排序依据以及排序次序等参数，单击“确定”按钮，完成排序操作。如图6.2.6所示为按出生年月先后进行排序的效果。,技巧：在使用Excel进行排序时，无论是按升序还是按降序排列，空白单元格总是被排在最后，其他单元格中的数据在两种排序方式中显示的顺序相反。,图6.2.5 “排序”对话框 图6.2.6 按出生年月先后排序,6.3 筛选数据筛选数据可显示满足指定条件的行，并隐藏不希望显示的行。筛选数据之后，对于筛选过的数据子集，不用重新排列或移动就可以复制、查找、编辑、设置格式、制作图表或打印。Excel 2007提供了两种筛选数据的方法，即自动筛选和高级筛选。使用自动筛选可以创建按列表值、按格式和按条

8、件3种筛选类型。,6.3.1 自动筛选自动筛选是按照选定的内容进行筛选，适用于简单条件。它为用户提供在大量数据记录的数据清单中快速查找符合条件记录的功能。自动筛选一次只能对工作表中的一个区域应用筛选。如果用户要在其他数据清单中使用筛选，则需要删除本次筛选，然后才可以在其他数据清单中重新进行筛选。,自动筛选的具体操作步骤如下：（1）选定要筛选的数据清单中的任意一个单元格，打开“数据”选项卡，在“排序和筛选”组中单击“筛选”按钮，即可看到每列旁边有一个下三角按钮，如图6.3.1所示。,图6.3.1 显示下拉列表框,（2）选中“H”列，单击下三角按钮，弹出如图6.3.2所示的下拉菜单。（3）在该下拉

9、菜单中选择“自定义筛选”命令，弹出“自定义自动筛选方式”对话框，在该对话框中选择筛选条件，如图6.3.3所示。,图6.3.2 下拉菜单 图6.3.3 “自定义自动筛选方式”对话框,（4）单击“确定”按钮，筛选结果如图6.3.4所示。技巧：自动筛选也可以对多个字段进行叠加筛选，只要先在一个字段中设置筛选条件，然后在筛选结果中再对另一个字段设置筛选条件即可。,图6.3.4 筛选结果,6.3.2 高级筛选如果要通过复杂的条件来筛选单元格区域，就要使用高级筛选命令。高级筛选的具体操作步骤如下：（1）在可用做条件区域的区域上方插入至少3个空白行。条件区域必须具有列标签，确保在条件值与区域之间至少留有一个

10、空白行，如图6.3.5所示。,图6.3.5 插入空白行,（2）在列标签下面的行中键入所要匹配的条件，如图6.3.6所示。,图6.3.6 输入筛选条件,（3）打开“数据”选项卡，在“排序和筛选”组中单击“高级”按钮，弹出“高级筛选”对话框，如图6.3.7所示。（4）在“方式”组中选择筛选结果显示的位置。如果选中“在原有区域显示筛选结果”单选按钮，结果将在原数据清单位置显示；如果选中“将筛选结果复制到其他位置”单选按钮，并在“复制到”文本框中选定要复制到的区域，筛选后的结果将显示在其他区域，其结果与原工作表并存。,（5）在“列表区域”文本框中输入要筛选的区域，也可以用鼠标直接在工作表中选定。在“条

11、件区域”文本框中输入筛选条件的区域。（6）如果要筛选重复的记录，则选中“选择不重复的记录”复选框。（7）单击“确定”按钮，筛选后的结果将显示在工作表中，如图6.3.8所示。,图6.3.7 “高级筛选”对话框 图6.3.8 筛选结果,技巧：如果要取消对某列的筛选，单击该列中的“筛选”按钮，在弹出的菜单中选择“从评委7中清除筛选”命令，或者单击“排序和筛选”组中的“清除”按钮即可。,6.4 分类汇总数据分类汇总是分析数据库中数据的一个有力工具，它可以自动计算数据清单中的分类汇总和总计算。如果要插入分类汇总，就必须先对要分类汇总的数据字段进行排序。 6.4.1 简单分类汇总在插入分类汇总前要确保分类

12、汇总的数据为数据清单的格式：第一行的每一列都有标志，并且同一列中应包含相似的数据，在数据清单中不应有空行或空列。插入分类汇总的具体操作步骤如下：,（1）选定要分类汇总的工作表中的任意单元格，如图6.4.1所示。（2）排序要分类汇总的列表。在“数据”选项卡中的“排序和筛选”选项区中单击“排序”按钮，弹出“排序”对话框，在该对话框中设置如图6.4.2所示的参数。,图6.4.1 选定单元格 图6.4.2 设置排序参数,（3）单击“确定”按钮，排序后的列表如图6.4.3所示。,图6.4.3 排序后的列表,（4）在“数据”选项卡中的“排序和筛选”选项区中单击“分类汇总”按钮，弹出“分类汇总”对话框，在该

13、对话框中设置参数如图6.4.4所示。（5）单击“确定”按钮，分类汇总后的表格如图6.4.5所示。,图6.4.4 设置后的“分类汇总”对话框 图6.4.5 分类汇总后的表格,为数据清单添加自动分类汇总时，Excel自动将数据清单分级显示（在工作表左侧出现分级显示符号），以便用户根据需要查看其结构。例如，只显示分类汇总和总计的汇总，可单击行数值旁的分级显示符号中的符号，如图6.4.6所示。或者单击和符号来显示或隐藏单个分类汇总的明细数据行。,图6.4.6 显示分类汇总和总计汇总,技巧：若要在工作表中进行分类汇总操作，必须先将数据排序，以便将要进行分类汇总的行组合在一起，然后，为包含数字的列计算分类

14、汇总。 6.4.2 嵌套分类汇总所谓嵌套分类汇总，是指将更小分组的分类汇总插入到现有的分类汇总组中，其操作步骤如下：（1）打开一个数据清单，单击数据清单中的任意单元格。,（2）在“数据”选项卡中的“排序和筛选”选项区中单击“分类汇总”按钮，弹出“分类汇总”对话框。在“分类字段”下拉列表中选择要分类汇总的列，这里选择“类别”选项；在“汇总方式”下拉列表中选择所需的用于计算分类汇总的汇总函数，这里选择“求和”选项；在“选定汇总项”下拉列表中选中进行分类汇总的复选框，这里选中“定价”复选框。（3）在插入嵌套分类汇总时，为防止覆盖已存在的分类汇总，可取消选中“替换当前分类汇总”复选框，如图6.4.7所

15、示。,（4）设置完成后，单击“确定”按钮，效果如图6.4.8所示。,图6.4.7 设置后的“分类汇总”对话框 图6.4.8 嵌套分类汇总,6.4.3 清除分类汇总进行分类汇总后，要恢复到汇总之前的状态时，其具体操作步骤如下：（1）选中要取消分类汇总数据清单中的任意单元格。（2）在“数据”选项卡中的“排序和筛选”选项区中单击“分类汇总”按钮，弹出“分类汇总”对话框。（3）在该对话框中单击“全部删除”按钮即可。,6.5 数据的合并计算如果数据分别存储在多张工作表中，且每张工作表中的数据都很多，那么将所有数据复制到同一张工作表中进行计算可能会很麻烦，数据量也比较大。应用Excel 2007提供的合并

16、计算功能可以很方便地完成该操作。合并计算的具体操作步骤如下：（1）打开存储数据的所有工作表，然后在要合并计算的工作表中单击选择一个单元格。,（2）在“数据”选项卡中的“数据工具”选项区中单击“合并计算”按钮，弹出如图6.5.1所示的“合并计算”对话框。,图6.5.1 “合并计算”对话框,（3）在“函数”下拉列表框中可以设置合并计算的类型，包括“求和”、“计数”、“方差”等；然后将输入焦点置于“引用位置”文本框中，在第一张工作表中选择要合并计算数据的所有单元格，返回“合并计算”对话框后单击“添加”按钮，完成第一组数据的添加，并在“所有引用位置”列表框中显示；再按照同样的方法把其他工作表中的数据单

17、元格添加到“所有引用位置”列表框中。,（4）如果在所选择的数据单元格中包含标题，则在“标签位置”栏中选择相应的选项。如果选择了标签，则对所有的数据按照类别进行合并计算；如果没有选择标签，则只能按照位置进行合并计算。（5）如果想使合并计算的结果随时可以更新，则选中“创建指向源数据的链接”复选框。（6）设置完成后单击“确定”按钮，完成合并计算。,例如，要将如图6.5.2所示的两张工作表中的数据合并到一个空白的工作表，其操作步骤如下：,图6.5.2 合并计算工作表,（1）新建一个空白的工作表，单击选中其A1单元格，然后在“数据”选项卡中的“数据工具”选项区中单击“合并计算”按钮，弹出如图6.5.3所

18、示的“合并计算”对话框。（2）在“函数”下拉列表框中选择“求和”，在“引用位置”文本框中逐次输入两张工作表中要合并的数据的单元格区域地址，并单击“添加”按钮将其添加到“所有引用位置”列表框中；在“标签位置”栏中选中“创建指向源数据的链接”复选框。,（3）单击“确定”按钮，完成合并计算，结果如图6.5.4所示。从图6.5.4中可以看到，两张工作表按照字段的类别进行了合并计算，并用分级显示方式给出了原来单元格数据的链接，以备随时更新。,图6.5.3 “合并计算”对话框设置 图6.5.4 合并计算结果,6.6 制作学生成绩统计表用计算机管理学生成绩的主要目的是为了统计、排序、查询和打印的方便。本例制

19、作学生成绩统计表，有以下几点要求：计算学生成绩总分并按总分的成绩进行排序；计算单科成绩的平均分，平均成绩保留2位小数；设置条件筛选。最终效果如图6.6.1所示。,图6.6.1 效果图,操作步骤如下：（1）启动Excel 2007，新建工作簿。（2）在工作表第一行输入表名“三年级二班考试成绩统计表”。在第二行输入项目名称，并输入各学生的资料，如图6.6.2所示。,图6.6.2 输入相关数据后的工作表,（3）选中G3单元格，在“公式”选项卡中的“函数库”选项区中单击“自动求和”按钮，此时的效果如图6.6.3所示。按“Enter”键显示计算结果，如图6.6.4所示。（4）选中G3单元格，移动光标至单

20、元格边框右下角，当光标变成形状时，单击并拖动鼠标到G10单元格中，释放鼠标左键，即可计算出其余同学的总分，如图6.6.5所示。,图6.6.4 计算结果,图6.6.3 输入公式,图6.6.5 计算出其余同学的总分,（5）将鼠标指针放在B11单元格内，在地址栏输入“=”号，并在地址栏上编辑如图6.6.6所示的公式。按“Enter”键显示计算结果，如图6.6.7所示。,图6.6.7 计算语文平均分,图6.6.6 编辑公式,（6）选中B11单元格，参照步骤（4）的方法，计算出其他科目的平均分，如图6.6.8所示。,图6.6.8 计算其他科目的平均分,（7）选中B11:F11单元格，并单击鼠标右键，在弹

21、出的快捷菜单中选择“设置单元格格式”命令，弹出“设置单元格格式”对话框，打开“数字”选项卡。在“分类”列表中选择“数值”选项，并把“小数位数”设为2位，如图6.6.9所示。,图6.6.9 “数字”选项卡,（8）单击“确定”按钮，平均分的小数位数被设置成了2位，如图6.6.10所示。,图6.6.10 取两位小数位,（9）选中A2:H11单元格区域，在“开始”选项卡的“编辑”组中单击“排序和筛选”按钮，在弹出的菜单中选择“自定义排序”命令，弹出“排序”对话框，如图6.6.11所示。,图6.6.11 “排序”对话框,（10）在该对话框中的“主要关键字”下拉列表中选择“总分”，在“排序依据”下拉列表中

22、选择“数值”，在“次序”列表中选择“降序”，单击“确定”按钮，排序后的效果如图6.6.12所示。,图6.6.12 按学生总分排序,（11）在名次列的H3和H4单元格内输入“1”和“2”，表示第一名和第二名。然后选中这两个单元格再拖动填充柄至H10单元格，它将自动填充单元格H5H10区域为38的自然数。（12）在工作表中对数据进行查找，选中“总分”列单元格。（13）在“开始”选项卡中的“编辑”选项区中单击“排序和筛选”按钮，在弹出的下拉菜单中选择“筛选”命令，此时，被选定数据区域标题栏下方将显示下拉箭头，如图6.6.13所示。（14）单击，弹出其下拉菜单，如图6.6.14所示。,图6.6.13

23、显示下拉箭头 图6.6.14 下拉菜单,（15）取消选中“全选”复选框，选择“数字筛选”“自定义筛选”命令，弹出“自定义自动筛选方式”对话框，如图6.6.15所示。,图6.6.15 “自定义自动筛选方式”对话框,（16）在该对话框中选择要作为筛选依据的数字，在此选择总分大于420分的成绩。（17）设置好后，单击“确定”按钮，效果如图6.6.1上图所示。（18）采用同样的方法也可对其他科目的成绩进行筛选。（19）若要进行高级筛选，可在表格的空白处创建条件区域，如图6.6.16所示。,（20）选中整个数据区域，在“数据”选项卡中的“排序和筛选”选项区中单击“高级”按钮，弹出“高级筛选”对话框。单击

24、“列表区域”右侧的“缩小对话框”按钮 ，选中A2H11单元格。用同样的方法在“条件区域”中选择B14D15单元格，如图6.6.17所示。,图6.6.16 条件格式 图6.6.17 设置筛选条件,（21）单击“确定”按钮，最终效果如图6.6.1下图所示。注意：在进行筛选之后，工作表中只显示筛选结果。如果要显示以前的所有数据，在“开始”选项卡中的“编辑”选项区中单击“排序和筛选”按钮，在弹出的下拉菜单中选择“筛选”命令即可恢复。,6.7 制作分班表本例利用本章学习的排序及自动筛选功能，为某个学校制作一个简单的分班表。要求各班男女生搭配平均，总分分布合理，效果如图6.7.1所示。,图6.7.1 效果

25、图,图6.7.1 效果图（续）,操作步骤如下：（1）在Excel工作表中输入内容并计算总分，如图6.7.2所示。,图6.7.2 输入工作表内容,（2）选中数据区域内的任意单元格，在“数据”选项卡中的“排序和筛选”选项区中单击“排序”按钮，弹出“排序”对话框，如图6.7.3所示。,图6.7.3 “排序”对话框,（3）在该对话框中的“主要关键字”下拉列表中选择“总分”，在“排序依据”下拉列表中选择“数值”，在“次序”下拉列表中选择“升序”，单击“确定”按钮，排序后的效果如图6.7.4所示。（4）重复步骤（2）和（3）的操作，按学生“性别”进行排序，从而实现男女生成绩的分别排序，结果如图6.7.5所

26、示。,图6.7.4 按学生总分排序图6.7.5 男女生成绩分别排序结果,（5）如果要为本年级分3个班，则按照1，2，3，2，3，1，2，1，3的顺序在班级字段中进行分班，如图6.7.6所示。,图6.7.6 在“班级”字段中输入内容,（6）选定要筛选的数据清单中的任意一个单元格，在“数据”选项卡中的“排序和筛选”选项区中单击“筛选”按钮，即可看到每列旁边有一个下三角按钮，如图6.7.7所示。,图6.7.7 显示下拉列表框,（7）选中“J”列，单击下三角按钮，弹出如图6.7.8所示的下拉菜单。（8）在该下拉菜单中选择“数字筛选”“自定义筛选”命令，弹出“自定义自动筛选方式”对话框，在对话框中设置筛

27、选条件，如图6.7.9所示。,图6.7.8 下拉菜单 图6.7.9 “自定义自动筛选方式”对话框,（9）单击“确定”按钮，筛选结果如图6.7.1上图所示。（10）如果要查看2班的情况，则单击“班级”项目右侧的下三角按钮，从弹出的下拉菜单中选择“2”选项，如图6.7.10所示。筛选后的效果如图6.7.1中图所示。（11）按照同样的方法查看3班的情况，如图6.7.1下图所示。,图6.7.10 执行“自动筛选”功能,过关练习 填空题1在Excel 2007中，数据清单是指_，在对数据清单进行管理时，可以把数据清单看成是一个_。2在Excel 2007中，一个工作表中一次可以使用_个数据清单。3在Ex