说数据透视表是Excel数据分析中最高频使用的功能，一点都不为过，甚至可以说Excel数据透视表是数据分析师日常工作中最常用的功能（vs Python）。有人可能会说，Python中pandas.pivot_table也可以做透视表，甚至还有很多优势，比如能够处理的数据量更大（超100万行）、统计函数类型更丰富（可自定义聚合函数），那为什么还要用Excel呢？

原因就两个字：灵活。

灵活体现在以下几个方面：

1、拖拽布局：值、行、列、筛选四个区域调整方便。

2、汇总计算：“值汇总方式”有多种汇总函数类型可选、“值显示方式”可以切换多种自定义计算方式，还有计算字段和计算项可编辑使用。

3、多项组合：当一个字段有多个取值（Excel中叫做“项”）可以合并为一项时，可以手动组合，而且可以在新组合上继续组合。

4、即时交互：以上所有操作结果都是即时可见的，这在数据分析中非常好用，因为分析不是一件能提前确定所有分析操作的事，常常需要边做边调整。

5、直接输出：Excel数据透视表的结果就是“表”，可作为一个普通的表直接输出，基于此可视化或者再透视都是可以的。

当然Excel数据透视表也有一些小问题：

1、文本类型字段只能计数，不能取文本的min、max。这个问题Excel无解，只能通过其他工具处理。

2、汇总函数中没有非重复计数。这个Excel有解，通过将数据源转换为数据模型就可以使用，后文会讲。

3、字段名改名之后可以通过“字段设置”找到源字段名，但字段的项改名之后，无法知道修改之前是什么。

好了，接下来，我们就以“A商城销售数据”为案例，把常用功能串起来，并且保证常用的处理方法没有遗漏。这里没讲到的大多数用不上，所以有些知识点没学，也不必觉得有什么遗憾。与多数讲透视表的文章不同的是，我不仅会讲具体怎么操作，还会讲清楚为什么（在什么场景下）这么操作，以及我更多会讲应该怎么做，而不是可以怎么做，把数据透视表的最佳玩法，展现给大家，肯定会涉及到深水区（我目前还没看到有其他地方有讲过），因为这都是我每天反复磨炼的泣血总结，其中不乏诸多巧思在里面。话不多说，我们开始吧。

在Excel底部“自定义状态栏”右键单击，把统计的那六项全部勾选，以后选中的区域的基本六项统计指标就都会在自定义状态栏快速统计好了，不需要任何函数或其他操作。

通过选中明确知道不为空的A列（行id），数值计数为9959，我们就知道这份数据总记录数是9959。选中“销售额”列，求和为16068954.13，选中“销量”列，求和为37534。

选择位置时建议选“新工作表”，这个在Excel官方的学习文档中也有提到。透视表与原始数据分开放置在不同Sheet的，以免相互影响。

若数据源经常需要增加行记录或者列字段，那么应该将数据源设置成可动态更新的。有两种方法，表格法和名称法，但这里推荐你用表格法。

表格法就是把普通区域转成“表”，表的一个特性就是会自动扩展连续区域。

名称法就是通过名称管理器使用函数返回动态区域来定义名称。但这个方法有个问题是，必须使用完全没有空值的行和列，这在数据经常更新状态下是无法彻底保证的。

勾选“将此数据添加到数据模型”后，统计函数中就可以使用“非重复计数”了。

后文会有详细操作。

为什么先放值？通常我们对数据是了解总体情况，而不知细节，所以先放值可以校验数据结果是否与预期相符。若大数都有差异，那就需要排查取数逻辑是否正确。

tips：当我们往透视表的值区域放了2个以上的汇总字段时，列区域会出来“数值”，这个是可以拖动放置到行区域里的。

两种不同摆放位置的结果如下：

调整数字格式

在数据透视表的统计字段上右键点击，出现下图，可以看到有“设置单元格格式”和“数字格式”两个指令。

单元格格式与数字格式的区别是，“单元格格式”仅设置选中单元格区域的格式，而“数字格式”设置活动值字段的格式。我们希望数字格式是应用在字段上而不是选中的单元格上，这样在数据透视的布局发生变化时，该字段的所有值格式都是设置好的。这样看起来貌似设置“数字格式”是更好的选择，但其实不然。

若一次性选中整列（含总计行）应用“单元格格式”，布局变动也不会导致设置好的格式在新行上失效。另外使用“单元格格式”还有其他两个好处：

