Általános képlet
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Összegzés
Ha egy cellát tesztel a sok karaktersorozat közül, a többiek kizárásával, használjon a KERESÉS, SZÁM SZÁM és SUMPRODUCT függvényeken alapuló képletet. A bemutatott példában a C5 képlete a következő:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
ahol az "include" az elnevezett tartomány: E5: E9, és a "kizárás" a megnevezett tartomány: G5: G6.
Magyarázat
Lényegében ez a képlet a KERESÉS funkcióval keresi meg a karakterláncokat egy cellában. A bal SUMPRODUCT belsejében a SEARCH a "include" nevű tartomány összes karakterláncát keresi.
A jobb SUMPRODUCT-ban a SEARCH a "kizárás" nevű tartomány összes karakterláncát keresi.
A képlet mindkét részében a KERESÉS numerikus pozíciókat ad vissza, ha karakterláncokat talál, és hibákat, ha nem. Az ISNUMBER függvény a számokat IGAZ, a hibákat FALSE, a kettős negatív pedig az IGAZ HAMIS értékeket 1 és 0 értékekké alakítja.
Az eredmény ezen a ponton így néz ki:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Azután:
=(1>0)*(0=0) =TRUE*TRUE =1
Megjegyzés: ez a képlet 1-t vagy nulla értéket ad vissza, amelyeket az IGAZ és HAMIS formában kezelnek a képletekben, a feltételes formázásban vagy az adatok érvényesítésében.
