任务四
数据管理与分析

一、创建数据清单

数据清单即常说的表格，它由一行文字作为数据类型的表头标志。在标志下是连续的数据区域。数据清单的第一行必须是文本类型，为相应列的名称。用户只是执行了数据库的命令，Excel命令会自动将数据清单默认为一个数据库，注意：一个数据库中不可以有空白的行或列，空白的行或列意味着把一个数据库分为多个数据库。数据清单中的列是数据库中的字段，数据清单中的列标志是数据库中的字段名，数据清单中的一行则是对应数据库中的一条记录。

1．创建数据清单应遵循的原则

（1）一个数据清单最好占用一个工作表。

（2）数据清单是一片连续的数据区域，不允许出现空行和空列。

（3）每一列包含相同类型的数据。

（4）将关键数据置于清单的顶部或底部，避免将关键数据放到数据清单的左右两侧，因为这些数据在筛选数据记录时可能被隐藏。

（5）显示行和列。在修改数据清单之前，要确保隐藏的行和列已经显示出来，如果清单中的行和列没有显示出来，那么数据有可能被删除。

（6）使用带格式的列标。在输入列标前，将单元格设置为文本格式。对于列标，要使用与清单中数据不同的字体、对齐方式、格式、填充色等。

（7）使清单独立。在工作表的数据清单与其他数据间至少应留出一个空行或者一个空列。这样才是一个独立的数据库。在执行排序、筛选和自动汇总等操作时，这将有利于用Excel检测和选定数据清单。

（8）不要在单元格内容前或后面输入空格，这些不好的习惯会影响数据库的排序和查找等操作。

2．创建数据清单

在Excel里，数据库作为一个数据清单来看待，我们可以理解数据清单就是数据库。在这个数据库中，信息按记录存储。每个记录中包含信息内容的各项，称为字段，一般数据库的第一行体现着每条记录的对于字段的属性，称为字段名。每一条客户信息就是一条记录，它由字段组成。所有记录信息的同一个字段存放相似的信息。

1）创建字段名。

创建字段名的步骤如下：

选定某行的第1个单元格设置单元格“数字”，格式为“文本”；在此单元格中输入该字段的字段名，创建字段名后，即可在个字段名下方直接输入数据。

2）输入数据记录。

在输入数据时，除了可以直接在数据清单中输入数据外，还可以使用“记录单”命令来输入或追加数据。Excel 2010以后的版本不再提倡使用“记录单”命令，所以想使用“记录单”命令，需要提前设置显示“记录单”命令。

方法如下：

单击“文件”选项卡里的“选项”，在“选项”对话框里选择“自定义功能区”，在“从下列位置选择命令”复选框里选择“不在功能区的命令”后，在下方找到“记录单”后单击选中，再单击对话框中间位置的按钮“添加”后“确定”，此时在Excel的快捷工具栏中就出现了“记录单”快捷按钮。使用“记录单”快捷按钮可以减少在行与列之间的不断切换，从而提高输入的速度和准确性。

3）设置数据有效性。

要为数据有效性设置数值和参数，操作步骤如下：

（1）选定要设置数据有效性的单元格，注意不要选择字段名。

（2）选择“数据”选项卡下“数据工具”分组里的“数据有效性”命令，在弹出的浮动面板里选择“数据有效性”。

（3）在“数据有效性”对话框里有四个选项卡：设置、输入信息、出错警告和输入法模式。单击“设置”选项卡，如图1-24所示。

（4）从“允许”下拉列表中选择一个需要设置的有效属性，然后在“数据”里设置对应的有效范围，单击“确定”即可。如果想设置输入错误信息后的提示对话框，可以单击“出错警告”设置，如果想设置数据有效性区域提示浮动面板，在“输入信息”选项卡里进行设置。

3．删除或编辑记录

删除记录的步骤如下：

选择数据清单中的任意一个单元格；选择“快速访问工具栏”中“记录单”按钮；在打开的对话框中，单击“上一条”或者“下一条”按钮来查找所删除的记录，也可以用对话框中间的滚动条移动到要删除的记录，然后单击“删除”按钮将其删除。

