对比两列数据,找相同项和不同项是实际工作中经常遇到的需求。今天和大家分享3个公式,分别是:
提取两列数据相同的数据
提取左侧列有,右侧列没有的数据
提取左侧列没有,右侧列有的数据
我们的示例数据如下,是豆瓣 TOP 20 和 IMDB TOP 20 电影的名称:
通用公式如下:
=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)))&''
通用公式如下:
=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单元格输入后,向下复制。
通用公式如下:
=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
函数参数的判断,通过不同的判断公式,可以返回不同的结果。
联系客服