Общая формула
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Резюме
Чтобы проверить ячейку на наличие одной из многих строк, исключая другие, вы можете использовать формулу, основанную на функциях ПОИСК, ЕЧИСЛО и СУММПРОИЗВ. В показанном примере формула в C5:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
где «include» - именованный диапазон E5: E9, а «exclude» - именованный диапазон G5: G6.
Объяснение
По сути, эта формула использует функцию ПОИСК для поиска нескольких строк внутри ячейки. Внутри левого SUMPRODUCT SEARCH ищет все строки в названном диапазоне "include".
В правом SUMPRODUCT ПОИСК ищет все строки в названном диапазоне "exclude".
В обеих частях формулы ПОИСК возвращает числовые позиции, когда строки найдены, и ошибки, когда нет. Функция ЕЧИСЛО преобразует числа в ИСТИНА, а ошибки в ЛОЖЬ, а двойное отрицательное значение преобразует ИСТИНА ЛОЖЬ в 1 и 0.
Результат на этом этапе выглядит так:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Потом:
=(1>0)*(0=0) =TRUE*TRUE =1
Примечание: эта формула возвращает либо 1, либо ноль, которые обрабатываются как ИСТИНА и ЛОЖЬ в формулах, условном форматировании или проверке данных.
