Excel VBA automātiskais filtrs: pilnīga rokasgrāmata ar piemēriem

Ir pieejamas arī daudzas Excel funkcijas, ko izmantot VBA - un Automātiskais filtrs metode ir viena no šādām funkcijām.

Ja jums ir datu kopa un vēlaties to filtrēt, izmantojot kritēriju, varat to viegli izdarīt, izmantojot lentes opciju Filtrs.

Un, ja vēlaties uzlabot tās versiju, programmā Excel ir arī uzlabots filtrs.

Tad kāpēc pat izmantot automātisko filtru VBA?

Ja jums vienkārši jāfiltrē dati un jāveic dažas pamata lietas, es ieteiktu pieturēties pie iebūvētās filtra funkcionalitātes, ko piedāvā Excel saskarne.

Jums vajadzētu izmantot VBA automātisko filtru, ja vēlaties filtrēt datus kā daļu no savas automatizācijas (vai ja tas palīdz ietaupīt laiku, paātrinot datu filtrēšanu).

Piemēram, pieņemsim, ka vēlaties ātri filtrēt datus, pamatojoties uz nolaižamo izvēli, un pēc tam kopēt šos filtrētos datus jaunā darblapā.

Lai gan to var izdarīt, izmantojot iebūvēto filtru funkcionalitāti, kā arī dažas kopēšanas un ielīmēšanas iespējas, tas var aizņemt daudz laika, lai to izdarītu manuāli.

Šādā gadījumā VBA automātiskā filtra izmantošana var paātrināt un ietaupīt laiku.

Piezīme: Es apskatīšu šo piemēru (par datu filtrēšanu, pamatojoties uz nolaižamo izvēli un kopēšanu jaunā lapā) vēlāk šajā apmācībā.

Excel VBA automātiskā filtra sintakse

Izteiksme. Automātiskais filtrs (_Field_, _Criteria1_, _Operator_, _Criteria2_, _VisibleDropDown_)

• Izteiksme: Šis ir diapazons, kurā vēlaties lietot automātisko filtru.
• Lauks: [Neobligāts arguments] Šis ir kolonnas numurs, kuru vēlaties filtrēt. Datu kopā tas tiek skaitīts no kreisās puses. Tātad, ja vēlaties filtrēt datus, pamatojoties uz otro kolonnu, šī vērtība būtu 2.
• Kritēriji1: [Neobligāts arguments] Šie ir kritēriji, pēc kuriem vēlaties filtrēt datu kopu.
• Operators: [Neobligāts arguments] Ja izmantojat arī 2. kritēriju, varat apvienot šos divus kritērijus, pamatojoties uz operatoru. Lietošanai ir pieejami šādi operatori: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Kritēriji2: [Neobligāts arguments] Šis ir otrais kritērijs, pēc kura varat filtrēt datu kopu.
• VisibleDropDown: [Neobligāts arguments] Varat norādīt, vai vēlaties, lai filtrētās slejās tiktu parādīta nolaižamā filtra ikona. Šis arguments var būt patiess vai nepatiess.

Izņemot izteiksmi, visi pārējie argumenti nav obligāti.

Ja neizmantojat nevienu argumentu, tas vienkārši lietos vai noņems filtru ikonas kolonnās.

Apakšfiltru rindas () darblapas ("Filtrēt datus"). Diapazons ("A1"). Automātiskā filtra beigu apakšdaļa

Iepriekš minētais kods vienkārši lietos automātiskās filtra metodi kolonnām (vai, ja tas jau ir lietots, tas to noņems).

Tas vienkārši nozīmē, ka, ja kolonnu galvenēs neredzat filtra ikonas, jūs sāksit to redzēt, kad tiks izpildīts iepriekš minētais kods, un, ja jūs to redzēsit, tas tiks noņemts.

Ja jums ir filtrēti dati, tas noņems filtrus un parādīs pilnu datu kopu.

Tagad apskatīsim dažus Excel VBA automātiskā filtra izmantošanas piemērus, kas padarīs to skaidru.

