Generieke formule
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Samenvatting
Als u een cel wilt testen om te zien of deze alle items in een lijst bevat, kunt u dit doen met een formule die de functie SEARCH gebruikt, met hulp van de functies ISNUMBER, SUMPRODUCT en COUNTA.
Context
Stel dat u een lijst met tekstreeksen heeft in het bereik B5: B8, en u wilt weten of deze cellen alle woorden uit een ander bereik, E5: E7, bevatten.
U zou een formule kunnen maken die geneste IF-instructies gebruikt om voor elk item te controleren, maar die zal niet goed schalen als u veel dingen moet zoeken. Elke keer dat u een woord toevoegt om naar te zoeken, moet u een andere geneste IF toevoegen en haakjes aanpassen.
Oplossing
De oplossing is om een formule te maken en alle overeenkomsten in één keer te tellen. Zodra we dat hebben, vergelijken we dat aantal met het aantal items dat we zoeken. Als ze overeenkomen, weten we dat een cel alle items bevat.
In het getoonde voorbeeld is de formule die we gebruiken:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Uitleg
De sleutel is dit fragment:
ISNUMBER(SEARCH(things,B5)
Dit is gebaseerd op een andere formule (hier in detail uitgelegd) die eenvoudig 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.
Als we dezelfde formule echter een lijst met dingen geven (in dit geval gebruiken we een benoemd bereik met de naam 'dingen', E5: E7), dan krijgen we een lijst met WAAR / ONWAAR waarden terug, één voor elk item in . Het resultaat is een array die er als volgt uitziet:
(WAAR; WAAR; WAAR)
Waar elke WAAR staat voor een gevonden item en elke ONWAAR staat voor een item dat niet is gevonden.
We kunnen de TRUE / FALSE-waarden forceren naar 1s en 0s met een dubbel negatief (-, ook wel een dubbel unair genoemd):
--ISNUMBER(SEARCH(things,B5))
wat een array als deze oplevert:
(1; 1; 1)
Vervolgens verwerken we deze array met SUMPRODUCT, wat ons een totaalbedrag oplevert. Als deze som gelijk is aan het aantal items in het benoemde bereik "dingen", weten we dat we alle dingen hebben gevonden en kunnen we WAAR retourneren. De manier waarop we dit doen, is door de twee getallen rechtstreeks te vergelijken. We krijgen een telling van niet-lege cellen in "dingen" met COUNTA:
=COUNTA(things)
Met een hardgecodeerde lijst
Het is niet vereist dat u een bereik gebruikt voor uw lijst met dingen. Als u slechts naar een klein aantal dingen zoekt, kunt u een lijst in matrixindeling gebruiken, die een matrixconstante wordt genoemd. Als u bijvoorbeeld alleen naar de kleuren rood, blauw en groen zoekt, kunt u ("rood", "blauw", "groen") als volgt gebruiken:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
