„Excel VBA“ automatinis filtras: išsamus vadovas su pavyzdžiais

Daug „Excel“ funkcijų taip pat galima naudoti VBA - ir Automatinis filtras metodas yra viena iš tokių funkcijų.

Jei turite duomenų rinkinį ir norite jį filtruoti pagal kriterijų, galite lengvai tai padaryti naudodami juostos Duomenys parinktį Filtras.

Ir jei norite tobulesnės jo versijos, „Excel“ taip pat yra išplėstinis filtras.

Tada kodėl net naudoti automatinį filtrą VBA?

Jei jums tiesiog reikia filtruoti duomenis ir atlikti kai kuriuos pagrindinius dalykus, rekomenduočiau laikytis „Excel“ sąsajos siūlomų integruotų filtrų funkcijų.

Turėtumėte naudoti VBA automatinį filtrą, kai norite filtruoti duomenis kaip savo automatikos dalį (arba jei tai padeda sutaupyti laiko, nes tai leidžia greičiau filtruoti duomenis).

Pvz., Tarkime, kad norite greitai filtruoti duomenis pagal išskleidžiamąjį meniu ir nukopijuoti šiuos filtruotus duomenis į naują darbalapį.

Nors tai galima padaryti naudojant integruotą filtro funkciją ir kai kurias kopijavimo įklijavimo priemones, tai gali užtrukti daug laiko, kai tai padarysite rankiniu būdu.

Esant tokiai situacijai, naudojant VBA automatinį filtrą galima pagreitinti ir sutaupyti laiko.

Pastaba: Šį pavyzdį (apie duomenų filtravimą pagal išskleidžiamąjį meniu ir kopijavimą į naują lapą) aptarsiu vėliau šioje pamokoje.

„Excel VBA“ automatinio filtro sintaksė

Išraiška. Automatinis filtras (_Field_, _Criteria1_, _Operator_, _Criteria2_, _VisibleDropDown_)

• Išraiška: Tai yra diapazonas, kuriame norite taikyti automatinį filtrą.
• Laukas: [Neprivalomas argumentas] Tai stulpelio numeris, kurį norite filtruoti. Tai skaičiuojama duomenų rinkinyje iš kairės. Taigi, jei norite filtruoti duomenis pagal antrąjį stulpelį, ši vertė būtų 2.
• Kriterijai1: [Neprivalomas argumentas] Tai yra kriterijai, pagal kuriuos norite filtruoti duomenų rinkinį.
• operatorius: [Neprivalomas argumentas] Jei naudojate ir 2 kriterijus, galite derinti šiuos du kriterijus pagal operatorių. Galima naudoti šiuos operatorius: xl ir And, xlOr, xl
• Kriterijai2: [Neprivalomas argumentas] Tai yra antrasis kriterijus, pagal kurį galite filtruoti duomenų rinkinį.
• VisibleDropDown: [Neprivalomas argumentas] Galite nurodyti, ar norite, kad filtro išskleidžiamoji piktograma būtų rodoma filtruotuose stulpeliuose, ar ne. Šis argumentas gali būti TIKRAS arba NETIESAS.

Be išraiškos, visi kiti argumentai yra neprivalomi.

Jei nenaudojate jokio argumento, jis tiesiog pritaikys arba pašalins filtrų piktogramas stulpeliuose.

Sub FilterRows () darbalapiai („Filtruoti duomenis“). Diapazonas („A1“). Automatinio filtro pabaiga

Aukščiau pateiktas kodas tiesiog pritaikytų stulpeliams automatinio filtravimo metodą (arba jei jis jau pritaikytas, jis jį pašalins).

Tai paprasčiausiai reiškia, kad jei nematote filtrų piktogramų stulpelių antraštėse, pradėsite jį matyti, kai bus įvykdytas aukščiau pateiktas kodas, o jei matysite, jis bus pašalintas.

Jei turite filtruotų duomenų, jie pašalins filtrus ir parodys visą duomenų rinkinį.

