数据透视分析及其功能

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

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

1.创建数据透视表

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

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

图1-54　数据源

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

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

图1-55　“创建数据透视表”窗口

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

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

图1-56　选择数据区

图1-57　数据透视表字段列表

图1-58　创建的数据透视表

2.数据透视图

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

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

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

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

图1-59　选择“数据透视图”

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

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

图1-60　数据透视表字段列表

图1-61　数据透视图

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

3.数据透视的编辑

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

（1）添加字段。

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

图1-62　按“类别”细分销售额的数据透视图

（2）设置字段。

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

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

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

图1-63　打开并设置单元格格式

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

图1-64　设置单元格格式

（3）筛选字段。

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

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

图1-65　报表筛选字段

图1-66　行标签字

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

（4）更改汇总方式。

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

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

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

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

图1-67　值字段设置

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

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

3.格式化数据透视表

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

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

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

图1-68　选择数据透视表样式

4.删除数据透视表

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