Samenvatting
Om rijen te tellen met OR-logica, kunt u een formule gebruiken die is gebaseerd op de functie SOMPRODUCT. In het getoonde voorbeeld is de formule in G6:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
waarbij groep (B5: B15), kleur1 (C5: C15) en kleur2 (D5: D15) benoemde bereiken zijn.
Uitleg
Een van de moeilijkere problemen in Excel is het tellen van rijen in een set gegevens met "OF-logica". Er zijn twee basisscenario's: (1) u wilt rijen tellen waarvan een waarde in een kolom ‘x’ OF ‘y’ is (2) u wilt rijen tellen waarvan de waarde ‘x’ in de ene of andere kolom voorkomt .
In dit voorbeeld is het doel om rijen te tellen waarbij groep = "a" EN Kleur1 OF Kleur2 "rood" zijn. Dit betekent dat we werken met scenario 2 hierboven.
Met COUNTIFS
U zou in eerste instantie kunnen bereiken voor de COUNTIFS-functie, die native meerdere criteria verwerkt. De functie COUNTIFS voegt echter voorwaarden samen met AND-logica, dus alle criteria moeten WAAR zijn om in de telling te worden opgenomen:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Dit maakt COUNTIFS onwerkbaar, tenzij we meerdere instanties van COUNTIFS gebruiken:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Vertaling: tel rijen waar groep "a" is en kleur1 is "rood" + tel rijen waar groep "a" is en kleur2 is "rood" - tel rijen waar groep "a" is en kleur1 is "rood" en kleur2 is " red "(om dubbeltellingen te voorkomen).
Dit werkt, maar u kunt zien dat dit een ietwat gecompliceerde en overbodige formule is.
Met Booleaanse logica
Een betere oplossing is om Booleaanse logica te gebruiken en het resultaat te verwerken met de SOMPRODUCT-functie. (Als je een inleiding over Booleaanse algebra nodig hebt, biedt deze video een inleiding.) In het getoonde voorbeeld is de formule in G6:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
waarbij groep (B5: B15), kleur1 (C5: C15) en kleur2 (D5: D15) benoemde bereiken zijn.
Het eerste deel van het probleem is om te testen op group = "a", wat we als volgt doen:
(group="a")
Omdat het bereik B5: B15 11 cellen bevat, retourneert deze uitdrukking als volgt een array van 11 WAAR en ONWAAR waarden:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Elke WAAR staat voor een rij waarin de groep "A" is.
Vervolgens moeten we controleren op de waarde "rood" in kolom1 of kolom2. We doen dit met twee uitdrukkingen die zijn samengevoegd door optellen (+), aangezien optellen overeenkomt met OR-logica in Booleaanse algebra:
(color1="red")+(color2="red")
Excel evalueert automatisch de waarden TRUE en FALSE als 1s en 0s tijdens elke wiskundige bewerking, dus het resultaat van de bovenstaande uitdrukking is een array als deze:
(2;0;0;1;1;0;1;0;0;0;1)
Het eerste getal in de array is 2, omdat zowel Color1 als Color2 "rood" zijn in de eerste rij. Om redenen die hieronder worden uitgelegd, moeten we ons tegen deze situatie beschermen door te controleren op waarden groter dan nul:
((2;0;0;1;1;0;1;0;0;0;1))>0
Nu hebben we weer een reeks TRUE en FALSE waarden:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
De onderstaande tabel geeft een overzicht van hoe Excel de hierboven beschreven kleurenlogica evalueert:
Op dit moment hebben we resultaten van het testen van Group = "a" in één array:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
En resultaten van het testen van "rood" in Color1 of Color2 in een andere array:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
De volgende stap is om deze twee arrays samen te brengen met "AND logic". Om dit te doen, gebruiken we vermenigvuldiging (*), aangezien vermenigvuldiging overeenkomt met EN-logica in Booleaanse algebra.
Nadat we de twee arrays met elkaar hebben vermenigvuldigd, hebben we een enkele array van 1s en 0s, die rechtstreeks naar de SUMPRODUCT-functie wordt gestuurd:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
De functie SOMPRODUCT retourneert de som van getallen, 2, als een eindresultaat. Dit is het aantal rijen waarin groep = "a" EN Kleur1 OF Kleur2 "rood" zijn.
Om dubbeltellingen te voorkomen
We willen rijen niet dubbel tellen waar zowel Color1 als Color2 "rood" zijn. Daarom controleren we de resultaten van (color1 = "red") + (color2 = "red") voor waarden groter dan nul in de onderstaande code:
((color1="red")+(color2="red"))>0
Zonder deze controle zouden de 2 van de eerste rij in de gegevens verschijnen in de laatste array en ervoor zorgen dat de formule ten onrechte 3 retourneert als de laatste telling.
FILTER optie
Een leuk ding over Booleaanse logica is dat het perfect werkt met de nieuwste functies van Excel, zoals XLOOKUP en FILTER. De FILTER-functie kan bijvoorbeeld exact dezelfde logica gebruiken die hierboven is uitgelegd om overeenkomende rijen te extraheren:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Het resultaat van FILTER zijn de twee rijen die voldoen aan de criteria, zoals hieronder te zien is:
Als u meer wilt weten over deze nieuwe functies, hebben we een overzicht en videotraining.
