Handleiding: Hoe kun je Functies nesten in Excel

Bijgewerkt op

In deze handleiding leer je hoe je meerdere functies in Excel kunt combineren om krachtigere en complexere formules te maken. Om het artikel voor iedereen begrijpelijk te maken, ook voor beginners, leg ik eerst kort uit wat een functie is en hoe argumenten werken.

Vervolgens wordt aan de hand van drie voorbeelden toegelicht hoe je functies kunt nesten. Het eerste voorbeeld is eenvoudig, maar daarna volgen steeds meer complexere voorbeelden. Tenslotte worden een aantal handige tips gedeeld om geneste functies op een efficiënte manier op te bouwen.

Inhoudsopgave

  1. Wat is een functie?
  2. Wat zijn argumenten in een functie?
  3. Wat betekent het om functies te ‘nesten’?
  4. Voorbeeld: De functie VANDAAG genest in DATUMVERSCHIL
  5. Voorbeeld: Geneste ALS functie
  6. Voorbeeld: Geneste ALS functie met berekeningen
  7. Tips voor het werken met geneste functies

1. Wat is een functie?

Een functie is een vooraf gedefinieerde formule waarmee je sneller en eenvoudiger berekeningen kunt uit te voeren. Iedere functie heeft zijn eigen functionaliteit. De SOM functie telt bijvoorbeeld getallen op en de GEMIDDELDE functie berekent het gemiddelde van een reeks getallen.

In bovenstaand voorbeeld wordt de SOM functie gebruikt om de getallen in het celbereik A1:C4 op te tellen. Wanneer je dit handmatig, zonder functie zou doen, krijg je de volgende formule: =A1+A2+A3+A4+B1+B2+B3+B4+C1+C2+C3+C4

Naar mate je meer cellen wilt optellen, wordt het al gauw onwerkbaar zonder gebruik te maken van een functie.

2. Wat zijn argumenten in een functie?

Een functie heeft input nodig van jou om te weten hoe de berekening uitgevoerd moet worden. Dit wordt in Excel argumenten genoemd. Afhankelijk van de functie heb je één of meerdere argumenten nodig. Al zijn er ook enkele waarbij geen enkele input nodig is om de functie uit te kunnen voeren.

In het voorbeeld van net met SOM functie moest Excel weten welke waarden opgeteld moesten worden. Het argument in dit voorbeeld was A1:C4

3. Wat betekent het om functies te ‘nesten’?

Functies ‘nesten’ betekent dat je een functie gebruikt als argument binnen een andere functie. Door hier gebruik van te maken kun je meer complexere formules maken en verschillende berekeningen combineren.

Om je te laten zien hoe dit werkt volgen nu een aantal voorbeelden.

4. Voorbeeld: De functie VANDAAG genest in DATUMVERSCHIL

We beginnen met een simpel voorbeeld, waarbij we de functie VANDAAG gaan gebruiken in combinatie met de functie DATUMVERSCHIL.

Met de functie DATUMVERSCHIL kun je het verschil berekenen tussen twee datums. Deze formule heeft drie argumenten:

  • Begindatum: De begindatum van een bepaalde periode.
  • Eindddatum: De einddatum van een bepaalde periode.
  • Eenheid: Hiermee geef je aan in welke eenheid je het resultaat wilt hebben.

Stel je wilt weten hoeveel dagen het verschil is tussen de datum 1-1-2025 en de huidige datum.

  • De begindatum staat in cel A2
  • De einddatum staat in cel B2
  • En de eenheid is in dagen “d”

In plaats van een vaste einddatum in te voeren, kunnen we VANDAAG nesten binnen DATUMVERSCHIL. Dit betekent dat Excel altijd de huidige datum gebruikt als einddatum.

De functie VANDAAG geeft resulteert namelijk automatisch de huidige datum. De schrijfwijze van deze functie is VANDAAG() en heeft dus geen input (argumenten) nodig om dit te kunnen berekenen.

In plaats van de celverwijzing B2 wordt het argument in dit voorbeeld dus VANDAAG()

5. Voorbeeld: Geneste ALS functie

Nu gaan we een stap verder en gaan we meerdere functies nesten, die zelf ook argumenten hebben.

