Functie FILTER in Excel

Met de functie FILTER kun je gegevens ophalen die voldoen aan één of meerdere voorwaarden. De functie retourneert alleen de rijen of waarden die aan die voorwaarden voldoen.

Belangrijk om te weten bij deze functie:

• Het resultaat is dynamisch: wanneer de brongegevens of de voorwaarden veranderen, wordt het resultaat automatisch bijgewerkt.
• De resultaten worden uitgespreid over meerdere cellen: deze functie retourneert (in de meeste gevallen) een reeks waarden. Deze worden automatisch verdeeld over meerdere cellen.
• De oorspronkelijke gegevens blijven ongewijzigd: je kunt het zien als dat je met de functie FILTER een ‘nieuw’ overzicht maakt, zonder dat de brongegevens worden aangepast.

Het verschil met de ingebouwde filterfunctie in Excel

Excel heeft ook een ingebouwde filter (in het lint via Gegevens > filter):

Dit werkt echter anders:

• Met de ingebouwde filter verberg of toon je rijen in de oorspronkelijke tabel.
• Met de functie FILTER (uit dit artikel) maak je een nieuw overzicht op een andere plek in je werkblad.

In Excel geldt meestal dat de ene methode niet per se beter is dan de ander. Welke je het beste kunt gebruiken, hangt vooral af van wat je wilt bereiken.

• Wil je tijdelijk data bekijken of aanpassen? Dan is de ingebouwde optie vaak handiger.
• Wil je een dynamisch overzicht of een rapport maken? Gebruik dan de FILTER functie.

Schrijfwijze formule

=FILTER(matrix;opnemen;[als_leeg])

Argumenten

• Matrix: hier voer je het bereik met gegevens in dat je wilt filteren. Dit kan één kolom zijn, maar ook meerdere kolommen tegelijk.
• Opnemen: dit is de voorwaarde waaraan de gegevens moeten voldoen. Dit is een logische test die resulteert in WAAR of ONWAAR. De uitkomst hiervan bepaalt welke partijen worden meegenomen.
• [als_leeg]: dit argument is optioneel. Hier kun je Excel laten weten welke waarde moet worden weergegeven als er geen resultaten worden gevonden. Als je dit argument leeg laat, dan wordt er een foutmelding getoond indien Excel niets kan vinden.

Voorbeeld 1: filteren op één voorwaarde (getal)

In onderstaand voorbeeld staat in kolom A een aantal fruitsoorten en in kolom B de bijbehorende voorraad.

We willen nu alleen de fruitsoorten tonen met een voorraad groter dan 25. Dit kan door een logische vergelijking te maken.

• A2:B6 is de matrix met gegevens die je wilt tonen
• De voorwaarde is B2:B6>25

Dit resulteert in de volgende formule: =FILTER(A2:B6;B2:B6>25)

Je kunt deze formule in een willekeurige cel invoeren, zolang er genoeg ruimte is om de uitkomst te verdelen.

Indien er onvoldoende ruimte is, retourneert Excel de foutmelding #OVERLOPEN!

Dynamisch maken met een celverwijzing

We kunnen er bijvoorbeeld ook voor kiezen om in cel E1 de minimale voorraad in te voeren. De formule wordt dan: =FILTER(A2:B6;B2:B6>E1)

Wanneer je nu de waarde in E1 aanpast, verandert het resultaat automatisch mee.

Geen resultaat

In de formules die tot nu toe zijn gebruikt, is het argument [als_leeg] niet ingevoerd. Wanneer de minimale voorraad in cel E1 bijv. wordt aangepast naar 60, zal Excel niets vinden:

Wil je deze foutmelding voorkomen, dan kun je een gewenste waarde invoeren, bijv. ‘Geen data beschikbaar’. Omdat dit een tekstwaarde is, dien je het tussen dubbele aanhalingstekens in te voeren: =FILTER(A2:B6;B2:B6>E1;"Geen data beschikbaar")

Voorbeeld 2: filteren op één voorwaarde (tekst)

Het is ook mogelijk om Excel te laten filteren op tekst.

We willen alleen producten uit te categorie fruit tonen. Hiervoor maken we gebruik van de logische operator is gelijk aan (=).

• A2:C7 is de matrix met gegevens die je wilt tonen
• De voorwaarde is A2:A7=”Fruit”

Dit resulteert in de formule =FILTER(A2:C7;A2:A7="Fruit")

Nu worden alleen de rijen waar in kolom A fruit staat weergegeven.

Voorbeeld 3: meerdere voorwaarden combineren

We gebruiken nu dezelfde gegevens als in voorbeeld 2, maar willen nu filteren op basis van meerdere voorwaarden. Dit kun je doen met behulp van booleaanse logica in Excel.

EN (beide voorwaarden moeten waar zijn)

We willen bijvoorbeeld filteren op:

• Fruit
• En een voorraad groter dan 25

De formule om dit te doen is: =FILTER(A2:C7;(A2:A7="Fruit")*(C2:C7>25))

In dit voorbeeld blijft nu alleen Appels en Perziken over. Het fruitsoort Bananen valt af met een voorraad van 18.

Deze formule oogt wat lastiger, maar ook hierbij levert elke voorwaarde een reeks met WAAR of ONWAAR op. Door deze voorwaarden met elkaar te vermenigvuldigen (*), blijven alleen de rijen over waarvoor beide voorwaarden waar zijn.

OF (één van de voorwaarden is voldoende)

We willen nu bijvoorbeeld filteren op:

• Granen
• Of groente

De formule wordt nu: =FILTER(A2:C7;(A2:A7="Granen")+(A2:A7="Groente"))

Ook bij deze formule levert elke voorwaarde WAAR of ONWAAR op. Door de voorwaarden bij elkaar op te tellen (+), wordt een rij meegenomen zodra minstens één van de voorwaarden waar is.

Kortom, de functie FILTER is een moderne functie in Excel waarmee je op een flexibele en dynamische manier gegevens kunt filteren.

Zoals je in dit artikel hebt gezien, worden de resultaten automatisch bijgewerkt zodra de brongegevens of voorwaarden veranderen. In de praktijk wordt deze functie vaak gebruikt bij het maken van overzichten en dashboards.