Utilizarea filtrului avansat în Excel pentru a rezolva problema lui Mort. Deși filtrele obișnuite au devenit mai puternice, există încă momente în care filtrul avansat poate face unele trucuri pe care alții nu le pot face.
Urmăriți videoclipul
- Filtrul avansat este mai „avansat” decât filtrul obișnuit, deoarece:
- 1) Se poate copia într-o gamă nouă
- 2) Puteți crea criterii mai complexe, cum ar fi Câmpul 1 = A sau Câmpul 2 = A
- 3) Este rapid
- Mort încearcă să proceseze 100K rânduri în VBA prin looping prin înregistrări sau folosind o matrice
- Utilizarea caracteristicilor Excel încorporate va fi întotdeauna mai rapidă decât scrierea propriului cod.
- Aveți nevoie de un interval de intrare, apoi de un interval de criterii și / sau un interval de ieșire
- Pentru intervalul de intrare: un singur rând de titluri deasupra datelor
- Adăugați un rând temporar pentru titluri
- Pentru intervalul de ieșire: un rând de titluri pentru coloanele pe care doriți să le extrageți
- Pentru intervalul de criterii: titluri în rândul 1, valori care încep în rândul 2
- Complicare: versiunile mai vechi de Excel nu ar permite ca intervalul de ieșire să fie pe altă foaie
- Dacă scrieți o macrocomandă care ar putea fi rulată în 2003, utilizați un interval numit pentru a eluda intervalul de intrare
Transcriere video
Aflați Excel din Podcast, Episodul 2060: Excel Advanced Filter
Hei, bine ai revenit pe netcast, eu sunt Bill Jelen. Întrebarea de astăzi trimisă de Mort. Mort, are 100.000 de rânduri de date și este interesat de coloanele A, B și D, unde coloana C se potrivește cu un anumit an. Așa că vrea ca o persoană să intre un an și apoi să obțină coloanele A, B și D. Și Mort are niște VBA în care folosește matrici pentru a face acest lucru și am spus: „Așteptați o secundă, știți, filtrul avansat ar face asta un mult mai bine. ” Bine, și acum doar pentru a revizui, m-am întors, m-am uitat înapoi prin videoclipurile mele. Nu am acoperit filtrul avansat de mult timp, așa că ar trebui să vorbim despre acest lucru.
Filtrul avansat necesită un interval de intrare și apoi cel puțin unul dintre acestea: un interval de criterii sau un interval de ieșire. Deși astăzi vom folosi ambele. Bine, deci intervalul de intrare este datele dvs. și trebuie să aveți titluri deasupra datelor. Deci, Mort nu are titluri și așa că voi introduce temporar un rând aici și aș face ca câmpul 1. Mort știe care sunt datele sale și așa ar putea pune titluri reale acolo. Și nu folosim nimic din ceea ce se numește - aceste date în coloanele E până la O, așa că nu trebuie să adaug titluri acolo, bine? Deci, acum, de la A1 la D, 100000 devine domeniul meu de intrare. Și apoi gama de ieșire și gama de criterii - Ei bine, gama de ieșire este doar o listă a titlurilor dorite. Deci, voi pune gama de ieșire aici și nu avem nevoie de câmpul 3, așa căVoi scoate asta în lateral. Deci, acum, acest interval chiar aici, A1 până la C1 devine domeniul meu de ieșire care îi spune Excel ce câmpuri doresc din intervalul de intrare. Și ar putea fi într-o ordine diferită dacă doriți să reordonați lucrurile, cum ar fi dacă vreau mai întâi câmpul 4, apoi câmpul 1 apoi câmpul 2. Și din nou, acestea ar fi titluri reale, cum ar fi numărul facturii. Pur și simplu nu știu cum arată datele lui Mort.
Și apoi, intervalul de criterii este un titlu și ce valoare doriți. Deci, să presupunem că am încercat să obțin ceva în anul 2014. Aceasta devine o gamă de criterii de genul acesta. Bine, doar un cuvânt de precauție aici. Sunt în Excel 2016 și este posibil să fac un filtru avansat între două foi în Excel 2016, dar dacă te duci înapoi și nu-mi amintesc ce cale de întoarcere este, poate 2003, nu sunt sigur. La un moment dat în trecut, se întâmpla că nu puteai face un filtru avansat de la o foaie la alta, așa că ar trebui să vii aici și să-ți denumiți intervalul de intrare. Ar trebui să creați un nume aici. MyName sau ceva de genul asta, bine? Și acesta ar fi modul în care ați putea să rezolvați acest lucru, bine. Nu neapărat în Excel 2016, dar din nou,Nu sunt sigur dacă Mort va rula acest lucru în versiunile mai vechi ale datelor.
Bine, așa că înapoi la Data, mergem la Advanced Filter, bine. Și vom copia într-o altă locație care permite gama noastră de ieșire acolo. Bine, deci gama de liste, unde sunt datele? Deoarece sunt în Excel 2016, voi merge la datele, în loc să folosesc intervalul de nume - Deci acesta este intervalul meu de intrare. Gama de criterii este acele celule chiar acolo și apoi, unde vom - ieșire, vor fi doar aceste trei celule acolo. Și apoi facem clic pe OK. Bine, și BAM! Atât de repede, de repede este. Și dacă ne-am dori un alt an? Dacă am dori un an diferit, am șterge rezultatele, le-am pune în 2015 și apoi am face din nou un filtru avansat, copiat într-o altă locație, facem clic pe OK și există toate înregistrările din 2015. Fulger rapid.
Bine acum, în timp ce sunt un fan al filtrului avansat în Excel obișnuit, am fost un mare fan al filtrului avansat în VBA, bine, pentru că VBA face filtrul avansat foarte, foarte, foarte simplu. Bine, așa că vom scrie aici un cod pentru Mort, presupunând că datele lui Mort nu au titluri și va trebui să adăugăm temporar titlurile, bine? Deci, voi trece la VBA, Alt + F11 și vom rula acest lucru din foaia de lucru care conține datele. Deci: Dim WS Ca foaie de lucru, Set WS = ActiveSheet. Apoi, introduceți rândul 1 și adăugați doar niște titluri: A, B, anul și D. Aflați câte rânduri de date avem astăzi și apoi începând de la celula A1 ieșind 4 coloane până la rândul final, numiți-l pe fi gama de intrare. Bine, și atunci acesta este de fapt codul lui Mort chiar aici, unde a cerut InputBox,primește anul pe care îl vor și apoi el întreabă ce an sau ce vor să numească noua foaie, bine. Deci, va introduce de fapt o foaie pe Fly și apoi I-Dimensionez o foaie nouă, WSN, ca ActiveSheet. Deci știu că WS este foaia originală, WSN este noua foaie care tocmai a fost adăugată. Pe noua foaie, puneți criteriul de gamă astfel încât în coloana E să existe titlul care se potrivește cu acest titlu aici și apoi, orice răspuns ne-au dat merge în E2. Gama de ieșire va fi celelalte trei titluri ale mele: A, B și D. Și din nou, dacă dvs. sau Mort le schimbați la titluri reale, ceea ce este probabil un lucru mai bun de făcut decât A, B, D și ați face, de asemenea, schimbați-le în titluri reale, bine? Deci, toate acestea sunt doar un pic de pre-lucru aici. Această linie minunată de cod va face întregul filtru avansat. Asa de,din InputRange facem un AdvancedFilter, vom copia. Acesta este filtrul nostru de alegere în loc sau copiere. CriteriaRange este de la E1 la E2, CopyToRange este de la A la C. Valori unice -Nu, vrem toate valorile. În regulă, o singură linie de cod de acolo face toată magia de a parcurge toate înregistrările sau înlocuiește buclarea prin toate înregistrările sau de a face matricele. Și apoi am terminat, vom șterge intervalul de criterii și apoi vom șterge rândul 1 înapoi pe foaia de lucru originală.Și apoi am terminat, vom șterge intervalul de criterii și apoi vom șterge rândul 1 înapoi pe foaia de lucru originală.Și apoi am terminat, vom șterge intervalul de criterii și apoi vom șterge rândul 1 înapoi pe foaia de lucru originală.
Bine, deci să revenim aici la datele noastre. Vom face mai ușor să rulați acest lucru, deci: Inserați, o formă și apelați acest filtru, Acasă, Centru, Centru, Mai mare, Mai mare, Mai mare, faceți clic dreapta, Alocați macro și atribuiți-l la MacroForMort. Bine, deci iată-ne. Vom face un test. Vedeți că suntem pe foaia de date, faceți clic pe Filtru, ce an dorim? Vrem 2015. Cum vreau să-l numesc? Vreau să-i spun 2015, bine. Și BAM! Acolo s-a terminat. Atât de repede, atât de repede este.
Acum, deoarece datele originale ale lui Mort nu aveau titluri, poate că aceste date nu ar trebui să aibă titluri. Deci, să mergem Alt + F11, chiar aici vrem să ștergem gama de criterii. Vom face și rânduri (1). Ștergeți. Bine, așa că acum, data viitoare când am fost în acest sens, va scăpa de aceste rubrici. Și hai doar - Mai degrabă decât să rulăm totul repede, să aruncăm o privire aici cu 2014. Așa că voi selecta o celulă pe Date, Alt + F11 și vreau să rulez până la punctul în care facem filtru avansat. Deci, putem privi și vedea ce face întreaga macro aici. Așa că vom face clic pe Run și vreau să obțin 2014. 2014, bine. Și așa, apăsați F8, suntem pe cale să facem filtrul avansat. Putem reveni la Excel aici și să vedem ce s-a întâmplat.
Primul lucru care sa întâmplat - Acum, primul lucru care sa întâmplat este că am adăugat un nou rând temporar cu titlurile. Am inserat această foaie de lucru, am construit un interval de criterii cu un antet și ce an au introdus, am ales câmpurile pe care vrem să le facem și apoi înapoi în VBA, voi rula următoarea linie de coduri, adică F8 care face filtrul avansat chiar acolo . Este incredibil de rapid și veți vedea că asta ne-a adus acum toate înregistrările. De acolo, este doar un pic de curățare, ștergeți acest lucru, ștergeți acest lucru. Mă întorc la date și șterg rândul 1 și vom fi bine să mergem. Așa că voi lăsa doar restul acelei să ruleze, să înlătur acel punct de întrerupere, bine? Deci, există VBA. Pentru mine, acesta este, cred, cel mai rapid mod, cel mai rapid mod de a merge.
Alright, episode recap: The advanced filter is more advanced than the regular filter because it can copy to a new range. And now, I didn't show it in this video but you can build complex criteria where Field 1 = A or Field 2 = A. The regular auto filter can't do that and it's fast. Mort is trying to process 100,000 rows in VBA by using an array or by looping, but it'll always be faster to use Excel building features than writing your own code. You need to define an input range, criteria range, output range. You always need an input range in at least one of these, although today I'm using both. For the input range, single row of headings above the data. So we're going to add a temporary row of headings. For the output range, the same headings that you want to extract, alright. So, you know, if it was A, B, Year and D, we’ll just put A, B and D as the output range. For the criteria range, headings in Row 1. So this is the field I want to build a criteria on and this is the value I'm looking for. Complications: Older versions of Excel will not allow the output range to be on another sheet, so, potentially your code will run back then. You want to use a named range for the input range because from this sheet, you know, the named range, even though it's on another sheet, the sheet believes the name branches on the current sheet. So that would allow the advanced filter to work.
În regulă, ei bine. Vreau să-i mulțumesc lui Mort pentru că a trimis întrebarea. Vreau să vă mulțumesc că ați trecut pe aici. Ne vedem data viitoare pentru un alt netcast de la.
Descărcare fișier
Descărcați exemplul de fișier aici: Podcast2060.xlsm
