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

天天都在跟Excel打交道，你的效率有多高？那些Excel里提升效率的神器，你会用多少？

说到提升Excel效率的神器，在小奚心中，神器榜排名第一的非数据透视表莫属了。

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

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

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

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

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

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

02 透视表转普通表格

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

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

03 更改值字段计算方式

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

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

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

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

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

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

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

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

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

05 透视表排序与筛选

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

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

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

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

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

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

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

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

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

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

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

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

07 修改行列字段顺序

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

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

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

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

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

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

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

08 刷新与更改数据源

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

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

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

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

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

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

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

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

09 透视表的复制与删除

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

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

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

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

10 透视表的三种布局

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

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

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

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

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

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

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

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

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

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

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

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

11 显示和隐藏分类汇总

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

16 刷新后格式保持不变

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

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

当A1为空值时A2为1，怎么让他空值时不显示？

我是了，显示 超出范围值

本回答被提问者和网友采纳

我们日常学习和工作中都会或多或少接触到数据，这些数据相对比较简单，不需要使用复杂的分析工具来处理，使用的最多的是Excel表格。

我们使用复制和粘贴功能是最多的，但Excel表格还有更多更好的功能等待着被挖掘，今天我们为大家精选了43个操作小技巧，提高你的数据分析效率。

1、多个工作表同时录入

对于需要输入多个表格中一些相同内容，利用Ctrl键就可以轻松实现。

首先按住Ctrl键利用鼠标选择工作表，然后输入内容，按回车键即可。

2、快速复制上一行内容

有时我们需要输入一些相同的数据，一次次输入，肯定是不合理的，所以这里利用Ctrl+D即可快速完成。

有时我们只想复制可见单元格，但是你会发现被隐藏的单元格也会被复制，那该怎么避免呢？首先按住Alt+分号，然后正常复制即可。

4、在指定区域输入相同内容

利用Ctrl+G，在对话框中输入区间，然后输入内容，最后按住Enter+Ctrl即可

同样这里也是利用定位功能以及字体颜色来是实现。

在Excel中我们是可以利用数据的有效性来设置范围，比如说帮主在姓名一列中设置只能是输入三个字符。

有时我们需要对日期转换为星期几，我们可以通过输入公式“=TEXT(A1，"AAA")”来实现。

8、如何防止重复数据的录入

选择数据——数据验证，打开相应的对话框，然后在自定义中输入公式COUNTIF(A:A,A1)=1，点击确定即可。

9、为什么没有绿帽子，但还是不能求和？

这里利用选择性粘贴的方法来处理，首先复制空单元格——选择性粘贴——粘贴运算：加，这样就可以将文本型数字更改为数值，最后再进行求和即可

10、如何快速拆分姓名和电话号码

用函数公式来实现。姓名可以直接使用公式：

电话号码可以使用公式：

11、换行符怎么批量删除

按Ctrl+H打开查找和替换对话框，在查找中输入Ctrl+J（换行符），替换中不输入任何内容，最后点击替换即可。

利用Ctrl+J换行符，即可快速换行。首先选中区域，之后按Ctrl+H打开查找替换框，在查找中输入需要查找的内容，在替换中输入Ctrl+J，点击全部替换即可。

13、文本公式怎么计算

这里我们需要借助名称定义来实现，首先点击公式——定义的名称——定义名称，在相应的对话框中输入内容，输入名称“计算”，在引用中输入公式=Evaluate()函数确定，之后在计算单元格输入=计算即可。

推荐一个容错函数IFERROR，只要在公式前面添加这个函数，就可以轻松解决。

IFERROR语法（检查是否存在错误的参数，返回的结果）

首先在序号列中输入公式：

公式解析：COUNTA($B$2:B2)表示计算区域内的非空单元个数

IF(B2="","",COUNTA($B$2:B2))表示判断B2单元格是否为空值，若是为空值，否则进行单元格个数统计。然后只要右侧单元格中输入内容即可自动生成编号。

首先我们添加自定义排序的内容，点击【文件】→【选项】→【高级】→【编辑自定义列表】，然后选中职务，选择【数据】→【排序】即可。

首先添加辅助列，然后再输入自然数同时在后面辅助一列相同数据，之后对数据进行排序，最后利用定位功能，定位空值辅助粘贴表头即可。

18、录入数据时遇到不会读音的字怎么办？

这里推荐一个好方法，就是利用U+部首拼音来录入。比如说我要录入“耄耋”，“倥偬”

