说数据透视表是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、如果第一个数据透视表数据源是名称区域引用，则可以对同区域新定义一个名称，供第二个数据透视表引用。

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

计算字段是对现有字段的所有项执行同一计算公式，得到一个新字段，计算项是对某一字段的已有项之间执行计算，相当于新建一个项组合但又不替代掉原项，所以结果其实是冗余的，这时候的总计已经是包含了新项的总计，范围会比原项要更大。举个例子，下面第一个图中的利润率=利润/销售额，是通过计算字段得到的。第二个图我们增加了计算项“办公用品+技术+家具”=办公用品+技术+家具三个项得到的。我们在最开始的“快速了解数据”中已经知道，该数据的总销售额是1600万，而现在这里的总计是3200万，是因为叠加了新的项“办公用品+技术+家具”。

讲到这里，有人可能还想不到用计算项有什么好处。

如果是个别项的组合，用分组功能就好，如果是所有项的组合，用总计就好，那计算项有什么额外好处呢？

有一个非常大的好处是，总计不能再拆到二级列维度，而计算项可以。

下图中在列维度“类别”下又增加了一个“细分”列维度（包含三个项：公司、消费者、小型企业）。可以看到总计下边是没有拆到“细分”列维度，而“办公用品+技术+家具”这个项下边继续下拆了。所以如果我们想同时看到各个项和总计的更细分列维度的拆解，就可以通过计算项的方式获得，而这时总计列就没有存在的必要了。

从前面定义可知，计算字段和计算项都是针对已有字段或项进行操作的，那么也可以基于已经建立好的计算字段或者计算项再新建字段或者项。如下图新建计算字段“利润率的1.2倍”，新建计算项“家具/总计”，可以看到家具占总销售额的比例以及家具的利润率相对总计的比例。

由于计算字段、计算项之间存在相互依赖的关系，我们可以通过“求解次序”更改公式求解顺序，还可以通过“列出公式”，一目了然看到所有计算字段和计算项的公式内容和求解次序。

计算项的用法局限

由于计算项是类似于字段分组的功能，所以在已经存在字段分组的情况下，不能插入计算项。

另外值得注意的是，同一字段的多数据字段和计算项不能共存的。但这个很容易解决，只要新建一个计算字段等于原字段就好了。

为什么这一节放在这里讲，而不是上一讲？是因为值的显示方式，跟行列维度非常相关。

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

接下来，我们将分成四组来分别展示（“指数”由于极少用到，此处不讲）。

第一组：百分比。

1、总计的百分比

特点是，同一个层级的所有单元格比例加总等于100%。如下图所示，示例中有两层行维度和两层列维度，所以总共有四个层级，分别用四种格式标注了。

四种层级分别是：行子维度+列子维度、行总维度+列子维度、行子维度+列总维度、行总维度+列总维度。这四个维度下的加总都是100%，都是对应单元格销售额与总计销售额（1600万）的比值。

2、列汇总的百分比

特点是，每一列都是除以该列的总计值。同一列同一个行层级的所有单元格比例加总等于100%。由于行有2级，所以共有两组数据加总等于100%。

3、行汇总的百分比

特点是，每一行都是除以该行的总计值。同一行同一个层级的所有单元格比例加总等于100%。与列汇总的百分比是一样的。

4、百分比

前面三个讲的都是某一行列总计的比例，而百分比是相对某一行列字段的某一项的比例。可以指定项，也可以设置相对上/下一项。比较灵活，但同时会产生很多没有意义的数值。

下图展示的是基本字段为“细分”，基本项为“上一个”的结果。最终只有一行有效数值。

5、父行汇总的百分比

与列汇总的百分比很相似，不同点在于行有2级可以组成三对父子关系，所以共有三组数据加总等于100%。

6、父列汇总的百分比

与“父行汇总的百分比”类似。

7、父级汇总的百分比

示例为选择基本字段为“细分2”的结果，可以看出，跟与“父行汇总的百分比”很像，但其实有效信息更少，因为细分2所在行，均为100%。

第二组：差异。

“差异”和“差异百分比”可以认为只是计算方法的区别，一个是A-B，一个是A/B-1，使用时一般搭配日期字段使用，可以计算很方便做出“同环比”指标。此处用示例演示“差异百分比”计算年同比。

第三组：按某一字段汇总。

“按某一字段汇总”和“按某一字段汇总的百分比”，可以实现SQL窗口函数功能。

下图示例中的每一行，都是按照年份字段按年顺序加总的，再除以行总计即为按年份字段汇总的百分比。需要注意年份不同的排列顺序得到的结果会有不同。Excel是按照从左至右（或从上至下）的顺序加总的。如果想得到预期的加总，一般要先做好排序。

第四组：升序排列和降序排列。

“升序排列”和“降序排列”，可以实现另一SQL窗口函数功能——row_number。

如下图，我们可以得到每一个细分行业内的大区销售额排序。

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

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

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

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

多个筛选是垂直排列，行号可能会发生变化。若要避免这一点，可将筛选区域字段显示改为“水平并排”。

值得注意的是，筛选字段从筛选区域移到字段区域的时候，筛选字段对值结果就不生效了。虽然字段区域中的原筛选字段右边有一个筛选漏斗样式的图表，但那仅仅意味着，该字段再次移入筛选区域时，保留原筛选状态。

