Als je bij functies nesten een functie dan zelf typt, eindig je vaak met een melding dat de berekening niet juist is: vaak gaat het om haakjes te veel of juist te weinig. Soms is het ook lastig om de volgorde te bepalen van de functies. Maar er zijn trucjes waarmee je dat kunt voorkomen. Daarover gaat dit artikel.
Voorbeelden
Eerst enkele voorbeelden van geneste functies, zodat je begrijpt waarover ik het heb.
=JAAR(VANDAAG())
=ALS(EN(A1 > 10, A1 < 20), “Binnen bereik”, “Buiten bereik”)
=ALS.FOUT(VERT.ZOEKEN(D1, A1:B10, 2, FALSE), “Niet gevonden”)
=DEEL(A2;VIND.SPEC(” “;A2);20)
=SORTEREN(UNIEK(FILTER(Tabel1;Tabel1[Afdeling]=E5;”GEEN DATA”)))
Voorbeeld 1: maak gebruik van het dialoogvenster Functieargumenten
Je wilt uitrekenen hoeveel bonus iemand krijgt. Iemand krijgt een bonus van 10% (zie B3) als de verkopen 5000 of meer zijn. Hiervoor heb je de functie =ALS() nodig (Engels: IF)
- Je begint met =als( te typen.
Excel geeft dan in de tooltip de uitleg welke argumenten je moet gebruiken ①. - Druk als je dit ziet staan op fx in de formulebalk ②.
Meestal gebruik ik dat om het dialoogvenster Functieargumenten op te roepen. Dat geeft uitgebreidere informatie over wat je waar moet invullen tussen de haakjes (…)
Het dialoogvenster Functieargumenten staat nu op het scherm ③.
In de formulebalk zie je dat =ALS() nu helemaal is aangevuld (ook met haakje sluiten) ④.
Het eerste argument is de voorwaarde waaraan voldaan moet worden. De voorwaarde is dat de verkopen 5000 of meer zijn.
Bij Logische-test moet dus staan: C6>=5000 ⑤. Je ziet dit ook weer in de formulebalk ⑥.
Daarna druk je op de Tab-toets om naar het volgende argument te gaan (of je klikt in dat vak).
Hier moet ingevuld worden wat er moet komen te staan als aan de voorwaarde is voldaan (welke waarde moet er komen als de logische-test waar zou opleveren.
Dan krijg je dus die bonus: die staat in B3 en moet vermenigvuldigd worden met de verkopen:
$B$3*C6 ⑦. Ook dit staat weer in de formulebalk ⑧.
De $-tekens moeten bij B3 omdat je de berekening wilt kopiëren.
Ga naar het laatste argument waarde-als-onwaar en vul hier in wat er moet komen als de logische test onwaar oplevert (zoals in dit geval). Daar moet dan 0 komen.
Kies OK.
De bonus is in dit geval 0, want de verkopen zijn kleiner dan 5000.
Maar stel dat er niet één voorwaarde is. Bijvoorbeeld dat je niet alleen 10% bonus krijgt als je verkopen 5000 of meer zijn, maar dat je ook een bonus krijg als je beoordelingsscore een A is. Er zijn dan 2 voorwaarden en je moet aan één van de voorwaarden voldoen om die bonus te krijgen.
In zo’n geval moet je als logische-test de OF-functie gebruiken (Engels: OR). Met OF kun je verschillende voorwaarden opgeven: als aan één van de voorwaarden voldaan is, wordt waarde-als-waar als uitkomst gegeven.
Als je dat in één keer wilt maken doe je dat als volgt:
- Selecteer D6 en typ =ALS(
- Klik op fx.
Je ziet het dialoogvenster van de ALS-functie. - Vul bij Logische-test in OF(
Klik nu in de formulebalk in het woord OF
Het dialoogvenster dat hoort bij deze functie verschijnt nu.
Je vult hier de voorwaarden in:
1e: verkopen 5000 of meer (C6>=5000)
2e: score gelijk aan A (B6=”A”)
In de formulebalk zie je dat ook.
Nu klik je in de formulebalk in de functie ALS ①: het dialoogvenster van die functie verschijnt nu. De logische-test is ingevuld met de OF-functie.
Je kunt verdergaan met de rest ②:
waarde-als-waar: $B$3*C6
waarde-als-onwaar: 0
Klik op OK.
Voorbeeld 2: bouw het stapsgewijs op
Stel je hebt een lijst/tabel als deze. Je wilt graag een overzicht hebben van alle afdelingen die er zijn in alfabetische volgorde.
De functie om een lijst te krijgen met elke afdelingsnaam is UNIEK() (Engels: UNIQUE).
De functie omdat te sorteren is SORTEREN() (Engels: SORT).
- Je begint met =uniek(
(natuurlijk kun je ook hier het dialoogvenster Functieargumenten weer gebruiken, maar dat doe ik nu niet). - Daarna geef je het gebied op waar je de unieke gegevens uit wilt halen. In dit geval B5:B20.
De rest van de argumenten kun je in dit geval overslaan. Dus Enter.
Je hebt nu een lijst met de afdelingen die in B5:B20 voorkomen.
Maar ze zijn niet in alfabetische volgorde!
Deze uitkomst wil je gesorteerd hebben.
Selecteer E6 en klik in de formulebalk achter de =
Typ hier SORTEREN(
Inh de tooltip zie je dat je nu de matrix moet opgeven. Maar dat staat er al, want dat is het resultaat van de functie UNIEK.
Je moet nu nog het haakje sluiten ) typen dat bij SORTEREN hoort.
Maar als je dat zou vergeten komt Excel met een automatisch correctie.
Het eindresultaat is een gesorteerde lijst van de afdelingsnamen!
Absolute aanrader
Wanneer je zoals zou gebruiken als voorbeeld 2 zou ik van het cellenbereik met de data (B5:B20) een Excel-tabel maken. Wordt de lijst dan uitgebreid dan zal de lijst met de afdelingen automatisch aangepast worden als er nieuwe afdelingen bij komen. En natuurlijk op alfabetisch volgorde blijven.
Extra informatie
De functies UNIEK en SORTEREN zijn functies met een dynamisch bereik als uitkomst. Ze zijn beschikbaar in de nieuwere versies van Excel (365 en vanaf versie 2021). Wil je hierover meer weten, volg dan de expert-training Geheimen van Excel.
Saskia Jacobsen is directeur-eigenaar van Toels-PC en helpt bedrijven en vooral hun gebruikers om slimmer met de apps op hun pc te werken. Zij geeft voor ons de Excel Expert-trainingen. Naast de blogs op onze website, publiceert zij ook wekelijks op YouTube een korte tip (maximaal 2-3 minuten) die meestal over Excel gaat.
Meer weten? Bekijk onze Excel-cursussen.
Geef een reactie