二维码 购物车
部落窝在线教育欢迎您!

数据源表格太杂乱?试一试这个无敌的数据清洗神器——PQ

 

作者:阿硕来源:部落窝教育发布时间:2021-08-09 17:10:30点击:2644

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。


编按:

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

IMG_256

相关推荐:

7个Excel小技巧,提高表格查看效率

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

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

9条最实用的计算excel中关于日期的公式!(建议收藏)

版权申明:

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