如何在Excel中比较两个列表：实用指南

准备要比较的列表

在应用任何比较公式之前，确保两个列表干净且格式一致。多余空格、前导或尾随撇号以及不可打印字符可能导致错误的不匹配。Excel的TRIM和CLEAN函数可以消除大部分这些问题。

如果列表在同一列中包含重复项，请决定是比较每个实例还是仅比较唯一值。首先删除内部重复项通常可以避免误导性结果，尤其是在计数匹配时。在进行跨列表比较之前，使用删除重复项功能或带有COUNTIF的辅助列来标记重复项。

1. 将每个列表复制到各自的列（例如，列表1在A列，列表2在B列），从第1行开始。
2. 选择每一列，在“数据”选项卡上运行“删除重复项”命令（如果只想比较唯一条目）。
3. 在新列中应用=TRIM(A1)并粘贴值以去除不需要的空格，然后用清理后的版本替换原始数据。

• 在应用转换之前，始终保留原始数据的备份。
• 使用=CLEAN(A1)删除经常从其他系统导入的不可打印字符。

=TRIM(A1)

=CLEAN(A1)

使用COUNTIF识别缺失或额外的项目

COUNTIF是检查一个列表中的每个项目是否出现在另一个列表中的最简单方法之一。该公式计算指定范围内值的出现次数。结果为零表示该项目不存在；任何正数表示它至少存在一次。

你可以构建一个辅助列，为每一行返回“匹配”或“仅在列表1中”，从而清晰了解重叠和差异。

1. 确保两个列表分别在A列和B列。在列表1旁边插入一个新列C。
2. 在单元格C1中输入：=IF(COUNTIF(B:B,A1)=0, "仅在列表1中", "匹配")。向下拖动公式以覆盖整个列表1。
3. 对列表2在D列重复该过程，以查看哪些项目仅在列表2中。

• COUNTIF不区分大小写。如果大小写重要，请使用SUMPRODUCT与EXACT方法（参见第5节）。
• 对于大范围，COUNTIF可能会降低工作簿速度；考虑将范围限制为实际使用的数据。

=IF(COUNTIF(B:B, A1)>0, "Match", "Only in List1")

使用XLOOKUP连接相关数据

当需要返回相关字段而不仅仅是是/否结果时，XLOOKUP可以搜索一个范围并返回另一个范围中的对应值。它在Microsoft 365和当前永久发布的Excel版本（如Excel 2021及更高版本）中可用；较旧版本可能需要INDEX-MATCH或VLOOKUP。

XLOOKUP为每个查找公式返回一个结果。如果查找失败，其可选的未找到参数可以显示清晰的标签，例如“未找到”。当一个键必须返回多条记录时，请使用FILTER、Power Query或结构化连接。

1. 假设查找值在A列（列表1），要检索的数据在B列和C列（查找列B，返回列C）。
2. 在D列中输入：=XLOOKUP(A2, $B$2:$B$100, $C$2:$C$100, "未找到")。调整范围以匹配你的数据。
3. 向下复制公式。显示“未找到”的条目在列表2中不存在，或存在但没有匹配记录。

• XLOOKUP默认为精确匹配，非常适合列表比较。
• 对于较旧版本的Excel，请使用INDEX-MATCH或VLOOKUP(FALSE)作为替代。

=XLOOKUP(A2, $B$2:$B$100, $C$2:$C$100, "Not found")

=INDEX($C$2:$C$100, MATCH(A2, $B$2:$B$100, 0))

使用条件格式突出显示差异

条件格式允许你向满足规则的单元格应用颜色，从而立即看到不匹配项，而无需添加额外列。你可以突出显示列表2中缺少的列表1中的单元格，反之亦然。

当你不需要永久标记或与不希望看到额外公式列的同事共享工作簿时，此方法最适合进行直观检查。

1. 选择列表1中的范围（例如A2:A100）。在“开始”选项卡上，单击“条件格式”>“新建规则”。
2. 选择“使用公式确定要设置格式的单元格”。输入：=COUNTIF($B$2:$B$100, $A2)=0
3. 单击“格式”并为列表1中唯一的单元格选择填充颜色（例如红色），然后单击“确定”。对列表2重复，将列表1作为范围引用。

• 使用混合引用($A2)，以便公式正确为每一行调整。
• 你还可以通过将规则更改为=COUNTIF($B$2:$B$100, $A2)>0来突出显示两个列表之间的重复项。

=COUNTIF($B$2:$B$100, $A2)=0

处理大小写敏感性和多余空格

Excel中的标准比较公式（COUNTIF、XLOOKUP、VLOOKUP）不区分大小写。如果列表包含“Apple”和“apple”，并且需要将它们视为不同，则必须使用EXACT函数与SUMPRODUCT或条件格式一起使用。

即使使用不区分大小写的比较，尾随空格也可能产生错误的不匹配。在比较之前，始终对两个列表应用TRIM，或者将TRIM嵌套在公式中。

1. 要对列表1与列表2执行区分大小写的匹配，在C1中输入：=IF(SUMPRODUCT((EXACT(A2, $B$2:$B$100))*1)>0, "匹配", "不同")并向下拖拽。
2. 或者，使用基于EXACT的条件格式规则：=SUMPRODUCT((EXACT($A2, $B$2:$B$100))*1)=0
3. 要处理空格，将每个范围引用包装在TRIM中：=IF(COUNTIF($B$2:$B$100, TRIM(A2))=0, ...)

• EXACT区分大小写并且也考虑空格，因此请事先清理数据。
• 对于大列表，SUMPRODUCT与EXACT可能很慢；考虑使用带有精确匹配标志的辅助列。

=IF(SUMPRODUCT((EXACT(A2, $B$2:$B$100))*1)>0, "Match", "No match")

了解Excel在大型或复杂比较方面的局限性

Excel工作表有固定的行限制，但比较的实际限制通常更低。工作簿大小、公式范围、重新计算、条件格式、可用内存和设备速度都会影响响应能力。

精确成员检查很简单。模糊匹配、多列连接和可重复的数据管道需要Power Query、专业插件、脚本或数据库。浏览器列表工具对于集中精确比较很有用，但它不是模糊匹配引擎，并且还应该用代表性样本进行测试。

• 当有界范围或表足够时，避免全列引用。
• 对实际工作簿进行基准测试，而不是依赖于通用的行阈值。
• 当任务需要结构化连接或可重复的自动化时，使用Power Query、SQL或脚本。

何时切换到基于浏览器的列表比较工具

对于一次性精确比较，将两个值列复制到专用浏览器工具中可能比维护工作簿中的公式更快。CompareTwoLists可以显示共享值、仅存在于第一个列表中的值、仅存在于第二个列表中的值或合并集。

处理在页面内进行，因此粘贴的列表值不会上传到站点服务器。比较是基于值的：它不会模糊匹配名称、连接完整的电子表格记录或推断哪些标题对应。当需要这些功能时，请返回使用Excel、Power Query、SQL或数据框。

1. 仅复制要比较的两个值列。
2. 将它们粘贴到Compare Two Lists或Compare Columns中。
3. 选择共享、仅第一、仅第二或合并结果。
4. 在使用输出之前验证行数并抽查几个值。

• 粘贴值的本地页面内处理
• 精确列表成员资格和集合操作
• 无模糊匹配或完整记录连接
• 性能取决于浏览器、设备和输入