In onderstaand voorbeeld wordt van een aantal producten de voorraad bij gehouden:

De bedoeling is dat Excel de status invoert in kolom C, afhankelijk van de voorraad in kolom B.

Voor dit soort scenario’s is de functie ALS ideaal. Je kunt met deze functie een voorwaarde testen en een een specifieke waarde retourneren op basis van het resultaat.

De schrijfwijze van deze functie is als volgt: ALS(logische-test;waarde-als-waar;waarde-als-onwaar)

Om te laten zien hoe dit werkt, zal eerst een ‘normale’ formule worden gebruikt met de functie ALS en daarna zal er een voorbeeld gebruiken hoe je deze functie kan nesten voor een meer gedetailleerd resultaat.

Als je wilt, kun je de oefening mee doen door het bovenstaand voorbeeld te downloaden.

Het eerste doel is om te bepalen of een product op voorraad is. Afhankelijk daarvan moet Excel in kolom C de status tonen.

In dit voorbeeld willen we als de voorraad ‘0’ is dat Excel de tekst ‘Niet op voorraad’ retourneert en anders ‘Op voorraad’.

We voeren daarom in cel C1 de volgende formule in:

  • Logische test: B2=0
  • Waarde als waar: “Niet op voorraad”
  • Waarde als onwaar: “Op voorraad”

Excel controleert in deze formule of de waarde in bel 2 gelijk is aan 0. Indien dat het geval is, retourneert de formule de tekst ‘Niet op voorraad’ zoals in cel C2. En in alle andere gevallen wordt de status ‘Op voorraad’.

Nu je weet hoe dit in principe werkt, breiden we het uit om een nauwkeurige voorraadstatus te tonen.

Stel we willen het volgende:

  • 0 – Niet op voorraad
  • Minder dan 5 – Bijna op
  • Minder dan 10 – Lage voorraad
  • 10 of meer – Op voorraad

Hiervoor gaan we meerdere keren de functie ALS gebruiken.

Stap 1

De eerste logische test blijft hetzelfde als in het vorige voorbeeld.

We controleren of de voorraad 0 is en tonen in dat geval ‘Niet op voorraad’. Als de voorraad 10 of meer is, tonen we (voorlopig) ‘Op voorraad’.

Je krijgt dan dus wederom de formule =ALS(B2=0;"Niet op voorraad";"Op voorraad")

Maar in plaats van dat we nu de formule al gaan invoeren, gaan we in de volgende stappen extra voorwaarden toevoegen.

De volgorde van de voorwaarden is belangrijk, want bijvoorbeeld een voorraad van 4 is zowel minder dan 5 als minder dan 10. We willen er in dat geval voor zorgen dat de status “Bijna op” wordt weergegeven.

Stap 2

Nu voegen we de voorwaarde toe dat als de voorraad kleiner is dan 5, Excel de status “Bijna op” moet tonen.

Hiervoor passen we het derde argument van de eerste ALS-functie aan. Dus het vetgedrukte deel in de bestaande formule:

=ALS(B2=0;”Niet op voorraad”;“Op voorraad”)

De logische test wordt nu B2 < 5, waarbij:

  • Waarde als waar resulteert in ‘Bijna op’
  • Waarde als onwaar resulteert in ‘Op voorraad’

De formule voor dit stukje wordt dan ALS(B2<5;"Bijna op";"Op voorraad")

Wanneer we dit in de oorspronkelijke formule invoegen op de plek van het derde argument, krijgen we:

=ALS(B2=0;"Niet op voorraad";ALS(B2<5;"Bijna op";"Op voorraad"))

Wat hier dus in feite gebeurt is het volgende:

  1. Excel controleert of de waarde in cel B2 0 is. Zo ja, dan wordt de tekst ‘Niet op voorraad’ getoond’.
  2. Indien dat niet het geval is, wordt de volgende logische test gecontroleerd: is de voorraad in cel B2 kleiner dan 5. Indien dat het geval is, dan toont Excel de tekst ‘Bijna op’.
  3. Is dat ook onwaar, dan wordt ‘Op voorraad’ getoond.

Stap 3

Nu voegen we de laatste controle toe, namelijk of de voorraad kleiner dan tien is.

