Iedereen maakt wel gebruik van filteren in Excel. Maar wist je dat dit op veel manieren kan?
Bij het filteren laat je maar een deel van de rijen zien: je maakt een selectie. De meest gebruikte manier om te filteren is met de filterknoppen, maar die zijn lang niet altijd het handigste.
Verschil sorteren en filteren
- Voor de duidelijkheid hier nog even het verschil tussen sorteren en filteren.
Bij het sorteren wijzig je de volgorde. Je begint en je eindigt dus met hetzelfde aantal rijen als vóór het sorteren! De sortering is iets definitiefs: je kunt het alleen ongedaan maken direct ná het sorteren! - Bij het filteren maak je een selectie: je eindigt dus met minder rijen dan vóór het filteren! Het filteren is iets tijdelijks: je kunt het filter altijd weer uitschakelen om alles weer te zien.
De verwarring bij sommigen komt misschien wel omdat je voor beide acties de filterknoppen kunt gebruiken.
Filterknoppen
Hierna wordt steeds onderstaande voorbeelddata gebruikt (A1:F37). Hiervan is een Excel-tabel gemaakt die de naam MijnData heeft gekregen.
In een eerder blog heb ik al uitgelegd waarom het slimmer is om van je data een Excel-tabel te maken. Dan krijg je de filterknoppen cadeau! Op zich kun je daar prima mee filteren.
- Je klikt op de filterknop ①
- Je kiest de items die je wilt zien met de selectievakjes of je gebruikt de getal-, tekst- of datumfilteropties ②.
- Je kiest OK.
Het resultaat is dat je in de tabel de rijen die niet aan het filtercriterium voldoen worden verborgen ④. Je ziet aan het filterknopje op welke kolom is gefilterd ③.
Als je zou sorteren op bijvoorbeeld de kolom datum, dan zie je dat aan de filterknop omdat er een pijltje bij staat.
Is dat een probleem?
Soms niet, maar je moet je realiseren dat je werkt met de originele data!
Na het filteren moet je het filter weer uitschakelen om alle gegevens weer te zien. En als je geen maatregelen hebt genomen (zoals hier een indexkolom) dan krijg je na het sorteren de gegevens nooit meer in de oorspronkelijke volgorde. En als je meer gefilterde lijsten wilt (bijvoorbeeld alles van een bepaalde regio èn een andere lijst met alleen een bepaalde categorie) dan kan dat niet gelijktijdig.
Kan dat anders?
Jazeker! Je kunt tegenwoordig de functie FILTER() gebruiken. Je gebruikt dan wel de originele data, maar je werkt er niet in. Je gefilterde resultaat staat ergens anders naar keuze (bijvoorbeeld op een ander werkblad, of naast de originele data). Bij een wijziging in de originele data, zal dit automatisch ook in de gefilterde gegevens worden doorgevoerd.
Hoe werkt dat: de basis!
In dit voorbeeld wil ik uit de Exceltabel MijnData de gegevens van de categorie Brood eruit filteren.
- Selecteer de cel waar je de gefilterde gegevens wilt hebben.
- Volg deze stappen:
1. Typ: =FILTER(
2. Nu moet je opgeven waar je data staan: je selecteert dus de cellen A2:F37.
Omdat het een Excel-tabel is, zal dit cellenbereik automatisch wijzigen in de naam van de tabel: MijnData.
3. Typ een puntkomma om het volgende argument op te geven:
Selecteer de cellen E2:E37, de kolom van de categorie.
Excel zal de naam van deze kolom invullen: MijnData[Categorie]
De inhoud van die kolom moet zijn Brood, dus je typt =”Brood”
(tussen “ “ omdat het een tekst is).
Typ haakje sluiten en Enter. Dit zal het resultaat zijn.
Bij het overnemen van de gefilterde gegevens zijn de celeigenschappen van de oorspronkelijke data niet overgenomen. Dat geldt ook voor de omschrijvingen van de kolommen.
Je moet dus in dit voorbeeld zelf de kolom met de datums en eventueel de bedragen de gewenste opmaak geven. En je moet de kolomnamen erboven zetten (kopiëren en plakken). Dan wordt het zo.
Achtergrondinformatie
De functie FILTER resulteert in een zogenaamd ‘overloopgebied’. Dat betekent dat de daadwerkelijke celinhoud maar in één cel staat, maar dat die overloopt in andere cellen. Je herkent dat overloopgebied aan de blauwe rand als je een cel erin hebt geselecteerd.
De enige cel waar de formule staat is de cel linksboven in dit gebied. Dat is ook de cel die je moet selecteren als je de formule wilt aanpassen.
De andere cellen in dat overloopgebied moeten wel leeg zijn, anders krijg je een foutmelding #OVERLOPEN! (Engels: #SPILL!).
Flexibiliteit inbouwen
Als je nu een nieuwe filtering wilt maken (bijvoorbeeld categorie Zuivel), dan moet je in de formule “brood” wijzigen in “zuivel”. Het is handiger om dan te werken met een keuzecel, waarin je de categorie zet. Bijvoorbeeld zoals hieronder.
Zeker als je die keuzecel dan ook nog een gegevensvalidatie geeft, wordt het nog flexibeler (zie deze eerder verschenen blog: https://www.accountancyvanmorgen.nl/2024/01/29/exceltip-flexibele-altijd-gesorteerde-keuzelijst/).
Op twee of meer categorieën filteren
Maar wat moet je nu doen als je zowel de categorie op Brood als Zuivel zou willen filteren. Met andere woorden in de kolom Categorie moet het woord Brood staan OF Zuivel om getoond te worden. Je hebt dan te maken met OF-criteria (vergelijk het met het selecteren van meer selectievakjes via de filterknop).
Je moet dan het tweede deel van het filter aanpassen. Nu wordt het iets lastiger.
Je moet namelijk de voorwaarden tussen haakjes zetten () met + ertussen.
=FILTER(MijnData;(kolomnaam=”brood”) + (kolomnaam=”zuivel”))
Als je het flexibel wilt houden met verwijzing naar cellen, kun je dat bijvoorbeeld zo doen.
=FILTER(MijnData;(MijnData[Categorie]=I1)+(MijnData[Categorie]=J1))
Filteren op een tijd- of getallenbereik
Je wilt de lijst filteren op alle waarden van februari (1-1-2024 tot en met 29-2-2024). Die kolominhoud moet dan groter zijn of gelijk aan 1-1-2024 EN gelijktijdig kleiner zijn of gelijk aan 29-2-2024. Je hebt dan te maken met EN-criteria. Dat geldt bijvoorbeeld ook als je alle bedragen tussen 50 en 150 zou willen filteren. (>=50 EN <=150).
Dan moet je geen + tussen de voorwaarden zetten, maar een *.
Voor februari wordt het dan zo:
En voor de bedragen tussen de 50 en 150 ziet de formule er zo uit (of natuurlijk weer celverwijzingen naar cellen waar die 50 en 150 staan).
=FILTER(MijnData;(MijnData[Bedrag]>=50)+(MijnData[Bedrag]>=150))
Meer opties
Bij het gebruik van de functie FILTER heb je nog meer mogelijkheden. Je kunt door deze functie te combineren met andere functies zorgen dat je alleen bepaalde kolommen eruit filtert. Of dat eindresultaat gesorteerd wordt.
Saskia Jacobsen is directeur-eigenaar van Toels-PC en helpt bedrijven en vooral hun gebruikers om slimmer met de apps op hun pc te werken. Naast de blogs op onze website, publiceert zij ook wekelijks op YouTube een korte tip (maximaal 2-3 minuten) die meestal over Excel gaat. Deze videotip gaat bijvoorbeeld ook over filteren: https://youtu.be/J6Qp1itFVyg
Dit is een van de onderwerpen die in de Expert-training Leer nieuwe geheimen van Excel aan de orde komt.
