Använda Advanced Filter i Excel för att lösa Morts problem. Även om vanliga filter har blivit kraftigare, finns det fortfarande tillfällen att Advanced Filter kan göra några knep som andra inte kan.
Kolla på video
- Avancerat filter är mer "avancerat" än vanligt filter eftersom:
- 1) Den kan kopiera till ett nytt intervall
- 2) Du kan bygga mer komplexa kriterier som fält 1 = A eller fält 2 = A
- 3) Det är snabbt
- Mort försöker bearbeta 100 000 rader i VBA genom att gå igenom poster eller använda en matris
- Det kommer alltid att vara snabbare att använda inbyggda Excel-funktioner än att skriva din egen kod.
- Du behöver ett inmatningsområde och sedan ett kriterieområde och / eller ett utmatningsområde
- För inmatningsområdet: en rad av rubriker ovanför data
- Lägg till en tillfällig rad för rubriker
- För utdataområdet: en rad rubriker för de kolumner du vill extrahera
- För kriterieområdet: rubriker i rad 1, värden som börjar i rad 2
- Komplikation: Äldre versioner av Excel tillåter inte att utmatningsområdet ligger på ett annat ark
- Om du skriver ett makro som kan köras 2003, använd ett angivet intervall för inmatningsområdet för att kringgå
Videoutskrift
Lär dig Excel från Podcast, avsnitt 2060: Excel Advanced Filter
Hej, välkommen tillbaka till netcast, jag heter Bill Jelen. Dagens fråga skickad in av Mort. Mort, han har 100 000 rader med data och han är intresserad av kolumnerna A, B och D där kolumn C matchar ett visst år. Så han vill att en person ska gå in i ett år och sedan få kolumnerna A, B och D. Och Mort har lite VBA där han använder arrays för att göra detta och jag sa, "Vänta en sekund, du vet, det avancerade filtret skulle göra det här en mycket bättre. ” Okej, och nu bara för att granska, jag gick tillbaka, jag tittade tillbaka genom mina videor. Jag har inte täckt avancerat filter på länge så vi borde prata om det här.
Avancerat filter kräver ett inmatningsområde och sedan minst ett av dessa: ett kriterieområde eller ett utmatningsområde. Även om vi idag ska använda båda dessa. Okej, så inmatningsområdet är dina data och du måste ha rubriker ovanför data. Så, Mort har inga rubriker och jag ska tillfälligt infoga en rad här uppe och göra precis som fält 1. Mort vet vad hans data är och så att han kunde lägga riktiga rubriker där uppe. Och vi använder ingenting som kallas - dessa data i kolumnerna E till O, så jag behöver inte lägga till rubriker där, okej? Så nu, A1 till D, blir 100000 mitt inmatningsområde. Och sedan utmatningsområdet och kriterieområdet - Tja, utmatningsområdet är bara en lista med de rubriker du vill ha. Så jag kommer att lägga utgångsområdet här och vi behöver inte fält 3 så jag 'Jag tar det bara åt sidan. Så nu, detta intervall här, blir A1 till C1 mitt utdataområde som berättar för Excel vilka fält jag vill ha från inmatningsområdet. Och de kan vara i en annan ordning om du vill ordna om sakerna, som om jag vill ha fält 4 först och sedan fält 1 och sedan fält 2. Och igen skulle det vara riktiga rubriker som fakturanummer. Jag vet bara inte hur Morts data ser ut.
Och sedan är kriterieområdet en rubrik och vilket värde du vill ha. Så låt oss säga att jag försökte få någonting 2014. Detta blir kriterieområdet så. Okej, bara ett försiktighetsord här. Jag är i Excel 2016 och det är möjligt att göra ett avancerat filter mellan två ark i Excel 2016 men om du går långt tillbaka, och jag kommer inte ihåg vilken väg tillbaka är, kanske 2003, är jag inte säker. Någon gång i det förflutna brukade det vara så att du inte kunde göra ett avancerat filter från ett ark till ett annat ark så att du måste komma hit och namnge ditt inmatningsområde. Du måste skapa ett namn här. MyName eller något liknande, okej? Och det skulle vara så att du skulle kunna dra av det här, okej. Inte nödvändigtvis i Excel 2016 men igen, jag 'Jag är inte säker på om Mort kommer att köra detta i äldre versioner av data.
Okej, så här på Data går vi till Advanced Filter, okej. Och vi ska kopiera till en annan plats som möjliggör vårt produktionsområde där. Okej, så listområdet, var är data? Eftersom jag är i Excel 2016 kommer jag att peka på Data istället för att använda namnområdet - Så det är mitt inmatningsområde. Kriterierna är de cellerna där och då, där vi ska - output till, kommer det bara att vara dessa tre celler där. Och sedan klickar vi på OK. Okej, och BAM! Så snabbt, snabbt är det. Och tänk om vi ville ha ett annat år? Om vi ville ha ett annat år skulle vi ta bort resultaten, lägga in 2015 och sedan göra ett avancerat filter igen, Kopiera till en annan plats, klicka på OK och det finns alla 2015-poster. Blixtsnabb.
Okej nu, medan jag är ett fan av avancerat filter i vanligt Excel, var jag ett enormt fan av avancerat filter i VBA, okej, för VBA gör förskottsfilter riktigt, riktigt, riktigt enkelt. Okej, så vi kommer att skriva lite kod här för Mort, förutsatt att Morts data inte har några rubriker och vi måste tillfälligt lägga till rubrikerna, okej? Så jag byter till VBA, Alt + F11 och vi kommer att köra detta från kalkylbladet som har data. Så: Dim WS som kalkylblad, Ställ in WS = ActiveSheet. Och sedan infoga rad 1 och lägg bara till några rubriker: A, B, år och D. Ta reda på hur många rader med data vi har idag och börja sedan från cell A1 och gå ut 4 kolumner ner till den sista raden, namnge det till vara ingångsområde. Okej, och då är detta faktiskt Morts kod här, där han bad om InputBox,får året de vill ha och sedan frågar han vilket år eller vad de vill namnge det nya arket, okej. Så det kommer faktiskt att infoga ett ark på Fly och sedan I- dimensionera ett nytt ark, WSN, som ActiveSheet. Så jag vet att WS är originalarket, WSN är det nya bladet som just har lagts till. På det nya arket, lägg kriterierna så under kolumn E finns rubriken som matchar denna rubrik här, och sedan, vilket svar de gav oss går i E2. Utdataområdet kommer att bli mina andra tre rubriker: A, B och D. Och igen, om du eller Mort ändrar dessa till riktiga rubriker är det förmodligen en bättre sak att göra än A, B, D, och du skulle också ändra dessa till riktiga rubriker, okej? Så allt detta är bara lite förarbete här. Denna fantastiska kodrad kommer att göra hela det avancerade filtret. Så,från InputRange gör vi ett AdvancedFilter, vi ska kopiera. Det är vårt valfilter på plats eller kopiera. CriteriaRange är E1 till E2, CopyToRange är A till C. Unika värden - Nej, vi vill ha alla värden. Okej, den ena raden kod där gör all den magi som går igenom alla poster eller ersätter att slinga igenom alla poster eller göra arrayer. Och sedan är vi klara, vi rensar kriterieområdet och tar sedan bort rad 1 tillbaka i det ursprungliga kalkylbladet.Och sedan är vi klara, vi kommer att rensa kriterieområdet och sedan radera rad 1 tillbaka i det ursprungliga kalkylbladet.Och sedan är vi klara, vi rensar kriterieområdet och tar sedan bort rad 1 tillbaka i det ursprungliga kalkylbladet.
Okej, så låt oss växla tillbaka hit till våra data. Vi kommer att göra det enkelt att köra detta, så: Infoga, en form och kalla detta filter, hem, mitt, mitt, större, större, större, högerklicka, tilldela makro och tilldela det till MacroForMort. Okej, så här går vi. Vi ska göra ett test. Se att vi är på databladet, klicka på Filter, vilket år vill vi ha? Vi vill ha 2015. Vad vill jag kalla det? Jag vill kalla det 2015, okej. Och BAM! Där är det gjort. Så snabbt, det är så snabbt det här är.
Nu, eftersom Morts ursprungliga data inte hade rubriker, kanske dessa uppgifter inte borde ha rubriker. Så låt oss gå Alt + F11, här vill vi rensa kriterieområdet. Vi kommer också att rad (1). Radera. Okej, så nästa gång vi var på det här kommer det att bli av med dessa rubriker. Och låt oss bara - Snarare än att köra hela saken snabbt, låt oss ta en titt här med 2014. Så jag väljer en cell på Data, Alt + F11, och jag vill springa bara ner till den punkt där vi gör avancerat filter. Så vi kan titta och se vad hela makrot gör här. Så vi klickar på Kör och jag vill få 2014. 2014, okej. Och så, tryck på F8, vi håller på att göra det avancerade filtret. Vi kan rulla tillbaka till Excel här och se vad som har hänt.
Det första som har hänt- Nu, det första som har hänt är att vi har lagt till en ny tillfällig rad med rubrikerna. Infogade detta kalkylblad, byggde ett kriterieområde med en rubrik och vilket år de matade in, valde de fält som vi vill göra och sedan tillbaka i VBA, jag kör nästa rad med koder, det är F8 som gör det avancerade filtret precis där . Det är otroligt snabbt och du kommer att se att det faktiskt nu har gett oss alla poster. Därifrån är det bara lite sanering, ta bort det här, ta bort det här. Jag går tillbaka till data och raderar rad 1 så är vi klara. Så jag låter bara resten av det springa, tar bort brytpunkten, okej? Så det finns VBA. För mig är det här jag tror det snabbaste vägen, snabbaste vägen att gå.
Okej, avsnitt sammanfattning: Det avancerade filtret är mer avancerat än det vanliga filtret eftersom det kan kopiera till ett nytt intervall. Och nu visade jag inte det i den här videon men du kan bygga komplexa kriterier där fält 1 = A eller fält 2 = A. Det vanliga automatiska filtret kan inte göra det och det är snabbt. Mort försöker bearbeta 100.000 rader i VBA med hjälp av en array eller genom looping, men det kommer alltid att vara snabbare att använda Excel-byggfunktioner än att skriva din egen kod. Du måste definiera ett inmatningsområde, kriterieområde, utmatningsområde. Du behöver alltid ett inmatningsområde i minst en av dessa, även om jag idag använder båda. För inmatningsområdet, en rad av rubriker ovanför data. Så vi ska lägga till en tillfällig rad med rubriker. För utdataområdet, samma rubriker som du vill extrahera, okej. Så du vet, om det var A, B,År och D, vi sätter bara A, B och D som utgångsområde. För kriterieområdet, rubriker i rad 1. Så det här är det fält jag vill bygga ett kriterium på och detta är det värde jag letar efter. Komplikationer: Äldre versioner av Excel tillåter inte att utmatningsområdet ligger på ett annat ark, så din kod kommer eventuellt att köras då. Du vill använda ett namngivet intervall för ingångsområdet, för från det här arket, vet du, det namngivna området, även om det är på ett annat ark, tror arket att namnet förgrenas på det aktuella arket. Så det skulle göra det möjligt för det avancerade filtret att fungera.Äldre versioner av Excel tillåter inte att utmatningsområdet ligger på ett annat ark, så potentiellt kommer din kod att köras då. Du vill använda ett namngivet intervall för ingångsområdet, för från det här arket, vet du, det namngivna området, även om det är på ett annat ark, tror arket att namnet förgrenas på det aktuella arket. Så det skulle göra det möjligt för det avancerade filtret att fungera.Äldre versioner av Excel tillåter inte att utmatningsområdet ligger på ett annat ark, så potentiellt kommer din kod att köras då. Du vill använda ett namngivet intervall för ingångsområdet, för från det här arket, vet du, det namngivna området, även om det är på ett annat ark, tror arket att namnet förgrenas på det aktuella arket. Så det skulle göra det möjligt för det avancerade filtret att fungera.
Okej, ja, där har du det. Jag vill tacka Mort för att skicka in den frågan. Jag vill tacka dig för att du kom förbi. Vi ses nästa gång för en ny netcast från.
Nedladdning fil
Ladda ner exempelfilen här: Podcast2060.xlsm
