Categorie: Excel

De ALS-functie is waarschijnlijk de eerste formule waarmee je Excel iets laat beslissen in plaats van alleen rekenen. Is de omzet hoger dan het doel? Dan “gehaald”. Zo niet, dan “niet gehaald”. In dit artikel gaan we van de simpelste variant naar formules met meerdere voorwaarden — en naar de functie die die ellendige stapel haakjes overbodig maakt.

De basis: ALS in drie onderdelen

=ALS(voorwaarde; waarde_als_waar; waarde_als_onwaar)

Een voorbeeld. In kolom B staat de omzet, het doel is 10.000 euro:

=ALS(B2>=10000; "Gehaald"; "Niet gehaald")

Tekst tussen dubbele aanhalingstekens, getallen zonder. In het Engels heet de functie IF en gebruik je komma’s in plaats van puntkomma’s.

Meerdere voorwaarden: EN en OF

Vaak moet er aan méér dan één eis worden voldaan. Daarvoor zet je EN of OF binnen je ALS-functie.

• EN — alle voorwaarden moeten kloppen: =ALS(EN(B2>=10000; C2="Ja"); "Bonus"; "Geen bonus")
• OF — één voorwaarde is genoeg: =ALS(OF(B2>=10000; C2="Ja"); "Bonus"; "Geen bonus")

Je mag ze ook combineren, maar houd het leesbaar. Een formule die je over een half jaar zelf niet meer begrijpt, is een formule die je collega straks niet durft aan te passen.

Geneste ALS: van een cijfer naar een letter

Wil je meer dan twee uitkomsten, dan kun je ALS-functies in elkaar stoppen. Het “anders”-deel van de ene ALS wordt dan een nieuwe ALS:

=ALS(B2>=90;"A"; ALS(B2>=75;"B"; ALS(B2>=60;"C";"D")))

Belangrijk: werk van hoog naar laag (of van laag naar hoog), en niet door elkaar. Excel stopt bij de eerste voorwaarde die waar is. Zet je de grens van 60 vooraan, dan krijgt een score van 95 ook een C — want 95 is immers groter dan 60.

Veel makkelijker: ALS.VOORWAARDEN

Drie geneste ALS-functies zijn nog te doen. Zes worden een haakjesdrama. In Microsoft 365 en Excel 2019 en nieuwer gebruik je daarom ALS.VOORWAARDEN (IFS). Je geeft simpelweg paren op van voorwaarde en uitkomst:

=ALS.VOORWAARDEN(B2>=90;"A"; B2>=75;"B"; B2>=60;"C"; WAAR;"D")

Dat laatste paar met WAAR is het vangnet: als geen enkele voorwaarde klopt, wordt dit de uitkomst. Laat je hem weg, dan krijg je een foutmelding bij waarden die nergens in passen.

Rekenen met een voorwaarde: SOM.ALS en AANTAL.ALS

Een veelgemaakte denkfout is om ALS te gebruiken waar je eigenlijk wilt optellen of tellen met een voorwaarde. Daar zijn eigen functies voor:

• =SOM.ALS(A2:A100; "Amsterdam"; B2:B100) — telt de omzet op van alle rijen met Amsterdam.
• =AANTAL.ALS(A2:A100; "Amsterdam") — telt hoeveel rijen Amsterdam bevatten.
• =SOMMEN.ALS(B2:B100; A2:A100;"Amsterdam"; C2:C100;"2026") — optellen met meerdere voorwaarden. Let op: hier staat het optelbereik juist vooraan.

Veelgestelde vragen

Hoe test ik of een cel bepaalde tekst bevat?

Met =ALS(ISGETAL(VIND.SPEC("factuur";A2));"Ja";"Nee") voor een hoofdlettergevoelige zoekopdracht, of VIND.ALLES in plaats van VIND.SPEC als hoofdletters niet uitmaken.

Waarom krijg ik een waardefout?

Meestal omdat je rekent met een cel waar tekst in staat, of omdat er aanhalingstekens ontbreken rond een tekstwaarde. Controleer ook of je geen komma hebt gebruikt waar een puntkomma hoort.

Kan ik een lege cel leeg laten in plaats van nul?

