Hvordan bruke Excel FILTER-funksjonen

Office 365 har noen flotte funksjoner som XLOOKUP, SORT og FILTER.

Ved filtrering av data i Excel, i pre-Office 365-verdenen, stolte vi mest på innebygde Excel-filtre eller på det meste avanserte filtre eller komplekse SUMPRODUCT-formler.Dette er ofte en komplisert løsning hvis du må filtrere deler av datasettet.

Men med den nye FILTER-funksjonen er det nå veldig enkelt å raskt filtrere deler av et datasett basert på forhold.

I denne opplæringen skal jeg vise deg hvor fantastisk den nye FILTER-funksjonen er, og noen nyttige ting du kan gjøre med den.

Men før jeg kommer inn på eksemplet, la oss ta en rask titt på syntaksen til FILTER-funksjonen.

Hvis du ønsker å få disse nye funksjonene i Excel, kan duOppgrader til Office 365(Bli med i det interne programmet for å få tilgang til alle funksjoner/formler)

Excel-filterfunksjon – syntaks

Følgende er syntaksen til FILTER-funksjonen:

=FILTER(matrise,inkluder,[hvis_tom])

• matrise - dette er celleområdet der du har dataene og vil filtrere noen data fra dem
• inkludere – Dette er tilstanden som forteller funksjonen hvilke poster som skal filtreres
• [hvis_tom] – Dette er en valgfri parameter der du kan spesifisere hva som skal returneres hvis FILTER-funksjonen ikke finner resultater.Som standard (når ikke spesifisert) returnerer den #CALC!Feil

La oss nå se på noen fantastiske eksempler på filterfunksjoner og hva det kan gjøre som tidligere var veldig komplisert uten det.

Eksempel 1: Filtrer data basert på én betingelse (region)

Anta at du har et datasett som det nedenfor, og at du bare vil filtrere alle poster i USA.

Her er FILTER-formelen som gjør dette:

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

Formelen ovenfor tar datasettet som en matrise, og betingelsen er $B$2:$B$11="US"

Denne tilstanden vil føre til at FILTER-funksjonen undersøker hver celle i kolonne B (celler som har området) og filtrerer bare de postene som samsvarer med denne betingelsen.

I dette eksemplet legger jeg også de originale og filtrerte dataene på samme ark, men du kan også legge dem i separate ark eller til og med arbeidsbøker.

Filterfunksjonen returnerer en dynamisk rekke resultater (som betyr at i stedet for å returnere en verdi, returnerer den en matrise som renner over i andre celler).

For dette må du ha et område hvor resultatet er tomt.Det er allerede noe i en hvilken som helst celle i området (E2:G5 i dette eksemplet), og funksjonen vil gi deg #SPILL-feilen.

Siden dette er en dynamisk matrise, kan du ikke endre en del av resultatet.Du kan slette hele området med resultatet eller celle E2 (hvor formelen er angitt).Begge disse vil slette hele resultatarrayen.Men du kan ikke endre en enkelt celle (eller slette den).

I formelen ovenfor har jeg hardkodet områdeverdien, men du kan også sette den i en celle og referere til den cellen med områdeverdien.

For eksempel, i eksemplet nedenfor, har jeg områdeverdien i celle I2 og refererer deretter til den i formelen:

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

Dette gjør formelen enda mer nyttig, nå trenger du bare å endre områdeverdien i celle I2 og filteret endres automatisk.

Du kan også ha en rullegardin i celle I2 hvor du ganske enkelt kan velge og den vil oppdatere de filtrerte dataene umiddelbart.

Eksempel 2: Filtrer data basert på ett kriterium (større eller mindre enn)

Du kan også bruke sammenligningsoperatorer i filterfunksjoner og trekke ut alle poster som er større eller mindre enn en viss verdi.

Anta for eksempel at du har datasettet vist nedenfor, og du vil filtrere alle poster med salg over 10000 XNUMX.

Følgende formel kan gjøre dette:

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

Array-argumentet refererer til hele datasettet, i dette tilfellet betingelsen ($C$2:$C$11>10000).

Formelen sjekker hver post for verdien i kolonne C.Hvis verdien er større enn 10000 XNUMX, filtreres den, ellers ignoreres den.

Hvis du ønsker å få alle poster mindre enn 10000 XNUMX, kan du bruke følgende formel:

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

Du kan også bli mer kreativ med FILTER-formelen.Hvis du for eksempel ønsker å filtrere de tre øverste postene basert på salg, kan du bruke følgende formel:

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

Formelen ovenfor bruker LARGE-funksjonen for å få den tredje største verdien i datasettet.Bruk deretter den verdien i en FILTER-funksjonstilstand for å få alle poster med salg større enn eller lik den tredje største verdien.

Eksempel 3: Filtrering av data ved hjelp av flere betingelser (AND)

