Generisk formel
(=AND(COUNT(SEARCH(inc,A1))>0,COUNT(SEARCH(exc,A1))=0))
Sammanfattning
För att testa en cell för att se om den innehåller vissa ord men inte andra kan du använda en matrisformel baserad på RÄKN- och SÖK-funktionerna, inslagna i OCH-funktionen.
I exemplet som visas är formeln i C5:
(=AND(COUNT(SEARCH(inc,B5))>0,COUNT(SEARCH(exc,B5))=0))
Denna formel returnerar SANT när B5 innehåller något av orden i det namngivna intervallet inc och inget av orden i det namngivna området exc .
Detta är en matrisformel och måste anges med Control + Shift + Enter.
Förklaring
Denna formel är beroende av OCH-funktionen för att testa två villkor samtidigt:
- Räkna ord från namngivet område inc är> 0
- Antal ord från det angivna intervallet exc är = 0
Om båda villkoren är SANT returnerar formeln SANT. Om något av villkoren är FALSE returnerar formeln FALSE.
Testet för flera ord görs med SEARCH-funktionen med hjälp av COUNT.
När SEARCH tar emot mer än ett objekt att leta efter returnerar det en rad resultat, ett per objekt. När en match hittas returnerar SEARCH matchens position. När ingen matchning hittas returnerar SEARCH #VALUE-felet. COUNT-funktionen får ett antal siffror i matrisen som returneras av SEARCH. COUNT ignorerar fel som standard.
I det visade exemplet löses formeln i steg som detta
=AND(COUNT((1;11;#VALUE!))>0,COUNT((#VALUE!;#VALUE!))=0) =AND(2>0,0=0) =AND(TRUE,TRUE) =TRUE
Med hårdkodade värden
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. För att till exempel testa för rött, blått eller grönt, men utesluter rosa och orange, kan du använda:
=AND(COUNT(SEARCH(("red","blue","green"),B5))>0,COUNT(SEARCH(("orange","pink"),B5))=0)
Denna version kräver inte kontroll + skift + ange matrisformelsyntax.
