Excel数据透视表最全干货都在这儿了（合集，值得转发保藏）

4lqedz

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

有些伴侣后台给小奚留言说很爱好之前讲的透视表内容，问能不可整理出一个Excel透视表的合集，只要是透视表的内容都能在里面找，今天小奚就给大众带来了这篇文案。

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

话不多说，上目录。本文内容较长，干货较多，大众能够按照目录挑选自己想看的内容。

我是目录，目录是我

上篇：基本操作

01 创建数据透视表

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

看见了吗？

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

有非常多初次接触数据透视表的朋友对透视表的四个字段（筛选器、行、列、值）的意思不太认识，小奚用一张图告诉大众：

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

02  透视表转普通表格

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

这个很简单，只需要复制数据透视表，粘贴成值就行了，倘若爱好透视表的格式还能够像小奚同样粘贴一下格式。

03  更改值字段计算方式

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

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

订单编号是文本类型，咱们能够看到将订单编号拖到值字段时Excel会自动计算，由于文本类型不可求和，而金额是数值，能够求和，亦能够计数。

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

04  插进计算字段

【问】：老板说，我还想晓得每一个营销以此刻的年营销额她们的提成能拿多少（按千分之七来算）？

这儿咱们有一种偷懒的做法，直接在透视表外面乘以0.007，针对这种办法会显现两个问题：

第1，有的朋友会发掘写完公式下拉后，数字并不会改变，仍然是第1个的计算结果，针对这种状况能够如此操作：【文件】-【选项】-【公式】-取消勾选【运用GetPivotData函数获取数据透视表引用】，如下图所示。

但即使是处理了第1个问题，亦会发掘一旦改变透视表的结构，咱们的计算结果就不可运用了，由于它在透视表外面不会随着透视表的改变而改变。

因此，其实最好的办法是在Excel里面插进计算字段。

05  透视表排序与筛选

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

降序摆列其实比较好办，只需要选中金额所在的地区，右键选取降序摆列就可。

倘若存在多个字段的状况下，怎么筛选出老板想要的呢？直接运用行标签去筛选是行不通的，处理办法是：将鼠标点在列标签外面一格，而后运用筛选功能，详细操作请看下图。

06  行列百分比汇总

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

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

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

总计百分比

当咱们分析各个项目占总值的百分比时，就能够用总计百分比。例如：老板想看营销1部在服装这个制品占全部机构的营销额的多少。

行/列汇总百分比

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

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

行汇总百分比，例如：老板想晓得单个营销在每一个制品的上售卖金额占比是多少，咱们就需拉行汇总百分比给他看。

父级汇总百分比

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

例如：老板说我想晓得王麻子的营销额在她们团队占比是多少。

07  修改行列字段次序

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

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

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

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

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

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

【先做一个辅助表】-【文件】-【选项】-【高级】-【常规】-【编辑自定义列表】-【选取最起始创立的辅助表】-以后就只需要排序就能按咱们期盼的字段次序显现了。

08  刷新与更改数据源

刷新

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

默认是不会自动更新的，需要手动刷新，倘若是在原基本上修改，不增多行列的话，咱们只需要刷新就能够了，倘若有多个数据透视表能够选取所有刷新。倘若害怕自己忘记刷新，亦能够设置【打开文件时刷新数据】

更改数据源

【问】：我的数据源表修改了，刷新了亦无显现我修改的东西怎么办？

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

更高级的用法

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

把数据源把设置成“表格”就能够处理这个问题。

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

并且经过动图能够看到，设置成“表格”后，倘若在首行输入公式都不需要下拉，会自动匹配。

09  透视表的复制与删除

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

复制透视表的状况其实蛮平常的，由于有的时候选择的数据源是相同的，需要做区别维度的汇总归类，倘若不想重新新建sheet，那样复制透视表后在这基本上更改字段是最好的办法。

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

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

中篇：美观与布局

10  透视表的三种布局

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

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

【设计】选项卡-【布局】菜单栏-【报表布局】（重视哟，给透视表穿上漂亮外衣的大都数功能都是在【设计】选项卡实现，这儿亦是咱们今天的重点阵地，大众能够自己科研科研这个选项卡的内容噢!）

压缩形式

有眼尖的朋友已然发掘啦，其实压缩形式便是咱们Excel默认的透视表格式，它重点的特点呢便是：

无论叠加多少个行字段，都只占一列。倘若对这个概念还不是尤其明白，能够多和下面讲到的两个布局方式做对比，相信你火速就能明白啦。

大纲形式

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

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

表格形式

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

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

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

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

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

11  表示和隐匿归类汇总

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

嗯，确实是这般，有的时候咱们并不需要看归类汇总，然则透视表会自动表示，有的时候咱们乃至都不需要看总计，那样怎么隐匿和表示归类汇总和总计呢？还是在【设计】选项中哦！

看完动图大众应该比较清楚在哪里表示和隐匿归类汇总和总计了吧？

不外细心的朋友应该已然发掘动图里一个小问题了吧？在这儿小奚要考一考大众哟！

【提问】：为何小奚选取的是在组的顶部表示归类汇总，最后Excel还是在组的底部表示的归类汇总呢？要结合咱们前面讲的内容哦，晓得答案的朋友请大声在留言区告诉我吧！

12  标签项重复表示

【问】：针对大纲形式布局和表格形式布局，由于行字段是并排表示，尤其是前面的行字段，常常一个就对应了后面多个字段，让表格欠好看，怎么优化呢？

哇！这个问题好，在这儿小奚要告诉大众两个优化的办法：第1个是让咱们的标签项重复表示，第二个是合并行标签。

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

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会将除了合并单元格的第1格以外的所有当做空值处理。

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

23 日期违法

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

检测一下源表，瞧瞧是不是存在违法日期？例如：20170101、2017.1.1等。

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

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

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

一样的咱们需要去检测一下数据源表，瞧瞧是不是用文本形式保留的数字呢？

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

那咱们来总结一下，在创建透视表的时候出问题绝大都数状况下需要咱们去检测源表，瞧瞧是不是：某些列无标题、存在合并单元格、违法日期以及文本类型存储的数字。

学完本文，几乎就能处理Excel透视表绝大都数的问题啦！

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

看十遍不如练一遍，后台回复数据透视表 可得到本文案例数据源。

倘若你觉得本文对你有所帮忙，请尽情保藏或转发吧！欢迎转载，欢迎后台撩我。

END