（友情提醒，多图，请在wifi下观看！流量壕请无视本条）

有些朋友后台给小奚留言说很喜欢之前讲的透视表内容，问能不能整理出一个Excel透视表的合集，只要是透视表的内容都能在里面找，今天小奚就给大家带来了这篇文章。

不用写公式，不用手工计算，数据透视表通过简单的拖拽就能完成各个维度你想要的数据分类汇总，可以说是基础的Excel操作里面最简单易上手，最实用，最常用的功能了。

话不多说，上目录。本文内容较长，干货较多，大家可以根据目录挑选自己想看的内容。

我是目录，目录是我

上篇：基础操作

01 创建数据透视表

【问】：老板给你公司今年的订单明细，让你告诉他每个销售今年的销售额是多少？

看见了吗？

创建透视表就是如此简单，通过拖拽，就能立马得到老板想要的结果。

有很多初次接触数据透视表的同学对透视表的四个字段（筛选器、行、列、值）的意思不太了解，小奚用一张图告诉大家：

字段拖放在不同的区域，就会以不同的显示方式显示汇总的结果，并且同一个区域内的顺序不同，在数据透视表内汇总的先后层次也会不同。这也就是为什么透视表能完成各个维度的数据汇总。

02  透视表转普通表格

【问】：小奚啊， 老板只需要最终的汇总结果，不需要把数据明细发给他，那么怎么把透视表转成普通表格发给老板呢？

这个很简单，只需要复制数据透视表，粘贴成值就行了，如果喜欢透视表的格式还可以像小奚一样粘贴一下格式。

03  更改值字段计算方式

【问】：老板说我不仅想看每个销售这一年销售的总金额，还想看他们这一年签了多少笔合同。

在前面我们用到的是求和的计算方式，对于这个问题，我们可能就需要用到计数的计算方式了。

订单编号是文本类型，我们可以看到将订单编号拖到值字段时Excel会自动计算，因为文本类型不能求和，而金额是数值，可以求和，也可以计数。

除此之外，值字段的计算方式还有以平均值，最大值，最小值等计算，但是在我们平时的运用中，最常用到的还是计数和求和这两种。

04  插入计算字段

【问】：老板说，我还想知道每个销售以现在的年销售额他们的提成能拿多少（按千分之七来算）？

这里我们有一种偷懒的做法，直接在透视表外面乘以0.007，对于这种方法会出现两个问题：

第一，有的同学会发现写完公式下拉后，数字并不会改变，仍然是第一个的计算结果，对于这种情况可以如此操作：【文件】-【选项】-【公式】-取消勾选【使用GetPivotData函数获取数据透视表引用】，如下图所示。

但即使是解决了第一个问题，也会发现一旦改变透视表的结构，我们的计算结果就不能使用了，因为它在透视表外面不会随着透视表的改变而改变。

所以，其实最好的方法是在Excel里面插入计算字段。

05  透视表排序与筛选

【问】：老板说，把每个销售以销售额降序排列，同时，给我筛选出总销售额排名前10的销售。（老板，您的需求不能一次说完吗？）

降序排列其实比较好办，只需要选中金额所在的地方，右键选择降序排列即可。

如果存在多个字段的情况下，怎么筛选出老板想要的呢？直接使用行标签去筛选是行不通的，解决方法是：将鼠标点在列标签外面一格，然后使用筛选功能，具体操作请看下图。

06  行列百分比汇总

【问】：前面说的都是数值，但是我想看百分比怎么办？

想要值以百分比的形式显示可以：【右键】-【值显示方式】-可选择相应的百分比选项。

数据透视表有多种数据百分比的显示方式，下面我们只挑选最常用的：总计百分比、行/列汇总百分比和父级汇总百分来看。

总计百分比

当我们分析各个项目占总值的百分比时，就可以用总计百分比。例如：老板想看销售1部在服装这个产品占整个公司的销售额的多少。

行/列汇总百分比

当我们想看某个数据在行字段或者列字段的垂直维度上的占比时，就需要用到行/列汇总百分比。

列汇总百分比，例如：老板想看每个销售在单个产品上的占比是多少，我们就需要拉列汇总百分比给他看。

行汇总百分比，例如：老板想知道单个销售在每个产品的上售卖金额占比是多少，我们就需拉行汇总百分比给他看。

