Booleaanse logica voor beginners

Op mijn website leg ik veel formules uit. In sommige van die formules gebruik ik een principe dat booleaanse logica wordt genoemd. Dit soort formules zien er al gauw ingewikkeld uit, omdat ze vaak wat langer zijn.

Toch is het principe achter booleaanse logica vrij eenvoudig. En als je het eenmaal doorhebt, kun je slimmere en flexibelere formules maken. Je kunt hiermee bijvoorbeeld voorwaarden combineren, en dus rekenen op basis van meerdere criteria, zonder dat je hulpkolommen nodig hebt.

In dit artikel leg ik stap voor stap uit wat booleaanse logica is, hoe dit in de context van Excel werkt, en hoe jij dit daarna zelf kunt toepassen op je eigen formules.

Wat is booleaanse logica?

Booleaanse logica draait om twee mogelijke uitkomsten:

• WAAR
• ONWAAR

Een vergelijking of voorwaarde levert altijd één van deze twee waarden op.

In onderstaand voorbeeld staat in cel A2 het getal 15. Met de vergelijkingsoperator > (groter dan) controleert Excel of de inhoud van cel A2 groter is dan 5. Dat is het geval, en dus retourneert Excel WAAR.

De formule =A3>5 retourneert ONWAAR, omdat cel A3 de waarde ‘5’ bevat, en dus niet groter is dan 5.

Dit principe komt eigenlijk uit de wiskunde, maar je komt het in Excel vaak tegen bij vergelijkingen en het maken van voorwaarden.

Booleaanse logica in Excel

Zoals je kunt zien in het voorbeeld retourneert Excel letterlijk WAAR of ONWAAR. Maar het is belangrijk om te weten dat Excel deze waarden intern als getallen behandelt.

• WAAR = 1
• ONWAAR = 0

Dit betekent dat je met WAAR en ONWAAR kunt rekenen, ook al lijkt dat in eerste instantie misschien vreemd.

In onderstaand voorbeeld heb ik in kolom B een aantal vergelijkingen gemaakt (de inhoud van kolom A is groter dan 5). Hier komt steeds WAAR en ONWAAR uit. In kolom C heb ik vervolgens een som gemaakt (optellen of vermenigvuldigen). In kolom D kun je zien welke formule er is gebruikt, bijvoorbeeld cel B2 (WAAR) + 0 = 1

Nu gaan we een stapje verder door te kijken hoe je dit kunt gebruiken om EN- en OF vergelijkingen te maken.

EN en OF logica met booleaanse waarden

Laten we beginnen met een eenvoudig voorbeeld, zodat het principe duidelijk wordt. In onderstaand voorbeeld staat in kolom A een aantallen getallen en in kolom B een regio.

Stel dat we twee voorwaarden willen controleren:

• Het getal is groter dan 10
• De regio is Noord

EN logica met vermenigvuldigen

Bij een EN voorwaarde moeten beide voorwaarden waar zijn. Zoals je inmiddels weet wordt in Excel WAAR als 1 behandelt en ONWAAR als 0.

Met de formule =(A2>10)*(B2="Noord") vermenigvuldigen we de logische vergelijkingen met elkaar.

Je kunt bovenstaand voorbeeld als volgt zien:

Alleen als beide voorwaarden WAAR zijn, krijg je een 1 als uitkomst. In ons voorbeeld is dat alleen in rij 2 (20 en Noord) en rij 7 (15 en Noord).

OF logica met optellen

Bij een OF voorwaarde hoeft minstens één van de voorwaarden waar te zijn. Dit kun je doen door de booleaanse waarden op te tellen. In dit voorbeeld gebruiken we wederom de volgende twee voorwaarden:

• Het getal is groter dan 10
• De regio is Noord

Met deze formule zijn er die verschillende uitkomsten mogelijk:

• 0 houdt in dat geen enkele voorwaarde WAAR is
• 1 houdt in dat één van de voorwaarden WAAR is
• 2 houdt in dat beide voorwaarden WAAR zijn

In ons voorbeeld gebeurt er het volgende:

Het is belangrijk om te weten dat de formule die we hebben gemaakt een getal retourneert, en geen WAAR of ONWAAR.

Het kan dus voorkomen dat je indien beide voorwaarden waar zijn, het resultaat 2 is. In sommige gevallen dien je dit op te lossen door een extra vergelijking toe te voegen:

=((A2>10)+(B2="Noord"))>0

Met deze formule zijn de volgende uitkomsten mogelijk:

• 0 resulteert in ONWAAR en dus 0
• 1 of 2 resulteert in WAAR en dus 1

Of dit nodig is, hangt af van hoe je deze uitkomst verder gebruikt in je formule. In sommige situaties is elke waarde groter dan 0 voldoende, maar in andere gevallen kan een waarde van 2 juist tot een verkeerd resultaat leiden.

