4.4 数据管理

在实际工作中常常面临着大量的数据且需要及时、准确地进行处理，这时可借助于数据清单技术、数据排序、数据筛选、分类汇总、数据透视表来处理。

4.4.1 数据清单

在Excel 2010中，数据清单是包含相似数据组的带标题的一组工作表数据行，可以将“数据清单”看成是“数据库”，其中行作为数据库中的记录，列对应数据库中的字段，列标题作为数据库中的字段名称。 数据清单是一种特殊的表格，必须包含表结构和纯数据。 表中的数据是按某种关系组织起来的，所以数据清单也称为关系表。

表结构为数据清单中的第一行列标题，Excel 2010利用这些标题名对数据进行查找、排序以及筛选等。 要正确建立数据清单应遵守以下规则：

(1)避免在一张工作表中建立多个数据清单，如果在工作表中还有其他数据，要与数据清单之间留出空行和空列。

(2)列标题名唯一且同列数据的数据类型和格式应完全相同。

(3)在数据清单的第一行里创建列标题，列标题使用的各种格式应与清单中其他数据有所区别。

(4)单元格中数据的对齐方式可用格式工具栏上的对齐方式按钮来设置，不要用输入空格的方法来调整。

“记录单”的操作方法：在Excel 2010中打开工作簿，单击“文件”选项卡，单击“选项”，在打开的Excel选项对话框中切换到“快速访问工具栏”选项卡。 在“从下列位置选择命令”下拉列表中选择“不在功能区的命令”，找到“记录单”命令将其添加到快速访问工具栏中，此时就可以在快速访问工具栏中找到“记录单”按钮。

打开“第4章/工资表.xls”sheet1工作表工资表，然后再单击快速访问工具栏中的“记录单”按钮即可创建如图4-41所示工资表记录单。

如图4-41所示，通过记录单可实现对表记录的添加、修改、删除以及查找等操作。例如，要查找记录，可在如图4-41所示的对话框中单击“条件”按钮，随即会出现类似的空白记录单，通过在该记录单中输入相应的检索条件然后单击“上一条”或“下一条”按钮就可以查找记录。

4.4.2 数据排序

用户可以根据数据区域中的数值对数据的行列进行排序。 排序时，Excel 2010将按照指定的排序方式重新排列行/列或单元格。排序的方式有升序(0到9，A到Z)和降序(9到0，Z到A)。

Excel 2010提供了简单排序、复杂排序和自定义排序三种排序方式。

1.简单排序

简单排序是指按单一字段(关键字)进行升序或降序排列。 如果需要对工作表中的数据进行简单排序，首先选中所要排序字段内的任意一个单元格，再单击“数据”选项卡“排序和筛选”组中的或按钮进行简单排序即可。

2.复杂排序

当排序的字段(主关键字)有多个相同的值时，可根据另外一个字段(次关键字)的内容再排序，依次类推，可以设置多个次关键字进行复杂排序。

例如，对如图4-42所示的工资表以“部门”为主关键字进行升序排序，“部门”相同的再按“实发工资”降序排序。

首先选中要排序的数据区域，再单击“数据”选项卡“排序和筛选”组中的“排序”按钮，打开“排序”对话框。 在“排序”对话框的主要关键字下拉列表框中，选择部门，再单击“添加条件”按钮，在次要关键字下拉列表框中选中实发工资，在次序下拉列表框中选中降序，如图4-43所示，然后单击“确定”按钮，即可实现工资表按“部门”为主要关键字进行升序排序，“部门”相同的再按“实发工资”降序排序，排序结果如图4-44所示。

在如图4-43所示的“排序”对话框中，单击“选项”，打开“排序选项”对话框，如图4-45所示，在“排序选项”对话框中，可设置排序的方向(行/列)和方法(字母/笔划)。

4.4.3 数据筛选

用户在对数据进行分析时，常会从全部数据中按需选出部分数据，如从工资表中选出所有“中专部”的员工，或选出“基本工资”在600元以下的员工等，应用Excel提供的“自动筛选”和“高级筛选”就很方便。

1.自动筛选

自动筛选是一种快速的筛选方法，用户可通过它快速地选出数据。 其具体操作方法如下：

(1)单击数据清单中任一单元格或选中整张数据清单。