Hier gebruiken we weer de functie ALS voor:

  • De logische test is B2<10
  • Waarde als waar is ‘Lage voorraad’
  • En waarde als onwaar is ‘Op voorraad’

Dit resulteert in de volgende functie: ALS(B2<10;"Lage voorraad";"Op voorraad")

We voegen dit weer in als het derde argument van de vorige formule:

=ALS(B2=0;"Niet op voorraad";ALS(B2<5;"Bijna op";ALS(B2<10;"Lage voorraad";"Op voorraad")))

Het is belangrijk om op het aantal haakjes te letten. In dit voorbeeld zie je dat er drie gesloten haakjes aan het einde staan.

Dit komt doordat elke ALS functie zijn argumentenlijst opent met een haakje ( en die ook weer moet sluiten met een haakje )

Aangezien we in dit voorbeeld drie ALS functies gebruiken, openen we drie keer een haakje ( en moeten we die aan het einde ook drie keer sluiten met een haakje ).

6. Voorbeeld: Geneste ALS functie met berekeningen

In het vorige voorbeeld hebben we de ALS functie steeds gebruikt om een bepaalde tekst te retourneren. Nu gaan we hetzelfde principe toepassen, maar dan met berekeningen.

In dit voorbeeld willen we een bonus berekenen voor werknemers op basis van het aantal gewerkte uren en de omzet die hebben gedraaid.

Klik hier om het voorbeeld te downloaden.

De bonus wordt als volgt berekend:

  • Meer dan 40 uur gewerkt en een omzet hoger dan €500 resulteert in een bonus van 10% van de omzet
  • Meer dan 40 uur gewerkt, maar met een omzet van €500 of minder resulteert in een bonus van 5% van de omzet
  • 40 uur of minder gewerkt resulteert in geen bonus.

Stap 1:

We beginnen met de logische test waarbij een werknemer geen bonus ontvangt. Dit is het geval als hij/zij minder dan 40 uur heeft gewerkt.