Dabar pažiūrėkime keletą „Excel VBA“ automatinio filtro naudojimo pavyzdžių, kurie paaiškins jo naudojimą.

Pavyzdys: duomenų filtravimas pagal teksto sąlygą

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite jį filtruoti pagal stulpelį „Elementas“.

Žemiau pateiktas kodas filtruotų visas eilutes, kuriose elementas yra „Spausdintuvas“.

Sub FilterRows () Darbo lapai ("Sheet1"). Diapazonas ("A1"). AutoFilter Field: = 2, Criteria1: = "Printer" End Sub

Aukščiau pateiktas kodas reiškia 1 lapą ir jame nurodo A1 (kuris yra duomenų rinkinio langelis).

Atminkite, kad čia mes naudojome lauką: = 2, nes elementų stulpelis yra antras stulpelis mūsų duomenų rinkinyje iš kairės.

Dabar, jei galvojate - kodėl man tai reikia padaryti naudojant VBA kodą. Tai galima lengvai padaryti naudojant integruotą filtro funkciją.

Tu teisus!

Jei tai yra viskas, ką norite padaryti, geriau naudokite integruotą filtro funkciją.

Tačiau skaitydami likusį vadovėlį pamatysite, kad jį galima derinti su papildomu kodu, kad būtų sukurta galinga automatika.

Tačiau prieš parodydamas jums, leiskite man pateikti keletą pavyzdžių, kurie parodys, ką gali padaryti visas „AutoFilter“ metodas.

Paspauskite čia atsisiųsti pavyzdinį failą ir sekti toliau.

Pavyzdys: keli kriterijai (IR/ARBA) tame pačiame stulpelyje

Tarkime, kad turiu tą patį duomenų rinkinį ir šį kartą noriu filtruoti visus įrašus, kuriuose elementas yra „Spausdintuvas“ arba „Projektorius“.

Žemiau pateiktas kodas tai padarys:

Sub FilterRowsOR () darbalapiai ("Sheet1"). Diapazonas ("A1"). Automatinio filtro laukas: = 2, Criteria1: = "Printer", Operatorius: = xlOr, Criteria2: = "Projektorius" pabaiga

Atkreipkite dėmesį, kad čia aš naudoju xlOR operatorius.

Tai nurodo VBA naudoti ir kriterijus, ir filtruoti duomenis, jei tenkinamas kuris nors iš dviejų kriterijų.

Panašiai galite naudoti AND kriterijus.

Pavyzdžiui, jei norite filtruoti visus įrašus, kurių kiekis yra didesnis nei 10, bet mažesnis nei 20, galite naudoti žemiau esantį kodą:

Sub FilterRowsAND () darbalapiai („Sheet1“). Diapazonas („A1“). Automatinio filtro laukas: = 4, Criteria1: = "> 10", _ Operatorius: = xlAnd, Criteria2: = "<20" End Sub

Pavyzdys: keli kriterijai su skirtingais stulpeliais

Tarkime, kad turite šį duomenų rinkinį.

Naudodami automatinį filtrą galite vienu metu filtruoti kelis stulpelius.

Pavyzdžiui, jei norite filtruoti visus įrašus, kuriuose prekė yra „Spausdintuvas“, o pardavimo atstovas - „Žymėti“, galite naudoti toliau nurodytą kodą:

Sub FilterRows () su darbalapiais ("Sheet1"). Diapazonas ("A1"). AutoFilter laukas: = 2, Criteria1: = "Printer". AutoFilter field: = 3, Criteria1: = "Mark" End with End Sub

Pavyzdys: 10 geriausių įrašų filtravimas naudojant automatinio filtravimo metodą

Tarkime, kad turite žemiau esantį duomenų rinkinį.

Žemiau yra kodas, kuris suteiks jums 10 geriausių įrašų (pagal kiekio stulpelį):

Antrinis filtrasRowsTop10 () ActiveSheet.Range ("A1"). Automatinio filtravimo laukas: = 4, 1 kriterijus: = "10", operatorius: = xlTop10Items End Sub