Ja: gebruik twee aanhalingstekens als uitkomst, bijvoorbeeld =ALS(B2="";"";B2*1,21). De cel lijkt dan leeg, maar bevat technisch gezien een lege tekst — bij sommige vervolgberekeningen is dat een verschil om rekening mee te houden.

Verder met Excel

ALS werkt het best als de ingevoerde waarden voorspelbaar zijn. Gebruik daarom een keuzelijst om typefouten te voorkomen, en VERT.ZOEKEN om gegevens uit een andere tabel op te halen.

Wie ALS begrijpt, begrijpt de logica achter vrijwel elke formule die erna komt. In Excel 365 van beginner tot pro bouwen we die logica stap voor stap op, met oefenbestanden en een certificaat na afloop.

Je hebt twee lijsten: één met artikelnummers en prijzen, en één met bestellingen. Hoe krijg je de juiste prijs bij de juiste bestelling, zonder duizend keer te zoeken en plakken? Daar is verticaal zoeken voor. In dit artikel leggen we VERT.ZOEKEN uit, laten we zien waar het meestal misgaat, en introduceren we de opvolger X.ZOEKEN — die een stuk minder gedoe geeft.

Wat doet VERT.ZOEKEN precies?

VERT.ZOEKEN (in het Engels: VLOOKUP) zoekt een waarde in de eerste kolom van een tabel, en geeft de waarde terug die daar een aantal kolommen naast staat. De formule heeft vier onderdelen:

=VERT.ZOEKEN(zoekwaarde; tabelmatrix; kolomindex; benaderen)

• zoekwaarde — wat je zoekt, bijvoorbeeld het artikelnummer in cel A2.
• tabelmatrix — het gebied waarin gezocht wordt. De zoekwaarde moet in de eerste kolom daarvan staan.
• kolomindex — het hoeveelste kolomnummer binnen dat gebied je terug wilt krijgen. Niet de kolomletter, maar een volgnummer.
• benaderen — vul hier ONWAAR in. Altijd. Hierover zo meer.

Een praktisch voorbeeld: staat je prijslijst op het tabblad Artikelen in kolommen A tot en met D, en wil je de prijs uit kolom C, dan wordt het:

=VERT.ZOEKEN(A2; Artikelen!$A$2:$D$500; 3; ONWAAR)

De vier fouten die iedereen maakt

1. Het laatste argument vergeten

Laat je het vierde argument weg, dan gaat Excel uit van WAAR: bij benadering zoeken. Excel pakt dan de dichtstbijzijnde lagere waarde — en geeft dus stilletjes een verkeerd antwoord in plaats van een foutmelding. Dat is de gevaarlijkste fout in dit rijtje, want je ziet hem niet. Vul dus altijd ONWAAR in (of 0, dat mag ook).

2. Het bereik verschuift bij doortrekken

Trek je de formule naar beneden, dan schuift A2:D500 mee naar A3:D501, en zo verder. Onderaan je lijst mis je dan rijen. Zet daarom dollartekens in de tabelmatrix. Snel doen? Selecteer het bereik in de formulebalk en druk op F4.

3. Een foutmelding terwijl de waarde er wél staat

Meestal komt dit door een verschil dat je niet ziet: een spatie aan het eind, of een getal dat als tekst is opgeslagen (te herkennen aan het groene driehoekje linksboven in de cel). Gebruik SPATIES.WISSEN om spaties weg te halen, en zet tekstgetallen om via Gegevens → Tekst naar kolommen → Voltooien.

4. Je zoekwaarde staat niet in de eerste kolom

VERT.ZOEKEN kan alleen naar rechts kijken. Staat het artikelnummer in kolom C en wil je iets uit kolom A, dan lukt het niet. Vroeger loste je dat op met een combinatie van INDEX en VERGELIJKEN. Tegenwoordig is er een betere oplossing.

X.ZOEKEN: de opvolger die alles makkelijker maakt

In Microsoft 365 en Excel 2021 zit X.ZOEKEN (XLOOKUP). Die werkt met bereiken in plaats van kolomnummers, en kan wél naar links kijken:

=X.ZOEKEN(A2; Artikelen!$A$2:$A$500; Artikelen!$C$2:$C$500; "Niet gevonden")

Je geeft achtereenvolgens op: wat je zoekt, waar je zoekt, wat je terug wilt, en optioneel wat er moet staan als er niets gevonden wordt. Drie voordelen tegenover VERT.ZOEKEN:

