第三节 数据的管理与分析

财务基础数据做好后，要筛选得到对工作有帮助的信息，必须对现有数据进行处理和分析，常用方法有排序、筛选、分类汇总和数据透视表。

一、数据排序

对数据进行排序是数据分析不可缺少的组成部分，我们对数据进行排序后就可以快速直观地显示数据，更好地理解数据，组织并查找所需数据，最终帮助我们作出有效的分析决策。

EXCEL 2010的排序功能更强大，优化后的排序功能可以更好地满足用户需求，除了可以对文本，数据进行排序外，还可对时间、日期、单元格字体颜色、图表、自定义序列等内容进行排序。

排序原则：

（1）如果按某一列进行排序，则在该列上完全相同的行将保持它们的原始次序。

（2）被隐藏起来的行不会被排序，除非它们是分级显示的一部分。

（3）如果按多列进行排序，则主要列中有完全相同的记录行会根据制定的第二列进行排序，如果第二列中有完全相同的记录行时，则会根据制定的第三列进行排序。

（4）在排序列中有空白单元格的行会被放置在排序的数据清单的最后。

（5）排序选项中如包含选定的列、顺序和方向等，则在最后列次后会被保存下来，直到修改它们或修改选定区域或列标记为止。

1．简单排序

简单排序是指排序的条件单一，工作表的数据是按照指定的某一种条件进行排列。

【例1-5】按佳园公司职工信息表中职工年龄的升序排列数据。

方法一：使用“升序”按钮排序。

步骤1：对数据进行简单升序排序。打开职工信息表，将光标定位于拟排序所在更任一单元格如F4单元格，单击【数据】选项卡中【排序和筛选】组的【升序】按钮。

步骤2：显示排序后的效果，如图1-41所示。

方法二：使用“排序”窗口进行排序。

步骤1：打开职工信息表，将光标定位于拟排序所在列任一单元格如F4单元格，单击【数据】选项卡、【排序和筛选】组、【排序】按钮。

步骤2：设置排序关键字。在打开的【排序】窗口中单击【主要关键字】右侧的下三角按钮，在展开的下拉列表中打击“出生日期”选项，如图1-42所示。单击【排序依据】下三角按钮“数值”项【次序】下三角按钮“升序”项并单击【确定】，完成设置。

2．高级排序

高级排序就是按照多个关键字对数据进行排序。除了在弹出的“排序”窗口中要设置主要关键字外，还要通过编辑设置对每个关键字来实现对数据的排序。设置多个关键字排序的目的是为了设置排序的优先级。

【例1-6】将佳园公司职工信息表先按“出生日期”的升序排列，若出生日期相同（如1975/12/6），再按“入职日期”升序排序。

步骤1：打开“职工信息表”工作簿，将光标定位于拟排序所在列任一单元格如F4，依次单击【数据】【排序和筛选】【排序】按钮。

步骤2：设置排序主要关键字。在【排序】窗口中单击【主要关键字】右侧的下三角按钮“出生日期”选项。

步骤3：设置添加条件。单击【排序】窗口【添加条件】，显示【次要关键字】排序条件，选择排序主要关键字为“基础工资”，排序依据“数值”，排序次序“升序”，如图1-43所示。

3．自定义序列排序

EXCEL 2010还允许对数据进行自定义排序，即按照我们事先设置的自定义序列中的顺序排序。

【例1-7】将佳园公司职工信息表按职工的文化程度大专、本科、硕士的次序排列数据。

步骤1：打开“职工信息表”工作簿，选取数据单元格任一单元格，单击【数据】选项卡、【排序和筛选】组、【排序】按钮。

步骤2：设置排序主要关键字。在【排序】窗口中单击【主要关键字】右侧的下三角按钮“文化程度”选项。

步骤3：单击【次序】下侧的下三角按钮，在展开的下拉列表中单击【自定义序列】选项，如图1-44所示。

步骤4：添加自定义序列。在弹出的【自定义序列】窗口的“输入序列（E）:”下的文本中，按竖列输入要排序的次序“大专”“本科”“硕士”、然后单击【添加】，如图1-45所示。

步骤5：选择自定义序列。单击【添加】按钮后，序列将添加到左侧【自定义序列】下的列表框里，选择序列“大专”“本科”“硕士”，单击【确定】按钮后退出【自定义序列】窗口，单击【确定】按钮退出【排序】窗口。显示排序后的效果如图1-46所示。

二、数据筛选

使用筛选功能选择数据，可以帮助我们快速而又方便地查找和使用所要的数据，筛选过后的数据显示为只是满足指定条件的数据，而那些无用的数据就会被隐藏起来。筛选数据之后，那些筛选产生的数据子集，就可以直接被我们用于分析和使用。

