Excel VLookUp函数的操作实例(查找两列重复或两表相同数据/两表数据对比)
在 Excel 中,查找重复数据(即重复项)可以用VLookUp函数。用VlookUp函数查找重复数据可分为一列数据一些项相同另一列数据不同(即一对多)、两列数据对应行都相同,两个表格一个或几个字段相同及所有字段(即一行)都相同几种情况。一对多的情况在前面的篇章《Excel VLookUp函数的使用方法,含逆向查找、一对多查找和近似匹配实例》中已经介绍过,本篇将介绍查找两列重复数据和两表相同数据(即两表数据对比)。
用VLookUp函数查找两表相同数据介绍了两个操作实例,一个是只要求两个表的一列数据有相同项,另一个是要求两个表所有列(即一行)都相同。
一、Excel VLookUp函数的操作实例一:查找两列的重复数据
1、假如要查找两列价格的重复数据。双击 C2 单元格,把公式 =IFERROR(VLOOKUP(B2,$A$2:$A$8,1,0),"") 复制到 C2,按回车,返回查找结果 89,说明第二行的 A 列与 B 列数据相同;选中 C2 单元格,把鼠标移到 C2 右下角的单元格填充柄上,鼠标变为十字架(+)后,双击左键,则筛选出所有两列价格重复数据;操作过程步骤,如图1所示:
图1
提示:如果只要求查找两列对应行的重复数据,公式要改为 IFERROR(VLOOKUP(B2,A2,1,0),"")。
2、公式 =IFERROR(VLOOKUP(B2,$A$2:$A$8,1,0),"") 说明:
A、B2 为 VLookUp 的查找值,为相对引用,往下拖会变为 B3、B4、……;$A$2 为对列和行的绝对引用,往下拖不会变为 A3、A4、……,$A$8 与 $A$2 是一个意思。
B、$A$2:$A$8 为查找范围,表示在 A2 至 A8 查找 B2,往下拖后 B2 变为 B3、B4、……,也同样在 A2 至 A8 查找 B2。
C、1 为 VLookUp 返回列号,0 表示精确匹配;当公式在 C2 时,VLOOKUP(B2,$A$2:$A$8,1,0) 意思是在 A2 至 A8 中查找 B2(即 89),在 A2 找到后,返回查找范围的第一列,即返回 A 列的 89。
D、IfError 是错误返回函数,如果 VLOOKUP(B2,$A$2:$A$8,1,0) 返回错误值,则返回空值,否则返回 VLOOKUP(B2,$A$2:$A$8,1,0) 的返回值。
提示:$A$2:$A$8 也可以用一列表示,即 A:A,但这样写有两问题,一个低版本 Excel 不支持,另一个是执行速度可能慢一点。
二、Excel VLookUp函数的操作实例二:查找两个表格相同数据(两表数据对比)
假如要查找相同数据的两个表格分别在“水果表1.xlsx”和“水果表2.xlsx”文档中,Sheet 名称分别为“水果销量表1”和“水果销量表2”,以下是具体的查找方法:
(一)只要求一列有数据相同
1、在“水果表1.xlsx”窗口,选中 E2 单元格,输入 1,把鼠标移到 E2 右下角的单元格填充柄上,鼠标变为十字架(+)后,双击左键,则有数据的行最后一列都填上 1;选择“视图”选项卡,单击“切换窗口”,在弹出的菜单中选择“水果表2”,切换到“水果表2”窗口;双击 E2 单元格,把公式 =IFERROR(VLOOKUP(A2,[水果表1.xlsx]水果销量表!A$2:E$7,5,0),"") 复制到 E2,按回车,返回空值,选中 E2,把鼠标移到 E2 右下角的单元格填充柄上,鼠标变为十字架后,双击左键,则返回所有行的查找结果,其中有 1 的表示与“水果表1”中的表格对应行重复;操作过程步骤,如图2所示:
图2
2、说明:
A、在“水果销量表1”的 E 列添加 1 用于在的“水果销量表2”的 E 列标记两表相同数据,即如果两个表有相同数据,在“水果销量表2”的 E 列显示 1。
B、公式 =IFERROR(VLOOKUP(A2,[水果表1.xlsx]水果销量表1!A$2:E$7,5,0),"") 中,查找值为 A2(水果销量表2的 A2),查找范围为 [水果表1.xlsx]水果销量表1!A$2:E$7,由于是在“水果表2.xlsx”文档中的工作表查找“水果表1.xlsx”文档的工作表,所以查找范围前要加文档名和工作表,即 [水果表1.xlsx]水果销量表1,且在工作表与引用单元格之间要加半角感叹号(!);5 是返回列号,即返回“水果销量表1”的第 5 列,也就返回“重复标记”列。
提示:如果在“水果销量表2”的 E2 中输入公式,嫌输入文档名和工作表名麻烦,输入 =IFERROR(VLOOKUP(A2, 后,切换到“水果表1.xlsx”窗口,单击一下工作表名称“水果销量表1”,就会自动输入 [水果表1.xlsx]水果销量表1!,再框选 A2:A7,就会自动输入 $A$2:$A$7,切换回“水果表2.xlsx”后,公式已经变为 =IFERROR(VLOOKUP(A2,[水果表1.xlsx]水果销量表1![水果表1.xlsx]水果销量表1!$A$2:$A$7,再输入其它参数即可。
(二)要求所有列都有数据相同,即一行数据都相同
1、假如要求两个工作表一行数据完全相同才视为相同。同样在“水果销量表1”最一列添加 1 作为重复标记;然后切换到“水果销量表2”,双击 E2 单元格,把公式 =IFERROR(VLOOKUP(A2&B2&C2&D2,IF({0,1},[水果表1.xlsx]水果销量表1!E$2:E$7,[水果表1.xlsx]水果销量表1!A$2:A$7&B$2:B$7&C$2:C$7&D$2:D$7),2,0),"") 复制到 E2,按回车,返回空值,接着用双击 E2 单元格填充柄的方法返回剩余的查找结果,显示 1 的表示该行数据相同;操作过程步骤,如图3所示:
图3
2、公式说明:
=IFERROR(VLOOKUP(A2&B2&C2&D2,IF({0,1},[水果表1.xlsx]水果销量表1!E$2:E$7,[水果表1.xlsx]水果销量表1!A$2:A$7&B$2:B$7&C$2:C$7&D$2:D$7),2,0),"")
A、A2&B2&C2&D2 为查找值,是把 A2、B2、C2、D2 四个单元格的数据连结在一起,即“香蕉浆果4.51258”。
B、IF({0,1},[水果表1.xlsx]水果销量表1!E$2:E$7,[水果表1.xlsx]水果销量表1!A$2:A$7&B$2:B$7&C$2:C$7&D$2:D$7) 的作用是把“水果销量表1”的查找范围 A 列到 D 列每行的数据连结为一列和把返回列 E 列作为独立的一列;如果不明白,请参考《Excel VLookUp怎么用If或If{0,1}实现两个或三个条件的多条件查找》一文,里面有详解。
提示:如果只要求两个表两列或三列有相同数据,例如只要求 A、B 和 C 列有相同数据,只需把 A2、B2 和 C2 连续起来,即 A2&B2&C2。
-
相关阅读
- Excel Search函数和SearchB函数模糊查找的用法1
- Excel Find函数和FindB函数使用方法的10个实例,含
- Excel SumIf函数条件带?/*、Average和数组多条
- Excel SubTotal函数包含忽略值的使用方法及与OffSet
- Excel AverageIf函数使用方法的7个实例,包含条件
- Excel两个以上条件统计CountIfs函数的使用方法,含与
- Excel Match函数的使用方法的8个实例,含与Sum、Of
- Excel SumIfs函数基本与用日期和数组组合同一列多条
- Excel Sum函数基本及与if、OffSet、Match、Rig
- Excel If函数单条件与用嵌套或And/*、OR/+组合多
- Excel VLookUp函数的使用方法,含逆向查找、一对多
- Excel CountIf函数的使用方法,包含与If、Sum组合