Excel 公式:对比两列数据,提取相同和不同

对比两列数据,找相同项和不同项是实际工作中经常遇到的需求。今天和大家分享3个公式,分别是:

  1. 提取两列数据相同的数据
  2. 提取左侧列有,右侧列没有的数据
  3. 提取左侧列没有,右侧列有的数据

我们的示例数据如下,是豆瓣 TOP 20 和 IMDB TOP 20 电影的名称:

公式1:提取两列都有的数据

通用公式如下:

=INDEX(左侧列,SMALL(IF(COUNTIF(右侧列,左侧列数据区域)>0,ROW(左侧列数据区域),大于总数据的一个数字), ROW(A1)))&""

上述公式是数组公式,使用时需要以三键输入(Ctrl+Shift+Enter)。

本例中的公式如下:

=INDEX(B:B,SMALL(IF(COUNTIF(C:C,$B$3:$B$22)>0,ROW($B$3:$B$22),1000), ROW(A1)))&""

 

公式2:提取仅在左侧列的数据

通用公式如下:

=INDEX(左侧列,SMALL(IF(COUNTIF(右侧列,左侧列数据区域)=0,ROW(左侧列数据区域),大于总数据的一个数字), ROW(A1)))&""

上述公式是数组公式,使用时需要以三键输入(Ctrl+Shift+Enter)。与第一个公式的差异在 COUNTIF(右侧列,左侧列数据区域)=0处,在这里是等于0(=0)。

本例中的公式如下:

=INDEX(B:B,SMALL(IF(COUNTIF(C:C,$B$3:$B$22)=0,ROW($B$3:$B$22),1000), ROW(A1)))&""

在E3单元格输入后,向下复制。

 

公式3:提取仅在右侧列的数据

通用公式如下:

=INDEX(右侧列,SMALL(IF(COUNTIF(左侧列,右侧列数据区域)=0,ROW(右侧列数据区域),大于总数据的一个数字), ROW(A1)))&""

上述公式是数组公式,使用时需要以三键输入(Ctrl+Shift+Enter)。与第二个公式的差异是对应的左右列和区域相反。

本例中的公式如下:

=INDEX(C:C,SMALL(IF(COUNTIF(B:B,$C$3:$C$22)=0,ROW($C$3:$C$22),1000), ROW(A1)))&""

在E3单元格输入后,向下复制。

 

最后

这个是一个万金油公式,用于一对多查询的场景。公式的核心是SMALL函数的第一个IF函数参数的判断,通过不同的判断公式,可以返回不同的结果。

转载注明:文章转载自「懒人Excel - www.lanrenexcel.com

您可能还喜欢...

订阅评论
提醒
49 评论
最新
最旧 最多投票
内联反馈
查看所有评论
你瞅瞅
2 月 前

如果两列值行数(个数)不同呢?公式需要做什么改变吗?

陶陶瑞塔
2 月 前

=INDEX(B:B,SMALL(if(COUNTIF(C:C,$B$2:$B$7)>0,ROW($B2:$B7,1000),ROW(A1)))&””用三键输入后显示公式错误呢

我测试下
6 月 前

没反应:=INDEX(B:B,SMALL(IF(COUNTIF(C:C,$B$3:$B$24)>0,ROW($B$3:$B$24),1000),ROW(A1)))&””

凡心一点
8 月 前

你这个SMALL最后的参数害得大家好惨啊。
=INDEX(B:B,SMALL(IF(COUNTIF(C:C,$B$3:$B$22)>0,ROW($B$3:$B$22),1000), ROW(A1)))”ROW(A1)=1,即SMALL变成了永远只反馈第一个最小行号值。此处实际跑起来就全是肖申克的救赎。单独跑SMALL部分就会发现全是行号3″
正确示例应为:=INDEX(B:B,SMALL(IF(COUNTIF(C:C,$B$3:$B$22)>0,ROW($B$3:$B$22),1000),ROW($B$3:$B$22)))),这样INDEX才会正确依次序引用B列中的3,6,10,11,19行的内容。

感觉这是个bug?似乎数组公式效果没有对SMALL函数生效,它是以整数组公式中的IF函数给出的值作为引用参数。

10 月 前

{=INDEX(A:A,SMALL(IF(COUNTIF(B:B,$A$2:$A$22)>0,ROW($A$2:$A$22),1000),ROW(A1)))&””},请问哪里不对

10 月 前

为什么搞了以后没有反应

yoyo
4 月 前
回复给  懒人Excel

是按这三个键以后再把公式编辑进去吗,还是什么意思呢?因为编辑之后,文件里显示就是公式本身,没有变成数字,按了这三个键,也没有反应

毛毛好学
10 月 前

这个函数嵌套还是没太懂,能出一个讲解视频吗

毛毛好学
10 月 前

这个场景在使用PQ后就简单多了,哈哈

空 白
11 月 前

这个函数都无法使用,显示输入函数过多

滨湖老王
1 年 前

提取两列数据相同的数据,并在相同数据的左侧那行显示,该如何操作

晶鎏*
1 年 前

请问,输入公式=INDEX(D1:D6,SMALL(IF(COUNTIF(E1:E6,$B$1:$B$6)>0,ROW($B$1:$B$6),1000),ROW(A1)))&””以ctrl+shift+enter结束出现的是#REF是什么原因?

大猫哥哥
1 年 前

老板,没有讲解呀

图图K
1 年 前

写的很详细,点赞

okay
2 年 前

可以留下素材 练习吗

lucky
2 年 前

你好,提取仅在左侧列的 第一遍输入之后 往下拉 出现#N/A 是什么意思呢?

xiaofen
2 年 前

您好,麻烦问下,我按照上面的函数公式,得出来的全是同样的结果,是什么情况呀?

xiaofen
2 年 前
回复给  懒人Excel

是三键结束,按照你上面的例子,返回的结果全部是肖申克救赎

撒大苏打
2 年 前

很好

丁海宁
2 年 前

非常好

ychiou
2 年 前

用格式条件

Ohy
2 年 前

三件输入是什么意思呢

ALISON
2 年 前

太实用了!每次遇到这样的情况,都是使用两次vlookup。今天又学到一招,一个公式解决,太棒了。

熊字饼干
2 年 前

上述公式是数组公式,使用时需要以三键输入(Ctrl+Shift+Enter.
这是什么意思啊

15761635831
3 年 前

ROW(A1)))&“”部分是什么意思

sendrow
3 年 前

关于公式的思路可以提供详细的解析吗

tomc
3 年 前

有附件下载吗?

49
0
希望看到您的想法,请您发表评论x