Generisk formel
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Sammendrag
Hvis du vil teste en celle for å se om den inneholder alle elementene i en liste, kan du gjøre det med en formel som bruker SØK-funksjonen, med hjelp fra funksjonene ISNUMBER, SUMPRODUCT og COUNTA.
Kontekst
La oss si at du har en liste over tekststrenger i området B5: B8, og du vil finne ut om disse cellene inneholder alle ordene i et annet område, E5: E7.
Du kan bygge en formel som bruker nestede IF-setninger for å sjekke for hvert element, men som ikke skaleres bra hvis du har mange ting å se etter. Hver gang du legger til et ord å se etter, må du legge til en ny nestet IF og justere parenteser.
Løsning
Løsningen er å lage en formel som teller alle treff samtidig. Når vi har det, sammenligner vi ganske enkelt det antallet med antall varer vi leter etter. Hvis de samsvarer, vet vi at en celle inneholder alle elementene.
I eksemplet som er vist er formelen vi bruker:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Forklaring
Nøkkelen er denne kodebiten:
ISNUMBER(SEARCH(things,B5)
Dette er basert på en annen formel (forklart i detalj her) som bare sjekker en celle for en enkelt understreng. Hvis cellen inneholder undergrunnen, returnerer formelen SANT. Hvis ikke, returnerer formelen FALSE.
Men hvis vi gir den samme formelen en liste over ting (i dette tilfellet bruker vi et navngitt område kalt "ting", E5: E7), vil det gi oss en liste over SANTE / FALSE verdier, en for hvert element i . Resultatet er en matrise som ser slik ut:
(SANT; SANT; SANT)
Hvor hver SANT representerer et funnet element, og hver FALSE representerer et element som ikke ble funnet.
Vi kan tvinge de SANTE / FALSE verdiene til 1s og 0s med en dobbel negativ (-, også kalt en dobbel unary):
--ISNUMBER(SEARCH(things,B5))
som gir en matrise som dette:
(1; 1; 1)
Deretter behandler vi denne matrisen med SUMPRODUCT, som vil gi oss en total sum. Hvis denne summen er lik antall varer i det nevnte området "ting", vet vi at vi har funnet alle ting og kan returnere SANT. Måten vi gjør dette på er å sammenligne de to tallene direkte. Vi får et antall ikke-tomme celler i "ting" ved bruk av COUNTA:
=COUNTA(things)
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:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
