![8b3f81b3b9b237a73cf8931e0d71883b.png](https://i-blog.csdnimg.cn/blog_migrate/ceec0367607012fc1bb0fc5455558927.jpeg)
前言
前几天在别人群里有人询问关于逆透视问题,逆透视本身不算太难,问题在于逆透视的列数不固定。藉此机会,写一篇关于PQ的小教程。
注意:PowerQuery在2016及以上版本已集成,但2010和2013需要安装插件。本次使用Excel2016专业版。
透视,即从不同的角度观察数据。比如:观察每个人每天的销售额。而逆透视则是将这一过程翻过来。
![0c4021c9814bb0e35f22e83a24ad659e.png](https://i-blog.csdnimg.cn/blog_migrate/61f92d1900890f4f2d0a80b6f1041360.png)
问题描述:对成绩单中的成绩进行逆透视,但是成绩列数可能不同,表格2列分别为学号和姓名。
本次使用的数据样本:
![a5b5f991def49aa0cf4c2f76336598ef.png](https://i-blog.csdnimg.cn/blog_migrate/ba4f6f61876144c8ac427d75159347ec.png)
导入清洗
首先在“数据”选项卡中使用“新建查询”-“从文件”-“从工作簿”导入文件。
![4a66890226263b17bdc4cff586707b5f.png](https://i-blog.csdnimg.cn/blog_migrate/f6d54e9771234a5735b102135cab57c8.png)
在出现的窗口选择需要的工作表即可,然后这里选择“转换数据”。
- 加载:加载工作簿中的工作表(前面选择的文件)到当前工作簿中的工作表。
- 转换数据:转换加载的工作表到PQ中并进行数据类型转换。
![bf7ec1bc85fd85dd422cb57a3765ca13.png](https://i-blog.csdnimg.cn/blog_migrate/f95cc629242f135986ce85a6ea885d71.png)
转换数据后会跳转到PQ界面,大概如下:
![20f3c8494d0b048483c1ff36ddd53807.png](https://i-blog.csdnimg.cn/blog_migrate/2762a7627bb8f989f5d783f1c0b70e56.jpeg)
这里我们称每个结果为“查询”(左下角),你可以双击修改名称。
右下角展示查询的步骤设置,你可以点击x删除步骤(不可撤回)或者点击齿轮修改具体设置。需要注意的是每个步骤其实我们都可以通过“高级编辑器”自己书写函数实现。
如果你自己观察当前的表格,其实可以发现列名是不对的。因为表格第一行是合并单元格,而且第一行也用不到,所以这里我们需要删除第一行。
删除“步骤”中的部分内容,只保留前面两个:
![223ef099366b84c6450f9027cef72ae4.png](https://i-blog.csdnimg.cn/blog_migrate/630aef34dc9bcb765db23162060eec99.png)
“主页”-“删除行”-“删除最前面几行”,输入1确认即可。
![0c165b998727b2b1563d2faa817e34f9.png](https://i-blog.csdnimg.cn/blog_migrate/a151d2653051ba8d7e098710cb80e80b.png)
![ba121c463ae2a6e27d52d2793cec183f.png](https://i-blog.csdnimg.cn/blog_migrate/b451275a373984bfe297ef2ffa7acc73.png)
提升标题,把第一行设置为标题。
![f0c3f479da08228ab4907c9ee0408e25.png](https://i-blog.csdnimg.cn/blog_migrate/ee46dac68ce13ae4ef3adce4a752eb60.png)
![abad82840360fe8858bb0886e2070fda.png](https://i-blog.csdnimg.cn/blog_migrate/018cd851f67cba2c577426d545fa4e12.png)
至此数据已经完成导入和清洗,下面开始逆透视~
逆透视
在以往的excel中逆透视是很麻烦的操作,可能你需要使用vba进行逆透视,但是在PQ中这是很简单的一步。
这里由于需要逆透视除“学号”、“姓名”的其他列,那么选中“学号”,“姓名”列。按住Ctrl点击标题多选或者Shift连选。
![7f4c8c3aa298aa91ae28e6482544dc72.png](https://i-blog.csdnimg.cn/blog_migrate/1c658d32886130c753fd1cb494709417.png)
“转换”-“逆透视”-“逆透视其他列”
![9c1461e868abb1860285510b09074f85.png](https://i-blog.csdnimg.cn/blog_migrate/233d7db609b199b59f0aa49fc8df4da2.png)
这里我们只需要双击“属性”和“值”修改列名即可。你也可以使用“高级编辑器修改”函数中的参数,不过不建议初学者这么做。
![61537a244f0d7d35119408112d03d809.png](https://i-blog.csdnimg.cn/blog_migrate/643470fe55ef39b5a05c27f588453cd3.png)
修改查询名称并修改列名后的最终完成效果:
![62d3b332f7b9c228bae233d9568c6f05.png](https://i-blog.csdnimg.cn/blog_migrate/456d0f359c8dd307c975ea52f3dbc14c.jpeg)
最后一步导出到Excel中,点击“主页”-“关闭并上载”。
![9958c4ddcfa2e2a6779bdacdd88704ad.png](https://i-blog.csdnimg.cn/blog_migrate/f4bebf5b048d43473ed2fb0fb1a90cb2.png)
加载完成后如果没有右侧的“工作簿查询”,可点击“数据”选项卡-“显示查询”显示。
![d4a6454d399c928848b342acbcfa2b44.png](https://i-blog.csdnimg.cn/blog_migrate/945b40a787b02cd7e5de7a55029b588e.jpeg)
验证
我们尝试对源文件增加“历史”列,并增加第9个人。
![2d67e7d259e7cbbc425049a0707c8f11.png](https://i-blog.csdnimg.cn/blog_migrate/f0d280ddcb6383c94ea4e026da5f1eab.png)
修改好以后保存(Ctrl+S)。然后在刚才加载出来的表格中随便点一下,在出现的“设计”选项卡中选择“刷新”。如果有多个表且需要刷新那么请选择全部刷新~
![b871be02d8825c42efcb29423bb0da7c.png](https://i-blog.csdnimg.cn/blog_migrate/09fcd239d7a6025e9f84331f8bdfba98.jpeg)
刷新后~
![c59250921b6ad0f5ae984245c15f6165.png](https://i-blog.csdnimg.cn/blog_migrate/07a4ca700559ab62942d0536cdf9ceeb.png)
写在最后
一般情况下,PQ的功能区就能够满足我们的功能了,如果有些需求很复杂,那么你就需要学习M语言。当然这里只是简单的体验下PQ,还没有结合PP完成统计汇总,PP才是我们最终的目标,后续我会提及。
微软官方M语言文档:
Power Query M function reference - PowerQuery M
欢迎关注个人公众号:【Excel自学之路】