Excel 列比较:查找匹配项和差异!
本指南深入探讨了在 Excel 中比较两列的各种方法,这是许多用户的常规任务。 无论你是 逐行比较, 逐个细胞, 突出显示匹配项或 查明差异,本教程解决了您可能遇到的各种场景。 我们针对大多数情况制定了解决方案,旨在增强您的 Excel 体验。 请注意: 您可以使用右侧表格👉快速导航到所需内容。
逐行比较两列
下面是一个数据集(范围 B2:C8),我需要检查同一行中 B 列中的名称是否与 C 列中的名称相同。
本部分提供了两个示例来解释如何逐行比较两列
示例 1:比较同一行中的单元格
通常,如果要逐行比较两列以进行完全匹配,则可以使用以下公式:
=B2=C2
媒体 Enter 键并将填充手柄向下拖动到单元格 D8。 如果公式返回 TRUE,则同一行两列的值完全相同; 如果返回 FALSE,则值不同。
或者您可以使用 IF 函数显示特定文本以显示匹配或不匹配,如下所示:
=IF(B2=C2,"Match","No match")
结果可能如下所示:
示例 2:比较同一行中的单元格(区分大小写)
如果要逐行比较两列是否区分大小写,可以使用组合 IF 和 EXACT 函数的公式。
IF(EXACT(B2,C2), "Match", "Mismatch")
媒体 Enter 键获取第一个结果,然后将自动填充手柄拖到单元格D8。
在上面的公式中,您可以将文本“Match”和“Mismatch”更改为您自己的描述。
比较同一行中的多个列
有时,您可能想要比较同一行中的两列以上,例如如下图所示的数据集(范围 B2:D7)。 在本节中,它列出了比较多列的不同方法。
在这里,它分为两部分,以提供有关比较同一行中的多个列的详细说明。
示例1:比较多列并在同一行的所有单元格中查找匹配项
要查找同一行中各列的完全匹配,下面的公式可以帮助您。
=IF(COUNTIF($B2:$D2, $B2)=3, "Full match", "Not")
媒体 Enter 键获取第一个比较结果,然后将自动填充手柄拖到单元格E7上。
- 该公式比较列时不区分大小写。
- 在公式中,3是列数,您可以更改它以满足您的需要。
示例 2:比较多列并在同一行的任意两个单元格中查找匹配项
有时,您想找出同一行中的任何两列是否匹配,可以使用下面的IF公式。
=IF(COUNTIF($B2:$D2,$B2)>=2,"Match","No match")
媒体 Enter 键,然后将填充手柄拖到单元格E7上。
- 此公式不支持区分大小写。
- 式中,2表示在同一行的任意两列中查找匹配项。 如果要在同一行的三列中查找匹配项,请将 2 更改为 3。
逐行比较两列或多列并突出显示匹配或差异
如果您想要比较两列或多列并突出显示匹配或差异,本节将介绍两种处理这些工作的方法。
有两个例子来比较和突出匹配和差异
示例 1:比较两列并突出显示同一行中的所有单元格或同一行中的任意两个单元格中的完全匹配项
要突出显示所有单元格或同一行中任意两个单元格中的匹配项,条件格式功能可以为您提供帮助。
1.选择您要使用的范围,然后单击 主页 > 条件格式 > 新规则.
2.在 新格式规则 对话框
- 使用公式来确定要格式化的单元格 来自 选择规则类型 部分
- 在中使用下面的公式 格式化此公式为真的值 文本框。
=COUNTIF($B2:$D2, $B2)=3
- 点击 格式.
3.在 单元格格式 对话框,然后选择一种填充颜色或其他单元格格式以突出行。 点击 OK > OK 关闭对话框。
现在,只有所有单元格都匹配的行才会突出显示。
示例 2:比较两列并突出显示同一行中的差异
如果你想突出显示同一行中的差异,即逐个比较列单元格,并根据第一列找到不同的单元格,你可以使用Excel内置功能-Go To Special。
1.选择要突出显示行差异的范围,然后单击 主页 > 查找和选择 > 定位条件.
2.在弹出 定位条件 对话框中,选择 行差异 选项。 点击 OK.
现在已选择行差异。
3 现在保持选中单元格,单击 主页 > 填充颜色 从下拉菜单中选择一种颜色。
比较单元格中两列的唯一数据和重复数据
在这部分中,数据集(范围B2:C8)如下所示,您想要同时查找B列和C列中的所有值,或者只查找B列中的值。
本节列出了比较单元格中两列的 4 种不同方法,您可以根据需要选择以下方法之一。
- 示例1:比较单元格中的两列并在另一列中显示比较结果
- 示例 2:比较单元格中的两列,并使用方便的工具选择或突出显示重复或唯一的数据
- 示例 3:比较单元格中的两列并突出显示重复或唯一数据
- 示例 4:比较单元格中的两列并在另一列中列出完全相同的重复项
示例1:比较单元格中的两列并在另一列中显示比较结果
在这里,您可以使用与 IF 和 COUNTIF 函数结合的公式来比较两列,并找到 B 列中但 C 列中不存在的值。
=IF(COUNTIF($C$2:$C$8, $B2)=0, "No in C", "Yes in C")
媒体 Enter 键,然后将自动填充手柄拖到单元格D8中。
- 此公式比较两列,不区分大小写。
- 您可以将描述“No in C”和“Yes in C”更改为其他。
示例 2:比较单元格中的两列,并使用方便的工具选择或突出显示重复或唯一的数据
有时,在比较两列之后,您可能会对匹配项或差异进行其他操作,例如选择,删除,复制等。 在这种情况下,方便的工具- 选择相同和不同的单元格 of Kutools for Excel 可以直接选择匹配或差异以更好地进行下一个操作,也可以直接突出显示值。
后 免费安装 Kutools for Excel,单击“ 库工具 > 选择 > 选择相同和不同的单元格。 然后在“选择相同和不同单元格”对话框中,请执行以下操作:
- 在 在中查找值 和 根据 部分, 选择两列 分别用来比较。
- 每一行 选项。
- 相同的值 or 不同的价值观 如你所需。
- 指定是否为所选值着色并单击 OK.
弹出一个对话框提醒您已找到的值的数量,单击 OK 关闭对话框。 同时,已经选择了值,现在您可以删除或复制或执行其他操作。
如果勾选“填充背景色”和“填充字体颜色”复选框,结果将如下所示:
- 如果要比较区分大小写,请勾选 区分大小写 选项。
- 该工具支持比较不同工作表中的两列。 单击此处了解有关选择相同和不同单元格的更多详细信息.
- 如果您对这个工具感兴趣, 单击此处免费下载并试用 30 天。
示例 3:比较单元格中的两列并突出显示重复或唯一数据
Excel 中的条件格式功能非常强大,您可以使用它来比较单元格中的两列,然后根据需要突出显示差异或匹配项。
1.选择要比较的两列,然后单击 主页 > 条件格式 > 加亮单元格规则 > 重复的值.
2. 在弹出的“重复值”对话框中, 选择突出显示格式 您需要从下拉列表中获取值。
3。 点击 OK。 然后,突出显示了两列中的重复项。
示例 4:比较单元格中的两列并在另一列中列出完全相同的重复项
如果您想在区分大小写的情况下逐个单元格比较两列后列出另一列中的匹配值,下面的宏代码可以帮助您。
1.启用要比较两列的工作表,然后按 Alt + F11 键显示 Microsoft Visual Basic应用程序 窗口。
2。 点击 插页 > 模块 ,在 Microsoft Visual Basic应用程序 窗口。
3.然后将以下代码复制并粘贴到新的空白模块脚本中。
VBA:比较两列后,在next列中列出重复项
Sub ExtendOffice_FindMatches()
'UpdatebyKutools
Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range
Dim xIntSR, xIntER, xIntSC, xIntEC As Integer
On Error Resume Next
SRg:
Set xRgC1 = Application.InputBox("Select first column:", "Kutools for Excel", , , , , , 8)
If xRgC1 Is Nothing Then Exit Sub
If xRgC1.Columns.Count <> 1 Then
MsgBox "Please select single column"
GoTo SRg
End If
SsRg:
Set xRgC2 = Application.InputBox("Select the second column:", "Kutools for Excel", , , , , , 8)
If xRgC2 Is Nothing Then Exit Sub
If xRgC2.Columns.Count <> 1 Then
MsgBox "Please select single column"
GoTo SsRg
End If
Set xWs = xRg.Worksheet
For Each xRgF1 In xRgC1
For Each xRgF2 In xRgC2
If xRgF1.Value = xRgF2.Value Then xRgF2.Offset(0, 1) = xRgF1.Value
Next xRgF2
Next xRgF1
End Sub
4。 按 F5 键运行代码,会一一弹出两个对话框,供您分别选择两列。 然后点击 OK > OK.
匹配项已自动列在两列的右列中。
示例1:比较两列并提取完全匹配的数据
例如,有两个表,现在您要比较B列和E列,然后从C列中找到相对价格并将它们返回到F列中。
这里介绍了一些有用的公式来解决这项工作。
在单元格F2(您要在其中放置返回值的单元格)中,使用以下公式之一:
=VLOOKUP(E2,$B$2:$C$8,2,0)
Or
=INDEX($B$2:$C$8,MATCH(E2,$B$2:$B$8,0),2)
媒体 Enter 键,并且找到第一个值。 然后将自动填充手柄向下拖动到单元格F6,已提取所有值。
- 公式不支持区分大小写。
- 公式中的数字 2 表示您在表数组的第二列中找到匹配的值。
- 如果公式找不到相对值,则返回错误值#N/A。
如果您对公式感到困惑,可以尝试使用方便的工具- 公式助手 of Kutools for Excel,其中包含用于解决Excel中大多数问题的多个公式。 使用它,您只需要选择范围,而无需记住公式的用法。 立即下载并试用!
示例2:比较两列并拉出部分匹配的数据
如果两个比较列之间存在一些细微差异,如下图所示,则上述方法不起作用。
在单元格F2(您要在其中放置返回值的单元格)中,使用以下公式之一:
=VLOOKUP("*"&E2&"*",$B$2:$C$8,2,0)
Or
=INDEX($B$2:$C$8,MATCH("*"&E2&"*",$B$2:$B$8,0),2)
媒体 Enter 键,并且找到第一个值。 然后将自动填充手柄向下拖动到单元格F6,已提取所有值。
- 公式不支持区分大小写。
- 公式中的数字 2 表示您在表数组的第二列中找到匹配的值。
- 如果公式找不到相对值,则返回错误值#N/A。
- 公式中的*为通配符,用于表示任意字符或字符串。
比较两列并找到缺失的数据点
假设有两列,列B较长,列C较短,如下图所示。 与B列相比,如何找出C列中丢失的数据?
示例 1:比较两列并找到缺失的数据点
如果只想在比较两列后确定缺少哪些数据,则可以使用以下公式之一:
=ISERROR(VLOOKUP(B2,$C$2:$C$10,1,0))
Or
=NOT(ISNUMBER(MATCH(B2,$C$2:$C$10,0)))
媒体 Enter 键,然后将自动填充手柄拖到单元格D10上。 现在,如果数据同时在B列和C列中,则公式返回FALSE,如果数据仅在B列中但在C列中未命中,则公式返回TRUE。
示例 2:找到缺失的数据点并将其列出在另一列中(使用方便的工具)
如果您想在比较两列之后对丢失的数据进行一些后续操作,例如在另一列中列出丢失的数据或在较短的列下方补充丢失的数据,则可以尝试使用方便的工具-选择相同和不同的单元格 of Kutools for Excel.
安装 Kutools for Excel 后,单击“ 库工具 > 选择 > 选择相同和不同的单元格。 然后在“选择相同和不同单元格”对话框中,执行以下操作:
- 在 寻找价值 部分中,选择包含完整列表的较长列。
- 在 根据 部分中,选择缺少某些数据的较短的列。
- 每一行 选项。
- 不同的价值观 选项。 点击 OK.
弹出对话框提醒您缺失数据的数量,点击 OK 关闭它。 然后选择丢失的数据。
现在你可以按 Ctrl + C 键复制选定的丢失数据,然后按粘贴它们 Ctrl + V 您可以根据需要在较短的列或另一个新列下面的键。
- 勾选 不区分大小写 “选择相同和不同的单元格”对话框中的“选项”将比较区分大小写的两列。
- 该工具支持比较不同工作表中的两列。 单击此处了解有关选择相同和不同单元格的更多详细信息.
- 如果您对这个工具感兴趣, 单击此处免费下载并试用 30 天。
示例 3:比较两列并在下面列出缺失的数据
如果要在比较两列之后在较短的列下面列出丢失的数据,则INDEX数组公式可以为您提供帮助。
在较短列的下面单元格中,假设单元格C7,键入以下公式:
=INDEX($B$2:$B$10,MATCH(TRUE,ISNA(MATCH($B$2:$B$10,$C$2:C6,0)),0))
媒体 Shift + Ctrl + Enter 键以获取第一个丢失的数据,然后向下拖动自动填充手柄,直到它返回错误值#N / A。
然后,您可以删除错误值,并且所有缺少的数据都已列在较短的列下面。
使用通配符比较两列
假设这里是B列中的数据列表,并且您要计算D列中包含“ Apple”或“ Candy”的单元格,如下所示:
要计算一个单元格是否包含一个或多个值,可以使用带通配符的公式来解决此问题。
=SUM(COUNTIF(B2,"*" & $D$2:$D$3 & "*"))
媒体 Shift + Ctrl + Enter 键进行第一次检查,然后将自动填充手柄向下拖动到单元格F8。
如果要计算包含D列中值的单元格总数,请使用以下单元格F8中的公式:
- 您还可以使用公式来计算单元格是否包含另一列中的值
此公式只需按 Enter 键,然后拖动自动填充柄即可。=SUMPRODUCT(COUNTIF(B2,"*" &$D$2:$D$3& "*"))
- 公式中,*为通配符,表示任意字符或字符串。
示例 1:比较两列是否大于或小于
您可以使用简单的公式快速查找每行中的日期1是否晚于日期2。
=IF(B2>C2,"Yes","No")
媒体 Enter 键以获取第一个比较结果,然后将自动填充手柄拖到单元格C6上以获取所有结果。
- 在 Excel 中,日期存储为数字系列,它们实际上是数字。 因此,您可以应用公式直接比较日期。
- 如果要比较每行中日期 1 是否早于日期 2,请将公式中的符号 > 更改为 <。
示例 2:比较两列,如果大于或小于则格式
如果要突出显示日期 1 列中大于日期 2 的单元格,可以使用 Excel 中的条件格式功能。
1.在B列(日期1)中选择日期,然后单击 主页 > 条件格式 > 新规则.
2.在 新格式规则 对话框中选择 使用公式来确定要格式化的单元格 ,在 选择规则类型 部分,然后键入公式
=$B2>$C2
进入的文本框 当该公式成立时,设置值的格式。
=$B2<$C2.
3。 点击 格式 按钮打开“设置单元格格式”对话框,然后根据需要选择格式类型。 点击 OK >好。
4. 然后,Date1 列中大于 Date2 列中的单元格已突出显示。
比较两列并计算匹配或差异
下面的数据集是比较和计数匹配或差异的示例。
SUMPRODUCT公式可以快速计算两列中的匹配项。
=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B8,C2:C8,0))))
媒体 Enter 获得结果的关键。
有关计算匹配和差异的更多方法,请访问此页面: 计算Excel中两列之间的所有匹配/重复项
比较两个范围
现在,您将在阅读上述方法后知道如何比较两列。 但是,在某些情况下,您可能需要比较两个范围(两个具有多个列的序列),可以使用上述方法(公式或条件格式)逐列比较它们,但是这里引入了一个方便的工具– Kutools for Excel 无需公式即可快速解决不同情况下的这项工作。
示例 1:按单元格比较两个范围
这是需要按单元格进行比较的两个范围,您可以使用 选择相同和不同的单元格 实用程序 Kutools for Excel 处理它。
免费安装 Kutools for Excel 后,单击“ 库工具 > 选择 > 选择相同和不同的单元格。 然后在弹出的“选择相同和不同单元格”对话框中,执行以下操作:
- 在 在中查找值 部分,比较两个范围后,选择要查找匹配或差异的范围。
- 在 根据 部分中,选择用于比较范围的其他范围。
- In 基于 部分中,选择 单细胞.
- 在 找到最适合您的地方 部分中,选择要选择或突出显示的单元格类型。
- 在 处理结果 部分,您可以通过填充背景颜色或字体颜色来突出显示单元格,如果不需要突出显示,请不要勾选复选框。 点击 OK.
弹出一个对话框,提醒已选择了多少个单元格/行,单击 OK 关闭它。
- 选择并突出独特的价值
- 选择并突出显示重复值
- 如果您想按行比较两个范围,您还可以应用选择相同和不同单元格功能,但在这种情况下,选择 每一行 选项。
- 勾选 不区分大小写 “选择相同和不同的单元格”对话框中的“选项”将比较区分大小写的两列。
- 该工具支持比较不同工作表中的两列。 单击此处了解有关选择相同和不同单元格的更多详细信息.
- 如果您对这个工具感兴趣, 单击此处免费下载并试用 30 天。
示例 2:如果数据顺序相同,则比较两个范围
假设范围F2:H7是一个模型,现在您要根据范围F2:H7查找范围B2:D7中的数据是否正确。
在这种情况下, 比较单元格 of Kutools for Excel 可以帮你。
免费安装 Kutools for Excel 后,单击“ 库工具 > 比较单元格。 然后在比较单元格对话框中,设置如下:
- 选择两个范围进入 在中查找值 和 根据 盒分开。
- 选择您要在其中突出显示的单元格类型 找到最适合您的地方 部分。
- 选择 突出显示类型 ,在 处理结果 部分。 单击“确定”。
弹出一个对话框,提醒已选择了多少个单元格,单击 OK 关闭它。 现在,已选择并突出显示与其他范围内的单元格不同的单元格。
- 勾选 不区分大小写 选项将比较两个区分大小写的单元格。
- 单击此处了解有关比较单元格的更多详细信息。 如果您对这个工具感兴趣, 单击此处免费下载并试用 30 天。
上面提供的信息详细介绍了如何比较 Excel 中的列。 我希望您发现它有价值且有益。 如需更多可改变数据处理方式的宝贵 Excel 提示和技巧, 潜入这里.
最佳办公生产力工具
🤖 | Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行 | 生成代码 | 创建自定义公式 | 分析数据并生成图表 | 调用 Kutools 函数... |
热门特色: 查找、突出显示或识别重复项 | 删除空白行 | 合并列或单元格而不丢失数据 | 不使用公式进行四舍五入 ... | |
超级查询: 多条件VLookup | 多值VLookup | 跨多个工作表的 VLookup | 模糊查询 .... | |
高级下拉列表: 快速创建下拉列表 | 依赖下拉列表 | 多选下拉列表 .... | |
列管理器: 添加特定数量的列 | 移动列 | 切换隐藏列的可见性状态 | 比较范围和列 ... | |
特色功能: 网格焦点 | 设计图 | 大方程式酒吧 | 工作簿和工作表管理器 | 资源库 (自动文本) | 日期选择器 | 合并工作表 | 加密/解密单元格 | 按列表发送电子邮件 | 超级筛选 | 特殊过滤器 (过滤粗体/斜体/删除线...)... | |
前 15 个工具集: 12 文本 工具 (添加文本, 删除字符,...) | 50+ 图表 类型 (甘特图,...) | 40+ 实用 公式 (根据生日计算年龄,...) | 19 插入 工具 (插入二维码, 从路径插入图片,...) | 12 转化 工具 (小写金额转大写, 货币兑换,...) | 7 合并与拆分 工具 (高级组合行, 分裂细胞,...) | ... 和更多 |
使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。 单击此处获取您最需要的功能...
Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松
- 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
- 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
- 每天将您的工作效率提高50%,并减少数百次鼠标单击!