Generisk formel
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Sammanfattning
Om du vill testa en cell för att se om den innehåller alla objekt i en lista kan du göra det med en formel som använder SÖK-funktionen, med hjälp av funktionerna ISNUMBER, SUMPRODUCT och COUNTA.
Sammanhang
Låt oss säga att du har en lista med textsträngar i intervallet B5: B8, och du vill ta reda på om dessa celler innehåller alla orden i ett annat intervall, E5: E7.
Du kan bygga en formel som använder kapslade IF-uttalanden för att kontrollera efter varje objekt, men det kommer inte att skala bra om du har många saker att leta efter. Varje gång du lägger till ett ord att leta efter måste du lägga till ytterligare en kapslad IF och justera parenteser.
Lösning
Lösningen är att skapa en formel som räknar alla matchningar samtidigt. När vi väl har det, jämför vi helt enkelt det antalet med antalet artiklar vi letar efter. Om de matchar vet vi att en cell innehåller alla objekt.
I exemplet som visas är formeln vi använder:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Förklaring
Nyckeln är detta utdrag:
ISNUMBER(SEARCH(things,B5)
Detta är baserat på en annan formel (förklaras i detalj här) som helt enkelt kontrollerar en cell för en enda substring. Om cellen innehåller understrängen returnerar formeln SANT. Om inte, returnerar formeln FALSE.
Men om vi ger samma formel en lista över saker (i det här fallet använder vi ett namngivet intervall som heter "saker", E5: E7), kommer det att ge oss tillbaka en lista med SANT / FALSKA värden, en för varje artikel i . Resultatet är en matris som ser ut så här:
(SANT; SANT; SANT)
Där varje SANT representerar ett hittat objekt och varje FALSK representerar ett objekt som inte hittades.
Vi kan tvinga SANT / FALSKA värden till 1s och 0s med en dubbel negativ (-, även kallad dubbel unary):
--ISNUMBER(SEARCH(things,B5))
som ger en array som denna:
(1; 1; 1)
Därefter bearbetar vi denna array med SUMPRODUCT, vilket ger oss en total summa. Om denna summa är lika med antalet artiklar i det namngivna intervallet "saker" vet vi att vi har hittat alla saker och kan returnera SANT. Sättet vi gör detta är att jämföra de två siffrorna direkt. Vi får ett antal icke-tomma celler i "saker" med COUNTA:
=COUNTA(things)
Med en hårdkodad lista
Det finns inget krav på att du använder ett intervall för din lista över saker. Om du bara letar efter ett litet antal saker kan du använda en lista i array-format, som kallas en arraykonstant. Om du till exempel bara letar efter färgerna röd, blå och grön kan du använda ("röd", "blå", "grön") så här:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