(2)单击“数据”选项卡“排序和筛选”组中的“筛选”按钮。

这时可以看到，在数据清单的每个字段名右侧都会出现一个向下的箭头，如图4-46所示。

单击要筛选列项的下拉箭头，如“基本工资”，则出现如图4-46所示的下拉列表框。在“数字筛选”子项下拉列表框中根据单元格数据类型显示与该类型相关的可选条件项，如数值类型则显示等于、大于、全部、10个最大的值、自定义筛选等。

单击相应筛选条件项打开“自定义自动筛选方式”对话框。 用户可在此为一个字段设置两个筛选条件，然后按照两个条件的组合进行筛选。 两个条件的组合有“与”或“或”两种，前者表示筛选出同时满足两个条件的数据，后者表示筛选出满足任意一个及以上条件的数据。 例如，筛选出基本工资大于或等于650或基本工资小于600的记录，“自定义自动筛选方式”对话框的设置如图4-47所示。 筛选的结果如图4-48所示。

如果要退出自动筛选状态，则再次单击“数据”选项卡“排序和筛选”组中的“筛选”按钮，则取消自动筛选且字段名侧的向下箭头也消失。

2.高级筛选

在实际应用中往往遇到更复杂的筛选条件，这时可以应用高级筛选。

关于高级筛选应用的示例：要求筛选出“工资表.xls”sheet1工作表中具有中专部中级职称，且岗位津贴在250至260之间的员工。

操作方法：

(1)在数据工作表下方选择空白区域作为设置条件的区域，并输入筛选条件：在C18输入“部门”，在C19输入“中专部”，在D18输入“职称”，在D19输入“中级”，在E18中输入“岗位津贴”，在E19中输入“〉250”，在F18中输入“岗位津贴”，在F19中输入“〈260”。

(2)单击数据区域中任一单元格，选择菜单中的“数据”选项卡“排序与筛选”工具组中的“筛选”按钮，弹出如图4-49所示“高级筛选”对话框。 在对话框“方式”中选择“将筛选结果复制到其他位置”。

(3)在“数据区域”中指定要筛选的数据区域 $A$3∶$I$13，再在“条件区域”中指定已输入的条件区域$C$18∶$F$19。 高级筛选对话框中还有一个复选框为“选择不重复的记录”，选中它，则筛选时会去掉重复的记录。

(4)单击“确定”，高级筛选结果如图4-50所示。

4.4.4 分类汇总

分类汇总就是把数据分类别进行统计，便于对数据的分析管理。 以下是具体的操作方法。

1.为数据区域插入汇总

具体操作步骤如下：

(1)先选定汇总列，对数据按汇总列字段进行排序，如按部门排序。

(2)在要分类汇总的数据清单中，单击任一单元格。

(3)单击“数据”选项卡“分级显示”功能区中的“分类汇总”按钮，打开“分类汇总”对话框，如图4-51所示。

(4)在“分类字段”下拉列表框中，单击需要用来分类汇总的数据列(如部门)。 选定的数据列应与步骤(1)中进行排序的列相同。

(5)在“汇总方式”下拉列表框中，单击所需的用于计算分类汇总的函数(如求和)。

(6)如图4-51所示，在“选定汇总项”(可多个)列表框中，选定与需要对其汇总计算的数值列对应的复选框。

(7)单击“确定”按钮，即可生成分类汇总，如图4-52所示为按部门汇总。

2.删除插入的分类汇总

当在数据清单中清除分类汇总时，Excel同时也将清除分级显示和插入分类汇总时产生的所有自动分页符。 具体操作步骤如下：

(1)在含有分类汇总的数据清单中，单击任一单元格。

(2)单击“数据”选项卡“分级显示”功能区中的“分类汇总”按钮，打开“分类汇总”对话框，如图4-51所示。

(3)单击“全部删除”按扭。

4.4.5 数据合并计算

所谓合并计算是指可以通过合并计算的方法来汇总一个或多个源区中的数据。Excel 2010提供了两种合并计算数据的方法。 一是通过位置，即当源区域有相同位置的数据汇总。 二是通过分类，当源区域没有相同的布局时，则采用分类方式进行汇总。

