Sådan filtreres data i Excel
Jeg har for nylig skrevet en artikel om, hvordan man bruger sammendragsfunktioner i Excel for nemt at opsummere store mængder data, men den artikel tog højde for alle data på regnearket. Hvad hvis du kun vil se på en undergruppe af data og opsummere delmængden af data?
I Excel kan du oprette filtre på kolonner, der vil skjule rækker, der ikke passer til dit filter. Derudover kan du også bruge specialfunktioner i Excel til at opsummere data ved kun at bruge de filtrerede data.
I denne artikel vil jeg gå igennem trinene til oprettelse af filtre i Excel og også bruge indbyggede funktioner til at opsummere de filtrerede data.
Opret enkle filtre i Excel
I Excel kan du oprette enkle filtre og komplekse filtre. Lad os starte med enkle filtre. Når du arbejder med filtre, skal du altid have en række øverst, der bruges til etiketter. Det er ikke et krav at have denne række, men det gør det lettere at arbejde med filtre.
Oven har jeg nogle falske data, og jeg vil oprette et filter på by kolonne. I Excel er det virkelig nemt at gøre. Gå videre og klik på Data fane i båndet og derefter klikke på Filter knap. Du behøver ikke at vælge dataene på arket eller klikke i den første række heller.
Når du klikker på Filter, vil hver kolonne i første række automatisk have en lille dropdown-knap tilføjet helt til højre.
Gå nu videre og klik på rullemenuen i kolonnen By. Du får se et par forskellige muligheder, som jeg vil forklare nedenfor.
Øverst kan du hurtigt sortere alle rækkerne ved hjælp af værdierne i kolonnen By. Bemærk, at når du sorterer dataene, flytter den hele rækken, ikke kun værdierne i kolonnen By. Dette sikrer, at dine data forbliver intakte, ligesom det var før.
Det er også en god ide at tilføje en kolonne ved den meget foran kaldte ID og nummer det fra en til hvor mange rækker du har i dit regneark. På denne måde kan du altid sortere efter id-kolonnen og få dine data tilbage i samme rækkefølge som det var oprindeligt, hvis det er vigtigt for dig.
Som du kan se, er alle data i regnearket sorteret nu baseret på værdierne i kolonnen By. Indtil videre er der ingen rækker gemt. Lad os nu se i afkrydsningsfelterne nederst i filterdialogboksen. I mit eksempel har jeg kun tre unikke værdier i kolonnen By, og de tre vises i listen.
Jeg gik videre og afkrydsede to byer og forlod en kontrolleret. Nu har jeg kun 8 rækker data, og resten er skjult. Du kan let fortælle dig, kigger på filtrerede data, hvis du tjekker række numrene længst til venstre. Afhængigt af hvor mange rækker der er gemt, vil du se et par ekstra vandrette linjer, og farven på tallene bliver blå.
Lad os nu sige, at jeg vil filtrere på en anden kolonne for yderligere at reducere antallet af resultater. I kolonne C har jeg det samlede antal medlemmer i hver familie, og jeg vil kun se resultaterne for familier med mere end to medlemmer.
Gå videre og klik på rullemenuen i kolonne C, og du vil se de samme afkrydsningsfelter for hver unik værdi i kolonnen. Men i dette tilfælde vil vi klikke på Nummerfiltre og klik derefter på Bedre end. Som du kan se, er der også en masse andre muligheder.
En ny dialog vil dukke op, og her kan du indtaste værdien for filteret. Du kan også tilføje mere end ét kriterium med en AND eller OR funktion. Du kan sige, at du ønsker rækker, hvor værdien er større end 2 og ikke svarer til 5, for eksempel.
Nu er jeg ned til kun 5 rækker af data: kun familier fra New Orleans og med 3 eller flere medlemmer. Nemt nok? Bemærk, at du nemt kan rydde et filter på en kolonne ved at klikke på rullemenuen og derefter klikke på Ryd filter fra "kolonne navn" link.
Så det handler om simple filtre i Excel. De er meget nemme at bruge, og resultaterne er ret lige fremad. Lad os nu se på komplekse filtre ved hjælp af Fremskreden filtre dialog.
Opret avancerede filtre i Excel
Hvis du vil oprette mere avancerede filtre, skal du bruge Fremskreden filter dialog. For eksempel, lad os sige, at jeg ønskede at se alle familier, der bor i New Orleans med mere end 2 medlemmer i deres familie ELLER alle familier i Clarksville med mere end 3 medlemmer i deres familie OG kun dem med a .EDU slutter e-mail-adresse. Nu kan du ikke gøre det med et simpelt filter.
For at gøre dette skal vi opsætte Excel-arket lidt anderledes. Gå videre og indsæt et par rækker over dit sæt data og kopier overskriften etiketter nøjagtigt til første række som vist nedenfor.
Nu er hvordan avancerede filtre fungerer. Du skal først skrive dine kriterier i kolonnerne øverst og derefter klikke på Fremskreden knappen under Sorter og filtrer på den Data fanen.
Så hvad kan vi skrive ind i disse celler? OK, så lad os starte med vores eksempel. Vi vil kun se data fra New Orleans eller Clarksville, så lad os skrive dem i cellerne E2 og E3.
Når du indtaster værdier på forskellige rækker, betyder det OR. Nu ønsker vi New Orleans familier med mere end to medlemmer og Clarksville familier med mere end 3 medlemmer. For at gøre dette skal du indtaste > 2 i C2 og > 3 i C3.
Siden 2 og New Orleans ligger på samme række, bliver det en AND-operatør. Det samme gælder for række 3 ovenfor. Endelig vil vi kun have familier med .EDU slutter e-mail-adresse. For at gøre dette, skal du bare indtaste * .edu ind i både D2 og D3. Symbolet * betyder et hvilket som helst antal tegn.
Når du har gjort det, skal du klikke et vilkårligt sted i dit datasæt og derefter klikke på Fremskreden knap. Det Liste Range-feltet vil automatisk finde ud af dit datasæt, da du klikede på det, inden du klikker på knappen Avanceret. Klik nu på den lille lille knap til højre for Kriterieinterval knap.
Vælg alt fra A1 til E3 og klik derefter på den samme knap igen for at komme tilbage til dialogboksen Avanceret filter. Klik på OK, og dine data skal nu filtreres!
Som du kan se, har jeg kun 3 resultater, der matcher alle disse kriterier. Bemærk, at etiketterne for kriterierne skal matches nøjagtigt med etiketterne til datasættet for at dette skal fungere.
Du kan naturligvis oprette mange mere komplicerede forespørgsler ved hjælp af denne metode, så spil med det for at få de ønskede resultater. Endelig, lad os tale om at anvende opsummeringsfunktioner til filtrerede data.
Sammenfatning af filtrerede data
Lad os nu sige, jeg vil opsummere antallet af familiemedlemmer på mine filtrerede data, hvordan ville jeg få det til at gøre det? Nå, lad os rydde vores filter ved at klikke på Klar knap i båndet. Bare rolig, det er meget nemt at anvende det avancerede filter igen ved blot at klikke på knappen Avanceret og klikke på OK igen.
Læg i bunden af vores datasæt, lad os tilføje en celle, der hedder i alt og tilføj derefter en sumfunktion for at opsummere de samlede familiemedlemmer. I mit eksempel skrev jeg bare = SUM (C7: C31).
Så hvis jeg ser på alle familier, har jeg 78 medlemmer i alt. Lad os nu gå videre og genanvende vores avancerede filter og se, hvad der sker.
Ups! I stedet for at vise det korrekte nummer, 11, ser jeg stadig, at alt er 78! Hvorfor det? Godt, SUM-funktionen ignorerer ikke skjulte rækker, så det gør stadig beregningen ved hjælp af alle rækkerne. Heldigvis er der et par funktioner, du kan bruge til at ignorere skjulte rækker.
Den første er SUBTOTAL. Før vi bruger nogen af disse specialfunktioner, vil du gerne rydde dit filter og derefter indtaste funktionen.
Når filteret er ryddet, skal du gå videre og skrive = SUBTOTAL ( og du bør se en dropdown box vises med en masse valgmuligheder. Ved hjælp af denne funktion vælger du først den type opsummeringsfunktion, du vil bruge ved hjælp af et nummer.
I vores eksempel vil jeg bruge SUM, så jeg ville skrive nummer 9 eller bare klikke på det fra dropdown. Skriv derefter et komma og vælg celleintervallet.
Når du trykker på enter, skal du se værdien af 78 er den samme som tidligere. Men hvis du nu anvender filteret igen, vil vi se 11!
Fremragende! Det er præcis det, vi ønsker. Nu kan du justere dine filtre, og værdien vil altid afspejle kun de rækker, der aktuelt vises.
Den anden funktion, der fungerer stort set nøjagtigt det samme som SUBTOTAL-funktionen er SAMLET. Den eneste forskel er, at der er en anden parameter i AGGREGATE-funktionen, hvor du skal angive, at du vil ignorere skjulte rækker.
Den første parameter er den summeringsfunktion, du vil bruge, og som med SUBTOTAL repræsenterer 9 SUM-funktionen. Den anden mulighed er, hvor du skal skrive 5 for at ignorere skjulte rækker. Den sidste parameter er den samme og er rækkevidden af celler.
Du kan også læse min artikel om sammendragsfunktioner for at lære at bruge AGGREGATE-funktionen og andre funktioner som MODE, MEDIAN, GENNEMSÆT, osv. Mere detaljeret.
Forhåbentlig giver denne artikel dig et godt udgangspunkt for at oprette og bruge filtre i Excel. Hvis du har spørgsmål, er du velkommen til at skrive en kommentar. god fornøjelse!
