假设a列和b列各有相同项和不同项,在c列找出不同项,在d列找出相同项,公式如下:
不同项
=IF(ROW(A1)<=COUNT(0/COUNTIF(A$1:A$4,B$1:B$4)),INDEX(A:A,LARGE(IF(COUNTIF(B$1:B$4,A$1:A$4)=0,ROW($1:$4)),ROW(A1))),INDEX(B:B,LARGE(IF(COUNTIF(A$1:A$4,B$1:B$4)=0,ROW($1:$4)),ROW(A1)-COUNT(0/COUNTIF(A$1:A$4,B$1:B$4)))))
相同项
=INDEX(A:A,LARGE(IF(COUNTIF(B$1:B$4,A$1:A$4),ROW($1:$4)),ROW(A1)))
|