Microsoft Excels nye FILTER()-funktion er et fantastisk værktøj til rapportering og dashboards. Jeg vil vise dig, hvordan du bruger den til at få mere fra hånden.
Filtrering er en stor del af mange Microsoft Excel-ark, og heldigvis er der mange måder at gøre det på. Microsoft 365’s nye FILTER()-funktion er en kraftfuld opgradering. Du får ikke nødvendigvis forskellige resultater ved hjælp af denne nye dynamiske array-funktion, men du får disse resultater ved hjælp af en enkelt funktion! I denne artikel gennemgår jeg den indbyggede filterfunktion, så lærer du, hvordan du bruger den nye FILTER()-funktion.
Jeg bruger Microsoft 365 (desktop) på et Windows 10 64-bit system. Den indbyggede filterfunktion er kun tilgængelig i båndversioner. FILTER() er tilgængelig for Microsoft 365, Excel 2021, Excel til web, Excel til iPad og iPhone, Excel til Android-tablets og telefoner. For nemheds skyld kan du downloade eksempel filen. Denne artikel forudsætter, at du har grundlæggende Excel-færdigheder, men selv en nybegynder burde kunne følge instruktionerne med succes.
Sådan bruges filterfunktionen i Microsoft Excel
Excels filterfunktion lader dig filtrere efter flere værdier, så du har muligvis slet ikke brug for FILTER()-funktionen. Af den grund vil jeg gennemgå et hurtigt eksempel, så du kan træffe et informeret valg. Figuren viser et simpelt datasæt med filtre. Hvis du vil anvende filtre, skal du klikke på fanen Data og derefter klikke på Filter i gruppen Sorter og filtrer.
For at bruge filtrene skal du blot klikke på den relevante listepile i overskriftscellen. Prøv det nu ved at klikke på rullemenuen Område. Det resulterende panel lader dig filtrere på mange forskellige måder. Når der ikke er indstillet noget filter, kontrolleres alle værdierne. Hvis du vil sortere efter en eller flere værdier, men ikke alle værdierne, skal du fjerne markeringen i punktet (Vælg alle). Afkryds derefter de emner, du vil se. Figuren viser udvalgte nordvest og sydvest. Efterfølgende viser det filtrerede sæt vist i den næste figur kun poster for disse to værdier; der er ingen registreringer vist for Central.
Denne funktion er fleksibel nok til de fleste situationer, men den har mindst en begrænsning: Du kan ikke vise det filtrerede sæt et andet sted. Denne funktion filtrerer kun poster på plads. Takket være den nye FILTER()-funktion er det næsten lige så let som at bruge filterfunktionen.
Lidt om funktionen FILTER() i Excel
Visning af et filtreret sæt på et andet sted gør det let at rapportere og fungerer især godt i et dashboard-setup. Indtil FILTER() blev introduceret, krævede en smule indsats og dygtighed at få vist et filtreret sæt på et andet sted.
FILTER() er en af Microsoft 365s nye dynamiske array-funktioner. Det understøtter det, der er kendt som et spildområde, som er resultatet af en dynamisk matrixformel, der returnerer flere værdier – dets output spildes ud over inputcellen. Kort sagt er et spildområde et område med beregnede resultater fra en formel. Når du vælger en celle i et spildområde, fremhæver Excel hele området med en blå kant. Du finder altid formlen i den øverste celle i dette område.
Denne funktion bruger syntaksen
FILTER(array; inkluder; [hvis_tom])
hvor array identificerer kildedataene, inkluder identificerer de værdier, du vil se i det filtrerede datasæt, og det valgfrie hvis_tom angiver den værdi, der skal vises, når resultatet er et tomt sæt.
Du kan bruge FILTER() til at returnere en eller flere kolonner. Derudover kan du filtrere efter en enkelt kolonne eller flere. Men lad os nu få funktionen FILTER() til at fungere.
Sådan bruges funktionen FILTER () i Excel
Brug af FILTER() har et krav: Du skal bruge de samme overskrifter i det filtrerede sætområde. Det originale datasæt i B2:F18 er kildedataene. Du skal blot kopiere overskrifterne til et andet sted.
Selvom det ikke er et krav, er funktionen mere fleksibel, hvis du bruger inputceller til inkluderingsargumentet. Figuren viser den nødvendige opsætning. Vi bruger kolonne I til at oprette mere fleksible, dynamiske funktioner. Det filtrerede datasæt vises i kolonner K til O.
Selvom du sjældent vil vise de filtrerede resultater fra kun en kolonne, er det muligt, så lad os gennemgå et hurtigt eksempel. Vis specifikt medarbejder for regionen Syd ved at indtaste funktionen
=FILTRER(D3:D18;E3:E18=I5;"Ingen resultater")
i M3, som vist i figuren. Indtast derefter Syd i I5, områdets inputcelle. (Fordi funktionen er indtastet i en enkelt celle spildes resultaterne i det tilstødende område, er absolut referencer ikke påkrævet.)
Resultatet er let at tyde ved at gennemgå funktionens argumenter:
- Arrayargumentet, D3:D18, viser kun matchende værdier fra kolonnen Medarbejder (kolonne D).
- Inkluder argumentet, E3:E18=I5, filtrerer listen efter værdien i celle I5, som er Syd.
Funktionen returnerer kun medarbejderværdierne for området Syd.
Lad os komplicere tingene lidt ved at tilføje flere display- og filtreringsværdier. Indtast funktionen
=FILTRER(B3:F18;(D3:D18=I4)*(E3:E18=I5);"Ingen resultat")
i K3, som vist i figuren. (Jeg har anvendt passende formatering, fordi funktionen FILTER() ikke kan.)
Inkluder argumentet (D3:D18=I4)*(E3:E18=I5), er måske ukendt for dig. *-Tegnet fungerer som en OG-operator. På almindeligt dansk filtrerer dette argument efter medarbejderværdien i I4 og områdeværdien i I5. Det resulterende filtrerede sæt indeholder kun én post for Clausen i området Syd. Hvis du vil anvende en ELLER-operator i inkluderingsargumentet, skal du bruge tegnet + i stedet for *-tegnet.
Der er dog endnu en måde at forbedre resultaterne på. Når det filtrerede sæt har flere poster, vil du måske sortere resultaterne ved hjælp af SORTER()
Sådan tilføjes funktionen SORTER() i Excel
SORTER()-funktionen er en anden ny dynamisk array-funktion. Det returnerer et sorteret array og bruger følgende syntaks:
SORTER(array, [sorter_indeks], [sortering_rækkefølge],[efter_kol])
hvor array er det eneste nødvendige argument og identificerer det område, der skal sorteres. De valgfrie argumenter følger:
sorter_indeks: En numerisk forskydningsværdi, der identificerer rækken eller kolonnen, der skal sorteres efter.
sorter_rækkefølge: tallet 1 for stigende sortering eller -1 for faldende, hvor 1 er standard.
efter_kol: De logiske værdier SAND for en række sortering og FALSK for en kolonne sortering, hvor SAND er standard.
Det nuværende FILTER() returnerer kun én post, så lad os fjerne Clausen fra I4 og returnere et faldende sorteret filtreret sæt for område Syd, sorteret efter kolonnen Værdi. For at gøre dette skal du indtaste funktionen
=SORTER(FILTER(B3:F18;E3:E13=I5; "Ingen resultater"); 2;-1)
i K3, som vist i figuren.
SORTER()’s array-argument er hele FILTER()-funktionen. Sorter_indeks-værdien på 2 sorterer efter værdikolonnen, den anden kolonne i array. Sorter_indeks-værdien er -1, hvilket anvender en faldende sortering. Resultatet er et sorteret filtreret sæt, der viser den højeste værdi først for område Syd.
Sidst men ikke mindst
Der er et par ting, du måske vil medtage, når du anvender FILTER() i dit eget arbejde. For det første indeholder eksempelarket en inputcelle for hver kolonne i kildedatasættet. Jeg anbefaler dog, at du kun medtager inputceller for de kolonner, der refereres til fra FILTER() -funktionens inkluder-argument. At tilbyde en inputcelle, der ikke opdaterer det filtrerede sæt, er forvirrende for brugerne.
Den indbyggede filterfunktion konverterer datasættet til et tabelobjekt. FILTER()-funktionerne refererer fortsat til den samme kildetabel. Når du indtaster funktionerne, hvis du bruger markeringsmetoden til at indsætte referencer, vil du se Excel’s tabelstruktur, der refererer til arbejdet i stedet for de faktiske område (array) referencer, jeg bruger. Bare rolig – alt vil stadig fungere.
Fordi kildedataene er et tabelobjekt, er alt dynamisk. Hvis du tilføjer, sletter eller ændrer en post i kildedataene, opdateres det filtrerede datasæt i overensstemmelse hermed.
I en rigtig rapporterings- eller dashboard-situation kan du overveje at bruge datavalidering til at oprette listefelterne for inputcellerne. Dette forhindrer fejl, når inputværdien er ugyldig.
Du kan pakker en FILTER()-funktion i en samlet funktion, f.eks. SUM(), MIDDEL() osv. Hvis du gør det, returneres kun en værdi, resultatet af aggregatet på de filtrerede resultater af FILTER().