热线电话：13121318867

登录
首页精彩阅读Excel 数据透视表，10 个不得不说的秘密
Excel 数据透视表，10 个不得不说的秘密
2017-03-15
收藏

这次，罂粟姐姐悄悄告诉你们 Excel 数据透视表的 10 个秘密。当然，如果有 1 个及以上的秘密是你之前不知道的，就果断打赏转发吧。（如果你全知道，呃……大神求带~）

1、手动刷新和定时刷新
在用透视表分析数据时，如果数据源（原始数据）发生变化，不需要重新生成数据透视表，可以手动刷新，保证透视表的结果与数据源保持一致。

方法1：在透视表中的任意单元格上单击鼠标右键，点击【刷新】，刷新一个透视表。

方法2：在【数据】选项卡中单击【全部刷新】，刷新工作簿内的所有数据透视表。

手动刷新

如果数据源记录的数据信息变化较为频繁，还可以设置数据透视表自动更新。

方法1：打开工作簿时自动刷新，在透视表中的任意单元格上单击鼠标右键，点击【数据透视表选项】，单击【数据】选项卡，勾选【打开文件时刷新数据】，单击【确定】。

打开工作簿时自动刷新

方法2：如果数据透视表的数据源为外部数据，可以设定固定时间间隔来刷新数据透视表。选中数据透视表中任意单元格，【数据透视表工具】-【分析】-【更改数据源】-【连接属性】-【使用状况】-【允许后台刷新】、【刷新频率】-设置刷新频率-【确定】。

固定时间间隔刷新

当然，还可以通过使用VBA代码刷新。

注意：在原始数据中增加记录时，数据透视表直接刷新不出来，对这一内容感兴趣的小伙伴可以关注姐姐接下来的教程哦。


2、快速统计重复项目

罂粟姐姐很久很久之前介绍过四种方法筛选重复数据，包括高级筛选法、条件格式法、函数法以及数据透视表法。这次温故知新，用数据透视表法统计各部门人数。

快速统计重复项目


3、合并对齐，透视表美化

在数据透视表中，文本格式会默认为左对齐，数值格式的默认为右对齐，有重复项目时会在第一行或第一列显示，其他行或列为空白单元格，为了是数据透视表更美观，更具可读性，需要数据透视表以表格形式显示，并且合并居中。

【设计】-【报表布局】-【以表格形式显示】，在数据透视表中的任意单元格单击鼠标右键，【数据透视表选项】-【布局和格式】-【合并且居中排列带标签的单元格】-【确定】。

透视表美化


4、自定义排序

数据透视表创建完以后，可以使用Excel中的“排序”功能对透视表中的某个字段进行排序。

Excel中汉字的排序通常是依据汉语拼音的顺序，可以升序，也可以降序，如果需要对关键字进行特殊要求的排序，则需要使用自定义排序。

【文件】-【选项】-【高级】-【常规】-【编辑自定义列表】-【自定义序列】-【从单元格中导入序列】-【导入】-【添加】-【确定】，右键单击数据透视表，【排序】-【其他排序选项】-【其他选项】-去掉【每日更新报表时自动排序】-下拉【主关键字排序次序】-选择导入的要求的顺序-【确定】-【升序】或【降序】

自定义排序


5、新增“计算字段”

现有单价、销量、成本等字段，如需利润字段的话，可以直接插入“计算字段”来做利润的统计分析。

【数据透视表工具】-【分析】-【字段、项目和集】-【计算字段】-输入字段名称-输入公式“=数量*单价-成本”。

新增“计算字段”


6、对日期进行组合

一般情况下，数据表中的日期会以日期顺序进行排列，这种排列对于分析某个时段的数据并不理想，不能直接看出不同时段之间的变化趋势，通过组合日期的方法，将日期按年、月或者季度等时段进行重新排列，可以让结果更加直观。（2016版可以自动进行月度汇总）

数据透视表第一个日期单元格，单击右键-【创建组】-【分组】-【自动】-【起始于】-【终止于】-【步长】。

对日期进行组合


7、强大的“值显示方式”

“值显示方式”可以快速对数据进行分析。2016版数据透视表内有14种显示方式，其中各种形式的百分比，为我们最常使用的功能。

例如：已知各月各地市销售总量，需要计算两个数据，一是每月各地市销量占比，二是各月在全年的销量占比。

增加【数量】值字段-单击第一个单元格右键-【值显示方式】-【父级行汇总百分比】，结果即为各月份各地市所占百分比，加粗的数据为各月在全年所占的百分比。

值显示方式


8、拆分多个工作表

在工作中，我们通常会遇到需要拆分数据的情况，数据透视表在一定条件下可以作为拆分数据的首选工具。（VBA拆分法会在之后的教程中跟大家分享。）

案例：需要把北京、上海、深圳、武汉、郑州5个城市的各个区域的总销量拆分为不同的工作表。

【数据透视表工具】-【分析】-【选项】-【显示报表筛选页】-筛选字段【一级区域】-【确定】

拆分多个工作表


9、利用切片器实现数据快速筛选

Excel2010以上版本新增“切片器”功能，与传统点选下拉选项筛选不同的是，通过切片器可以更加快速直观地实现对数据的筛选操作。

【数据透视表工具】-【分析】-【插入切片器】-选择筛选字段。

切片器


10、智能化的数据透视图

数据透视图可认为是数据透视表的图表化，通过不同的字段筛选来实现多维度的数据计算与展示。在做Excel数据图表时，使用数据透视图非常方便。

这2个图鸟叔和罂粟录了10遍才上传成功
后来才发现Gif超时导致上传失败

10个小秘密都知道的大神，求带~~~


作者 罂粟姐姐
本文为罂粟姐姐原创作品，转载需授权



CDA数据分析师考试相关入口一览（建议收藏）：

▷ 想报名CDA认证考试，点击>>>   “CDA报名” 了解CDA考试详情；

▷ 想加入CDA考试题库，点击>>> “CDA题库” 了解CDA考试详情；

▷ 想学习CDA考试教材，点击>>> “CDA教材” 了解CDA考试详情；

▷ 想查询CDA考试成绩，点击>>> “CDA成绩” 了解CDA考试详情；

▷ 想了解CDA考试含金量，点击>>> “CDA含金量” 了解CDA考试详情；

▷ 想获取CDA考试时间/费用/条件/大纲/通过率，点击 >>>“CDA考试官网” 了解CDA考试详情；

透视表 数据透视 数据透视表 字段 统计分析

数据分析咨询请扫描二维码

上一篇图论在大数据分析中的作用！
下一篇CDA认证再升一档！与国家共同推进大数据人才培养标准教育事业！
CDA考试动态
CDA报考指南
热门栏目
最新资讯
更多
Copyright © 2015-present 北京国富如荷网络科技有限公司 All Rights Reserved.
京ICP备11001960号-9   京公网安备 11010802034615号   经营许可证编号：京B2-20210330
客服在线
立即咨询