Ну, вы понимаете, как искать слово в строке:
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
найдет пустую строку в каждой строке.