首页 > Excel专区 > Excel技巧 >

excel 如何快速比对两列数据相同点和不同点 每个人都会用到

Excel技巧 2021-11-25 09:46:54

分享一篇关于Excel数据比对的文章,包括如何快速得到两列数据之间的相同部分和差异部分,这个应用场景在实际工作中是很常见的。请耐心观看,阅读仅需五分钟。

一:比对两列数据的相同部分。

Excel应用 如何快速比对两列数据相同点和不同点 每个人都会用到

如图所示,如何得到在物理和英语竞赛中都获奖的选手学号?这里介绍两种方式。

1:最快方式:条件格式>突出显示单元格规则>重复值

Excel应用 如何快速比对两列数据相同点和不同点 每个人都会用到

解析:

选中AB两列,通过条件格式>突出显示单元格规则>重复值,就能轻松搞定,是不是特别快啊?下面看看第二种方式。

2:条件格式>新建规则>使用公式确定要设置格式的单元格

Excel应用 如何快速比对两列数据相同点和不同点 每个人都会用到

解析:

首先增加两个辅助列,辅助列1的公式为COUNTIF(B:B, A2),表示A列是否在B列中存在;

辅助列2的公式为COUNTIF(A:A, B2),表示B列是否在A列中存在。

然后通过条件格式化>新建规则>使用公式确定单元格格式,如果C列值为1则A列单元格变绿,如果D列值为1则B列单元格变绿。

通过设定公式的方式,虽然繁琐了一点,但是通用性和扩展性更好。

二:比对数据之间的差异

下面我们直接看一个稍微难一点的例子,假设同时满足A=C并且B=D的时候,AB和CD才算相同,否则算作差异。如何标识出AB列和CD列有差异的单元格呢?

Excel应用 如何快速比对两列数据相同点和不同点 每个人都会用到

下面直接看动画演示吧。

Excel应用 如何快速比对两列数据相同点和不同点 每个人都会用到

解析:

首先,增加两列辅助列,E列上公式为COUNTIFS(C:C, A2,D:D, B2),F列上公式为COUNTIFS(A:A, C2,B:B, D2)。分别用于判断AB列是否存在于CD列以及CD列是否存在于AB列。

然后,按照动画演示,在AB上设定条件公式:$E2=0,在CD上设定条件公式:$F2=0,满足条件时标识出相应颜色。

注意:公式中$E2=0以及$F2=0,必须带上$符号。

扩展:

请在A=C但是B!=D的情况,标示颜色。如果A根本找不到C的情况下不用标颜色。

Excel应用 如何快速比对两列数据相同点和不同点 每个人都会用到

解析:

E列上公式为:IF(COUNTIF(C:C, A2)=0, 1, IF(B2 = VLOOKUP(A2, $C$2:$D$12, 2, FALSE), 1, 0))

F列上公式为:IF(COUNTIF(A:A, C3)=0, 1, IF(D3 = VLOOKUP(C3, $A$2:$B$12, 2, FALSE), 1, 0))

后续一样的,都是通过条件格式化设定公式。小伙伴们可以自己试一下。

总结:

这一期主要讲了Excel中如何比对两列数据的相同部分和差异部分,用到的函数有:COUNTIF,用到的功能点为:

条件格式化


标签: Excel比对数据相同点Excel比对数据不同点Excel差异

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.