提到数据分析,就必然离不开 Excel 中的「数据透视表」。如果之前没有用过这个功能,单看「透视表」这个名字,多少会觉得有点不知所云。

我们不妨回到数据分析的源头来想一想,究竟数据分析的核心本质是什么呢?在我看来,有两点:

  • 定义指标:当我们拿到一堆数据时,是茫然无措的。要进行数据分析,就必须先要有假设和目标:数据分析是为了什么,希望得出什么样的结论。那么,我们就必须要定义指标。例如,要考量一个公司的盈利情况,光有收入和成本的数据是不够的,我们还希望进一步地看到利润额和利润率。
  • 从微观到宏观:大量的数据在没有整合之前,颗粒度往往是非常细节的。许多数据的使用者可能是管理层或者决策者,对他们来说,过于细节的数据毫无意义,他们想看到的是整体宏观的情况。例如,便利店中每一笔成交订单的利润率,对于管理决策者来说过于细节,他们想看到的是某一个周期,或某一个区域内,整体的利润率水平。

而「数据透视表」,恰恰就能做到这两点:对数据进行合并同类项,并进行运算和展示。在这篇文章中,我们就来看看它的各类应用场景,下文中的案例都会体现在 这张 Excel 表格 中 ,你可以下载后对照着阅读,效果更佳。

初识数据透视表

作为全国的销售主管,你现在手头拿到了 2020 年的销售数据,你想了解一下去年全年的产品满意度,而这份原始数据是按照区域、门店类型、产品、月份的详细历史数据,一共有近 600 行。

G 列展示了满意度

这时候,你选中整个 G 列,Excel 底部会显示所有满意度数据的平均值为 84%。那么接下来,如果你想进一步了解按区域划分的满意度,按门店类型划分的满意度,按产品线划分的满意度,该怎么办呢?

的确,Excel 中有许多公式都可以帮助你计算得出这些数字,但是最简单的方法,还是「数据透视表」,只需要简单的拖拽,就能马上生成想看的数据报告。

以按区域划分的满意度为例,只需要在「插入」选项卡中点击「数据透视表」,确认创建后,将「区域」字段拖入「行」,「满意度」字段拖入「值」,并修改为展示「平均值」,这样,就马上能得到按区域的满意度统计了。可以看到,全国的满意度平均值为 84,主要是华东区域拖了后腿,满意度只有 74%。

使用透视表生成按区域的满意度统计

基于同样的道理,你也可以快速按门店类型或产品线生成满意度统计。本质上,「数据透视表」就是一个同类项合并工具,可以快速把同一类数据进行归并,且按求和、计数、平均值等方式计算结果。

透视表的使用方式与应用场景

理解了数据透视表最核心的本质后,接下来我们就看看它的各种常见应用场景。

一维数据的二维化展示

数据透视表字段中可以设置「列」和「行」,从而快速将一维数据生成二维化的展示。例如,对于收入这个指标来说,我们既关心按区域的销售情况,也关心不同产品线的销售情况,这时候可以分别将「区域」和「产品」拖入「行」和「列」中,从而快速生成一个区域-产品的收入表格,可以方便地看到 A/B/C 三个产品各自的销量汇总,以及按四个区域的销量汇总。

按区域 - 产品展示的收入

添加个性化的指标

除了原始表格中包含的字段可以放入「值」中进行计算,你还可以在透视表中自定义指标。例如,我们关注的不仅仅是收入和成本,更重要的是产品的利润率。那么,可以利用已有的数值,使用「计算字段」功能,自定义一个指标:利润率 = (收入-成本) / 收入。

自定义的利润率指标

按分组进一步合并同类项

透视表功能除了针对原始数据中的字段进行合并同类项之外,还能额外针对时间和数值型的字段,提供自由度更高的合并同类项功能。这是什么意思呢?

举例来说,原始数据中的收入和成本是按照月份统计的,然而作为管理者,可能不需要看到月度这么细的数据,如果我只想按照季度查看收入,该怎么办呢?

这时候就可以使用透视表的「分组选择」功能。如下方所展示的,当选中月份区域,「活动字段」展示为「月份」时,点击「分组选择」菜单,在弹出的对话框中,可以选择按照「季度」分组,数据的颗粒度,就能从月份变为季度,更加精简了。

按季度汇总展示

注意,除了时间类型的字段外,还有数值型的字段也可以进行分组。例如,我们想看产品的销售收入区间的占比分布,就可以按下图所示操作,在「分组选择」中,将「收入」的开始与结束值分别设为 10000 与 20000,并以每 1000 作为一个统计区间,就可以得到下图所示的占比。

按收入区间汇总

使用切片器制作动态报表

透视表中还有一个「切片器」,这个名字也不是非常直观,实际上,你可以把它理解成一个快速筛选功能。插入切片器后,相当于在透视图页面上增加了几个快速的筛选项,通过点击不同的选项组合,数据透视表和相应的图表数据也会自动更新,从而生成一个简易的动态报表。

在透视表中加入切片器

有了切片器后,透视表就瞬间变成了一个动态报表,点击切片器上不同的选项组合,下面的数据也会联动筛选更新。

使用切片器生成动态报表

快速合并单元格

透视表除了上述用途外,其实还有一个排版方面的小技巧:快速合并单元格。

例如,针对原始表格,想把区域、门店类型、产品等,把相同的值合并单元格,一共有近 600 行,如果纯手工操作,非常麻烦。这时候借用数据透视表,就能快速实现合并单元格的排版,只需要按照如下步骤操作:

第一步:在数据透视表字段中,按原表格中的顺序,依次将字段拉入显示在「行」中;

将字段按顺序,显示在「行」中

第二步:选择「设计」选项卡,依次将「分类汇总」设置为「不显示分类汇总」,以及「报表布局」设置为「以表格形式显示」,并且选择「重复所有项目标签」,得到初步的表格样式;

依次设置「分类汇总」和「报表布局」

第三步:右键透视表数据区域,选择「数据透视表选项」,打开「布局」选项卡,选中「合并且使带标签的单元格居中」;

选中「合并且使带标签的单元格居中」

确认后,你就得到了一个将同类项都合并单元格后的表格了。

合并单元格完成

以上,就是关于数据透视表的几个应用场景与小技巧。