字段列表中显示更多字段

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

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

数据透视表经典视图

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

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

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

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

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

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

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

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

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

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

选择布局方式的建议

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

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

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

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

修改默认布局

在2019版及之后的Excel版本中，可以在Excel选项设置中，编辑数据透视表的默认布局。

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

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

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

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

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

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

数据透视表最后一讲，文末给出案例Excel下载方式。包含数据透视表这三篇文章用到的案例数据、新建的数据透视表样式及自动创建VBA的代码。

解决问题：

根据城市维度的平均利润率分层，统计各分层下的城市数、省份数、订单量、销售额分布。

问题解析：

原始数据是订单明细，没有城市维度的汇总，所以首先第一步是先透视得到城市维度的平均利润率，汇总订单量、销售额，再基于该透视表再透视，得到按照城市维度利润率分层下的城市数、订单量、销售额分布。

第一步：直接透视

第二步：再透视前的处理

改字段名：我的习惯是先删除“求和项:”，然后在原字段后加一个空格，尽量跟原字段名一致，又不是完全一致导致出现下图的警告内容。

报表布局调整：以表格形式显示，不显示分类汇总，总计对行列禁用，重复所有项目标签。

第三步：在空白处插入一个新透视表

选择上一个透视表数据区域

第四步：按照需要设置透视表，与其他无异

by城市利润率分层结果

by省份+城市利润率分层结果

如下图所示，类别维度的“办公用品”和“家具”项下，按“细分”维度拆解，在“技术”项下，按“地区”维度拆解。

当然，可以用case when ... then... 方法拼接出一个新字段来，但未免有些麻烦。这里推荐的方法是，做两个透视表，纵向排布，隐藏中间行，关联筛选项。

选中筛选字段，插入切片器，点击报表连接，将本工作表的上下两个数据透视表选中，即可通过切片器或者第4行的上面这个数据透视表的筛选区域进行筛选。

Excel默认数据透视表样式常常与公司规范样式不符，无法直接粘贴或截图使用，通常做法是粘贴到新位置，再设置应用表格样式。

但更佳做法是，直接修改数据透视表的默认样式，这样就一劳永逸了。而且比表格样式适应各种灵活变换。

设置数据透视表样式

选中一个相对符合预期的样式，单击右键点击复制，新建一个数据透视表样式，命名为“默认”，勾选下方“设置为此文档的默认数据透视表样式”。

根据需要设置好相应的表元素，点击确定保存。

这个新建的数据透视表样式仅对当前工作簿的新建数据透视表有效，但如果在新工作簿当中需要使用这个已经设置好的数据透视表，要怎么办呢？

复制粘贴法在Excel2016版本之后不再生效，我们这里推荐使用复制工作表法，其实就是把整个工作表复制（或拖拽）到新工作簿当中，数据透视表及其样式都会出现在新工作簿当中，删除原数据透视表，样式依然保留，可以应用在新工作簿中新建的透视表上。

单元格引用与数据透视表函数GETPIVOTDATA

数据透视表任一所在单元格均可作为普通单元格被引用，如上图中引用技术的总销售额，可以直接在引用位置单元格输入=C60，也可以输入=之后选中单元格，但如果只选中C60这一个单元格，编辑器会出现“=GETPIVOTDATA("销售额",$B$59,"类别","技术")”。

可以通过“数据透视表工具”|“分析”|“数据透视表”|“选项”，勾选掉“生成GetPivotData”，这样在当指向数据透视表单元格时，只是普通单元格的引用。

两种引用方法对比

由于数据透视表会经常变化，单元格内容也会经常随之变化，单元格引用的方法就会经常失效。而函数GETPIVOTDATA在某些情况下不会失效，但它检索的数据必须是可见的，如果修改了数据透视表使得GETPIVOTDATA函数所使用的值变得不可见，则公式将会返回一个错误。

GETPIVOTDATA函数语法有三种，但了解其中一种即可，这个语法类似SUMIFS函数，所以比较好理解。

对比下SUMIFS函数，使用逻辑差不多，都少不了选择指标、条件、标准。

GETPIVOTDATA函数了解即可，一般情况下，都可以找到替代方案。

分析过程常常是指标字段已经基本确定，但要by很多维度交叉来看，这一部分需要多次摸索才能确定。确定的指标字段部分，我们就可以通过VBA的方式，直接固化下来。

回顾前面提到的，我们把字段放入透视表的时候，通常要进行的操作是，设置计算公式、更改聚合方式（求和、平均等）、设置数据格式、重命名字段名，再加上应用数据透视表样式。这些都是明确但又繁琐的，我们可以用过一个表格来提前管理这些不同字段的设置，然后用以下这段VBA直接调用这些设置，一键自动生成数据透视表。

演示过程：

附代码：

自此数据透视表的上中下三讲已全部完结。

『Excel数据分析』系列其他文章

Excel数据分析——数据输入

Excel数据分析——数据处理

Excel重磅分析功能之数据透视表（上）

Excel重磅分析功能之数据透视表（中）

接下来准备讲函数、快捷键、图表等内容。

如果你有其他感兴趣的内容想了解，或者对已有内容有任何疑问的，都可以私信我，一定知无不言。

关注公号『数据分析师修炼手册』，发送关键词“透视表”，获取案例文件下载。