☰

寻找公式在 Excel 中搜索特定模式

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2013
寻找公式在 Excel 中搜索特定模式

我正在寻找公式来搜索具有特定字母模式的“单词”，例如INNSA1或INMAA4或INPTPB，其中单词以 、 、或 开头并以、、或IN结尾。单词始终正好是六个字符，字母可以是大写或小写。416B

Excel 单元格集合中的每个单元格都包含一个句子，其中将找到一个或多个这样的单词（始终至少有一个）。例如：

  • 我的自定义位置是 INNSA1
  • 我在 INMAA4 工作
  • 我想去 INDEL6
  • INNSAB 是边境地区

单词（或多个单词，如果有多个的话）可以位于句子中的任何位置。目标单词始终是单独的单词，而不是较大单词的一部分。

对于每个句子（即单元格），我想搜索是否存在符合所描述字母模式的单词，并将该单词作为值返回。

答案1

这只是一个示例，您可以根据自己的模式进行调整。

数据来自A2到A26， 在B2进入：

=IF(AND(LEFT(A2,2)="IN",OR(RIGHT(A2,1)="B",RIGHT(A2,1)="1",RIGHT(A2,1)="4",RIGHT(A2,1)="6")),1+MAX($B$1:B1),"")

并向下复制：

在此处输入图片描述

如您所见，每项“好”数据都用一个简单的顺序索引标记。

答案2

下列操作有效：

=CONCAT(  IF(  ISNUMBER(SEARCH($E$1:$E$4,A1)),  MID(A1,SEARCH($E$1:$E$4,A1),6),  "")  )

您可以构建所需搜索项的列表，在本例中为 E1:E4，但可以在任何地方，例如命名范围，或物理单元格中的某个位置，就像我在测试中使用的一样。电子表格的逐步改进可能包括在公式本身中生成列表。如今，使用LET允许将其保留在公式中，但直接输入。有很多方法可以做到这一点。

然后使用SEARCH，它允许使用通配符，因此像 这样的列表项可以IN???4工作。如果数据单元格中存在匹配项，它将给出匹配项的起始位置。如果不存在匹配项，它将给出错误。ISNUMBER测试结果（在本例中为四个，因为要查找四个项目）并且是 中的“测试” IF。如果成功，它返回匹配的单词的字符串。对于三次失败，返回 FALSE ""。（缺点：其结果既不能容纳单个句子中同一个单词的多个实例，也不能容纳单个句子中两个或多个不同匹配词的实例。）

由于失败的IF返回结果实际上并没有太多关联。只有匹配的单词和三个无，或者无单词和四个无。无论哪种方式，都会检查每个句子并做出正确的返回。""CONCAT

答案3

有很多方法可以解决每个问题，但需求的组合指向一种简单明了的方法，即 VBA 解决方案。非 VBA 解决方案使用辅助列。电子表格的布局是未知的，其他因素也是如此，例如句子数和可能的结果数，以及您需要和想要如何使用结果。因此，这个答案将比具体的解决方案更具有通用指导性。

使用函数，在句子中搜索可能不止一个目标会很复杂。一种更简单的方法是将句子分解成单词，然后检查单词。解析句子的一种简单方法是使用内置的“将文本转换为列”向导。

假设句子在 A 列中，则将接下来的几列留空以显示结果。这样，您的结果将始终位于已知位置，并显示在数据旁边。我假设最多有两列，因此 B 列和 C 列将包含结果值。

计算一个句子中可以包含的最大单词数，并保留那么多后续列作为辅助列。这些将用于测试每个单词。假设您允许五个单词。列 D:H 将包含分别指向列 I:M 的公式。

假设第 2 行是您的第一个数据行，因此第一个公式将位于 D2 中，指向 I2。该公式将类似于 Gary's Student 建议的公式：

=IF(AND(LEFT(I2,2)="IN",OR(RIGHT(I2,1)="B",RIGHT(I2,1)="1",RIGHT(I2,1)="4",RIGHT(I2,1)="6")),1,0)

另一个公式是：

=IFERROR((LEFT(I2,2)="IN")*(SEARCH(RIGHT(I2,1)," 416b")>1),0)

这只是为您提供了一些关于如何判断目标单词是否符合模式的想法。第二个公式使用 SEARCH（因为它不区分大小写）来查看最后一个字符是否在您的列表中。测试前两个字符IN和最后一个字符，每个结果都是 True 或 False，Excel 分别将其视为1和0。因此，如果两个测试都为真，则结果为1，否则为0。

在搜索表达式中，我从空白开始并测试结果 >1。我这样做的原因是因为我使用 MS Office Online 进行测试，并且它认为空单元格的最右边的字符位于测试字符串的位置 1。

如果 SEARCH 未找到匹配项，则会返回错误，因此将公式包装在 IFERROR 中可确保任何不匹配的单词都会导致0。

因此，1如果单词符合您的模式，则任一公式都会返回，0如果不符合（或其目标单元格为空），则返回。

在 D2 中输入任一公式，然后将其拖动或复制到 H2，并向下复制到所需的最后一行。

要填充目标单元格，请选择 A 列中的所有数据，然后将其复制并粘贴到 I 列（文本到列将覆盖数据并从数据列开始）。选择 I 列中的数据。在选项卡的组Data中Data Tools，单击Text to Columns。您将获得一个不言自明的向导。

告诉它数据是分隔符，然后选择space分隔符。它会给你一个预览，让你了解它认为你希望它如何解析单词。当你完成向导时，它会将每个单词放在行中连续的单元格中。

D:H 中的公式应在每一行中至少显示一个1。这些是您的单词，并且它们的列与解析的单词处于相同的相对位置。

要获取 B 列中第一个单词的值，请使用 INDEX 和 MATCH 查找1D:H 行中的第一个单词，并从 I:M 中的同一行中检索相应的值。例如，B2 将是：

=INDEX(I2:M2,MATCH(1,D2:H2,0))

1这将从 I2:M2 中的单元格中检索与在 D2:H2 中找到的完全匹配相对应的值。

为了得到 C 列的后续值（如果有超过两个，则还要得到后面的值），有多种解决方案可以找到第 N 个匹配的1。这里是一条链接一种方法。

相关内容