Generieke formule
=SUMPRODUCT(--(A1=things))>0
Samenvatting
Als u een cel wilt testen om te zien of deze gelijk is aan een van meerdere dingen, kunt u dit doen met een formule die de functie SOMPRODUCT gebruikt.
Context
Stel dat u een lijst met tekstreeksen heeft in het bereik B5: B11, en u wilt elke cel testen met een andere lijst met dingen in het bereik E5: E9. Met andere woorden, voor elke cel in B5: B11 wil je weten: is deze cel gelijk aan een van de dingen in E5: E9?
U zou kunnen beginnen met het bouwen van een grote formule op basis van geneste IF-instructies, maar een matrixformule op basis van SUMPRODUCT is een eenvoudigere, schonere benadering.
Oplossing
De oplossing is om een formule te maken die op meerdere waarden test en een lijst met TRUE / FALSE-waarden retourneert. Zodra we dat hebben, kunnen we die lijst (eigenlijk een array) verwerken met SUMPRODUCT.
De formule die we gebruiken ziet er als volgt uit:
=SUMPRODUCT(--(B5=things))>0
Uitleg
De sleutel is dit fragment:
--(B5=things)
die eenvoudigweg de waarde in B5 vergelijkt met elke waarde in het genoemde bereik "dingen". Omdat we B5 vergelijken met een array (dwz het benoemde bereik "dingen", E5: E11), zal het resultaat een array van WAAR / ONWAAR waarden zijn, zoals deze:
(TRUE; FALSE; FALSE; FALSE; FALSE)
Als we zelfs maar één TRUE in de array hebben, weten we dat B5 gelijk is aan ten minste één ding in de lijst, dus om de TRUE / FALSE-waarden te forceren naar 1s en 0s, gebruiken we een dubbel negatief (-, ook wel een double genoemd unair). Na deze dwang hebben we dit:
(1; 0; 0; 0; 0)
Nu verwerken we het resultaat met SOMPRODUCT, waarmee de elementen in de array worden opgeteld. Als we een resultaat krijgen dat niet gelijk is aan nul, hebben we minstens één overeenkomst, dus gebruiken we> 1 om een eindresultaat van WAAR of ONWAAR te forceren.
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:
--(B5=("red","blue","green"))
Omgaan met extra ruimtes
Als de cellen die u test extra ruimte bevatten, komen ze niet goed overeen. Om alle extra ruimte te verwijderen, kunt u de formule wijzigen om de TRIM-functie als volgt te gebruiken:
=SUMPRODUCT(--(TRIM(A1)=things))>0
