ExcelHome培训 http://t.excelhome.net

分页符

腻如玉指涂朱粉 光似金刀剪紫霞

——Excel 2007综合班I课时小结之三

 

曾参加过数据透视表的初级班，从完全没用过到现在每日（工作日）必用，和数据透视表有了越来越深的感情。本节课木兰老师全面细致地对比了07版中数据透视表与03版中的差异，讲解了07数据透视表中的基本技巧，其实对自己又是一个提高的过程。

一、什么是数据透视表

1、打开方式

（1）开始—编辑组—查找和选择—转到/定位条件，如图

1、数据透视表的概念

数据透视表是用来从Excel数据列表、关系数据库文件或OLAP多维数据集中的特殊字段中总结信息的分析工具。它是一种交互式报表，可以快速分类汇总、比较大量的数据，并且可以随时选择其中页、行和列中的不同元素，以快速查看源数据的不同统计结果，同时还可以方便地显示或打印出感兴趣区域的明细数据。

2、数据透视表的优点

数据透视表有机地综合了数据排序、筛选和分类汇总等常用数据分析方法的优点，可以方便地调整分类汇总的方式，灵活地以多种不同方式展示数据的特征。一张“数据透视表”仅靠鼠标移动字段位置，即可变换出各种类型的报表。同时，数据透视表也是解决Excel公式计算速度瓶颈的手段之一。因此，该工具是最常用、功能最全的Excel数据分析工具之一。

3、数据透视表的用途

数据透视表是专门针对以下用途设计的：

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

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

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

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

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

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

如果要分析相关的汇总值，尤其是在要合计较大的数字列表并对每个数字进行多种比较时，通常使用数据透视表。

二、创建透视表及认识2007透视表布局

1、创建第一个数据透视表

创建数据透视表可使用工作表数据，也可引用外部数据，本次课主要讲的是为工作表数据创建数据透视表。步骤如下：

（1）为数据透视表定义数据源

若要将工作表数据用作数据源，只需单击包含该数据的单元格区域内的一个单元格，此时会默认选择了包含该单元格的连续区域。同时要确保该区域具有列标题或表中显示了标题，并且该区域或表中没有空行。区域选择时不要整行整列地选，这样会产生空白项，也不利于将来做组合字段。

（2）创建数据透视表

在“插入”选项卡上的“表”组中，单击“数据透视表”命令，若要 同时创建基于数据透视表的数据透视图，单击“数据透视表”下方的箭头，再单击“数据透视图”。如图

 

在“创建数据透视表”对话框中，确保已选中“选择一个表或区域”，然后在“表/区域”框中验证单元格区域。Excel 会自动确定数据透视表的区域，但您可以键入不同的区域或您为该区域定义的名称来替换它。

对于来自其他工作表或工作簿的数据，可使用下列语法来包含相应工作簿和工作表的名称：[WorkBookName]SheetName!Range。也可以单击“压缩对话框” 暂时隐藏该对话框，然后在工作表上选择区域，再单击“展开对话框” 。如图

 

若要将数据透视表放置在新工作表中，并以单元格 A1 为起始位置，单击“新工作表”；若要将数据透视表放在现有工作表中的特定位置，选择“现有工作表”，然后在“位置”框中指定放置数据透视表的单元格区域的第一个单元格。单击“确定”后，Excel 会将空的数据透视表添加至指定位置并显示数据透视表字段列表，以便可以添加字段、创建布局以及自定义数据透视表。如图

 

（3）向数据透视表添加字段

在“数据透视表字段列表”中，执行下面的一项或多项操作：若要将字段放置到布局部分的默认区域中，在字段部分中选中相应字段名称旁的复选框。默认情况下，非数值字段会添加到“行标签”区域，数值字段会添加到“值”区域，日期和时间层级则会添加到“列标签”区域。

若要将字段放置到布局部分的特定区域中，在字段部分中右键单击相应的字段名称，然后选择“添加到报表筛选”、“添加到列标签”、“添加到行标签”或“添加到值”。如图

 

若要将字段拖放到所需的区域，在字段部分中单击并按住相应的字段名称，然后将它拖到布局部分中的所需区域中。如图

 

也可以根据需要使用“数据透视表字段列表”重新排列字段，方法是：在布局部分中右键单击相应字段，然后选择所需的区域，或者在布局部分中的区域间拖动字段。

2、数据透视表的布局

选择数据透视表，在功能区会出现上文标签，主要有“选项”和“设计”两个功能，如图

 

右键单击数据透视表，可调出布局管理器。如图

 

也可以点击“数据透视表工具—选项”选项卡的“显示”组中，单击“字段列表”命令来显示或隐藏布局管理器。如图

 