De basis van deze functie wordt dan =ALS(B2<=40;0;

Hetgeen hier staat komt neer op dat als B2 kleiner dan of gelijk is aan 40 Excel een ‘0’ retourneert. Als dat niet het geval is, dan gaan we verder kijken wat er moet gebeuren.. en dat gaan we in stap 2 doen.

Stap 2:

We weten nu dat in dit gedeelte van de formule een werknemer meer dan 40 uur heeft gewerkt. Wat we nu nog moeten doen om de bonus te bepalen, is het controleren van de omzet.

De vraag is nu wanneer krijgt een werknemer 10% bonus? Dat is het geval als de omzet in cel C2 meer is dan €500.

ALS(C2>500;C2*10%;

Dit plaatsen we achter het gedeelte waar we in stap 1 zijn gebleven, waardoor de formule nu als volgt is:

=ALS(B2<=40;0;ALS(C2>500;C2*10%;

Dit komt neer op:

  • Als B2 kleiner of gelijk is aan 40 dan krijgt de werknemer €0 bonus
  • Als B2 groter is dan 40 en C2 groter is dan 500 dan krijgt de werknemer 10% bonus
  • Als B2 groter is dan 40, maar C2 niet groter is dan 500, dan moeten we verder kijken wat er moet gebeuren..

Stap 3:

Het laatste wat we nu nog moeten doen, is Excel laten weten wat het moet berekenen indien:

  • Een werknemer meer dan 40 uur heeft gewerkt
  • Maar de omzet niet hoger is dan €500

We voegen dit toe als het laatste argument van de formule die we tot nu toe hebben opgebouwd.

  • Op dit punt weten we al dat het aantal gewerkte uren meer dan 40 is (anders was de functie al gestopt bij 0).
  • Ook weten we dat de omzet niet hoger is dan €500, want anders zou Excel de 10% bonus hebben berekend in stap 2.

Daarom hoeven we alleen nog de berekening C2*5% toe te voegen.

Tot slot sluiten we de zowel ALS-functie uit stap 3 en de oorspronkelijke ALS-functie uit stap 1 met een haakje ).

De uiteindelijke formule wordt dan:

=ALS(B2<=40;0;ALS(C2>500;C2*10%;C2*5%))

7. Tips voor het werken met geneste functies

Er zijn verschillende manieren om met geneste functies te werken in Excel. Zoals wel vaker met Excel is er niet per se één “beste” manier. Hieronder volgen slechts een aantal tips hoe ik het vaak doe.

Tip 1: Gebruik de formulebalk

Wanneer je een lange complexe formule schrijft kan het verwarrend zijn om te zien welk argument je aan het invullen bent.

De instructies in de formulebalk kunnen je hierbij helpen.

Wanneer je een formule invoert, zie je onder de formulebalk de structuur van de functie. Het argument waarmee je op dat moment bezig bent, wordt vetgedrukt weergegeven.

In bovenstaand voorbeeld kun je dus duidelijk zien dat je met het argument ‘waarde-als-onwaar’ bezig bent.

Hoewel je deze instructies ook direct in de cel ziet, biedt de formulebalk veel meer ruimte waardoor het overzichtelijker blijft.

Je hebt ook nog de mogelijkheid om de formulebalk groter te maken. Dit kan helpen met het beter leesbaar maken van formules. Rechts in de hoek van de formulebalk vind je een ‘pijltje naar beneden’. Zodra je hier op klikt wordt de formulebalk uitgeklapt.

Een extra handige truc is het gebruik van Alt + Enter binnen de formulebalk. Hiermee kun je een formule op meerdere regels plaatsen, zodat je geneste functies beter kunt structureren.

Aan de hand van een heel simpel voorbeeld zal ik je laten zien wat het nut hier van is. Het gaat hierbij niet om de uitleg van de formule zelf, aangezien we die al hebben behandeld, maar puur hoe je de formulebalk hierbij kan gebruiken.

In cel A1 staat een getal en in cel B1 willen we met behulp van de ALS functie het volgende retourneren:

  • A1 kleiner dan 5: klein
  • A1 groter dan 10: groot
  • Anders gemiddeld

Het eerste gedeelte van de formule typ je zoals gebruikelijk =ALS(A1<5;"klein";

Maar zodra je de volgende functie wilt invoeren, druk je eerst op Alt + Enter om de geneste functie om de volgende regel in de formulebalk in te voeren.

Dit kan helpen om de verschillende stappen in de formule beter te begrijpen.

Een klein nadeel van het gebruik van enters in de formulebalk is dat als je de balk later weer inklapt, je niet direct de hele formule ziet.

Zodra je de formulebalk weer uitklapt, zie je dat uiteraard wel weer.

Tip 2: Controleer de hoeveelheid haakjes

Bij geneste functies in Excel is het belangrijk om goed op de haakjes te letten.

Zoals je weet opent elke functie zijn argumenten met een haakje ( en deze moeten ook allemaal worden afgesloten met een haakje ).

Als je een haakje vergeet te sluiten, krijg je een foutmelding waarin Excel aangeeft dat er een typefout in de formule zit. Vaak doet Excel dan een suggestie om de formule te corrigeren, maar het is veiliger om dit zelf goed te controleren.

In een eerder voorbeeld hebben we bijvoorbeeld de formule =ALS(B2<=40;0;ALS(C2>500;C2*10%;C2*5%)) gemaakt. Als je deze formule bekijkt valt het volgende op:

  • De eerste ALS functie opent met ALS(B2<=40, en heeft een gesloten haakje nodig op het einde.
  • Daarna wordt er nog een argumentenlijst geopend met de tweede ALS functie met ALS(C2>500
  • Dit betekent dat er een extra open haakje is waardoor er op het einde van de formule twee gesloten haakjes zijn om beide functies op de juiste manier af te sluiten.

Tip 3: Test elke stap afzonderlijk

Wanneer je met lange of complexe formules werkt, kan het handig om deze stap voor stap te testen. Het kost wellicht wat meer tijd, maar, het kan fouten voorkomen en maakt het makkelijker om eventuele problemen te vinden.

Ook als je nog weinig ervaring hebt met een bepaalde functie kan deze methode je helpen te begrijpen hoe de formule werkt, voordat je het verder uitbreidt.

Stel je wilt dat Excel eerst controleert of een waarde groter is dan 10. Als dat niet het geval is, wil je een andere functie gebruiken om verder te rekenen.

In plaats van direct een lange, geneste formule te maken, kun je simpel beginnen met =ALS(A1>10;"Goed";"Fout")

Daarna test je de formule eerst door verschillende waarden in A1 in te voeren en te controleren of het resultaat klopt.

Zo ja, dan kun je de formule verder uitbreiden met extra functies. Eventueel kun je hierbij tip 1 toepassen door de geneste functies op een nieuwe regel in de formulebalk te schrijven.

Tip 4: Soms hoef je niet te nesten

Heb je deze hele handleiding gelezen.. en dan komt de laatste tip dat je soms helemaal niet hoeft te nesten.

Begrijp mij niet verkeerd.. lange formules kunnen er indrukwekkend uitzien en soms ontkom je er simpelweg niet aan om het gewenste resultaat te krijgen.

Maar dat betekent niet dat je het altijd ingewikkeld hoeft te maken.

In sommige gevallen kun je ook gebruik maken van hulpkolommen en/of meerdere cellen gebruiken om een formule op te splitsen, waardoor het gemakkelijker voor je wordt.

Stel je wilt in Excel de prijs berekenen van een product, waarbij de korting afhangt van de hoeveelheid.

  • Minder dan 5 stuks levert geen korting op.
  • 5 t/m 10 stuks resulteert in 10% korting.
  • Meer dan 10 stuks zorgt voor 20% korting.

Wanneer je dit in één formule wilt berekenen, zou dat er bijvoorbeeld zo uit kunnen zien:

=B2*(C2-(C2*ALS(B2<5;0;ALS(B2<=10;10%;20%))))

  • In cel C2 staat de prijs per stuk
  • In cel B2 staat de hoeveelheid
  • En de ALS functie controleert de voorwaarden en bepaalt de korting op basis van wat er in cel B2 staat.

Dit komt neer op C2–(C2*korting) om de prijs per stuk na korting te berekenen.

En vervolgens wordt dit vermenigvuldigd met B2 (de hoeveelheid) om de totaalprijs te krijgen.

Dit werkt goed, maar is niet echt beginnersvriendelijk.

Nu gaan we hetzelfde berekenen, maar dan met behulp van twee extra hulpkolommen.

In cel D2 wordt het kortingspercentage berekend met de formule =(B2>=5)*10%+(B2>10)*10%

In Excel resulteert een logische expressie in WAAR (1) of ONWAAR (0). Als we bovenstaande formule in stukjes opdelen krijg je:

  • (B2>=5)*10% met als resultaat 0% of 10%
  • (B2>10)*10% ook met als resultaat 0% of 10%
  • Samen krijg je dan 0%, 10% of 20% (10% + 10%)

Het is hierbij belangrijk dat cel D2 staat ingesteld als percentage.

In cel E2 wordt daarna de prijs per stuk met korting berekend met de formule =C2*(1-D2)

Tenslotte wordt in cel F2 de totaalprijs berekend door de hoeveelheid in cel B2 te vermenigvuldigen met de prijs uit cel E2.

Zo deel je het dus op in stapjes, en kun je sneller controleren of het klopt. Als ik bijvoorbeeld de aantal in cel B2 verander in 11, dan zie je direct dat het percentage in cel D2 verandert in 20%.

Deze manier kan zelfs de voorkeur hebben als je meer inzicht wil krijgen in de onderliggende berekeningen.

Het vergt wel meer ruimte in je werkblad. Als je dat niet hebt, dan zal je alsnog moeten nesten.

Maar gelukkig weet je nu hoe dat kan.

Hoi, ik ben Gerard

In 2013 ben ik deze blog over Excel begonnen. Het doel van Handleiding Excel is om jou te helpen beter te worden in Excel.

Ontvang gratis Excel tips in je inbox

Schrijf je in voor mijn nieuwsbrief en ontvang gratis tips + een formules en functies handleiding.

Schrijf je in

Excel templates

Bespaar tijd met vooraf ontworpen Excel templates

Factuur

Urenregistratie

Takenlijst

Kasboek

Bekijk templates

Plaats een reactie