Общая формула
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Резюме
Чтобы проверить ячейку, чтобы увидеть, содержит ли она одну из многих строк, вы можете использовать формулу, основанную на функциях ПОИСК, ЕЧИСЛО и СУММПРОИЗВ. Скопированная формула в C5:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
где вещи - именованный диапазон E5: E9.
Объяснение
Мы хотим протестировать каждую ячейку в B5: B11, чтобы увидеть, содержит ли она какие-либо строки в именованном диапазоне вещей (E5: E9). Формула, которую мы используем в C5, скопированная ниже:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Эта формула основана на формуле (объясняется здесь), которая проверяет ячейку на наличие единственной подстроки. Если ячейка содержит подстроку, формула возвращает ИСТИНА. Если нет, формула возвращает ЛОЖЬ:
ISNUMBER(SEARCH(things,B5))
Однако в этом случае мы даем ПОИСКУ список строк. Поскольку в вещах 5 строк , ПОИСК возвращает 5 результатов в виде такого массива:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Когда ПОИСК находит строку, он возвращает позицию этой строки. Если ПОИСК не находит строку, возвращается #VALUE! ошибка. Поскольку «желтый» появляется как первое слово в B5, мы видим 1. Поскольку другие строки не найдены, остальные 4 элемента являются ошибками.
Этот массив возвращается непосредственно в функцию ISNUMBER. ISNUMBER затем возвращает массив значений TRUE / FALSE:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Если у нас есть хотя бы одно ИСТИНА в массиве, мы знаем, что ячейка содержит по крайней мере одну из строк, которые мы ищем. Самый простой способ проверить ИСТИНА - сложить все значения вместе. Мы можем сделать это с помощью SUMPRODUCT, но сначала нам нужно принудительно установить значения TRUE / FALSE на 1 и 0 с двойным отрицательным знаком (-), например:
--ISNUMBER(SEARCH(things,B5))
Это дает новый массив, содержащий только единицы и нули:
(1;0;0;0;0)
доставляется непосредственно в SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
Имея всего один массив для обработки, SUMPRODUCT добавляет элементы в массив и возвращает результат. Любой ненулевой результат означает, что у нас есть "попадание", поэтому мы добавляем> 0, чтобы получить окончательный результат ИСТИНА или ЛОЖЬ:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
С жестко запрограммированным списком
Нет необходимости использовать диапазон для поиска в списке строк. Вы также можете использовать константу массива. Например, чтобы проверить "красный", "синий" и "зеленый", вы можете использовать следующую формулу:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Предотвращение ложных совпадений
Одна из проблем этого подхода заключается в том, что вы можете получить ложные совпадения из подстрок, которые появляются внутри более длинных слов. Например, если вы попытаетесь сопоставить «dr», вы также можете найти «Andrea», «drink», «dry» и т. Д., Поскольку «dr» появляется внутри этих слов. Это происходит потому, что ПОИСК автоматически находит совпадение "содержит".
Для быстрого взлома вы можете добавить пробел вокруг поисковых слов (например, «dr» или «dr»), чтобы не поймать «dr» в другом слове. Но это не удастся, если "dr" появится первым или последним в ячейке или появится с пунктуацией.
Если вам нужно более точное решение, один из вариантов - сначала нормализовать текст во вспомогательном столбце, позаботившись также о добавлении начального и конечного пробелов. Затем вы используете формулу на этой странице для полученного текста.
