如何比较两列并查找电子表格中的缺失值

准备数据以实现清晰比较

在比较之前，确保两列数据干净且一致。使用TRIM函数删除多余空格，检查前导零或隐藏字符，并决定比较是否区分大小写。不严格要求按字母排序每列，但有助于稍后直观验证结果。

空白单元格可能导致误导性结果。提前决定如何处理它们：作为缺失值、合法数据或排除项。此外，如果某一列存在重复项，可能需要单独处理，因为单个缺失项可能出现多次，扭曲计数。

1. 将两列复制到同一工作表中的相邻列（例如A列和B列）。
2. 在辅助列中使用TRIM函数删除意外空格：=TRIM(A1)。
3. 如有必要，转换数据类型（例如，存储为文本的数字）。使用VALUE或TEXT函数。
4. 清楚标记比较列，并保存原始数据的备份。

使用VLOOKUP或XLOOKUP查找缺失值

VLOOKUP和XLOOKUP可以测试第一列中的值是否存在于第二列中。XLOOKUP适用于Google表格和当前Excel版本；较旧的Excel安装可以使用VLOOKUP、MATCH或INDEX-MATCH。

对于成员资格检查，返回查找值本身，并使用函数的未找到行为或IFERROR显示“Missing”。这些公式每个输入行返回一个结果，当存在重复键时不证明一对一关系。

• VLOOKUP: =IFERROR(VLOOKUP(A2, B$2:B$100, 1, FALSE), "Missing")
• XLOOKUP: =IFERROR(XLOOKUP(A2, B$2:B$100, B$2:B$100), "Missing")
• #N/A错误表示未找到值；IFERROR将其转换为可读标签。

=IFERROR(VLOOKUP(A2, B$2:B$100, 1, FALSE), "Missing")

=IFERROR(XLOOKUP(A2, B$2:B$100, B$2:B$100), "Missing")

使用INDEX-MATCH获得更大灵活性

INDEX-MATCH是VLOOKUP的一个灵活替代方案，因为返回范围不必位于查找范围的右侧。它对于没有XLOOKUP的工作簿也很有用。

对于缺失值标记，仅MATCH就足够了；将其包装在ISNA或IFERROR中。性能取决于工作簿、范围大小、公式设计和Excel版本，因此不要假设一种查找模式总是更快。

• 公式：=IFERROR(INDEX(B$2:B$100, MATCH(A2, B$2:B$100, 0)), "Missing")
• MATCH函数返回一个值在范围中的相对位置。
• INDEX返回该位置第二列的实际值。如果未找到，IFERROR返回"Missing"。

=IFERROR(INDEX(B$2:B$100, MATCH(A2, B$2:B$100, 0)), "Missing")

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

对于直观概览，条件格式可以突出显示一列中未出现在另一列中的单元格。此方法适用于希望一眼发现缺失值而无需添加辅助列的情况。Excel和Google表格都支持带有自定义公式的条件格式。

应用基于公式的规则。例如，要突出显示A列中未在B列中找到的值，选择A2:A100并输入COUNTIF或MATCH公式，当值缺失时返回TRUE。

1. 选择要格式化的A列范围（例如A2:A100）。
2. 转到开始 > 条件格式 > 新建规则（Excel）或格式 > 条件格式（表格）。
3. 选择“使用公式确定要设置格式的单元格”。
4. 输入公式如=COUNTIF(B$2:B$100, A2)=0并设置填充颜色。
5. 确认并应用。A列中在B列缺失的单元格将被突出显示。

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

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

处理一列或两列中的重复项

重复项可能使比较变得混乱。如果主列表中有多个相同值，简单公式会将每个出现视为单独项目，可能多次标记相同的缺失值。同样，查找列表中的重复项不会导致错误，但如果你期望一对一匹配，可能会产生意外结果。

要处理重复项，首先决定它们是否有意义。如果应忽略它们，在比较前使用COUNTIF标记重复项。然后删除或聚合重复行。或者，为了查找缺失的唯一值，使用唯一列表作为主列。

1. 在主列表旁边插入辅助列，使用=COUNTIF(A$2:A2, A2)>1标记重复项。
2. 筛选或排序以查看并根据需要删除重复条目。
3. 使用UNIQUE函数（Excel 365/2021，Google表格）创建去重列表以进行比较。
4. 然后对唯一列表运行比较公式。

=COUNTIF(A$2:A2, A2)>1

处理空白单元格

任一列中的空白单元格需要谨慎处理。在许多公式中，空白被视为零或空字符串，可能导致错误匹配或缺失标记。例如，如果两列在同一行都有空白，VLOOKUP可能将它们匹配为等价，尽管空白可能不代表有效值。

要管理空白，请预处理数据，用占位符（例如"BLANK"）替换空白，或使用IF条件从比较中排除空白单元格。或者，使用显式检查空白的ISBLANK公式。

1. 决定策略：将空白视为有效值进行比较或排除它们。
2. 如果将空白视为有效，用一致的占位符替换空白：=IF(A1="", "BLANK", A1)。
3. 如果排除，在运行比较前筛选出任一列为空白的行。
4. 在公式中使用IF条件：=IF(OR(A2="", B2=""), "Exclude", primary_comparison)。

=IF(A2="", "BLANK", A2)

验证结果并避免误报

验证至关重要。误报可能源于隐藏空格、大小写不匹配或数据类型差异。应用比较后，抽查一些标记项目，手动验证它们。使用筛选器隔离缺失值，并确认它们不是由于格式问题而存在。

一个实用的验证步骤是使用数据透视表计算主列中每个值出现的次数，并与查找列中的计数进行比较。此外，通过使用Ctrl+F或查找功能在源列中搜索确切实文来审计几个条目。这个额外检查可确保你的缺失列表在采取行动前是准确的。

1. 添加一个临时列，使用UPPER和TRIM去除空格并转换为一致的大小写。
2. 手动抽查标记条目：高亮一个，复制值，在查找列中搜索。
3. 使用数据透视表：计算主列中每个值的次数，也计算查找列中的次数，然后比较计数。
4. 如果使用CompareTwoLists之类的工具，注意它完全在浏览器中运行，并清晰地显示匹配和未匹配的集合。

=UPPER(TRIM(A2))

=UPPER(TRIM(B2))

=IF(COUNTIF($D$2:$D$100,C2)>0,"Match","Missing")