Så här filtrerar du data i Excel
Jag skrev nyligen en artikel om hur man använder sammanfattande funktioner i Excel för att enkelt sammanfatta stora mängder data, men den artikeln tog hänsyn till alla data på arbetsbladet. Vad händer om du bara vill titta på en delmängd data och sammanfatta delmängden data?
I Excel kan du skapa filter i kolumner som kommer att gömma rader som inte matchar ditt filter. Dessutom kan du också använda speciella funktioner i Excel för att sammanfatta data med endast den filtrerade data.
I den här artikeln går jag igenom stegen för att skapa filter i Excel och även med inbyggda funktioner för att sammanfatta den filtrerade data.
Skapa enkla filter i Excel
I Excel kan du skapa enkla filter och komplexa filter. Låt oss börja med enkla filter. När du arbetar med filter ska du alltid ha en rad överst som används för etiketter. Det är inte ett krav att ha den här raden, men det gör det lättare att arbeta med filter.
Ovan har jag några falska data och jag vill skapa ett filter på Stad kolumn. I Excel är det här väldigt enkelt att göra. Fortsätt och klicka på Data fliken i bandet och klicka sedan på Filtrera knapp. Du behöver inte välja data på arket eller klicka på den första raden heller.
När du klickar på Filter kommer varje kolumn i första raden automatiskt att ha en liten nedrullningsknapp till höger.
Fortsätt nu och klicka på rullgardinsmenyn i kolumnen Stad. Du får se ett par olika alternativ, som jag kommer att förklara nedan.
Överst kan du snabbt sortera alla rader med värdena i kolumnen Stad. Observera att när du sorterar data kommer den att flytta hela raden, inte bara värdena i kolumnen Stad. Detta kommer att se till att dina data förblir intakta precis som tidigare.
Det är också en bra idé att lägga till en kolumn på framsidan som heter ID och numrera det från en till hur många rader du har i ditt arbetsblad. På så sätt kan du alltid sortera efter ID-kolumnen och få tillbaka dina data i samma ordning som det var ursprungligen, om det är viktigt för dig.
Som du kan se, sorteras alla data i kalkylbladet nu baserat på värdena i kolumnen Stad. Hittills är inga rader dolda. Låt oss nu ta en titt på kryssrutorna längst ner i filterdialogrutan. I mitt exempel har jag bara tre unika värden i kolumnen Stad och de tre visas i listan.
Jag gick vidare och avmarkerade två städer och lämnade en kontrollerad. Nu har jag bara 8 rader med data som visar och resten är gömda. Du kan enkelt berätta att du tittar på filtrerade data om du kontrollerar radenummer längst till vänster. Beroende på hur många rader som är dolda ser du några extra horisontella linjer och färgen på siffrorna blir blå.
Låt oss nu säga att jag vill filtrera på en andra kolumn för att ytterligare minska antalet resultat. I kolumn C har jag totalt antal medlemmar i varje familj och jag vill bara se resultaten för familjer med mer än två medlemmar.
Fortsätt och klicka på rullgardinsmenyn i kolumn C och du kommer att se samma kryssrutor för varje unikt värde i kolumnen. Men i det här fallet vill vi klicka på Nummerfilter och klicka sedan på Större än. Som du kan se finns det en massa andra alternativ också.
En ny dialogruta kommer dyka upp och här kan du skriva in värdet för filtret. Du kan också lägga till mer än ett kriterium med en AND eller OR-funktion. Du kan säga att du vill ha rader där värdet är större än 2 och inte lika med 5, till exempel.
Nu går jag ner till bara 5 rader data: endast familjer från New Orleans och med 3 eller fler medlemmar. Lätt nog? Observera att du enkelt kan rensa ett filter på en kolumn genom att klicka på rullgardinsmenyn och sedan klicka på Rensa filtret från "kolumnnamn" länk.
Så det handlar om enkla filter i Excel. De är mycket lätta att använda och resultaten är ganska rakt framåt. Låt oss nu titta på komplexa filter med hjälp av Avancerad filterdialogrutan.
Skapa avancerade filter i Excel
Om du vill skapa mer avancerade filter måste du använda Avancerad filterdialogrutan. Till exempel, låt oss säga att jag ville se alla familjer som bor i New Orleans med mer än 2 medlemmar i deras familj ELLER alla familjer i Clarksville med mer än 3 medlemmar i sin familj OCH bara de med a .EDU slutar e-postadressen. Nu kan du inte göra det med ett enkelt filter.
För att göra detta måste vi konfigurera Excel-arket lite annorlunda. Fortsätt och sätt in ett par rader ovanför din uppsättning data och kopiera rubriketiketterna exakt i första raden som visas nedan.
Nu är det hur avancerade filter fungerar. Du måste först skriva dina kriterier i kolumnerna längst upp och klicka sedan på Avancerad knapp under Sortera och filtrera på Data flik.
Så vad exakt kan vi skriva in i dessa celler? OK, så låt oss börja med vårt exempel. Vi vill bara se data från New Orleans eller Clarksville, så låt oss skriva in dem i cellerna E2 och E3.
När du skriver värden i olika rader betyder det OR. Nu vill vi New Orleans familjer med mer än två medlemmar och Clarksville familjer med mer än 3 medlemmar. För att göra detta, skriv in > 2 i C2 och > 3 i C3.
Eftersom> 2 och New Orleans ligger på samma rad, blir det en AND-operatör. Detsamma gäller för rad 3 ovan. Slutligen vill vi bara familjerna med .EDU sluta e-postadress. För att göra detta, skriv bara in * .edu in i både D2 och D3. Symbolen * betyder vilket antal tecken som helst.
När du har gjort det, klicka någonstans i ditt dataset och klicka sedan på Avancerad knapp. De Lista Range-fältet kommer automatiskt att räkna ut ditt dataset sedan du klickade på det innan du klickade på knappen Avancerat. Klicka nu på den lilla lilla knappen till höger om Kriterierna knapp.
Välj allt från A1 till E3 och klicka sedan på samma knapp igen för att komma tillbaka till dialogrutan Avancerad filter. Klicka på OK och dina data ska nu filtreras!
Som du kan se har jag bara 3 resultat som matchar alla dessa kriterier. Observera att etiketterna för kriterierna måste matcha exakt med etiketterna för datasetet för att detta skall fungera.
Du kan självklart skapa mycket mer komplicerade frågor med hjälp av den här metoden, så lek med det för att få de önskade resultaten. Slutligen, låt oss prata om att använda summeringsfunktioner till filtrerad data.
Sammanfattar filtrerad data
Låt oss nu säga att jag vill summera antalet familjemedlemmar på min filtrerade data, hur skulle jag få det att göra det? Tja, låt oss rensa vårt filter genom att klicka på Klar knapp i bandet. Oroa dig inte, det är väldigt enkelt att använda det avancerade filtret igen genom att helt enkelt klicka på knappen Avancerat och klicka på OK igen.
Längst ner i vårt dataset låt vi lägga till en cell som heter Total och lägg sedan till en summafunktion för att sammanfatta de totala familjemedlemmarna. I mitt exempel skrev jag bara = SUMMA (C7: C31).
Så om jag tittar på alla familjer har jag 78 medlemmar totalt. Låt oss nu gå vidare och återanvända vårt avancerade filter och se vad som händer.
Oj då! Istället för att visa rätt nummer, 11, ser jag fortfarande totalt 78! Varför är det så? Tja, SUM-funktionen ignorerar inte dolda rader, så det gör fortfarande beräkningen med alla rader. Lyckligtvis finns det ett par funktioner du kan använda för att ignorera dolda rader.
Den första är DELSUMMA. Innan vi använder någon av dessa speciella funktioner, vill du rensa ditt filter och sedan skriva in funktionen.
När filtret är klart, fortsätt och skriv in = SUBTOTAL ( och du bör se en rullgardinsmeny visas med en massa alternativ. Med den här funktionen väljer du först typen av summeringsfunktion som du vill använda med ett nummer.
I vårt exempel vill jag använda SUMMA, så jag skulle skriva in numret 9 eller bara klicka på det från rullgardinsmenyn. Skriv sedan ett komma och välj cellintervallet.
När du trycker på enter bör du se att värdet på 78 är detsamma som tidigare. Om du nu använder filtret igen ser vi 11!
Excellent! Det är precis vad vi vill ha. Nu kan du justera dina filter och värdet kommer alltid att återspegla endast de rader som visas.
Den andra funktionen som fungerar ungefär exakt samma som SUBTOTAL-funktionen är SUMMA. Den enda skillnaden är att det finns en annan parameter i AGGREGATE-funktionen där du måste ange att du vill ignorera dolda rader.
Den första parametern är summationsfunktionen som du vill använda och som med SUBTOTAL representerar 9 SUM-funktionen. Det andra alternativet är där du måste skriva in 5 för att ignorera dolda rader. Den sista parametern är densamma och är cellintervallet.
Du kan också läsa min artikel om sammanfattande funktioner för att lära dig hur du använder AGGREGATE-funktionen och andra funktioner som MODE, MEDIAN, GEMENSKAP, etc. i mer detalj.
Förhoppningsvis ger den här artikeln en bra utgångspunkt för att skapa och använda filter i Excel. Om du har några frågor, skriv gärna en kommentar. Njut av!
