Общая формула
(=AND(COUNT(SEARCH(inc,A1))>0,COUNT(SEARCH(exc,A1))=0))
Резюме
Чтобы проверить ячейку на наличие определенных слов, но не других, вы можете использовать формулу массива, основанную на функциях COUNT и SEARCH, заключенных в функцию AND.
В показанном примере формула C5 имеет следующий вид:
(=AND(COUNT(SEARCH(inc,B5))>0,COUNT(SEARCH(exc,B5))=0))
Эта формула возвращает значение ИСТИНА, если B5 содержит любое из слов в именованном диапазоне inc и ни одно из слов в именованном диапазоне exc .
Это формула массива, и ее нужно вводить с помощью Control + Shift + Enter.
Объяснение
Эта формула использует функцию И для проверки двух условий одновременно:
- Количество слов из именованного диапазона inc > 0
- Количество слов из именованного диапазона exc = 0
Если оба условия ИСТИНА, формула возвращает ИСТИНА. Если любое из условий - ЛОЖЬ, формула возвращает ЛОЖЬ.
Тест на несколько слов выполняется с помощью функции ПОИСК с помощью COUNT.
Когда SEARCH получает более одного элемента для поиска, он возвращает массив результатов, по одному на элемент. Когда совпадение найдено, ПОИСК возвращает позицию совпадения. Если совпадений не найдено, ПОИСК возвращает ошибку # ЗНАЧЕНИЕ. Функция COUNT получает количество чисел в массиве, возвращаемом функцией SEARCH. COUNT по умолчанию игнорирует ошибки.
В показанном примере формула решается в виде следующих шагов
=AND(COUNT((1;11;#VALUE!))>0,COUNT((#VALUE!;#VALUE!))=0) =AND(2>0,0=0) =AND(TRUE,TRUE) =TRUE
С жестко запрограммированными значениями
Не требуется, чтобы вы использовали диапазон для своего списка вещей. Если вы ищете лишь небольшое количество вещей, вы можете использовать список в формате массива, который называется константой массива. Например, чтобы проверить красный, синий или зеленый, но исключить розовый и оранжевый, вы можете использовать:
=AND(COUNT(SEARCH(("red","blue","green"),B5))>0,COUNT(SEARCH(("orange","pink"),B5))=0)
Эта версия не требует синтаксиса формулы массива control + shift + enter.
