☰

Excel 2010 - 如何比较列？

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
Excel 2010 - 如何比较列？

我有 2 列（A 和 B），每列有超过 16k 行数据。

列A比 列 多 80 行B。它们应该具有相同的行数。

如何比较两列以查看 B 列中的内容？B 列中包含 A 列中没有的名称。我想比较 A 列以确保所有名称都在 B 列中。

例子：

--------------------------------------------
| Column A           |     Column B        |
--------------------------------------------
| Abdeljawa,Mohammed | ABDELJAWA MOHAMMED  |
| Abdella,Georgette  | ABDELLA GEORGETTE   |
| Abdul,Moiz M       | ABDELLA ZIAD        |
| Abdullahi,Abukar   | ABDUL MOIZ MOSHEEN  |
--------------------------------------------

答案1

简单回答：

在单元格中C1输入公式：

=VLOOKUP(A1,B:B,1,FALSE)

并将其拖至列下方，C直至列中数据的末尾B。

这样，您就可以比较列中的每个值B以检查它们是否在列中A（寻找精确的匹配值）。

深层发掘...

根据您提供的示例数据，列中的相同名称似乎A在列中的显示方式可能不同B。

我注意到的三件事是：

  • 大写（全部大写与句子大小写）
  • 标点符号（添加逗号代替空格）
  • 订单（例如 FirstName[空间]姓氏与姓氏、名字[空间]中间初始）

如果您还想在查找列中是否B包含所有内容时处理这些情况A，那么您就需要更加深入地参与。

您可能只想根据每个不一致性出现的次数来考虑其中的一些不一致性（如果有）。

我将分别解决数据中的每一种不一致问题。根据您需要考虑的不一致问题，您可以根据需要嵌套它们。

大写：Excel函数不区分大小写。因此，如果您使用Excel 查找数据，VLOOKUP这应该不是问题。但如果您使用其他函数，大写字母很容易处理。您需要做的是转换VLOOKUP全部将数据转换为小写或大写以保持一致。（我个人更喜欢大写，原因我也不清楚）

注意：您还可以使用正常功能如果您愿意的话，也可以将所有内容转换为适当的大小写。

您可以使用这些函数并创建 2 个具有一致大写字母的新数据列，或者可以在公式中使用它们来动态转换值。

标点符号：根据您提供的示例，有一列中的A名称用逗号分隔，而同一名称用空格分隔。如果有很多这样的情况，您可以使用以下格式：SUBSTITUTE 函数。

例如，如果您想删除单元格中的所有逗号A1并将其替换为空格，则可以使用：

=SUBSTITUTE(A1,","," ")

与大写类似，您可以有 2 个包含替换结果的新列，也可以在任何其他公式中使用它们。VLOOKUP如果您想A1在列中查找之前删除单元格中的逗号并将其替换为空格，则公式将如下所示B：

=VLOOKUP(SUBSTITUTE(A1,","," "),B:B,1,FALSE)

顺序：如果同一名称在列A和列中的顺序不同B，并且存在大量此类情况，则您可能需要考虑在查找名称之前对其进行重新排序。这很容易变得非常复杂，因此：

  1. 在执行此操作之前，请确保有大量数据需要重新排序。
  2. 确保同一名称在两列中的顺序不同持续的方式。
  3. 如果在正确匹配的数据中存在这样的数据，则仅对没有匹配的数据执行此操作。

要将单元格中的姓名从“姓氏、名字”重新排序为“名字、姓氏”，请参见下文。

取自如何将字符串从“lastName, firstName”切换为“firstName LastName”？：

如果名字在 A2 中，请尝试在 B2 中复制此公式

=MID(A2&" "&A2,FIND(" ",A2)+1,LEN(A2)-1)

如果您想要反转姓名的顺序，和/或在重新排序时考虑中间名首字母，则可以使用以下函数左边，正确的，中，伦，寻找也需要这样做。

最后一件事，根据数据进入 Excel 文件的方式，你可能需要执行其他数据清理功能例如干净的和修剪。

答案2

方法有很多，这里介绍其中一种：

在 C 列中输入一个公式，该公式获取 A 列的每个值并在 B 列中搜索该值；如果未找到，则标记它。然后在标记上放置一个过滤器；这样您就可以轻松看到差异。

公式如下所示（单元格 C1）：
=VLOOKUP(A1,B$1:B$20000,1,FALSE)
如果 A 中的值也存在于 B 中，则将该值放入 C，#ERROR否则。

然后将公式向下复制，并过滤 C #ERROR，您将看到 B 中缺失的所有 A 值。

如果您的拼写有细微差别 - 如您的示例所示 - 那还不够；您需要使用UPPER(A1)in 代替A1来使它们匹配，并且如果其中也有逗号，则需要添加一个 replace ','by nothing 。

答案3

问题相当复杂，因为 A 列中有逗号，而 B 列没有，而且 B 列全部大写，而 A 列不是。我个人的做法是将每列加载到单独的文本文件中，然后使用 Linux 实用程序 sed 删除逗号并在第一个文件中将其全部改为大写。然后我就可以运行 diff 实用程序了。

答案4

我的英语不好，但我希望下面列出的内容能有所帮助

在开头：单元格中的名称切换到更多带有文本到数据的单元格。示例 Cell1 = Abdul,Moiz M Cell3 = Abdul Cell4 = Moiz Cell5 = M And Cell2 = ABDUL MOIZ MOSHEEN Cell6 = ABDUL Cell7 = MOIZ Cell8 = MOSHEEN

上面的单元格是 2,3,4,6,7,8

并且使用 MATCH 函数计算单元格 2、3、4、6、7、8 的匹配次数，两次及以上匹配可获得较好的结果，且失败概率较小。

相关内容