Hoe om die Excel FILTER-funksie te gebruik

Office 365 bring 'n paar wonderlike kenmerke soos XLOOKUP, SORT en FILTER.

Wanneer ons data in Excel gefiltreer het, het ons in die pre-Office 365-wêreld meestal staatgemaak op Excel-ingeboude filters of hoogstens gevorderde filters of komplekse SUMPRODUCT-formules.Dit is dikwels 'n ingewikkelde oplossing as jy 'n deel van die datastel moet filter.

Maar met die nuwe FILTER-funksie is dit nou baie maklik om dele van 'n datastel vinnig te filtreer op grond van toestande.

In hierdie tutoriaal sal ek jou wys hoe wonderlik die nuwe FILTER-funksie is, en 'n paar nuttige dinge wat jy daarmee kan doen.

Maar voordat ek by die voorbeeld ingaan, kom ons kyk vinnig na die sintaksis van die FILTER-funksie.

As jy hierdie nuwe kenmerke in Excel wil kry, kan jyGradeer op na Office 365(Sluit aan by die interne program om toegang tot alle kenmerke/formules te kry)

Excel-filterfunksie – sintaksis

Die volgende is die sintaksis van die FILTER-funksie:

=FILTER(skikking,sluit in,[as_leeg])

• verskeidenheid - dit is die reeks selle waar jy die data het en sommige data daaruit wil filtreer
• sluit - Dit is die toestand wat die funksie vertel watter rekords om te filter
• [as_leeg] – Dit is 'n opsionele parameter waar jy kan spesifiseer wat om terug te gee as die FILTER-funksie geen resultate vind nie.By verstek (wanneer nie gespesifiseer nie) gee dit #CALC!Fout

Kom ons kyk nou na 'n paar wonderlike voorbeelde van filterfunksies en wat dit kan doen wat vroeër baie ingewikkeld was daarsonder.

Voorbeeld 1: Filter data gebaseer op een toestand (streek)

Gestel jy het 'n datastel soos die een hieronder, en jy wil net alle rekords in die Verenigde State filter.

Hier is die FILTER formule wat dit doen:

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

Die formule hierbo neem die datastel as 'n skikking, en die toestand is $B$2:$B$11="VS"

Hierdie toestand sal veroorsaak dat die FILTER-funksie elke sel in kolom B (selle met die reeks) nagaan en net daardie rekords filter wat by hierdie toestand pas.

Ook in hierdie voorbeeld plaas ek die oorspronklike en gefiltreerde data op dieselfde blad, maar jy kan dit ook in aparte velle of selfs werkboeke plaas.

Die filterfunksie gee 'n dinamiese reeks resultate terug (wat beteken dat in plaas daarvan om 'n waarde terug te gee, dit 'n skikking terugstuur wat na ander selle oorloop).

Hiervoor moet jy 'n area hê waar die resultaat leeg is.Daar is reeds iets in enige sel in die reeks (E2:G5 in hierdie voorbeeld) en die funksie sal jou die #SPILL-fout gee.

Ook, aangesien dit 'n dinamiese skikking is, kan jy nie 'n deel van die resultaat verander nie.Jy kan die hele reeks uitvee met die resultaat of sel E2 (waar die formule ingevoer word).Albei hierdie sal die hele resultaatskikking uitvee.Maar jy kan nie enige enkele sel verander (of dit uitvee nie).

In die formule hierbo het ek die reekswaarde hardkodeer, maar jy kan dit ook in 'n sel plaas en daardie sel met die reekswaarde verwys.

Byvoorbeeld, in die voorbeeld hieronder het ek die reekswaarde in sel I2 en verwys dit dan in die formule:

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

Dit maak die formule selfs meer bruikbaar, nou hoef jy net die reekswaarde in sel I2 te verander en die filter sal outomaties verander.

Jy kan ook 'n aftreklys in sel I2 hê waar jy eenvoudig 'n keuse kan maak en dit sal die gefiltreerde data onmiddellik opdateer.

Voorbeeld 2: Filter data gebaseer op een kriterium (groter of minder as)

U kan ook vergelykingsoperateurs in filterfunksies gebruik en alle rekords groter of minder as 'n sekere waarde onttrek.

Gestel byvoorbeeld jy het die datastel wat hieronder gewys word, en jy wil alle rekords met verkope meer as 10000 XNUMX filter.

Die volgende formule kan dit doen:

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

Die skikkingsargument verwys na die hele datastel, in hierdie geval die toestand ($C$2:$C$11>10000).

Die formule kontroleer elke rekord vir die waarde in kolom C.As die waarde groter as 10000 is, word dit gefiltreer, anders word dit geïgnoreer.

As jy alle rekords minder as 10000 XNUMX wil kry, kan jy die volgende formule gebruik:

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

Jy kan ook meer kreatief raak met die FILTER-formule.As jy byvoorbeeld die top drie rekords op grond van verkope wil filtreer, kan jy die volgende formule gebruik:

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

Die formule hierbo gebruik die LARGE-funksie om die derde grootste waarde in die datastel te kry.Gebruik dan daardie waarde in 'n FILTER funksie toestand om alle rekords met verkope groter as of gelyk aan die derde grootste waarde te kry.

Voorbeeld 3: Filtreer data deur gebruik te maak van veelvuldige voorwaardes (EN)