Piemērs: datu filtrēšana, pamatojoties uz teksta nosacījumu

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties to filtrēt, pamatojoties uz sleju “Vienums”.

Zemāk esošais kods filtrētu visas rindas, kurās vienums ir “Printeris”.

Apakšfiltru rindas () darblapas ("lapa1"). Diapazons ("A1"). Automātiskā filtra lauks: = 2, kritērijs1: = "printeris" beigu apakšdaļa

Iepriekš minētais kods attiecas uz lapu 1 un tajā - uz A1 (kas ir datu kopas šūna).

Ņemiet vērā, ka šeit mēs esam izmantojuši lauku: = 2, jo vienuma kolonna ir otrā kolonna mūsu datu kopā no kreisās puses.

Tagad, ja jūs domājat - kāpēc man tas jādara, izmantojot VBA kodu. To var viegli izdarīt, izmantojot iebūvēto filtra funkcionalitāti.

Tev taisnība!

Ja tas ir viss, ko vēlaties darīt, labāk izmantojiet iebūvēto filtra funkcionalitāti.

Bet, lasot atlikušo apmācību, jūs redzēsit, ka to var apvienot ar kādu papildu kodu, lai izveidotu jaudīgu automatizāciju.

Bet pirms es jums tos parādīšu, ļaujiet man vispirms apskatīt dažus piemērus, lai parādītu, ko var paveikt visa automātiskā filtra metode.

Noklikšķiniet šeit lai lejupielādētu parauga failu un sekotu tam.

Piemērs: vairāki kritēriji (UN/VAI) vienā slejā

Pieņemsim, ka man ir viena un tā pati datu kopa, un šoreiz es vēlos filtrēt visus ierakstus, kur vienums ir vai nu “Printeris”, vai “Projektors”.

Zemāk esošais kods to darīs:

ApakšfiltrsRowsOR () darblapas ("Sheet1"). Diapazons ("A1"). Automātiskā filtra lauks: = 2, Criteria1: = "Printeris", Operators: = xlOr, Criteria2: = "Projector" End Sub

Ņemiet vērā, ka šeit es izmantoju xlOR operators.

Tas liek VBA izmantot gan kritērijus, gan filtrēt datus, ja ir izpildīts kāds no diviem kritērijiem.

Līdzīgi varat izmantot arī AND kritērijus.

Piemēram, ja vēlaties filtrēt visus ierakstus, kuru daudzums ir lielāks par 10, bet mazāks par 20, varat izmantot tālāk norādīto kodu.

ApakšfiltrsRowsAND () darblapas ("Sheet1"). Diapazons ("A1"). Automātiskās filtra lauks: = 4, Criteria1: = "> 10", _ Operators: = xlAnd, Criteria2: = "<20" End Sub

Piemērs: vairāki kritēriji ar dažādām kolonnām

Pieņemsim, ka jums ir šāda datu kopa.

Izmantojot automātisko filtru, vienlaicīgi varat filtrēt vairākas kolonnas.

Piemēram, ja vēlaties filtrēt visus ierakstus, kur vienums ir “Printeris” un tirdzniecības pārstāvis ir “Atzīmēt”, varat izmantot tālāk norādīto kodu.

Apakšfiltru rindas () ar darblapām ("lapa1"). Diapazons ("A1"). Automātiskā filtra lauks: = 2, kritēriji1: = "printeris". Automātiskā filtra lauks: = 3, kritērijs1: = "Atzīmēt" beigas ar beigu apakšdaļu

Piemērs: filtrējiet 10 populārākos ierakstus, izmantojot automātiskās filtrēšanas metodi

Pieņemsim, ka jums ir zemāk esošā datu kopa.

Zemāk ir kods, kas sniegs jums desmit populārākos ierakstus (pamatojoties uz daudzuma kolonnu):

