《全国计算机等级考试一级教程》课件-5-6

5.6工作表中的数据库操作

Excel提供了较强的数据库管理功能，不仅能够通过记录单来增加、删除和移动数据，还能够按照数据库的管理方式对以数据清单形式存放的工作表进行各种排序、筛选、分类汇总、统计和建立数据透视表等操作。需要特别注意的是，对工作表数据进行数据库操作，要求数据必须按“数据清单”存放。

5.6.1建立数据清单

1．数据清单

数据清单是指包含一组相关数据的一系列工作表数据行。Excel允许采用数据库管理的方式管理数据清单。数据清单由标题行（表头）和数据部分组成。数据清单中的行相当于数据库中的记录，行标题相当于记录名；数据清单中的列相当于数据库中的字段，列标题相当于字段名。2．使用记录单建立数据清单

建立数据清单时，可以采用建立工作表的方式向行、列中逐个输入数据，也可以使用“记录单”建立和编辑数据清单。记录单是数据清单的一种管理工具，通过将记录单添加到“自定义快速访问栏”可以更方便地在数据清单中输入、修改、删除和移动数据记录。

将记录单添加到“自定义快速访问栏”的具体操作步骤：

（1）选择“文件”选项卡，“选项/快速访问工具栏”，在“从下列位置选择命令”中选择“所有命令”，选中下拉列表中的“记录单”。

（2）单击“添加”、“确定”按钮。

使用“记录单”按钮输入、修改、删除和移动数据记录具体操作步骤：

（1）选定工作表的数据区域，单击“记录单”按钮

，弹出“记录单”对话框。如图5-46所示。

（2）单击“新建”按钮，依次在字段名右侧的输入框中输入数据，再次单击“新建”或单击“关闭”，记录增加完成（只能在记录尾部增加记录）。

如想修改、删除数据记录，只需定位在要修改、删除的记录，直接修改、删除即可。

5.6.2数据排序

1．使用“升序”按钮

（“降序”按钮

）`排序

使用“升序”按钮

（“降序”按钮

）`排序的具体操作步骤：

（1）选定要排序的单元格区域。

（2）单击“升序”（“降序”）按钮，弹出“排序提醒”对话框，选择“以当前选定区域排序”单选框，单击“排序”。2．使用自定义排序

使用自定义排序的具体操作步骤：

（1）选定要排序的单元格区域。

（2）选择“数据”/排序和筛选”，单击“排序”，弹出“排序”对话框如图5-47所示。在“列”选项组中“主要关键字”下拉列表中选择要排序的列。“排序依据”下拉列表中，可根据需要，对于文本值、数值、日期或时间值排序选择“数值”；对于格式排序选择“单元格颜色”、“字体颜色”和“单元格图标”。在“次序”下拉列表中选择排序方式。

（3）当排序关键字不止一个时，单击“添加条件”，对话框显示“次要关键字”，如步骤

（2），重复设置。

若要删除作为排序依据的列，需选择该条目，然后单击“删除条件”。若要复制作为排序依据的列，需选择该条目，然后单击“复制条件”。若要更改列的排序顺序，需选择一个条目，然后单击

或更改顺序

（4）单击“确定”按钮。提示：

对列进行排序时，隐藏的列不会移动；对行进行排序时，隐藏的列也不会移动。在对数据进行排序之前，最好先取消隐藏已隐藏的列和行。

当选取数据包含标题时勾选“数据包含标题”以免排序时出现错误。

单击“排序”对话框中“选项”按钮，弹出“排序选项”对话框，如图5-48所示。选中“方向”下的“按行排序”可以对行进行排序。

在Excel2010中，排序条件最多可以支持64个关键字。3．排序数据区域选择

如果选定的数据清单内容没有包含所有的列，Excel会弹出“排序提醒”对话框，可选择“扩展选定区域”或“以当前选定区域排序”，如果选中“扩展选定区域”，Excel自动选定数据清单的全部内容，如果选中“以当前选定区域排序”，Excel将只对已选定的区域排序，未选定的区域不变（有可能引起数据错误）。4．恢复排序

