Slik filtrerer du data i Excel
Jeg har nylig skrevet en artikkel om hvordan du bruker sammendragsfunksjoner i Excel for å enkelt oppsummere store mengder data, men den artikkelen tok hensyn til alle dataene på regnearket. Hva om du bare vil se på en delmengde data og oppsummere delmengden av data?
I Excel kan du opprette filtre på kolonner som vil skjule rader som ikke samsvarer med filteret ditt. I tillegg kan du også bruke spesielle funksjoner i Excel for å oppsummere data ved å bruke bare de filtrerte dataene.
I denne artikkelen vil jeg gå gjennom trinnene for å lage filtre i Excel og også bruke innebygde funksjoner for å oppsummere de filtrerte dataene.
Lag enkle filtre i Excel
I Excel kan du lage enkle filtre og komplekse filtre. La oss starte med enkle filtre. Når du arbeider med filtre, bør du alltid ha en rad øverst som brukes til etiketter. Det er ikke et krav å ha denne raden, men det gjør det lettere å arbeide med filtre.
Over, jeg har noen falske data, og jeg vil lage et filter på By kolonne. I Excel er dette veldig enkelt å gjøre. Gå videre og klikk på Data tab i båndet og klikk deretter på Filter knapp. Du trenger ikke å velge dataene på arket eller klikke i den første raden heller.
Når du klikker på Filter, vil hver kolonne i første rad automatisk ha en liten rullegardin lagt til helt til høyre.
Gå nå og klikk på rullegardinpilen i kolonnen By. Du får se et par forskjellige alternativer, som jeg vil forklare nedenfor.
På toppen kan du raskt sortere alle rader med verdiene i kolonnen By. Merk at når du sorterer dataene, vil den bevege hele raden, ikke bare verdiene i kolonnen By. Dette vil sikre at dataene forblir intakt akkurat som det var før.
Det er også en god ide å legge til en kolonne på den aller fremste kalt ID og nummer det fra en til hvor mange rader du har i regnearket. På denne måten kan du alltid sortere etter ID-kolonnen og få dataene dine tilbake i samme rekkefølge som det var opprinnelig, hvis det er viktig for deg.
Som du ser, er alle dataene i regnearket nå sortert basert på verdiene i kolonnen By. Så langt er ingen rader gjemt. La oss nå se på avmerkingsboksene nederst i filterdialogboksen. I mitt eksempel har jeg bare tre unike verdier i kolonnen By, og de tre vises i listen.
Jeg gikk videre og ukontrollert to byer og forlot en sjekket. Nå har jeg bare 8 rader med data som vises og resten er skjult. Du kan enkelt fortelle deg at du ser på filtrerte data hvis du sjekker radnummerene til venstre. Avhengig av hvor mange rader som er skjult, ser du noen ekstra horisontale linjer, og fargen på tallene vil bli blå.
La oss si at jeg vil filtrere på en andre kolonne for å ytterligere redusere antall resultater. I kolonne C har jeg totalt antall medlemmer i hver familie, og jeg vil bare se resultatene for familier med mer enn to medlemmer.
Gå videre og klikk på rullegardinpilen i kolonne C, og du vil se de samme boksene for hver unik verdi i kolonnen. Men i dette tilfellet vil vi klikke på Nummerfiltre og klikk deretter på Større enn. Som du kan se, er det også en rekke andre alternativer.
En ny dialog vil dukke opp og her kan du skrive inn verdien for filteret. Du kan også legge til flere enn ett kriterium med en AND eller OR-funksjon. Du kan si at du vil ha rader der verdien er større enn 2 og ikke tilsvarer 5, for eksempel.
Nå er jeg nede til bare 5 rader med data: familier bare fra New Orleans og med 3 eller flere medlemmer. Enkelt nok? Merk at du enkelt kan fjerne et filter på en kolonne ved å klikke på rullegardinmenyen og deretter klikke på Tøm filter fra "kolonne navn" link.
Så det handler om enkle filtre i Excel. De er veldig enkle å bruke, og resultatene er ganske rett frem. La oss nå se på komplekse filtre ved hjelp av Avansert filter dialog.
Lag avanserte filtre i Excel
Hvis du vil lage mer avanserte filtre, må du bruke Avansert filterdialogboksen. For eksempel, la oss si at jeg ønsket å se alle familier som bor i New Orleans med mer enn 2 medlemmer i familien deres ELLER alle familier i Clarksville med mer enn 3 medlemmer i familien deres OG bare de med a .EDU slutt e-postadresse. Nå kan du ikke gjøre det med et enkelt filter.
For å gjøre dette må vi sette opp Excel-arket litt annerledes. Fortsett og sett inn et par rader over ditt sett med data og kopier overskriftetikettene nøyaktig inn i første rad som vist nedenfor.
Nå er det hvordan avanserte filtre fungerer. Du må først skrive inn kriteriene dine i kolonnene øverst og deretter klikke på Avansert knappen under Sorter og filtrer på Data tab.
Så hva kan vi skrive inn i disse cellene? OK, så la oss starte med vårt eksempel. Vi vil bare se data fra New Orleans eller Clarksville, så la oss skrive dem inn i celler E2 og E3.
Når du skriver inn verdier på forskjellige rader, betyr det OR. Nå ønsker vi New Orleans familier med mer enn to medlemmer og Clarksville familier med mer enn 3 medlemmer. For å gjøre dette, skriv inn > 2 i C2 og > 3 i C3.
Siden 2 og New Orleans ligger på samme rad, vil det være en AND-operatør. Det samme gjelder for rad 3 ovenfor. Endelig vil vi bare ha familier med .EDU slutter e-postadresse. For å gjøre dette, bare skriv inn * Edu inn i både D2 og D3. Symbolet * betyr et hvilket som helst antall tegn.
Når du har gjort det, klikker du hvor som helst i datasettet og klikker deretter på Avansert knapp. De Liste Range-feltet vil automatisk finne ut datasettet ditt siden du klikket på det før du klikker på Avansert-knappen. Klikk nå på den lille lille knappen til høyre for Kriterieområde knapp.
Velg alt fra A1 til E3 og klikk deretter på den samme knappen igjen for å komme tilbake til dialogboksen Avansert filter. Klikk på OK, og dataene dine skal nå filtreres!
Som du ser, har jeg bare 3 resultater som stemmer overens med alle disse kriteriene. Merk at etikettene for kriteriene må passe nøyaktig med etikettene for datasettet for at dette skal fungere.
Du kan selvsagt lage mange mer kompliserte spørringer ved hjelp av denne metoden, så spill deg med det for å få de ønskede resultatene. Til slutt, la oss snakke om å bruke summeringsfunksjoner til filtrerte data.
Oppsummering av filtrerte data
La oss nå si at jeg vil oppsummere antall familiemedlemmer på mine filtrerte data, hvordan ville jeg få det til å gjøre det? Vel, la oss fjerne filteret vårt ved å klikke på Klar knapp i båndet. Ikke bekymre deg, det er veldig enkelt å bruke det avanserte filteret igjen ved å bare klikke på Avansert-knappen og klikke på OK igjen.
På bunnen av datasettet legger vi til en celle som heter Total og legg deretter til en sum-funksjon for å oppsummere de totale familiemedlemmene. I mitt eksempel skrev jeg bare = SUM (C7: C31).
Så hvis jeg ser på alle familier, har jeg 78 medlemmer totalt. La oss nå gå videre og bruke vårt avanserte filter og se hva som skjer.
Uff! I stedet for å vise riktig nummer, 11, ser jeg fortsatt at alt er 78! Hvorfor det? Vel, SUM-funksjonen ignorerer ikke skjulte rader, så det gjør fortsatt beregningen med alle rader. Heldigvis finnes det et par funksjoner du kan bruke til å ignorere skjulte rader.
Den første er TOTAL. Før vi bruker noen av disse spesialfunksjonene, vil du slette filteret og deretter skrive inn funksjonen.
Når filteret er slettet, fortsett og skriv inn = SUBTOTAL ( og du bør se en rullegardinboks vises med en rekke alternativer. Ved å bruke denne funksjonen velger du først hvilken summasjonsfunksjon du vil bruke ved hjelp av et nummer.
I vårt eksempel vil jeg bruke SUM, så jeg ville skrive inn nummer 9 eller bare klikke på det fra rullegardinmenyen. Skriv deretter et komma og velg rekkefølge av celler.
Når du trykker på enter, bør du se verdien av 78 er den samme som tidligere. Men hvis du bruker filteret igjen, ser vi 11!
Utmerket! Det er akkurat det vi vil ha. Nå kan du justere filtrene dine og verdien vil alltid gjenspeile bare radene som vises.
Den andre funksjonen som fungerer ganske mye akkurat det samme som SUBTOTAL-funksjonen er AGGREGATE. Den eneste forskjellen er at det er en annen parameter i AGGREGATE-funksjonen der du må spesifisere at du vil ignorere skjulte rader.
Den første parameteren er summasjonsfunksjonen du vil bruke, og som med SUBTOTAL representerer 9 SUM-funksjonen. Det andre alternativet er hvor du må skrive inn 5 for å ignorere skjulte rader. Den siste parameteren er den samme og er rekkevidden av celler.
Du kan også lese artikkelen min på sammendragsfunksjoner for å lære hvordan du bruker AGGREGATE-funksjonen og andre funksjoner som MODE, MEDIAN, AVERAGE etc. i mer detalj.
Forhåpentligvis gir denne artikkelen deg et godt utgangspunkt for å lage og bruke filtre i Excel. Har du spørsmål, kan du legge inn en kommentar. Nyt!
