Generisk formel
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Sammanfattning
För att testa en cell för en av många strängar, medan du exkluderar andra, kan du använda en formel baserad på funktionerna SÖK, ISNUMBER och SUMPRODUCT. I exemplet som visas är formeln i C5:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
där "inkludera" är det namngivna området E5: E9 och "exkludera" är det namngivna området G5: G6.
Förklaring
Kärnan använder denna formel SÖK-funktionen för att leta efter flera strängar inuti en cell. Inuti vänster SUMPRODUCT letar SEARCH efter alla strängar i det angivna intervallet "inkluderar".
I höger SUMPRODUCT letar SEARCH efter alla strängar i det angivna intervallet "exkludera".
I båda delarna av formeln returnerar SÖK numeriska positioner när strängar hittas och fel när inte. ISNUMBER-funktionen omvandlar siffrorna till SANT och fel till FALSKA, och det dubbla negativa omvandlar SANT FALSKT till 1 och 0.
Resultatet vid denna punkt ser ut så här:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Sedan:
=(1>0)*(0=0) =TRUE*TRUE =1
Obs: den här formeln returnerar antingen 1 eller noll, som hanteras som SANT och FALSK i formler, villkorlig formatering eller datavalidering.
