Jeg skrev for nylig en artikel om, hvordan man bruger sammendragsfunktioner i Excel til let at opsummere store mængder data, men den artikel tog hensyn til alle data på regnearket. Hvad hvis du kun ønsker at se på en delmængde af data og opsummere delmængden af ​​data?

I Excel kan du oprette filtre på kolonner, der skjuler rækker, der ikke svarer til dit filter. Derudover kan du også bruge specielle funktioner i Excel til at opsummere data ved kun at bruge de filtrerede data.

I denne artikel leder jeg dig gennem trinene til oprettelse af filtre i Excel og også ved hjælp af 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.

eksempeldata excel

Ovenfor har jeg nogle falske data, og jeg vil oprette et filter i kolonnen By. I Excel er dette virkelig let at gøre. Gå videre og klik på fanen Data i båndet, og klik derefter på knappen Filter. Du behøver ikke at vælge dataene på arket eller klikke på den første række.

excel datafilter

Når du klikker på Filter, tilføjes hver kolonne i den første række automatisk en lille dropdown-knap til højre.

tilføjet filter excel

Gå nu videre og klik på rullemenuen i kolonnen By. Du ser et par forskellige indstillinger, som jeg vil forklare nedenfor.

filterindstillinger excel

Øverst kan du hurtigt sortere alle rækker efter værdierne i kolonnen By. Bemærk, at når du sorterer dataene, vil den flytte hele rækken, ikke kun værdierne i kolonnen By. Dette vil sikre, at dine data forbliver intakte, ligesom de var før.

Det er også en god ide at tilføje en kolonne helt foran kaldet ID og nummerere den fra en til uanset 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 den samme rækkefølge, som de oprindeligt var, hvis det er vigtigt for dig.

data sorteret excel

Som du kan se, er alle data i regnearket nu sorteret baseret på værdierne i kolonnen By. Indtil videre er ingen rækker skjult. Lad os nu se på afkrydsningsfelterne nederst i filterdialogen. I mit eksempel har jeg kun tre unikke værdier i kolonnen By, og disse tre vises på listen.

filtrerede rækker udmærker sig

Jeg gik foran og fjernede afkrydsningen af ​​to byer og lod en kontrolleres. Nu har jeg kun 8 rækker med data, der vises, og resten er skjult. Du kan nemt fortælle, at du ser på filtrerede data, hvis du kontrollerer rækkenumrene helt til venstre. Afhængig af hvor mange rækker der er skjult, ser du et par ekstra vandrette linjer, og farven på numrene vil være 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.

nummerfilter excel

Gå videre og klik på rullemenuen i kolonne C, så får du de samme afkrydsningsfelter for hver unik værdi i kolonnen. I dette tilfælde vil vi dog klikke på Number Filters og derefter klikke på Greater Than. Som du kan se, er der også en masse andre muligheder.

er større end filter

En ny dialog vises, og her kan du indtaste værdien for filteret. Du kan også tilføje mere end et kriterium med en AND- eller OR-funktion. Du kan sige, at du vil have rækker, hvor værdien er større end 2 og for eksempel ikke lig med 5.

to filtre udmærker sig

Nu har jeg kun fem rækker med data: kun familier fra New Orleans og med 3 eller flere medlemmer. Nemt nok? Bemærk, at du nemt kan rydde et filter i en kolonne ved at klikke på rullemenuen og derefter klikke på ryd filteret Fra “Kolonnenavn” -linket.

klart filter excel

Så det handler om enkle filtre i Excel. De er meget lette at bruge, og resultaterne er ret ligetil. Lad os nu se på komplekse filtre ved hjælp af dialogboksen Avancerede filtre.

Opret avancerede filtre i Excel

Hvis du vil oprette mere avancerede filtre, skal du bruge dialogboksen Avanceret filter. Lad os for eksempel 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 en .EDU-e-mail-adresse. Nu kan du ikke gøre det med et simpelt filter.

For at gøre dette er vi nødt til at opsætte Excel-arket lidt anderledes. Gå videre og indsæt et par rækker over dit datasæt, og kopier overskriftetiketterne nøjagtigt i den første række som vist nedenfor.