父级汇总百分比

当我们不想看某个数据占全部的占比，只是想看它在细分维度的占比的时候就需要用到父级汇总占比。

例如：老板说我想知道王麻子的销售额在他们团队占比是多少。

07  修改行列字段顺序

【问】：小奚，透视表自己出来的行列字段的顺序有的时候并不是我们想要的顺序，是不是只能【右键】-【移动】-【上移/下移/移至开头或结尾】？

其实小奚曾经也是这么干的，当时字段还特别多，小奚拿着鼠标点右键，上移下移了无数次，差点没崩溃。

这时领导从小奚身后飘过，实在看不下去了，说了一句：“我都是直接拖就可以了。”此刻小奚的内心戏是：“领导，我错了，是我学艺不精，你就当我是在练手指的灵活度吧！”

恩，忍住笑，严肃脸。下面来看领导是怎么拖的，上动图（前部分为上下移动的操作方式，后部分为直接拖动的操作方式）。

丢了这么大个人，不扳回一城不是小奚的风格啊，所以小奚又潜心学习，一个关于修改字段的更高阶技能出现啦！

这个技能对于反复使用的字段比较方便，平时若用得少，直接拖动会更方便一些。

【先做一个辅助表】-【文件】-【选项】-【高级】-【常规】-【编辑自定义列表】-【选择最开始建立的辅助表】-以后就只需要排序就能按我们希望的字段顺序出现了。

08  刷新与更改数据源

刷新

【问】：如果我想修改源表的数据，透视表会自动更新吗？

默认是不会自动更新的，需要手动刷新，如果是在原基础上修改，不增加行列的话，我们只需要刷新就可以了，如果有多个数据透视表可以选择全部刷新。如果害怕自己忘记刷新，也可以设置【打开文件时刷新数据】

更改数据源

【问】：我的数据源表修改了，刷新了也没有出现我修改的东西怎么办？

这种情况，就是我上面说的增加了行或者列啦，只是刷新是不行的，还需要更改数据源。

更高级的用法

有的时候我们并不想每次都去修改数据源，那太过于麻烦。又怎么办呢？

把数据源把设置成“表格”就可以解决这个问题。

只要把数据源表设置成“表格”，不管增加行还是列都不需要再去更改数据源，只需要刷新即可（注意，只针对将数据源更改为“表格”之后建立的透视表有效，这也是为什么在动图的例子里小奚要重新建透视表的原因）。

并且通过动图可以看到，设置成“表格”后，如果在首行输入公式都不需要下拉，会自动匹配。

09  透视表的复制与删除

【问】：我想复制或者删除透视表怎么办？删除必须要把整个sheet删除掉吗？

复制透视表的情况其实蛮常见的，因为有的时候选取的数据源是相同的，需要做不同维度的汇总分类，如果不想重新新建sheet，那么复制透视表后在这基础上更改字段是最好的方法。

只需要全选透视表，复制粘贴即可。

删除透视表只需要全选透视表，直接按detele键就能全部删除。

中篇：美观与布局

10  透视表的三种布局

【问】：做为一个有追求的员工，只会Excel透视表默认的呆板展示方式简直不能忍，好吗？小奚呀，为什么别人的透视表和自己的就是长得不一样呢？

那我们就要从透视表的三类展示姿势开始说起了，这三类布局分别是：压缩形式、大纲形式、表格形式。在哪里找到这三类布局呢？

【设计】选项卡-【布局】菜单栏-【报表布局】（注意哟，给透视表穿上美丽外衣的大多数功能都是在【设计】选项卡实现，这里也是我们今天的主要阵地，大家可以自己研究研究这个选项卡的内容噢!）

压缩形式

有眼尖的同学已经发现啦，其实压缩形式就是我们Excel默认的透视表格式，它主要的特点呢就是：

无论叠加多少个行字段，都只占一列。如果对这个概念还不是特别明白，可以多和下面讲到的两个布局方式做对比，相信你很快就能明白啦。

大纲形式

大纲形式与压缩形式最重要的区别就是：大纲形式有几个行字段就会占几列，即行字段会并排显示，就如我们例子中的行字段有三个，那么大纲形式的布局就会占三列而不像压缩形式只占一列。

另外，大纲形式的分项汇总显示在每项的上方。

