Excel: поиск списка строк в определенной строке с использованием формул массива?

Question

Я хочу найти в ячейке список слов. Я думал, что это будет работать как формула массива:

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

Но он находит совпадение только тогда, когда слово, которое находится в ячейке, которую я ищу, находится в первой строке списка слов, которые я ищу. Есть ли способ написать формулу, которая просматривает весь список? И я бы предпочел, чтобы он не возвращал TRUE/FALSE. Я знаю, как искать в ячейке список слов и возвращать значение ИСТИНА / ЛОЖЬ в зависимости от того, существует ли слово в списке в ячейке. Я хочу на самом деле узнать, какое слово было найдено, или его положение.

Answer 1

Это вернет подходящее слово или ошибку, если совпадение не найдено. Для этого примера я использовал следующее.

Список слов для поиска: G1:G7
Ячейка для поиска в: A1

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

Введите в качестве формулы массива, нажав Ctrl+Shift+Enter.

Эта формула работает, сначала просматривая список слов, чтобы найти совпадения, затем записывая положение слова в списке как положительное значение, если оно найдено, или как отрицательное значение, если оно не найдено. Наибольшее значение из этого массива - это позиция найденного слова в списке. Если слово не найдено, отрицательное значение передается в INDEX() функция, выдающая ошибку.

Чтобы вернуть номер строки соответствующего слова, вы можете использовать следующее:

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

Это также должно быть введено как формула массива, нажав Ctrl+Shift+Enter. Он вернется -1 если совпадений не найдено.

Answer 2

Добавление этого ответа для таких людей, как я, для которых ответ ИСТИНА / ЛОЖЬ вполне приемлем

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

или с учетом регистра

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

Где диапазон для условий поиска G1:G7

Не забудьте нажать CTRL+SHIFT+ENTER

Answer 3

В случае, если это может оказаться полезным для других: я обнаружил, что при добавлении начальной пустой ячейки в мой список условий поиска вместо ошибки будет возвращено нулевое значение.

={INDEX(SearchTerms!$A$1:$A$38,MAX(IF(ISERROR(SEARCH(SearchTerms!$A$1:$A$38,SearchCell)),0,1)*((SearchTerms!$B$1:$B$38)+1)))}

NB: столбец A содержит условия поиска, B - индекс номера строки.

Answer 4

1. Распределите список слов по разделителям, которые никогда не встречаются в словах, например, fe |
2. поменяйте местами аргументы в вызове find - нам нужен поиск, если значение ячейки соответствует одному из слов в строке шаблона{=FIND("cell I want to search","list of words I want to search for")}
3. если шаблоны похожи, есть риск получить больше результатов, чем хотелось бы, мы ограничиваем только правильные результаты, добавляя & "|" к проверенному значению ячейки (хорошо работает с формулами массива) ячейка G3 может содержать: {=SUM(FIND($A$1:$A$100&"|";A3))}это гарантирует, что электронная таблица будет сравнивать строки типа "cellvlaue|" againts "pattern1|", "pattern2|" и т. д., который разбирает конфликты, такие как pattern1="недавно добавленный", pattern2="добавленный" (сумма всех ячеек, соответствующих "добавленному", была бы слишком высокой, включая целевые значения для ячеек, соответствующих "вновь добавленному", что было бы логичным ошибка)