★编按★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.如果只想显示各种午餐的数量，那么只需将“午餐”字段拖入到“行”中，将“数量”字段拖入到“值”中即可。
如下图所示：
使用此种方式设置数据透视表得到的数据如下图所示：
 fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E)
可以看到，各种午餐的数量就计算出来了。2.如果想要显示每一种午餐在各部门的情况，则只需将“午餐”和“数量”拖入到“行”和“值”中，然后，再将“部门”字段拖入至“列”中即可。结果如下图所示：
使用此种方式设置数据透视表得到的数据如下图所示：
可以看到，各种午餐在各部门的数量就显示出来了，在数据透视表的最后一列，还有显示了各种午餐的数量总计这样更能够一目了然亲爱的小伙伴，用数据透视表结合PQ进行统计，是不是功能很强大呢？你学会了吗？今日互动话题。
在评论区留下你的足迹叭~有什么好用的Excel插件可以推荐~扫一扫添加老师微信
在线咨询Excel课程阅读推荐关注我们，发现更多Excel优质教程比VBA好用100倍！拆分工作表，用数据透视表5秒就搞定！靠一只“猪”一秒拆分上千个工作表？！同事的骚操作看呆我......不懂这个“人类高质量Excel技巧”，就不要轻易在简历上写“精通Excel”
大厂HR：有这种Excel思维的实习生，我真的想高薪留下来小瞧日期函数的都被打脸了！同事用这个函数做Excel时间表，效率提高35%！课程推荐前路浩浩荡荡，万物皆可期待想要全面系统学习Excel，不妨关注部落窝教育
《一周Excel直通车》视频课或者《Excel极速贯通班》《一周Excel直通车》视频课包含Excel技巧、函数公式、数据透视表、图表一次购买，永久学习！！！最实用接地气的Excel视频课《一周Excel直通车》，风趣易懂，快速高效，。
带您7天学会Excel38 节视频大课（已更新完毕，可永久学习），理论+实操一应俱全。
主讲老师：滴答Excel技术大神，资深培训师；课程粉丝100万+；开发有《Excel小白脱白系列课》《Excel极速贯通班》原价299元限时特价 99 元！！！少喝两杯咖啡，少吃两袋零食就能习得受用一生的Excel职场技能！。
购课后，加客服微信：blwjymx3领取练习课件 长按左边二维码立即购买学习让工作提速百倍的「Excel极速贯通班」，点击文章下方的“阅读原文”，可直接购买。
您的“点赞”、“在看”和 “分享”是我们产出的动力
发表评论:
◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。