FILTER in Excel: rijen ophalen die aan voorwaarden voldoen
Bijgewerkt op . Engelse naam: FILTER
FILTER haalt alle rijen uit een lijst die aan een voorwaarde voldoen, zoals =FILTER(A2:C20;B2:B20="Noord") voor alle verkopen in regio Noord. Anders dan de filterknoppen laat de functie je lijst staan en zet hij de gevonden rijen op een andere plek, die vanzelf bijwerkt. In het Engels heet de functie ook FILTER.
Syntax
=FILTER(matrix; opnemen; [indien_leeg])
- matrix
- Het bereik met de rijen die je wilt laten zien, zonder kopregel.
- opnemen
- De voorwaarde: een vergelijking met een kolom die even lang is als de matrix, bijvoorbeeld B2:B20="Noord".
- indien_leeg(optioneel)
- Wat er moet verschijnen als geen enkele rij voldoet, bijvoorbeeld "Geen resultaten". Zonder dit argument krijg je dan een foutmelding.
Hoe werkt FILTER?
Je geeft FILTER twee dingen: het bereik met de gegevens en een voorwaarde. =FILTER(A2:C20;B2:B20="Noord") laat alle rijen zien waarin kolom B Noord bevat. Typ de formule in één cel. De uitkomst loopt vanzelf over naar de cellen rechts en eronder.
De voorwaarde is een vergelijking met een kolom die precies even lang is als het gegevensbereik. Begint het ene bereik in rij 2 en eindigt het in rij 20, dan moet de voorwaarde dat ook doen.
De kopregel neem je niet mee. Zet de kopjes zelf boven de formule. Zet de zoekwaarde liever in een cel, zoals B2:B20=G1, dan pas je het filter aan door alleen G1 te veranderen. Met een keuzelijst in G1 maak je zo een handig zoekscherm.
Filter met meerdere voorwaarden (EN)
Moeten rijen aan twee voorwaarden tegelijk voldoen, dan vermenigvuldig je de voorwaarden met een sterretje:
=FILTER(A2:C20;(B2:B20="Noord")*(C2:C20>1000))
Dit geeft alleen de rijen uit regio Noord met een omzet boven de 1000. Zet elke voorwaarde tussen haakjes.
Waarom werkt dit? Voor Excel is WAAR gelijk aan 1 en ONWAAR gelijk aan 0. Alleen als beide voorwaarden WAAR zijn, staat er 1 keer 1 en dat is 1. In alle andere gevallen komt er 0 uit en valt de rij af. Je kunt zo veel voorwaarden achter elkaar zetten als je wilt.
Gebruik hier niet de functie EN. Die geeft één antwoord voor het hele bereik, in plaats van één antwoord per rij.
Filter met de ene of de andere voorwaarde (OF)
Mag een rij aan de ene óf de andere voorwaarde voldoen, dan tel je de voorwaarden op met een plusteken:
=FILTER(A2:C20;(B2:B20="Zuid")+(B2:B20="West"))
Nu krijg je alle rijen uit Zuid en alle rijen uit West. Elke uitkomst groter dan 0 telt als WAAR.
Je kunt * en + ook combineren. Zet de OF-voorwaarden dan samen tussen extra haakjes:
=FILTER(A2:C20;((B2:B20="Zuid")+(B2:B20="West"))*(C2:C20>1000))
Dit geeft de rijen uit Zuid of West, maar alleen met een omzet boven de 1000. Ook hier gebruik je niet de functie OF, maar het plusteken.
Het derde argument: als er niets gevonden wordt
Voldoet geen enkele rij aan de voorwaarde, dan geeft FILTER een foutmelding. Dat staat slordig in een rapport. Met het derde argument bepaal je zelf wat er dan verschijnt:
=FILTER(A2:C20;B2:B20=G1;"Geen resultaten")toont een nette melding=FILTER(A2:C20;B2:B20=G1;"")laat de cel leeg=FILTER(C2:C20;B2:B20=G1;0)geeft een 0, handig als je er nog mee rekent
Gebruik het derde argument eigenlijk altijd als de zoekwaarde uit een cel komt. Vroeg of laat typt iemand een waarde die niet in de lijst staat.
FILTER combineren met andere functies
De uitkomst van FILTER kun je direct doorgeven aan een andere functie:
=SORTEREN(FILTER(A2:C20;B2:B20="Noord");3;-1)sorteert de gevonden rijen op omzet=SOM(FILTER(C2:C20;B2:B20="Noord"))telt de omzet van Noord op=UNIEK(FILTER(A2:A20;C2:C20>1000))geeft elke verkoper met een grote order één keer
Zoek je op een stukje tekst, combineer de voorwaarde dan met VIND.SPEC: =FILTER(A2:C20;ISGETAL(VIND.SPEC("bakker";A2:A20))). Dat vindt alle rijen waarin bakker ergens in de naam staat, zonder op hoofdletters te letten.
Voorbeelden
Alle verkopen uit regio Noord
Je wilt alleen de verkopen uit Noord zien. Het derde argument zorgt voor een nette melding als er niets gevonden wordt.
=FILTER(A2:C7;B2:B7="Noord";"Geen resultaten") in cel E2
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Verkoper | Regio | Omzet | Verkoper | Regio | Omzet | ||
| 2 | Sanne de Vries | Noord | € 1.250 | Sanne de Vries | Noord | € 1.250 | ||
| 3 | Mark Jansen | Zuid | € 860 | Fatima Bakker | Noord | € 640 | ||
| 4 | Fatima Bakker | Noord | € 640 | Tom de Boer | Noord | € 1.730 | ||
| 5 | Ahmed Yilmaz | West | € 2.100 | |||||
| 6 | Lisa Visser | Zuid | € 1.480 | |||||
| 7 | Tom de Boer | Noord | € 1.730 | |||||
| 8 | ||||||||
| 9 |
Klik op cel E2 om de formule te zien of aan te passen.
Sanne, Fatima en Tom komen uit Noord en verschijnen met hun omzet in E2:G4.
Noord én een omzet boven de 1000
Nu moeten twee voorwaarden allebei kloppen. Je vermenigvuldigt ze met een sterretje.
=FILTER(A2:C7;(B2:B7="Noord")*(C2:C7>1000)) in cel E2
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Verkoper | Regio | Omzet | Verkoper | Regio | Omzet | ||
| 2 | Sanne de Vries | Noord | € 1.250 | Sanne de Vries | Noord | € 1.250 | ||
| 3 | Mark Jansen | Zuid | € 860 | Tom de Boer | Noord | € 1.730 | ||
| 4 | Fatima Bakker | Noord | € 640 | |||||
| 5 | Ahmed Yilmaz | West | € 2.100 | |||||
| 6 | Lisa Visser | Zuid | € 1.480 | |||||
| 7 | Tom de Boer | Noord | € 1.730 | |||||
| 8 | ||||||||
| 9 |
Klik op cel E2 om de formule te zien of aan te passen.
Fatima valt af met € 640. Alleen Sanne en Tom voldoen aan allebei de voorwaarden.
Zuid of West
Je wilt alle verkopen uit Zuid en West samen zien. Eén van de twee voorwaarden is genoeg, dus je telt ze op.
=FILTER(A2:C7;(B2:B7="Zuid")+(B2:B7="West")) in cel E2
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Verkoper | Regio | Omzet | Verkoper | Regio | Omzet | ||
| 2 | Sanne de Vries | Noord | € 1.250 | Mark Jansen | Zuid | € 860 | ||
| 3 | Mark Jansen | Zuid | € 860 | Ahmed Yilmaz | West | € 2.100 | ||
| 4 | Fatima Bakker | Noord | € 640 | Lisa Visser | Zuid | € 1.480 | ||
| 5 | Ahmed Yilmaz | West | € 2.100 | |||||
| 6 | Lisa Visser | Zuid | € 1.480 | |||||
| 7 | Tom de Boer | Noord | € 1.730 | |||||
| 8 | ||||||||
| 9 |
Klik op cel E2 om de formule te zien of aan te passen.
Mark, Ahmed en Lisa verschijnen, in dezelfde volgorde als in de lijst.
Veelgemaakte fouten
EN of OF in de voorwaarde
=FILTER(A2:C20;EN(B2:B20="Noord";C2:C20>1000)) werkt niet, want EN maakt er één antwoord van voor het hele bereik. Vermenigvuldig de voorwaarden met * voor EN en tel ze op met + voor OF.
Bereiken die niet even lang zijn
Het gegevensbereik en de voorwaarde moeten in dezelfde rij beginnen en eindigen. A2:C20 met B2:B25 geeft #WAARDE!.
Haakjes vergeten
Zonder haakjes rekent Excel eerst het keerteken uit en klopt de voorwaarde niet. Zet elke voorwaarde tussen haakjes: (B2:B20="Noord")*(C2:C20>1000).
#OVERLOOP! in de cel
De gevonden rijen hebben ruimte nodig. Maak de cellen rechts en onder de formule leeg.
Oefen het zelf
Lezen is één, het zelf typen is wat blijft hangen. Deze lessen gaan erover:
Veelgestelde vragen
Hoe filter ik met een formule in Excel?
Gebruik =FILTER(gegevens;voorwaarde), bijvoorbeeld =FILTER(A2:C20;B2:B20="Noord"). De gevonden rijen verschijnen vanaf de cel met de formule.
Hoe gebruik ik FILTER met meerdere voorwaarden?
Vermenigvuldig de voorwaarden met * als ze allebei moeten kloppen: (B2:B20="Noord")*(C2:C20>1000). Tel ze op met + als één van de twee genoeg is.
Wat doet het derde argument van FILTER?
Daarin zet je wat er moet verschijnen als geen enkele rij voldoet, zoals "Geen resultaten". Zonder dat argument krijg je een foutmelding.
Kan ik met FILTER maar een paar kolommen tonen?
Ja, geef als eerste argument alleen de kolommen die je wilt zien, zoals =FILTER(A2:A20;B2:B20="Noord") voor alleen de namen. De voorwaarde mag over een andere kolom gaan.
Wat is het verschil tussen FILTER en de filterknoppen?
De filterknoppen verbergen rijen in je lijst zelf. FILTER laat de lijst staan, zet de gevonden rijen op een andere plek en werkt vanzelf bij.
In welke Excel werkt de functie FILTER?
In Excel 365, Excel 2021 en nieuwer en in Excel voor het web. In oudere versies gebruik je de filterknoppen op het tabblad Gegevens.