Pirmiau pateiktame kode naudoju „ActiveSheet“. Jei norite, galite naudoti lapo pavadinimą.

Atminkite, kad šiame pavyzdyje, jei norite gauti 5 populiariausius elementus, tiesiog pakeiskite skaičių 1 kriterijus: = "10" nuo 10 iki 5.

Taigi 5 populiariausių prekių kodas būtų toks:

Antrinis filtrasRowsTop5 () ActiveSheet.Range ("A1"). Automatinio filtro laukas: = 4, 1 kriterijus: = "5", operatorius: = xlTop10Items End Sub

Tai gali atrodyti keista, tačiau nesvarbu, kiek norite geriausių elementų, operatoriaus vertė visada išlieka xlTop10Items.

Panašiai žemiau pateiktas kodas suteiks jums 10 apatinių elementų:

Antrinis filtrasRowsBottom10 () ActiveSheet.Range ("A1"). Automatinio filtro laukas: = 4, 1 kriterijus: = "10", operatorius: = xlBottom10Items End Sub

Ir jei norite 5 apatinių elementų, pakeiskite skaičių 1 kriterijus: = "10" nuo 10 iki 5.

Pavyzdys: 10 procentų filtras naudojant automatinio filtravimo metodą

Tarkime, kad turite tą patį duomenų rinkinį (kaip ir ankstesniuose pavyzdžiuose).

Žemiau yra kodas, kuris suteiks jums 10 procentų geriausių įrašų (remiantis kiekio stulpeliu):

Antrinis filtrasRowsTop10 () ActiveSheet.Range ("A1"). Automatinio filtro laukas: = 4, 1 kriterijus: = "10", operatorius: = xlTop10Percent End Sub

Mūsų duomenų rinkinyje, kadangi turime 20 įrašų, jis pateiks 2 geriausius įrašus (tai yra 10% visų įrašų).

Pavyzdys: pakaitos simbolių naudojimas automatiniame filtre

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau:

Jei norite filtruoti visas eilutes, kuriose elemento pavadinime yra žodis „lenta“, galite naudoti žemiau esantį kodą:

Antrinis filtrasRowWildcard () darbalapiai ("Sheet1"). Diapazonas ("A1"). AutoFilter Field: = 2, Criteria1: = "*Board*" End Sub

Pirmiau pateiktame kode naudoju pakaitos simbolį * (žvaigždutę) prieš ir po žodžio „lenta“ (tai yra kriterijus).

Žvaigždutė gali reikšti bet kokį simbolių skaičių. Taigi tai filtruotų bet kurį elementą, kuriame yra žodis „lenta“.

Pavyzdys: nukopijuokite filtruotas eilutes į naują lapą

Jei norite ne tik filtruoti įrašus pagal kriterijus, bet ir nukopijuoti filtruotas eilutes, galite naudoti žemiau esančią makrokomandą.

Jis nukopijuoja filtruotas eilutes, prideda naują darbalapį ir įklijuoja šias nukopijuotas eilutes į naują lapą.

Sub CopyFilteredRows () Dim rng kaip diapazonas Dim ws kaip darbalapis, jei darbalapiai ("Sheet1"). AutoFilterMode = False then MsgBox "Nėra filtruotų eilučių" Exit Sub End, jei nustatytas rng = darbalapiai ("Sheet1"). AutoFilter.Range Set ws = Worksheets.Add rng.Copy Range ("A1") Pabaiga

Pirmiau pateiktas kodas patikrintų, ar 1 lape yra filtruotų eilučių.

Jei nėra filtruotų eilučių, tai parodys pranešimų laukelį.

Ir jei yra filtruotų eilučių, jos nukopijuos jas, įterps naują darbalapį ir įklijuos šias eilutes į tą naujai įterptą darbalapį.

Pavyzdys: filtruokite duomenis pagal langelio vertę

