任务四 数据管理与分析

一、创建数据清单

数据清单即常说的表格，它用一行文字作为区分数据类型的表头标志。在标志下是连续的数据区。数据清单的第1行必须为文本类型，为相应列的名称。用户只要执行了数据库命令，Excel 2010会自动将数据清单默认为一个数据库。数据清单中的列是数据库中的字段，数据清单中的列标志是数据库中的字段名，数据清单中的一行则对应数据库中的一条记录。

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

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

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

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

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

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

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

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

（8）不要在单元格前面或后面输入空格，不然将影响排序和搜索。

2. 创建数据清单

（1）创建字段名。创建字段名的步骤：选定某行的第1个单元格并在其中输入文本，在与该单元格相邻的右侧单元格中输入其他作为字段名的文字。创建字段名后，即可在各字段名下直接输入数据。

（2）输入数据。在输入数据时，除了可以直接在数据清单中输入数据外，还可以使用“记录单”命令来输入或追加数据。使用记录单功能可以减少在行与列之间的不断切换，从而提高输入的速度和准确性。

打开Excel 2010，单击“文件”菜单，然后在下拉菜单中单击“选项”，打开“Excel选项”对话框。在“Excel选项”对话框中单击“快速访问工具栏”，然后在右侧“从下列位置选择命令”下拉框中选择“不在功能区的命令”。下拉滑块，找到“记录单”功能，然后单击“添加”，添加到“快速访问工具栏”。单击“确定”按钮，这时，我们看到快速访问工具栏上添加了“记录单”按钮。单击“记录单”按钮，打开“记录单”窗口，在每个字段后的文本框中输入数据。按“Tab”键可以在各个字段间切换。输完一条记录的内容后，单击“新建”按钮，可以继续添加新的记录。

输入所有的记录后，单击“关闭”按钮返回到工作表中，新加入的记录将列在清单的底部。

（3）设置数据的有效性。为数据有效性设置数值和参数的操作步骤如下。

①选定应用数据有效性的字段所在的列。

②选择“数据”选项卡下“数据工具”分组里的“数据有效性”命令，弹出一个级联菜单，选择“数据有效性”命令。

③在“数据有效性”对话框中有4个选项卡：设置、输入信息、出错警告和输入法模式。如果没有选定“设置”选项卡，则单击选定它，如图1-16所示。

④从“允许”下拉列表中选择一个数值。

⑤从“数据”下拉列表中选择一个选项。

⑥显示的参数依赖于“允许”和“数据”中的选项。输入限制参数，很多情况下，仅仅是最小值和最大值，比如最小数字和最大数字，或者是允许的最早日期和最晚日期。

⑦单击“确定”按钮完成操作。

3. 删除或编辑记录

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

编辑记录通常指的是对数据进行修改。在记录单中编辑记录的具体操作步骤与删除记录基本一致，在找到所要修改的记录后，直接在相应的文本框中进行编辑修改即可。

二、数据排序和筛选

1. 排序

排序是将数字或文字按一定顺序进行排列。其中，数字是按照数字本身大小进行排序，文字是按照汉字拼音字母的先后顺序进行排序，并且可以将相同的内容放在一起，从而达到分类的目的。

例如，某班学生成绩情况如表1-2所示。

若要将表1-2中的总分按从小到大的顺序排列，应首先选中总分列的任意一个单元格，然后单击“开始”选项卡下“编辑”分组里的“排序和筛选”按钮，在弹出的级联菜单中选择“升序”即可。如果要进行多个关键字排序，则选择“自定义排序”按钮，在“排序”对话框中进行设置。勾选“有标题行”，在“主要关键字”项中确定排序的第1依据，以及排序的标准是递增还是递减，如“列H”“升序”；若通过主要关键字排不出顺序，则单击“添加条件”按钮，在“次要关键字”项中确定排序的第2依据以及排序的标准是递增还是递减，如“列E”“降序”等，最后单击“确定”按钮即可。

2. 筛选

筛选是根据给定的条件从数据清单中找出并显示满足条件的记录，不满足条件的记录将被隐藏。数据筛选包括自动筛选和高级筛选。与排序不同，筛选并不重排清单，只是暂时隐藏不必显示的行。

（1）自动筛选。进行自动筛选时，首先选中数据清单中的任一单元格，单击“数据”选项卡下“排序和筛选”分组里的“筛选”按钮后，在每个字段名的右下方会出现“筛选控制”按钮，如图1-17所示，单击按钮可显示筛选的条件。如对表格中的数据进行筛选，筛选一类记录，则单击此类记录对应的关键字即可。