设置方式较多：这里的单元格格式，与“开始-数字”功能菜单中的设置按钮是一致的，也可以用Ctrl+1快捷调出设置窗口，或者应用快捷键，或者右键快捷菜单上方的浮动工具栏上的常用格式工具

设置格式更多：除了数字格式外，还可以设置字体、对齐等其他单元格格式。

所以推荐使用“单元格格式”设置数据透视表的字段格式，并且建议在选完指标之后就设置好数字格式，后续看数会更轻松。

数据透视表的数值区域只能是数值格式，所以源数据的字段以及数据透视表中的公式或计算字段的返回结果，都只能是数值或可直接转换为数值的日期或文本字符。

是计数、求和，还是求平均、最大、最小。同一个字段可以放多种不同汇总方式。

这里的非重复计数选项是置灰的，需要先建数据模型，在数据透视表字段选择区域中的最下方，点击“更多表格”。

字段筛选区域、值的名称会变得略有不同外，其他功能没变。可以看到非重复计数城市数是573个。

本案例中我们要计算两个字段：

1、分项及整体的利润率是多少？

2、分项及整体的折扣率是多少？

看C5单元格的公式我们知道，刚刚创建的计算字段公式（=利润/销售额），其实是sum(利润)/sum(销售额)，是整体利润率，即利润率的加权平均。

这是因为计算公式是应用在公式中的列总和上，而不是项（单个记录）上，无论这个列字段在数据透视表中的统计方式是求和还是平均。这就会导致在求整体的平均折扣率时，会出现问题。

首先直接对折扣字段求平均肯定是错误的，因为这没有考虑到不同金额的权重差异。

正确的整体折扣率公式应该是sum(销售额*折扣)/sum(销售额)。但如果计算公式写成销售额*折扣/消费额，在计算公式的实际应用其实是=sum(销售额)*sum(折扣)/sum(消费额)=sum(折扣)，最终的折扣率结果是折扣字段的加总=1059.7，这显然不对。

推荐的解决方法是，在源数据当中新增一列“折扣额”，计算公式=销售额*折扣，再在计算字段中新增“折扣率”，计算公式=折扣额/销售额，最终得到总的折扣率是9.15%。

出个题考考大家，当总计计算公式为每个子项结果的加权平均的场景下，以下哪类计算公式应该先提前在源数据中增加计算字段，而不能在数据透视表中的计算字段中使用呢？

1、(A+B)/C

2、A/(B-C)

3、(A*B)/C

4、A/(B*C)

答案是只有3和4需要。

当总计计算公式为每个子项结果的加总时，则透视表的计算公式中只能有加减法，不能有乘除，乘除计算需要提前在源数据中处理好。

行列其实没有本质区别，无非就是转置一下。但根据习惯，如果维度取值较多，更倾向于放行区域，能看到更多内容。

我们分析时常常需要对列字段项目取值较多的做一些分类汇总。这个列字段常常是文本型字段或者日期型字段，偶尔也需要对数值型字段项目分组。

Excel对日期型字段会自动分组。我们把订单日期字段拖到行区域，可以看到Excel自动将日期组合到了年/季/月，还可以选择日、小时、分和秒。但没有周可选。

我们经常出周报需要将日期按周分组，假设我们周的分组方式是上周四到本周三（2015年1月1日是周四）。我们可以将步长选为日，然后右下角目前置灰的天数可以调整了，我们调整为7天，则订单日期分组变成按周分组的了。

文本型字段只能手动分组，数值型可以等距步长自动分组，但比较少用，更多还是做手动分组。

手动分组可以遵循的方法有：

1、聚类法，原则是组内差异尽量小、组间差异尽量大。通常需要依赖一些指标结果来判断和调整。

2、业务参数法，假设业务上将1000元以上销售额的订单称为大单，100元以下订单为小单，那销售量的分层就可以参考这两个参数。

3、等量法，结果是希望让每组规模相当，那就根据分组数量，找到相应比例的分位数，作为分组参数。

同一数据源多个透视表其中一个变更组合方式后，另一个也会同步更新，如何解决？

具体问题：两个基于同一数据源的数据透视表，一个变更组合方式后，另一个透视表会同步更新。