avanceret filteropsætning

Nu er det her, hvordan avancerede filtre fungerer. Du skal først indtaste dine kriterier i kolonnerne øverst og derefter klikke på knappen Avanceret under Sorter & filter under fanen Data.

avanceret filterbånd

Så hvad kan vi præcist indtaste i disse celler? OK, så lad os starte med vores eksempel. Vi vil kun se data fra New Orleans eller Clarksville, så lad os indtaste dem i celler E2 og E3.

avanceret filterby

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.

avancerede filtre excel

Da> 2 og New Orleans er på samme række, vil det være en AND-operatør. Det samme gælder for række 3 ovenfor. Endelig vil vi kun have familier med .EDU-e-mail-adresse. For at gøre dette skal du bare indtaste * .edu i både D2 og D3. Symbolet * betyder ethvert antal tegn.

kriterier interval excel

Når du gør det, skal du klikke hvor som helst i dit datasæt og derefter klikke på knappen Avanceret. Feltet List Range finder automatisk ud dit datasæt, da du klikkede på det, før du klikker på knappen Avanceret. Klik nu på den lille lille knap til højre for knappen Kriterier.

Vælg kriterier

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!

filterresultater

Som du kan se, har jeg nu kun 3 resultater, der matcher alle disse kriterier. Bemærk, at etiketterne for kriterierne skal svare nøjagtigt til mærkaterne for datasættet for at dette kan fungere.

Du kan selvfølgelig oprette en meget mere kompliceret forespørgsel ved hjælp af denne metode, så leg rundt med den for at få dine ønskede resultater. Endelig, lad os tale om at anvende summeringsfunktioner på filtrerede data.

Resumé af filtrerede data

Lad os nu sige, at jeg vil opsummere antallet af familiemedlemmer på mine filtrerede data, hvordan ville jeg få det ved at gøre det? Nå, lad os rydde vores filter ved at klikke på knappen Ryd i båndet. Bare rolig, det er meget let at anvende det avancerede filter igen ved blot at klikke på knappen Avanceret og derefter klikke på OK igen.

ryd filter i excel

Nederst i vores datasæt, lad os tilføje en celle kaldet Total og derefter tilføje en sumfunktion for at opsummere det samlede familiemedlemmer. I mit eksempel skrev jeg lige = SUM (C7: C31).

summen excel

Så hvis jeg ser på alle familier, har jeg i alt 78 medlemmer. Lad os nu gå videre og genanvende vores avancerede filter og se, hvad der sker.

forkert totalfilter

Ups! I stedet for at vise det rigtige nummer 11, ser jeg stadig, at det samlede beløb er 78! Hvorfor det? Nå, SUM-funktionen ignorerer ikke skjulte rækker, så den 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. Inden vi bruger nogen af ​​disse specielle funktioner, skal du rydde dit filter og derefter indtaste funktionen.

Når filteret er ryddet, skal du gå videre og indtaste = SUBTOTAL (og du skal se en dropdown-boks vises med en masse muligheder. 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 vil indtaste tallet 9 eller bare klikke på det fra dropdown. Skriv derefter et komma, og vælg cellens rækkevidde.

subtotal funktion

Når du trykker på enter, skal du se, at værdien af ​​78 er den samme som tidligere. Men hvis du nu anvender filteret igen, vil vi se 11!

subtotal på filter

Fremragende! Det er præcis, hvad vi ønsker. Nu kan du justere dine filtre, og værdien afspejler altid kun de rækker, der vises i øjeblikket.

Den anden funktion, der fungerer stort set nøjagtigt det samme som SUBTOTAL-funktionen er AGGREGATE. Den eneste forskel er, at der er en anden parameter i AGGREGATE-funktionen, hvor du skal specificere, at du vil ignorere skjulte rækker.

aggregrate-funktion

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 indtaste 5 for at ignorere skjulte rækker. Den sidste parameter er den samme og er området for celler.

Du kan også læse min artikel om oversigtsfunktioner for at lære, hvordan du bruger AGGREGATE-funktionen og andre funktioner som MODE, MEDIAN, AVERAGE 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!