Naudodami automatinį filtrą VBA kartu su išskleidžiamuoju sąrašu, galite sukurti funkciją, kai, kai tik išskleidžiamajame meniu pasirenkate elementą, visi to elemento įrašai yra filtruojami.

Kažkas, kaip parodyta žemiau:

Paspauskite čia atsisiųsti pavyzdinį failą ir sekti toliau.

Šio tipo konstrukcija gali būti naudinga, kai norite greitai filtruoti duomenis ir toliau juos naudoti savo darbe.

Žemiau yra kodas, kuris tai padarys:

Privatus antrinis darbalapio keitimas („ByVal Target As Range“) Jei Target.Address = "$ B $ 2" Then If Range ("B2") = "All" then Range ("A5"). AutoFilter Else Range ("A5"). AutoFilter Field : = 2, 1 kriterijus: = Diapazonas („B2“) Pabaiga Jei pabaiga Jei pabaiga Sub

Tai darbalapio įvykio kodas, kuris vykdomas tik tada, kai darbalapis pasikeičia, o tikslinė ląstelė yra B2 (kur mes turime išskleidžiamąjį meniu).

Be to, „If Then Else“ sąlyga naudojama norint patikrinti, ar vartotojas išskleidžiamajame meniu pasirinko „All“. Jei pasirinkta Viskas, rodomas visas duomenų rinkinys.

Šis kodas neįdėtas į modulį.

Vietoj to jis turi būti dedamas į darbalapio, kuriame yra šie duomenys, užpakalinę dalį.

Štai šie veiksmai, kaip įdėti šį kodą į darbalapio kodo langą:

1. Atidarykite VB redaktorių (spartusis klavišas - ALT + F11).
2. „Project Explorer“ srityje dukart spustelėkite darbalapio pavadinimą, kuriame norite naudoti šią filtravimo funkciją.
3. Darbo lapo kodo lange nukopijuokite ir įklijuokite aukščiau esantį kodą.
4. Uždarykite VB redaktorių.

Dabar, kai naudojate išskleidžiamąjį sąrašą, jis automatiškai filtruoja duomenis.

Tai darbalapio įvykio kodas, kuris vykdomas tik tada, kai darbalapis pasikeičia, o tikslinė ląstelė yra B2 (kur mes turime išskleidžiamąjį meniu).

Be to, „If Then Else“ sąlyga naudojama norint patikrinti, ar vartotojas išskleidžiamajame meniu pasirinko „All“. Jei pasirinkta Viskas, rodomas visas duomenų rinkinys.

Įjunkite/išjunkite „Excel“ automatinį filtrą naudodami VBA

Taikant automatinį filtrą daugybei ląstelių, jau gali būti keli filtrai.

Galite naudoti toliau pateiktą kodą, kad išjungtumėte visus iš anksto pritaikytus automatinius filtrus:

Sub TurnOFFAutoFilter () darbalapiai ("Sheet1"). AutoFilterMode = False End Sub

Šis kodas patikrina visus lapus ir pašalina visus pritaikytus filtrus.

Jei nenorite išjungti filtrų iš viso lapo, bet tik iš konkretaus duomenų rinkinio, naudokite žemiau esantį kodą:

Papildomas posūkis „OFF“ „AutoFilter“ () Jei darbalapiai („Sheet1“). Diapazonas („A1“). Automatinis filtras, tada darbalapiai („Sheet1“). Diapazonas („A1“). AutoFilter End, End End

Aukščiau pateiktas kodas tikrina, ar jau yra filtrų, ar ne.

Jei filtrai jau naudojami, jis pašalinamas, kitaip nieko nedaroma.

Panašiai, jei norite įjungti automatinį filtrą, naudokite žemiau esantį kodą:

Sub TurnOnAutoFilter () Jei ne darbalapiai („Sheet1“). Diapazonas („A4“). AutoFilter After Worksheets („Sheet1“). Diapazonas („A4“). AutoFilter End End End

Patikrinkite, ar automatinis filtras jau pritaikytas

