95后小姐姐因数据混乱被老板骂哭：我怒用这个神器一秒完成数据规范整理
★
编按
★
Hello各位小伙伴，在日常工作中，我们能见到很多混乱、不规范的数据表格。面对这样的数据，我们往往没有办法直接进行汇总统计。这个时候Excel中的无敌数据清洗神器——Power Query就能大展身手啦~
上进青年小张最近又遇到了一个新的Excel汇总统计问题。其实问题听起来也不难：统计公司各部门订午餐的情况。按一般情况来说，以小张的水平，如果数据规范的话，用数据透视表进行统计就是分分钟的事儿。可是，我们想得太简单了——实际工作中的数据，永远具备让人抓狂的“超能力”。
我们先看一下数据，如下图所示。A列是部门，B列是各部门所订午餐的明细，其中包括餐名和数量，餐名和数量之间没任何有分隔符号，但是不同的餐名及相应的数量之间用中文逗号分隔。
扫码入群，下载Excel练习文件
本例中小张遇到的数据，与我们常见的一维数据表长得不一样。对于这样的数据，该如何进行汇总统计呢？别怕！Excel中的无敌数据清洗神器——PQ了解一下！用它对数据进行整理之后，轻松完成统计汇总。
我们先来说一下解决这个问题的思路：先用PQ整理数据，使其符合数据透视表的规范；然后再通过数据透视表，进行汇总统计。
Step1 用PQ整理数据
首先，点击数据区域内的任意一个单元格，例如B3。然后依次点击【数据】－【自表格/区域】。如下图所示：
点击【自表格/区域】之后，弹出“创建表”对话框，如下图所示：
此处，“表数据的来源”已经由Excel为我们判断出来，即“$A$1:$B$9”区域，保持其不变即可；
勾选“表包含标题”；然后点击“确定”，即可进入PQ编辑器的操作界面。如下图所示：
鼠标单击选中“午餐”这一列，然后依次点击【转换】－【拆分列】－【按分隔符】。如下图所示：
点击【按分隔符】之后，弹出“按分隔符拆分列”对话框。如下图所示：
接下来，有两个选项需要设置：
1.通过下拉条，将“选择或输入分隔符”下面的“逗号”更改为“自定义”，并且在“自定义”下面的输入栏中输入中文状态下的逗号；
2.点击“高级选项”前面的小三角符号，则可将“高级选项”展开，将“拆分为”由默认的“列”，改为“行”。
这两处的设置如下图所示：
点击“确定”后，得到的结果如下图所示。可以看到，我们现在已经将餐名和数量按行拆分出来了。也就是说，以部门为依据，每一种餐名和数量均扩展到新的一行之中（餐名和数量仍然连在一起），此时的数据，已经由原来的9行扩展至22行。
接下来，我们再对餐名和数量进行拆分。首先，我们还是单击选中“午餐”这一列，依次点击【转换】－【拆分列】－【按照从非数字到数字的转换】。如下图所示：
点击【按照从非数字到数字的转换】之后，则可将餐名和数量进一步拆分出来。如下图所示：
可以看到，原来的“午餐”这一列没有了，同时，数据界面中新增了两列数据，分别为“午餐.1”和“午餐.2”，两列中的数据分别对应的内容是餐名和数量。
为了方便我们后续做数据透视表的时候能够见名知义，我们对“午餐.1”和“午餐.2”进行重命名。这个比较简单，我们只要双击相应的字段名称，然后输入我们需要的字段名称即可。我们将 “午餐.1”重命名为“午餐”，将“午餐.2”重命名为“数量”。重命名之后的数据如下图所示：
对字段进行重命名之后，我们还要设置一下“数量”字段的数据类型。单击“数量”前面的“ABC”图标，在弹出的界面中选择“整数”，如下图所示。
转换数据类型之后的数据如下图所示：
可以看到，“数量”前面的标识已经由“ABC”变成了“123”，说明数据的格式已经变为整数类型。
至此，原始数据已经被我们整理得非常规范了，符合数据透视表的使用需求。接下来，我们要做的事情，就是通过数据透视表对数据进行汇总统计。
依次点击【主页】－【关闭并上载】－【关闭并上载至】。如下图所示：
在弹出的“导入数据”对话框中，将“表”更改为“数据透视表”，其他选项保持不变，如下图所示。
点击“确定”后，即可从PQ编辑器中退出，转而进入数据透视表的操作界面。
到了这界面，小伙伴们是不是感觉回到了自己的地盘！数据透视表可是咱们公众号中经常推送的学习内容！
Step2 用数据透视表汇总数据
下面，我们再来看一下用PQ整理出来的数据如何在数据透视表中进行汇总。
1.如果只想显示各种午餐的数量，那么只需将“午餐”字段拖入到“行”中，将“数量”字段拖入到“值”中即可。如下图所示：
使用此种方式设置数据透视表得到的数据如下图所示：
可以看到，各种午餐的数量就计算出来了。
2.如果想要显示每一种午餐在各部门的情况，则只需将“午餐”和“数量”拖入到“行”和“值”中，然后，再将“部门”字段拖入至“列”中即可。结果如下图所示：
使用此种方式设置数据透视表得到的数据如下图所示：
可以看到，各种午餐在各部门的数量就显示出来了，在数据透视表的最后一列，还有显示了各种午餐的数量总计。这样更能够一目了然。
亲爱的小伙伴，用数据透视表结合PQ进行统计，是不是功能很强大呢？你学会了吗？
今日互动话题
在评论区留下你的足迹叭~
有什么好用的Excel插件可以推荐~
阅读推荐
关注我们，发现更多Excel优质教程
靠一只“猪”一秒拆分上千个工作表？！同事的骚操作看呆我......