Gratis tips Excel - Filterende Drop-Down Lijst


Tips Excel		go to : Index

Tip 200
Excel Tip

Filterende Drop-Down Lijst
Op vraag van Walther van Heeswijk toon ik jullie een manier om te filteren in je drop-downlijst.
Wens je het bestand te downloaden, klik dan hier.


Wanneer je honderden leden hebt, kan het wel een tijdje duren voor je de juiste persoon hebt gevonden. Walther vroeg me of het mogelijk is, deze lijst te filteren op ingetypte tekst.
Wat we hiervoor moeten doen is een nieuwe lijst maken, gebaseerd op de ingetypte tekst, en hiervan de validatielijst maken.
In deze tip toon ik jullie een manier hoe je dit doet enkel met formules, geen VBA, geen Active X besturingselementen, enkel formules.
Het eerste wat we doen is, zoeken welke leden voldoen aan de ingetypte tekst.
Dit kan met twee functies. Een eerste manier is met de functie =VIND.SPEC Deze functie zoekt op zowel kleine als hoofdletters. Zoals je ziet in onderstaande formule zoekt Excel de inhoud van cel B2 in cel D2. Cel B2 maken we absoluut omdat we deze formule straks naar onder kopiëren, en hij steeds moet gaan zoeken naar de inhoud van cel B2. Zoals je ook ziet geeft Excel me hiervoor de waarde 7. Dit betekent dat Excel deze waarde heeft gevonden als zevende karakter in het te doorzoeken woord. En dat klopt, Aagje zijn er vijf, dan een spatie is er één, samen is dit zes. Dan is er de D, dus op de zevende plaats. Iedereen volgt nog? Ik hoop het.


De tweede hiervoor mogelijke functie is =VIND.ALLES Deze functie doet net hetzelfde, alleen maakt deze onderscheidt tussen hoofdletters en kleine letters. Zoals je ziet in onderstaande afbeelding, vindt Excel geen kleine letter d in de naam Aagje Dom. Aan jou de keuze welke functie je wenst te gebruiken.


In dit voorbeeld ga ik verder met de functie =VIND.ALLES, omdat ik dit de meest geschikte vindt. Het interesseert me geen bal wanneer ik bv een Lemmens zoek, ik ook een Miller zie in mijn lijstje. Deze formule kopiëer ik naar onder. Dan zie ik of een getal op welke plaats hij de (Hoofd)letter gevonden heeft, of de foutmelding "WAARDE" wanneer hij niks heeft gevonden.


Op welke plaats hij deze heeft gevonden interesseert me geen bal, maar wel of hij er één heeft gevonden, en hoeveel hij er heeft gevonden.
Daar waar hij niks heeft gevonden en me een foutmelding geeft, wens ik een blanco cel weer te geven. Het eerste los ik op door er de functie ISGETAL aan toe te voegen. Dit geeft me de waarde WAAR of ONWAAR. Ter informatie: vergeet je haakjes van deze bijgevoegde functie niet te sluiten.


De waarden WAAR en ONWAAR kun je niet optellen, dus maak ik hiervan een één of een nul. Dit doe ik door hier de functie ALS aan toe te voegen. Dus als de inhoud waar is geef me een één, is hij fout, geef me dan een nul.


Wat we vervolgens moeten doen is de ééntjes optellen. Hiervoor gebruiken we de MAX-functie. Er zijn andere methodes, maar ik gebruik hiervoor de MAX-functie. De MAX-functie zal het bereik $C$1:C1 controleren, en hier één aan toe voegen wanneer dit waar is, 0 wanneer het niet waar is. Ik probeer dit uit te leggen: De eerste keer, in cel C2, komt hij het getel 1 tegen, 0 + 1 = 1 De tweede keer dat hij het getal 1 tegenkomt is in cel C21, dus 1 + 1 = 2 De derde keer dat hij het getal 1 tegenkomt is in cel C24, dus 2 + 1 = 3 Enzoverder.


Deze lijst gaan we gebruiken om onze nieuwe lijst, een dynamische lijst, samen te stellen. Hiervoor gebruiken we de functies RIJEN en VERT.ZOEKEN. =VERT.ZOEKEN(RIJEN($F$2:F3);$C$2:$D$323;2;0) De functie RIJEN telt het aantal cellen in een bereik. Wanneer we deze functie gebruiken samen met de functie VERT.ZOEKEN geeft ons dit het resultaat uit kolom D. Indien er niks is gevonden, dan wordt er een foutmelding weergegeven. Ter informatie: ik heb het zoekwoord D gewijzig in Do zodat ik wat minder resultaten krijg te zien.


Om de foutmelding te verbergen voegen we de functie ALS.FOUT toe.


Onze lijst is nu dynamisch. Wijzigen we de inhoudt van cel B2, dan wijzigt ons lijstje.


Wat we nu nog moeten weten is, hoeveel namen dit dynamische lijstje heeft. Hiervoor gebruiken we de functie AANTAL.ALS. Meer bepaalt =AANTAL.ALS(F2:F500;"?*")
Dus aantal als in het bereik F2:F500. En het criteria is "?*". Met andere woorden wanneer er tekst staat.
Dit gebruiken we samen met de functie VERSCHUIVING.
=VERSCHUIVING($F$2;;;AANTAL.ALS($F$2:$F$500;"?*"))
Deze formule gaan we gebruiken als een naam voor een bereik, dat we dan later gaan gebruiken in een validatielijst. Het eerste wat we doen is ons naambereik ingeven. Selecteer het tabblad formules en klik de knop "Namen beheren". Klik de knop "Nieuw".


Geef het bereik een naam, en plak de formule in het onderste vak.


Alles is klaar, nu gaan we de validatielijst maken. Selecteer een cel waar je deze wenst, en klik de knop "Gegevensvalidatie". In het venster dat opent kies je "Lijst", en selecteer je de daarstraks aangemaakte lijst. Klik de knop F3 op je toetsenbord om de bereiknaam te kunnen selecteren.


Verwijder eerst nog even het vinkje voor de tekst "Foutmelding weergeven...", onder het tabblad "Foutmelding".


We testen onze validatielijst en wat blijkt, "het werkt".


GratisCursus.be denkt aan jullie.
> Index