Voorwaardelijke functies in spreadsheets

Als je een programmeertaal leert, kom je al snel bij conditionals uit. Dat zijn de klassieke IF-ELSEIF-ELSE-instructies, waarmee programma's verschillende bewerkingen kunnen uitvoeren op basis van logische voorwaarden. In Excel is dat misschien niet het eerste waar je aan denkt. Toch bestaan ze daar ook, en ze zijn behoorlijk belangrijk wanneer je met Excel-rapporten van enige complexiteit werkt. In deze tutorial behandelen we hoe IF-ELSE-instructies in Excel zijn opgebouwd, en ook een paar geavanceerdere functies zoals COUNTIF() die in bepaalde situaties erg handig zijn.

Basale IF-instructies

Laten we beginnen met een overzicht van hoe je een eenvoudige IF-instructie in Excel schrijft. De syntaxis is als volgt:

IF(condition, value_if_true, value_if_false)

Waarbij:

• condition: waarde of een logische bewerking die evalueert naar TRUE of FALSE
• value_if_true: De waarde die wordt geretourneerd als de voorwaarde TRUE is
• value_if_false: De waarde die wordt geretourneerd als de voorwaarde FALSE is

Goed, laten we een simpel voorbeeld bekijken van een basale voorwaardelijke instructie met een speelgoedtabel:

Eenvoudig toch? Voor wie R kent: je hebt vast gemerkt dat deze syntaxis in wezen hetzelfde is als de ifelse()-functie. Er zijn echter een paar dingen die iets anders zijn. Bijvoorbeeld de logische operatoren. In dit eenvoudige voorbeeld heb ik de logische operator ">>" gebruikt, een standaardmanier om groter dan aan te geven. Andere zijn minder standaard. Dat geldt bijvoorbeeld voor de niet gelijk aan-operator, die wordt geschreven als "<>"

Een volledig overzicht van alle logische operatoren die in Excel worden gebruikt, zie je hieronder:

OR(), AND() en NOT()

Vergelijkingsoperatoren zijn niet de enige onderdelen van voorwaardelijke Excel-instructies die kunnen afwijken van wat je in Python, R of Matlab gebruikt. Ook de manier waarop je booleaanse bewerkingen zoals OR, AND en NOT definieert, is anders. In Excel zou je een OR-expressie bijvoorbeeld zo schrijven:

IF(OR(value_1 = k, value_2 = y), value_if_true, value_if_false)

Om alle booleaanse operatoren in Excel in actie te zien, bekijk je het onderstaande voorbeeld:

Geneste IF-instructies

Net zoals je IF-ELSEIF-ELSE-instructies kunt nesten in Python, R of Matlab, kan dat ook in Excel. Je doet dit simpelweg door een extra IF-instructie binnen een eerdere IF-instructie te plaatsen wanneer die TRUE of FALSE oplevert. Zo kun je meer voorwaarden testen en meer resultaten retourneren op basis van die voorwaarden. Excel kan tot 64 IF-instructies nesten. Ik raad echter sterk af om er te veel te nesten: je moet scherp op de logica letten en debuggen of later aanpassen kan erg omslachtig worden.

Hier is een eenvoudig voorbeeld van geneste IF's in Excel zelf:

COUNTIF() en COUNTIFS()

Naast traditionele IF-instructies heeft Excel andere functies die bewerkingen kunnen uitvoeren op basis van een set voorwaarden. In deze sectie focus ik op voorwaardelijke tel-functies, specifieker: de functies COUNTIF() en COUNTIFS().

COUNTIF() laat je cellen tellen die aan één criterium voldoen. De syntaxis is als volgt:

COUNTIF(cell_range_to_count, criteria)

Waarbij:

