Generisk formel
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Sammendrag
For å teste en celle for en av mange strenger, mens du ekskluderer andre, kan du bruke en formel basert på funksjonene SØK, ISNUMBER og SUMPRODUKT. I eksemplet vist er formelen i C5:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
hvor "inkluderer" er det navngitte området E5: E9, og "ekskluder" er det navngitte området G5: G6.
Forklaring
I kjernen bruker denne formelen SØK-funksjonen til å lete etter flere strenger inne i en celle. Inne i venstre SUMPRODUCT ser SEARCH etter alle strenger i det valgte området "inkluderer".
I høyre SUMPRODUCT ser SEARCH etter alle strenger i det valgte området "ekskluder".
I begge deler av formelen returnerer SØK numeriske posisjoner når strenger blir funnet, og feil når ikke. ISNUMBER-funksjonen konverterer tallene til SANT og feil til FALSE, og den dobbelte negative konverterer SANNE FALSE-verdiene til 1 og 0.
Resultatet på dette tidspunktet ser slik ut:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Deretter:
=(1>0)*(0=0) =TRUE*TRUE =1
Merk: denne formelen returnerer enten 1 eller null, som håndteres som SANT og FALSK i formler, betinget formatering eller datavalidering.
