Excel -filterfunktion - forklaret med eksempler + video

Se video - Excel FILTER Funktion Eksempler

Office 365 har nogle fantastiske funktioner - f.eks. XLOOKUP, SORT og FILTER.

Når det kommer til filtrering af data i Excel, i præ-Office 365-verdenen, var vi for det meste afhængige af et indbygget Excel-filter eller maksimalt det avancerede filter eller komplekse SUMPRODUCT-formler. Hvis du skulle filtrere en del af et datasæt, var det normalt en kompleks løsning (noget jeg har dækket her).

Men med den nye FILTER -funktion er det nu virkelig let hurtigt at filtrere en del af datasættet ud fra en betingelse.

Og i denne vejledning vil jeg vise dig, hvor fantastisk den nye FILTER -funktion er og nogle nyttige ting, du kan gøre med dette.

Men før jeg går ind på eksemplerne, lad os hurtigt lære mere om syntaksen for FILTER -funktionen.

Hvis du vil have disse nye funktioner i Excel, kan du opgradering til Office 365 (tilmeld dig insider -programmet for at få adgang til alle funktioner/formler)

Excel -filterfunktion - syntaks

Nedenfor er syntaksen for FILTER -funktionen:

= FILTER (array, inkluder, [if_empty])

• array - dette er celleområdet, hvor du har dataene, og du vil filtrere nogle data fra dem
• omfatte - dette er betingelsen, der fortæller funktionen, hvilke poster der skal filtreres
• [if_empty] - dette er et valgfrit argument, hvor du kan angive, hvad du skal returnere, hvis der ikke findes resultater ved FILTER -funktionen. Som standard (når den ikke er angivet), returnerer den #CALC! fejl

Lad os nu se på nogle fantastiske filterfunktionseksempler og ting, den kan gøre, som før var ret kompleks i dens fravær.

Klik her for at downloade eksempelfilen og følge med

Eksempel 1: Filtrering af data baseret på ét kriterium (område)

Antag, at du har et datasæt som vist nedenfor, og du vil filtrere alle poster kun for USA.

Nedenfor er FILTER -formlen, der gør dette:

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

Ovenstående formel bruger datasættet som array og betingelsen er $ B $ 2: $ B $ 11 = ”US”

Denne betingelse ville få FILTER -funktionen til at kontrollere hver celle i kolonne B (en, der har regionen), og kun de poster, der matcher dette kriterium, filtreres.

I dette eksempel har jeg også de originale data og de filtrerede data på det samme ark, men du kan også have disse i separate ark eller endda projektmapper.

Filterfunktion returnerer et resultat, der er et dynamisk array (hvilket betyder, at det i stedet for at returnere en værdi returnerer et array, der spildes til andre celler).

For at dette skal fungere, skal du have et område, hvor resultatet ville blive tomt. I nogen af ​​cellerne i dette område (E2: G5 i dette eksempel) har allerede noget i det, giver funktionen dig #SPILL -fejlen.

Da dette også er et dynamisk array, kan du ikke ændre en del af resultatet. Du kan enten slette hele området, der har resultatet, eller celle E2 (hvor formlen blev indtastet). Begge disse ville slette hele det resulterende array. Men du kan ikke ændre nogen individuel celle (eller slette den).

I ovenstående formel har jeg hårdkodet regionværdien, men du kan også have den i en celle og derefter henvise til den celle, der har regionsværdien.

For eksempel har jeg i nedenstående eksempel regionens værdi i celle I2, og dette refereres derefter til formlen:

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

Dette gør formlen endnu mere nyttig, og nu kan du blot ændre regionsværdien i celle I2, og filteret ændres automatisk.

Du kan også have en rullemenu i celle I2, hvor du simpelthen kan foretage valget, og det ville øjeblikkeligt opdatere de filtrerede data.

Eksempel 2: Filtrering af data baseret på ét kriterium (mere end eller mindre end)

Du kan også bruge komparative operatorer inden for filterfunktionen og udtrække alle de poster, der er mere eller mindre end en bestemt værdi.

Antag f.eks., At du har datasættet som vist herunder, og du vil filtrere alle de poster, hvor salgsværdien er mere end 10000.

Nedenstående formel kan gøre dette:

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

Arrayargumentet refererer til hele datasættet, og betingelsen er i dette tilfælde ($ C $ 2: $ C $ 11> 10000).

Formlen kontrollerer hver post for værdien i kolonne C. Hvis værdien er mere end 10000, filtreres den, ellers ignoreres den.

Hvis du vil have alle poster mindre end 10000, kan du bruge nedenstående formel:

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

Du kan også blive mere kreativ med FILTER -formlen. Hvis du f.eks. Vil filtrere de tre øverste poster baseret på salgsværdien, kan du bruge nedenstående formel:

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

Ovenstående formel bruger LARGE -funktionen til at få den tredjestørste værdi i datasættet. Denne værdi bruges derefter i FILTER-funktionskriterierne til at hente alle de poster, hvor salgsværdien er mere end eller lig med den tredjestørste værdi.

Klik her for at downloade eksempelfilen og følge med

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

Antag, at du har nedenstående datasæt, og du vil filtrere alle poster for USA, hvor salgsværdien er mere end 10000.

