返回目录:excel表格制作
数据分析的工具,Excel是最通用普遍使用的工具;熟练地使用Excel中的数据透视表,则是每个职场人士必须具备的基本能力!
虽然透视表的入门非常简单,但很可惜的是,仍有不少的同学未能高效地使用,本文将分享数据透视表相关的10大效率操作,助你养成良好的使用习惯,高效地玩透视表~
1、数据源:使用“表”而不是普通单元格区域
生成透视表前,习惯要把数据源转换成【表】的形式:
① 点击数据源中的任意一个单元格
② 按下快捷键「CTRL+T」(请必须确保你的数据是有表头的)
③ 点下确定,就成功把数据源转成【表】了
为什么要用表---表格有自动扩展的特性,当你增加数据时,不需要再修改透视表的数据源;再深入的用法就是你可以用PQ或SQL连接外部的数据进来,实现全自动化的报表。
2、把日期字段分组成年、季、月等
分析数据时,经常要看年、季、月各种时间维度的数据。不需要在数据源中增加多余的列,只需要使用分组功能就ok,非常高效!
① 把日期字段拉到行或列区域中
② 右键点击透视表中任意一个日期,选择「分组」
③ 选择你要组合的日期维度,点确定,就实现了!
3、行/列字段的手动排序
有时我们想要一个特定的顺序排列,为了实现这个目的,你可以选择手工拖动进行重新排序,这个在行字段、列字段都是一样的操作方法。
① 选择行/列字段里的某一项,光标停留在该项的边框上,直到出现移动的图标(有4个方向的箭头)
② 按住鼠标左键把该项拖动到你期望的位置,就实现了拖动排序
4、不要直接更改单元格区域的数字格式
透视表的报表,我们其实经常要改指标的数字格式的,例如把数字的单位改成「万」。但很多人的操作不够规范,下图是错误示范:
正确的操作应该是如下图所示:
更改值字段的数字格式,操作步骤:
① 右键值字段的任意一个数字
② 选择【数字格式】
③ 设置你要的数字格式
这样做有什么好处:
→ 改变的是透视表字段的格式,而非单元格的格式
→ 如果你要换一个指标时,不需要重新调整单元格格式,行数变化也自适应
→ 选整列调整单元格格式会影响了列所有的单元格
5、设置条件格式要正确选择作用区域
条件格式,在表格可视中可以帮助我们更好地看数据,所以透视表配合条件格式+自定义格式是非常好的展示效果。
第4点我们介绍过自定义格式需要设置值字段,而不要选择单元格格式。那么在透视表中如果要设置条件格式,同样是不要选择单元格或整列去设置。正确的姿势是:
从上图可以看到,即使我更换了行字段,条件格式仍然存在,并会自动适应行数。
① 选中值字段的任意一个单元格,设置好条件格式
② 然后在该单元格右侧会有一个智能标记提示,点开它
③ 所选单元格→就是仅指当前选定的单元格
所有显示为该值字段的单元格→就是指设置这个值字段有条件格式(行数会自适应)
所有显示为“品牌”的值字段→就是指仅为品牌的值字段设置条件格式,切换成其他筛选字段就没了条件格式
6、双击下钻看明细
当你想看到某一项的明细时,你可以双击对应的项/值,去显示下层的数据或者明细的数据
双击有2种方式:
① 双击行列字段的项,可以选择下钻的字段
② 双击某项的值,会生成新的Sheet显示明细
7、取消GETPIVOTDATA
当透视表本身的功能不足以实现需求时,我们还需要借助公式去配合处理。但是当我们公式参数选择透视表的值时,经常会出现GETPIVOTDATA这个烦人的东西~
GETPIVOTDATA其实是可以帮我们动态获取透视表的指定数据,但一般用得很少,所以取消它没有什么不良影响。
8、取消自动调整列宽
每次刷新透视表、切换行列值字段时,列宽都动态在变化?对布局造成各种困扰!所以必须要知道怎么取消这个选项:
操作步骤为:
① 右键透视表任意一个单元格 → 数据透视表选项
② 切换至「布局和格式」的选项中,如上图所示
③ 取消勾选“更新时自动调整列宽”
9、删除数据源,但透视表仍然可以正常运作
当你插入透视表时,Excel会把数据源都放入到工作簿级别的缓存中,然后后续的数据透视表操作是基于缓存去进行的。所以当生成透视表后,再删除你的数据源,其实并不会对透视表有影响。
这有什么用?或者有什么要注意的?
① 脱离数据源,透视表正常运作,可以减少文件的Size,利于分享、打开速度提升
② 你也可以通过上面介绍的双击下钻看到明细数据
③ 注意:需要刷新数据时,将会失败,因为连接不了数据源了。
10、多数据源组合呈现在一个透视表中
你可以把多张表的数据连接在一起,在一个透视表中使用。例如「订单表」、「产品表」、「门店表」等,传统方法是用vlookup整合成一张大表,然后再生成透视表。
但这样的操作太麻烦,运算效率低,数据量大,刷新个vlookup都要几分钟,实在令人受不了!尤其是当你要处理几十万、上百万数据时,用传统的透视表尤如老鼠拉龟!
在Excel 2016以上,开始支持由Power Pivot数据模型中输出为透视表,这时就真的彻底抛弃vlookup了!几百万行的数据量,也能高效的运算,灵活的指标构造语言,还支持导入到power bi中使用!
想学习用Excel的Power Pivot模型输出透视表报表的朋友,可以趁着现在有特别活动,加入《数说》会员学习Excel的BI入门,只要你有透视表的经验,就能实现入门,学习Excel全自动报表的新逻辑!
除了Excel,《数说》还有业务分析思维,分析理论,Excel分析实操等内容,非常丰富!由于《数说》内容的丰富、实用、通用,《数说》是全网中屈指可数的数据分析综合优质课程之一!
中秋特惠活动 → 限时7折
你的时间花在哪里,成就就在哪里!