ApakšfiltrsRowsTop10 () ActiveSheet.Range ("A1"). Automātiskās filtra lauks: = 4, Kritēriji1: = "10", Operators: = xlTop10Items Beigu apakšdaļa

Iepriekš minētajā kodā esmu izmantojis ActiveSheet. Ja vēlaties, varat izmantot lapas nosaukumu.

Ņemiet vērā, ka šajā piemērā, ja vēlaties iegūt 5 populārākos vienumus, vienkārši mainiet numuru 1. kritērijs: = ”10” no 10 līdz 5.

Tātad pieciem populārākajiem vienumiem kods būtu šāds:

ApakšfiltrsRowsTop5 () ActiveSheet.Range ("A1"). Automātiskās filtra lauks: = 4, Kritēriji1: = "5", Operators: = xlTop10Items Beigu apakšdaļa

Tas var izskatīties dīvaini, taču neatkarīgi no tā, cik daudz vienumu vēlaties, operatora vērtība vienmēr paliek xlTop10Items.

Līdzīgi zemāk esošais kods sniegs jums 10 apakšējos vienumus:

ApakšfiltrsRowsBottom10 () ActiveSheet.Range ("A1"). Automātiskās filtra lauks: = 4, Kritēriji1: = "10", Operators: = xlBottom10Items Beigu apakšdaļa

Un, ja vēlaties 5 apakšējos vienumus, mainiet numuru 1. kritērijs: = ”10” no 10 līdz 5.

Piemērs: filtrējiet 10 procentus, izmantojot automātiskās filtra metodi

Pieņemsim, ka jums ir tāda pati datu kopa (kā izmantota iepriekšējos piemēros).

Zemāk ir kods, kas sniegs jums 10 procentus labākos ierakstus (pamatojoties uz daudzuma kolonnu):

ApakšfiltrsRowsTop10 () ActiveSheet.Range ("A1"). Automātiskās filtra lauks: = 4, Kritēriji1: = "10", Operators: = xlTop10Percent End Sub

Tā kā mūsu datu kopā ir 20 ierakstu, tas atgriezīs 2 labākos ierakstus (kas ir 10% no kopējiem ierakstiem).

Piemērs: aizstājējzīmju izmantošana automātiskajā filtrā

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk:

Ja vēlaties filtrēt visas rindas, kurās vienuma nosaukumā ir vārds “Board”, varat izmantot tālāk norādīto kodu.

ApakšfiltrsRowWildcard () darblapas ("Sheet1"). Diapazons ("A1"). Automātiskās filtra lauks: = 2, Criteria1: = "*Board*" End Sub

Iepriekš minētajā kodā esmu izmantojis aizstājējzīmi * (zvaigznīte) pirms un pēc vārda “Board” (tas ir kritērijs).

Zvaigznīte var attēlot neierobežotu rakstzīmju skaitu. Tātad tas filtrētu jebkuru vienumu, kurā ir vārds “dēlis”.

Piemērs: kopējiet filtrētās rindas jaunā lapā

Ja vēlaties ne tikai filtrēt ierakstus, pamatojoties uz kritērijiem, bet arī kopēt filtrētās rindas, varat izmantot zemāk esošo makro.

Tas kopē filtrētās rindas, pievieno jaunu darblapu un pēc tam ielīmē šīs kopētās rindas jaunajā lapā.

Sub CopyFilteredRows () Dim rng kā diapazons Dim ws kā darblapa Ja darblapas ("Sheet1"). AutoFilterMode = False, tad MsgBox "Nav filtrētu rindu" Exit Sub End, If Set rng = Worksheets ("Sheet1"). AutoFilter.Range Set ws = Worksheets.Add rng.Copy Range ("A1") End Sub

Iepriekš minētais kods pārbaudītu, vai 1. lapā ir filtrētas rindas.

Ja nav filtrētu rindu, tas parādīs ziņojumu lodziņu.

Un, ja ir filtrētas rindas, tas tās kopēs, ievietos jaunu darblapu un ielīmēs šīs rindas šajā tikko ievietotajā darblapā.

