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

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

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

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

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

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

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

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

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

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

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

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

▲ 头都要大了！

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

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

01 需求

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

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

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

02 解决办法

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

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

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

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

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

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

这样确实比筛选快了~

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

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

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

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

知识点插播：

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

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

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

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

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

知识点插播：

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

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

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

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

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

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

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

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

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

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

▋步骤 3：筛选，去掉不要的列

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

▋步骤 4：关闭并上载

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

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

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

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

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

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

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

大功告成啦~

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

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

03 写在最后

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

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

❶ 使用 PowerQuery 进行逆透视，对数据源进行整理；

❷ 数据透视表和 PowerQuery 强强联合，1+1＞2；

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

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

本文来自微信公众号：秋叶 Excel （ID：excel100），作者：绿水零 编辑：竺兰

广告声明：文内含有的对外跳转链接（包括不限于超链接、二维码、口令等形式），用于传递更多信息，节省甄选时间，结果仅供参考，IT之家所有文章均包含本声明。