分享

Excel应用大全 | 数据跨文件关联匹配该如何实现

 hercules028 2021-05-28
一本书教会你分分钟搞定数据分析!



在使用Excel进行数据整理的过程中,一种常见的情况是,需要整合的信息分别处于不同的工作表甚至工作簿中。例如,销售表和价格表、工资表和员工信息表等。处理这类问题常用的方法或工具有公式、PowerQuery等。


图片

使用函数查询商品信息表

图片
图片

图片
如图4-124所示,一个工作簿内存在多张工作表,分别为“商品销售数据表”和“价格表”。现在需要根据两张表之间商品编码的关系,将价格表的“商品名称”“价格”两个字段的数据写入商品销售数据表。操作步骤如下。

步骤1:选取商品销售数据表,在E1和F1单元格分别输入字段名“商品名称”“单价(元)”。

步骤2:在 E2 单元格输入以下VLOOKUP公式,并复制填充至 E2:F71 单元格区域。
($B2,价格表!$A:$C,MATCH(E$1,价格表!$A$1:$C$1,0),0)

图片

图 4-124 商品销售数据表和价格表

$B2单元格的商品编码是查找值,价格表!$A:$C是查找范围。
使用MATCH函数,查找E$1单元格中的字段名在价格表$A$1:$C$1中的位置,返回结果为2,表示VLOOKUP函数返回查找区域中第2列的内容。
第四参数使用0,表示使用精确匹配的方式进行查找。公式返回结果如图4-125所示。

图片

图 4-125 VLOOKUP 关联查询结果

更多使用函数与公式实现数据关联和匹配的技巧请参阅第 4 章和第 5 章内容。



图片

用Power Query数据关联与匹配

图片
图片

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

图片

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

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

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

图片

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

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

图片

图 4-128 导入商品价格表


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

图片

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

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

图片

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

图片

4-131 设置合并对话框

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

图片

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

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

图片

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

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

图片

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

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

图片

4-135 数据导入工作表


图片

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多