Excel kennis verspreiden - deel 05: Filteren in Excel

  • 21/01/2014
Voorbeeldbestanden bij dit artikel:
  • Deel 05 - Filteren

    • Inleiding

    Vanavond deel 5 alweer. Ik zou het onderwerp van filteren in Excel willen behandelen: een Autofilter, een Uitgebreide filter, en databasefuncties. Over de basiszaken zullen we sneller over gaan, die zijn wel bekend. De minder bekende zaken gaan ons uiteraard meer boeien. Naar mijn ervaring is men over het algemeen weinig bekend met geavanceerde filters en databasefuncties. Het is een uitgebreide les geworden, want het zijn geen onderwerpen die je op 5 minuten bespreekt. Zet u er eens goed voor en experimenteer ! :-) Sinds Excel 2007 zijn filters drastisch uitgebreid met meer mogelijkheden, alsook zijn ze gemakkelijker te hanteren. Voorbeelden daarvan zullen volgen.

    Zoals reeds eerder gesteld, wat bekend is sla je gerust over… maar ik verzeker dat er nieuwe dingen in de tekst staan :-)

    1. Autofilter

    Je kent ze wel, de typische tabelletjes met de dropdown keuzelijsten (de "pijltjes") in de kolomhoofdingen. Bovenaand deze pagina heb ik een heel simpel voorbeeldbestandje toegevoegd. Er zijn kolommen met tekst, datums, tijden en bedragen, formules. Van alles wat dus. Voor de liefhebbers van formules onder ons, bekijk de formule in de kolom "Regio" eens :-) De Autofilter schakel je in via Data > Autofilter. Dat is een toggle (aan/uit knop) dus nogmaals klikken desactiveert de filter opnieuw. De filterpijlen komen tevoorschijn en zijn onmiddellijk bruikbaar voor het maken van selecties binnen de bewuste kolom. Bvb. filter alle records van Land "Frankrijk".

    Zoals reeds eerder aangehaald, zorg voor een goede tabel layout: geen lege kolommen en geen lege rijen, en bij voorkeur ook geen lege cellen. Zet er liever een "NB" (niet beschikbaar) of een andere waarde, dan cellen leeg te laten. Een snellere manier is om rechtstreeks op de te filteren waarde met de rechtermuisknop te klikken en kiezen voor Filter > Filter by Selected Cell’s value: dan filter je meteen op die waarde en hoeft de Autofilter niet actief gemaakt te worden (dat doet Excel zelf uitgaande van de CurrentRegion - zie eerdere lessen).

    Deze manier van snel filteren is nieuw in latere Excel versies. Evenals filteren op kleur. Hoewel ik geen voorstander ben van kleurboeken in Excel kan kleur bij momenten al eens handig zijn (vooral dan in combinatie met Voorwaardelijke opmaak en tabellen om dit dynamisch te maken): op die manier filteren is wél een goede manier van werken.

    Snel de Autofilter tonen is ook mogelijk door Ctrl-T te drukken en een tabel in te voegen die gaat kijken rondom de geselecteerde cel. Met een Tabel krijg je automatisch de selectiepijlen.

    Het tonen van de filterpijlen maakt ook de alfabetische sortering onmiddellijk zichtbaar. Naast filteren op kleur kan je ook sorteren op kleur, inclusief kleuren die het resultaat zijn van voorwaardelijke opmaak. Mensen die blijven steken zijn in Excel 2003 of eerder, zullen dit toejuichen. Je kan ook filteren op de kleur van de inhoud van de cel of op Voorwaardelijke opmaak-icoontjes.

    Een Autofilter toont de unieke combinaties binnen de kolom. Sinds Excel 2007 de eerste 10.000 unieke combinaties. Naast filteren laat dit uiteraard ook toe om bvb. snel ongewenste data te verwijderen: foutmeldingen, personen in de kolom "Persoon" niet mogen voorkomen, … Filter die data uit en verwijder de rijen die voldoen aan de selectie.

    Sinds Excel 2007 typ je gewoon hetgeen je wil uitfilteren, zelfs bedragen kan je intypen. Typ je het woord land kan dan krijg je een kortere lijst te zien, aangezien Belgiëland en Luxemburgland niet bestaan:

    Nadat je filtert toont de Statusbalk onderaan het scherm het aantal records dat voldoet aan de selectie. Merk op, elk werkblad kan maximaal 1 Autofilter hebben! Maar wél meerdere tabellen, dus je voelt ‘m al aankomen wat je moet doen in dat geval. (Had ik al gezegd dat Tabellen één van mijn favorieten zijn? :-) ) Slicers zullen later misschien aan bod komen in deze lessenreeks. Uiteraard filter je ook simpelweg op meerdere kolommen. Gebruik dan de juiste filterpijl om de selectie / voorwaarde te maken, per kolom.

    Een filter verwijderen kan je doen in de autofilterpijl. ALLE selecties onmiddellijk verwijderen kan via het menu / lint:

            OF:        

    Selecties kunnen verder gaan dat het aankruisen van individuele keuzes: een OF en een EN conditie zijn eenvoudig in te stellen. Bijvoorbeeld, filter op records voor Frankrijk met Persoon "jij" OF "zij".

            GEVOLGD DOOR:        

    Een Autofilter is intelligent: als door een bepaalde filter op een kolom in een andere kolom bepaalde selecties niet meer mogelijk zijn, dan zullen die ook niet weergegeven worden. Je moet dan desgevallend eerst de eedere filterselectie ongedaan maken vooraleer je verder kan.

    Sedert Excel 2007 hebben we meer mogelijkheden. Kende je de onderstaande selectiemogelijkheden al voor datums? Excel stelt zelf vast dat het om datums gaat (als jouw input goed is). Je kan dan filteren op alle datums tussen 2 bepaalde dagen, of "datums later dan", enz. Excel heeft zelfs een kalendertje om de invoer te vergemakkelijken! (lees: het is al anders geweest in het verleden).

            EN TEVENS:        

    Of de "Top 10 bedragen" uit de lijst filteren, of alle records waarvan het bedrag onder het gemiddelde ligt, of alle records die samen de laagste 5% bedragen vormen? Dit zijn standaardselecties waarvoor geen programmeerkennis nodig is.

    (Intelligent) tellen en optellen en gemiddeldes … op gefilterde bereiken, doe je met de SUBTOTAAL functie:

    Allemaal vrij handig en leuk, maar dit gezegd zijnde komen we ongeveer aan bij grenzen van de Autofilter: hij kent zijn tekortkomingen. Filteren met "OF" condities over meerdere kolommen, zal standaard niet lukken. Bvb. filter de records voor Land = "België" OF de datum valt in de huidige maand. Er zijn (ten minste) 2 oplossingen:

    1. Zet een extra kolom met een ALS-formule: =ALS(OF(A2 = "België";TEKST(D2;"mmjj") = TEKST(VANDAAG();"mmjj"));1;0) Filter dan die extra kolom uit op de 1-tjes. Niet bijster handig dus.
    2. Gebruik een Geavanceerde filter / Uitgebreide filter.

    Wat een naadloze overgang naar het volgende onderwerp van vandaag :-)

    2. Uitgebreide filter

    Dit is toch minder bekend als ik het zo bekijk op forums. Wellicht omdat een kleine beetje meer opzetwerk vereist. De voordelen zijn dan wel navenant.

      Een Uitgebreide filter heeft voornamelijk 3 doelen / gebruikswijzen:
    1. Zoals een Autofilter, filteren van de rijen die voldoen aan het criterium / de criteria. De records die niet voldoen worden verborgen.
    2. Kopiëren van de geretourneerde records naar een andere plaats in het werkblad of zelfs werkboek. Kopiëren is steeds als harde waarden (formules verdwijnen).
    3. Daarbij kan je al dan niet de Unieke records uitfilteren.

      4. Inderdaad, punt 3 zegt dat een Uitgebreide filter ook UNIEKE records kan uitfilteren. Op online forums is dat een veel voorkomende vraag. Je kan uit databases met adresgegevens de unieke records filteren, al is de efficiëntie van de filteracties afhankelijk van hoe goed de data is.

        De Uitgebreide filter vind je ook in het menu Data op het lint, net naast de Autofilter. Klik je op de knop dan vraagt Excel een aantal dingen:
      1. Welke tabel wil je uitfilteren?
      2. Waar bevindt zich het filtercriterium / de filtercriteria?
      3. Wil je de geretourneerde rijen kopiëren? Zo ja, naar waar; zo neen, even goede vrienden.

      Stel de criteria op (zie hieronder voor voorbeelden), zet de cursor in een cel uit de tabel, kies de Uitgebreide filter in het Lint en vul je vakjes in. Als je dit gedaan hebt, zal Excel ook benoemde bereiken aanmaken genaamd Criteria en Extract: dat is een verwijzing naar cellen met jouw laatst gekozen filteractie.

      Hieronder een eenvoudig voorbeeldje: we filteren de records met Land = "België" EN Persoon = "ik" EN Bedrag < 500 EN Bedrag > 100. BELANGRIJK: voor condities met getallen, typ een ' (weglatingsteken) vóór de voorwaarde, om karakters zoals = < > en <> niet te laten interpreteren door Excel.

      Daarna ga je de kracht van uitgebreide filters wel meer appreciëren in dit soort voorbeelden: hier is het Land = "België" OF het Bedrag < 500. Merk op dat je 2 rijen gebruikt in de criteria. Een compleet lege cel staat dan voor "geen verdere selectie gespecifieerd".

      En van de truc die nu komt ben ik zeker dat die heel weinig bekend is... :-) Wat met ons eerdere voorbeeld van Land = "België" OF de datums van de huidige maand? Bekijk het plaatje en vooral het Excel bestand bovenaan dit artikel - hier in de plaatjes toon ik enkel de criteria.

        Je kan dus even goed formules opnemen in de voorwaarden. Let op:
      • De kolomhoofdingen van de criteria met formules, mogen NIET in de kolomhoofdingen voorkomen van de te filteren tabel ! Vandaar dat ik zette "Datums huidige maand" in plaats van "Datum".
      • De uitkomst van de formule moet WAAR of ONWAAR zijn (in het Engels TRUE of FALSE).
      • Gebruik de cel(len) op rij 2, in het algemeen de rij met de eerste record van de tabel. Daarom nam ik cel D2 in de formule in cel I29.

      Als je deze simpele regels volgt kan je heel wilde selecties maken. Groot voordeel bij een Uitgebreide filter (in tegenstelling tot Autofilters) is dat de criteria zichtbaar blijven in het werkblad. Je ziet na een jaar later direct de selecties die je destijds maakte. (Tenzij je ze verwijdert uiteraard ;-) )

        Bij wijze van afsluiting van het hoofdstuk rond de Uitgebreide filter, dit zijn de principes tot succes:
      1. De te filteren tabel is "deftig" (well behaved) - zie eerder, laat geen lege rijen, kolommen, enz.
      2. Als je 1 of meer voorwaarden gebruikt, zorg dan dat de kolomhoofding van de voorwaarde exact gelijk is aan de kolomhoofding van de filteren kolom. GEEN EXTRA SPATIES en dergelijke.
      3. MAAR: bij formules in de voorwaarde(n) mag de kolomhoofding NIET voorkomen in de te filteren tabel ! Sorry om het zo moeilijk te maken.
      4. Je kan de geretourneerde rijen filteren naar een plaats in hetzelfde werkblad, maar standaard NIET naar een plaats in een ander werkblad van dat bestand, of naar een ander bestand! ’t Is te zeggen, dat gaat wél, MAAR dan moet je de Uitgebreide filter starten vanaf het werkblad/werkboek WAARNAAR je wil kopiëren. Sorry om het zo moeilijk te maken.
      5. Qua voorwaarden die je kan opnemen in een apart bereik:
        • Een EN voorwaarde: gebruik DEZELFDE RIJ in het bereik van de voorwaarden
        • Een OF voorwaarde: gebruik een ANDERE RIJ in het bereik van de voorwaarden
        • Herhaal de selectiekeuzes of laat ze expliciet leeg, afhankelijk van de selectie die je wil maken.

      Maar ik blijf erbij: play by the rules en de Uitgebreide filter is zéér krachtig ! Eens te meer als je hem gaat aansturen vanuit VBA-code (dit geldt ook voor een Autofilter uiteraard)

      Tellen van rijen / bedragen / ... bewerkingen doen, kan alweer met de krachtige SUBTOTAAL functie. MAAR… er is meer… en hier komt alweer een geweldige overgang naar het volgende onderdeel: waarom filteren en gaan rekenen met de SUBTOTAAL functie, als er ook functies zijn die bewerkingen kunnen doen ZONDER te filteren? EN, belangrijk, toch héél krachtig zijn en (grote) databases aankunnen. Hier is het laatste onderdeel.

      3. Databasefuncties

        Een aantal voorbeeldvragen die je met 1 formule oplost:
      • Wat is het gemiddelde bedrag van de records voor Land "Frankrijk" en Persoon "zij" waarvan het tijdstip tussen 6u en 10u ’s morgens is?
      • Wat is de recentste dag voor Persoon = "wij"?
      • In hoeveel records van het huidige jaar (2014 met een formule) was het bedrag groter dan 300 €?
      • Wat is de som van Bedrag voor Land = "Duitsland" en Persoon = "zij" of Persoon = "jullie"?
      • Wat is de verhouding van het gemiddelde bedrag in Benelux landen tov. het gemiddelde Bedrag in Niet-Benelux landen?
      • …

      Dit vereist geen hogere wiskunde of uitgebreide filteracties en SUBTOTAAL, of SOMPRODUCT formules, of SOMMEN.ALS, matrixformules, moeilijke draaitabellen, … Wel: DATABASEFUNCTIES. Zij beginnen met de D van database (in een Engelse Excel installatie) en DB (in een Nederlandse Excel installatie).

      De uitkomsten van de voorbeelden van de oefeningen hierboven kan je in 1 afbeelding vinden:

      Met een Nederlandse Excel versie moet je de bijlage maar openen, dan zie je de Nederlandse variant van de D(atabase) formules: DBSOM, DBGEMIDDELDE, enz.

      Noot: Tellen en bewerkingen doen met gekleurde cellen is NIET mogelijk met zulke functies (en zal met andere standaard ingebouwde functies ook niet lukken). Daarvoor moet je gaan programmeren.

      Voilà: alweer veel tekst, veel materiaal, maar vaak toegepaste handelingen in Excel kan je maar beter onder de knie hebben :-D




    Homepage

    Rubriek onderdelen

    Over Wim

    Wim Gielis is Business Intelligence consultant en Excel expert

    Andere links