Ну, вы понимаете, как искать слово в строке:
SEARCH(A$2, E1)
ищет значение A2 («bob») в E1 (это может быть что-то вроде «I'm sam»).
Это возвращает #VALUE , так как «bob» не появляется в «I'm sam». Тем не мение,
SEARCH("sam", "I'm sam")
возвращает 5 , потому что «sam» появляется в «I'm sam», начиная с 5-го символа.
Вы просите хитрость, чтобы масштабировать это для большого количества поисковых строк.
Хитрость заключается в использовании формул массива. Формула массива выглядит примерно так:
=SEARCH(A$2:A$17, E1)
который ищет E1 для каждого из значений в A2:A17 , создавая массив из 16 результатов.
Для данных примера эти результаты выглядят как { #VALUE ; #VALUE ; 5 ; …}
Смотрите важное примечание внизу этого ответа.
Вы не можете использовать это само по себе, потому что ячейка должна вычислять значение, а не массив.
Но вы можете сделать что-то вроде
=COUNT(SEARCH(A$2:A$17, E1))
который подсчитывает числа в результате SEARCH - то есть сообщает, сколько слов A («bob», «linda», «sam» и т. д.) было найдено в E1 .
Так что ваши
=IF( OR(ISNUMBER(SEARCH(A$2,E1)),…,ISNUMBER(SEARCH(A$17,E1))), 1, 0)
формулу (1 если найдено хотя бы одно слово, 0 противном случае) можно заменить на
=IF(COUNT(SEARCH(A$2:A$17, E1))>0, 1, 0)
или просто
=--(COUNT(SEARCH(A$2:A$17, $E1))>0)
(Помещение -- (двойной минус) перед значением TRUE/FALSE превращает его в 1 или 0 , соответственно.)
Но вы, вероятно, хотите сделать больше, чем просто определить , совпадает ли одна из строк; Вы, вероятно, хотите знать, какой . Хорошо, помните, когда я говорил, что SEARCH(A$2:A$17, E1) вычисляется в массиве { #VALUE ; #VALUE ; 5 ; …}?
Давайте извлечем 5 из этого:
=MIN(IFERROR(SEARCH(A$2:A$17, $E1), ""))
ISERROR немного похож на ISNUMBER только наоборот; он возвращает TRUE если параметр является значением ошибки, и FALSE если это нормальное значение (число, логическое значение, дата или строка).IFERROR - очень удобная функция;
IFERROR ( рассчитано_значение, значение по умолчанию )
коротка для
IF (ISERROR ( рассчитанное_значение ), default_value, рассчитанное_значение )
т. е. он заменяет значение ошибки по умолчанию (обычно не ошибочное значение).
- Поэтому
IFERROR(SEARCH(A$2:A$17, $E1), "") изменяет приведенный выше результат массива на { "" ; "" ; 5 ; …}
- И тогда
MIN( the_above ) извлекает наименьшее число из массива, пропуская пустые строки. Обычно это будет единственный номер, за исключением случаев, таких как «Пожалуйста, найдите Сэма, Боба или Люси», где есть несколько совпадений.
В этих случаях это будет первое совпадение в строке E (здесь 13 , где появляется «sam»).
Итак, теперь
=MATCH(MIN(IFERROR(SEARCH(A$2:A$17, E1), "")), SEARCH(A$2:A$17,E1))
Это (MATCH) ищет 5 (или 13 или что-то) в полных результатах поиска, чтобы увидеть, где это происходит. В приведенных выше примерах это возвращает 3 , потому что 5 (или 13 или что-то еще) является третьим элементом в результате поиска - потому что это соответствует «sam», которое является третьим поисковым словом.
На изображении ниже
F1 (количество совпадений) = =COUNT(SEARCH(A$2:A$17, E1))G1 (местоположение первого совпадения) = =MIN(IFERROR(SEARCH(A$2:A$17, E1), FALSE))H1 (индекс (номер строки) первого совпадения) =
=MATCH(MIN(IFERROR(SEARCH(A$2:A$17, E1), "")), SEARCH(A$2:A$17,E1))
Важные заметки:
- Когда вы набираете формулу массива (т.е. любую из формул, обсужденных выше), введите ее, набрав Ctrl+Shift+Enter.
Это заставит это появляться с фигурными скобками вокруг этого. Не вводите фигурные скобки вручную.
- Приведенные выше формулы предполагают, что ваши данные для поиска («Я сам» и т.д.) Начинаются в ячейке
E1 .
Отрегулируйте по мере необходимости. Введите формулы, соответствующие первой строке ваших данных, и перетащите вниз.
- Избегайте пустых ячеек в
A2:A17 , потому что SEARCH найдет пустую строку в каждой строке.
