Excel 列比较：查找匹配项和差异！

按行比较两列

下面是一个数据集（区域 B2:C8），我需要检查 B 列中的名称是否与同一行中的 C 列名称相同。

这一部分提供了两个示例来说明如何按行比较两列

• 示例1：比较同一行中的单元格
• 示例2：区分大小写地比较同一行中的单元格

示例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：比较多列并在同一行的所有单元格中找到匹配项
• 示例2：比较多列并在同一行的任意两个单元格中找到匹配项

示例1：比较多列并在同一行的所有单元格中找到匹配项

要在同一行的列中找到完全匹配项，以下公式可以帮助您。

=IF(COUNTIF($B2:$D2, $B2)=3, "Full match", "Not")

按 Enter 键获取第一个比较结果，然后拖动自动填充柄到单元格 E7。

示例2：比较多列并在同一行的任意两个单元格中找到匹配项

有时，您想找出同一行中是否有任意两列匹配，可以使用以下 IF公式。

=IF(COUNTIF($B2:$D2,$B2)>=2,"Match","No match")

按 Enter 键，并拖动填充柄到单元格 E7。

按行比较两列或多列并突出显示匹配项或差异

如果您想比较两列或多列并突出显示匹配项或差异，本节将介绍处理这些任务的两种方法。

有两个示例用于比较和突出显示匹配项和差异

• 示例1：比较两列并在同一行的所有单元格或任意两个单元格中突出显示完全匹配项
• 示例2：比较两列并在同一行中突出显示差异

示例1：比较两列并在同一行的所有单元格或任意两个单元格中突出显示完全匹配项

要在同一行的所有单元格或任意两个单元格中突出显示匹配项，条件格式功能可以帮助您。

1.选择您使用的范围，然后单击主页 > 使用条件格式 > 新建规则。

2. 在新建格式规则对话框中

1. 选择使用公式来确定要设置格式的单元格，来自选择规则类型部分
2. 在中使用以下公式 格式化此公式为真的值 文本框中。
   

   =COUNTIF($B2:$D2, $B2)=3

3. 单击格式。

3. 在 设置单元格格式对话框中，然后选择一种填充颜色或其他单元格格式以突出显示行。单击确定 > 确定以关闭对话框。

现在只有所有单元格匹配的行会被突出显示。

示例2：比较两列并在同一行中突出显示差异

如果您想在同一行中突出显示差异，这意味着它逐个比较列单元格，并根据第一列找到不同的单元格，您可以使用 Excel 内置功能-定位至特殊。

1.选择您要突出显示行差异的范围，然后单击主页 > 查找 &选择 > 定位至特殊。

2. 在弹出的定位至特殊对话框中，选择行差异选项。单击确定。

现在行差异已被选中。

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。

示例2：在单元格中比较两列并选择或突出显示重复或唯一数据，使用一个便捷工具

有时，在比较两列后，您可能会对匹配项或差异执行其他操作，例如选择、删除、复制等。在这种情况下，一个便捷工具 – Kutools for Excel 的选择相同 & 不同单元格可以直接选择匹配项或差异以更好地进行下一步操作，也可以直接突出显示值。

免费安装 Kutools for Excel 后，单击 Kutools >选择 > 选择相同 & 不同单元格。然后在选择相同 & 不同单元格对话框中，请按以下步骤操作：

1. 在查找值和根据部分中，分别选择用于比较的两列。
2. 选择按行选项。
3. 根据需要选择相同值或不同值。
4. 指定是否为选定的值着色并单击确定。

弹出一个对话框提醒您已找到的值的数量，单击确定关闭对话框。同时，值已被选中，现在您可以删除或复制或执行其他操作。

如果您勾选填充背景色和填充字体颜色复选框，结果如下所示：

示例3：在单元格中比较两列并突出显示重复或唯一数据

Excel 中的条件格式功能非常强大，您可以使用它来比较单元格中的两列，然后根据需要突出显示差异或匹配项。

1.选择您将要比较的两列，然后单击主页 > 使用条件格式 > 突出显示单元格规则 > 重复值。

