- N +

数据透视表这么好用,不懂你就亏大了!

来自:Skill成长课堂(ID:gh_1de09c9c5828 )

作者:院长大大

刚入职的第一天,老板跟我说,做Excel表格,有两个功能一定要学会,一个是查找引用函数VLOOKUP,另一个是数据透视表。

VLOOKUP函数是表哥表姐的大众情人,查找数据第一时间都会想到她,久而久之,你也应该学会个七八成了,但统计分析的神器——数据透视表,却很多人都没有掌握。

做统计,有人用筛选排序,有人用分类汇总,有人用合并计算,最多的肯定是用函数,COUNTIF、SUMIF用得很精,但效率很慢。

统计字段不会拖,计算值只懂计数,日期没有合并……这么好用的功能,就是因为这些简单的问题,就被放弃了。(泪目……)

今天,就带你重新认识一下数据透视表这个贼好用的功能,不讲道理,只讲人话,看完哪里不懂的,留言提问。

1

源数据表的规范

在建立数据透视表以前,首先要检查源数据表有没有存在下面这几种不规范的情况。

标题行

名称缺失,无法正常建立数据透视表,需要把标题名称补充完整。

数据透视表这么好用,不懂你就亏大了!

名称重复,导致创建数据透视表后,字段名称相同并标注序号,导致语义不明确,需要修改相同名称的标题。

源数据

合并单元格,无法正常使用筛选和排序,合并单元格除了首个单元格有数据以外,其他单元格数值为空,解除合并单元格并补充完整数据。

文本型数字,统计值为零,无法正常参加求和、平均值等数值计算,使用分列功能重新修改为数值。

不规范日期,无法正常使用筛选排序和组合,使用分列功能规范日期格式。

2

插入数据透视表

选中需要做统计分析的源数据表,点击插入选项卡,点击数据透视表,在新工作表或现有工作表中的单元格新建数据透视表。

数据透视表这么好用,不懂你就亏大了!

源数据表中的标题行为数据透视表中的统计字段,用于拖动到不同的区域。

数据透视表的区域分成四个部分

筛选区域,用于对数据透视表整体的筛选

行区域,字段放在行区域,以行的方式展示

列区域,字段放在列区域,以列的方式展示,行区域和列区域,我称呼它为统计维度

值区域,把字段拖动到值区域,实现不同类型的计算,达到数据统计的效果。

数据透视表这么好用,不懂你就亏大了!

那如何正确地拖到字段到数据透视表的区域呢?分析统计的需求,把需求分成三个部分,分别是统计维度,统计值和统计类型。

举个栗子,我要统计出各店面的销售总额。在这里,统计维度是店面,统计值是销售金额,统计类型是求和,可以得到下图的数据透视表。

数据透视表这么好用,不懂你就亏大了!

再举个栗子,我要统计出各经手人的销售单数。统计维度变成了经手人,统计值这里要注意因为一行数据代表一单,这里使用任意一个字段都可以,为避免歧义,这里使用商品名称,统计类型为计数,得到下图的数据透视表。

数据透视表这么好用,不懂你就亏大了!

最后再举个栗子,我要统计出各店面各商品销售金额占销售总额的百分比,统计维度有两个,分别是店面和商品名称,统计值为销售金额,统计维度是求和,值显示方式是总计的百分比。两个条件可同时放在行区域,也可以分别放置在行、列区域,可以得到下图的数据透视表。

数据透视表这么好用,不懂你就亏大了!

数据透视表这么好用,不懂你就亏大了!

3

统计类型的切换

数据透视表最常用的值汇总方式是求和与计数,除此以外,还有平均值、最大值、最小值、乘积等。如果统计字段为文本的话,只能使用计数功能,统计字段为数值的话,可以使用任意的值汇总方式。

点击值区域的计算字段,点击值字段设置,在值汇总方式的页签下按统计需求更改计算类型。

数据透视表这么好用,不懂你就亏大了!

数据透视表这么好用,不懂你就亏大了!

除了值汇总方式以外,还能通过值显示方式页签修改显示方式,一般使用无计算显示方式。按统计需求,可以修改为总计的百分比,列、行汇总的百分比等不同的显示方式。

数据透视表这么好用,不懂你就亏大了!

4

排序筛选与组合

筛选

数据透视表中的筛选可分为标签筛选,就是对区域数值的筛选,值筛选,对统计值字段进行筛选。

我可以通过行标签,筛选出分店,通过列标签,筛选出商品名称,点击下拉三角形,勾选复选框进行筛选。

数据透视表这么好用,不懂你就亏大了!

我也可以通过值筛选,筛选出销售金额大于5000元的分店,或筛选出销售金额大于3500元的商品,分别对行标签、列标签进行值筛选的操作。

点击行标签下拉三角形,选择值筛选,条件为大于5000,即可筛选出广东站站和天河城店。

数据透视表这么好用,不懂你就亏大了!

排序

排序功能除了可以实现简单的升降序以外,还能实现手动排序。

选中行标签,点击鼠标右键,选择移动功能,将该标签进行手动移动,实现手动排序。

数据透视表这么好用,不懂你就亏大了!

组合

当统计维度中包含有日期,可通过组合功能按一定周期把日期组合起来。

选中行标签,点击鼠标右键,选择组合功能,案例中按月进行组合,生成月报表。

数据透视表这么好用,不懂你就亏大了!

对普通文本,直接选中多个文本进行组合,即可实现数据组的建立。

数据透视表这么好用,不懂你就亏大了!


返回列表
上一篇:
下一篇:

发表评论

快捷回复:

    评论列表 (暂无评论,共1145人参与)参与讨论

    还没有评论,来说两句吧...