办公软件 > Excel > 正文

Excel VLookUp函数的操作实例(查找两列重复或两表相同数据/两表数据对比)

亮术网 2019-08-13 本网原创

在 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所示:

Excel VLookUp函数的操作实例,查找重复数据

图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所示:

Excel VLookUp函数查找两个表格相同数据

图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所示:

Excel VLookUp函数两表数据对比

图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。