Generisk formel
=SUMPRODUCT(--(A1=things))>0
Resumé
Hvis du vil teste en celle for at se, om den er lig med en af flere ting, kan du gøre det med en formel, der bruger SUMPRODUCT-funktionen.
Sammenhæng
Lad os sige, at du har en liste over tekststrenge i området B5: B11, og at du vil teste hver celle mod en anden liste over ting i området E5: E9. Med andre ord, for hver celle i B5: B11 vil du vide: svarer denne celle til noget af tingene i E5: E9?
Du kan begynde at opbygge en stor formel baseret på indlejrede IF-udsagn, men en matrixformel baseret på SUMPRODUCT er en enklere og renere tilgang.
Løsning
Løsningen er at oprette en formel, der vil teste for flere værdier og returnere en liste med SAND / FALSK-værdier. Når vi har det, kan vi behandle denne liste (en matrix faktisk) med SUMPRODUCT.
Formlen, vi bruger, ser sådan ud:
=SUMPRODUCT(--(B5=things))>0
Forklaring
Nøglen er dette uddrag:
--(B5=things)
der blot sammenligner værdien i B5 med hver værdi i det navngivne interval "ting". Fordi vi sammenligner B5 med en matrix (dvs. det navngivne interval "ting", E5: E11), vil resultatet være en matrix med SANDE / FALSKE værdier som denne:
(SAND; FALSK; FALSK; FALSK; FALSK)
Hvis vi selv har en SAND i matrixen, ved vi, at B5 svarer til mindst én ting på listen, så for at tvinge de SANDE / FALSE-værdier til 1s og 0s bruger vi en dobbelt negativ (-, også kaldet en dobbelt unary). Efter denne tvang har vi dette:
(1; 0; 0; 0; 0)
Nu behandler vi resultatet med SUMPRODUCT, som tilføjer elementerne i arrayet. Hvis vi får et resultat, der ikke er nul, har vi mindst en kamp, så vi bruger> 1 til at tvinge et slutresultat af enten SAND eller FALSK.
Med en hårdkodet liste
Der er ikke noget krav om, at du bruger et interval til din liste over ting. Hvis du kun leder efter et lille antal ting, kan du bruge en liste i matrixformat, der kaldes en matrixkonstant. For eksempel, hvis du bare leder efter farverne rød, blå og grøn, kan du bruge ("rød", "blå", "grøn") sådan:
--(B5=("red","blue","green"))
Håndtering af ekstra mellemrum
Hvis cellerne, du tester, indeholder ekstra plads, matcher de ikke korrekt. For at fjerne alt ekstra plads kan du ændre formlen for at bruge TRIM-funktionen som sådan:
=SUMPRODUCT(--(TRIM(A1)=things))>0