1．自动筛选

自动筛选可以用在快速筛选且筛选条件较少的数据时，一般情况下，我们在使用自动筛选时，筛选条件是单一的。

（1）单条件自动筛选。

【例1-8】筛选出佳园公司职工信息表中文化程度为本科的职工信息。

步骤1：定位于选择数据表范围内的任意单元格，单击【数据】选项卡、【排序和筛选】组、【筛选】工具按钮，如图1-43所示。

步骤2：单击“文化程度”字段右侧的【筛选】按钮下拉列表【全选】复选框，取消“√”标记【本科】复选框，打上“√”标记，单击【确定】按钮，如图1-47所示。

在图1-47中可以看出，对“文化程度”列的数据进行自动筛选后，右侧的筛选按钮发生了改变，因此，可以从筛选按钮上看出对哪些列的数据进行了筛选，即对哪些列表设置了筛选条件。另外，从工作表行号上可以看出隐藏了哪些行。

（2）多条件自动筛选。

【例1-9】筛选出佳园公司职工信息表中文化程度为本科的女职工信息。

步骤1：在所示的表格中单击“性别”字段后的【筛选】按钮，选择“女”，并单击【确定】按钮，如图1-48所示。

步骤2：重复上面的步骤，单击“文化程度”字段后的【筛选】按钮，选择“本科”。筛选结果如图1-49所示。

2．自定义筛选

如果要设置一个条件范围，筛选出符合或范围内的数据行，则需要使用自定义筛选。自定义筛选数据比较灵活，可以进行比较复杂的筛选。

【例1-10】筛选出佳园公司职工信息表中70年代出生的职工信息。

步骤1：选中出生日期列标下所有数据区域（F2:F11），点击【数据】选项卡下的【分列】，默认选项到第3步，将数据完全修改为“日期”“YMD”格式，如图1-50所示。

步骤2：单击选择数据表格范围内的任意单元格。

步骤3：在打开的【日期筛选】后选择【自定义筛选（F）…】，如图1-51所示。

步骤4：设置筛选条件。在打开的定义自定义筛选方式窗口中，选择出生日期“在以下日期之后或与之相同”“1970/1/1”“在以下日期之前”“1980/1/1”，如图1-52所示。点击“确定”后，筛选结果如图1-53所示。

3．取消筛选

对工作表进行筛选后，不符合条件的数据行就被隐藏。若需要查看被隐藏的数据行，就需要进行取消筛选操作。取消筛选时，对某一列数据进行了筛选操作，就需要对该列数据进行取消筛选操作。

【例1-11】取消筛选出20世纪70年代出生的职工信息。

单击【数据】选项卡、【排序和筛选】组、【清除】按钮，清除筛选结果；单击【筛选】图标按钮，撤销筛选功能。

三、数据透视分析

数据透视表（Pivot Table）是一种交互式的表格，可以对工作表数据的重新组合，它通过组合、计数、分类汇总、排序等方式从大量数据中提取总结性信息，用以制作各种分析报表和统计报表。数据透视表可以动态地改变它们的版面布置，以便按照不同方式分析数据，也可以重新安排行号、列标和页字段。每一次改变版面布置时，数据透视表会立即按照新的布置重新计算数据。另外，如果原始数据发生更改，则可以更新数据透视表。

例如，可以水平或者垂直显示字段值，然后计算每一行或列的合计；也可以将字段值作为行号或列标，在每个行列交汇处计算出各自的数量，然后计算小计和总计。再如，如果要按季度来分析每个雇员的销售业绩，可以将雇员名称作为列标放在数据透视表的顶端，将季度名称作为行号放在表的左侧，然后对每一个雇员以季度计算销售数量，放在每个行和列的交汇处。

1．创建数据透视表

EXCEL 2010提供了创建数据透视表的向导来进行操作与数据分析。

【例1-12】根据图1-54所示的“全家百货超市2015年各季度商品销售情况表”创建数据透视表。

步骤1：选中需要汇总的数据单元格，含列标题。

步骤2：单击【插入】选项卡、【数据透视表】下拉框选择“数据透视表”，如图1-55所示。

步骤3：此时，【选择一个表或区域（S）】下的【表/区域（T）:】会自动出现需要汇总的数据单元格（含列标题），单击选择【新工作表（N）】选项，如图1-56所示，单击【确定】按钮。

