Generisk formel
=SUMPRODUCT(--(A1=things))>0
Sammendrag
Hvis du vil teste en celle for å se om den tilsvarer en av flere ting, kan du gjøre det med en formel som bruker SUMPRODUCT-funksjonen.
Kontekst
La oss si at du har en liste over tekststrenger i området B5: B11, og at du vil teste hver celle mot en annen liste over ting i området E5: E9. Med andre ord, for hver celle i B5: B11, vil du vite: tilsvarer denne cellen noen av tingene i E5: E9?
Du kan begynne å bygge en stor formel basert på nestede IF-setninger, men en matriseformel basert på SUMPRODUCT er en enklere og renere tilnærming.
Løsning
Løsningen er å lage en formel som vil teste for flere verdier og returnere en liste over SANNE / FALSE verdier. Når vi har det, kan vi behandle den listen (en matrise, faktisk) med SUMPRODUCT.
Formelen vi bruker ser slik ut:
=SUMPRODUCT(--(B5=things))>0
Forklaring
Nøkkelen er denne kodebiten:
--(B5=things)
som bare sammenligner verdien i B5 til hver verdi i det nevnte området "ting". Fordi vi sammenligner B5 med en matrise (dvs. det navngitte området "ting", E5: E11), blir resultatet en matrise med SANNE / FALSE verdier som dette:
(SANT; FALSE; FALSE; FALSE; FALSE)
Hvis vi har til og med en SANT i matrisen, vet vi at B5 tilsvarer minst en ting i listen, så for å tvinge SANNE / FALSE verdiene til 1s og 0s, bruker vi en dobbel negativ (-, også kalt en dobbel unary). Etter denne tvangen har vi dette:
(1; 0; 0; 0; 0)
Nå behandler vi resultatet med SUMPRODUCT, som vil legge sammen elementene i matrisen. Hvis vi får et resultat som ikke er null, har vi minst en kamp, så vi bruker> 1 for å tvinge et sluttresultat av enten SANT eller FALSK.
Med en hardkodet liste
Det er ikke noe krav at du bruker et område for listen over ting. Hvis du bare leter etter et lite antall ting, kan du bruke en liste i matriseformat, som kalles en matrisekonstant. Hvis du for eksempel bare leter etter fargene rød, blå og grønn, kan du bruke ("rød", "blå", "grønn") slik:
--(B5=("red","blue","green"))
Håndterer ekstra mellomrom
Hvis cellene du tester inneholder ekstra plass, vil de ikke matche ordentlig. For å fjerne all ekstra plass, kan du endre formelen for å bruke TRIM-funksjonen slik:
=SUMPRODUCT(--(TRIM(A1)=things))>0
