Загальна формула
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Резюме
Щоб протестувати комірку для однієї з багатьох рядків, виключаючи інші, ви можете використовувати формулу, засновану на функціях ПОШУК, ІЗНОМ і СУММЕР. У наведеному прикладі формула в С5:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
де "включити" - іменований діапазон E5: E9, а "виключити" - іменований діапазон G5: G6.
Пояснення
По суті, ця формула використовує функцію SEARCH для пошуку кількох рядків усередині комірки. Усередині лівого SUMPRODUCT, SEARCH шукає всі рядки в названому діапазоні "include".
У правому SUMPRODUCT, SEARCH шукає всі рядки в названому діапазоні "виключити".
В обох частинах формули SEARCH повертає числові позиції, коли знайдені рядки, а помилки - коли ні. Функція ISNUMBER перетворює числа в TRUE, а помилки в FALSE, а подвійне від'ємне - значення TRUE FALSE в 1 і 0.
Результат на даний момент виглядає так:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Тоді:
=(1>0)*(0=0) =TRUE*TRUE =1
Примітка: ця формула повертає або 1, або нуль, які обробляються як TRUE і FALSE у формулах, умовному форматуванні або перевірці даних.
