点击上方↑↑↑“ExcelEasy”关注我们
Power Query是Excel Power BI套件系列工具中的一个,是一个非常 强大的数据获取处理工具。有了它,原来很多需要非常复杂的公式或VBA完成的工作就可以很轻松就完成了。强烈推荐大家学习掌握这个工具。
在Excel 2016中,不需要额外安装,但是Power Query选项卡被取消了,换了一个面目,在“数据”菜单中以“获取和转换”的名字出现。
1
从Excel文件导入数据
点击“Power Query”选项卡中的“从文件”,然后点击“从Excel”选择一个文件:“可乐产品销售数据.xlsx”。
在经过可以忽略的等待后,出现了一个叫做导航器的对话框:
左侧列出了该Excel文件的所有工作表,点击某个工作表后,右侧列出了工作表的内容。
点击右下角3个按钮中的第一个按钮“加载”
在Excel中将会添加一个新的工作表,内容就是在导航器选中的工作表内容。
2
认识“Power Query编辑器”
我们先来认识一下Power Query编辑器。这是一个数据导入中基本上每次都会用到的工具。首先,回到导航器界面,
点击右下角“编辑”按钮,就会调出Power Query编辑器。
这个编辑器有3个主要的选项卡:开始,转换,添加列。
在开始选项卡中有这几个主要功能:
关闭并上载。关闭编辑器,并将数据导入到Excel表格中或者数据模型中。
刷新预览。刷新查询,重新执行所有转换步骤,并且预览加载结果。
高级编辑器。显示查询代码(用M语言)。
选择列/删除列。允许决定在导入时保留哪些列。点击选择列后,或出现一个对话框:
保留行/删除行。允许决定导入哪些行。在这里可以选择删除重复行。
拆分列。将一列拆分为多列。概念上类似分列。可以按照分隔符或字符数拆分。
分组。将多行分为一组,依据是选定列中的值。
3
导入同一目录下的多个CSV文件
CSV文件是逗号分隔的文本文件。在实际工作中,我们可能会将数据存放在这样的文件中,并且将这些文件放在同一目录下。我们可以一次性将该目录下的CSV文件导入。
点击“Power Query”选项卡的“从文件”,然后点击“从文件夹”
然后选择存放数据文件的文件夹:
Excel列出该目录下所有的文件
点击右下角的合并按钮,继续点击“合并和编辑”,在出现的“合并文件”对话框中点击确定
点击“确定”,打开了Power Query编辑器。
可以看到除了原有的数据列外,Excel新增加了一列记录数据来源的文件名。
接下来我们替换这个文件名为我们希望的内容
替换值
在“开始”选项卡中,点击“替换值”,在出现的对话框中,在“要查找的值”中输入“可乐销售数据1.csv”,在"替换为"中输入“南区”,点击确定。
Excel自动开始替换。
再次点击替换值,将“可乐销售数据2.csv”,替换为“北区”。
分组
点击“开始”选项卡中的“分组依据”,在出现的对话框中,在“分组依据”中选择“产品”,其他保留缺省值不变。点击“确定”。
Power Query编辑器出现下列结果
可以看到,Power Query自动将刚才两个文件的数据汇总成一个不同产品的计数结果了。
点击“关闭并上载”,Excel中出现一个新的工作表,就是刚才生成的不同产品的计数结果了。
4
总结
今天,我们是初识Power Query。实际上Power Query还有很多非常强大的功能,既可以导入其它类型的数据,还可以进行各种复杂的处理和转换。有时候一些复杂的报表也可以通过Power Query直接做出来。在后面的章节中,我们会陆续给大家介绍。好了,今天就分享到这里了。如果大家有什么问题和想法,欢迎下面留言讨论。
取得本文案例文件的方式:关注本公众号
- 点击底部菜单“联系客服”,与客服取得联系,索取“Power Query”案例文件
觉得好看点个【在看】再走吧