Excel中有一个功能强大但常被忽视的数据清洗工具，其位于菜单栏“数据”选项中的“获取和转换数据”工具组。用户点击该工具组下的任一图标，均可调用“Power Query编辑器”──这是用于Power BI、Power Automate和Excel的数据清洗工具。　　

        Power Query编辑器执行的每个清洗步骤会保存为一个步骤，以后可再次运行该步骤。例如，你每个月会下载一份需要运行6个清洗步骤的报告，如果对当月下载的文件执行这些步骤，并将这一系列步骤保存在工作簿中，下个月就可以轻松运行这些步骤。

　　Power Query编辑器并非由Excel团队开发，而是出自微软的SQL Server团队。第一次使用Power Query工具时，你可能会有完全不同于Excel的体验。比如，Excel菜单栏“数据”选项下的“分列”工具，其等效命令在Power Query编辑器中叫“拆分列”。但在Power Query编辑器中点击“拆分列”下拉菜单，会看到许多比Excel“分列”更强大的功能。

　　本文将重点介绍一些在Power Query编辑器中附加的、在Excel中不易实现的功能。

“加载”数据

　　在Power Query编辑器进行的数据转换不会破坏Excel工作表中的原始数据，使用Power Query所做的“更改”将另存为一组指令。在Power Query中完成数据清洗后，相关指令会在内存中运行，并输出一个包含已完成数据清洗的新工作表。

　　使用Power Query编辑器，首先要将数据格式化为表格，这可以使用快捷键“Ctrl+T”，也可以点击菜单栏“开始”选项，再选择“套用表格格式”工具。又或者，为数据创建命名范围。Power Query只适用于表格或有命名范围的数据。

　　在Excel菜单栏“数据”选项下，找到“获取和转换数据”工具组。在旧版本Excel中，点击“来自表格/区域”按钮；在最新版Microsoft 365中，该命令最近更名为“来自工作表”（见图1）。选择表格中的一个单元格（如果使用命名范围，则选择整个范围），然后在“数据”选项下选择“来自工作表”。

　　数据的前1,000行就会显示在Power Query编辑器中。图2显示了Power Query编辑器菜单栏下五花八门的工具，菜单栏则包括文件、主页、转换、添加列和视图这五个选项。

POWER QUERY工具

　　以下是Power Query的一些工具及功能，这些工具在功能上要优于Excel中的同类工具。

　　向下填充：如果某一列以大纲形式显示，例如，“东部地区”在A2单元格中，后跟空白单元格，直至第200行是“中部地区”，然后再是空白单元格，直至第500行是“西部地区”，就可以使用菜单栏“转换”选项下的“向下填充”工具来快速填充空白单元格。

　　删除行：如果导入的数据在行与行之间存在空白行，可以使用“删除行”工具中的“删除间隔行”来删除。“删除间隔行”命令很灵活，允许用户进行一些指定操作，如保留第2行、删除接下来的3行，然后保留1行，并在数据中重复该模式。

　　拆分列：此命令可以指定拆分工作表中的文本与数字字符，反之亦然。对于A123、BC234或DEF111这样的零件编号，可以通过内置的拆分命令轻松处理。

提取菜单：可使用该菜单来选择分隔符之后、之前以及之间的所有文本。  

　　信息菜单：Excel有判断偶数、奇数和符号的函数，而在Power Query编辑器菜单栏“信息”选项下，可以找到更易使用的工具来执行相同的操作。  

　　索引列：可以快速从1或从0开始添加索引列。

　　图2中还有部分未显示的命令是逆透视列，以及按分隔符拆分单元格数据和将数据拆分为多行。就像在Excel中一样，许多常用的命令都可以通过右键单击列标题弹出。如果你习惯使用Excel，使用Power Query编辑器应该也很容易。

清洗后的数据

　　在清洗数据时，屏幕右侧会创建一个你所执行步骤的列表（即“应用的步骤”）。留意这个列表。若操作有误，可在任意步骤上点“X”删除此步，然后再重试。该面板提供了可审查的详细记录，可在任何步骤之后回顾期间结果。

　　在Power Query中完成数据清洗后，点击菜单栏“主页”下的“关闭并上载”命令，Excel将插入一个包含已完成清洗数据的新工作表。

　　该功能的好处是下次更新原始数据时，可以使用菜单栏“数据”选项下的“全部刷新”命令再次清洗数据。

Bill Jelen，MrExcel.com负责人，著有61本Excel方面的书籍，帮助制作了IMA Excel数据分析课程（bit.ly/2Ru2nvY）和IMA Excel 365系列课程（bit.ly/2qDKYXV）。可发送问题至IMA@MrExcel.com，它们有可能成为以后文章的主题。

于婕 译，郭强 校