Dette er en AND -tilstand, hvor du skal kontrollere to ting - regionen skal til USA, og salget skal være mere end 10000. Hvis kun én betingelse er opfyldt, bør resultaterne ikke filtreres.

Nedenfor er FILTER -formlen, der filtrerer poster med USA som regionen og salg på mere end 10000:

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

Bemærk, at kriteriet (kaldet inkluder argumentet) er ($ B $ 2: $ B $ 11 = ”US”)*($ C $ 2: $ C $ 11> 10000)

Da jeg bruger to betingelser, og jeg skal have begge til at være sande, har jeg brugt multiplikationsoperatoren til at kombinere disse to kriterier. Dette returnerer en matrix med 0’er og 1’ere, hvor 1 kun returneres, når begge betingelser er opfyldt.

Hvis der ikke er nogen poster, der opfylder kriterierne, returnerer funktionen #CALC! fejl.

Og hvis du vil returnere noget, der betyder (i stedet for fejlen), kan du bruge en formel som vist herunder:

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

Her har jeg brugt “Not Found” som det tredje argument, som bruges, når der ikke findes poster, der matcher kriterierne.

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

Du kan også ændre 'inkluder' -argumentet i FILTER -funktionen for at kontrollere, om der er et ELLER -kriterium (hvor en af ​​de givne betingelser kan være sande).

Antag f.eks., At du har datasættet som vist herunder, og du vil filtrere de poster, hvor landet enten er USA eller Canada.

Nedenfor er formlen, der gør dette:

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

Bemærk, at i ovenstående formel har jeg simpelthen tilføjet de to betingelser ved hjælp af tilføjelsesoperatoren. Da hver af disse betingelser returnerer en matrix med SAND og FALSK, kan jeg tilføje for at få en kombineret matrix, hvor den er SAND, hvis en af ​​betingelserne er opfyldt.

Et andet eksempel kan være, når du vil filtrere alle de poster, hvor enten landet er USA eller salgsværdien er mere end 10000.

Nedenstående formel gør dette:

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

Bemærk: Når du bruger AND -kriterier i en FILTER -funktion, skal du bruge multiplikationsoperatoren (*), og når du bruger OR -kriterierne, skal du bruge additionsoperatoren (+).

Eksempel 5: Filtrering af data for at komme over/under gennemsnittet

Du kan bruge formler inden for FILTER -funktionen til at filtrere og udtrække poster, hvor værdien er over eller under gennemsnittet.

Antag f.eks., At du har datasættet som vist nedenfor, og du vil filtrere alle de poster, hvor salgsværdien er over gennemsnittet.

Du kan gøre det ved hjælp af følgende formel:

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

Tilsvarende kan du for under gennemsnittet bruge nedenstående formel:

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

Klik her for at downloade eksempelfilen og følge med

Eksempel 6: Filtrering af kun EVEN -nummerposter (eller ODD -nummerposter)

Hvis du hurtigt skal filtrere og udtrække alle poster fra lige rækker eller ulige talrækker, kan du gøre det med FILTER -funktionen.

For at gøre dette skal du kontrollere rækketallet inden for FILTER -funktionen og kun filtrere rækkenumre, der opfylder kriterierne for rækkenumre.

Antag, at du har datasættet som vist nedenfor, og jeg vil kun udtrække lige nummererede poster fra dette datasæt.

Nedenfor er formlen, der gør dette:

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

Ovenstående formel bruger MOD -funktionen til at kontrollere rækkenummeret for hver post (som er givet ved ROW -funktionen).

Formlen MOD (RÆK (A2: A11) -1,2) = 0 returnerer SAND, når rækkenummeret er lige og FALSKT, når det er ulige. Bemærk, at jeg har trukket 1 fra ROW (A2: A11) delen, da den første post er i den anden række, og dette justerer rækkenummeret for at betragte den anden række som den første post.

På samme måde kan du filtrere alle de ulige nummererede poster ved hjælp af nedenstående formel:

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

Eksempel 7: Sorter de filtrerede data med formel

Brug af FILTER -funktion med andre funktioner giver os mulighed for at få meget mere gjort.

For eksempel, hvis du filtrerer et datasæt ved hjælp af FILTER -funktionen, kan du bruge SORT -funktionen med det for at få det resultat, der allerede er sorteret.

Antag, at du har et datasæt som vist nedenfor, og du vil filtrere alle de poster, hvor salgsværdien er mere end 10000. Du kan bruge funktionen SORT med funktionen til at sikre, at de resulterende data er sorteret baseret på salgsværdien.

Nedenstående formel gør dette:

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

Ovenstående funktion bruger FILTER -funktionen til at hente de data, hvor salgsværdien i kolonne C er mere end 10000. Denne matrix, der returneres af FILTER -funktionen, bruges derefter inden for SORT -funktionen til at sortere disse data baseret på salgsværdien.

Det andet argument i SORT -funktionen er 3, som skal sorteres baseret på den tredje kolonne. Og det fjerde argument er -1, som skal sortere disse data i faldende rækkefølge.

Klik her for at downloade eksempelfilen

Så det er 7 eksempler på at bruge FILTER -funktionen i Excel.

Håber du fandt denne vejledning nyttig!

Du kan også lide følgende Excel -selvstudier:

1. Sådan filtreres celler med fed skriftformatering i Excel
2. Dynamisk Excel -filter søgefelt
3. Sådan filtreres data i en pivottabel i Excel