MENU
 
Home
Nieuwsbrief
GC in de media
SiteMap
 
 
Tips en Tricks
Excel
Word
Photoshop
PowerPoint
Surf Tips
Gmail
Android
iPad
Excel ClipTips
 
cursus Office 2016
Access
Excel
Outlook
PowerPoint
Word
 
cursus Office 2013
Excel
PowerPoint
Word
Nieuw in Excel 2013
Nieuw in Word 2013
Nieuw in PowerPoint 2013
 
cursus Office 2010
Access
Nieuw in Excel 2010
Excel automatiseren
Excel
OneNote
Outlook
PowerPoint
Word
 
cursus Office 2007
Access
Excel
Outlook
PowerPoint
Publisher
Visio
Word
 
cursus Office
2000-2002-2003-XP
Access
Excel
PowerPoint
Outlook
Word
 
cursus Photoshop
CC -3D
Animatie
CS6
CS5
CS4
CS2 - CS3
Lightroom 3
Elements 6
Mask Pro
Nik Collection
 
cursus Dreamweaver
CS3
 
cursus Illustrator
CS4
 
cursus Flash
CS4
 
cursus Fireworks
CS4
 
cursus Paint Shop Pro
X en X2
 
cursus Premiere
Elements 7-8
 
cursus Joomla
Joomla 1.5
 
Sociale Netwerk sites
Facebook
LinkedIn
Twitter
 
iPad
Apps
 
Diversen
Celtx
CSS
DropBox
Firefox 3.6
GIMP
Internet Explorer 9
LIME
Linux
OpenSUZE
PREZI
ProShow Producer
YouTube
 
cursus Google
Agenda
Analytics
Gmail
Zoeken
Picasa 3
SketchUp
Chrome
Street View
 
cursus Windows
Live Movie Maker
Windows 10
Windows 8
Windows 7
XP
Vista
 
cursus Office '97
Word
Excel
 
 

 

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
 
 
 
 
 
 
 
 
 
 
 

All courses now available in English:
www.swotster.com

copyright © 2012 - Swotster Ltd - Hong Kong - China