Excel 公式:对比两列数据,提取相同和不同
对比两列数据,找相同项和不同项是实际工作中经常遇到的需求。今天和大家分享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
函数参数的判断,通过不同的判断公式,可以返回不同的结果。
这个函数嵌套还是没太懂,能出一个讲解视频吗
后期计划推出公式原理课程,欢迎关注。
这个场景在使用PQ后就简单多了,哈哈
是的,PQ也能实现此需求。不过每个方法的适用场景不同,按照自己的需要选择合适的方法。
这个函数都无法使用,显示输入函数过多
会不会是少输入了括号的原因
提取两列数据相同的数据,并在相同数据的左侧那行显示,该如何操作
这个相对简单一些。直接使用COUNTIF函数统计在另一列出现的次数,如果大于0,就是表示相同,IF函数显示即可。
请问,输入公式=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是什么原因?
INDEX 函数的第一个参数与 COUNTIF 函数第二个参数的引用列不一致。
老板,没有讲解呀
感谢反馈,后续会加上
写的很详细,点赞
可以留下素材 练习吗
以后的文章争取分享对应的素材,谢谢建议
你好,提取仅在左侧列的 第一遍输入之后 往下拉 出现#N/A 是什么意思呢?
公式需要以三键(ctrl+shift+enter)输入
您好,麻烦问下,我按照上面的函数公式,得出来的全是同样的结果,是什么情况呀?
注意公式需要三键(ctrl+shift+回车)结束
是三键结束,按照你上面的例子,返回的结果全部是肖申克救赎
贴一下你的公式
很好
非常好
用格式条件
三件输入是什么意思呢
三键输入时数组公式的输入方式,而且必须以三键输入
太实用了!每次遇到这样的情况,都是使用两次vlookup。今天又学到一招,一个公式解决,太棒了。
上述公式是数组公式,使用时需要以三键输入(Ctrl+Shift+Enter.
这是什么意思啊
数组公式是特殊的公式类型,特点就是输入时,需要以三键输入(Ctrl+Shift+Enter)。关于数组公式我会再写一篇介绍文
ROW(A1)))&“”部分是什么意思
这里是把值变成文本
关于公式的思路可以提供详细的解析吗
几个公式均用到万金油公式,可以看一下这篇:https://www.lanrenexcel.com/excel-keyword-return-multiple-values/
后期公式文章会发解决思路的
有附件下载吗?
暂时没有,后续会提供下载