表格形式

表格形式的透视表是小奚最常用的一种形式。它的主要特点呢是：

1、与大纲形式一样，行字段会并排显示，有几个行字段会占几列；

2、与大纲形式不同的是，表格形式是有表格的（好像在说废话，记得看图找区别噢）；

3、与大纲形式第二个不同是表格形式的分项汇总是在每项的下方，而大纲形式是在上方。

以上的三种布局形式的特点，你都了解了吗？

11  显示和隐藏分类汇总

【问】：小奚啊，你的例子里面，我只想看每个销售细分到各省份客户的销售额，并不想看销售汇总的销售额，并且那些销售汇总放在里面看得我眼花，怎么解决呢？

嗯，确实是这样，有的时候我们并不需要看分类汇总，但是透视表会自动显示，有的时候我们甚至都不需要看总计，那么怎么隐藏和显示分类汇总和总计呢？还是在【设计】选项中哦！

看完动图大家应该比较清楚在哪里显示和隐藏分类汇总和总计了吧？

不过细心的同学应该已经发现动图里一个小问题了吧？在这里小奚要考一考大家哟！

【提问】：为什么小奚选择的是在组的顶部显示分类汇总，最后Excel还是在组的底部显示的分类汇总呢？要结合我们前面讲的内容哦，知道答案的同学请大声在留言区告诉我吧！

12  标签项重复显示

【问】：对于大纲形式布局和表格形式布局，因为行字段是并排显示，特别是前面的行字段，常常一个就对应了后面多个字段，让表格不好看，怎么优化呢？

哇！这个问题好，在这里小奚要告诉大家两个优化的方法：第一个是让我们的标签项重复显示，第二个是合并行标签。

先讲让标签项重复显示：【设计】选项卡- 【布局】-【报表布局】-【重复所有项目标签】。（注意，标签重复项只对大纲式和表格式有效，对压缩式无效，想想为什么？）

13  合并行标签

合并行标签也是回答上面的提问，这是另外一个优化行字段并排显示的方式。

当然，大多数的同学可能会对合并行标签更感兴趣一点，因为小奚发现，大多数的同学在操作Excel的时候，对合并单元格尤为热衷。（虽然合并单元格一直被称为Excel处理数据时的一大杀手）

好了，废话不多说，直接上操作：【右键】-【数据透视表选项】-【布局和格式选项卡】-【合并且居中排列带标签的单元格】（注意：合并行标签只对表格形式布局有效，对大纲式和压缩式无效，不信你可以试试哟！）

14  插入空行间隔

【问】：小奚啊，我们公司的透视表数据很多，老板说看得他密集恐惧症都要犯了，该怎么办呀？（同学，你是认真的吗？）

我们在每一项之间插入空行，对于透视表数据很多的情况可能会有所帮助。

具体操作：【设计】选项卡- 【布局】-【空行】-【在每个项目后插入空行】

对于插入空行，在展示的时候会比较好，因为看起来让人不那么累。但是在日常处理数据的时候，空行会带来一些麻烦，所以处理数据时还是建议大家不插入空行哟。

15  取消字段前+/-符

【问】：透视表行标签前面总是有+/-符号，看起来有些丑，可以隐藏吗？

当然可以呀，上操作：【分析】选项卡-【显示】-【+/-按钮】

16  刷新后格式保持不变

【问】：前两天一个同事一脸悲痛地来找我，说透视表的排版布局我都做好了要给领导看，行高和列宽都需要固定，不能变，但是每次我一刷新透视表列宽和格式就全变了，要崩溃了。

不知道工作中你是否也遇到了这样的情况呢？不要心急，一招就能帮你搞定：

【右键】-【数据透视表选项】-【布局和格式选项卡】-取消勾选【更新时自动调整列宽】

17  透视表模板套用

【问】：透视表模板套用是不是让透视表变美观的东西呀？

真聪明，在Excel里面已经内置了一些透视表的模板，我们可以选择自己喜欢的模板，直接套用就行了，还是在【设计】选项卡里面。

最后的最后，关于美观与布局，还可以给透视表更改好看的字体和字号，例如微软雅黑等，再结合以上的内容，立马让你的透视表秒杀同事的呆板透视表。

下篇一：分组

18 文本分组

