在两个工作表中查找相似的数据并放入第三个工作表中

在两个工作表中查找相似的数据并放入第三个工作表中

将两个不同 Excel 电子表格中的数据合并到第三个 Excel 电子表格中的最佳/最简单方法是什么?电子表格 1 将仅包含用户名。电子表格 2 将包含大量信息,包括名字、姓氏、部门、用户名等。第三个电子表格需要显示电子表格 1 中的用户名以及电子表格 2 中的名字、姓氏和部门。

答案1

您要使用的函数是VLOOKUP。具体操作将略微取决于工作表的排列方式,但所有函数都遵循相同的语法:

=VLOOKUP( lookup value, table array, column index number, range lookup)

  • lookup value是您要搜索的数据。
  • table array定义要从中提取数据的单元格,包括包含搜索目标的列
  • column index numbertable array是您想要从中提取信息的列的索引。(例如:对于 A:E 的数组,D 列将是4。)
  • range lookup是一个 TRUE/FALSE 选项,用于指定近似匹配是否可接受,还是需要精确匹配。为了简单起见,我总是将其设置为FALSE。如果您需要更多详细信息,请在 Excel 中按 F1。

公式的格式会略有不同,具体取决于您的数据是否全部位于同一工作簿中。下面我将分别举例说明。

重要的提示:您使用的搜索词lookup value 必须在第一列中可以找到table arrayVLOOKUP 才能工作。


第一个例子:所有数据都将保存在同一个 Excel 工作簿中,但保存在不同的表格中。第一个表格标记为“用户名”,仅包含用户名。第二个表格称为“用户数据”,包含所有用户详细信息。我们将第三个表格称为“查找结果”。 “用户数据”表格包含五列,A:E

  1. 确保“用户数据”电子表格在 A 列中包含所有用户名。
  2. 将所有用户名从“用户名”复制到“查找结果”。
    • 我假设您正在使用标题行,因此“查找结果”中的第一个用户名将出现在 A2 中。
  3. B2 中“查找结果”的公式应为: =VLOOKUP(A2,'User Data'!A:B,2,FALSE)
  4. B3 的“查找结果”公式应为: =VLOOKUP(A3,'User Data'!A:B,2,FALSE)
  5. “查找结果”中 C2 的公式应为: =VLOOKUP(A2,'User Data'!A:C,3,FALSE)

现在你应该已经看出了其中的规律。对于每一列,你应该能够在第一个单元格(例如:B2)中写入 VLOOKUP 公式,然后将公式填充到工作表的其余部分。但是,复制并粘贴公式穿过列不是那么简单——您需要更新table arraycolumn index number值。


第二示例:每个数据集都保存在自己的 Excel 工作簿中。工作簿中的工作表名称是默认的(即:第一个工作表是“Sheet1”)。工作簿文件名为“用户名.xlsx”、“用户数据.xlsx”和“查找结果.xlsx”。这些都位于名为“我的电子表格”的文件夹中,该文件夹位于名为“我”的用户的桌面上。

  1. 确保“User Data.xlsx”表的 A 列中包含所有用户名。
  2. 将所有用户名从“Usernames.xlsx”复制到“Lookup Results.xlsx”。
    • 再次假设您使用标题行,它将从 A2 开始。
  3. “查找结果.xlsx”中 B2 的公式应为=VLOOKUP(A2,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:B,2,FALSE)
  4. “查找结果.xlsx”中 B3 的公式应为=VLOOKUP(A3,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:B,2,FALSE)
  5. “查找结果.xlsx”中 C2 的公式应为=VLOOKUP(A2,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:C,3,FALSE)

再次,您现在应该能够看到这里的模式。根据需要沿行和列进行剪切/粘贴/调整,就大功告成了。


这里需要注意的另一件事是,此工作表不会自动更新“用户名”数据的变化。可以使用此方法提取“用户数据”工作表的更改,但如果您想要跟踪“用户名”的更改,则需要更高级的技术。

答案2

Vlookup 和类似功能仅在两张表中的数据相同时才有效。
我相信您需要的是 Excel 的模糊逻辑插件。这将允许您根据几个不同的参数找到相似的结果。检查下载页面

答案3

HLOOKUP/VLOOKUP - 使用公式从电子表格 1 中获取用户名,然后使用用户名作为键,并使用电子表格 2 作为 HLOOKUP/VLOOKUP 一个实例的查找矩阵(我不知道哪个是哪个,因为我使用的是非英文 Excel 版本)。

答案4

您可以使用 Excel 文件查询:

  • 为电子表格 1 中的数据集定义名称(公式选项卡 -> 定义名称)
  • 为电子表格 2 中的数据集定义名称
  • 在电子表格 1 中,转到“数据”选项卡,选择“来自其他来源”,然后从下拉列表中选择“来自 Microsoft Query”
  • 选择另一个电子表格文件并确认您要手动合并列
  • 在以下窗口“从 Excel 文件查询”中,将第一个数据集的“用户名”列拖放到第二个数据集的“用户名”列中 - 将在这些列之间创建链接
  • 转到“文件”菜单,单击“将数据返回到 MS Office Excel”,将弹出“导入数据”对话框
  • 选择要导入匹配数据的工作表
  • 单击“确定” - 您应该会看到来自两个电子表格的列匹配的数据

相关内容