Het Excel bestand dat hoort bij deze pagina over (afhankelijke) dropdown lijsten, staat onderaan de tekst en mag gratis worden gedownload voor eigen gebruik.
Handmatige dropdown lijsten
Dropdown lijsten zijn een handig hulpmiddel wanneer je gebruikers wilt laten kiezen uit een aantal standaard mogelijkheden. Ze zijn eenvoudig te maken. In deze blog gebruik ik een tabel met 3 automerken (merken) en bijbehorende autotypes (types) om een en ander uit te leggen. Ik begin met het “handmatig” maken van een dropdown lijst.
Voorbeeld 1 – Tab ‘Handmatige Dropdown’
In cel F2 staat automerk ‘Seat’ geschreven. In cel G2 wil ik een dropdown lijst met Seat-modellen tonen.
Stap 1: Ga in cel G2 staan en kies in menu ‘Gegevens’ voor ‘Validatie…’ Op de ‘Instellingen’ tab kies je vervolgens bij ‘Toestaan:’ voor ‘Lijst’. Bij ‘Bron:’ kun je nu intypen welke Seat-modellen (= brongegevens) in de lijst getoond moeten worden*). Als je klaar bent druk je op de ‘OK’ knop.
Stap 2: Activeer met je muis de dropdown lijst in cel G2. Je ziet nu de bij stap 1 ingetypte brongegevens verschijnen.
*) In plaats van bij stap 1 de Seat-modellen afzonderlijk in te typen, kun je bij ‘Bron:’ verwijzen naar de cellen waar de modellen al klaar staan. In dat geval vul je in: =D2:D7 en de dropdown lijst vult zich automatisch met de brongegevens uit deze cellen nadat je op ‘OK’ hebt gedrukt.
Dropdown lijsten op basis van naamgeving
Staan de brongegevens die je in een dropdown lijst wil tonen op een ander tabblad, dan dien je te werken met naamgeving. Dit betekent dat je een reeks cellen eerst een naam geeft voordat je een dropdown lijst maakt. Dit werkt als volgt.
Voorbeeld 2 – Tab ‘Genaamde Dropdown’
In de cellen F2, F3 en F4 staan de merken ‘Volkswagen’, ‘Renault’ en ‘Seat’ geschreven. In de cellen G2, G3 en G4 wil ik dropdown lijsten maken met de bijbehorende types. Ik begin in dit voorbeeld met ‘Seat’.
Stap 1 en 2: Selecteer cel D2 tot met D7 en vul links boven in het naamvak een naam voor deze celreeks in. Ik geef de geselecteerde reeks de naam ‘lijst_seat’ (zonder de aanhalingstekens!).
Stap 3: Ga in cel G4 staan en kies in menu ‘Gegevens’ voor ‘Validatie…’ Op de ‘Instellingen’ tab kies je vervolgens bij ‘Toestaan:’ voor ‘Lijst’. Bij ‘Bron:’ vul je nu een ‘=’ teken en de naam van de celreeks in, in dit geval =lijst_seat
Stap 4: Activeer met je muis de dropdown lijst in cel G4. Je ziet nu de brongegevens van de cellen die ik ‘lijst_seat’ heb genoemd.
Bij ‘Volkswagen’ en ‘Renault’ doe je exact hetzelfde. Je selecteert de brongegevens (respectievelijk B2:B11 en C2:C10), geeft de celreeksen een naam (‘lijst_vw’ en ‘lijst_ren’) en gebruikt deze namen om dropdown lijsten te maken in cel G2 en G3.
Afhankelijke dropdown lijsten (eenvoudig)
In Excel is het mogelijk om de inhoud van verschillende dropdown lijsten van elkaar af te laten hangen. In dit geval wil ik met de eerste dropdown lijst een merk kiezen. De tweede dropdown lijst dient daarna alleen de types van het gekozen merk te tonen.
Voorbeeld 3 – Tab ‘Afhankelijke Dropdown 1’
In cel G2 wil ik een merk kunnen kiezen. Daarna wil ik in cel G3 een bijbehorend type kunnen kiezen. Kies ik bijvoorbeeld ‘Volkswagen’ als merk, dan wil ik daarna alleen de types ‘Polo’ tot en met ‘Touareg’ kunnen selecteren.
Stap 1: Begin met het aanmaken van een standaard dropdown lijst voor de automerken. Geef daartoe de automerken in celreeks B1:D1 de naam ‘lijst_merk’.
Stap 2: Ga in cel G2 staan en kies in menu ‘Gegevens’ voor ‘Validatie…’ Op de ‘Instellingen’ tab kies je vervolgens bij ‘Toestaan:’ voor ‘Lijst’. Bij ‘Bron:’ vul je nu een ‘=’ teken en de celreeks naam in, in dit geval =lijst_merk
Stap 3: Ga in cel G3 staan en kies in menu ‘Gegevens’ voor ‘Validatie…’ Op de ‘Instellingen’ tab kies je vervolgens bij ‘Toestaan:’ voor ‘Lijst’. Bij ‘Bron:’ moet nu een formule komen om deze lijst afhankelijk te maken van het merk. De formule luidt:
=VERSCHUIVING(A1;1;VERGELIJKEN(G2;lijst_merk;0);10;)
Stap 4: Activeer met je muis de dropdown lijst in cel G2 en kies een merk. Activeer met je muis vervolgens de dropdown lijst in cel G3. Je ziet nu de types van het merk dat je bij G2 hebt geslecteerd. In dit voorbeeld heb ik ‘Seat’ gekozen en zie ik daarna de types ‘Mii electric’ tot en met ‘Tarraco’
Het enige dat nieuw is, is het gebruik van een formule in het ‘Bron:’ vak van de dropdown lijst. Deze formule luidt:
=VERSCHUIVING(A1;1;VERGELIJKEN(G2;lijst_merk;0);10;)
De formule bestaat uit de functies VERSCHUIVING en VERGELIJKEN en is nodig omdat je Excel 5 dingen moet laten weten voor de afhankelijke dropdown lijst met types:
1) Wat is het startpunt van waaruit gezocht moet worden naar autotypes?
2) Hoeveel rijen moet je omlaag om bij het eerste autotype te komen?
3) Hoeveel kolommen moet je naar rechts om bij de juiste autotypes te komen?
4) Hoeveel autotypes moeten worden getoond?
5) Hoe breed is de lijst met autotypes?
Het antwoord op deze vragen vind je als volgt terug:
1) VERSCHUIVING (A1;…;…;…;…) = Het startpunt van waaruit gezocht moet worden naar de autotypes is cel A1. Excel start dus in cel A1.
2) VERSCHUIVING (…;1;…;…;…) = Welk automerk je ook kiest, het eerste autotype staat altijd in rij 2, dus vanuit startpunt A1 moet Excel 1 rij omlaag om in rij 2 te komen. Daarmee schuift Excel van cel A1 naar cel A2.
3) VERSCHUIVING (…;…;VERGELIJKEN(G2;lijst_merk;0);…;…) = Hoeveel kolommen Excel naar rechts moet om bij de juiste autotypes te komen, hangt af van de keuze voor het automerk in G2. Je kunt dus geen vast getal intypen zoals bij stap 2 maar hebt een extra functie nodig. Gebruik de functie VERGELIJKEN om te zien welk merk in G2 is gekozen, vergelijk dat met de celreeks ‘lijst_merk‘ (B1:D1) en zorg voor een exacte match (0). Ik heb in G2 ‘Seat’ gekozen en Excel stelt via VERGELIJKEN vast dat dit merk de 3e waarde in ‘lijst_merk’ is. Excel zal dus 3 kolommen naar rechts schuiven en gaat daarmee van cel A2 naar D2. Dit is het begin van de lijst met autotypes van automerk ‘Seat’.
4) VERSCHUIVING (…;…;…;10;…) = Renault heeft de langste typelijst met 10 waarden. Deze waarde gebruik ik in de formule. Het getal 10 wil hier dus zeggen dat Excel vanuit cel D2 in totaal 10 waarden in de dropdown lijst zal laten zien (incl. de waarde in D2 zelf!). In dit geval zijn dat de 6 autotypes van ‘Seat’ en 4 lege plekken. Had ik in G2 voor ‘Renault’ gekozen dan had ik alle 10 autotypes van dat merk gezien zonder lege plekken in de dropdown lijst.
5) VERSCHUIVING (…;…;…;…;1) = Een lijst met autotypes is altijd 1 kolom breed. Je kunt een ‘1’ invullen of deze gewoon weglaten zoals in het voorbeeld.
Afhankelijke dropdown lijsten (complex)
In voorbeeld 3 zit nog een schoonheidsfout. De dropdown lijst met types toont altijd 10 waarden, ongeacht het echte aantal types van een merk. Als een merk minder dan 10 types heeft, toont Excel lege regels in de dropdown lijst. En als een merk meer dan 10 types heeft, toont Excel toch maximaal 10 types in de dropdown lijst omdat ik dit ‘hard’ heb geprogrammeerd. Het zou mooier zijn als de lengte van de dropdown lijst zich ook automatisch aanpast aan het aantal types. Dat is mogelijk. Je moet dan de waarde ’10’ in de verschuivingsformule vervangen door een functie die de lengte bepaalt.
Voorbeeld 4 – Tab ‘Afhankelijke Dropdown 2’
Uitgaande van voorbeeld 3 is het enige wat je doet de ’10’ vervangen (zie 2) door een functie die ik voor het gemak boven in beeld (zie 1) heb weergegeven. Daarna past de lijstlengte zich automatisch aan (zie 3).
De oorspronkelijke formule bij ‘Bron:’ luidt:
=VERSCHUIVING(A1;1;VERGELIJKEN(G2;lijst_merk;0);10😉
Houdt deze formule geheel in tact. Echter, vervang ’10’ door:
AANTALARG(VERSCHUIVING(A1;1;VERGELIJKEN(G2;lijst_merk;0);20;1))
De formule om het aantal autotypes in een kolom te bepalen luidt dus:
AANTALARG(VERSCHUIVING(A1;1;VERGELIJKEN(G2;lijst_merk;0);20;1))
Deze formule bestaat uit de functies AANTALARG, VERSCHUIVING en VERGELIJKEN. Die doen het volgende:
AANTALARG = Geeft als uitkomst een getal, namelijk het aantal items in de dropdown lijst. Hier is het allemaal om begonnen want we willen niet standaard ’10’ gebruiken.
VERSCHUIVING = Excel moet net als in voorbeeld 3 weten waar de autotypes zich bevinden.
VERGELIJKEN = Excel moet net als in voorbeeld 3 weten om welk automerk het gaat want daaronder staan de autotypes.
De eerste 3 stappen van de verschuivingsfunctie werken daarom hetzelfde zoals eerder beschreven bij voorbeeld 3 hierboven:
1) Excel start in cel A1.
2) Excel gaat 1 rij omlaag en komt in cel A2 terecht.
3) Om te bepalen hoeveel kolommen Excel naar rechts moet, wordt de vergelijkingsfunctie gebruikt. Het automerk uit cel G2 wordt vergeleken met de celreeks ‘lijst_merk’. Zo blijkt ‘Seat’ het 3e merk in de celreeks te zijn dus schuift Excel op naar cel D2.
4) De zelf gekozen ’20’ geeft aan dat Excel nu vanuit cel D2 in totaal 20 cellen naar beneden kijkt en deze telt met de AANTALARG functie indien ze tekst bevatten. Vanuit cel D2 ziet Excel in de 20 cellen daaronder (incl. D2 zelf!) in totaal 6 cellen met tekst, namelijk ‘Mii electric’ (cel D2), ‘Ibiza’ (cel D3), etcetera. De uitkomst is daarom 6.
5) De lijst met autotypes is 1 kolom breed.
Het vervangen van ’10’ door de echte lijstlengte gebeurt dus feitelijk bij stap 4). De rest is ‘oude koek’ 😉
Tot slot een aantal tips
- Oefen eerst met eenvoudige dropdown lijsten die niet van elkaar afhankelijk zijn.
- Experimenteer met het gebruik van naamgeving aan cellen en celreeksen. Als je deze techniek beheerst kun je veel van je Excel berekeningen sterk vereenvoudigen en ‘leesbaar’ maken.
- Verdiep je in de functies VERSCHUIVING, VERGELIJKEN en AANTALARG.
- Begin met eenvoudige, afhankelijke dropdown lijsten (voorbeeld 3) en leer pas daarna schoonheidsfoutjes wegpoetsen (voorbeeld 4)