19、删除表格里中的虚线

在打印预览或者设置页面之后，我们会发现表格中增加了一些虚线，那这些虚线怎么删除呢？我们可以直接选择“文件”-“选项”-“高级”，将“显示分页符”的勾去掉即可

20、删除Excel中部分图片

我们知道利用定位对象功能，我们可以快速定位表格中所有图片，然后按住Delete键删除所有图片，那如果只想删除部分呢？该怎么办？

首先利用定位功能选中所有图片，然后右击选择“大小和属性”，在“属性”下勾选“随单元格改变大小和位置”，最后直接删除单元格即可。

21、首行和首列同时固定不动

想让首行和首列同时不动，我们可以将鼠标放在第二行和第二列的交叉单元格，然后点击“视图”-“冻结窗口”-“冻结拆分窗格”。

22、大写阿拉伯数字如何输入

做会计的，经常会遇到输入大写阿拉伯数字，那如何快速输入呢？

这里帮主推荐两种方式：第一种：利用函数NUMBERSTRING直接转换；第二种：利用搜狗

23、快速关闭所有打开的Excel文件

按住Shift键不放，然后直接关闭窗口即可。

注意：如果没有保存，则会跳出是否保存的对话框哦。

数据之间的调整也是可以利用Shift键与鼠标左键来实现。

电话分段显示可以直接右击“设置单元格格式”，然后在自定义中输入“000-”点击确认即可快速分段

在Excel中，我们可以利用“条件格式”下的“重复值”快速标识。

27、快速提取身份日期

假设我们已经录入好身份证号码，这里我们可以选择数据，然后点击数据下“分列”，进入相应对话框，其次利用分列线，将日期框选，最后将数据改为“日期”格式即可。

如果我们想要了解每行数据的图表走向，利用迷你图则是最合适的方法。

首先选中数据，在快速分析中选择“迷你图”，然后选择一个图表即可。

学会跨列居中这个小技巧，再也不用担心合并单元格带来的问题了。

30、数据批量添加前缀

不需要用键盘一个个敲上去，一步即可搞定！

想要把休息日排除掉，非常简单！

直接按【Alt+F1】，一键生成图表。

现在要在这份清单里，把相同的部门都合并起来，最简单的，就是用鼠标一个一个选择合并、选择合并。这也是最麻烦、最耗时间的做法，如果数据有上千行，一下午就这么废了。

其实使用数据透视表，10 秒钟就可以搞定一下午的事情，点击几下鼠标就可以轻松搞定。

34、自动批量创建表格

公司里的费用报表，要按照月份创建多个工作表，记录每个月的费用。每次都要一个一个新建工作表，还得为每一个工作表修改名称，麻烦！相当麻烦！

拆分工作表这种工作，交给数据透视表，还是点击几下鼠标，搞定！

35、按照季度分段统计

每个月最后那几天最让人讨厌，又要做月报统计，又要做季度统计，特别是季度，要一个季度一个季度地筛选、统计、记录，超级繁琐。

如果你会数据透视表，月报、季度报、年报只是点击几下鼠标的事情。

36、灵活变换统计口径

做数据统计最讨厌的就是，你刚刚费了九牛二虎之力，把数据统计好。领导说要按照 A 方法统计，过会儿又说按照 B 方法统计，改来改去。

你都想象不到，用数据透视表应对这种需求，有多方便、多灵活、鼠标拖一拖就好了。

37、轻松制作动态图表

动态图表看上去非常直观、生动，但是一看到那些公式、控件什么的，心里就发毛。

使用数据透视表+切片器，再创建一个图表，轻轻松松实现动态图表。

38、提取姓名、手机号

想拆分姓名和手机号码？先输入第一个姓名，选取它和下面的空行，按Ctrl+E，一秒搞定拆分（提取手机号码同样方法）

39、恢复E+的数字格式显示

数字长度如果大于11位会以科学记数法显示，设置自定义格式 0 可以让数字正常显示出来。

按CTRL键分两段选取并复制，选取要粘贴的位置，按Enter即完成转换

不规范的数字和字串，用分列可以快速转换成标准的日期格式

42、快速合并多行数据

把列拉宽 - 选取数据 ， 开始 - 填充 - 两端对齐（Excel2003是内容重排）

43、快速跨表复制粘贴

按shift选取多个工作表，选取工复制的表格区域，开始 - 填充 - 成组工作表。