• Geen kolomnummers tellen — en dus geen kapotte formules als iemand een kolom invoegt.
• Standaard exact zoeken. Je kunt de fout uit punt 1 hierboven niet meer maken.
• Een nette foutmelding ingebouwd, zonder omweg via ALS.FOUT.

Heb je een oudere Excel, of deel je het bestand met collega’s die dat hebben? Dan blijft VERT.ZOEKEN de veilige keuze — X.ZOEKEN geeft in oudere versies een foutmelding.

Foutmeldingen netjes opvangen

Een werkblad vol foutmeldingen leest niet prettig, en rekent ook niet door: een som over een kolom met fouten geeft zelf ook een fout. Vang ze op:

=ALS.FOUT(VERT.ZOEKEN(A2; Artikelen!$A$2:$D$500; 3; ONWAAR); 0)

Eén waarschuwing: doe dit pas als je zeker weet dat de fouten terecht zijn. Anders verstop je met ALS.FOUT precies de signalen die je nodig hebt om je data op te schonen.

Veelgestelde vragen

Hoe heet verticaal zoeken in het Engels?

VLOOKUP. En horizontaal zoeken heet HLOOKUP, in het Nederlands HORIZ.ZOEKEN. Let op dat een Engelstalige Excel komma’s gebruikt als scheidingsteken in plaats van puntkomma’s.

Kan ik zoeken in een ander bestand?

Ja, maar het bronbestand moet open staan om de waarden te verversen, en de koppeling breekt zodra iemand het bestand verplaatst. Voor iets dat blijft draaien is het beter om de data met Power Query op te halen.

Hoe zoek ik op twee voorwaarden tegelijk?

Met X.ZOEKEN kun je de zoekbereiken aan elkaar plakken met het ampersand-teken. Een alternatief is een hulpkolom maken waarin je de twee velden samenvoegt, en daarop zoeken — minder elegant, maar wel makkelijker uit te leggen aan de volgende die je bestand opent.

Verder met Excel

Zoekfuncties komen zelden alleen: met de ALS-functie laat je Excel per regel een keuze maken, en met een draaitabel vat je de opgehaalde gegevens samen tot een overzicht.

Zoekfuncties zijn het moment waarop Excel van “digitaal ruitjespapier” verandert in een echt hulpmiddel. In het leerpakket Excel 365 van beginner tot pro oefen je met zoekfuncties, draaitabellen en formules aan de hand van echte bestanden, in je eigen tempo en met een certificaat na afloop.

Een keuzelijst in Excel — ook wel dropdown, keuzemenu of validatielijst genoemd — zorgt ervoor dat mensen in een cel alleen kunnen kiezen uit vaste opties. Geen typefouten meer, geen “Amsterdam” naast “amsterdam” naast “A’dam”, en filters en draaitabellen die eindelijk kloppen. In dit artikel maak je er in vijf minuten een, en daarna laten we zien hoe je hem netjes houdt als je lijst groeit.

Een keuzelijst maken in 4 stappen

1. Selecteer de cellen waar de keuzelijst in moet komen. Dat mag één cel zijn, maar ook een hele kolom in één keer.
2. Ga naar het tabblad Gegevens en klik op Gegevensvalidatie (in de groep “Hulpmiddelen voor gegevens”).
3. Kies bij Toestaan de optie Lijst.
4. Typ bij Bron je opties, gescheiden door een puntkomma: Ja;Nee;Misschien. Klik op OK.

Klaar. In de cel verschijnt nu een pijltje waarmee je kiest. Werk je met een Engelstalige Excel? Dan heet het Data → Data Validation → Allow: List, en scheid je de opties met een komma in plaats van een puntkomma.

Beter: haal de opties uit een bereik

Opties direct intypen werkt prima voor drie vaste waarden, maar niet voor een lijst met vijftig leveranciers die elke maand verandert. Dan wil je de opties op een apart tabblad zetten en daarnaar verwijzen.

1. Maak een tabblad Lijsten en zet je opties daar onder elkaar, bijvoorbeeld in A2 tot en met A20.
2. Open weer Gegevens → Gegevensvalidatie → Lijst.
3. Klik in het vak Bron en selecteer het bereik op het tabblad Lijsten. Er komt iets te staan als =Lijsten!$A$2:$A$20.