2. 在弹出的重复值对话框中，从值的下拉列表中选择您需要的突出显示格式。

3. 单击确定。然后两列中的重复项已被突出显示。

示例4：在单元格中比较两列并在另一列中列出精确重复项

如果您想在区分大小写的情况下逐个单元格比较两列后在另一列中列出匹配值，下面的宏代码可以帮助您。

1. 启用您要比较两列的工作表，然后按 Alt + F11 键显示 Microsoft Visual Basic for Applications 窗口。

2. 在 Microsoft Visual Basic for Applications 窗口中单击插入 > 模块。

3. 然后将下面的代码复制并粘贴到新的空白模块脚本中。

VBA：在比较两列后在旁边列出重复项

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 键运行代码，会弹出两个对话框让您分别选择两列。然后单击 确定 > 确定。

匹配项已自动列在两列的右列中。

比较两个列表并提取匹配数据

在这里，我们介绍了比较两个列表并提取数据的两种不同场景。

• 示例1：比较两列并提取精确匹配的数据
• 示例2：比较两列并提取部分匹配的数据

示例1：比较两列并提取精确匹配的数据

例如，有两个表，现在您想比较 B 列和 E 列，然后从 C 列中找到相对价格并将其返回到 F 列。

这里介绍了一些有用的公式来解决这个问题。

在单元格 F2（您想放置返回值的单元格）中，使用以下公式之一：

=VLOOKUP(E2,$B$2:$C$8,2,0)

或者

=INDEX($B$2:$C$8,MATCH(E2,$B$2:$B$8,0),2)

按 Enter 键，第一个值已找到。然后向下拖动自动填充柄到单元格 F6，所有值已被提取。

示例2：比较两列并提取部分匹配的数据

如果两列之间存在一些细微差异，如下图所示，上述方法无法工作。

在单元格 F2（您想放置返回值的单元格）中，使用以下公式之一：

=VLOOKUP("*"&E2&"*",$B$2:$C$8,2,0)

或者

=INDEX($B$2:$C$8,MATCH("*"&E2&"*",$B$2:$B$8,0),2)

按 Enter 键，第一个值已找到。然后向下拖动自动填充柄到单元格 F6，所有值已被提取。

比较两列并查找缺失的数据点

假设有两列，B 列较长，C 列较短，如下图所示。与 B 列相比，如何找出 C 列中缺失的数据？

• 示例1：比较两列并查找缺失的数据点
• 示例2：查找缺失的数据点并将其列在另一列中（使用便捷工具）
• 示例3：比较两列并在下方列出缺失数据

示例1：比较两列并查找缺失的数据点

如果您只想在比较两列后识别哪些数据缺失，可以使用以下公式之一：

=ISERROR(VLOOKUP(B2,$C$2:$C$10,1,0))

或者

=NOT(ISNUMBER(MATCH(B2,$C$2:$C$10,0)))

按 Enter 键，然后拖动自动填充柄到单元格 D10。现在，如果数据同时存在于 B 列和 C 列中，公式返回 FALSE；如果数据仅存在于 B 列中但在 C 列中缺失，公式返回 TRUE。

示例2：查找缺失的数据点并将其列在另一列中（使用便捷工具）

如果您想在比较两列后对缺失数据进行一些后续操作，例如将缺失数据列在另一列中或在较短列下补充缺失数据，可以尝试 Kutools for Excel 的选择相同 & 不同单元格便捷工具。

安装 Kutools for Excel 后，单击 Kutools > 选择 > 选择相同 & 不同单元格。然后在选择相同 & 不同单元格对话框中，按以下步骤操作：

1. 在查找值部分，选择包含完整列表的较长列。
2. 在根据部分，选择缺少一些数据的较短列。
3. 选择按行选项。
4. 选择不同值选项。单击确定。

弹出一个对话框提醒您缺失数据的数量，单击确定关闭它。然后缺失的数据已被选中。

