掌握Excel里的这个超级工具,工作效率可以提升80%

作者:jcmp      发布时间:2021-04-22      浏览量:0
在工作中,我们经常用Excel对数据进行

在工作中,我们经常用Excel对数据进行处理,均值、求和、极值等统计,但我发现很多人都没用到Excel自带的工具,它的功能非常强大,虽然达不到万能,但至少可以让你免去那些令人头疼的统计函数烦恼,我们日常遇到的大多数问题都以用它来解决,这个超级工具就是数据透视表。

数据透视表

数据透视表就是对Excel表格中的字段进行快速分类汇总的一个分析工具,它是一种交互式报表,利用它,我们可以调整分类汇总的方式,灵活地以多种不同的方式展示数据。

一张数据透视表仅靠拖动鼠标字段位置,即可变换出各种类型的报表。用户只需要指定所要分析的字段、数据透视表的组织形式,以及要计算的类型(求和、计数、平均)。如果原始数据发生更改,字可以刷新数据透视表来更改结果。

数据透视表除了有机综合了 数据排序、筛选、分类汇总 等数据处理分析功能以外,它还解决了 函数公式速度瓶颈 的问题。下面列出一些数据透视表相关的数据,便于大家进一步了解这个工具。

对数据透视表的作用有了初步了解之后,为了让大家能更快熟悉以及使用,接下来就结合案例给大家讲解。

数据透视表分析实践

上图是2016年某公司文具销量明细,从此表中我们要通过使用数据透视表来解决下面这个几个问题:

1、2016年总销量是多少?总销售额是多少?

2、2016年A、B、C三个地区的销量及销售额各式多少?

3、2016年哪种产品销量最好?哪种产品销量最差?

4、2016年各业务员中谁的销售额最好?谁的销售额最差?

5、2016年公司哪个月的销售额最好?哪个月的销售额最差?

6、2016年B地区业务员王五的钢笔销量是多少?

在解决上面的问题之前,我们需要创建一个数据透视表。

step 01 选中数据源位置,单击“插入”选项卡,在选项卡“表格”功能中组中,单击数据透视表,在弹出的“创建数据透视表”对话框“选择一个表或区域”,如下图所示。

step 02 选择放置数据透视表的位置,继续在“创建数据表”对话框“选择放置数据透视表的位置”中选中“新工作表”。如下图所示:

到这里空白的数据透视表就建好了:

接下来我们回答第一个问题:2016年总销量是多少?总销售额是多少? 将“销量”“销售额”拖至数值汇总区域,把“销量”“销售额”汇总方式在它们各自的“值字段设置”功能设置为为求和,如下图所示:

所以2016年总销量为12146个,总销售额为227975元。

继续第二个问题:2016年A、B、C三个地区的销量及销售额各式多少? 只需要在数据透视表中增加一个“地区”维度,也就是将“地区”字段拖至行标签区域,所以答案如下图所示:

第3个问题是:2016年哪种产品销量最好?哪种产品销量最差? 将“销量”拖至数值汇总区域,把“销量”字段汇总方式在“值字段设置”功能中设置为求和,将“品名”拖至行或列标签区域,计算结果都一样,只是布局略微不同,这里我就选拖至行标签区域吧,结果如下图所示,2016年订书机的销量最好,笔记本最差。

第4个问题:2016年各业务员中谁的销售额最好?谁的销售额最差? 将“销售额”拖至数值汇总区域,把“销售额”字段汇总方式在“值字段设置”功能汇总设置为求和,将“业务员”拖至行标签区域,结果如下图所示,2016年业务员周六的业绩最好,业务员张三的业绩最差。

由于原数据中没有直接给出月份信息,需要信件一个月份字段,可以利用MONTH函数根据“日期”字段计算出相应的月份值,如下图:

这个时候在根据增加月份的新表创建创建空白数据透视表,将“销售额”拖至数值汇总区域,把“销售额”字段汇总的方式在“值字段设置”功能中设置为求和,将“月份”字段拖至行标签区域,如下图所示,2016年公司5月份业绩最好,7月的业绩最差。

最后一个问题:2016年B地区业务员王五的钢笔销量是多少? 将“销量”拖至数值汇总区域,并设置为求和,然后再把“地区”“业务员”“品名”拖至》“报表筛选”区域,如下图所示:

在B1单元格“地区”右侧的下拉菜单中选择“B”地区,单击“确定”按钮,如下图所示:

用同样的方法在“业务员”项中选择“王五”,在“品名”项中选择钢笔,最后结果就是2016年B地区王五的销量为50支。

以上就是对数据透视表的基本应用,如果还要对数据做进行一步分析,还需要使用一些小技巧,下面将从百分比计算、同比环比计算统计这两方面讲解。

数据透视表小技巧

百分比计算

继续结合上面的列子,刚刚我们已经计算出2016年A、B、C三地区的销售额,下面我们进一步了解三个地区的销售额占比数据,也就是哪个地区贡献最大,贡献了多少份额。

step 01 继续将“销售额”字段拖至数值汇总区,将其汇总方式设置为求和。

step 02 用鼠标点击刚得到的“销售额”求和数据范围内的任一单元格,单击右键,选择“值字段设置”——“列汇总的百分比”,将标题命名为“百分比”,即可得到下图所示:

因为这个列子数据有限,所以就介绍环比,但是两者的计算原理都是一样的。

环比计算

以2016年每月的销售额计算为例,计算每个月的环比数据,也就是连续两个月之间的比较。

step01 继续将“销售额”字段拖至数值汇总区域,将其汇总方式设置为求和。

step02 用鼠标点击销售额数据范围的任一单元格,单击右键,选择“值显示方式”——“差异百分比”。

step03 在弹出的值字段设置对话框中,设置要计算差异百分比的基本字段、基本项。本例中基本字段选择“月份”,基本项选择(上一个),也就是环比的意思,单击确定,结果如下。

如果有两年以上的数据,并且有年份的字段,可以将年份和月份字段拖至数据透视表行标签,在值字段设置对话框的基本字段中,将出现“年份”和“月份”两个字段,选择“年份”即可计算每月的同比数据。

好啦,数据透视表的小技巧就分享到这里,是不是觉得很实用呢。其实数据透视表还有很多功能,由于时间有限就不在这里深入介绍了,感兴趣的小伙伴可以自行挖掘。

对于数据透视表的使用可能一开始用的时候会慢一些,但是熟悉之后,这个工具和技巧都可以让你在工作中效率倍增,赶快用起来吧。