Piemērs: filtrējiet datus, pamatojoties uz šūnas vērtību

Izmantojot automātisko filtru VBA kopā ar nolaižamo sarakstu, varat izveidot funkcionalitāti, kurā, tiklīdz nolaižamajā izvēlnē atlasāt vienumu, visi šī vienuma ieraksti tiek filtrēti.

Kaut kas, kā parādīts zemāk:

Noklikšķiniet šeit lai lejupielādētu parauga failu un sekotu tam.

Šāda veida konstrukcija var būt noderīga, ja vēlaties ātri filtrēt datus un pēc tam tos izmantot savā darbā.

Zemāk ir kods, kas to darīs:

Privāta apakšdarblapas_maiņa (ByVal mērķis kā diapazons) Ja Target.Address = "$ B $ 2" Then If Range ("B2") = "All" Then Range ("A5"). AutoFilter Else Range ("A5"). AutoFilter Field : = 2, 1. kritērijs: = Diapazons ("B2") Beigas Ja beigas Ja beigas Apakš

Šis ir darblapas notikuma kods, kas tiek izpildīts tikai tad, ja darblapā ir izmaiņas un mērķa šūna ir B2 (kur mums ir nolaižamā izvēlne).

Tāpat tiek izmantots nosacījums Ja pēc tam cits, lai pārbaudītu, vai lietotājs nolaižamajā izvēlnē ir atlasījis opciju “Visi”. Ja ir atlasīts Visi, tiek parādīta visa datu kopa.

Šis kods NAV ievietots modulī.

Tā vietā tas jāievieto darblapas aizmugurē, kurā ir šie dati.

Tālāk ir norādītas darbības, kā ievietot šo kodu darblapas koda logā:

1. Atveriet VB redaktoru (īsinājumtaustiņš - ALT + F11).
2. Rūtī Project Explorer veiciet dubultklikšķi uz darblapas nosaukuma, kurā vēlaties izmantot šo filtrēšanas funkcionalitāti.
3. Darblapas koda logā nokopējiet un ielīmējiet iepriekš minēto kodu.
4. Aizveriet VB redaktoru.

Tagad, kad izmantojat nolaižamo sarakstu, tas automātiski filtrē datus.

Šis ir darblapas notikuma kods, kas tiek izpildīts tikai tad, ja darblapā ir izmaiņas un mērķa šūna ir B2 (kur mums ir nolaižamā izvēlne).

Tāpat tiek izmantots nosacījums Ja pēc tam cits, lai pārbaudītu, vai lietotājs nolaižamajā izvēlnē ir atlasījis opciju “Visi”. Ja ir atlasīts Visi, tiek parādīta visa datu kopa.

Ieslēdziet/izslēdziet Excel automātisko filtru, izmantojot VBA

Lietojot automātisko filtru šūnu diapazonam, iespējams, jau ir ievietoti daži filtri.

Varat izmantot tālāk norādīto kodu, lai izslēgtu visus iepriekš lietotos automātiskos filtrus.

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

Šis kods pārbauda visas lapas un noņem visus lietotos filtrus.

Ja nevēlaties izslēgt filtrus no visas lapas, bet tikai no konkrētas datu kopas, izmantojiet tālāk norādīto kodu.

Sub TurnOFFAutoFiltrēt

Iepriekš minētais kods pārbauda, ​​vai filtri jau ir ievietoti.

Ja filtri jau ir lietoti, tas tos noņem, citādi tas neko nedara.

Līdzīgi, ja vēlaties ieslēgt automātisko filtru, izmantojiet tālāk norādīto kodu.

Sub TurnOnAutoFilter () Ja nav darblapas ("Sheet1"). Diapazons ("A4"). AutoFilter, pēc tam darblapas ("Sheet1"). Diapazons ("A4"). AutoFilter End if End Sub

Pārbaudiet, vai automātiskais filtrs jau ir lietots