如果希望将已经过多次排序的数据清单恢复到排序前的状况，可以在数据清单中设置“记录号”字段，内容为顺序数字1、2、3、4…，无论何时，只要按“记录号”字段升序排列即可恢复为排序前的数据清单。

5.6.3数据筛选

数据筛选是在工作表的数据清单中快速查找具有特定条件的记录，筛选后数据清单中只包含符合筛选条件的记录，以便于浏览。

在单元格区域或Excel表中选中至少一个单元格，选择“数据”选项卡，“排序和筛选”组，单击“筛选”按钮

。启用筛选如图5-49所示。

单击标题旁下拉箭头

显示筛选器选择列表如图5-50所示。1．自动筛选1）单字段条件筛选

筛选条件只涉及一个字段内容称为单字段条件筛选。

单字段自动筛选的具体操作步骤：

（1）选定要筛选的数据。

（2）选择选择“数据/排序和筛选”组，单击“筛选”按钮。

（3）单击列标题中的下拉箭头

，在弹出的筛选器选择列表中，按所需可选择

①在“搜索”框输入要搜索的字符，单击“确定”。

②在数据列中，取消不需显示的数值选项前的复选框。

③根据列中的数据类型，可选择“数字筛选”如图5-51所示或“文本筛选”如图5-52所示。按所需，数字类型可选择“等于”、“不等于”、“大于”、“大于等于”和“自定义筛选”等条件进行筛选，文本类型可选择“等于”、“不等于”、“开头是”、“结尾是”和“自定义筛选”等条件进行筛选。

（4）单击“确定”。列标题中的

变为表示此列已应用筛选。

（5）单击列标题中的

，在弹出的“筛选器选择列表”中，单击“从**中清除筛选”可以取消此列数据的筛选。2）多字段条件筛选

多字段自动筛选的具体操作步骤：

（1）选定要筛选的数据。

（2）选择选择“数据/排序和筛选”，单击“筛选”按钮。

（3）单击列标题中的下拉箭头

，在弹出的“筛选器选择列表”中选择“文本筛选/自定义筛选”（或“数字筛选/自定义筛选”），弹出“自定义自动筛选方式”对话框，如图5-53所示。在右侧一个或多个框中，输入文本（或数字）或从下拉列表中选择文本值（或数字）

（4）单击“确定”。列标题中的

变为

。

（5）在第一次筛选出的结果中，打开下一次要筛选的列标题下拉箭头

，重复（3）、（4）步骤。

（6）单击列标题中的

，在弹出的“筛选器选择列表”中，单击“从**中清除筛选”可以取消此列数据的筛选。选择“数据”选项卡，“排序和筛选”组，单击“清除”按钮

可以清除当前数据范围的筛选和排序。2．高级筛选Excel提供高级筛选方式，主要用于多字段条件的筛选。使用高级筛选必须先建立一个条件区域，用来编辑筛选条件。条件区域的第一行是所有作为筛选条件的字段名，这些字段名必须与数据清单中的字段名完全一样。条件区域的其他行输入筛选条件，“与”关系的条件必须出现在同一行内，“或”关系的条件不能出现在同一行内。条件区域与数据清单区域不能连接，须用空行隔开。

【例题5-14】对工作表“公司人员薪金表”数据清单的内容进行高级筛选，如图5-54所示。必须同时满足两个条件：条件1，基本薪金大于等于4000并且小于等于5000；条件2，学历为硕士或博士。

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

（1）在工作表的第一行前插入四行作为高级筛选的条件区域。

（2）在条件区域（A1:D3）区域输入筛选条件，选择工作表的数据清单区域（A6:G26）。

（3）选择“数据/排序和筛选”，单击“高级”按钮

，弹出“高级筛选”对话框，选择“在原有区域显示筛选结果”（也可以选择“将筛选结果复制到其他位置”），利用下拉按钮