合并计算数据时首先必须为汇总信息定义一个目的区，用来显示摘录的信息。 此目标区域可位于与源数据相同的工作表上，或在另一个工作表上或工作簿内。 其次，需要选择要合并计算的数据源。 此数据源可以来自单个工作表、多个工作表或多个工作簿中。

在Excel 2010中，可以指定多个源区域来进行合并计算。 在合并计算时，不需要打开包含源区域的工作簿。

例如，要创建“工资表.xls”工作薄前三个工作表合并计算5～7月工资数据，将结果保存在sheet4工作表中，操作步骤如下：

(1)打开“第4章/工资表.xls”，为汇总信息定义目的区，注意保持前四列数据顺序与源数据区一致，如图4-53所示。

(2)单击“数据”选项卡“数据工具”功能区中的“合并计算”按钮，打开“合并计算”对话框，如图4-54所示。

(3)在“函数”下拉列表框中，选定希望Excel用来合并计算数据的汇总函数，求和(SUM)函数是默认的函数。

(4)在“引用位置”框中，输入希望进行合并计算的源区的定义，或单击“引用位置”框中红色箭头按钮，然后在工作表选项卡上单击“sheet1”，在工作表中选定源区域，如图4-55所示，该区域的单元格引用将出现在“引用位置”框中。

(5)如图4-54所示，按下“添加”按钮，对要进行合并计算的三个源区域重复上述步骤添加到“所有引用位置”。 单击“确定”按钮则可以看到合并计算的结果，如图4-56所示。

如果希望当源数据改变时，Excel会自动更新合并计算表。 要实现该功能的操作是：在“合并计算”对话框中选定“创建指向源数据的链接”复选框(见图4-54)，选定后在其前面的方框中会出现一个“√”符号。 这样，当每次更新源数据时，就不必都要再执行一次“合并计算”命令。 而当源和目标区域在同一张工作表时，是不能够建立链接的。

4.4.6 数据透视表

数据透视表是一种可以对大量数据快速汇总和建立交叉列表的交互式表格。 它能够对行和列进行转换以查看源数据的不同汇总结果，并显示不同页面以筛选数据，还可以根据需要显示区域中的明细数据。 数据透视表是一种动态工作表，它提供了一种以不同角度观看数据清单的简便方法。

1.数据透视表的组成

数据透视表一般由以下几个部分组成：

页字段：是数据透视表中指定为页方向的源数据清单或表单中的字段。 单击页字段的不同项，在数据透视表中会显示与该项相关的汇总数据。 源数据清单或表单中的每个字段、列条目或数值都将成为页字段列表中的一项。

数据字段：是指含有数据的源数据清单或表单中的字段，它通常汇总数值型数据，数据透视表中的数据字段值来源于数据清单中同数据透视表行、列、数据字段相关的记录的统计。

数据项：是数据透视表中的分类，它代表源数据中同一字段或列中的单独条目。 数据项以行标或列标的形式出现，或出现在页字段的下拉列表框中。

行字段：数据透视表中指定为行方向的源数据清单或表单中的字段。

列字段：数据透视表中指定为列方向的源数据清单或表单中的字段。

数据区域：是数据透视表中含有汇总数据的区域。 数据区中的单元格用来显示行和列字段中数据项的汇总数据，数据区每个单元格中的数值代表源记录或行的一个汇总。

2.创建数据透视表、图

以成绩表为例，成绩表的数据作为源数据，要求计算各院系各班的平均总成绩。

在工作表中单击“插入”选项卡“数据透视表”，打开“创建数据透视表”对话框，在对话框中选择“要分析的数据”和“放置数据透视表的位置”。

单击“确定”按钮后，分别将“院系”“班”“总成绩”拖到对应区域，将“总成绩”拖到“数据区域”时会自动显示为“求和项”，右键单击后在快捷菜单中选择“设置字段”，再在汇总方式中选择“平均值”，再对数据区域单元格的格式设置两个小数位，完成创建数据透视表，如图4-57所示。

用户可以应用“数据透视表”工具对数据透视表参数修改和设置格式，使数据透视表变得更加适用、美观。

【思考题】

(1)数据清单包括哪些功能?

(2)排序的方式有哪些?

(3)“自动筛选”和“高级筛选”有何区别与联系?

(4)举例说明对数据进行分类汇总的操作步骤。

(5)举例说明什么是合并计算?