现在您可以按 Ctrl + C 键复制选定的缺失数据，并按 Ctrl + V 键将其粘贴到较短列下方或其他新列中。

示例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 的下方使用公式：

比较两列（日期）是否大于或小于

如果有两列日期，如下图所示，您可能想比较同一行中哪个日期更晚。

• 示例1：比较两列是否大于或小于
• 示例2：比较两列是否大于或小于然后格式化

示例1：比较两列是否大于或小于

您可以使用简单的公式快速查找每行中日期1 是否晚于日期2。

=IF(B2>C2,"Yes","No")

按 Enter 键获取第一个比较结果，然后拖动自动填充柄到单元格 C6以获取所有结果。

示例2：比较两列是否大于或小于然后格式化

如果您想突出显示日期1 列中大于日期2 的单元格，可以使用 Excel 中的条件格式功能。

1.选择 B 列（日期1）中的日期，然后单击主页 > 使用条件格式 > 新建规则。

2. 在新建格式规则对话框中，选择使用公式来确定要设置格式的单元格，在选择规则类型部分中，然后输入公式

 =$B2>$C2

到格式化此公式为真的值的文本框中。

=$B2<$C2.

3. 单击格式按钮打开设置单元格格式对话框，然后根据需要选择格式类型。单击确定 > 确定。

4. 然后 B 列中大于 C 列的单元格已被突出显示。

比较两列并计算匹配项或差异

下面的数据集是用于比较和计算匹配项或差异的示例。

SUMPRODUCT公式可以快速计算两列中的匹配项。

=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B8,C2:C8,0))))

按 Enter 键获取结果。

有关计算匹配项和差异的更多方法，请访问此页面：在 Excel 中计算两列之间的所有匹配项/重复项

比较两个区域

现在您知道如何在阅读上述方法后比较两列。然而，在某些情况下，您可能想比较两个区域（两个包含多列的序列）。您可以使用上述方法（公式或条件格式）逐列比较它们，但这里介绍了一个便捷工具 – Kutools for Excel 可以在不同情况下快速解决此问题，无需公式。

• 示例1：按单元格比较两个区域
• 示例2：如果数据顺序相同，则比较两个区域

示例1：按单元格比较两个区域

这里有两个需要按单元格比较的区域，您可以使用 Kutools for Excel 的选择相同 & 不同单元格实用程序来处理它。

免费安装 Kutools for Excel后，单击Kutools > 选择 > 选择相同 & 不同单元格。然后在弹出的选择相同 & 不同单元格对话框中，按以下步骤操作：

1. 在查找值部分，选择您想在比较两个区域后找出匹配项或差异的范围。
2. 在根据部分，选择用于比较的另一个范围。
3. 在基于部分，选择按单元格。
4. 在查找部分，选择您想选择或突出显示的单元格类型。
5. 在对选中结果的处理部分，您可以通过填充背景颜色或字体颜色来突出显示单元格，如果不需要突出显示，请不要勾选复选框。单击确定。

弹出一个对话框提醒您已选择的单元格/行数量，单击确定关闭它。

• 选择并突出显示唯一值
  
• 选择并突出显示重复值
  

示例2：如果数据顺序相同，则比较两个区域

假设，范围 F2:H7 是一个模型，现在您想找出范围 B2:D7 的数据是否按 F2:H7 的顺序排列。

在这种情况下，Kutools for Excel 的单元格对比可以帮助您。

免费安装 Kutools for Excel后，单击Kutools > 单元格对比。然后在单元格对比对话框中，设置如下：

1. 分别选择查找值和根据框中的两个范围。
2. 在查找部分，选择您想突出显示的单元格类型。
3. 在对选中结果的处理部分中选择突出显示类型。单击确定。

弹出一个对话框提醒您已选择的单元格数量，单击确定关闭它。现在，与另一个范围中的单元格不同的单元格已被选中并突出显示。

以上提供的信息详细说明了如何在 Excel 中比较列。希望您觉得它有价值且有益。有关更多无价的 Excel 技巧和窍门，可以改变您的数据处理，请点击这里。