• cell_range_to_count: Het specifieke celbereik dat je wilt tellen
• criteria: Het criterium dat bepaalt welke cellen geteld worden. Deze kunnen zeer divers zijn, bijvoorbeeld:
  • COUNTIF(A1:A10, 10) - Dit telt hoeveel cellen in het bereik van A1 tot en met A10 gelijk zijn aan 10
  • COUNTIF(A1:A10, B5) - Dit telt hoeveel cellen in het bereik van A1 tot en met A10 gelijk zijn aan de waarde in cel B5
  • COUNTIF(A1:A10, "Gandalf") - Dit telt hoeveel cellen in het bereik van A1 tot en met A10 gelijk zijn aan "Gandalf"
  • COUNTIF(A1:A10, ">20") - Dit telt hoeveel cellen in het bereik van A1 tot en met A10 groter zijn dan 20
  • COUNTIF(A1:A10, "<>Gandalf") - Dit telt hoeveel cellen in het bereik van A1 tot en met A10 niet gelijk zijn aan "Gandalf"

COUNTIFS verschilt, zoals je wellicht al vermoedde, van COUNTIF doordat je ermee cellen kunt tellen die aan meerdere criteria voldoen. De syntaxis is als volgt:

COUNTIFS(cell_range_to_count_1, criteria_1, cell_range_to_count_2, criteria_2,...cell_range_to_count_n, criteria_n)

Belangrijk: voor elk extra bereik dat je in COUNTIFS opgeeft, moet het aantal rijen en kolommen gelijk zijn aan dat van het oorspronkelijke bereik (cell_range_to_count_1), anders krijg je een foutmelding.

Deze twee functies zijn fantastisch voor bedrijfsrapporten. Een van de beste voorbeelden vind ik hoe eenvoudig ze het maken om het aantal afspraken te tellen dat onze salesmedewerkers elke maand maken en waar ze zich bevinden in onze salesfunnel.

In de onderstaande video's laat ik zien hoe deze twee functies in de praktijk werken. Eerst tellen we alleen het totaal aantal afspraken van een set fictieve salesmedewerkers:

Makkelijk toch? Je hebt zojuist geteld hoeveel afspraken elk van onze fictieve salesmedewerkers in totaal had. In theorie had je dit ook met basale IF-instructies kunnen doen, maar dat zou aanzienlijk meer werk zijn geweest voor exact hetzelfde resultaat.

Stel nu dat je ook per maand de afspraken van deze medewerkers wilt tellen. In dat geval is COUNTIFS() het antwoord; je ziet het in actie in de onderstaande video:

Let op: Je hebt misschien gezien dat ik in de laatste video het bereik dat ik wilde tellen met $-tekens heb vastgezet. Dit doe je om te voorkomen dat het verandert wanneer ik de formule naar beneden sleep.

Voilà, we hebben de afspraken van onze dummy-salesmedewerkers per maand geteld zonder moeite. Het is het vermelden waard dat je dit op een meer "efficiënte" manier kunt doen door met $-tekens te spelen om bepaalde cellen en bereiken vast te zetten wanneer je formules sleept (d.w.z. ik had de formule ook in F2 kunnen schrijven en die vervolgens naar de rest kunnen slepen). Ik wilde de COUNTIFS-formules voor januari en februari echter explicieter uitschrijven voor meer duidelijkheid. Ben je nieuwsgierig, probeer dan de COUNTIFS()-formule in F2 zo te herschrijven dat je die over de overige cellen kunt slepen met dezelfde resultaten. Dit is een nuttige oefening, want het is erg handig om bereiken en cellen vast te zetten wanneer je Excel-bedrijfsrapporten groot worden. Hier is de datatabel die in deze tutorial is gebruikt.

SUMIF() en SUMIFS()

We hebben gezien hoe je cellen kunt tellen die aan één of meerdere criteria voldoen met COUNTIF() en COUNTIFS(). Maar wat als je de cellen wilt optellen in plaats van alleen tellen? Daar zijn SUMIF() en SUMIFS() voor. Deze functies zijn vooral handig wanneer je bijvoorbeeld de omzet wilt optellen die een bepaalde salesmedewerker in totaal of in een bepaalde maand heeft gegenereerd. De syntaxis van beide functies is als volgt:

SUMIF(criteria_range, criteria, (optional) sum_range)

SUMIFS(sum_range, criteria_range_1, criteria_1, criterion_range_2, criteria_2...criteria_range_n, criteria_n)

