透视表技巧

1.透视表－规范数据

数据透视表的数据有些要求：不能有空列，不能在表头有合并单元格，不能在行字段有合并单元格，数据区域也不能有合并单元格。最好是数据块里面也没有空行。

有空行时候不便于快速选定数据块。数据区域最好是没有空格（空格都填上0）。有空列是无法做透视表的：

2.透视表－基本操作之做新透视表

选定数据块（CTRL+A或者CTRL+*），菜单“数据（D）”—“透视表和透视图（P）”，得到的是如下的样式：

按“完成（F）”。

得到下图的结果：

一定要注意这个结构：

（1）、请将页字段拖到此处

比如把部门字段拖到页字段，我们后续可以选择其中过一个部门获得这个部门里的一些数据信息。

（2）、将行字段拖到此处

要实现汇总的字段，比如供应商编码及名称，物料编码，会计科目代码及名称等。其实是对这里写字段进行分类汇总。行子段可以拖多个，比如把地区和省份都放在行字段里。

（3）、将列字段拖到此处

这个一般来说比较少用的，比如有年字段和月字段的，我们可以把年和月都拖到列字段，这样就可以实现按年、月分别做汇总。注意年月字段的先后顺序，两个选项是可以直接在列字段处拖动来调换位置的。

（4）、请将数据项拖到此处

这里是防止数据的，用来实现求和或者计数等功能。当只有一个数据项的时候，结构如下：

当有2个及以上的数据项时，结构如下：

这个时候可以把“数据”C3拖到“汇总”D3那个单元格。则纵向排列的数据变成了横排了：

3.透视表－已有透视表如何操作

透视表已经做好，数据有添加的时候，我们可以鼠标点原透视表上任意位置，在鼠标右键，“数据透视表向导（W）”，上一步－上一步，就可以去重新选定数据透视表的范围了，如果又增加列，可以将新的字段添加在行、列、页字段的任意一个里。

4.透视表－行列字段汇总的取消

行字段里面常常有一些我们不需要的小计，这个时候，可以选择D6单元格，鼠标右键，选择“隐藏（N）”，即可将汇总做隐藏。

另一个操作路径更直观，可以选择D6单元格，鼠标右键，可以选择“字段设置（N）”，在分类汇总下选择“无(N)”

需要取消行的总计和列的总计，可以在透视表任意一个位置鼠标右键—“表格选项”，将“列总计（G）”或者“行总计（T）”取消勾选。其余还有：对于错误值的显示；空单元格的显示；是否显示明细数据等选项可以选择：

5.透视表－计算字段及列出公式

在菜单“视图（V）”－“工具栏（T）”，选择“数据透视表”，也可以直接在菜单栏鼠标右键选择“数据透视表”：

点“数据透视表（P）”的倒三角，“公式（M）”－“计算字段”。可以设置公式，如”名称（N）：输入“出勤率”，公式（M）输入＝出勤天数/满勤天数。

再按添加（A）如下图：

在字段（F）下可以找到出勤率：

按“确定”，则出勤率已经添加到了透视表的数据项里。

别人已经在透视表设好公式或者我们设好了公式希望显示出来给人看，透视表本身有这个功能：

调出数据透视表工具栏，点“数据透视表（P）”的倒三角，“公式（M）”－“列出公式（L）”。即可在一个新表页列出公式如下图：

6.透视表－添加和删除计算项

单击数据透视表的列标签或行标签，再单击“数据透视表工具”—“选项”—“工具”－“公式”下拉按钮－“计算项”。调出“在“销售方式”（本例中的字段名）中插入计算字段”对话框：

设置名称为：外销比重

公式：=（转厂出口＋直接出口）/( 内销+ 直接出口+ 转厂出口)

再按添加即可完成外销比重的计算：

这个用处非常大，做财务分析的时候，在透视表里做一些项之间的比较很实用的。

7.透视表－字段修改

透视表里生成的行字段一般都是表里的内容，而数据项会出现“求和项：×××”的样式，这个时候，我们可以选中这个数据项的描述，直接进行修改。

例如将下表的数据项求和分别修改:

修改后的样式如下：

我们修改之后的描述不能在基础数据的数据项的描述一致，象上面我修改为满勤天、出勤天是可以的，可不能修改为满勤天数和出勤天数。因为这两个字段在基础数据表的的数据项已经存在了。而出勤率是我设置公式自定义的名称，当我修改为出勤率的时候，透视表会提示“已有相通数据透视表字段名存在”。所以我们修改后的描述不只是不能和基础表的列字段一样，还不能与透视表自定义的名称一样。这个时候我们希望实用出勤率这个字段，可以在出勤率前面加一个空格——“出勤率”会认为和已有的字段名不一样。

如果我们希望保留透视表里的字段名，而不希望显示“求和项：”，这个时候我们可以批量选定该行，将“求和项：”替换为" "（一个空格）。

8.透视表－应用自带格式

调出透视表自带格式：在菜单栏鼠标右键，可以调出常用工具栏窗口，选择透视表，则透视表工具条会出现。

透视表生成的默认格式有“数据”觉得不够漂亮的话：

我们可以做一些调整。当我们停留在透视表上，点数据透视表工具条的“设置报告格式”。

可以进入一些自带格式选择的窗口，推荐第四个和最后一个。最后一个样式则没有了上述的“数据”行。如果想要返回最初的默认格式，可以选择倒数第二个“传统数据透视表”：

9.透视表－排序

在Excel2007以及高级版本里，透视表是可以选择某一列进行排序的。而Excel2003的透视表里排序则有一些技巧。

务必停留在行字段。在透视表工具条的“数据透视表（P）”右侧的倒三角打开选项，可以看到排序并列出前面10个（A）。

选择升序或者降序，然后在在“使用字段（G）”选择要排序的数据。

而另一个自动显示前10项。可以点“打开（N）”，然后显示理选择最大或者最小，右侧可以选择要显示的个数。再选择“使用字段（U）”里选择相应的字段。

10.透视表－透视表内数据的引用

我们做了透视表后，想要引用透视表里面的数据时，发现设置公式的时候会出现=GETPIVOTDATA()的样式：

向下拖拉的时候，不能对数据表内的单元格做相对引用。这个时候我们有一个小技巧，选定C5:D5或者更大一个区域，会下图的效果：

这个时候我们删除：D5，就实现了对C5的相对引用。

规范的操作方式是：把透视表的“生成GetPivotData”调出来，点以下这个图标：