最近会员客户的投诉有点多,老板想知道,到底客户是对哪些方面不满意。
易发资讯网
于是让同事小李打电话给会员客户,进行满意度调查。 218918.cOm
小李干活非常麻利,思路也非常清晰,迅速制作了一份满意度调查的表格: 218918.com
wWW.218918.COM
经过一天的努力,小李打完了 50 个电话,完成了满意度调查。收集到的数据是这样子的: 内容来自易发资讯网
wWW.218918.COM
当然,小李心里清楚,上面这个表是不能直接给老板看的! WWw.218918.cOm
还需要对每一个问题(就是每一列)的答案进行统计,得到下面这种格式的调查结果。 blog.218918.com
这怎么能难倒机智的小李呢~
小李马上开始用逐条筛选、然后计数的方式,进行统计,具体做法是这样子:
❶ 根据性别,筛选男性有多少,女性有多少。
❷ 把结果填到「统计结果」中。
❸ 每个满意度调查项目,都要重复上面 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),作者:绿水零 编辑:竺兰
未经允许不得转载:天下头条网 » 统计分析怎么做?Excel 数据透视表 + PQ,功能太强大