Generisk formel
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Resumé
Hvis du vil teste en celle for at se, om den indeholder alle elementerne på en liste, kan du gøre det med en formel, der bruger SEARCH-funktionen med hjælp fra ISNUMBER-, SUMPRODUCT- og COUNTA-funktionerne.
Sammenhæng
Lad os sige, at du har en liste over tekststrenge i området B5: B8, og du vil finde ud af, om disse celler indeholder alle ordene i et andet interval, E5: E7.
Du kan opbygge en formel, der bruger indlejrede IF-udsagn til at kontrollere for hvert element, men det skaleres ikke godt, hvis du har mange ting at se efter. Hver gang du tilføjer et ord, du skal kigge efter, skal du tilføje et andet indlejret IF og justere parenteser.
Løsning
Løsningen er at oprette en formel, der tæller alle matches på én gang. Når vi har det, sammenligner vi simpelthen det antal med antallet af varer, vi leder efter. Hvis de matcher, ved vi, at en celle indeholder alle elementer.
I det viste eksempel er formlen, vi bruger:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Forklaring
Nøglen er dette uddrag:
ISNUMBER(SEARCH(things,B5)
Dette er baseret på en anden formel (forklaret detaljeret her), der simpelthen kontrollerer en celle for en enkelt understreng. Hvis cellen indeholder understrengen, returnerer formlen SAND. Hvis ikke, returnerer formlen FALSK.
Men hvis vi giver den samme formel en liste over ting (i dette tilfælde bruger vi et navngivet område kaldet "ting", E5: E7), vil det give os en liste over SANDE / FALSKE værdier tilbage, en for hvert element i . Resultatet er en matrix, der ser sådan ud:
(SAND; SAND; SAND)
Hvor hver SAND repræsenterer et fundet element, og hver FALSK repræsenterer et element, der ikke blev fundet.
Vi kan tvinge de SANDE / FALSKE værdier til 1s og 0s med en dobbelt negativ (-, også kaldet en dobbelt unary):
--ISNUMBER(SEARCH(things,B5))
som giver en matrix som denne:
(1; 1; 1)
Dernæst behandler vi denne matrix med SUMPRODUCT, som giver os en samlet sum. Hvis denne sum er lig med antallet af varer i det navngivne interval "ting", ved vi, at vi har fundet alle ting og kan returnere SAND. Den måde, vi gør dette på, er at sammenligne de to tal direkte. Vi får et antal ikke-tomme celler i "ting" ved hjælp af COUNTA:
=COUNTA(things)
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:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
