Общая формула
=СУММПРОИЗВ(—ЕЧИСЛО(ПОИСК(список;A1)))>0
=SUMPRODUCT(—ISNUMBER(SEARCH(things,A1)))>0
Объяснение
Для того чтобы выделить ячейки, содержащие одну из многих текстовых строк, вы можете использовать формулу, основанную на функциях ЕЧИСЛО и ПОИСК, вместе с функцией СУММПРОИЗВ.
В приведённом примере условное форматирование, использованное для B4:B11, использует эту формулу:
=СУММПРОИЗВ(—ЕЧИСЛО(ПОИСК(список;B4)))>0
=SUMPRODUCT(—ISNUMBER(SEARCH(things,B4)))>0
Как работает формула
В процессе вычисления эта часть формулы ищет каждую ячейку диапазона B4:B11 для всех значений в именованном диапазоне «список»:
=СУММПРОИЗВ(—ЕЧИСЛО(ПОИСК(список;B4)))>0
=SUMPRODUCT(—ISNUMBER(SEARCH(things,B4)))>0
Функция ПОИСК возвращает позицию найденного значения, а также ошибку #ЗНАЧ, если значение не найдено.
Для B4 результаты возвращаются в массив, подобный этому:
{8; # ЗНАЧ;! # ЗНАЧ!}
Функция ЕЧИСЛО изменяет все результаты на ИСТИНА или ЛОЖЬ:
{ИСТИНА; ЛОЖЬ; ЛОЖЬ}
Двойноеотрицание перед ЕЧИСЛО вычислят ИСТИНА / ЛОЖЬ как 1/0:
{1; 0; 0}
Затем функция СУММПРОИЗВ добавляет результаты, которые проверены на ноль:
=СУММПРОИЗВ({1;0;0})>0
=SUMPRODUCT({1;0;0})>0
Любой ненулевой результат означает, что было найдено хотя бы одно значение, поэтому формула возвращает ИСТИНА, применяя правило.
Вариант, чувствительный к регистру
Функция ПОИСК не чувствительна к регистру. Если вам нужно также проверить регистр, просто замените ПОИСК с НАЙТИ следующим образом:
=СУММПРОИЗВ(—ЕЧИСЛО(НАЙТИ(список;A1)))>0
=SUMPRODUCT(—ISNUMBER(FIND(things,A1)))>0
