我有一个非常大的数据集，其一般形式如下：
| 顾客姓名 | 客户ID |
|---|---|
| 供应商 1 | 123345 |
| 供应商 2 | 987654 |
我有大约 96,000 条此类记录。我试图实现的目标是确保每个唯一的客户名称只与一个唯一的客户 ID 关联，反之亦然。我可以拥有名称-ID 对的重复项，但有很多单个唯一的名称与多个不同的 ID 关联，反之亦然。
找出与多个 ID 关联的名称和与多个名称关联的 ID 的最简单方法是什么？如果我能找到这些，我可以直接将它们从工作表中删除。
答案1
=IF(CONCATENATE(IF(COUNTIF($A$2:$A$10;A2)>1;"名称不唯一";"唯一名称");" / ";IF(COUNTIF($B$2:$B$10;B2)>1;"ID 不唯一";"唯一 ID"))="唯一名称 / 唯一 ID";"名称和 ID 都是唯一的";CONCATENATE(IF(COUNTIF($A$2:$A$10;A2)>1;"名称不唯一";"唯一名称");" / ";IF(COUNTIF($B$2:$B$10;B2)>1;"ID 不唯一";"唯一 ID")))
代码可能会根据当地语言而改变，将“；”替换为“，”
答案2
假设您的数据位于 A 列和 B 列，其中列标题为您在示例中给出的，数据从第 2 行开始。在这种情况下，以下内容将显示哪些行是重复的：
=IFERROR(IF(VLOOKUP(A3&"|"&B3,INDEX($A$2:A2&"|"&$B$2:B2,,1),1,FALSE)=A3&"|"&B3,"Duplicate",),"")
创建一个“辅助列”（显然您愿意这样做）并在第一个数据行的单元格中输入此公式，然后复制并粘贴到数据末尾。
（小问题：我没有填写IF()返回值，因为在这种情况下，VLOOKUP()失败，IF()因此为 FALSE，因为这种情况永远不会发生。所以没有必要。但如果感觉不那么令人困惑，请务必为其 FALSE 结果添加类似“不是重复”的内容。）
的搜索值VLOOKUP()是每行中两个单元格数据的组合。它们由“|”字符分隔，因为有时意外的配对可能会匹配。考虑简单的客户名称/ID 对和Cust 11 and 234：Cust 1 and 1234如果没有不寻常的分隔符，您将得到两者的配对CUST 11234。使用分隔符，它们是不同的。“不寻常的分隔符”是，因此在实际数据中不太可能出现。
然后，它使用 来INDEX()收集当前行上方的所有数据行，并通过像完成的那样将它们连接起来，就像形成查找值一样形成它们。连接后一行中的两个逗号构成了要搜索的INDEX()数据集，以便返回所有行。它们之间没有值告诉 Excel 使用所有行，而不是一行或一些行。由于表格将每行中的值组合在一起，因此最终会得到一个有多行但只有一列宽的区域，因此您必须对的返回列使用“1” 。VLOOKUP()VLOOKUP()VLOOKUP()
它找到的任何匹配都是重复的。这是因为 的范围中使用的引用INDEX()。请注意，第一部分是绝对的，第二部分是相对的。因此，范围将是从第一行到公式所在行正上方的行。 的范围INDEX()不断变长。这是使范围“动态”的一种方法，是一种很有用的工具。
因此，它找到的任何匹配项都会与查找的值进行比较，显然，它必须等于它，因此返回IF()TRUE 并在单元格中放置“重复”。
任何匹配失败都会返回错误。IFERROR()然后接管并返回一个空白单元格。因此，您可以向下扫描该列（如果您真的想这样做），当存在重复项时，您可以看到一些明显的信息，而当它们是可接受的配对时，只会显示空白。
但是，这是删除它们的非常困难的方法。相反，在复制公式后，突出显示所有公式单元格并将它们复制到剪贴板，然后Paste|Special|Values将它们更改为文本。按此对三列进行排序，所有显示“重复”的行将归入一个大组。突出显示这些行并删除它们。问题解决了。删除辅助列，您就会得到一个干净的数据集。
还有其他方法可以显示哪个是哪个，但既然您愿意删除它们并完成，这是最简单的方法。
任何时候，如果你想知道是否存在更多重复项，都可以使用以下公式。如果它显示除 0 以外的任何值，则存在许多重复项：
=ROWS(A2:A96001)-COUNTA(UNIQUE(A2:A96001&"|"&B2:B96001))
计算表中的行数ROWS()。当然，如果您像我上面那样直接指定，您可以直接在那里输入“96000”。但您可能正在使用命名范围、动态确定范围或使用实际的 Excel，然后需要Table使用该函数。
返回UNIQUE()所有唯一配对的列表，并将其输入到对COUNTA()它们进行计数的函数中。它的最大数量（使用上面的地址）是 96000，因此您将有 96000-96000=0 个重复项。如果有一千个重复项，则将是 95000，因此减法将得出 1,000。
这样做可以让您立即知道何时发生重复。这样就可以立即修复。问题可能不会再次出现。如果通过复制和粘贴添加大量条目，或者加载其他数据，它会让您知道问题是否刚刚出现以及问题有多严重。很有用的东西。