Ja jums ir lapa ar vairākām datu kopām un vēlaties pārliecināties, ka zināt, ka filtru jau nav, varat izmantot tālāk norādīto kodu.

Sub CheckforFilters () Ja ActiveSheet.AutoFilterMode = True, tad MsgBox "Jau ir ievietoti filtri" Cits MsgBox "Nav filtru" Beigt, ja beigas Sub

Šis kods izmanto ziņojumu lodziņa funkciju, kas, atrodot filtrus lapā, parāda ziņojumu “Jau ir ievietoti filtri”, citādi tas parāda “Nav filtru”.

Rādīt visus datus

Ja datu kopai esat lietojis filtrus un vēlaties parādīt visus datus, izmantojiet tālāk norādīto kodu.

Sub ShowAllData () Ja ActiveSheet.FilterMode Tad ActiveSheet.ShowAllData beigu apakšdaļa

Iepriekš minētais kods pārbauda, ​​vai FilterMode ir TRUE vai FALSE.

Ja tā ir taisnība, tas nozīmē, ka ir izmantots filtrs un visu datu parādīšanai tiek izmantota ShowAllData metode.

Ņemiet vērā, ka tas nenoņem filtrus. Filtra ikonas joprojām ir pieejamas izmantošanai.

Automātiskā filtra izmantošana aizsargātajās lapās

Pēc noklusējuma, aizsargājot lapu, filtri nedarbosies.

Ja jums jau ir ievietoti filtri, varat iespējot automātisko filtru, lai pārliecinātos, ka tas darbojas pat aizsargātās lapās.

Lai to izdarītu, pārbaudiet opciju Izmantot automātisko filtru, vienlaikus aizsargājot lapu.

Lai gan tas darbojas, ja jums jau ir ievietoti filtri, ja mēģināsit pievienot automātiskos filtrus, izmantojot VBA kodu, tas nedarbosies.

Tā kā lapa ir aizsargāta, tas neļautu darboties nevienam makro un veikt izmaiņas automātiskajā filtrā.

Tāpēc jums ir jāizmanto kods, lai aizsargātu darblapu un pārliecinātos, ka tajā ir iespējoti automātiskie filtri.

Tas var būt noderīgi, ja esat izveidojis dinamisku filtru (kaut ko es apskatīju piemērā - “Filtrēt datus, pamatojoties uz šūnas vērtību”).

Zemāk ir kods, kas aizsargās lapu, bet tajā pašā laikā ļaus tajā izmantot filtrus, kā arī VBA makro.

Privāta apakšdarbgrāmata_atvērt () ar darblapām ("Sheet1") .EnableAutoFilter = True .Protect Password: = "password", Contents: = True, UserInterfaceOnly: = Patiess beigas ar beigu apakšdaļu

Šis kods ir jāievieto ThisWorkbook koda logā.

Tālāk ir norādītas darbības, kā ievietot kodu šī darbgrāmatas koda logā:

1. Atveriet VB redaktoru (īsinājumtaustiņš - ALT + F11).
2. Project Explorer rūtī veiciet dubultklikšķi uz objekta ThisWorkbook.
3. Atvērtajā koda logā nokopējiet un ielīmējiet iepriekš minēto kodu.

Tiklīdz atverat darbgrāmatu un iespējojat makro, tā automātiski izpildīs makro un aizsargās lapu1.

Tomēr, pirms to izdarīt, tas norādīs “EnableAutoFilter = True”, kas nozīmē, ka filtri darbosies arī aizsargātajā lapā.

Turklāt tiek iestatīts arguments “UserInterfaceOnly” uz “True”. Tas nozīmē, ka, kamēr darblapa ir aizsargāta, VBA makro kods turpinās darboties.

Jums varētu patikt arī šādas VBA apmācības:

• Excel VBA cilpas.
• Filtrējiet šūnas ar drosmīgu fontu formatējumu.
• Makro ierakstīšana.
• Kārtot datus, izmantojot VBA.
• Kārtot darblapas cilnes programmā Excel.