Korištenje naprednog filtra u Excelu za rješavanje Mortovog problema. Iako su redoviti filtri postali moćniji, još uvijek ima slučajeva da Napredni filtar može napraviti neke trikove, a drugi ne.
Gledaj video
- Napredni filtar je "napredniji" od uobičajenog filtra jer:
- 1) Može kopirati u novi raspon
- 2) Možete izgraditi složenije kriterije poput Polja 1 = A ili Polja 2 = A
- 3) Brzo je
- Mort pokušava obraditi 100.000 redaka u VBA prevlačenjem zapisa ili upotrebom niza
- Uvijek će biti brže koristiti ugrađene Excel značajke nego pisanje vlastitog koda.
- Trebate raspon unosa, a zatim raspon kriterija i / ili raspon izlaza
- Za opseg unosa: jedan red naslova iznad podataka
- Dodajte privremeni redak za naslove
- Za opseg izlaza: red naslova za stupce koje želite izdvojiti
- Za raspon kriterija: naslovi u retku 1, vrijednosti koje počinju u retku 2
- Komplikacija: Starije verzije Excela ne bi dopuštale da se raspon izlaza nalazi na drugom listu
- Ako pišete makronaredbu koja bi se mogla pokrenuti u 2003, upotrijebite imenovani raspon za ulazni raspon da biste ga zaobišli
Prijepis videozapisa
Naučite Excel iz Podcasta, epizoda 2060: Excel napredni filtar
Hej, dobrodošao natrag na netcast, ja sam Bill Jelen. Današnje pitanje poslao je Mort. Mort, on ima 100 000 redaka podataka i zanimaju ga stupci A, B i D gdje se stupac C podudara s određenom godinom. Dakle, želi da osoba uđe u godinu dana, a zatim dobije stupce A, B i D. A Mort ima neki VBA u kojem za to koristi nizove, a ja sam rekao, "Čekajte malo, znate, napredni bi filtar to učinio puno bolje. " U redu, i sad samo da pregledam, vratio sam se, pogledao svoje videozapise. Već dugo nisam pokrivao napredni filtar pa bismo trebali razgovarati o ovome.
Napredni filtar zahtijeva ulazni raspon, a zatim barem jedan od ovih: raspon kriterija ili izlazni raspon. Iako ćemo danas upotrijebiti i jedno i drugo. U redu, dakle ulazni opseg su vaši podaci i iznad podataka morate imati naslove. Dakle, Mort nema naslove i zato ću privremeno umetnuti redak ovdje i ponašati se kao Polje 1. Mort zna koji su njegovi podaci i mogao bi tamo staviti stvarne naslove. I ne koristimo ništa što se zove - ovi podaci u stupcima E do O, pa ne moram tamo dodavati naslove, u redu? Dakle, sada od A1 do D, 100000 postaje moj ulazni opseg. A zatim raspon izlaza i raspon kriterija - Pa, raspon izlaza je samo popis naslova koje želite. Dakle, ovdje ću staviti izlazni raspon i ne treba nam polje 3, paSamo ću to skinuti sa strane. Dakle, ovaj raspon upravo ovdje, od A1 do C1 postaje moj izlazni raspon koji Excel-u govori koja polja želim iz ulaznog raspona. A mogli bi biti i drugim redoslijedom ako želite preurediti stvari, na primjer ako prvo želim polje 4, a zatim polje 1 pa polje 2. I opet, to bi bili stvarni naslovi poput broja fakture. Jednostavno ne znam kako izgledaju Mortovi podaci.
A zatim, raspon kriterija je naslov i koju vrijednost želite. Pa recimo da sam pokušavao nešto dobiti 2014. godine. To postaje takav raspon kriterija. U redu, samo riječ opreza ovdje. Ja sam u programu Excel 2016 i moguće je napraviti napredni filtar između dva lista u programu Excel 2016, ali ako se vratite unatrag, a ne sjećam se koji je to put, možda 2003., nisam siguran. U nekom trenutku u prošlosti znalo je biti da niste mogli napraviti napredni filtar s jednog lista na drugi, pa biste morali doći ovdje i imenovati svoj raspon unosa. Ovdje biste trebali stvoriti ime. Moje ime ili nešto slično, u redu? I to bi bio način da to uspijete izvesti, u redu. Ne nužno u programu Excel 2016, ali opet,Nisam siguran hoće li Mort to pokretati u starijim verzijama podataka.
U redu, dakle ovdje na Data, idemo na Napredni filtar, u redu. A mi ćemo kopirati na drugo mjesto koje tamo omogućuje naš izlazni opseg. Dobro, dakle raspon popisa, gdje su podaci? Budući da sam u programu Excel 2016, idem usmjeriti na podatke, umjesto da koristim raspon imena - Dakle, to je moj ulazni raspon. Raspon kriterija su one stanice upravo tamo i onda, tamo gdje ćemo ići - izlaz na, to će samo biti ove tri stanice tamo. A zatim kliknemo U redu. Dobro, i BAM! To je tako brzo, brzo. A što ako bismo željeli drugu godinu? Ako bismo željeli drugu godinu, izbrisali bismo rezultate, stavili 2015. godinu, a zatim ponovno napravili napredni filtar, kopirali na drugo mjesto, kliknuli U redu i tu su svi zapisi iz 2015. godine. Brzo munje.
U redu sada, iako sam obožavatelj naprednog filtra u uobičajenom Excelu, bio sam veliki obožavatelj naprednog filtra u VBA-u, u redu, jer VBA čini filtar za unaprijed stvarno, stvarno, stvarno jednostavnim. U redu, ovdje ćemo napisati neki kod za Mort, pod pretpostavkom da Mortovi podaci nemaju naslove i morat ćemo privremeno dodati naslove, u redu? Dakle, prebacit ću se na VBA, Alt + F11 i to ćemo pokrenuti s radnog lista koji sadrži podatke. Dakle: Zatamni WS kao radni list, postavi WS = ActiveSheet. A zatim umetnite redak 1 i dodajte samo neke naslove: A, B, Godina i D. Shvatite koliko redova podataka danas imamo, a zatim počevši od ćelije A1 koja izlazi 4 stupca do zadnjeg retka, imenujte to u biti ulazni opseg. U redu, a ovo je zapravo Mortov kod ovdje, gdje je tražio InputBox,dobiva godinu koju žele, a zatim pita koju godinu ili kako žele imenovati novi list, u redu. Dakle, zapravo će umetnuti list na Fly, a zatim ću dimenzionirati novi list, WSN, kao ActiveSheet. Dakle, znam da je WS originalni list, WSN je novi list koji je upravo dodan. Na novi list stavite raspon kriterija, tako da se pod stupcem E nalazi naslov koji se ovdje podudara, a zatim, koji god odgovor koji su nam dali ide u E2. Izlazni raspon bit će moja ostala tri naslova: A, B i D. I opet, ako ih vi ili Mort promijenite u stvarne naslove, što je vjerojatno bolje učiniti od A, B, D, a vi biste također promijeniti ih u stvarne naslove, u redu? Dakle, sve je ovo samo malo predradnje ovdje. Ovaj sjajni redak koda učinit će cijeli napredni filtar. Tako,iz InputRange radimo AdvancedFilter, kopirat ćemo. To je naš odabrani filtar na mjestu ili kopija. CriteriaRange je od E1 do E2, CopyToRange je od A do C. Jedinstvene vrijednosti -Ne, želimo sve vrijednosti. U redu, taj jedan red koda tamo čini svu čaroliju petlje kroz sve zapise ili zamjenjuje petlju kroz sve zapise ili radeći nizove. A onda smo gotovi, očistit ćemo raspon kriterija, a zatim izbrisati redak 1 natrag na izvornom radnom listu.A onda smo gotovi, očistit ćemo raspon kriterija, a zatim izbrisati redak 1 na izvornom radnom listu.A onda smo gotovi, očistit ćemo raspon kriterija, a zatim izbrisati redak 1 natrag na izvornom radnom listu.
Ok, vratimo se ovdje na naše podatke. Olakšat ćemo to pokretanje, pa: Umetnite oblik i pozovite ovaj filtar, dom, centar, centar, veći, veći, veći, desni klik, dodijeli makronaredbu i dodijeli MacroForMort. U redu, idemo. Napravit ćemo test. Vidite da smo na tehničkom listu, kliknite Filter, koju godinu želimo? Želimo 2015. Kako ga želim nazvati? Želim to nazvati 2015, u redu. I BAM! Eto, gotovo je. Toliko je brzo, toliko je brzo ovo.
Budući da Mortovi izvorni podaci nisu imali naslove, možda ovi podaci ne bi trebali imati naslove. Idemo Alt + F11, upravo ovdje želimo očistiti raspon kriterija. Također ćemo redati (1) .Izbriši. U redu, pa će se sljedeći put kad se budemo bavili tim riješiti tih naslova. I neka je samo - Umjesto da cijelu stvar pokrenemo brzo, pogledajmo ovdje 2014. Dakle, odabrat ću jednu ćeliju na Data, Alt + F11, i želim pokrenuti samo do točke u kojoj radimo napredni filtar. Tako možemo pogledati i vidjeti što ovdje radi cijela makronaredba. Pa ćemo kliknuti Pokreni, a ja želim dobiti 2014. 2014., u redu. I tako, pritisnite F8, mi ćemo napraviti napredni filtar. Ovdje se možemo vratiti na Excel i vidjeti što se dogodilo.
Prvo što se dogodilo - Prvo, što se dogodilo, dodali smo novi privremeni redak s naslovima. Umetnuo je ovaj radni list, izgradio opseg kriterija sa zaglavljem i koju godinu unose, odabrao polja koja želimo napraviti, a zatim natrag u VBA, pokrenut ću sljedeći redak kodova, to je F8 koji radi napredni filtar upravo tamo . Nevjerojatno je brz i vidjet ćete da nam je to zapravo donijelo sve rekorde. Odatle je samo malo čišćenja, izbriši ovo, izbriši ovo. Vratit ću se na podatke i izbrisati redak 1 i bit ćemo spremni za to. Dakle, pustit ću ostatak da radi, ukloniti točku prekida, u redu? Dakle, tu je VBA. Za mene je ovo mislim najbrži put, najbrži put.
U redu, sažetak epizode: Napredni filtar napredniji je od uobičajenog filtra jer može kopirati u novi raspon. I sada, nisam to prikazao u ovom videozapisu, ali možete izgraditi složene kriterije gdje je Polje 1 = A ili Polje 2 = A. Redovni automatski filtar to ne može učiniti i to brzo. Mort pokušava obraditi 100 000 redaka u VBA pomoću niza ili petlje, ali uvijek će biti brže koristiti značajke Excel gradnje nego pisanje vlastitog koda. Morate definirati ulazni raspon, raspon kriterija, izlazni raspon. Uvijek vam treba opseg unosa u barem jednom od njih, iako danas koristim oba. Za opseg unosa, jedan red zaglavlja iznad podataka. Pa ćemo dodati privremeni red naslova. Za opseg izlaza, isti naslovi koje želite izdvojiti, u redu. Pa, znate, ako je to A, B,Godina i D, samo ćemo staviti A, B i D kao opseg izlaza. Za raspon kriterija, naslovi u retku 1. Dakle, ovo je polje na kojem želim graditi kriterije i to je vrijednost koju tražim. Komplikacije: Starije verzije programa Excel neće dopustiti da se raspon izlaza nalazi na drugom listu, pa će se potencijalno vaš kôd tada pokretati. Želite koristiti imenovani raspon za ulazni raspon, jer iz ovog lista, znate, imenovani raspon, iako je na drugom listu, list vjeruje da se naziv grana na trenutnom listu. Dakle, to bi omogućilo napredni filtar da radi.Starije verzije Excela neće dopustiti da se raspon izlaza nalazi na drugom listu, pa će se potencijalno vaš kôd tada pokretati. Želite koristiti imenovani raspon za ulazni raspon, jer iz ovog lista, znate, imenovani raspon, iako je na drugom listu, list vjeruje da se naziv grana na trenutnom listu. Dakle, to bi omogućilo napredni filtar da radi.Starije verzije Excela neće dopustiti da se raspon izlaza nalazi na drugom listu, pa će se potencijalno vaš kôd tada pokretati. Želite koristiti imenovani raspon za ulazni raspon, jer iz ovog lista, znate, imenovani raspon, iako je na drugom listu, list vjeruje da se naziv grana na trenutnom listu. Dakle, to bi omogućilo napredni filtar da radi.
Pa dobro, eto ti. Želim zahvaliti Mortu što je poslao to pitanje. Želim vam zahvaliti što ste navratili. Vidimo se sljedeći put za još jedan prijenos od.
Preuzmi datoteku
Preuzmite datoteku uzorka ovdje: Podcast2060.xlsm