Jei turite lapą su keliais duomenų rinkiniais ir norite įsitikinti, kad žinote, kad nėra filtrų, galite naudoti žemiau esantį kodą.

Sub CheckforFilters () Jei ActiveSheet.AutoFilterMode = True Tada MsgBox "Jau yra filtrų" Else MsgBox "Nėra filtrų" End If End Sub

Šis kodas naudoja pranešimų langelio funkciją, kuri, radusi lape filtrus, rodo pranešimą „Jau yra filtrų“, kitaip rodoma „Nėra filtrų“.

Rodyti visus duomenis

Jei duomenų rinkiniui pritaikėte filtrus ir norite parodyti visus duomenis, naudokite žemiau esantį kodą:

Sub ShowAllData () Jei ActiveSheet.FilterMode Tada ActiveSheet.ShowAllData End Sub

Aukščiau pateiktas kodas tikrina, ar „FilterMode“ yra TRUE, ar FALSE.

Jei tai tiesa, tai reiškia, kad buvo pritaikytas filtras ir jis naudoja „ShowAllData“ metodą, kad būtų rodomi visi duomenys.

Atminkite, kad tai nepašalina filtrų. Filtro piktogramas vis dar galima naudoti.

Automatinio filtro naudojimas apsaugotuose lapuose

Pagal numatytuosius nustatymus, kai apsaugote lapą, filtrai neveiks.

Jei jau turite filtrų, galite įjungti automatinį filtrą, kad įsitikintumėte, jog jis veikia net ir apsaugotuose lapuose.

Norėdami tai padaryti, patikrinkite parinktį Naudoti automatinį filtrą, tuo pačiu apsaugodami lapą.

Nors tai veikia, kai jau turite filtrus, jei bandysite pridėti automatinius filtrus naudodami VBA kodą, tai neveiks.

Kadangi lapas yra apsaugotas, jis neleis vykdyti jokių makrokomandų ir keisti automatinio filtro.

Taigi, norėdami apsaugoti darbalapį, turite naudoti kodą ir įsitikinti, kad jame įjungti automatiniai filtrai.

Tai gali būti naudinga, kai sukūrėte dinaminį filtrą (ką aptariau pavyzdyje - „Filtruoti duomenis pagal langelio vertę“).

Žemiau yra kodas, kuris apsaugo lapą, tačiau tuo pačiu leidžia jame naudoti filtrus ir VBA makrokomandas.

Privati ​​antrinė darbo knyga_Atidaryti () su darbalapiais („Sheet1“) .EnableAutoFilter = True .Protect Password: = "password", Contents: = True, UserInterfaceOnly: = Tikroji pabaiga su pabaiga

Šį kodą reikia įdėti į „ThisWorkbook“ kodo langą.

Štai kodo įdėjimo į „ThisWorkbook“ kodo langą veiksmai:

1. Atidarykite VB redaktorių (spartusis klavišas - ALT + F11).
2. „Project Explorer“ srityje dukart spustelėkite „ThisWorkbook“ objektą.
3. Atsidariusiame kodo lange nukopijuokite ir įklijuokite aukščiau esantį kodą.

Kai tik atidarysite darbaknygę ir įgalinsite makrokomandas, ji automatiškai paleis makrokomandą ir apsaugos „Sheet1“.

Tačiau prieš tai darydamas jis nurodys „EnableAutoFilter = True“, o tai reiškia, kad filtrai veiks ir apsaugotame lape.

Be to, jis nustato „UserInterfaceOnly“ argumentą į „True“. Tai reiškia, kad nors darbalapis yra apsaugotas, VBA makrokomandų kodas ir toliau veiks.

Jums taip pat gali patikti šios VBA pamokos:

• „Excel VBA“ kilpos.
• Filtruokite langelius su paryškintu šriftu.
• Makro įrašymas.
• Rūšiuokite duomenis naudodami VBA.
• Rūšiuoti darbalapio skirtukus „Excel“.