Hoe de Excel FILTER-functie te gebruiken?

Office 365 biedt een aantal geweldige functies, zoals XZOEKEN, SORTEREN en FILTER.

Bij het filteren van gegevens in Excel, in de pre-Office 365-wereld, vertrouwden we meestal op ingebouwde Excel-filters of hoogstens geavanceerde filters of complexe SUMPRODUCT-formules.Dit is vaak een ingewikkelde oplossing als u een deel van de dataset moet filteren.

Maar met de nieuwe FILTER-functie is het nu heel eenvoudig om snel delen van een dataset te filteren op basis van voorwaarden.

In deze tutorial laat ik je zien hoe geweldig de nieuwe FILTER-functie is, en enkele handige dingen die je ermee kunt doen.

Maar laten we, voordat ik inga op het voorbeeld, even kijken naar de syntaxis van de functie FILTER.

Als u deze nieuwe functies in Excel wilt gebruiken, kunt u:Upgrade naar Office 365(Doe mee met het interne programma om toegang te krijgen tot alle functies/formules)

Excel-filterfunctie - Syntaxis

Het volgende is 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
• omvatten - Dit is de voorwaarde die de functie vertelt welke records moeten worden gefilterd
• [if_leeg] – Dit is een optionele parameter waarmee u kunt specificeren wat moet worden geretourneerd als de functie FILTER geen resultaten vindt.Standaard (indien niet gespecificeerd) retourneert het #CALC!Fout

Laten we nu eens kijken naar enkele verbazingwekkende voorbeelden van filterfuncties en wat het kan doen dat vroeger erg ingewikkeld was zonder.

Voorbeeld 1: gegevens filteren op basis van één voorwaarde (regio)

Stel u heeft een dataset zoals hieronder, en u wilt alleen alle records in de Verenigde Staten filteren.

Hier is de FILTER-formule die dit doet:

=FILTER($A$2:$C$11,$B$2:$B$11="VS")

De bovenstaande formule neemt de dataset als een matrix en de voorwaarde is $B$2:$B$11=”US”

Deze voorwaarde zorgt ervoor dat de functie FILTER elke cel in kolom B (cellen met het bereik) controleert en alleen die records filtert die aan deze voorwaarde voldoen.

Ook plaats ik in dit voorbeeld de originele en gefilterde gegevens op hetzelfde blad, maar u kunt ze ook in afzonderlijke bladen of zelfs werkmappen plaatsen.

De filterfunctie retourneert een dynamische reeks resultaten (wat betekent dat in plaats van een waarde te retourneren, deze een reeks retourneert die overloopt in andere cellen).

Hiervoor moet u een gebied hebben waar het resultaat leeg is.Er is al iets in een cel in het bereik (E2:G5 in dit voorbeeld) en de functie geeft je de #SPILL-fout.

Omdat dit een dynamische array is, kunt u ook een deel van het resultaat niet wijzigen.U kunt het hele bereik verwijderen met het resultaat of cel E2 (waar de formule is ingevoerd).Beide zullen de volledige resultatenarray verwijderen.Maar u kunt geen enkele cel wijzigen (of verwijderen).

In de bovenstaande formule heb ik de bereikwaarde hard gecodeerd, maar je kunt deze ook in een cel plaatsen en naar die cel verwijzen met de bereikwaarde.

In het onderstaande voorbeeld heb ik bijvoorbeeld de bereikwaarde in cel I2 en verwijs ik ernaar in de formule:

=FILTER($A$2:$C$11,$B$2:$B$11=I1)

Dit maakt de formule nog nuttiger, nu hoeft u alleen de bereikwaarde in cel I2 te wijzigen en het filter verandert automatisch.

U kunt ook een vervolgkeuzelijst in cel I2 hebben waar u eenvoudig een selectie kunt maken en de gefilterde gegevens onmiddellijk worden bijgewerkt.

Voorbeeld 2: Filter gegevens op basis van één criterium (groter of kleiner dan)

U kunt ook vergelijkingsoperators gebruiken in filterfuncties en alle records extraheren die groter of kleiner zijn dan een bepaalde waarde.

Stel dat u de onderstaande dataset heeft en u wilt alle records filteren met verkopen van meer dan 10000.

De volgende formule kan dit doen:

=FILTER($A$2:$C$11,($C$2:$C$11>10000))

Het array-argument verwijst naar de gehele dataset, in dit geval de voorwaarde ($C$2:$C$11>10000).

De formule controleert elk record op de waarde in kolom C.Als de waarde groter is dan 10000, wordt deze gefilterd, anders wordt deze genegeerd.

Als u alle records kleiner dan 10000 wilt krijgen, kunt u de volgende 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 records wilt filteren op basis van verkopen, kunt u de volgende formule gebruiken:

=FILTER($A$2:$C$11,($C$2:$C$11>=LARGE(C2:C11,3)))

De bovenstaande formule gebruikt de functie LARGE om de op twee na grootste waarde in de gegevensset te krijgen.Gebruik deze waarde vervolgens in een FILTER-functievoorwaarde om alle records te krijgen met verkopen groter dan of gelijk aan de op twee na grootste waarde.

Voorbeeld 3: Gegevens filteren met meerdere voorwaarden (AND)

Stel dat u de volgende dataset heeft en u wilt alle records in de Verenigde Staten filteren met een verkoopwaarde groter dan 10000.