“数据透视表字段列表”窗口中可以通过右上角的下拉菜单从五种布局样式中选择相应的样式；字段列表相当于一个布局对话框：报表筛选（页字段）、行标签、列标签、数值（推迟布局更新，取消后所见即所得）。如图

 

 

07版数据透视表默认以压缩形式显示，对于习惯03版的同学，有两种方法可以更改为03的表格形式显示。

一是点击“数据透视表工具—设计”选项卡的“布局”组下的“报表布局”命令，勾选“以表格形式显示”。如图

 

二是在创建好空白的透视表上单击右键，选择“数据透视表选项—显示”，勾选“经典透视表布局”，如图

 

向数据透视表添加字段在前面已经提到过，删除的方法主要有三种，一是“选择要添加到报表的字段”框中，清除要删除的字段的复选框，二是在布局区域中，单击要删除的字段，然后单击“删除字段”，三是在布局部分中单击并按住字段名，然后将其拖到数据透视表字段列表之外。

3、更改数据源、移动数据透视表

对创建好的数据透视表，可以更改其数据源，也可移动或复制到其它位置。更改数据源是在“数据透视表工具—选项”选项卡的“数据”组中，点击“更改数据源”命令，在对话框中选择新的数据源即可。如图

 

移动数据透视表可在“数据透视表工具—选项”选项卡的“操作”组中，点击“移动数据透视表”命令，在对话框中选择放置数据透视表区域的第一个单元格即可。如图

 

 

复制数据透视表可在“数据透视表工具—选项”选项卡的“操作”组中，点击“选择—整个数据透视表”命令，选择整个数据透视表（直接鼠标拖拉也可），使用复制粘贴命令。如图

 

4、数据透视表中的分类汇总和总计字段

处理数据透视表 时，可以显示或隐藏各个列和行字段的分类汇总，显示或隐藏整个报表的列和行总计，计算有筛选项或没有筛选项的分类汇总和总计。

（1）对列和行字段进行分类汇总

在数据透视表中，选择行或列字段的某个项，在“选项”选项卡上的“活动字段”组中，单击“字段设置”中的“分类汇总和筛选”命令，若要使用默认汇总函数分类汇总外部行或列标签，单击“自动”，若要使用其它函数、显示多种类型的分类汇总，或者分类汇总内部行或列标签，单击“自定义”，然后选择函数，若要删除分类总计，单击“无”，若要在应用筛选（已在其中选择“筛选”菜单中的特定项）时包括或排除新项，请选中或清除“在手动筛选中包含新项目”复选框。

若要快速显示或隐藏当前的分类汇总，请右键单击字段项，然后选中或清除“分类汇总‘<标签名>’”旁的复选框，如果需要显示，可重新选择。如图

 

对于压缩或大纲形式的外部行标签，可通过在“设计”选项卡上的“布局”组中，单击“分类汇总”，选择“不显示分类汇总”、“在组底部显示所有分类汇总”或“在组顶部显示所有分类汇总”，在其项的上方或下方显示分类汇总，或者隐藏分类汇总。如图

 

（2）显示或隐藏数据透视表的总计

选择数据透视表，在“数据透视表工具—设计”选项卡的“布局”组中，点击“总计”命令，选择需要的显示选项。如图

 

在“选项”选项卡上的“数据透视表”组中，单击“选项”命令，在“数据透视表选项”对话框中的“汇总和筛选”选项卡上，若要显示或隐藏总计，选中或取消“显示列总计”复选框和/或“显示行总计”复选框。如图

 

（3）计算有筛选项或没有筛选项的分类汇总和总计

在“选项”选项卡上的“数据透视表”组中，单击“选项”，在“数据透视表选项”对话框中的“汇总和筛选”选项卡上，选中或清除“每个字段允许多个筛选”复选框，以在汇总中包括或排除筛选项，如上图。

5、显示或取消字段名前的+/-号

当行字段有多个时，非最后一个行字段明细数据的隐藏及打开与03中的加减明细一样，选择“选项”选项卡上的“显示”组中的“+/-按钮”，就可以显示或隐藏，隐藏后同样可以双击就可以出现同样的效果。如图

 

6、合并单元格显示

选择数据透视表，在“数据透视表选项”对话框中的“布局和格式”选项卡上，选中或取消“合并且居中排列带标签的单元格”前的复选框，使得同类项合并单元格且居中显示，也可选择压缩行标签。如图

 

7、数据透视表字段操作

（1）各字段的操作

报表筛选字段，勾选选择多项时可以进行多项选择，不勾选时，只能筛选一个或者全部显示，如图

 

可按照报表筛选的字段，将数据透视表分页显示，在“选项”选项卡下选择“选项”的“显示报表筛选页”命令即可分页，如图

 

