Excel:使用数组公式在特定字符串中搜索字符串列表? [英] Excel: Search for a list of strings within a particular string using array formulas?

问题描述

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

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>)}

但只有当我搜索的单元格中的单词位于我搜索的单词列表的第一行时，它才会找到匹配项.有没有办法编写一个可以查看整个列表的公式?我更喜欢它不只是返回 TRUE/FALSE.我知道如何在单元格中搜索单词列表并根据列表中的单词是否存在于单元格中返回 TRUE/FALSE.我真的想知道找到了哪个词，或者它的位置.

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.

推荐答案

如果没有找到匹配，这将返回匹配的单词或错误.对于这个例子，我使用了以下内容.

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

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

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

按 Ctrl+Shift+Enter 输入数组公式.

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

这个公式的工作原理是首先查看单词列表以查找匹配项，然后将单词在列表中的位置记录为正值(如果找到)或负值(如果未找到).此数组中的最大值是找到的单词在列表中的位置.如果没有找到单词，则将负值传递给 INDEX() 函数，从而引发错误.

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.

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

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))

这也必须通过按Ctrl+Shift+Enter作为数组公式输入.如果未找到匹配项，它将返回 -1.

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

这篇关于Excel:使用数组公式在特定字符串中搜索字符串列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！