Dit is een EN-voorwaarde, u moet twee dingen controleren: de regio moet in de VS zijn en de verkoop moet meer dan 10000 bedragen.Als aan slechts één voorwaarde is voldaan, mogen de resultaten niet worden gefilterd.

Hier is de filterformule die records filtert met de VS als regio en met meer dan 10000 verkopen:

=FILTER($A$2:$C$11,($B$2:$B$11="US")*($C$2:$C$11>10000))

請注意，標準（稱為包含參數）是 ($B$2:$B$11=”US”)*($C$2:$C$11>10000)

Omdat ik twee voorwaarden gebruik en ik moet beide waar zijn, heb ik de vermenigvuldigingsoperator gebruikt om de twee voorwaarden te combineren.Dit retourneert een array van nullen en enen, waarbij 0 alleen wordt geretourneerd als aan beide voorwaarden is voldaan.

Als er geen overeenkomende records zijn, retourneert de functie #CALC!Fout.

Als u iets zinvols wilt retourneren (geen fout), kunt u een formule als deze gebruiken:

=FILTER($A$2:$C$11,($B$2:$B$11="USA")*($C$2:$C$11>10000),"Nothing Found")

Hier heb ik "niet gevonden" gebruikt als de derde parameter, die wordt gebruikt als er geen overeenkomend record wordt gevonden.

Voorbeeld 4: Gegevens filteren met meerdere criteria (OR)

U kunt ook de parameter "bevat" in de functie FILTER wijzigen om te controleren op OF-voorwaarden (waarbij elke gegeven voorwaarde waar kan zijn).

Laten we bijvoorbeeld zeggen dat u de onderstaande dataset heeft en dat u records wilt filteren waarvan het land de Verenigde Staten of Canada is.

Hier is de formule om dit te doen:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+($B$2:$B$11="Canada"))

Merk op dat ik in de bovenstaande formule alleen de twee voorwaarden toevoeg met behulp van de opteloperator.Aangezien elk van deze voorwaarden een array van TRUE en FALSE retourneert, kan ik een gecombineerde array toevoegen die 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 Verenigde Staten is of de verkoopwaarde hoger is dan 10000.

De volgende formule doet dit:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+(C2:C11>10000))

OPMERKING: Wanneer u de EN-voorwaarde in de FILTER-functie gebruikt, gebruikt u de vermenigvuldigingsoperator (*), en wanneer u de OF-voorwaarde gebruikt, gebruikt u de opteloperator (+).

Voorbeeld 5: Gegevens filteren voor records boven/onder het gemiddelde

U kunt formules in de FILTER-functie gebruiken om records met waarden boven of onder het gemiddelde te filteren en te extraheren.

Stel dat u de onderstaande dataset heeft en dat u alle records wilt filteren met een verkoopwaarde boven het gemiddelde.

Dit doe je met de volgende formule:

=FILTER($A$2:$C$11,C2:C11>AVERAGE(C2:C11))

Nogmaals, voor onder het gemiddelde kun je de volgende formule gebruiken:

=FILTER($A$2:$C$11,C2:C11<AVERAGE(C2:C11))

Voorbeeld 6: Filter alleen even records (of oneven records)

Als u snel alle records in even of oneven rijen moet filteren en extraheren, kunt u hiervoor de functie FILTER gebruiken.

Om dit te doen, moet u het regelnummer in de functie FILTER controleren en alleen de regelnummers filteren die overeenkomen met de regelnummervoorwaarde.

Stel je hebt een dataset zoals hieronder en ik wil alleen even records uit deze dataset halen.

Hier is de formule om dit te doen:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=0)

De bovenstaande formule gebruikt de MOD-functie om het rijnummer (gegeven door de ROW-functie) van elk record te controleren.

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 2 heb afgetrokken van het ROW(A11:A1)-gedeelte omdat het eerste record zich in de tweede rij bevindt, waardoor de rijnummers worden aangepast om de tweede rij als het eerste record te behandelen.

Op dezelfde manier kunt u alle oneven records filteren met de volgende formule:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=1)

Voorbeeld 7: Gefilterde gegevens sorteren met een formule

Door de FILTER-functie met andere functies te gebruiken, kunnen we meer doen.

Als u bijvoorbeeld de functie FILTER gebruikt om een ​​gegevensset te filteren, kunt u de functie SORTEREN gebruiken om gesorteerde resultaten te krijgen.

Stel dat u een dataset heeft zoals hieronder weergegeven en u wilt alle records filteren met een omzet van meer dan 10000.Met deze functie kunt u de functie SORTEREN gebruiken om ervoor te zorgen dat de resulterende gegevens worden gesorteerd op verkoop.

De volgende formule doet dit:

=SORT(FILTER($A$2:$C$11,($C$2:$C$11>10000)),3,-1)

De bovenstaande functie gebruikt de FILTER-functie om de gegevens in kolom C te krijgen met een omzet van meer dan 10000.Gebruik vervolgens de array die wordt geretourneerd door de functie FILTER in de functie SORTEREN om de gegevens te sorteren op verkoop.

De tweede parameter in de SORT-functie is 3, dat is om te sorteren volgens de derde kolom.De vierde parameter is -1, waarmee de gegevens in aflopende volgorde worden gesorteerd.

Dit zijn dus 7 voorbeelden van het gebruik van de FILTER-functie in Excel.

Ik hoop dat je deze tutorial nuttig vond.