对于报表筛选，可在“数据透视表选项”对话框中的“布局和格式”选项卡上，根据需要修改报表筛选的布局。如图

 

行列字段的筛选中，有两个筛选选项，一是标签筛选，二是值筛选，前者以标签本身的内容进行筛选，后者以科目划分下的各数据字段汇总值进行筛选。如图

 

数值字段在默认情况下视没有筛选的，实现方法是通过借助透视表外的单元格，向内拖动选取一行，然后再执行自动筛选命令就可以了。这样建立的筛选与透视表筛选不同，只有普通的筛选。如图

 

（2）各字段的排序

报表筛选不能直接排序，但可通过把报表筛选字段拖到行字段进行排序，再拖回即可。

行列字段的排序类似，排序类型有两种，一是可以根据标签自身数据升降排序或自定义序列排序，也可以手动拖动排序，二是依据数据标签进行升降序排序。

依据数值标签进行升降序排序如图

 

自定义序列排序，需事先将序列导入，选择“其他排序选项”的“其他选项”，选择主关键字即可。如图

 

手动排序是在鼠标指针出现四向箭头时拖动数据改变次序。

依据数值标签排序，如图

 

（3）字段的增加、删除和变换

添加字段是根据需要，将字段列表中的字段拖放到相应的报表筛选区、行标签、列标签或数值区。也可在经典布局下，直接将字段拖放到数据透视表相应位置。

删除字段可直接在字段名上单击右键，选择“删除‘<标签>’”，如图

 

也可在经典布局下，选中字段，直接拖到数据透视表外，出现删除符号时松开鼠标即可。

或者直接在字段列表中将相应字段取消勾选，也可以直接用鼠标拖拽。

同时，可以用鼠标拖拽改变字段的位置，真正实现数据透视表“拖拖拉拉”的强大精髓。

8、数据透视表格式设置

（1）刷新

右键“刷新”一下数据表即可，或者如下图命令：

 

对于同一个数据源生成的数据透视表，都可以进行刷新修改，如果是修改了字段的话，同样所生成的数据透视表都要进行刷新（同样的操作）后才能正确

当某些字段中字段项不存在时，刷新后下拉列表框中仍然可见这些多余的字段项，去除这些多余的字段项的方法：先删除字段，再刷新透视表，再把字段添加回去。

（2）数据透视表样式

在“数据透视表工具—设计”选项卡的“数据透视表样式”组下可选择样式或清除样式，也可自定义样式，如图

 

（3）汇总字段名称修改

可将“求和项：”、“平均值项：”等说明汇总方式的字样替换为空格，在字段名称不与数据源中字段名重名时即可修改。如图

 

（4）启用选定内容

在“数据透视表工具—选项”选项卡的“操作”组中的“选择”命令中，点击“启用选定内容”命令，可以快速定位相关数据。如图

 

三、理解透视表字段默认汇总方式

在数据透视表中，源数据中的每列或字段都成为汇总多行信息的数据透视表字段。值字段（例如“销售小计”）包含要汇总的值。默认情况下，值区域中的数据采用以下方式对数据透视图中的基本源数据进行汇总：数值采用 SUM 函数来添加值，文本值则采用 COUNT 函数来统计值的个数。

如果数据源有空值或文本，做出的数据透视表默认是计数，可以通过把文本转换为数值型数据，把第一行空白的地方输入,选取标题与第一行数字，先生成数据透视表，再通选取更改数据源选择整个区域，这样做出的数据透视表默认就是求和。

四、空白数据项及空值处理

对一些空白数据项产生数据透视表表中一些字段中的空白，可在字段下拉列表中不勾选显示，或替换为空格。如图

 

对于汇总后的空值或错误值，可在“数据透视表选项”对话框的“布局和格式”选项卡下，选择为0或空格，也可替换为其它值。如图

 

五、创建动态数据透视表

1、通过函数公式定义名称实现动态引用数据源

将可以实现动态引用数据源的函数公式定义为名称，在创建透视表时数据源区域直接输入该名称创建，首先定义名称data，在引用位置输入公式：=OFFSET(数据源!$A$1,,,COUNTA(数据源!$A:$A),COUNTA(数据源!$1:$1))，在创建数据透视表时，在表/区域中直接输入data即可。如图

 

当数据源增加行/列或是删除行/列时，透视表刷新后，自动随之改变数据源区域发生变化，透视表永远跟随发生了变化后的数据源创建。

此方法要求数据源中的首列和首行不包含空单元格，否则将无法用定义名称取得正确的数据区域。

2、创建表方式

将数据源通过插入表的方式创建为表后，在创建数据透视表，07中表支持行列内容的扩展－可实现动态数据透视表。如图

在Excel选项中的“公式”中“在公式中使用表名”勾选可以去掉在选择数据时出现的“表”。