确定“列表区域”（数据清单区域）和“条件区域”（筛选条件区域），单击“确定”按钮即可完成高级筛选，如图5-55所示。

5.6.4数据分类汇总

分类汇总是对数据内容进行分析的一种方法。Excel分类汇总是对工作表中数据清单的内容进行分类，然后统计同类记录的相关信息，包括求和、计数、平均值、最大值、最小值等，由用户进行选择。

分类汇总只能对数据清单进行，数据清单的第一行必须有列标题。在进行分类汇总前，必须根据分类汇总的数据类对数据清单进行排序。1．创建分类汇总

创建分类汇总的具体操作步骤：

（1）按分类字段进行排序。例如：按“部门”分类，

（2）选择“数据/分级显示”组，单击“分类汇总”按钮

，弹出“分类汇总对话框”如图5-56所示。

（3）在“分类字段”、“汇总方式”的下拉列表中以及“选定汇总项”复选框中，选择所需选项。例如：汇总方式为“平均值”，汇总项为“基本薪金”。

若本次汇总前，已经进行过某种分类汇总，根据需要决定是否替换原汇总数据，选中（或清除）“替换当前分类汇总”。

根据需要决定每类汇总是否独占一页，选中（或清除）“每组数据分页”。

（4）单击“确定”。分类汇总后的结果如图5-57所示。2．删除分类汇总

如果要删除已经创建的分类汇总，可在“分类汇总”对话框中单击“全部删除”按钮，即可删除分类汇总。3．隐藏分类汇总数据

若要只显示分类汇总和总计的汇总，请单击行编号旁边的分级显示符号

。使用

和符号来显示或隐藏各个分类汇总的明细数据行。5.6.5数据合并

数据合并可以把来自不同源数据区域的数据进行汇总，并进行合并计算。不同源数据区包括同一工作表中、同一工作簿的不同工作表中、不同工作簿中的数据区域。数据合并是通过建立合并表的方式来进行的。

【例题5-15】现有在同一工作簿中的“1分店”和“2分店”4种型号的产品一月、二月、三月的“销售数量统计表”数据清单，位于工作表“销售单1”和“销售单2”中，如图5-58所示。现需新建工作表，计算出两个分店4种型号的产品一月、二月、三月每月销售量总和。数据合并的具体操作步骤：

（1）在本工作簿中新建工作表“合计销售单”数据清单，数据清单字段名与源数据清单相同，第一列输入产品型号，选定用于存放合并计算结果的单元格区域B3:D6，如图5-59所示。

（2）选择“数据/数据工具”，单击“合并计算”按钮

弹出“合并计算”对话框，在“函数”下拉列表框中选择“求和”，在“引用位置”下拉按钮下选取“销售单1”的B3:D6单元格区域，单击“添加”按钮，再选取“销售单2”的B3:D6单元格区域，单击“添加”按钮（此时，单击“浏览”按钮可以选取不同工作表或工作簿中的引用位置），选中“创建指向源数据的链接”复选框，如图5-60所示。计算结果如图5-61所示。

（3）合并计算结果以分类汇总的方式显示，单击合计销售单工作表左侧的“+”号，可以显示源数据信息。

5.6.6建立数据透视表

数据透视表从工作表的数据清单中提取信息，它可以对数据清单进行重新布局和分类汇总，还能立即计算出结果。在建立数据透视表时，需考虑如何汇总数据。

【例题5-16】现有如图5-62所示工作表中的数据清单，现建立数据透视表，显示各分店各型号产品销售量的和、总销售额的和以及汇总信息。具体操作步骤：

（1）选定工作表中的一个单元。

（2）选择“插入/表格”组，单击“数据透视表”按钮

，弹出“创建数据透视表”对话框，如图5-63所示。确认“表/区域”框内为所需数据区域，“选择放置数据透视表的位置”选中“现有工作表”单选框，单击

，在现有工作表指定放置数据透视表的单元格区域