在使用Excel进行数据整理的过程中,一种常见的情况是,需要整合的信息分别处于不同的工作表甚至工作簿中。例如,销售表和价格表、工资表和员工信息表等。处理这类问题常用的方法或工具有公式、PowerQuery等。如图4-124所示,一个工作簿内存在多张工作表,分别为“商品销售数据表”和“价格表”。现在需要根据两张表之间商品编码的关系,将价格表的“商品名称”“价格”两个字段的数据写入商品销售数据表。操作步骤如下。步骤1:选取商品销售数据表,在E1和F1单元格分别输入字段名“商品名称”“单价(元)”。步骤2:在 E2 单元格输入以下VLOOKUP公式,并复制填充至 E2:F71 单元格区域。($B2,价格表!$A:$C,MATCH(E$1,价格表!$A$1:$C$1,0),0)$B2单元格的商品编码是查找值,价格表!$A:$C是查找范围。使用MATCH函数,查找E$1单元格中的字段名在价格表$A$1:$C$1中的位置,返回结果为2,表示VLOOKUP函数返回查找区域中第2列的内容。第四参数使用0,表示使用精确匹配的方式进行查找。公式返回结果如图4-125所示。 图 4-125 VLOOKUP 关联查询结果 更多使用函数与公式实现数据关联和匹配的技巧请参阅第 4 章和第 5 章内容。 如图4-126所示,一个文件夹内存在多个工作簿,其中包含“商品销售数据表”“商品价格表”等。使用PowerQuery实现数据关联并匹配操作步骤如下。步骤1:新建一个工作簿并打开。在【数据】选项卡下依次单击【获取数据】下拉按钮→【自文件】→【从工作簿】命令,如图4-127所示。步骤2:在弹出的【导入数据】对话框中,选择目标工作簿,如“商品价格表.xlsx”,并单击【导入】按钮。在弹出的【导航器】对话框中,单击目标工作表,如“商品价格表”,单击【转换数据】按钮,如图4-128所示。图 4-128 导入商品价格表 步骤3:在PowerQuery编辑器左侧的【查询】窗格空白处,单击右键,在弹出的快捷菜单中依次单击【新建查询】→【文件】→【Excel】命令。在弹出的【导入数据】对话框中,重复步骤2,选取目标工作簿,单击【导入】按钮,在弹出的【导航器】对话框中,选中目标工作表,如“商品销售数据表”,并单击【转换数据】按钮,如图4-129所示。步骤4:在PowerQuery编辑器的【主页】选项卡下,单击【合并查询】右侧的下拉按钮,在弹出的下拉菜单中单击【将查询合并为新查询】按钮。步骤5:在弹出的【合并】对话框中,将主要表设置为【商品销售数据表】,匹配表设置为【商品价格表】。联接种类保持默认选项【左外部(第一个中的所有行,第二个中的匹配行)】。先后单击主要表和匹配表的“商品编码”字段,也就是将该字段作为匹配列。单击【确定】按钮,如图4-131所示。步骤 6:系统会自动生成一个名为“Merge1”的查询,在该查询的数据预览窗口,单击【商品价格表】字段右侧的扩展按钮,在弹出的选项菜单中取消选中【商品编码】复选框,取消选中【使用原始列名作为前缀】复选框,单击【确定】按钮,如图 4-132 所示。 图 4-132 扩展“商品价格表”字段 步骤7:在PowerQuery编辑器的【主页】选项卡下,依次单击【开始】选项卡的【关闭并上载】下拉按钮→【关闭并上载至...】命令,在弹出的【导入数据】对话框中,选中【仅创建连接】单选按钮,单击【确定】按钮关闭对话框。如图4-133所示。步骤 8:在当前工作表的【查询 & 连接】窗格,右击名为【Merge1 仅限连接】选项,在弹出的快捷菜单中单击【加载到 ...】命令,在弹出的【导入数据】对话框中选中【表】单选按钮,在【数据的放置位置】区域选中【现有工作表】单选按钮,在【现有工作表】编辑框中输入“=A1”,最后单击【确定】按钮关闭对话框,如图 4-134 所示。数据导入当前工作表后,如图4-135所示。 图 4-135 数据导入工作表
|