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