☰

在 Excel 中搜索一组字符串

tag-icon • tag-icon microsoft-excel search
在 Excel 中搜索一组字符串

我有一组术语和一组字符串，这些字符串可能包含也可能不包含其中一个或多个术语。我想搜索是否有一个或多个术语的匹配项。

搜索词（A1:A17以下示例中的单元格）

* bob  
* linda  
* sam

搜索字符串（E下例中的列）

* I'm sam  
* I'm lucy  
* Please find sam or bob or lucy

目前我正在使用一个OR嵌套了一堆搜索函数的函数。例如：
=IF(OR(ISNUMBER(SEARCH(A$2,E33)),ISNUMBER(SEARCH(A$3,E33)),....,ISNUMBER(SEARCH(A$17,E33))),1,0)

我更喜欢使用相当于 SQL 的子字符串 IN (1,2,3,4) 的某种方式

答案1

好了，您了解了如何在字符串中搜索单词：

SEARCH(A$2, E1)

A2在 中搜索（“bob”）的值E1（可能类似于“I'm sam”）。这将返回#VALUE，因为“bob”未出现在“I'm sam”中。但是，

SEARCH("sam", "I'm sam")

返回5，因为“sam”出现在“I'm sam”中，从第 5 个字符开始。

您要求使用技巧来将其扩展到大量搜索字符串。技巧是使用数组公式。数组公式看起来有点像这样：

=SEARCH(A$2:A$17, E1)

搜索E1​每个中的值A2:A17，创建一个包含 16 个结果的数组。对于示例数据，这些结果看起来像 { #VALUE; #VALUE; 5; … }。

请参阅此答案底部的重要说明。

您不能单独使用它，因为单元格需要计算出一个值，而不是数组。但您可以这样做

=COUNT(SEARCH(A$2:A$17, E1))

计算结果中的数字SEARCH——即它告诉多少的A单词（“bob”、“linda”、“sam”等）出现在 中E1。因此，您的

=IF( OR(ISNUMBER(SEARCH(A$2,E1)),…,ISNUMBER(SEARCH(A$17,E1))), 1, 0)

公式（1如果至少找到一个单词，0否则）可以替换为

=IF(COUNT(SEARCH(A$2:A$17, E1))>0, 1, 0)

或者简单地

=--(COUNT(SEARCH(A$2:A$17, $E1))>0)

（在/值--前面放置(双减) 分别将其变成或。）TRUEFALSE10

但你可能想做的不仅仅是确定无论其中一个字符串匹配；你可能想知道哪个一。好吧，还记得我说过 SEARCH(A$2:A$17, E1)对数组 { #VALUE; #VALUE; 5; … } 求值吗？让我们5从中提取：

 =MIN(IFERROR(SEARCH(A$2:A$17, $E1), ""))
  • ISERROR有点类似，ISNUMBER只是反过来而已；TRUE如果参数是错误值则返回，FALSE如果是正常值（数字、布尔值、日期或字符串），则返回。

  • IFERROR是一个非常方便的功能；

    如果错误（计算值，默认值）

    是缩写

    如果（IS错误（计算值),默认值，计算值）

    即，它替换默认值（通常是非-错误值）表示错误值。

  • 因此IFERROR(SEARCH(A$2:A$17, $E1), "")将上述数组结果更改为 { ""; ""; 5; … }。
  • 然后从数组中提取最小数字，跳过空字符串。通常这将是MIN( the_above )仅有的数字，除非在“请查找 sam 或 bob 或 lucy”等有多个匹配项的情况下。在这些情况下，它将是字符串中的第一个匹配项E（此处为13，其中出现“sam”）。

那么，现在就行动吧

=MATCH(MIN(IFERROR(SEARCH(A$2:A$17, E1), "")), SEARCH(A$2:A$17,E1))

此 ( ) 会在完整搜索结果中MATCH查找5(or or whatever)，以查看其出现的位置。在上面的示例中，此操作返回，因为(or or whatever) 是搜索结果中的第三个元素 - 因为它对应于“sam”，这是第三个搜索词。133513

如下图所示，

  • F1(匹配数) ==COUNT(SEARCH(A$2:A$17, E1))
  • G1（第一个匹配的位置）==MIN(IFERROR(SEARCH(A$2:A$17, E1), FALSE))

  • H1(第一个匹配的索引 (字符串编号)) =
                =MATCH(MIN(IFERROR(SEARCH(A$2:A$17, E1), "")), SEARCH(A$2:A$17,E1))

           

重要笔记：

  • 输入数组公式（即上述任何公式）时，请输入Ctrl+ Shift+ Enter。这样公式就会出现在花括号中。不要手动输入花括号。
  • 上述公式假设您要搜索的数据（“我是 sam”等）从单元格开始E1。根据需要进行调整。输入与数据第一行对应的公式并向下拖动。
  • 避免在中出现空白单元格A2:A17，因为SEARCH会在每个字符串中找到空字符串。

答案2

我回答了一个与此非常相似的问题，并使用 Power Query 插件构建了一个工作演示。

以下是之前的问题与答案：

我可以通过关键字分隔列表吗？

相关内容