Bruke Advanced Filter i Excel for å løse Morts problem. Selv om vanlige filtre har blitt kraftigere, er det fortsatt tider at Advanced Filter kan gjøre noen triks som andre ikke kan.
Se på video
- Avansert filter er mer "avansert" enn vanlig filter fordi:
- 1) Den kan kopiere til et nytt område
- 2) Du kan bygge mer komplekse kriterier som felt 1 = A eller felt 2 = A
- 3) Det er raskt
- Mort prøver å behandle 100 000 rader i VBA ved å gå gjennom poster eller bruke en matrise
- Det vil alltid være raskere å bruke innebygde Excel-funksjoner enn å skrive din egen kode.
- Du trenger et inngangsområde, og deretter et kriterieområde og / eller et utgangsområde
- For inngangsområdet: enkelt rad med overskrifter over dataene
- Legg til en midlertidig rad for overskrifter
- For utgangsområdet: en rad med overskrifter for kolonnene du vil trekke ut
- For kriterieområdet: overskrifter i rad 1, verdier som starter i rad 2
- Komplikasjon: Eldre versjoner av Excel tillater ikke at utskriftsområdet ligger på et annet ark
- Hvis du skriver en makro som kan kjøres i 2003, bruker du et navngitt område for inngangsområde for å omgå
Videoutskrift
Lær Excel fra Podcast, Episode 2060: Excel Advanced Filter
Hei, velkommen tilbake til netcast, jeg er Bill Jelen. Dagens spørsmål sendt inn av Mort. Mort, han har 100.000 rader med data, og han er interessert i kolonnene A, B og D der kolonne C samsvarer med et bestemt år. Så han vil at en person skal gå inn i et år og deretter få kolonnene A, B og D. Og Mort har litt VBA der han bruker arrays for å gjøre dette, og jeg sa: "Vent et sekund, du vet, det avanserte filteret ville gjort dette en mye bedre. ” Greit, og nå bare for å gjennomgå, gikk jeg tilbake, jeg så tilbake gjennom videoene mine. Jeg har ikke dekket avansert filter på lenge, så vi bør snakke om dette.
Avansert filter krever et inngangsområde og deretter minst ett av disse: et kriterieområde eller et utgangsområde. Selv om vi i dag skal bruke begge disse. Greit, så inngangsområdet er dataene dine, og du må ha overskrifter over dataene. Så, Mort har ikke overskrifter, og derfor skal jeg midlertidig sette inn en rad her oppe og gjøre akkurat som felt 1. Mort vet hva dataene hans er, og så kan han sette reelle overskrifter der oppe. Og vi bruker ikke noe som heter disse dataene i kolonnene E til O, så jeg trenger ikke legge til overskrifter der, ok? Så nå blir A1000 til D mitt inngangsområde. Og så utgangsområdet og kriterieområdet - Vel, utgangsområdet er bare en liste over overskriftene du vil ha. Så jeg skal sette utgangsområdet her, og vi trenger ikke felt 3 så jeg 'Jeg tar bare det til siden. Så nå, dette området akkurat her, blir A1 til C1 mitt utgangsområde som forteller Excel hvilke felt jeg vil ha fra inndataområdet. Og de kan være i en annen rekkefølge hvis du vil omorganisere tingene, som om jeg vil ha felt 4 først, og deretter felt 1 og deretter felt 2. Og igjen, dette vil være reelle overskrifter som fakturanummer. Jeg vet bare ikke hvordan dataene til Mort ser ut.
Og så er kriterieområdet en overskrift og hvilken verdi du vil ha. La oss si at jeg prøvde å få tak i noe i 2014. Dette blir kriterieområdet slik. Greit, bare et ord med forsiktighet her. Jeg er i Excel 2016, og det er mulig å gjøre et avansert filter mellom to ark i Excel 2016, men hvis du går langt tilbake, og jeg ikke husker hva veien tilbake er, kanskje 2003, er jeg ikke sikker. På et tidspunkt tidligere, pleide det å være at du ikke kunne gjøre et avansert filter fra ett ark til et annet ark, så du måtte komme hit og gi navnet ditt inndataområde. Du må opprette et navn her. MyName eller noe sånt, ok? Og det er slik du vil være i stand til å trekke dette, ok. Ikke nødvendigvis i Excel 2016, men igjen, jeg 'Jeg er ikke sikker på om Mort kommer til å kjøre dette i eldre versjoner av dataene.
OK, så tilbake til Data, går vi til Advanced Filter, ok. Og vi skal kopiere til et annet sted som muliggjør utgangsområdet vårt der. Greit, så listeområdet, hvor er dataene? Fordi jeg er i Excel 2016, skal jeg peke på Data, i stedet for å bruke navnområdet - Så det er mitt inngangsområde. Kriterieområdet er de cellene der og da, hvor vi skal - output til, det kommer bare til å være disse tre cellene der. Og så klikker vi OK. Greit, og BAM! Så raskt, raskt er det. Og hva om vi ønsket et annet år? Hvis vi ønsket et annet år, ville vi slette resultatene, legge inn 2015, og deretter gjøre et avansert filter igjen, Kopier til et annet sted, klikk OK og det er alle 2015-postene. Lynrask.
Greit nå, mens jeg er en fan av avansert filter i vanlig Excel, var jeg en enorm fan av avansert filter i VBA, ok, fordi VBA gjør forhåndsfilter veldig, veldig, veldig enkelt. Greit, så vi kommer til å skrive litt kode her for Mort, forutsatt at Morts data ikke har noen overskrifter, og vi må midlertidig legge til overskriftene, ok? Så jeg bytter til VBA, Alt + F11, og vi skal kjøre dette fra regnearket som har dataene. Så: Dim WS som regneark, sett WS = ActiveSheet. Sett deretter inn rad 1 og bare legg til noen overskrifter: A, B, år og D. Finn ut hvor mange rader med data vi har i dag, og start deretter fra celle A1 og gå ut 4 kolonner ned til den siste raden, navn det til være inngangsområde. Greit, og så er dette faktisk Morts kode akkurat her, hvor han ba om InputBox,får året de vil ha, og så spør han hvilket år eller hva de vil gi navnet til det nye arket, ok. Så det kommer til å faktisk sette inn et ark på Fly, og deretter dimensjonerer jeg et nytt ark, WSN, som ActiveSheet. Så jeg vet at WS er det originale arket, WSN er det nye arket som nettopp ble lagt til. På det nye arket, legg kriterieområdet så under Kolonne E er det overskriften som samsvarer med denne overskriften her, og deretter, det svaret de ga oss, går i E2. Produksjonsområdet vil være de tre andre overskriftene mine: A, B og D. Og igjen, hvis du eller Mort endrer disse til ekte overskrifter, er det sannsynligvis en bedre ting å gjøre enn A, B, D, og du vil også endre disse til ekte overskrifter, ok? Så alt dette er bare litt forarbeid her. Denne fantastiske kodelinjen vil gjøre hele det avanserte filteret. Så,fra InputRange gjør vi et AdvancedFilter, vi skal kopiere. Det er vårt valgfilter på plass eller kopier. CriteriaRange er E1 til E2, CopyToRange er A til C. Unike verdier -Nei, vi vil ha alle verdiene. OK, den ene linjen med kode der gjør all magien med å gå gjennom alle postene eller erstatter å gå gjennom alle postene eller gjøre matriser. Og så er vi ferdige, vi vil fjerne kriterieområdet og deretter slette rad 1 tilbake på det opprinnelige regnearket.Og så er vi ferdige, vi vil fjerne kriterieområdet og deretter slette rad 1 tilbake på det opprinnelige regnearket.Og så er vi ferdige, vi vil fjerne kriterieområdet og deretter slette rad 1 tilbake på det opprinnelige regnearket.
Ok, så la oss bytte tilbake hit til dataene våre. Vi vil gjøre det enkelt å kjøre dette, så: Sett inn, en form og kaller dette Filter, Hjem, Senter, Senter, Større, Større, Større, høyreklikk, Tildel makro, og tilordne det til MacroForMort. Ok, så her går vi. Vi skal gjøre en test. Se at vi er på databladet, klikk på Filter, hvilket år vil vi ha? Vi vil ha 2015. Hva vil jeg kalle det? Jeg vil kalle det 2015, ok. Og BAM! Der er det gjort. Det er så fort, det er så raskt dette er.
Nå, siden Morts originale data ikke hadde overskrifter, burde disse dataene kanskje ikke ha overskrifter. Så la oss gå med Alt + F11, akkurat her vil vi fjerne kriteriene. Vi vil også Rader (1). Slett. Greit, så neste gang vi var på dette, vil det bli kvitt disse overskriftene. Og la oss bare - I stedet for å kjøre det hele raskt, la oss ta en titt her med 2014. Så jeg velger en celle på Data, Alt + F11, og jeg vil løpe bare ned til det punktet hvor vi gjør avansert filter. Så vi kan se og se hva hele makroen gjør her. Så vi klikker Kjør, og jeg vil få 2014. 2014, ok. Og så, trykk F8, vi er i ferd med å gjøre det avanserte filteret. Vi kan gå tilbake til Excel her og se hva som har skjedd.
Det første som har skjedd - Nå, det første som har skjedd er at vi har lagt til en ny midlertidig rad med overskriftene. Sett inn dette regnearket, bygde et kriterieområde med en overskrift og hvilket år de skrev inn, valgte feltene vi vil gjøre og deretter tilbake i VBA, jeg kjører neste kodelinje, det er F8 som gjør det avanserte filteret der . Det er utrolig raskt, og du vil se at det faktisk nå har gitt oss alle postene. Derfra er det bare litt opprydding, slett dette, slett dette. Jeg går tilbake til dataene og sletter rad 1, så er vi klar. Så jeg lar bare resten av det løpe, fjerne det brytepunktet, ok? Så det er VBA. For meg er dette jeg tror den raskeste veien, raskeste veien å gå.
OK, episodeoppsummering: Det avanserte filteret er mer avansert enn det vanlige filteret fordi det kan kopiere til et nytt område. Og nå viste jeg det ikke i denne videoen, men du kan bygge komplekse kriterier der felt 1 = A eller felt 2 = A. Det vanlige bilfilteret kan ikke gjøre det, og det er raskt. Mort prøver å behandle 100.000 rader i VBA ved å bruke en matrise eller ved å løkke, men det vil alltid være raskere å bruke Excel-bygningsfunksjoner enn å skrive din egen kode. Du må definere et inngangsområde, kriterieområde, utgangsområde. Du trenger alltid et inngangsområde i minst en av disse, selv om jeg i dag bruker begge deler. For inngangsområdet, enkelt rad med overskrifter over dataene. Så vi skal legge til en midlertidig rad med overskrifter. For utgangsområdet, de samme overskriftene du vil trekke ut, ok. Så du vet, hvis det var A, B,År og D, vi setter bare A, B og D som utgangsområde. For kriterieområdet, overskriftene i rad 1. Så dette er feltet jeg vil bygge et kriterium på, og dette er verdien jeg ser etter. Komplikasjoner: Eldre versjoner av Excel tillater ikke at utskriftsområdet ligger på et annet ark, så potensielt vil koden din kjøre da. Du vil bruke et navngitt område for inngangsområdet, for fra dette arket, vet du, det navngitte området, selv om det er på et annet ark, mener arket at navnet forgrener seg på det aktuelle arket. Slik at det avanserte filteret kan fungere.Eldre versjoner av Excel tillater ikke at utskriftsområdet ligger på et annet ark, så potensielt vil koden din kjøre da. Du vil bruke et navngitt område for inngangsområdet, for fra dette arket vet du det navngitte området, selv om det er på et annet ark, mener arket at navnet forgrener seg på det nåværende arket. Slik at det avanserte filteret kan fungere.Eldre versjoner av Excel tillater ikke at utskriftsområdet ligger på et annet ark, så potensielt vil koden din kjøre da. Du vil bruke et navngitt område for inngangsområdet, for fra dette arket vet du det navngitte området, selv om det er på et annet ark, mener arket at navnet forgrener seg på det nåværende arket. Slik at det avanserte filteret kan fungere.
Greit, vel, der har du det. Jeg vil takke Mort for at du sendte spørsmålet inn. Jeg vil takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Last ned fil
Last ned eksempelfilen her: Podcast2060.xlsm