Wil je het tabblad met opties niet in beeld hebben? Klik met de rechtermuisknop op de tab en kies Verbergen. De keuzelijst blijft gewoon werken.

De lijst laten meegroeien: gebruik een tabel

Dit is de stap die de meeste mensen missen. Verwijs je naar een vast bereik als $A$2:$A$20, dan verschijnt optie nummer 21 níet in je keuzelijst. Los dat op door van je optielijst een echte Excel-tabel te maken:

1. Klik ergens in je optielijst en druk op Ctrl + T (tabel maken). Vink aan dat de tabel kopteksten heeft.
2. Geef de tabel een naam via Tabelontwerp → Tabelnaam, bijvoorbeeld Leveranciers.
3. Zet bij Bron in de gegevensvalidatie: =INDIRECT("Leveranciers[Naam]").

Vanaf nu voegt elke rij die je onderaan de tabel toevoegt zichzelf toe aan de keuzelijst. De omweg via INDIRECT is nodig omdat gegevensvalidatie niet rechtstreeks een tabelverwijzing accepteert.

Een foutmelding en een tip op maat

In hetzelfde venster Gegevensvalidatie zitten twee tabbladen die vaak ongebruikt blijven:

• Invoerbericht — een geel tekstballonnetje dat verschijnt zodra iemand de cel selecteert. Ideaal voor “Kies de regio waar de klant gevestigd is”.
• Foutmelding — wat er gebeurt als iemand tóch iets anders typt. Bij stijl Stop wordt de invoer geweigerd; bij Waarschuwing mag het wel, maar met een melding. Kies Stop als je data schoon moet blijven.

Afhankelijke keuzelijsten: regio bepaalt de stad

Een veelgevraagde variant: in kolom A kies je een provincie, en in kolom B verschijnen alleen de steden uit díe provincie. Dat werkt zo:

1. Zet elke groep steden in een eigen kolom, met de provincienaam als koptekst.
2. Selecteer alles inclusief de kopteksten en ga naar Formules → Maken op basis van selectie. Vink Bovenste rij aan. Excel maakt nu een benoemd bereik per provincie.
3. Geef de tweede keuzelijst als bron: =INDIRECT(A2), waarbij A2 de cel is met de gekozen provincie.

Let op: namen van benoemde bereiken mogen geen spaties bevatten. “Noord-Holland” werkt, “Noord Holland” niet — vervang spaties door een underscore en pas dat ook in je formule aan met =INDIRECT(SUBSTITUEREN(A2;" ";"_")).

Veelgestelde vragen

Hoe verwijder ik een keuzelijst?

Selecteer de cellen, ga naar Gegevens → Gegevensvalidatie en klik linksonder op Alles wissen. Wil je de validatie uit een hele kolom halen, selecteer dan eerst de hele kolom.

Waarom werkt mijn keuzelijst niet meer na kopiëren en plakken?

Gewoon plakken overschrijft de gegevensvalidatie van de doelcel. Gebruik Plakken speciaal → Waarden om alleen de inhoud te plakken en de keuzelijst te behouden.

Kan ik meerdere opties tegelijk kiezen?

Niet standaard. Een keuzelijst laat één waarde per cel toe. Meerdere keuzes in één cel vraagt om een VBA-macro — meestal is het handiger om er extra kolommen met Ja/Nee van te maken, want daar kun je wél mee filteren en rekenen.

Waarom zie ik het pijltje niet?

Het pijltje verschijnt alleen als de cel geselecteerd is. Zie je het dan nog niet, controleer dan of “Vervolgkeuzelijst in cel” aangevinkt staat in het validatievenster, en of het werkblad niet beveiligd is.

Verder met Excel

Een keuzelijst houdt je gegevens schoon, wat meteen de voorwaarde is waarop de ALS-functie en VERT.ZOEKEN betrouwbaar werken.

Gegevensvalidatie is één van die functies die je werkblad meteen professioneler maakt. Wil je Excel echt onder de knie krijgen — van opmaak en formules tot draaitabellen en dashboards — kijk dan eens naar ons leerpakket Excel 365 van beginner tot pro. Je leert in je eigen tempo, met oefenbestanden en een certificaat na afloop.

Ook handig: hoe je een draaitabel maakt in Excel.