Excel -filterfunksjon - forklart med eksempler + video

Se video - Excel FILTER Funksjon Eksempler

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

Når det gjelder filtrering av data i Excel, i pre-Office 365-verdenen, var vi stort sett avhengige av det innebygde Excel-filteret eller maks Avansert filter eller komplekse SUMPRODUCT-formler. Hvis du måtte filtrere en del av et datasett, var det vanligvis en kompleks løsning (noe jeg har dekket her).

Men med den nye FILTER -funksjonen er det nå veldig enkelt å raskt filtrere en del av datasettet basert på en tilstand.

Og i denne opplæringen vil jeg vise deg hvor fantastisk den nye FILTER -funksjonen er og noen nyttige ting du kan gjøre med dette.

Men før jeg går inn på eksemplene, la oss raskt lære om syntaksen til FILTER -funksjonen.

Hvis du vil få disse nye funksjonene i Excel, kan du oppgradere til Office 365 (bli med i insider -programmet for å få tilgang til alle funksjoner/formler)

Excel -filterfunksjon - syntaks

Nedenfor er syntaksen til FILTER -funksjonen:

= FILTER (matrise, inkluder, [if_empty])

• matrise - dette er celleområdet der du har dataene, og du vil filtrere noen data fra dem
• inkludere - dette er tilstanden som forteller funksjonen hvilke poster som skal filtreres
• [if_empty] - dette er et valgfritt argument hvor du kan angi hva du skal returnere hvis FILTER -funksjonen ikke finner noen resultater. Som standard (når den ikke er angitt), returnerer den #KALK! feil

La oss nå se på noen fantastiske eksempler på filterfunksjoner og ting den kan gjøre, som tidligere var ganske komplisert i fraværet.

Klikk her for å laste ned eksempelfilen og følge med

Eksempel 1: Filtrering av data basert på ett kriterium (region)

Anta at du har et datasett som vist nedenfor, og du vil filtrere alle postene bare for USA.

Nedenfor er FILTER -formelen som gjør dette:

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

Formelen ovenfor bruker datasettet som array og betingelsen er $ B $ 2: $ B $ 11 = ”US”

Denne betingelsen ville få FILTER -funksjonen til å sjekke hver celle i kolonne B (en som har regionen), og bare de postene som samsvarer med dette kriteriet vil bli filtrert.

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

Filterfunksjon returnerer et resultat som er en dynamisk matrise (som betyr at den i stedet for å returnere en verdi, returnerer en matrise som søl til andre celler).

For at dette skal fungere, må du ha et område der resultatet blir tomt. I noen av cellene i dette området (E2: G5 i dette eksemplet) har allerede noe i det, vil funksjonen gi deg #SPILL -feilen.

Siden dette er en dynamisk matrise, kan du ikke endre en del av resultatet. Du kan enten slette hele området som har resultatet eller celle E2 (der formelen ble angitt). Begge disse ville slette hele den resulterende matrisen. Men du kan ikke endre noen enkelt celle (eller slette den).

I formelen ovenfor har jeg hardkodet regionverdien, men du kan også ha den i en celle og deretter referere til den cellen som har regionverdien.

For eksempel, i eksemplet nedenfor, har jeg regionverdien i celle I2, og dette refereres deretter til formelen:

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

Dette gjør formelen enda mer nyttig, og nå kan du ganske enkelt endre regionverdien i celle I2, og filteret vil automatisk endres.

Du kan også ha en rullegardinmeny i celle I2 hvor du ganske enkelt kan gjøre valget, og det ville umiddelbart oppdatere de filtrerte dataene.

Eksempel 2: Filtrering av data basert på ett kriterium (mer enn eller mindre enn)

Du kan også bruke komparative operatører i filterfunksjonen og trekke ut alle postene som er mer eller mindre enn en bestemt verdi.

Anta for eksempel at du har datasettet som vist nedenfor, og du vil filtrere alle postene der salgsverdien er mer enn 10000.

Formelen nedenfor kan gjøre dette:

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

Arrayargumentet refererer til hele datasettet, og betingelsen er i dette tilfellet ($ C $ 2: $ C $ 11> 10000).

Formelen sjekker hver post for verdien i kolonne C. Hvis verdien er mer enn 10000, filtreres den, ellers ignoreres den.

Hvis du vil få alle postene mindre enn 10000, kan du bruke formelen nedenfor:

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

Du kan også bli mer kreativ med FILTER -formelen. For eksempel, hvis du vil filtrere de tre beste postene basert på salgsverdien, kan du bruke formelen nedenfor:

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

Formelen ovenfor bruker LARGE -funksjonen for å få den tredje største verdien i datasettet. Denne verdien brukes deretter i FILTER-funksjonskriteriene for å få alle postene der salgsverdien er mer enn eller lik den tredje største verdien.

Klikk her for å laste ned eksempelfilen og følge med

Eksempel 3: Filtrering av data med flere kriterier (AND)

Anta at du har datasettet nedenfor, og du vil filtrere alle postene for USA der salgsverdien er mer enn 10000.

Dette er en OG -tilstand der du må kontrollere to ting - regionen trenger til USA og salget må være mer enn 10000. Hvis bare én betingelse er oppfylt, bør resultatene ikke filtreres.

