不服不行!Power Query这功能狂甩函数、VBA好几条街!

 

Power Query是微软这几年主推的一个数据清洗、数据转换的利器,在最新版的office中已经完美集成到Excel软件中。

 

如何用Power Query(PQ)合并同一文件夹下的多个Excel工作簿文件?一文中,我们已经见识了Query在合并文件方面的高效。

 

合并功能还不是Power Query的最强大的功能,它最核心功能是数据转换。

 

今天就让我们一起来见识下。

 

现在有这样的一个数据源

 

颜色列的每行的多个颜色都由逗号分隔,为了便于数据的处理,需要将每行的数据,按照颜色分拆成如下所示

 

当你第一次看到这样的需求,现在给你5分钟时间思考下要怎么做?

 

如果是位Excel函数高手,可能已经想到了处理的办法,第一步肯定是先写个函数公式把C列的颜色按照分隔符拆分后逐个提取出来,然后还要再思考5-10分钟想下如何把分拆的字符从上到下放置,最后还要想办法把A、B、D列的内容如何引用过来,这时候时间可能已经半个小时过去了,即使最后都把公式写出来了,调试还要花些时间,前前后后一个小时没了。等最后都弄好了,发现数据量特别大,10万行,OMG,函数下拉计算结果时,如果电脑配置很好,可能只需要再花个10-20分钟计算公式的结果,如果电脑配置不行,有可能Excel就已经处于假死状态了,计算量太大,要重新启动Excel了。

 

如果是位VBA高手,一般思路是这样的,从上到下循环,先把C列的颜色按照分隔符拆分到数组,然后再依次循环数组,同时把A、B、D列的内容同时引用进来。这期间写代码和调试的时间少则10-20分钟,多则半个小时以上。

 

接下来,让我们看看Power Query是如何处理这种问题的!

 

1.选中数据源的任意一个单元格,单击“数据”选项卡下的“获取和转换数据”组中的“自表格/区域”命令,在弹出的“创建表”对话框中单击“确定”。

 

 

2.在出现的Power Query编辑器窗口中,选中“颜色”列的任意一个格子,在“转换”组中选择“拆分列”中的“按分隔符”

 

 

3.在弹出的“按分隔符拆分列”对话框中,展开“高级选项”,选择拆分为“行”(全文最最关键的一步就在这个“行”上),然后单击“确定”。

 

 

结果如上图所示,我们要的效果已经有了。

 

4.最后就是将Power Query里的数据加载到Excel单元格中,单击“关闭并上载”按钮,将Power Query处理后的数据加载到Excel单元格中,完整的操作动画如下

 

 

如果后期数据源增加或减少或者改变,只需要单击“数据”选项卡下的“刷新”按钮或者按下组合快捷键Alt+F5即可将结果更新一遍。

 

最后,让我们回过神来对比下,整个操作过程如果熟练以后,实际上1分钟都不到就完成了所有的步骤,你们说,是不是狂甩函数和VBA几条街。

 

这还仅仅是Power Query众多高效功能中的其中一个,今后将陆续的向大家介绍Power Query更多秒杀函数和VBA的功能。

 

       

发表评论