当前位置：首页 > 虚拟主机 > 正文

如何根据2列数据对比重复标注颜色？Excel对比两列数据高亮显示重复值

admin
虚拟主机
2026-06-28
12

在Excel或WPS表格等数据处理软件中，快速识别两列数据中的重复项是数据清洗和核对的高效手段，通过条件格式功能，我们可以直观地将两列中共同存在的数值或文本高亮显示，从而避免人工逐一比对带来的疏漏,以下是具体的操作逻辑与步骤说明。

核心操作逻辑

实现这一功能的关键在于利用“条件格式”中的“新建规则”，并配合公式来定义高亮条件，我们需要告诉软件：当当前单元格的值同时存在于另一列时，就应用特定的填充颜色，这种方法不仅适用于数值，也适用于文本字符串,且能自动适应数据量的变化。

具体操作步骤

假设我们需要对比 A列和 B列的数据,并将两列中重复的内容标红。

选中目标区域
选中你想要进行比对的整个数据区域，如果你要对比A2:A100和B2:B100，你可以同时选中这两个区域（按住Ctrl键分别选中，或者先选中A2:B100整个矩形区域）。
打开条件格式
在菜单栏中找到“开始”选项卡，点击“条件格式”，然后选择“新建规则”。
选择规则类型
在弹出的对话框中，选择“使用公式确定要设置格式的单元格”。
输入对比公式
这是最关键的一步，假设你选中的区域是从A2开始到B100结束，我们需要编写一个公式,判断当前单元格是否在另一列中存在。

输入以下公式：
=COUNTIF($B$2:$B$100, A2)>0
注意：这里的 $B$2:$B$100 是你要对比的另一列的绝对引用范围，A2 是当前选中区域左上角的第一个单元格（相对引用），如果你选中的是整个A2:B100，这个公式会分别对A列和B列生效。

为了更通用的写法，确保公式能同时覆盖两列，建议使用以下逻辑：
如果选中了 A2:B100,公式可以写为：
=COUNTIF($A$2:$A$100, $A2)>0 + COUNTIF($B$2:$B$100, $A2)>0
但更简单且常用的方法是分别对两列设置规则，或者使用一个跨列的COUNTIF,最稳妥的方式是：
- 针对A列重复项：选中A2:A100，公式为 =COUNTIF($B$2:$B$100, A2)>0
- 针对B列重复项：选中B2:B100，公式为 =COUNTIF($A$2:$A$100, B2)>0
如果你希望一次性选中A2:B100并让两列都生效，可以使用以下公式（假设选中区域左上角是A2）：

=OR(COUNTIF($B$2:$B$100, A2)>0, COUNTIF($A$2:$A$100, A2)>0)
注：不同版本的Excel对混合引用的处理略有差异，建议分别对两列设置规则最为稳妥。
设置格式
点击“格式”按钮，切换到“填充”选项卡，选择一种醒目的颜色（如浅红色或黄色），然后点击“确定”。
完成应用
再次点击“确定”关闭新建规则对话框,此时两列中重复的数据就会自动带上你选择的背景色。

注意事项与常见问题

绝对引用与相对引用：在公式中，对比的范围（如 $B$2:$B$100）必须使用绝对引用（加上$符号），以防止拖动公式时范围偏移；而当前单元格的引用（如 A2）必须是相对引用,以便软件能逐行判断。
数据格式一致性：确保两列数据的格式一致，如果A列是“文本型数字”，而B列是“数值型数字”，COUNTIF可能无法识别为重复，可以使用“分列”功能或VALUE()函数统一格式。
空格干扰：文本中可能包含不可见的空格，导致看似相同的文本不被识别为重复，可以使用TRIM()

函数清理数据后再进行比对。