编按：

Hello各位小伙伴，在日常工作中，我们能见到很多混乱、不规范的数据表格。面对这样的数据，我们往往没有办法直接进行汇总统计。这个时候Excel中的无敌数据清洗神器——Power Query就能大展身手啦~

上进青年小张最近又遇到了一个新的Excel汇总统计问题。其实问题听起来也不难，老板交给他一个新的任务——统计公司各部门订午餐的情况。按一般情况来说，以小张的水平，如果数据规范的话，用数据透视表进行统计就是分分钟的事儿。可是，我们想得太简单了——实际工作中的数据，永远具备让人抓狂的“超能力”。

我们先看一下数据，如下图所示。A列是部门，B列是各部门所订午餐的明细，其中包括餐名和数量，餐名和数量之间没任何有分隔符号，但是不同的餐名及相应的数量之间用中文逗号分隔。

本例中小张遇到的数据，与我们常见的一维数据表长得不一样，对于这样的数据，该如何进行汇总统计呢？别怕！别忘了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进行统计，是不是功能很强大呢？你学会了吗？

本文配套的练习课件请加入QQ群：902294808下载。

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

扫下方二维码关注公众号，可随时随地学习Excel：

相关推荐：

７个Excel小技巧，提高表格查看效率

Excel运用规范1：一个单元格只记录一条信息

快速整理不规范的Excel表格的7个公式

9条最实用的计算excel中关于日期的公式！（建议收藏）

版权申明：

本文作者阿硕；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。