- Excel-filterfunctie - Syntaxis
- Voorbeeld 1: Gegevens filteren op basis van één criterium (regio)
- Voorbeeld 2: Gegevens filteren op basis van één criterium (meer dan of minder dan)
- Voorbeeld 3: Gegevens filteren met meerdere criteria (AND)
- Voorbeeld 4: Gegevens filteren met meerdere criteria (OR)
- Voorbeeld 5: Gegevens filteren om records boven/onder het gemiddelde te krijgen
- Voorbeeld 6: Alleen de EVEN-nummerrecords (of ONEVEN-nummerrecords) filteren
- Voorbeeld 7: Sorteer de gefilterde gegevens met formule
Bekijk video - Excel FILTER-functievoorbeelden
Office 365 brengt een aantal geweldige functies met zich mee, zoals XZOEKEN, SORTEREN en FILTER.
Als het gaat om het filteren van gegevens in Excel, waren we in de pre-Office 365-wereld meestal afhankelijk van het ingebouwde Excel-filter of maximaal het geavanceerde filter of complexe SUMPRODUCT-formules. Als je een deel van een dataset moest filteren, was dit meestal een complexe oplossing (iets dat ik hier heb behandeld).
Maar met de nieuwe FILTER-functie is het nu heel eenvoudig om snel een deel van de dataset te filteren op basis van een voorwaarde.
En in deze tutorial laat ik je zien hoe geweldig de nieuwe FILTER-functie is en wat handige dingen die je hiermee kunt doen.
Maar voordat ik inga op de voorbeelden, laten we snel leren over de syntaxis van de FILTER-functie.
Als u deze nieuwe functies in Excel wilt gebruiken, kunt u: upgraden naar Office 365 (doe mee aan het insider-programma om toegang te krijgen tot alle functies/formules)Excel-filterfunctie - Syntaxis
Hieronder staat de syntaxis van de functie FILTER:
=FILTER(matrix,inclusief,[indien_leeg])
- reeks - dit is het cellenbereik waar u de gegevens hebt en u wilt er enkele gegevens uit filteren
- erbij betrekken - dit is de voorwaarde die de functie vertelt welke records moeten worden gefilterd
- [if_leeg] - dit is een optioneel argument waarmee u kunt specificeren wat moet worden geretourneerd als er geen resultaten worden gevonden door de functie FILTER. Standaard (indien niet opgegeven) wordt de #CALC! fout
Laten we nu eens kijken naar enkele verbazingwekkende voorbeelden van filterfuncties en dingen die het kan doen, wat vrij complex was in zijn afwezigheid.
Klik hier om het voorbeeldbestand te downloaden en volg mee
Voorbeeld 1: Gegevens filteren op basis van één criterium (regio)
Stel dat u een dataset heeft zoals hieronder weergegeven en dat u alle records alleen voor de VS wilt filteren.
Hieronder vindt u de FILTER-formule die dit zal doen:
=FILTER($A$2:$C$11,$B$2:$B$11="VS")
De bovenstaande formule gebruikt de dataset als de matrix en de voorwaarde is $B$2:$B$11=”US”
Deze voorwaarde zou ervoor zorgen dat de functie FILTER elke cel in kolom B controleert (een met de regio) en alleen die records die aan dit criterium voldoen, worden gefilterd.
Ook heb ik in dit voorbeeld de originele gegevens en de gefilterde gegevens op hetzelfde blad, maar u kunt deze ook in afzonderlijke bladen of zelfs werkmappen hebben.
Filterfunctie retourneert een resultaat dat een dynamische array is (wat betekent dat in plaats van één waarde te retourneren, het een array retourneert die overloopt naar andere cellen).
Om dit te laten werken, moet u een gebied hebben waar het resultaat leeg zou zijn. In elk van de cellen in dit gebied (E2:G5 in dit voorbeeld) staat al iets erin, de functie geeft de fout #SPILL.
Omdat dit een dynamische array is, kunt u ook een deel van het resultaat niet wijzigen. U kunt het hele bereik met het resultaat of cel E2 (waar de formule is ingevoerd) verwijderen. Beide zouden de gehele resulterende array verwijderen. Maar u kunt geen afzonderlijke cel wijzigen (of verwijderen).
In de bovenstaande formule heb ik de regiowaarde hard gecodeerd, maar u kunt deze ook in een cel plaatsen en vervolgens verwijzen naar die cel met de regiowaarde.
In het onderstaande voorbeeld heb ik bijvoorbeeld de regiowaarde in cel I2 en hier wordt vervolgens naar verwezen in de formule:
=FILTER($A$2:$C$11,$B$2:$B$11=I1)
Dit maakt de formule nog nuttiger en nu kun je eenvoudig de regiowaarde in cel I2 wijzigen en het filter zou automatisch veranderen.
U kunt ook een vervolgkeuzelijst in cel I2 hebben, waar u eenvoudig de selectie kunt maken en de gefilterde gegevens onmiddellijk worden bijgewerkt.
Voorbeeld 2: Gegevens filteren op basis van één criterium (meer dan of minder dan)
U kunt ook vergelijkende operators gebruiken binnen de filterfunctie en alle records extraheren die meer of minder zijn dan een specifieke waarde.
Stel dat u de onderstaande gegevensset hebt en dat u alle records wilt filteren waarvan de verkoopwaarde hoger is dan 10000.
De onderstaande formule kan dit doen:
=FILTER($A$2:$C$11,($C$2:$C$11>10000))
Het array-argument verwijst naar de volledige dataset en de voorwaarde is in dit geval ($C$2:$C$11>10000).
De formule controleert elke record op de waarde in kolom C. Als de waarde meer dan 10000 is, wordt deze gefilterd, anders wordt deze genegeerd.
Als u alle records van minder dan 10000 wilt krijgen, kunt u de onderstaande formule gebruiken:
=FILTER($A$2:$C$11,($C$2:$C$11<10000))
U kunt ook creatiever worden met de FILTER-formule. Als u bijvoorbeeld de top drie record wilt filteren op basis van de verkoopwaarde, kunt u de onderstaande formule gebruiken:
=FILTER($A$2:$C$11,($C$2:$C$11>=GROOT(C2:C11,3)))
De bovenstaande formule gebruikt de functie LARGE om de op twee na grootste waarde in de gegevensset te krijgen. Deze waarde wordt vervolgens gebruikt in de FILTER-functiecriteria om alle records op te halen waarvan de verkoopwaarde groter is dan of gelijk is aan de op twee na grootste waarde.
Klik hier om het voorbeeldbestand te downloaden en volg mee
Voorbeeld 3: Gegevens filteren met meerdere criteria (AND)
Stel dat u de onderstaande dataset heeft en dat u alle records wilt filteren voor de VS waar de verkoopwaarde meer dan 10000 is.
Dit is een EN-voorwaarde waarbij u op twee dingen moet controleren: de regio moet naar de VS en de verkoop moet meer dan 10000 bedragen. Als aan slechts één voorwaarde wordt voldaan, mogen de resultaten niet worden gefilterd.
Hieronder vindt u de FILTER-formule die records filtert met de VS als regio en verkoop van meer dan 10000:
=FILTER($A$2:$C$11,($B$2:$B$11="VS")*($C$2:$C$11>10000))
Merk op dat het criterium (het zogenaamde include-argument) ($B$2:$B$11=”US”)*($C$2:$C$11>10000) is
Omdat ik twee voorwaarden gebruik en beide waar moeten zijn, heb ik de vermenigvuldigingsoperator gebruikt om deze twee criteria te combineren. Dit retourneert een array van nullen en enen, waarbij een 1 alleen wordt geretourneerd als aan beide voorwaarden is voldaan.
Als er geen records zijn die aan de criteria voldoen, retourneert de functie de #CALC! fout.
En in het geval dat u iets betekenis wilt retourneren (in plaats van de fout), kunt u een formule gebruiken zoals hieronder weergegeven:
=FILTER($A$2:$C$11,($B$2:$B$11="VS")*($C$2:$C$11>10000),"Niets gevonden")
Hier heb ik "Niet gevonden" gebruikt als het derde argument, dat wordt gebruikt wanneer er geen records worden gevonden die voldoen aan de criteria.
Voorbeeld 4: Gegevens filteren met meerdere criteria (OR)
U kunt ook het argument 'opnemen' in de functie FILTER wijzigen om te controleren op een OF-criterium (waarbij een van de gegeven voorwaarden waar kan zijn).
Stel dat u de onderstaande gegevensset hebt en dat u de records wilt filteren waarbij het land de VS of Canada is.
Hieronder is de formule die dit zal doen:
=FILTER($A$2:$C$11,($B$2:$B$11="VS")+($B$2:$B$11="Canada"))
Merk op dat ik in de bovenstaande formule eenvoudig de twee voorwaarden heb toegevoegd met behulp van de opteloperator. Aangezien elk van deze voorwaarden een array van TRUEs en FALSEs retourneert, kan ik toevoegen om een gecombineerde array te krijgen waarbij het TRUE is als aan een van de voorwaarden wordt voldaan.
Een ander voorbeeld kan zijn wanneer u alle records wilt filteren waarvan het land de VS is of de verkoopwaarde meer dan 10000 is.
De onderstaande formule doet dit:
=FILTER($A$2:$C$11,($B$2:$B$11="VS")+(C2:C11>10000))
Opmerking: wanneer u EN-criteria gebruikt in een FILTER-functie, gebruikt u de vermenigvuldigingsoperator (*) en wanneer u de OF-criteria gebruikt, gebruikt u de opteloperator (+).
Voorbeeld 5: Gegevens filteren om records boven/onder het gemiddelde te krijgen
U kunt formules binnen de functie FILTER gebruiken om records te filteren en te extraheren waarvan de waarde boven of onder het gemiddelde ligt.
Stel dat u de onderstaande gegevensset hebt en dat u alle records wilt filteren waarvan de verkoopwaarde boven het gemiddelde ligt.
U kunt dat doen met behulp van de volgende formule:
=FILTER($A$2:$C$11,C2:C11>GEMIDDELDE(C2:C11))
Evenzo kunt u voor onder het gemiddelde de onderstaande formule gebruiken:
=FILTER($A$2:$C$11,C2:C11<>
Klik hier om het voorbeeldbestand te downloaden en volg mee
Voorbeeld 6: Alleen de EVEN-nummerrecords (of ONEVEN-nummerrecords) filteren
Als u snel alle records moet filteren en extraheren uit rijen met even getallen of rijen met oneven getallen, kunt u dat doen met de functie FILTER.
Om dit te doen, moet u het rijnummer in de FILTER-functie controleren en alleen rijnummers filteren die voldoen aan de rijnummercriteria.
Stel dat je de dataset hebt zoals hieronder getoond en ik wil alleen even records uit deze dataset halen.
Hieronder is de formule die dit zal doen:
=FILTER($A$2:$C$11,MOD(RIJ(A2:A11)-1,2)=0)
De bovenstaande formule gebruikt de MOD-functie om het rijnummer van elk record te controleren (dat wordt gegeven door de ROW-functie).
De formule MOD(ROW(A2:A11)-1,2)=0 geeft WAAR als het rijnummer even is en ONWAAR als het oneven is. Merk op dat ik 1 heb afgetrokken van het ROW(A2:A11)-gedeelte aangezien het eerste record zich in de tweede rij bevindt, en dit past het rijnummer aan om de tweede rij als het eerste record te beschouwen.
Op dezelfde manier kunt u alle oneven genummerde records filteren met behulp van de onderstaande formule:
=FILTER($A$2:$C$11,MOD(RIJ(A2:A11)-1,2)=1)
Voorbeeld 7: Sorteer de gefilterde gegevens met formule
Door de FILTER-functie met andere functies te gebruiken, kunnen we veel meer gedaan krijgen.
Als u bijvoorbeeld een gegevensset filtert met de functie FILTER, kunt u de functie SORTEREN gebruiken om het resultaat te krijgen dat al is gesorteerd.
Stel dat u een dataset heeft zoals hieronder weergegeven en u wilt alle records filteren waarvan de verkoopwaarde hoger is dan 10000. U kunt de functie SORTEREN gebruiken met de functie om ervoor te zorgen dat de resulterende gegevens worden gesorteerd op basis van de verkoopwaarde.
De onderstaande formule doet dit:
=SORTEREN(FILTER($A$2:$C$11,($C$2:$C$11>10000)),3,-1)
De bovenstaande functie gebruikt de functie FILTER om de gegevens op te halen waarbij de verkoopwaarde in kolom C meer dan 10000 is. Deze array die wordt geretourneerd door de functie FILTER wordt vervolgens binnen de functie SORTEREN gebruikt om deze gegevens te sorteren op basis van de verkoopwaarde.
Het tweede argument in de SORT-functie is 3, dat is om te sorteren op basis van de derde kolom. En het vierde argument is -1 om deze gegevens in aflopende volgorde te sorteren.
Klik hier om het voorbeeldbestand te downloaden
Dit zijn dus 7 voorbeelden om de FILTER-functie in Excel te gebruiken.
Ik hoop dat je deze tutorial nuttig vond!
Misschien vind je de volgende Excel-zelfstudies ook leuk:
- Cellen filteren met vetgedrukte lettertype-opmaak in Excel
- Dynamisch Excel-filterzoekvak
- Gegevens filteren in een draaitabel in Excel
