Обща формула
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Обобщение
За да тествате клетка за един от многото низове, като същевременно изключвате други, можете да използвате формула, базирана на функциите SEARCH, ISNUMBER и SUMPRODUCT. В показания пример формулата в C5 е:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
където "включва" е наименуваният диапазон E5: E9, а "изключва" е наименуваният диапазон G5: G6.
Обяснение
В основата си тази формула използва функцията SEARCH за търсене на множество низове в клетка. Вътре в левия SUMPRODUCT, SEARCH търси всички низове в посочения диапазон "включва".
В десния 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 във формули, условно форматиране или валидиране на данни.