Nedenfor er FILTER -formelen som filtrerer poster med USA som regionen og salg på mer enn 10000:

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

Vær oppmerksom på at kriteriet (kalt inkluder argumentet) er ($ B $ 2: $ B $ 11 = ”US”)*($ C $ 2: $ C $ 11> 10000)

Siden jeg bruker to betingelser og jeg trenger begge for å være sanne, har jeg brukt multiplikasjonsoperatoren for å kombinere disse to kriteriene. Dette returnerer en matrise med 0’er og 1’er, der 1 bare returneres når begge betingelsene er oppfylt.

Hvis det ikke er noen poster som oppfyller kriteriene, vil funksjonen returnere #CALC! feil.

Og hvis du vil returnere noe som betyr noe (i stedet for feilen), kan du bruke en formel som vist nedenfor:

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

Her har jeg brukt "Not Found" som det tredje argumentet, som brukes når det ikke blir funnet poster som samsvarer med kriteriene.

Eksempel 4: Filtrering av data med flere kriterier (OR)

Du kan også endre "inkludere" -argumentet i FILTER -funksjonen for å se etter et ELLER -kriterium (der noen av de gitte betingelsene kan være sanne).

Anta for eksempel at du har datasettet som vist nedenfor, og du vil filtrere postene der landet enten er USA eller Canada.

Nedenfor er formelen som gjør dette:

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

Vær oppmerksom på at i formelen ovenfor har jeg ganske enkelt lagt til de to betingelsene ved å bruke tilleggsoperatoren. Siden hver av disse betingelsene returnerer en matrise med SANN og FALSK, kan jeg legge til for å få en kombinert matrise der den er SANN hvis noen av betingelsene er oppfylt.

Et annet eksempel kan være når du vil filtrere alle postene der enten landet er USA eller salgsverdien er mer enn 10000.

Formelen nedenfor vil gjøre dette:

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

Merk: Når du bruker AND -kriterier i en FILTER -funksjon, bruker du multiplikasjonsoperatoren (*), og når du bruker OR -kriteriene, bruker du addisjonsoperatoren (+).

Eksempel 5: Filtrering av data for å komme over/under gjennomsnittlige poster

Du kan bruke formler i FILTER -funksjonen til å filtrere og trekke ut poster der verdien er over eller under gjennomsnittet.

Anta for eksempel at du har datasettet som vist nedenfor, og du vil filtrere alle postene der salgsverdien er over gjennomsnittet.

Du kan gjøre det ved å bruke følgende formel:

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

På samme måte, for under gjennomsnittet, kan du bruke formelen nedenfor:

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

Klikk her for å laste ned eksempelfilen og følge med

Eksempel 6: Filtrering av bare EVEN -nummeroppføringer (eller ODD -nummeroppføringer)

Hvis du raskt må filtrere og trekke ut alle postene fra partallrader eller oddetallrader, kan du gjøre det med FILTER -funksjonen.

For å gjøre dette må du sjekke radnummeret i FILTER -funksjonen, og bare filtrere radnummer som oppfyller radnummerkriteriene.

Anta at du har datasettet som vist nedenfor, og jeg vil bare trekke ut partallede poster fra dette datasettet.

Nedenfor er formelen som gjør dette:

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

Formelen ovenfor bruker MOD -funksjonen for å kontrollere radnummeret til hver post (som er gitt av ROW -funksjonen).

Formelen MOD (RAD (A2: A11) -1,2) = 0 returnerer SANN når radnummeret er partall og FALSKT når det er merkelig. Legg merke til at jeg har trukket fra 1 fra RAD (A2: A11) siden den første posten er i den andre raden, og dette justerer radnummeret for å betrakte den andre raden som den første posten.

På samme måte kan du filtrere alle oddetallspostene ved å bruke formelen nedenfor:

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

Eksempel 7: Sorter de filtrerte dataene med formel

Ved å bruke FILTER -funksjonen med andre funksjoner kan vi få gjort mye mer.

For eksempel, hvis du filtrerer et datasett ved hjelp av FILTER -funksjonen, kan du bruke SORT -funksjonen med det for å få resultatet som allerede er sortert.

Anta at du har et datasett som vist nedenfor, og du vil filtrere alle postene der salgsverdien er mer enn 10000. Du kan bruke SORT -funksjonen med funksjonen for å sikre at de resulterende dataene er sortert basert på salgsverdien.

Formelen nedenfor vil gjøre dette:

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

Funksjonen ovenfor bruker FILTER -funksjonen for å hente dataene der salgsverdien i kolonne C er mer enn 10000. Denne matrisen som returneres av FILTER -funksjonen, brukes deretter i SORT -funksjonen til å sortere disse dataene basert på salgsverdien.

Det andre argumentet i SORT -funksjonen er 3, som skal sorteres basert på den tredje kolonnen. Og det fjerde argumentet er -1 som er å sortere disse dataene i synkende rekkefølge.

Klikk her for å laste ned eksempelfilen

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

Håper du synes denne opplæringen var nyttig!

Du kan også like følgende Excel -opplæringsprogrammer:

1. Slik filtrerer du celler med fet skriftformatering i Excel
2. Dynamic Excel Filter Search Box
3. Slik filtrerer du data i et pivottabell i Excel