Excel:使用数组公式搜索特定字符串中的字符串列表?

I want to search a cell for a list of words. I thought this would work as an array formula:

我想在单元格中搜索单词列表。我认为这可以作为一个数组公式:

{=FIND(<list of words I want to search for>,<cell I want to search>)}

But it only finds a match when a word that's in the cell I'm searching sits in the first row of the list of words I'm searching for. Is there any way to write a formula that looks through the entire list? And I'd prefer if it didn't just return TRUE/FALSE. I know how to search cell for a list of words and return TRUE/FALSE based on whether or not a word in the list exists in the cell. I want to actually know which word was found, or its position.

但只有当我搜索的单元格中的一个单词位于我要搜索的单词列表的第一行时，它才会找到匹配。有没有办法写出一个公式来遍历整个列表?我希望它不仅仅返回TRUE/FALSE。我知道如何搜索一个单词列表并返回TRUE/FALSE，基于该列表中是否存在一个单词。我想知道找到了哪个词，或者它的位置。

3 个解决方案

#1

This will return the matching word or an error if no match is found. For this example I used the following.

如果没有找到匹配字，则返回匹配字或错误。对于这个示例，我使用了以下内容。

List of words to search for: G1:G7
Cell to search in: A1

要搜索的单词列表:G1:G7要搜索的单元格:A1

=INDEX(G1:G7,MAX(IF(ISERROR(FIND(G1:G7,A1)),-1,1)*(ROW(G1:G7)-ROW(G1)+1)))

Enter as an array formula by pressing Ctrl+Shift+Enter.

按Ctrl+Shift+Enter键，输入一个数组公式。

This formula works by first looking through the list of words to find matches, then recording the position of the word in the list as a positive value if it is found or as a negative value if it is not found. The largest value from this array is the position of the found word in the list. If no word is found, a negative value is passed into the INDEX() function, throwing an error.

这个公式的工作原理是首先查看单词列表以找到匹配，然后将单词在列表中的位置记录为一个正值(如果找到了)，或者是一个负值(如果没有找到的话)。这个数组的最大值是在列表中找到的单词的位置。如果没有找到单词，则向INDEX()函数传递一个负值，从而抛出一个错误。

To return the row number of a matching word, you can use the following:

要返回匹配字的行号，可以使用以下命令:

=MAX(IF(ISERROR(FIND(G1:G7,A1)),-1,1)*ROW(G1:G7))

This also must be entered as an array formula by pressing Ctrl+Shift+Enter. It will return -1 if no match is found.

通过按Ctrl+Shift+Enter键，这也必须以数组公式的形式输入。如果没有找到匹配，它将返回-1。

#2

Adding this answer for people like me for whom a TRUE/FALSE answer is perfectly acceptable

给像我这样的人加上这个答案是完全可以接受的。

OR(IF(ISNUMBER(SEARCH($G$1:$G$7,A1)),TRUE,FALSE))

or case-sensitive

或者是区分大小写的

OR(IF(ISNUMBER(FIND($G$1:$G$7,A1)),TRUE,FALSE))

Where the range for the search terms is G1:G7

搜索词的范围是G1:G7

Remember to CTRL+SHIT+ENTER

记得CTRL +屎+ ENTER

#3

Arange your word list with delimiter which never occures in the words, f.e. |
在你的词表上加上从来不会出现在单词f.e. |中的分隔符
swap arguments in find call - we want search if cell value is matching one of the words in pattern string {=FIND("cell I want to search","list of words I want to search for")}
查找调用中的交换参数——我们想要搜索是否单元格值匹配模式字符串{= find中的一个单词(“我想要搜索的单元格”、“我想要搜索的单词列表”)}
if the patterns are similar, there is a risk of getting more results than wanted, we restrict just correct results via adding &"|" to the cell value tested (works well with array formulas) cell G3 could contain: {=SUM(FIND($A$1:$A$100&"|";A3))} this ensures spreadsheet will compare strings like "cellvlaue|" againts "pattern1|", "pattern2|" etc. which sorts out conflicts like pattern1="newly added", pattern2="added" (sum of all cells matching "added" would be too high, including the target values for cells matching "newly added", which would be a logical error)
如果模式相似，则有可能得到超出预期的结果，我们通过在测试的单元格值(与数组公式配合得很好)中添加&“|”来限制正确的结果:{=SUM(FIND($A$1:$A$ A$100&“|”;A3))}这确保电子表格能够比较像“cellvlaue|”这样的字符串与“pattern1|”、“pattern2|”等的字符串，这些字符串可以对诸如“pattern1=“新添加”、pattern2=“新添加”、“添加”等的字符串进行排序(对于所有新添加的单元来说，包括“新添加的新添加的错误的)都是“新添加的高匹配”的错误)(对于“新添加的错误的)

#1