编辑记录通常指的是对数据进行修改。在“记录单”命令中编辑记录的具体操作步骤和删除记录基本差不多一致，只是在找到所要修改的记录后，直接在相应的文本框里进行编辑修改，然后按回车即可。如果要增加一条记录，将面板中间的滚动条滑到最下面，此时左侧的文本框全部为空，输入新的记录后按回车即可。

二、数据排序和筛选

1．排序

排序是将数据库中的记录按着一定顺序进行排放。其中，数字是按照数字本身大小进行排序，文字默认按着汉字拼音字母的先后顺序进行排序，也可以设置按笔画顺序进行排序，并且可以将相同内容的记录排在一起，从而达到分类的目的。

例如，金星公司12月份工资表如表1-2所示，Excel默认人民币单位为“元”。

将“金星公司12月份工资表”先按“性别”升序排序，再按“奖金”降序排序，具体方法如下：

（1）在数据区域内单击任意一个单元格。

（2）单击“开始”选项卡，选择“编辑”组中“排序和筛选”按钮，在弹出的下拉菜单中选择“自定义排序”按钮，打开“排序”对话框。

（3）在“排序”对话框中的“主要关键字”列下拉列表中选择“性别”，在“次序”下拉列表中选择“升序”按钮。

（4）单击“添加条件”，然后在下面“次要关键字”列下拉列表中选择“奖金”，在次序下拉列表中选择“降序”按钮，如图1-25所示。

（5）单击“确定”按钮，出现如图1-26所示的排列效果。

在“排序”对话框中如果选中“数据包含标题”单选按钮，则表示在排序时保留记录的字段名称行，字段名称行不参与排序。如果未选中“数据包含标题”单选按钮，则表示在排序时删除字段名称行，字段名称行中的数据也参与排序。

2．筛选

筛选是指为工作表中的数据指定某些特定的条件，使工作表中只显示满足条件的数据记录，其他不符合条件的数据记录全部隐藏起来。

Excel 2010提供了两个筛选命令：用于简单条件的“自动筛选”和用于复杂条件的“高级筛选”。与排序不同，筛选并不重排记录，只是暂时隐藏不必显示的行（记录）。

1）自动筛选。

图1-27为望海学院教师工资表。

在望海学院教师工资表中筛选出“岗位工资”为3 200的记录，就可以按以下步骤筛选数据：

（1）在望海学院教师工资表中单击任意一个单元格。

（2）切换到“数据”选项卡，在“排序和筛选”组中单击“筛选”按钮，此时在每个字段的右侧会显示一个下拉按钮“ ”。

（3）单击“岗位工资”右侧的下拉按钮，在弹出的下拉列表框中选择“数字筛选”，再在右侧的列表中选择“自定义筛选”，将弹出“自定义自动筛选方式”对话框。

（4）在该对话框中设置筛选条件，在对话框左边的下拉列表框中选择“等于”，在右边的列表框中输入“3 200”，如图1-28所示。

（5）单击“确定”按钮，筛选完毕。筛选结果如图1-29所示，只显示了“岗位工资”为3 200的记录，而隐藏了其他数据行。

自动筛选功能也能设置多项筛选条件，比如不仅要筛选“岗位工资”为3 200的记录，而且要筛选“性别”为男的记录，可以在图1-29所示的“岗位工资”为3 200的记录中再单击“性别”单元格右侧的下拉按钮，进行相应设置即可。

在筛选后的数据表中用户可以发现，使用了自动筛选的字段，其字段名右边的下三角箭头变成了“ ”，而且行号也呈现为黄色。

2）高级筛选。

如果需要进行筛选的数据列表中的字段比较多，筛选条件又比较复杂，则使用自动筛选就显得非常麻烦，此时使用高级筛选将可以非常简单地对数据进行筛选。

使用高级筛选时，必须先建立一个条件区域，输入筛选字段名称，并在其下方输入筛选条件，然后打开“高级筛选”对话框，设置筛选条件。高级筛选可以和自动筛选一样对数据列表进行数据筛选，但与自动筛选不同的是，使用高级筛选将不显示字段名的下拉列表，而是在区域下方单独的条件区域中键入筛选的条件，条件区域允许设置复杂的条件筛选。需要注意的是，条件区域和数据列表不能连接在一起，必须用一条空记录将其隔开。对于比较复杂的数据筛选，使用高级筛选可以大大提高工作效率。

