Jeg skrev nylig en artikkel om hvordan du bruker oppsummering funksjoner i Excel enkelt oppsummere store mengder data, men at artikkelen tok hensyn til alle data i regnearket. Hva om du bare ønsker å se på et delsett av data og oppsummere delsett av data?
I Excel kan du opprette filtre på kolonner som vil skjule rader som ikke samsvarer med filteret. I tillegg kan du også bruke spesielle funksjoner i Excel for å oppsummere dataene bare ved hjelp av filtrerte data.
I denne artikkelen, jeg vil lede deg gjennom trinnene for å opprette filtre i Excel, og også ved hjelp av innebygde funksjoner for å oppsummere det filtrerte data.
Lage 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 for etiketter. Det er ikke et krav for å ha denne raden, men det gjør arbeidet med filtre litt enklere.
Ovenfor har jeg noen falske data og jeg ønsker å opprette et filter på Byen kolonne. I Excel, dette er virkelig lett å gjøre. Gå videre og klikk på Data – kategorien på båndet, og deretter klikker du på Filter – knappen. Du trenger ikke å velge data på arket eller klikk på første rad.
Når du klikker på Filter, hver kolonne i den første raden, vil automatisk ha en liten dropdown-knappen lagt til helt til høyre.
Nå kan du gå videre og klikk på dropdown-pilen i Byen kolonne. Vil du se et par forskjellige alternativer, som jeg vil forklare nedenfor.
På toppen, du kan raskt sortere alle radene av de verdiene som er i Byen-kolonnen. Vær oppmerksom på at når du sorterer data, det vil flytte hele raden, ikke bare verdiene i Byen kolonne. Dette vil sikre at dine data er fortsatt intakt, akkurat som det var før.
Det er også en god idé å legge til en kolonne helt foran kalla ID og antall det fra en til men mange rader som du har i regnearket. På denne måten kan du alltid sorter etter ID-kolonnen, og få dataene tilbake i samme rekkefølge som det opprinnelig var, hvis det er viktig for deg.
Som du kan se alle dataene i regnearket er nå sortert basert på verdiene i Byen kolonne. Så langt, ingen rader er skjult. La oss nå ta en titt på avkrysningsrutene på bunnen av filter i dialogboksen. I mitt eksempel har jeg bare tre unike verdier i Byen kolonne og de tre dukke opp i listen.
Jeg gikk videre og ukontrollert to byer og venstre en kontrollert. Nå har jeg bare har 8 rader med data som viser og resten er skjult. Du kan enkelt fortelle deg er å se på filtrerte data hvis du sjekker rad tallene lengst til venstre. Avhengig av hvor mange rader er skjult, vil du se noen ekstra horisontale linjer og fargen på tallene skal være blå.
La oss nå si at jeg ønsker å filtrere på en annen kolonne for ytterligere å redusere antallet resultater. I kolonne C, jeg har det totale antall medlemmer i hver familie, og jeg vil bare se resultatene for familier med flere enn to medlemmer.
Gå videre og klikk på dropdown-pilen i Kolonne C og du vil se den samme rutene for hver av de unike verdien i kolonnen. Imidlertid, i dette tilfellet, vi ønsker å klikke på Antall Filtre og klikk deretter på mer Enn. Som du kan se, det er en haug av andre alternativer også.
En ny dialogboks vil dukke opp, og her kan du skrive inn verdien for filter. Du kan også legge til mer enn én kriterier med en OG eller ELLER funksjon. Du kan si at du vil rader der verdien er større enn 2, og ikke lik 5, for eksempel.
Nå er jeg ned 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å nedtrekksmenyen, og klikke på Fjern Filter Fra “Kolonne Navn” – koblingen.
Så det er om det for enkle filtre i Excel. De er veldig enkle å bruke og resultatene er ganske rett-fram. La oss nå ta en titt på komplekse filtre ved hjelp av Avanserte filtre dialog.
Opprett Avanserte Filtre i Excel
Hvis du ønsker å opprette mer avanserte filtre, må du bruke Avansert filter i dialogboksen. For eksempel, la oss si at jeg ønsket å se alle familier som lever i New Orleans, med mer enn 2 medlemmer i deres familie ELLER alle familier i Clarksville med mer enn 3 medlemmer i familien sin OG kun de som er med .EDU slutter e-postadressen. Nå kan du ikke gjøre det med et enkelt filter.
For å gjøre dette, vi trenger å sette opp Excel-ark litt annerledes. Gå videre og sette inn et par rader over dine egne data og kopiere overskriften etiketter nøyaktig inn i den første raden som vist nedenfor.
Nå her er hvordan avanserte filtre arbeid. Du må først skrive inn kriterier i kolonner på toppen, og deretter klikker du Avansert – knappen under Sortere Og Filtrere på Data – fanen.
Så hva kan vi skriver inn i celler? OK, så la oss starte med vårt eksempel. Vi bare ønsker å se data fra New Orleans eller Clarksville, så la oss skrive disse inn i cellene E2 og E3.
Når du skriver inn verdier på ulike rader, det betyr at du ELLER. Nå ønsker vi New Orleans familier med flere enn to medlemmer og Clarksville familier med mer enn 3 medlemmer. For å gjøre dette, skriv i >2 i C2 og >3 i C3.
Siden >2 og New Orleans er på samme rad, vil det være en OG operatør. Det samme er sant for rad 3 ovenfor. Til slutt ønsker vi bare familier med .EDU slutter e-postadressen. For å gjøre dette, bare skriv inn *.edu i både D2 og D3. Symbolet * betyr hvilket som helst antall tegn.
Når du gjør det, klikker du hvor som helst i datasettet og klikk deretter på Avansert – knappen. Den Listen Ringtee-feltet vil automatisk regne ut din dataset siden du klikket inn i det før du klikker på knappen Avansert. Nå kan du klikke på den lille lille knappen til høyre for vilkårsområdet – knappen.
Velg alt fra A1 til E3, og klikk deretter på den samme knappen igjen for å komme tilbake til Avansert Filter i dialogboksen. Klikk på OK, og dataene skal nå være filtrert!
Som du kan se, nå har jeg bare 3 resultater som oppfyller alle disse kriteriene. Vær oppmerksom på at etiketter for de kriteriene utvalget har for nøyaktig å matche med etiketter for datasettet for å få dette til å fungere.
Du kan selvsagt lage et mye mer komplisert spørsmål ved hjelp av denne metoden, så spille rundt med dette for å få de ønskede resultatene. Til slutt, la oss snakke om søker summering funksjoner for å filtrerte data.
Oppsummering Filtrerte Data
La oss nå si at jeg ønsker å oppsummere antall familiemedlemmer på min filtrerte data, hvordan ville jeg fikk om å gjøre det? Vel, la oss fjerne våre filter ved å klikke på Slett – knappen på båndet. Ikke bekymre deg, det er veldig enkelt å bruke det avanserte filteret igjen ved å klikke på Avansert-knappen og klikke på OK igjen.
Nederst i vårt datasett, la oss legge til en celle kalt Total og deretter legge til en sum-funksjonen for å oppsummere den totale familiemedlemmer. I mitt eksempel, jeg bare skrev =SUM(C7:C31).
Så hvis jeg ser på alle familier, jeg har 78 medlemmer totalt. La oss nå gå videre og bruke vår Avanserte filteret og se hva som skjer.
Whoops! I stedet for å vise det riktige tallet, 11, jeg ser fremdeles totalt er 78! Hvorfor er det slik? Vel, summer-funksjonen ikke ignorere skjulte rader, så det er fortsatt gjør en beregning med alle radene. Heldigvis, det er et par av funksjonene du kan bruke til å ignorere skjulte rader.
Den første er DELSUM. Før vi bruker noen av disse spesielle funksjoner, vil du ønsker å fjerne filteret, og skriv deretter inn i funksjonen.
Når filteret er merket av, gå videre og skriv inn =DELSUM( og du bør se en dropdown boks vises med en haug av alternativer. Ved hjelp av denne funksjonen, må du først velge type summering funksjonen du ønsker å bruke ved hjelp av et nummer.
I vårt eksempel, jeg vil bruke SUMMEN, så jeg ville skrive i antall 9 eller bare klikk på den fra rullegardinmenyen. Deretter skriver du inn et komma og velg celleområdet.
Når du trykker på enter, bør du se verdien av 78 er det samme som tidligere. Imidlertid, hvis du nå bruke filteret igjen, vil vi se 11!
Utmerket! Det er akkurat hva vi ønsker. Nå kan du justere filtrene og verdien vil alltid gjenspeiler bare de radene som er for tiden vise.
Den andre funksjonen som fungerer ganske mye nøyaktig den samme som funksjonen DELSUM er SAMLET. Den eneste forskjellen er at det er en annen parameter i den SAMLEDE funksjonen der du må spesifisere at du ønsker å ignorere skjulte rader.
Den første parameteren er summering funksjon du vil bruke, og som med DELSUM, 9 representerer SUMMEN funksjon. Det andre alternativet er der du må skrive inn 5 for å ignorere skjulte rader. Den siste parameteren er den samme og er omfanget av celler.
Du kan også lese min artikkel om oppsummering funksjoner for å lære hvordan å bruke den SAMLEDE funksjon og andre funksjoner som MODUS, MEDIAN, GJENNOMSNITT, etc. i mer detalj.
Forhåpentligvis vil denne artikkelen gir deg et godt utgangspunkt for å skape og bruke filter i Excel. Hvis du har noen spørsmål, feel free til å legge inn en kommentar. Nyt!