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

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

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

按 Ctrl + Shift + 输入数组公式

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 + 输入作为数组公式输入。如果没有找到匹配，它将返回 -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屋！