北美都市脈搏北美都市脈搏北美都市脈搏

统计分析怎么做?Excel 数据透视表 + PQ,功能太强大

最近会员客户的投诉有点多,老板想知道,到底客户是对哪些方面不满意。

于是让同事小李打电话给会员客户,进行满意度调查。

小李干活非常麻利,思路也非常清晰,迅速制作了一份满意度调查的表格:

统计分析怎么做?Excel 数据透视表 + PQ,功能太强大

经过一天的努力,小李打完了 50 个电话,完成了满意度调查。收集到的数据是这样子的:

统计分析怎么做?Excel 数据透视表 + PQ,功能太强大

当然,小李心里清楚,上面这个表是不能直接给老板看的!

还需要对每一个问题(就是每一列)的答案进行统计,得到下面这种格式的调查结果。

统计分析怎么做?Excel 数据透视表 + PQ,功能太强大

这怎么能难倒机智的小李呢~

小李马上开始用逐条筛选、然后计数的方式,进行统计,具体做法是这样子:

❶ 根据性别,筛选男性有多少,女性有多少。

❷ 把结果填到「统计结果」中。

❸ 每个满意度调查项目,都要重复上面 1、2 的操作。

统计分析怎么做?Excel 数据透视表 + PQ,功能太强大

▲ 头都要大了!

做到第 2 个调查项目的时候就觉得不对劲了。

想想还要对那么多列的答案进行计数,小李觉得一定有快速简单的方法,于是跑来问我。

01 需求

我先分析了下小李的需求,大概可以分成两个部分:

❶ 统计每个问题的答案数量,以及比例。

❷ 重复第 1 步,统计每个问题。

02 解决办法

小李所用的方法非常繁琐。

其实,我们可以直接用透视表,按照以下步骤,拖动一下,就可以快速统计出来。

❶ 全选表格,选择【插入】选项卡-【数据透视表】-【确定】。

❷ 将【性别】字段放进行和值,因为性别是文本字段,所以会直接进行计数。

统计分析怎么做?Excel 数据透视表 + PQ,功能太强大

只花了不到 20s 就完成了性别的统计。

接下来复制数据透视表,选择其他字段进行统计即可。

统计分析怎么做?Excel 数据透视表 + PQ,功能太强大

这样确实比筛选快了~

但是,现在只有 7 个调查项目,我们可以做 7 个数据透视表。

如果调查项目有几十个怎么办?

高效的方式仍然是使用数据透视表!

但需要把数据转换为标准的格式 —— 一维表。

知识点插播:

一维表指 1 行是 1 条数据,1 列是 1 个属性的表格。

我们先看看小李的表格,7 个调查项目(C 列到 I 列)其实是同一个属性,都是调查项目。

这种多列属于相同属性的表格,叫做二维表。

统计分析怎么做?Excel 数据透视表 + PQ,功能太强大

为了把二维表转化为一维表,需要给数据来个「降维打击」,把 7 个调查项目列转换为 2 列(属性列、值列)。

这个降维的过程是一个逆透视列的过程。

知识点插播:

逆透视列,将选中的 N 列生成属性列和值列,属性列的值是原来 N 列的标题(图示绿色部分),值列是原来 N 列的值(图示橙色部分)。

下图左边的表格是二维表,右边的表格是一维表。

统计分析怎么做?Excel 数据透视表 + PQ,功能太强大

OK,知识点了解后,具体要怎么执行这个「降维打击」呢?

这个时候就要用到一个非常好用的工具,PowerQuery。

▋步骤 1:将数据导入到 PowerQuery

❶ 全选数据,点击【数据】选项卡-【获取和转换数据】-【自表格 / 区域】。

❷ 在弹出的对话框中,选择表包含标题,确定。

统计分析怎么做?Excel 数据透视表 + PQ,功能太强大

▋步骤 2:对数据进行逆透视

❶【Ctrl+A】全中所有列。

❷ 选择【转换】选项卡-逆透视列。

统计分析怎么做?Excel 数据透视表 + PQ,功能太强大

▋步骤 3:筛选,去掉不要的列

因为会员号和姓名不是要统计的问题,通过筛选功能将他们去掉。

统计分析怎么做?Excel 数据透视表 + PQ,功能太强大

▋步骤 4:关闭并上载

点击【文件】-【关闭并上载】,会在 Excel 中生成新的工作表保存结果。

统计分析怎么做?Excel 数据透视表 + PQ,功能太强大

▋步骤 5:插入数据透视表

❶ 插入数据透视表,依次将【属性】、【值 2】都放入行中;将【值 2】放进值(值 2 是文本,可以直接计次),放两次;

❷ 在数据透视表的【计数项:值 2_2】列的单元格上右键-值显示方式-父级汇总的百分比-基本字段选属性,最后点击【确定】。

❸ 按自己需求美化下表格。

❹ 知识点插播父级汇总的百分比:

= 某项的值(如 36-45 岁)的合计数 / 所选「基本字段」的值(属性字段的年龄)的合计数。

统计分析怎么做?Excel 数据透视表 + PQ,功能太强大

大功告成啦~

统计分析怎么做?Excel 数据透视表 + PQ,功能太强大

这样就算再来几十个问题,都可以轻松搞定!

而且用 PowerQuery 和数据透视表一样具有同步更新的功能,这样下次在进行满意度调查,只要刷新一下就可以更新统计结果,一劳永逸。

03 写在最后

今天的案例其实很简单,我是这样思考的:

重复性的操作一定有可以简化的方法。

❶ 使用 PowerQuery 进行逆透视,对数据源进行整理;

❷ 数据透视表和 PowerQuery 强强联合,1+1>2;

❸ 在一开始只用数据透视表的时候,没有什么太好的思路进行高效的整理。但是 PowerQuery 的加入,让整个统计过程效率明显提升。

推荐你也尝试一下 PowerQuery,你会推开新世界的大门。

本文来自微信公众号:秋叶 Excel (ID:excel100),作者:绿水零 编辑:竺兰

未经允许不得转载:北美都市脈搏 » 统计分析怎么做?Excel 数据透视表 + PQ,功能太强大