【问】：小奚，老板除了看一个省的销售情况，还希望看一个区域的情况，比如：想看西区的销售情况（西区包含：贵州、湖北、陕西、四川、云南、重庆），该怎么办呢？

对于这种情况，其实有两种方式，一是在数据源表里面去添加区域的辅助列，二是不添加辅助列，直接在透视表里面创建文本分组啦。

按住Ctrl键选择贵州、湖北、陕西、四川、云南、重庆，然后点击“鼠标右键”选择“创建组”数据透视表按照我们所选定的内容进行组合。接着可以修改组的名称，例如改为西区。

19 日期分组

【问】：既然可以Ctrl键选中想要分组的内容来分组，那么如果对日期来分组是不是也可以这样呢？

严肃地讲是可以这样的，以前领导让小奚拉一个月的数据，小奚选中了这个月的每一天然后创建了分组，但这样做有点傻，因为Excel是很智能的，能够对日期进行识别，对于日期的分组，我们有个简便的方法。

选中日期，右键选择“创建组”，就可以按照我们的需求进行月、季度、年的汇总啦！

怎么样？Excel还是很智能吧？

20 数值分组

【问】：1、小奚，我是一个HR，要对员工进行工龄汇总，5年为一个工龄段，有什么简单的方法吗？

2、小奚，老板需要对销售的金额段分析，该怎么分金额段呢？

那就需要用到透视表的数值分组啦，依然是选中司龄或者金额，右键选择“创建组”，在组合中可自己设置起始和结尾以及步长，如有不清楚，请看动图吧。

司龄分组：

金额段分组：

大家发现了吗？透视表只能按照等距步长来组合哟！比如司龄的步长设置为5，那么只能按照5的间隔来组合。

但是在实际运用中，特别是对于金额段的实际运用，我们常常需要的是不等距组合，比如：1000-2000金额段，2000-5000金额段。对于不等距的组合我们该怎么操作呢？

不等距组合有两种操作方式：

一是按照18分本分组讲的Ctrl选中做组合的方式创建不等距组合，这种方式对于数据量小的时候，还能操作，对于数据量大的时候其实是蛮绝望的。

第二种方法是需要在源表建立辅助列，然后用VLOOKUP的模糊匹配，这个我们会在下一次讲到。

下篇二：数据表出错原因

常常有人来问我为什么我的透视表出问题了呢？其实透视表出错的原因绝大多数都是因为咱们的数据源表出了问题。

21  某些列没有标题

【问】：我无法创建数据透视表，显示透视表字段名无效是什么意思啊？

这种情况是因为我们的数据源表某些列没有标题，这种情况常见于前一列的标题过长，覆盖了没有标题的那一个让我们误以为都是有标题的，具体请看动图。

操作：需要我们把数据源表的标题行补全就可以正常创建透视表啦。

22 存在合并单元格

【问】：我的数据源表中明明没有空白，但是在透视表中却出现了空白是怎么回事呢？

这可能是数据源表中存在合并单元格，Excel会将除了合并单元格的第一格以外的全部当做空值处理。

操作：取消合并单元格，并填充完整，再刷新数据透视表就可以看见没有空白啦。

23 日期非法

【问】：为什么我不能对透视表的日期进行月、季度、年的分组呢？

检查一下源表，看看是否存在非法日期？例如：20170101、2017.1.1等。

操作：回到数据源表，选中日期列-分列-选择日期-完成。就可以看到日期被变成了标准格式，再去透视表刷新，就可以按日期创建组啦！

24  数据源存在文本类型的数字

【问】：小奚，为什么我的源表里面明明是数字，但是透视表里只能计数，求和的结果总是显示0呢？

同样的我们需要去检查一下数据源表，看看是不是用文本形式保存的数字呢？

操作：将以文本形式储存的数字按数值类型储存，方法是：选中-分列-完成，刷新透视表即可。

那我们来总结一下，在创建透视表的时候出问题绝大多数情况下需要我们去检查源表，看看是否：某些列没有标题、存在合并单元格、非法日期以及文本类型存储的数字。

学完本文，几乎就能解决Excel透视表绝大多数的问题啦！

还在看什么？赶紧动手操作吧！

如果你觉得本文对你有所帮助，请尽情收藏或转发吧！欢迎转载，另外，若想要本文案例数据源欢迎简信撩我。

END