Er zal daarom zowel een voorbeeld worden laten zien waarbij dit niet van belang is, en wanneer je wel een extra vergelijking moet toevoegen voor het juiste resultaat.

Voorbeeld: SOMPRODUCT

Nu gaan we alle kennis die je tot nu toe hebt geleerd toepassen in een praktijkvoorbeeld.

In dit voorbeeld maken we gebruik van een eenvoudige inventarislijst:

We willen nu eerst de totale waarde van de inventaris berekenen. Hiervoor is de functie SOMPRODUCT ideaal. Deze functie vermenigvuldigt waarden per rij met elkaar, en telt de uitkomsten vervolgens op.

Om de totale waarde te berekenen vermenigvuldig je per rij de voorraad (kolom C) met de prijs per stuk (kolom D):

=SOMPRODUCT(C2:C10;D2:D10)

Nadat alle uitkomsten bij elkaar zijn opgeteld, retourneert Excel één totaalbedrag. In dit geval €8050.

SOMPRODUCT met EN logica

Stel dat we nu alleen de totale waarde willen berekenen van producten die:

• In de categorie Woonkamer vallen
• EN waarvan de voorraad groter is dan 6

Om dit te berekenen voeg je booleaanse voorwaarden toe aan de formule:

=SOMPRODUCT((B2:B10="Woonkamer")*(C2:C10>6)*C2:C10*D2:D10)

Laten we eerst controleren of de uitkomst klopt. De volgende rijen voldoen aan beide voorwaarden:

• Rij 3 valt in de categorie Woonkamer en heeft een voorraad van 8
  • 8 x 140 = 1120
• Rij 6 valt in de categorie Woonkamer en heeft een voorraad van 8
  • 8 x 50 = 400

1120+ 400 = €1.520

Dit werkt als volgt:

• (B2:B10="Woonkamer") resulteert in WAAR (1) of ONWAAR (0)
• (C2:C10>6) resulteert in WAAR (1) of ONWAAR (0)

Je kunt dit als volgt zien:

Omdat deze voorwaarden met elkaar worden vermenigvuldigd houden alleen rijen waar beide voorwaarden waren WAAR zijn een waarde groter dan 0. Alle andere rijen worden automatisch 0 en tellen dus niet mee.

SOMPRODUCT met OF logica in dezelfde kolom

Wanneer je een OF logica wilt toepassen, waarbij de voorwaarden in dezelfde kolom zijn, kan de formule grotendeels gelijk blijven. Alleen in plaats van vermenigvuldigen ga je nu optellen.

We willen bijvoorbeeld de waarde weten van de producten uit:

• De categorie Slaapkamer
• Of Kantoor

De formule wordt in dat geval:

=SOMPRODUCT(((B2:B10="Slaapkamer")+(B2:B10="Kantoor"))*C2:C10*D2:D10)

• In rij 3 resulteert dit in 0 (ONWAAR) + 0 (ONWAAR) x 8 x 140 = 0
• In rij 4 resulteert dit in 0 (ONWAAR) + 1 (WAAR) x 5 x 200 = 1000

Een rij kan nooit tegelijk in Slaapkamer en Kantoor zijn. Daardoor zijn de uitkomsten per rij altijd:

• 0 + 0 = 0
• 1 + 0 = 1
• 0 + 1 = 1

SOMPRODUCT met OF logica in verschillende kolommen

Wanneer je OF logica wilt toepassen over verschillende kolommen moet je wel uitkijken, bijvoorbeeld:

• Categorie = kantoor
• of voorraad > 5

Met de voorwaarde (B2:B10="Kantoor")+(C2:C10>5) kan een rij nu wel aan beide voorwaarden tegelijk voldoen. In ons voorbeeld is dat het geval in rij 9, en dan krijg je dus 1 + 1 = 2. En dat betekent in onze formule 2 x voorraad x prijs, waardoor de uitkomst van SOMPRODUCT niet langer klopt.

In dit soort gevallen moet je de OF logica eerst terugbrengen naar 0 of 1: ((B2:B10="Kantoor")+(C2:C10>5)>0)

De volledige formule wordt daardoor: =SOMPRODUCT(((B2:B10="Kantoor")+(C2:C10>5)>0)*C2:C10*D2:D10)

Nu geldt per rij weer:

• ONWAAR resulteert in 0
• WAAR resulteert in 1

Op deze manier kun je dus OF logica toepassen in verschillende kolommen.

In de basis draait het steeds om hetzelfde principe: WAAR en ONWAAR, die Excel intern behandelt als 1 en 0. Als je dit eenmaal doorhebt, kun je voorwaarden combineren en berekeningen maken op basis van meerdere criteria.

Mijn advies is om deze technieken eerst uit te proberen met kleine, overzichtelijke voorbeelden, zoals ik in dit artikel ook heb proberen te doen. Zodra je begrijpt wat er per rij gebeurt, kun je dit soort formules ook steeds vaker zelf gaan toepassen.