Waarbij:

• criteria_range: Het celbereik waarin het criterium wordt getoetst.
• criteria: Het voorwaardelijke criterium dat bepaalt welke cellen worden opgeteld.
• sum_range: De cellen die worden opgeteld. Als dit in COUNTIF() niet is opgegeven, worden de cellen in criteria_range opgeteld.

Laten we nu zonder verdere omhaal een voorbeeld bekijken dat zowel SUMIF() als SUMIFS() gebruikt om de totale omzet en de omzet per maand te berekenen die door onze nep-salesmedewerkers is gegenereerd:

Opvallend is dat in het geval van SUMIFS() sum_range een verplicht argument is en het eerste argument vormt, terwijl het in SUMIF() optioneel is en het laatste argument. Dit kan verwarrend zijn wanneer je beide formules gebruikt, zoals je zag toen ik de SUMIFS-functie voor februari typte. Wees daarom voorzichtig wanneer je met beide formules werkt. Controleer altijd of je de juiste celbereiken optelt.

IFERROR()

Wie een programmeer- of scripttaal kent, weet dat er zoiets bestaat als try/catch-blokken voor foutafhandeling. In Excel hebben we de functie IFERROR() om precies dat te doen. Met IFERROR kun je fouten zoals #N/A, #VALUE! of #REF! "opvangen" en een betekenisvollere output geven. De syntaxis van de IRERROR-functie is als volgt:

IFERROR(value, value_if_error)

Waarbij:

• value: De waarde of formule die op een fout wordt gecontroleerd
• value_if_error: De waarde die wordt weergegeven wanneer de functie een fout tegenkomt.

Zo werkt het in de praktijk, zoals te zien in de volgende video:

Bij het werken met verkoopsrapporten is het meestal verstandig om bepaalde berekeningen in IFERROR-functies te verpakken. Soms hebben nieuwe salesmedewerkers heel weinig afspraken, gekwalificeerde opportunities of gesloten deals, wat kan leiden tot delingen door 0 bij het berekenen van zaken als het percentage afspraken dat uitmondt in gekwalificeerde opportunities voor een bepaalde verkoper. Dat levert lelijk uitziende fouten op die je rapport rommelig maken en lastiger te lezen voor besluitvormers. Als je deze berekeningen echter van IFERROR()-instructies voorziet, kun je die fouten vervangen door iets veel prettigers voor het oog, zoals een streepje, waardoor je rapport netter oogt. Bovendien gaan IFERROR()-instructies verder dan alleen nette foutmeldingen geven. Je kunt ze ook nesten om bijvoorbeeld gekoppelde VLOOKUPs uit te voeren.

Conclusie

Gefeliciteerd! Je kent nu verschillende manieren om conditionals in Excel te benutten. In deze tutorial heb ik geprobeerd die functies te behandelen die ik zelf vaak gebruik wanneer ik Excel-bedrijfsrapporten maak. Wil je nog meer leren over dit onderwerp, dan zijn er nog andere functies zoals SWITCH(), AVERAGEIF() of AVERAGEIFS() waar ik het hier niet over heb gehad, maar die je mogelijk interessant vindt.

Bovendien kun je in functies als COUNTIFS() ook wildcards gebruiken om gedeeltelijke tekenreeks-overeenkomsten te tellen. Stel dat je een Excel-bestand hebt met een lijst van menu-items van een restaurant en je wilt tellen hoeveel daarvan het woord "fish" bevatten. In dat geval kun je COUNTIF(cell_range,"*fish") schrijven en deze functie telt items zoals swordfish of dolphinfish. Ik raad je sterk aan om meer te leren over wildcards; ze kunnen erg nuttig zijn en je weet nooit wanneer ze van pas komen in je volgende Excel-project.

Tot slot kun je ook de cursus Data Analysis with Spreadsheets op DataCamp bekijken, want die heeft een sectie gewijd aan voorwaardelijke functies, wat als extra naslag kan dienen.

Bekijk ook onze if…elif…else in Python-tutorial.