Uporaba naprednega filtra v Excelu za reševanje Mortove težave. Čeprav so običajni filtri postali močnejši, lahko napredni filter še vedno stori nekaj trikov, ki jih drugi ne.
Oglejte si video
- Napredni filter je bolj "napreden" kot običajni filter, ker:
- 1) Kopira lahko v nov obseg
- 2) Lahko sestavite bolj zapletena merila, kot je polje 1 = A ali polje 2 = A
- 3) Hitro je
- Mort poskuša obdelati 100.000 vrstic v VBA tako, da pregleduje zapise ali uporablja matriko
- Uporaba vgrajenih Excelovih funkcij bo vedno hitrejša kot pisanje lastne kode.
- Potrebujete obseg vnosa in nato obseg meril in / ali obseg izhoda
- Za obseg vnosa: ena vrstica naslovov nad podatki
- Dodajte začasno vrstico za naslove
- Za izhodno območje: vrstica naslovov za stolpce, ki jih želite izvleči
- Za obseg meril: naslovi v vrstici 1, vrednosti, ki se začnejo v vrstici 2
- Zaplet: Starejše različice Excela ne dovoljujejo, da bi bilo izhodno območje na drugem listu
- Če pišete makro, ki bi ga bilo mogoče zagnati leta 2003, za izogibanje uporabite imenovani obseg za obseg vnosa
Video zapis
Naučite se Excel iz podcasta, epizoda 2060: Excel Advanced Filter
Hej, dobrodošli nazaj na netcastu, jaz sem Bill Jelen. Današnje vprašanje je poslal Mort. Mort, ima 100.000 vrstic podatkov in ga zanimajo stolpci A, B in D, kjer se stolpec C ujema z določenim letom. Torej želi, da oseba vstopi v eno leto in nato dobi stolpce A, B in D. In Mort ima nekaj VBA, kjer za to uporablja nize, in rekel sem: "Počakajte malo, veste, napredni filter bi to naredil veliko bolje. " V redu, zdaj pa samo za pregled, sem se vrnil, pogledal sem svoje videoposnetke. Že dolgo nisem pokrival naprednega filtra, zato bi se morali o tem pogovoriti.
Napredni filter zahteva vhodno območje in nato vsaj enega od teh: obseg meril ali izhodni obseg. Čeprav bomo danes uporabili oba. V redu, torej vhodni obseg so vaši podatki in nad njimi morate imeti naslove. Torej, Mort nima naslovov, zato bom tu začasno vstavil vrstico in naredil tako kot Polje 1. Mort ve, kakšni so njegovi podatki, zato bi lahko postavil prave naslove tja gor. In v stolpcih E do O ne uporabljamo ničesar, kar se imenuje, teh podatkov, zato mi ni treba dodati naslovov, v redu? Tako zdaj od A1 do D 100000 postane moje vnosno območje. In nato obseg izhoda in obseg meril - No, izhodni obseg je le seznam naslovov, ki jih želite. Torej bom postavil obseg izhodov sem in ne potrebujemo polja 3, zatoTo bom samo odstranil na stran. Torej, ta obseg tukaj, od A1 do C1, postane moj izhodni obseg, ki Excelu pove, katera polja želim iz vhodnega obsega. In lahko bi bili v drugačnem vrstnem redu, če bi želeli stvari preurediti, na primer, če najprej želim polje 4, nato pa polje 1, nato polje 2. In spet bi bili to pravi naslovi, kot je številka računa. Preprosto ne vem, kako izgledajo Mortovi podatki.
In potem je obseg meril naslov in kakšno vrednost želite. Recimo, da sem poskušal kar koli dobiti v letu 2014. To postane razpon meril tako. Tukaj je le previdna beseda. Sem v Excelu 2016 in v Excelu 2016 je mogoče narediti napredni filter med dvema listoma, toda če se vrnete nazaj in se ne spomnim, kakšna je pot nazaj, morda 2003, nisem prepričan. Včasih se je zgodilo, da niste mogli narediti naprednega filtra z enega lista na drugega, zato bi morali priti sem in poimenovati vnosni obseg. Tu bi morali ustvariti ime. Moje ime ali kaj podobnega, v redu? In to bi bil način, na katerega bi to lahko izpeljali, v redu. Ni nujno v Excelu 2016, ampak spetNisem prepričan, ali bo Mort to uporabljal v starejših različicah podatkov.
V redu, torej nazaj pri Data, gremo v Advanced Filter, v redu. Kopirali bomo na drugo lokacijo, ki tam omogoča naš izhodni obseg. V redu, torej obseg seznama, kje so podatki? Ker sem v Excelu 2016, bom namesto, da bi uporabil obseg imen, pokazal na Podatki - To je torej moj obseg vnosa. Obseg meril so tiste celice tam in tam, kjer bomo - izhod, bodo samo te tri celice tam. In nato kliknemo V redu. V redu, in BAM! Tako hitro, hitro je. In kaj, če bi želeli drugo leto? Če bi želeli drugo leto, bi rezultate izbrisali, postavili leta 2015 in nato znova naredili napredni filter, kopirali na drugo mesto, kliknili V redu in tam so vsi zapisi iz leta 2015. Bliskovito hitro.
Zdaj, čeprav sem ljubitelj naprednega filtra v običajnem Excelu, sem bil velik oboževalec naprednega filtra v VBA, v redu, saj VBA naredi vnaprejšnji filter resnično, zelo, zelo preprost. V redu, zato bomo tukaj napisali neko kodo za Mort, ob predpostavki, da Mortovi podatki nimajo naslovov in bomo morali začasno dodati naslove, v redu? Torej, prestopil bom na VBA, Alt + F11 in to bomo zagnali z delovnega lista, ki vsebuje podatke. Torej: Zatemni WS kot delovni list, nastavi WS = ActiveSheet. Nato vstavite vrstico 1 in dodajte le nekaj naslovov: A, B, leto in D. Ugotovite, koliko vrstic podatkov imamo danes, nato pa začnite od celice A1 do 4 stolpcev navzdol do zadnje vrstice, poimenujte to biti vhodno območje. V redu, potem je to pravzaprav Mortova koda tukaj, kjer je prosil za InputBox,dobi leto, ki ga želijo, nato pa vpraša, katero leto ali kako želijo imenovati nov list, v redu. Torej bo dejansko vstavil list na Fly in nato I-Dimension nov list, WSN, kot ActiveSheet. Torej vem, da je WS izvirni list, WSN pa je novi, pravkar dodan list. Na nov list vnesite obseg meril, tako da je pod stolpcem E naslov, ki se tukaj ujema, in nato, kateri koli odgovor, ki so nam ga dali, gre v E2. Izhodni obseg bodo moji drugi trije naslovi: A, B in D. In spet, če jih vi ali Mort spremenite v prave naslove, kar je verjetno bolje kot A, B, D, in tudi vi spremeniti v prave naslove, v redu? Torej, vse to je tu le malo predhodnega dela. Ta čudovita vrstica kode bo naredila celoten napredni filter. Torej,iz InputRange naredimo AdvancedFilter, kopirali bomo. To je naš filter za izbiro ali kopiranje. CriteriaRange je od E1 do E2, CopyToRange je od A do C. Edinstvene vrednosti -Ne, želimo vse vrednosti. V redu, ena vrstica kode tam naredi vso čarovnijo, da prelista vse zapise ali nadomesti zanko po vseh zapisih ali izvede polja. Potem smo končali, počistili bomo obseg meril in nato vrstico 1 izbrisali nazaj na prvotni delovni list.Potem smo končali, počistili bomo obseg meril in nato vrstico 1 izbrisali nazaj na prvotni delovni list.Potem smo končali, počistili bomo obseg meril in nato vrstico 1 izbrisali nazaj na prvotni delovni list.
V redu, zato se vrnimo nazaj k našim podatkom. To bomo olajšali, zato: Vstavite obliko in pokličite ta filter, dom, center, center, večji, večji, večji, z desno miškino tipko dodeli makro in ga dodeli MacroForMort. V redu, torej gremo. Naredili bomo test. Ko smo na podatkovnem listu, kliknite Filter, katero leto želimo? Želimo si 2015. Kako naj ga imenujem? Želim ga imenovati 2015, v redu. In BAM! Tam je končano. Tako hitro, tako hitro je to.
Ker Mortovi prvotni podatki niso imeli naslovov, morda ti podatki ne bi smeli imeti naslovov. Torej, pojdimo na Alt + F11, tukaj želimo počistiti obseg meril. Bomo tudi vrstice (1) .Izbriši. Torej, zdaj ko se bomo naslednjič ukvarjali s tem, se bomo znebili teh postavk. In samo - raje kot da vse skupaj zaženemo, si oglejmo leto 2014. Torej bom izbral eno celico na podatkih, Alt + F11, in želim teči tik do točke, ko naredimo napredni filter. Tako lahko pogledamo in vidimo, kaj tukaj počne ves makro. Torej bomo kliknili Zaženi in želim dobiti leto 2014, v redu. In tako, pritisnite F8, naredili bomo napredni filter. Tu se lahko vrnemo v Excel in vidimo, kaj se je zgodilo.
Prva stvar, ki se je zgodila - prva stvar, ki se je zgodila, je, da smo dodali novo začasno vrstico z naslovi. Vstavil je ta delovni list, zgradil obseg meril z naslovom in leto, ki ga vnesejo, izbral polja, ki jih želimo narediti, nato pa spet v VBA, zagnal bom naslednjo vrstico kod, to je F8, ki tam napreduje filter. . Je neverjetno hiter in videli boste, da nam je to dejansko prineslo vse zapise. Od tam naprej je le malo čiščenja, to izbriši, to izbriši. Vrnil se bom k podatkom in izbrisal 1. vrstico, zato bomo dobro šli. Torej bom pustil preostanek tečaja, da odstranim to mejo, v redu? Torej obstaja VBA. Zame je to najhitrejša pot, najhitrejša pot.
V redu, povzetek epizode: Napredni filter je naprednejši od običajnega filtra, ker lahko kopira v nov obseg. In zdaj tega nisem prikazal v tem videoposnetku, vendar lahko sestavite zapletene kriterije, kjer je polje 1 = A ali polje 2 = A. Navadni samodejni filter tega ne zmore in je hiter. Mort poskuša obdelati 100.000 vrstic v VBA z uporabo polja ali z zanko, vendar bo vedno hitreje uporabljati funkcije zgradbe Excel kot pisanje lastne kode. Določiti morate obseg vnosa, obseg meril in obseg izhoda. Vedno potrebujete vnosno območje za vsaj enega od teh, čeprav danes uporabljam oba. Za obseg vnosa ena vrstica naslovov nad podatki. Torej bomo dodali začasno vrstico naslovov. Za obseg izhoda enaki naslovi, ki jih želite izvleči, v redu. Torej, če bi bili A, B,Leto in D, za izhodno območje bomo postavili samo A, B in D. Za obseg meril, naslovi v vrstici 1. To je torej polje, na katerem želim zgraditi kriterije in to je vrednost, ki jo iščem. Zapleti: Starejše različice Excela ne bodo dovoljevale, da bi bilo izhodno območje na drugem listu, zato se bo morda vaša koda takrat zagnala. Za vnosni obseg želite uporabiti imenovani obseg, ker iz tega lista, saj veste, imenovani obseg, čeprav je na drugem listu, meni, da ime veje na trenutnem listu. Torej bi to omogočilo delovanje naprednega filtra.Starejše različice Excela ne bodo dovoljevale, da bi bilo izhodno območje na drugem listu, zato se bo morda vaša koda takrat zagnala. Za vnosni obseg želite uporabiti imenovani obseg, ker iz tega lista, saj veste, imenovani obseg, čeprav je na drugem listu, meni, da ime veje na trenutnem listu. Torej bi to omogočilo delovanje naprednega filtra.Starejše različice Excela ne bodo dovoljevale, da bi bilo izhodno območje na drugem listu, zato se bo morda vaša koda takrat zagnala. Za vnosni obseg želite uporabiti imenovani obseg, ker iz tega lista, saj veste, imenovani obseg, čeprav je na drugem listu, meni, da ime veje na trenutnem listu. Torej bi to omogočilo delovanje naprednega filtra.
No, no, tu ste. Zahvaljujem se Mortu, da je poslal to vprašanje. Zahvaljujem se vam, da ste se ustavili. Se vidimo naslednjič za še eno oddajo iz.
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast2060.xlsm