Gestel jy het die volgende datastel en jy wil alle rekords in die Verenigde State met 'n verkoopswaarde groter as 10000 XNUMX filter.

Dit is 'n EN-toestand, jy moet twee dinge nagaan - die streek moet in die VSA wees en die verkope moet meer as 10000 XNUMX wees.As slegs een voorwaarde nagekom word, moet die resultate nie gefiltreer word nie.

Hier is die filterformule wat rekords sal filtreer met die VSA as die streek en met meer as 10000 XNUMX verkope:

=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)

Aangesien ek twee voorwaardes gebruik en ek het albei nodig om waar te wees, het ek die vermenigvuldigingsoperateur gebruik om die twee voorwaardes te kombineer.Dit gee 'n skikking van 0'e en 1'e terug, waar 1 slegs teruggestuur word as aan beide voorwaardes voldoen word.

As daar geen ooreenstemmende rekords is nie, sal die funksie #CALC!fout.

As jy iets betekenisvol wil teruggee (nie 'n fout nie), kan jy 'n formule soos hierdie gebruik:

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

Hier het ek "nie gevind nie" as die derde parameter gebruik, wat gebruik word wanneer geen ooreenstemmende rekord gevind word nie.

Voorbeeld 4: Filtreer data deur gebruik te maak van veelvuldige kriteria (OF)

Jy kan ook die "bevat" parameter in die FILTER funksie verander om te kyk vir OF toestande (waar enige gegewe voorwaarde waar kan wees).

Byvoorbeeld, kom ons sê jy het die datastel wat hieronder gewys word, en jy wil rekords filter waar die land die Verenigde State of Kanada is.

Hier is die formule om dit te doen:

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

Let daarop dat ek in die bogenoemde formule net die twee voorwaardes byvoeg deur die opteloperateur te gebruik.Aangesien elk van hierdie voorwaardes 'n skikking van WAAR en ONWAAR terugstuur, kan ek 'n gekombineerde skikking byvoeg wat WAAR sal wees as aan enige voorwaarde voldoen word.

Nog 'n voorbeeld kan wees wanneer jy alle rekords wil filtreer waar die land die Verenigde State is of die verkoopswaarde meer as 10000 XNUMX is.

Die volgende formule sal dit doen:

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

LET WEL: Wanneer die EN-voorwaarde in die FILTER-funksie gebruik word, gebruik die vermenigvuldigingsoperateur (*), en wanneer die OF-voorwaarde gebruik word, gebruik die opteloperateur (+).

Voorbeeld 5: Filter data vir rekords bo/onder gemiddeld

U kan formules in die FILTER-funksie gebruik om rekords te filter en te onttrek met waardes bo of onder die gemiddelde.

Byvoorbeeld, kom ons sê jy het die datastel wat hieronder gewys word, en jy wil alle rekords filtreer met 'n verkoopswaarde bo die gemiddelde.

Jy kan dit met die volgende formule doen:

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

Weereens, vir ondergemiddeld kan jy die volgende formule gebruik:

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

Voorbeeld 6: Filter net ewe rekords (of onewe rekords)

As jy vinnig alle rekords in ewe of onewe rye moet filter en onttrek, kan jy die FILTER-funksie gebruik om dit te doen.

Om dit te doen, moet jy die reëlnommer in die FILTER-funksie nagaan en net die reëlnommers filter wat by die reëlnommer-voorwaarde pas.

Gestel jy het 'n datastel soos hieronder en ek wil net selfs rekords uit hierdie datastel onttrek.

Hier is die formule om dit te doen:

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

Die formule hierbo gebruik die MOD-funksie om die rynommer (gegewe deur die ROW-funksie) van elke rekord na te gaan.

Die formule MOD(RY(A2:A11)-1,2)=0 gee WAAR as die rynommer ewe is en ONWAAR as dit onewe is.Let daarop dat ek 2 van die RY(A11:A1)-deel afgetrek het omdat die eerste rekord in die tweede ry is, wat die rynommers aanpas om die tweede ry as die eerste rekord te hanteer.

Net so kan jy alle vreemde rekords filter met die volgende formule:

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

Voorbeeld 7: Sorteer gefiltreerde data deur 'n formule te gebruik

Deur die FILTER-funksie saam met ander funksies te gebruik, kan ons meer doen.

Byvoorbeeld, as jy die FILTER-funksie gebruik om 'n datastel te filtreer, kan jy die SORT-funksie gebruik om gesorteerde resultate te kry.

Gestel jy het 'n datastel soos hieronder getoon en jy wil alle rekords met verkope meer as 10000 XNUMX filter.Jy kan die SORT-funksie saam met hierdie funksie gebruik om te verseker dat die resulterende data volgens verkope gesorteer word.

Die volgende formule sal dit doen:

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

Die funksie hierbo gebruik die FILTER-funksie om die data in kolom C te kry met verkope groter as 10000.Gebruik dan die skikking wat deur die FILTER-funksie in die SORT-funksie teruggestuur word om die data volgens verkope te sorteer.

Die tweede parameter in die SORT-funksie is 3, wat is om volgens die derde kolom te sorteer.Die vierde parameter is -1, wat die data in dalende volgorde sorteer.

Dit is dus 7 voorbeelde van die gebruik van die FILTER-funksie in Excel.

Hoop jy het hierdie tutoriaal nuttig gevind.