用 Power Query 实现跨文件关联匹配

当通过数据关联实现匹配的数据量偏大，或者数据来源于不同文件时，工作表函数的表现往往不尽如人意，使用 Power Query 效率更高，操作也较为简单。

如图 4-126 所示，一个文件夹内存在多个工作簿，其中包含“商品销售数据表”“商品价格表”等。

图 4-126 一个文件夹内两个工作簿

使用 Power Query 实现数据关联并匹配操作步骤如下。

步骤 1：新建一个工作簿并打开。在【数据】选项卡下依次单击【获取数据】下拉按钮→【自文件】→【从工作簿】命令，如图 4-127 所示。

图 4-127 从文件夹获取数据

步骤 2：在弹出的【导入数据】对话框中，选择目标工作簿，如“商品价格表 .xlsx”，并单击【导入】按钮。在弹出的【导航器】对话框中，单击目标工作表，如“商品价格表”，单击【转换数据】按钮，如图 4-128 所示。

图 4-128 导入商品价格表

步骤 3：在 Power Query 编辑器左侧的【查询】窗格空白处，单击右键，在弹出的快捷菜单中依次单击【新建查询】→【文件】→【Excel】命令。在弹出的【导入数据】对话框中，重复步骤 2，选取目标工作簿，单击【导入】按钮，在弹出的【导航器】对话框中，选中目标工作表，如“商品销售数据表”，并单击【转换数据】按钮，如图 4-129所示。

图4-129 导入商品销售数据表

步骤 4：在 Power Query 编辑器的【主页】选项卡下，单击【合并查询】右侧的下拉按钮，在弹出的下拉菜单中单击【将查询合并为新查询】按钮。

图 4-130 将查询合并为新查询

步骤 5：在弹出的【合并】对话框中，将主要表设置为【商品销售数据表】，匹配表设置为【商品价格表】。联接种类保持默认选项【左外部（第一个中的所有行，第二个中的匹配行）】。先后单击主要表和匹配表的“商品编码”字段，也就是将该字段作为匹配列。单击【确定】按钮，如图 4-131 所示。

图 4-131 设置【合并】对话框

步骤 6：系统会自动生成一个名为“Merge1”的查询，在该查询的数据预览窗口，单击【商品价格表】字段右侧的扩展按钮，在弹出的选项菜单中取消选中【商品编码】复选框，取消选中【使用原始列名作为前缀】复选框，单击【确定】按钮，如图 4-132 所示。

图 4-132 扩展“商品价格表”字段

步骤 7：在 Power Query 编辑器的【主页】选项卡下，依次单击【开始】选项卡的【关闭并上载】下拉按钮→【关闭并上载至 ...】命令，在弹出的【导入数据】对话框中，选中【仅创建连接】单选按钮，单击【确定】按钮关闭对话框。如图 4-133 所示。

图 4-133 导入数据仅创建连接

步骤 8：在当前工作表的【查询 & 连接】窗格，右击名为【Merge1 仅限连接】选项，在弹出的快捷菜单中单击【加载到 ...】命令，在弹出的【导入数据】对话框中选中【表】单选按钮，在【数据的放置位置】区域选中【现有工作表】单选按钮，在【现有工作表】编辑框中输入“=A1”，最后单击【确定】按钮关闭对话框，如图 4-134 所示。

图 4-134 设置导入数据对话框

数据导入当前工作表后，如图 4-135 所示。

图 4-135 数据导入工作表

推荐阅读

《Excel 2016应用大全》