Generieke formule
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Samenvatting
Om een cel te testen om te zien of deze een van de vele strings bevat, kunt u een formule gebruiken die is gebaseerd op de functies SEARCH, ISNUMBER en SUMPRODUCT. De formule in C5, gekopieerd, is:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
waar dingen het benoemde bereik E5: E9 is.
Uitleg
We willen elke cel in B5: B11 testen om te zien of deze een van de strings bevat in de genoemde reeks dingen (E5: E9). De formule die we gebruiken in C5, gekopieerd, is:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Deze formule is gebaseerd op een formule (hier uitgelegd) die een cel controleert op een enkele subtekenreeks. Als de cel de subtekenreeks bevat, retourneert de formule WAAR. Als dit niet het geval is, retourneert de formule FALSE:
ISNUMBER(SEARCH(things,B5))
In dit geval geven we SEARCH echter een lijst met strings. Omdat dingen 5 strings bevatten , geeft SEARCH 5 resultaten terug in een array als deze:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Als SEARCH een string vindt, geeft het de positie van die string terug. Als SEARCH geen tekenreeks vindt, retourneert het een #VALUE! fout. Omdat "geel" als eerste woord in B5 verschijnt, zien we een 1. Omdat de andere strings niet worden gevonden, zijn de andere 4 items fouten.
Deze array wordt direct teruggestuurd naar de ISNUMBER-functie. ISNUMBER retourneert vervolgens een reeks TRUE / FALSE-waarden:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Als we zelfs maar één TRUE in de array hebben, weten we dat een cel minstens één van de strings bevat waarnaar we op zoek zijn. De eenvoudigste manier om te controleren of TRUE is, is door alle waarden bij elkaar op te tellen. We kunnen dat doen met SUMPRODUCT, maar eerst moeten we de TRUE / FALSE-waarden forceren naar 1s en 0s met een dubbel negatief (-) als volgt:
--ISNUMBER(SEARCH(things,B5))
Dit levert een nieuwe array op met alleen 1s en 0s:
(1;0;0;0;0)
rechtstreeks geleverd aan SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
Met slechts één array om te verwerken, voegt SOMPRODUCT de items in de array toe en retourneert een resultaat. Elk resultaat dat niet gelijk is aan nul betekent dat we een "treffer" hebben, dus voegen we> 0 toe om een eindresultaat van WAAR of ONWAAR te forceren:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
Met een hardgecodeerde lijst
Het is niet nodig om een bereik te gebruiken voor de lijst met strings waarnaar moet worden gezocht. U kunt ook een matrixconstante gebruiken. Als u bijvoorbeeld wilt controleren op 'rood', 'blauw' en 'groen', kunt u een formule als deze gebruiken:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Voorkomen van valse overeenkomsten
Een probleem met deze benadering is dat u mogelijk valse overeenkomsten krijgt van subtekenreeksen die in langere woorden voorkomen. Als u bijvoorbeeld "dr" probeert te matchen, kunt u ook "Andrea", "drinken", "droog", enz. Vinden, aangezien "dr" in deze woorden voorkomt. Dit gebeurt omdat SEARCH automatisch een "bevat" -overeenkomst doet.
Voor een snelle hack kun je spatie toevoegen rond de zoekwoorden (bijv. "Dr" of "dr") om te voorkomen dat je "dr" in een ander woord ophaalt. Maar dit zal mislukken als "dr" als eerste of laatste in een cel verschijnt, of met interpunctie.
Als u een nauwkeurigere oplossing nodig heeft, is een optie om de tekst eerst in een hulpkolom te normaliseren, waarbij u ervoor zorgt dat u ook een voorloop- en volgspatie toevoegt. Dan gebruik je de formule op deze pagina op de resulterende tekst.