如果要取消对某一列的筛选，则单击该列首单元格右下方的“筛选控制”按钮，再单击“全选”；如果要在数据清单中取消对所有列进行的筛选，则再次单击“筛选”按钮即可。

（2）高级筛选。高级筛选能快速将满足多重条件的信息筛选并显示出来。

当数据管理过程中遇到一些复杂的筛选条件时，使用前述“自动筛选”功能将不能满足要求，必须使用“高级筛选”来实现，即建立筛选条件区域，并在该区域中设置相应的筛选条件。高级筛选的步骤如下。

选中表格后，单击“数据”选项卡下“排序和筛选”分组里的“高级”按钮，弹出对话框。在“数据区域”框中会显示被选中的数据区域单元格地址范围，在“条件区域”框中单击，选中条件区域，在“方式”选项区选中“在原有区域显示筛选结果”单选框，单击“确定”按钮，即可将筛选结果在原位置处显示出来（即隐藏不符合筛选条件的记录）。

取消高级筛选，单击“数据”选项卡下“排序和筛选”分组里的“清除”按钮即可。

三、数据的分类汇总

对数据进行分析和统计时，分类汇总是对数据进行分析的一个非常有力的工具。例如，对一个包含上千条商品信息的数据清单，有产品名称、地区、销售量等字段信息，用户可以根据需要使用分类汇总功能，产生按产品名称、地区和销售量分类的数据清单。

分类汇总可以对数据清单中的某一个字段进行如求和、平均值这样的汇总，对分类汇总值进行计算，并且能将计算的结果分级显示出来。在执行分类汇总命令前，必须先对数据清单进行排序，数据清单的第1行里必须有列标记。具体操作方法如下。

1. 创建分类汇总

（1）打开Excel文件。

（2）在数据清单中选择任意一个单元格。

（3）选择“数据”选项卡下“分组显示”分组里的“分类汇总”命令，打开“分类汇总”对话框。

（4）单击“分类字段”下拉列表框右边的按钮，在弹出的下拉列表中选择要进行分类汇总的列。

（5）单击“汇总方式”下拉列表框右边的按钮，在弹出的下拉列表中选择分类汇总的函数。

（6）在“选定汇总项”列表框中选择相应的列。

（7）单击“确定”按钮，就会产生分类汇总的结果。

2. 删除分类汇总

对数据清单进行了分类汇总后，如果对结果不满意，可以删除分类汇总，回到数据清单的初始状态。其具体操作步骤如下。

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

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

另外，也可以直接单击“撤销”按钮，或选择“编辑”菜单中的“撤销”命令来删除分类汇总，但是这两种方法都要求分类汇总后没有进行过其他操作。

四、数据透视表

数据透视表是一种对大量数据进行快速汇总和建立交互列表的交叉式表格，用于对多种来源的数据进行汇总。建立表格后，可以对其进行重排，深入分析数值数据，并且可以回答一些预料之外的数据问题，以便从不同的透视角度观察数据。数据透视表是专门针对以下用途设计的。

（1）以多种用户友好方式查询大量数据。

（2）对数值数据进行分类汇总和聚合，按分类和子分类对数据进行汇总，创建自定义计算和公式。

（3）展开和折叠要关注结果的数据级别，查看感兴趣区域汇总数据的明细。

（4）将行移动到列或将列移动到行（或“透视”），以查看源数据的不同汇总。

（5）对最有用和最关注的数据子集进行筛选、排序、分组和有条件地设置格式，使用户能够关注所需的信息。

（6）提供简明、有吸引力并且带有批注的联机报表或打印报表。

例如，某公司加班情况统计表如表1-3所示。

利用表1-3中的数据创建数据透视表的步骤如下。

（1）选中数据列表中的任意一个单元格，单击“插入”选项卡下“表格”分组里的“数据透视表”按钮，进入“创建数据透视表”对话框。

（2）被选中数据区域的地址显示在“选择一个表或区域”框内。

（3）确认数据区域正确后，选择数据透视表建立的位置，单击对话框的“确定”按钮即可。

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

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

（5）进入透视工作区后，系统将自动显示“数据透视表”工具栏。该工具栏罗列了一组工具图标，可以很方便地进行数据透视表的设置。例如，如果不小心关闭了“数据透视表字段列表”窗口，可以单击“选项”选项卡下“显示”分组里的“字段列表”按钮，重新打开窗口，如图1-19所示。

（6）将“数据透视表字段列表”中的字段名依次拖曳至对应的区域中，此时交叉统计的结果显示于新表格中，如图1-20所示。