步骤4：在如图1-57所示的【数据透视表字段列表】任务窗格中，将“销售区域”和“类别”依次拖动至【行标签】下的空白框中，将“季度”拖动值【列标签】下的空白框中，再将“销售额”拖动至【数值】下的空白框中。单击【确定】按钮，创建好的数据透视表如图1-58所示。

2．数据透视图

数据透视图是数据透视表的图表化，更能体现“透视”的效果。

【例1-13】根据图1-54所示的“全家百货超市2015年各季度商品销售情况表”创建数据透视图。

步骤1：选中需要汇总的数据单元格，含列标题。

步骤2：单击【插入】选项卡、【数据透视表】下拉框选择“数据透视图”，如图1-59所示。

步骤3:【选择一个表或区域（S）】下的【表/区域（T）:】会自动出现需要汇总的数据单元格（含列标题），单击选择【新工作表（N）】选项，并单击【确定】按钮。

步骤4：此时出现【数据透视表字段列表】任务窗格，如图1-60所示，在其中将“销售区域”拖动至【行标签】下的空白框中，将“季度”拖动值【列标签】下的空白框中，再将“销售额”拖动至【数值】下的空白框中，单击【确定】按钮，创建好的数据透视图如图1-61所示。

我们如果按“类别”细分销售额，可以仅将“类别”拖拽至【行标签】下的空白框中，结果如图1-62所示。

3．数据透视的编辑

数据透视表的编辑包括对报表字段的设置和对整个报表的布局格式的设置。最基本的就是通过【数据透视表字段列表】窗格对报表字段进行设置。

（1）添加字段。

在数据透视表创建完成后，我们就需要添加数据透视表的字段。添加字段的方法有三种。例如，将“销售区域”字段添加到“报表筛选”空白框中。可以在【数据透视表字段列表】窗格中右击要添加的字段“销售区间”，在弹出的快捷选项卡中单击【添加到报表筛选】命令。

（2）设置字段。

【例1-14】将图1-58中所有数值型数据改为货币型显示。

步骤1：选择要设置的数据。按住鼠标左键拖动选中所有数值型数据区域B6:E14。

步骤2：打开【单元格格式】窗口，点击鼠标右键弹出快捷选项卡【设置单元格格式（F）…】，打开【设置单元格格式】窗口，如图1-63所示。

步骤3：选择【设置单元格格式】【数字】【货币】项，小数位修改为“2”位，选择货币符号“－Y”，如图1-64所示，单击【确定】。

（3）筛选字段。

数据透视表中的报表筛选字段、列标签字段和行标签字段，我们可以很方便地筛选出符合要求的数据，同时快速地查阅数据内容。

筛选报表字段值：单击【销售区域全部】右侧的下拉三角按钮，选择“日用品区”项，如图1-65所示，单击【确定】，完成对其他销售区间的隐蔽。

筛选行标签字段值：单击【行标签】右侧的下拉三角按钮，在展开的下拉列表中单击“全选”和“第4季度”项，如图1-66所示，单击【确定】，完成对其他季度的隐蔽。

（4）更改汇总方式。

在数据透视表中，EXCEL提供了多种汇总方式，包括求和、计数、平均值、最大值、最小值、乘积、数值计数等，用户可以根据需要选择不同的汇总方式来进行汇总。

例如，将数据透视表中的销售额的汇总方式改为平均值。

步骤1：单击要改变汇总方式的数据透视表中的任一单元格。

步骤2：选择平均值汇总方式。在数据透视表【选项】【字段设置】中，打开【值字段设置】窗口，选择【值字段设置】窗口中【汇总方式】选项卡中的“平均值”，如图1-67所示。

步骤3：设置汇总字段的数字格式。单击【值字段设置】窗口中的【数字格式（N）】按钮，打开【设置单元格格式】窗口，选择【数值】项，并保留2位小数并单击【确定】，回到【值字段设置】窗口。

步骤4：完成平均值设置。单击【值字段设置】窗口和【确定】按钮。

3．格式化数据透视表

数据透视表建立完成后，为了使数据透视表更加美观，我们还可以对它的格式进行设置。在设置其格式时，最简单、快速的方法是使用数据透视表样式。

设置数据透视表格式的实施步骤如下：

选定数据透视表格范围内的任意一个单元格，然后单击【设计】选项卡下的【数据透视表样式】、【其他】工具按钮和【数据透视表样式中等深浅9】，如图1-68所示。当然，我们也可以自动套用“数据透视表样式中等深浅9”的数据透视表效果图。

4．删除数据透视表

如果要删除数据透视表，先将光标定位于数据透视表，在【选项】选项卡上的【操作】组中，单击“选定”，然后单击“整个数据透视表”，再按键盘中的Delete键。