Anta at du har følgende datasett og du vil filtrere alle poster i USA med en salgsverdi større enn 10000 XNUMX.

Dette er en OG-tilstand, du må sjekke to ting - regionen må være i USA og salget må være over 10000 XNUMX.Hvis bare én betingelse er oppfylt, bør ikke resultatene filtreres.

Her er filterformelen som vil filtrere poster med USA som region og med mer enn 10000 XNUMX salg:

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

Siden jeg brukte to betingelser og jeg trengte at begge skulle være sanne, brukte jeg multiplikasjonsoperatoren for å kombinere de to betingelsene.Dette returnerer en matrise med 0-er og 1-er, der 1 bare returneres hvis begge betingelsene er oppfylt.

Hvis det ikke er noen samsvarende poster, vil funksjonen returnere #CALC!Feil.

Hvis du vil returnere noe meningsfullt (ikke en feil), kan du bruke en formel som denne:

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

Her har jeg brukt "ikke funnet" som den tredje parameteren, som brukes når ingen samsvarende post er funnet.

Eksempel 4: Filtrering av data ved hjelp av flere kriterier (ELLER)

Du kan også endre "inneholder"-parameteren i FILTER-funksjonen for å se etter ELLER-betingelser (der en gitt betingelse kan være sann).

La oss for eksempel si at du har datasettet vist nedenfor, og du vil filtrere poster der landet er USA eller Canada.

Her er formelen for å gjøre dette:

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

Merk at i formelen ovenfor legger jeg bare til de to betingelsene ved å bruke addisjonsoperatoren.Siden hver av disse betingelsene returnerer en matrise med TRUE og FALSE, kan jeg legge til en kombinert matrise som vil være TRUE hvis begge betingelsene er oppfylt.

Et annet eksempel kan være når du vil filtrere alle poster der landet er USA eller salgsverdien er over 10000 XNUMX.

Følgende formel vil gjøre dette:

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

MERK: Når du bruker AND-betingelser i FILTER-funksjonen, bruk multiplikasjonsoperatoren (*), og når du bruker OR-betingelser, bruk addisjonsoperatoren (+).

Eksempel 5: Filtrer data for poster over/under gjennomsnittet

Du kan bruke formler i FILTER-funksjonen for å filtrere og trekke ut poster med verdier over eller under gjennomsnittet.

La oss for eksempel si at du har datasettet vist nedenfor, og du vil filtrere alle poster med en salgsverdi over gjennomsnittet.

Du kan gjøre dette med følgende formel:

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

Igjen, for under gjennomsnittet kan du bruke følgende formel:

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

Eksempel 6: Filtrer bare partallsposter (eller oddetallsposter)

Hvis du raskt trenger å filtrere og trekke ut alle poster i partalls- eller oddetallsrader, kan du bruke FILTER-funksjonen for å gjøre det.

For å gjøre dette må du sjekke linjenummeret i FILTER-funksjonen og filtrere kun linjenumrene som samsvarer med linjenummerbetingelsen.

Anta at du har et datasett som nedenfor, og jeg vil bare trekke ut jevne poster fra dette datasettet.

Her er formelen for å gjøre dette:

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

Formelen ovenfor bruker MOD-funksjonen for å sjekke radnummeret (gitt av ROW-funksjonen) for hver post.

Formelen MOD(RAD(A2:A11)-1,2)=0 returnerer SANN når radnummeret er partall og FALSK når det er oddetall.Merk at jeg har trukket 2 fra ROW(A11:A1)-delen fordi den første posten er i den andre raden, som justerer radnumrene for å behandle den andre raden som den første posten.

På samme måte kan du filtrere alle odde poster med følgende formel:

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

Eksempel 7: Sortering av filtrerte data ved hjelp av en formel

Ved å bruke FILTER-funksjonen med andre funksjoner kan vi gjøre mer.

Hvis du for eksempel bruker FILTER-funksjonen til å filtrere et datasett, kan du bruke SORT-funksjonen for å få sorterte resultater.

Anta at du har et datasett som vist nedenfor og du vil filtrere alle poster med salg over 10000 XNUMX.Du kan bruke SORT-funksjonen med denne funksjonen for å sikre at de resulterende dataene blir sortert etter salg.

Følgende formel vil gjøre dette:

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

Funksjonen ovenfor bruker FILTER-funksjonen for å få dataene i kolonne C med salg større enn 10000 XNUMX.Bruk deretter matrisen som returneres av FILTER-funksjonen i SORTERING-funksjonen for å sortere dataene i henhold til salg.

Den andre parameteren i SORT-funksjonen er 3, som skal sortere i henhold til den tredje kolonnen.Den fjerde parameteren er -1, som sorterer dataene i synkende rekkefølge.

Så dette er 7 eksempler på bruk av FILTER-funksjonen i Excel.

Håper du fant denne opplæringen nyttig.