例如，在望海学院教师工资表中筛选出性别为男，岗位工资大于等于3 000，总工资大于7 000的记录，其操作步骤如下：

（1）在条件区域中输入列标志和进行筛选的条件，如图1-30所示。

（2）选择数据区域内任意一个单元格。切换至“数据”选项卡，单击“排序和筛选”组中的“高级”按钮，打开“高级筛选”对话框。如图1-31所示。

（3）默认选择“在原有区域显示筛选结果”单选按钮，表示在原区域上进行筛选，且Excel自动选择工作表中的列表区域，用户无须设置“列表区域”。

（4）单击“条件区域”右侧的“跳转”按钮，切换至工作表，选择前面输入的筛选条件所有单元格“$C$12:$E$13”。

（5）单击“确定”按钮，即可在原区域中显示出筛选结果，如图1-32所示。

三、数据分类汇总

1．创建分类汇总

分类汇总是对数据列表进行数据分析的一种方法。分类汇总对数据列表中指定的字段进行分类，然后统计同一类记录的有关信息。利用自动分类汇总可实现一组或多组数据的分类汇总、求和，还可以求平均值、最大值、最小值，计数，求标准偏差及总计方差等。

在进行分类汇总前先确定两点：一是进行分类汇总的列已经排好序；二是工作表中的各列都包含列标题。

例如，对望海学院教师工资表以职称为单位求总工资的平均值，操作步骤如下：

（1）选择“职称”字段的任意单元格，切换到“数字”选项卡，单击“排序和筛选”组中的“升序”按钮，将“职称”字段按“升序”排列好。

（2）选中数据区域内的任意一个单元格。

（3）单击“分级显示”组中的“分类汇总”按钮，弹出“分类汇总”对话框。在“分类字段”中选择“职称”，在“汇总方式”中选择“平均值”，在“选定汇总项”中选择“总工资”。如图1-33所示。

（4）单击“确定”按钮，分类汇总完毕，结果如图1-34所示。

提示：在分类汇总时，要进行分类汇总的数据列表必须有字段名，即每一列的数据都要有列标题，同类型的数据要连续，Excel根据列标题及连续的数据类型来创建数据组并计算总和。

2．删除分类汇总

对数据清单进行了分类汇总，如果不满意，可以对分类汇总进行删除，切记不可以简单单击删除键进行删除，那样只能删除文本，并不能删除分类汇总。具体操作如下：

（1）在数据清单中任意单击一个单元格。

（2）单击“分类汇总”命令，在弹出的“分类汇总”对话框中单击“全部删除”按钮即可。

四、数据透视表

数据透视表是一种对大量数据快速汇总和建立交互列表的交互动态表格，是Excel中的数据分析利器，数据透视表的主要用途是从数据库的大量数据中生成动态的数据报告，对数据进行分类汇总和聚合，帮助用户分析组织数据。还可以对记录数据较多、结构复杂的工作表进行筛选、排序、分组和有条件地设置格式、显示数据规律。

例如，某公司2020年加班补贴费报表，如图1-35所示。

利用图1-35中的数据创建数据透视表的步骤如下：

（1）把活动单元格确定在数据列表任意一单元格，单击“插入”选项卡下“表格”分组里的“数据透视表”——“创建数据透视表”，此时出现“创建数据透视表”对话框，如图1-36所示。如果活动单元格没有确定在数据列表任意一单元格，此时需要在“表/区域”中进行选区的设置，选区为所有字段名及所有记录，然后单击“确定”。

（2）完成透视表创建过程后，自动在当前工作表标签左侧添加新工作表标签，同时显示“数据透视表”工具栏。

在新工作表中，左上角提供了新表格重组的设置区，右上角提供了“数据透视表字段列表”区。如图1-37所示。

（3）此时，可以通过拖拽的方式，根据自己透视的需求，将“选择要添加到报表的字段”中所列的字段，分别拖拽到“报表筛选”“列标签”“行标签”和“数值”中，如图1-38所示。