这其实是因为数据透视表默认使用了共享缓存。共享缓存可以减小文件大小，另外还会在不同数据透视表间同步计算字段、计算项和项的分组。但同步分组这点有时候是讨厌的，比如第一个透视表已经做好之后，在第二个透视表上新做了一种分组，则第一个做好的分组会被替换为新的，这些若不注意，就都是预期之外的变更，想还原有难度。

通常我们是在做第二个透视表的过程中发现需要取消数据缓存共享，可以按下面步骤操作，核心是让新透视表引用的数据范围不同于第一个。

1、如果第一个数据透视表数据源是表类型，则新透视表数据引用可以改为区域引用

2、如果第一个数据透视表数据源是矩形/列区域引用，则新透视表数据引用可以改为区域引用列/矩形区域引用

3、如果第一个数据透视表数据源是名称区域引用，则可以对同区域新定义一个名称，供第二个数据透视表引用。

计算项和计算字段的使用差异

Excel数据透视表中有14种显示方式。

总计、列、行的百分比，都比较好理解。百分比

在『二、Excel数据分析——数据处理』已经讲过，大差不差，具体操作不再重复。

这里主要想给大家一些分析上的建议/提醒。

1、开始分析时，所有维度组合都放着，重点的靠上/靠左放，除非已经把数据看熟了，再把筛选字段挪到“筛选”框。

2、普通表格只能垂直方向排序，数据透视表是可以左右方向排序的。

字段列表中显示更多字段

当源数据字段数很多时，字段列表显示不下，可以通过修改字段节和区域节的显示方式放大。

下边左图是“字段节和区域节层叠”的摆放效果，右图是“字段节和区域节并排”的效果，但此时“数据透视表字段”整体是嵌套在Excel的菜单栏下边，仍没有充分利用纵向的空间。可以再通过移动“数据透视表字段”至悬浮在Excel界面之上，再拉伸至整个屏幕高度，这时就已经充分利用纵向空间了。

数据透视表经典视图

当还想为横向视图让出空间时，字段节和区域节布局中可以选择“仅字段节”，但此时最后搭配数据透视表经典视图来使用。

在选中数据透视表的任一单元格时点击鼠标右键，打开“数据透视表选项”，点击“显示”选项卡，勾选“经典数据透视表布局”，括号里的“启用网格中的字段拖放”是经典数据透视表布局的相比于默认布局的独特之处，即可以通过把字段列表中拖放到透视表区域，而不是数据透视表字段窗口下的四大区域节，所以为节省横向空间，可以选择“仅字段节”。

报表布局的四种显示形式及其区别

下图摆放了四种报表布局，区别主要有以下几点：

1、压缩形式的行和列字段名不显示，大纲和表格形式的则会显示

2、压缩形式的行区域字段，多个字段压缩在第一列，以缩进形式反映层级关系，大纲和表格形式则不会压缩，占用不同的列显示。

3、压缩和大纲形式默认分类汇总在组的顶部，若改成组的底部，则会新增一行，而表格形式只能显示在底部。

4、表格形式的分类汇总行是额外的，若不显示分类汇总，则这行会删除，而压缩形式和大纲形式原分类汇总行只删除数据不会删除行。

5、“重复所有标签”，压缩形式对列区域字段生效，大纲和表格形式对行列区域字段生效。

6、经典形式与表格形式除了字段拖放外，其他内容一模一样。

选择布局方式的建议

1、如无特殊要求，默认选择的压缩形式就很好。

2、如想把字段名展示出来，则选择大纲形式。

3、若要基于数据透视结果再透视（后文会讲），则用表格形式，取消分类汇总，重复所有标签

4、若希望能拖拽字段到数据透视区域，则选择经典视图。

以下示例是2行*2行*2值的较为复杂的情况，数值均放在列区域中。上图的数值是放在列的最下方，而下图的数值是放在列的最上方。

可以对比看出，列数值的摆放位置，只影响列的显示顺序。所以要如何摆放就取决于想把哪些维度/指标的数据挨着看。

想固定维度下看两个指标之间的关联，就先放维度，再放数值。

想比较同一个指标下，不同区域不同邮寄方式的数值，就数值放最上方，然后分别是区域和邮寄方式。

当然，如果想固定区域维度，看同一指标下邮寄方式的差异，也可以先放区域维度，中间放数值，然后放邮寄方式。

同理，数值组合也可以挪到行区域内，放在行字段维度的最外侧、中间和最内侧。