☰

如何在 Excel 中查找两列之间的重复子字符串文本？

tag-icon • tag-icon microsoft-excel duplicate
如何在 Excel 中查找两列之间的重复子字符串文本？

我有两列数据。第 1 列包含电子邮件地址域列表，例如gmail.com, yahoo.com, mydomain.edu, myotherdomain.org等。第二列包含完整电子邮件地址列表，例如[email protected]。

我想突出显示 B 列中与 A 列中的一个域的子字符串匹配的每个电子邮件地址。

例如，在 B 列中有地址，[email protected]而在 A 列中有字符串。Bdomain1.com列中的地址应该突出显示。如果 B 列中有地址，但A 列中没有[email protected]字符串，则电子邮件地址一定不能突出显示。domain2

我怎样才能做到这一点？

答案1

使用条件格式并插入此公式来确定要格式化的单元格

=IFNA(MATCH(MID($B1,SEARCH("@",$B1)+1,999),A:A,0)>0,FALSE)

您可以通过将公式放在空列中并检查其结果来仔细检查公式

在此处输入图片描述

笔记：在 2013 年之前的 Excel 版本中=IFNA()必须替换为=IF(ISNA())

=IF(ISNA(MATCH(MID($B1,SEARCH("@",$B1)+1,999),A:A,0)),FALSE,TRUE)

从内到外解释语法

  • SEARCH("@",$B1)将确定 B 列每个单元格的 @ 符号的位置。
    上述示例的结果将是 8 和 5。

  • MID($B1,[...]+1,999)将从 @ 字符开始删除所有内容。请注意，我们必须在起始位置添加 +1，否则将包含 @ 符号。在我们的示例中，结果将是domain1.com和domain2.com。

    长度参数不能省略，因此我选择 999 以确保安全。

  • MATCH([...],A:A,0)将在 A 列中搜索域字符串并返回行索引。（该死，我们需要 false 或 true）。示例结果将是 2 和 #NA，因为第二封电子邮件在 A 列中没有匹配的域。

  • ISNA([...]))用作将行索引转换为真或假的小技巧。如果我们有一个有效匹配，则提供行索引，并且它不是 #NA，因此我们得到错误的。但是如果内部公式的结果为 #NA，则 ISNA 公式将返回正确结果。

    等一下，但这是互换的！是的，我们需要反过来。

  • =IF([...],FALSE,TRUE)只是开关真的和错误的所以我们可以将其用作条件公式的输入

答案2

条件格式规则的更紧凑公式：

=COUNTIF(A:A,MID(B1,FIND("@",B1)+1,999))

假设第一封电子邮件位于单元格 B1 中，此公式的工作原理如下：

  FIND("@",B1)+1 ：查找域名的第一个字符
                              电子邮件地址（以一个字符开头
                              超过 @）

  MID(B1,FIND(...)+1,999) ：从电子邮件中提取完整域名
                              地址

  COUNTIF(A:A, MID(…)) ：计算 A 列中匹配的数量
                              电子邮件地址中的域名（将
                              如果域列表不包含，则最多为 1
                              重复）。

要使用公式，您需要选择要格式化的范围，打开条件格式对话框，选择根据公式创建新的格式规则，粘贴公式，然后设置要突出显示匹配单元格的格式。

Excel 条件格式引擎将把公式生成的 1（表示域名匹配）视为 TRUE，将 0（表示域名不匹配）视为 FALSE。（即使域列表中存在重复项，因此计数大于 1，公式仍将起作用。）

此公式与 Excel 2007 及更高版本兼容。以下修正公式应与所有前一年版本的 Excel 向后兼容。它假定域列表在范围 A1:A10 内。

=SUM(IF($A$1:$A$10=MID(B1,FIND("@",B1)+1,999),1,0))>0

相关内容