透视表技巧总汇

jinwa · 发表于 2012-5-21 11:51:02

透视表是财务人员必须用好的工具，数据处理效率能大大提高

jinwa · 发表于 2012-5-21 11:53:18

1. 透视表－规范数据
数据透视表的数据有些要求：不能有空列，不能在表头有合并单元格，不能在行字段有合并单元格，数据区域也不能有合并单元格。最好是数据块里面也没有空行。有空行时候不便于快速选定数据块。数据区域最好是没有空格（空格都填上0）。有空列是无法做透视表的：
提示：数据透视表字段名无效。在创建透视表时，必须使用组合为带有标志列列标的数据。如果要更改数据透视表字段的名称，必须键入字段新名称。

jinwa · 发表于 2012-5-21 11:54:52

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

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

按“完成（F）”。

得到下图的结果：

一定要注意这个结构：

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

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

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

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

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

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

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

这里是防止数据的，用来实现求和或者计数等功能。

当有2个及以上的数据项时，可以把 “数据”C3拖到 “汇总”D3那个单元格。则纵向排列的数据变成了横排。

jinwa · 发表于 2012-5-21 11:57:19

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

jinwa · 发表于 2012-5-21 11:57:43

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

行字段里面常常有一些我们不需要的小计，这个时候，可以选择D6单元格，鼠标右键，选择“隐藏（N）”，即可将汇总做隐藏。
另一个操作路径更直观，可以选择D6单元格，鼠标右键，可以选择“字段设置（N）”，在分类汇总下选择“无(N)”：

jinwa · 发表于 2012-5-21 11:59:05

5.
透视表－计算字段及列出公式
在菜单“视图（V）”－“工具栏（T）”，选择“数据透视表”，也可以直接在菜单栏鼠标右键选择 “数据透视表”：
点“数据透视表（P）”的倒三角，“公式（M）”－“计算字段”。可以设置公式，如”名称（N）：输入“出勤率”，公式（M）输入＝出勤天数/满勤天数。再按添加（A）如下图：
在字段（F）下可以找到出勤率：
按“确定”，则出勤率已经添加到了透视表的数据项里。
别人已经在透视表设好公式或者我们设好了公式希望显示出来给人看，透视表本身有这个功能：
调出数据透视表工具栏，点“数据透视表（P）”的倒三角，“公式（M）”－“列出公式（L）”。即可在一个新表页列出公式如下图：

jinwa · 发表于 2012-5-21 12:05:27

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

设置名称为：外销比重
公式：=（转厂出口＋直接出口）/( 内销 + 直接出口 + 转厂出口 )
再按添加即可完成外销比重的计算：

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

jinwa · 发表于 2012-5-21 12:06:16

7.
透视表－字段修改

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

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

修改后的样式如下：

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

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