作者:采悟
链接:https://www.zhihu.com/question/23858175/answer/1178247857
来源:知乎
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
很多人都有这样的苦恼,熟练Excel各种基础操作,会用Excel各种公式,会用Excel数据透视表,但和高手相比,差距很大,自己怎么能成为高手呢?
常规的操作和公式可以让你熟练掌握Excel,成为Excel高手,但要想成为真正的Excel数据处理高手,还差那么一点。
你不能只会公式和数据透视,甚至熟练使用VBA也还不行,因为VBA的强项并不是数据处理。那还必须会什么技能呢?
那就是PowerQuery!
PowerQuery,从字面意思上看,就是超级查询,专门为数据处理而生。
从Excel2010开始,微软开发了一个独立的PowerQuery插件,它突破了Excel的行数限制,到Excel2016,甚至把这个插件内置化,成为Excel中的一个常驻功能,可即使这样,依然大部分都没有听说过它,微软真的是太低调了,这么好用的工具,竟然都不大力宣传,让大部分人白白错过打好的学习机会。
既然微软不大力宣传,那我就帮微软宣传一下好了(虽然微软也不给发工资:)。
学好PowerQuery,不仅帮你成为高手,还能帮你快速的成为Excel高手。
PowerQuery在哪里?
它现在安静的躺在Excel的【数据】选项卡下面,可能很多人压根没有注意过,
![2e972f9ef4ee63917922dcaf30be4429.png](https://i-blog.csdnimg.cn/blog_migrate/357dcaceadb2dd2943216e7976b15872.jpeg)
点击进去,你就会发现另有一番天地,
![775f05caa388df48986f53daa7f98326.png](https://i-blog.csdnimg.cn/blog_migrate/9a834f938d7dfd3bebf8743d04d04b87.jpeg)
简直就是另外一个软件,有木有!
其实不用被这些吓到了,它使用起来十分简单,至少大部分常用的功能都通过点点鼠标就可以完整的。
那么它到底有什么用呢?为什么要从Excel界面来到这个奇怪的地方?
PowerQuery主要用于大数据清洗,就是将各种杂乱的数据整理成我们想要的样子,它的特点:
- 数据无限制,Excel有1048576行的限制,这里完全没有!
- 简单的操作,实现各种在传统Excel中很难完成的数据处理,快速成为Excel专家!
- 一劳永逸:所有的数据处理步骤自动保存,下次点击刷新即可。
可能说到这里大家还是不知道它到底有什么用,怎么用,下面以几个实例来看看它的用法。
一,批量合并多个Excel
如何将多个工作簿的数据合并到一张表上?
可能不同的人有不同的做法?
普通青年用万能的复制粘贴
二逼青年网上百度VBA代码一键汇总
文艺青年找个崇拜自己的实习小MM帮忙
其实都不必这么麻烦,Power Query来了。
下面来看一下PQ是如何汇总多文件的数据的:
假设有一个连锁型零售商店,有北京、广州、杭州三个城市门店,总部每月需要汇总每个城市门店销售明细数据,现在需要汇总2016年1-3月的销售明细,共9个工作簿,保存在一个文件夹内,结构如下:
![06ec744587c25713448ae3cb4a608662.png](https://i-blog.csdnimg.cn/blog_migrate/00ea5df14e0df26cc0f4efa6924c621b.jpeg)
由于只是数据处理的过程,下面的演示就在Excel2016进行,使用Excel2010、Excel2013的插件以及在Power BI Desktop中的操作也都是一样的。
首先我们新建一张空白Excel工作簿,点击"数据"选项卡下"新建查询",从文件夹获取数据:
![2c2da242a51b910c94c40b14d66a50e6.png](https://i-blog.csdnimg.cn/blog_migrate/73e91aed90781bc0353c732877d06dac.jpeg)
浏览找到该文件夹的路径,确认后出现这个界面,
![8d04318b7853a027a0d8f60675fe713d.png](https://i-blog.csdnimg.cn/blog_migrate/f5a5979d2077ef1daeec95da05373921.jpeg)
点击"编辑",进入查询编辑器:
![d1a564df5d39333897c9159fdea70b7b.png](https://i-blog.csdnimg.cn/blog_migrate/fadcf577e65194a3aa58657c3641b070.jpeg)
数据就储存在[Content]列,其他列都是每个工作簿的信息,现在要做的就是把Content的内容提取出来,点击"添加列"选项卡,添加自定义列,
![455a8249923b934faf229859bb4a80c1.png](https://i-blog.csdnimg.cn/blog_migrate/5dcf5114ed68c703c62d7f6d37f03354.jpeg)
自定义列中输入公式=Excel.Workbook([Content]),这里要注意严格区分大小写,不能写错了,这就是提取Excel格式数据的M函数(关于M函数后面会单独介绍)。
确认后就出现了一个自定义列:
![442b62465dbab861d5ec1ed9a8dd6286.png](https://i-blog.csdnimg.cn/blog_migrate/959f0d84f0ddb0333c9b40c1236543fb.jpeg)
点击自[定义列]右上角的双箭头展开数据,出现这个窗口,
![22d615076b38a73b7e678266ad3a4872.png](https://i-blog.csdnimg.cn/blog_migrate/f514180626fa5d206d59ee45d29324d4.jpeg)
直接点击确定,出现了如下这个界面:
![e60573e587d3f322c6621c7284e4b295.png](https://i-blog.csdnimg.cn/blog_migrate/fdbafd186e297959c625d2d9926bcb6a.jpeg)
又新增加了几列,继续点击[自定义.Data]列的右上角的双箭头,然后还是直接点确认,数据就全部出来了:
![394d3855d8f5e2053d2b727dc8eb06c3.png](https://i-blog.csdnimg.cn/blog_migrate/292914b96b82b25f02da1200aa4e3eb8.jpeg)
然后只留下下各门店上报的数据了,可以看到列的标题是系统添加的,其实应该用第一行作为列的标题,我们直接点击"转换"选项卡下的将第一行作为标题:
![62d48506e721c9ce79efb8b4c0738c5d.png](https://i-blog.csdnimg.cn/blog_migrate/a23f421f4a0a048e653b92e5c6b0b980.jpeg)
然后标题就提升上去了。
数据导入过程中9个表格的标题行是重复的,另外表格中可能有空行,所有把标题行和空行筛选出去,像在Excel中一样,点击城市的倒三角,去掉这两个勾选:
![41273b9e89ccb8d4cc244b2d688f72e1.png](https://i-blog.csdnimg.cn/blog_migrate/4bcbc5f888f647b6e25639bcc8d1919c.jpeg)
数据汇总完成,点击上载数据:
![5706f8c10a9eb8a5a25e4b1831a97971.png](https://i-blog.csdnimg.cn/blog_migrate/6f7c84dfe91d59d9480c5737c55f9f67.jpeg)
然后大功告成,数据就全部汇总到这个Excel表格中了。
看着好像步骤挺多,其实动手做起来,所有这些步骤只需一分钟而已,中间除了那个简单的M函数,一直都是点点鼠标,是不是非常简单呢。
更简单的是,上面操作的所有步骤都被记录下来,下个月销售记录更新的时候,比如把各个门店的4月份的明细数据放到相应的文件夹里面,连点鼠标都不用了,直接刷新数据,然后4月的数据就全部汇总到这个表格了。
如果你说这些其实通过VBA或者简单的复制粘贴还都可以做出来,那么如果有100家门店,每家门店全年12个月的数据呢,复制粘贴显然不现实,如果数据量大用VBA估计也会把电脑卡死。而在PQ中呢,就是打开文件点击刷新,这个文件夹下无论多少文件,无论数据量有多大,汇总也是秒秒钟的事情。
二、合并一个Excel工作簿中的多个sheet
步骤一:获取数据
随便新建一个Excel工作簿,点击数据>获取数据>自文件>从工作簿
![c1186cb669d5f34f3190be8c999b05f8.png](https://i-blog.csdnimg.cn/blog_migrate/ca02f7033b58f8e4202f094bbfee5554.jpeg)
步骤二:选择一个表,加载入PowerQuery
选择需要合并的工作簿,然后出现如下界面,
![792e8c696318e4a7306a2cd8d5133715.png](https://i-blog.csdnimg.cn/blog_migrate/4bcbd3204ccf5f0f6a564054dd6734c5.jpeg)
随便选择一个表,点击编辑,。
步骤三:删除系统步骤
进入powerquery编辑器后,找到右边步骤面板,把【源】之后的步骤全部删除
![c467391b034468b45b41cddffe5eddef.png](https://i-blog.csdnimg.cn/blog_migrate/d0b1f8e8aead331d22340b6ddd5f4312.jpeg)
步骤四:展开Data
点击Data列右侧的双箭头,如下图,点击确定。
![434b67cf60d34a6089316c5cd2c71649.png](https://i-blog.csdnimg.cn/blog_migrate/5149646ad105978347e5354aa4224b6a.jpeg)
步骤五:数据整理
到这里已经合并完成,
![2e31d555776402ae3db06d4a66cac83e.png](https://i-blog.csdnimg.cn/blog_migrate/b34dfe6298cae169204f30f46968ea70.jpeg)
然后提升标题,删除需要的列。
步骤六:上载数据
![f305450debc2da5ac7813d9ba1283d54.png](https://i-blog.csdnimg.cn/blog_migrate/783a86e8491bc3cce9b311699f066c79.jpeg)
然后数据就可以在Excel工作表中看到了。
仅仅点击几下鼠标,没有输入任何公式和代码,合并多个sheet的功能就完成了。
三、二维表转一维表
利用PowerQuery,二维表转为一维表十分方便,以Excel中常见的几种结构的表格为例,看看都是如何操作的。
第一种情形
![f978784237c775006179d016043e5e1d.png](https://i-blog.csdnimg.cn/blog_migrate/417eff663c01d40bf1c515c4c0399acc.jpeg)
简单的二维表,如本文的第一张图表,直接使用逆透视功能,就可以快速转为一维表,
![c2fc126e3490abddfac85bf0bee2d93c.png](https://i-blog.csdnimg.cn/blog_migrate/95ed73325312e6ce285061db8774ebcf.jpeg)
可以选择需要透视的列进行“逆透视”,也可以选择不需要透视的列,然后点击“逆透视其他列”来完成。
提示:这些操作,生成的最终一维表的列名,需要自己手动更改一下,下同。
第二种情形
行标题带有层级结构的二维表,如下,
![f475c7606a9acdf7651b4ab473bb7247.png](https://i-blog.csdnimg.cn/blog_migrate/6fd338265bcc3a080eab0cc194a86691.jpeg)
这种结构很清晰,但做数据分析最让人头疼的就是合并单元格,不过在PowerQuery中处理也并不困难,只是多了一些步骤。
将上表导入到PowerQuery编辑器后,先把年度列向下填充,将年度数据补齐,然后再进行逆透视,
![0f2e705c097ad310365d6bcdcdea4364.png](https://i-blog.csdnimg.cn/blog_migrate/4e96d227f1ad40c2f5a02312a25648de.jpeg)
第三种情形
列标题带有层级结构的二维表,如下,
![02b6769fe8ee8ea9dbb4553d2b2ed17d.png](https://i-blog.csdnimg.cn/blog_migrate/12246a8696cede8eb5110b4ca55e1cbb.jpeg)
这种表格可以先转置,转置以后,就是第二种情形,然后再进行逆透视就可以了,
![9e247723fbd41f693a98fad246600074.png](https://i-blog.csdnimg.cn/blog_migrate/942499eded10964d9ca17c4e6639afcc.jpeg)
第四种情形
行标题和列标题均带有层次结构,如下图,
![2e7788e465c87c90836b79e12b827b85.png](https://i-blog.csdnimg.cn/blog_migrate/ca0d210e47613bc4871b78af95dd06ed.jpeg)
看起来更复杂是不是,其实同样是上述几个步骤灵活组合,
a. 将年度列向下填充,补齐数据
![e8cb232801bd5ee8410ad5b7f7e3d984.png](https://i-blog.csdnimg.cn/blog_migrate/a3e7f5d9fa2971ec1a9757a936a00f71.jpeg)
b. 将年度列和季度列合并,生成年度季度列,这种结构就变成第三种情形,
![2380adc34aa8e678a793d602a4712216.png](https://i-blog.csdnimg.cn/blog_migrate/39c51c6efe48a9506cb912749cc70d4e.jpeg)
c. 转置表、把第一列向下填充,并提升标题,就变成了很简单的结构,也就是第二种情形,
![fbb2ab7562a21c8341cf16ad820e97ea.png](https://i-blog.csdnimg.cn/blog_migrate/08bed047e66a26b53c0b7705d0fa1d3a.jpeg)
d. 选中前两列,逆透视其他列,就变成了一维表
![d8ef2c5dbe4c0cb4c916dfc01abb10e6.png](https://i-blog.csdnimg.cn/blog_migrate/028f2389362c49793654f1efaccf64e1.jpeg)
e. 为了和源数据维度一致,将年度季度列进行分列
![c8d98e57d2925581f745aa52df9605d6.png](https://i-blog.csdnimg.cn/blog_migrate/9dd5b61d2db3e80658f037985a714c2f.jpeg)
至此就得到了最终的一维表,看起来步骤很多,其实熟练掌握了也就分分钟的事。
四:零代码轻松获取地址的经纬度
从一个地址中找出该地址所处的省份、城市,如果是结构化的地址,比如“北京市海淀区丹棱街5号”,可以通过分列或者文本函数提取出相关信息,但是如果一个特定的地址不包含城市信息,比如“中央电视台总部大楼”,这种情况下你如何自动找出所在的位置信息呢?
更进一步的,我们还想返回这些地址的经纬度信息,这种需求也很普遍,但通过简单的文本函数已无法实现。
不过还好有更强大更方便的实现方式:调用地图网站的API.
关于什么是API就不再细说,可以自行搜索相关信息,下面直接介绍PowerQuery如何调用API,并返回一个位置信息的相关维度。
各大地图网站都有可供调用的API,这里我们使用高德地图的API,首先需要申请一个key,关于如何申请,请自行百度,有了这个key以后,就可以在PQ中开始操作了。
以返回"中央电视台总部大楼"所在的城市、辖区、经纬度等信息为例,来分步操作。
1、获取数据>web,输入网址:
https://restapi.amap.com/v3/geocode/geo?address=中央电视台总部大楼&output=XML&key=你申请的key
2、点击编辑进入pq编辑器中,
![2002294a993051efe78b55337057e58e.png](https://i-blog.csdnimg.cn/blog_migrate/00e4c8e5a0d974afcfd22b59c59111ac.jpeg)
3、逐步展开Table中的数据并删除不必要的列,就可以得到我们想要的信息,
![c72717ac1efb3452850023f57c504ed1.png](https://i-blog.csdnimg.cn/blog_migrate/99f4228db30bdf6bfb78d9351eae8fbd.jpeg)
(调出来的信息很丰富,也包括国家、区号、结构化地址等信息,可以根据自己的需要来选择保留哪些列)
上面的这些步骤只是获取地址信息的sample,我们不能每查询一个地址都要操作一遍这些步骤,下面才是重点。
还记得之前介绍的自定义函数吗?(认识Power Query的自定义函数),这里就是将以上的步骤封装成一个函数,随时调用。
右键该表>创建函数,
![fb9ed304e2de2aaad1460f478e610f88.png](https://i-blog.csdnimg.cn/blog_migrate/bf7ddaa8ef702d475d3ece7b53a7a2f3.jpeg)
输入一个函数名,自己随便写,不冲突就可以,我这里用location作为函数名,这样就建好了一个自定义函数location。
然后打开这个自定义函数的代码,在括号中输入参数,并将地址更改为参数,比如参数设为x,将上面步骤中的“中央电视台总部大楼”替换为参数x。然后这个自定义函数就建好了。
如果想找到上海东方明珠的位置信息,直接输入参数框并调用即可,
![805bc7a356d7c786384fa10d6f22fb1e.png](https://i-blog.csdnimg.cn/blog_migrate/368737d0422542dda5d29e5800f8029f.jpeg)
对于批量的地址,还可以直接通过添加列来批量调用自定义函数location,
![46ce5b8e434c0b90f6681fc1dde6619f.png](https://i-blog.csdnimg.cn/blog_migrate/390ef30bdfe0318156416f2958e58e41.jpeg)
利用PowerQuery调用API是不是很强大呢,全程没有输入一行代码,只是通过界面操作,点点鼠标,就批量获得你想要的各类地址信息。
通过以上几个示例,看到PowerQuery的强大了吧,关键是它极易上手,无论变成基础,也不用对Excel非常熟练,甚至Excel小白,都能轻松完成在Excel几乎不可能完成的数据处理,帮你快速超过Excel老司机,成为Excel大神。
关于Excel的PowerQuery,以及强大的Excel数据建模PowerPivot,这些模块现在都已经集成在了更强大的Power BI中,也可以直接在PowerBI中学习,关键看平时进行数据处理的场景主要在Excel中还是在数据可视化方面。