Екцел ВБА аутофилтер: Потпуни водич са примерима

Доста Екцел функција је такође доступно за употребу у ВБА - и Аутофилтер метода је једна таква функционалност.

Ако имате скуп података и желите да га филтрирате помоћу критеријума, то можете лако учинити помоћу опције Филтер на траци са подацима.

А ако желите његову напреднију верзију, постоји напредни филтер и у Екцелу.

Зашто онда уопште користити аутофилтер у ВБА?

Ако само требате филтрирати податке и обавити неке основне ствари, препоручио бих да се придржавате уграђене функције филтера коју нуди Екцел интерфејс.

Требали бисте користити ВБА Аутофилтер када желите да филтрирате податке као део ваше аутоматизације (или ако вам помаже да уштедите време тако што ћете убрзати филтрирање података).

На пример, претпоставимо да желите брзо да филтрирате податке на основу падајућег избора, а затим да ове филтриране податке копирате у нови радни лист.

Иако се то може учинити помоћу уграђене функције филтера заједно са копирањем и лепљењем, може вам требати много времена да то учините ручно.

У таквом сценарију, коришћење ВБА Аутофилтера може убрзати ствари и уштедети време.

Белешка: Овај пример (о филтрирању података на основу падајућег избора и копирању у нови лист) ћу описати касније у овом водичу.

Синтакса Екцел ВБА аутофилтера

Израз. Аутофилтер (_Фиелд_, _Цритериа1_, _Оператор_, _Цритериа2_, _ВисиблеДропДовн_)

• Израз: Ово је опсег на који желите да примените аутоматски филтер.
• Поље: [Опциони аргумент] Ово је број колоне који желите да филтрирате. Ово се рачуна са леве стране у скупу података. Дакле, ако желите да филтрирате податке на основу друге колоне, ова вредност би била 2.
• Критеријуми1: [Опциони аргумент] Ово су критеријуми на основу којих желите да филтрирате скуп података.
• Оператер: [Опциони аргумент] У случају да користите и критеријум 2, можете да комбинујете ова два критеријума на основу оператора. Доступни су следећи оператори: клАнд, клОр, клБоттом10Итемс, клТоп10Итемс, клБоттом10Перцент, клТоп10Перцент, клФилтерЦеллЦолор, клФилтерДинамиц, клФилтерФонтЦолор, клФилтерИцон, клФилтерВалуес
• Критеријуми2: [Опциони аргумент] Ово је други критеријум на основу којег можете филтрирати скуп података.
• ВисиблеДропДовн: [Опциони аргумент] Можете да наведете да ли желите да се падајућа икона филтера приказује у филтрираним колонама или не. Овај аргумент може бити ТРУЕ или ФАЛСЕ.

Осим Израза, сви други аргументи су опционални.

У случају да не користите ниједан аргумент, он би једноставно применио или уклонио иконе филтера у колоне.

Суб ФилтерРовс () Радни листови ("Филтер Дата"). Распон ("А1"). АутоФилтер Енд Суб

Горњи код би једноставно применио методу аутоматског филтрирања на колоне (или ако је већ примењен, уклониће га).

То једноставно значи да ако не видите иконе филтера у заглављима колона, почећете да их видите када се изврши овај горњи код, а ако га видите, биће уклоњен.

У случају да имате филтриране податке, уклониће филтере и приказати вам комплетан скуп података.

Погледајмо сада неке примере коришћења Екцел ВБА аутофилтера који ће његову употребу учинити јасном.

Пример: Филтрирање података на основу текстуалног стања

Претпоставимо да имате скуп података као што је приказано у наставку и желите да га филтрирате на основу колоне „Ставка“.

Код испод би филтрирао све редове у којима је ставка „штампач“.

Суб ФилтерРовс () Радни листови ("Схеет1"). Распон ("А1"). АутоФилтер Фиелд: = 2, Цритериа1: = "Принтер" Енд Суб

Горњи код се односи на Схеет1, а унутар њега се односи на А1 (која је ћелија у скупу података).

Имајте на уму да смо овде користили поље: = 2, јер је колона ставке друга колона у нашем скупу података са леве стране.

Сада ако размишљате - зашто морам то да радим помоћу ВБА кода. То се лако може учинити помоћу уграђене функције филтера.

У праву си!

Ако је ово све што желите да урадите, боље користите уграђену функцију филтера.

Али док читате преостали водич, видећете да се ово може комбиновати са неким додатним кодом за стварање моћне аутоматизације.

Али пре него што вам их покажем, дозволите ми да прво обухватим неколико примера да вам покажем шта све метода аутоматског филтрирања може да уради.

Кликните овде да преузмете датотеку примера и следите је.

Пример: Више критеријума (И/ИЛИ) у истој колони

Претпоставимо да имам исти скуп података, и овај пут желим да филтрирам све записе у којима је ставка „Штампач“ или „Пројектор“.

Код испод би ово урадио:

Суб ФилтерРовсОР () Радни листови ("Схеет1"). Распон ("А1"). Поље аутофилтера: = 2, Цритериа1: = "Штампач", Оператер: = клОр, Цритериа2: = "Пројектор" Енд Суб

Имајте на уму да сам овде користио клОР оператер.

Ово говори ВБА да користи оба критеријума и филтрира податке ако су неки од два критеријума испуњени.

Слично, можете користити и критеријуме АНД.

На пример, ако желите да филтрирате све записе у којима је количина већа од 10, али мања од 20, можете користити доњи код:

Суб ФилтерРовсАНД () Радни листови ("Схеет1"). Опсег ("А1"). Поље аутофилтера: = 4, Цритериа1: = "> 10", _ Оператор: = клАнд, Цритериа2: = "<20" Енд Суб

Пример: Више критеријума са различитим колонама

Претпоставимо да имате следећи скуп података.

Помоћу аутоматског филтера можете филтрирати више колона истовремено.

На пример, ако желите да филтрирате све записе у којима је ставка „Штампач“, а продајни представник „Означи“, можете користити доњи код:

Суб ФилтерРовс () Витх Ворксхеетс ("Схеет1"). Ранге ("А1") .АутоФилтер фиелд: = 2, Цритериа1: = "Принтер" .АутоФилтер фиелд: = 3, Цритериа1: = "Марк" Енд витх Енд Суб

Пример: Филтрирајте 10 најбољих записа помоћу методе аутоматског филтрирања

Претпоставимо да имате скуп података испод.

Испод је код који ће вам дати првих 10 записа (на основу колоне количине):

Под ФилтерРовсТоп10 () АцтивеСхеет.Ранге ("А1"). Поље аутофилтера: = 4, Критеријуми1: = "10", Оператор: = клТоп10Итемс Крај под

У горњем коду сам користио АцтивеСхеет. Можете користити назив листа ако желите.

Имајте на уму да у овом примеру, ако желите да добијете првих 5 ставки, само промените број Критеријуми1: = ”10 ″ од 10 до 5.

Дакле, за првих 5 ставки код би био:

Суб ФилтерРовсТоп5 () АцтивеСхеет.Ранге ("А1"). Поље аутофилтера: = 4, Критеријуми1: = "5", Оператор: = клТоп10Итемс Крај под

Можда изгледа чудно, али без обзира колико врхунских ставки желите, вредност Оператор увек остаје клТоп10Итемс.

Слично, доњи код би вам дао доњих 10 ставки:

Суб ФилтерРовсБоттом10 () АцтивеСхеет.Ранге ("А1"). Поље аутофилтера: = 4, Критеријуми1: = "10", Оператор: = клБоттом10Итемс Енд Суб

А ако желите доњих 5 ставки, промените број Критеријуми1: = ”10 ″ од 10 до 5.

Пример: Топ 10 процената филтрирања помоћу методе аутоматског филтрирања

Претпоставимо да имате исти скуп података (као што је коришћен у претходним примерима).

Испод је код који ће вам дати првих 10 посто записа (на основу колоне количине):

Суб ФилтерРовсТоп10 () АцтивеСхеет.Ранге ("А1"). Поље аутофилтера: = 4, Критеријуми1: = "10", Оператор: = клТоп10Перцент Енд Суб

У нашем скупу података, пошто имамо 20 записа, он ће вратити прва 2 записа (што је 10% од укупног броја записа).

Пример: Коришћење џокер знакова у Аутофилтеру

Претпоставимо да имате скуп података као што је приказано испод:

Ако желите да филтрирате све редове у којима назив ставке садржи реч „табла“, можете користити доњи код:

Суб ФилтерРовсВилдцард () Радни листови ("Схеет1"). Распон ("А1"). Поље аутоматског филтрирања: = 2, Цритериа1: = "*Боард*" Енд Суб

У горњем коду користио сам џокер знак * (звездица) пре и после речи „Одбор“ (што је критеријум).

Звездица може представљати било који број знакова. Дакле, ово би филтрирало сваку ставку која у себи има реч „табла“.

Пример: Копирајте филтриране редове у нови лист

Ако желите не само да филтрирате записе на основу критеријума, већ и да копирате филтриране редове, можете користити доњи макро.

Копира филтриране редове, додаје нови радни лист, а затим ове копиране редове залепи у нови лист.

Суб ЦопиФилтередРовс () Дим рнг Ас Ранге Дим вс ас Воркхеет Иф Ворксхеетс ("Схеет1"). АутоФилтерМоде = Фалсе тхен МсгБок "Нема филтрираних редова" Изађи из подзавршавања ако је постављено рнг = Ворксхеетс ("Схеет1"). АутоФилтер.Ранге Сет вс = Радни листови.Додати рнг.Копирај опсег ("А1") Крај под

Горњи код би проверио да ли постоје неки филтрирани редови у Схеет1 или не.

Ако нема филтрираних редова, приказаће се оквир за поруку у коме се то наводи.

А ако постоје филтрирани редови, он ће их копирати, уметнути нови радни лист и залепити ове редове на тај ново уметнути радни лист.

Пример: Филтрирајте податке на основу вредности ћелије

Користећи Аутофилтер у ВБА заједно са падајућом листом, можете креирати функцију у којој се, чим изаберете ставку са падајућег менија, филтрирају сви записи за ту ставку.

Нешто као што је приказано испод:

Кликните овде да преузмете датотеку примера и пратите је.

Ова врста конструкције може бити корисна када желите брзо филтрирати податке, а затим их даље користити у свом раду.

Испод је код који ће то учинити:

Привате Суб Ворксхеет_Цханге (БиВал Таргет Ас Ранге) Иф Таргет.Аддресс = "$ Б $ 2" Тхен Иф Ранге ("Б2") = "Алл" Тхен Ранге ("А5"). АутоФилтер Елсе Ранге ("А5"). АутоФилтер Фиелд : = 2, Критеријуми1: = Опсег ("Б2") Енд Иф Енд Иф Енд Суб

Ово је код догађаја радног листа, који се извршава само када дође до промене на радном листу и циљна ћелија је Б2 (где имамо падајући мени).

Такође, услов Иф Тхен Елсе се користи за проверу да ли је корисник са падајућег менија изабрао „Све“. Ако је изабрано Све, приказује се цео скуп података.

Овај код се НЕ ставља у модул.

Уместо тога, потребно га је поставити у позадину радног листа који садржи ове податке.

Ево корака за стављање овог кода у прозор кода радног листа:

1. Отворите ВБ Едитор (тастерска пречица - АЛТ + Ф11).
2. У окну Пројецт Екплорер двапут кликните на назив радног листа у којем желите ову функцију филтрирања.
3. У прозору кода радног листа копирајте и залепите горњи код.
4. Затворите ВБ Едитор.

Сада када користите падајућу листу, она ће аутоматски филтрирати податке.

Ово је код догађаја радног листа, који се извршава само када дође до промене на радном листу и циљна ћелија је Б2 (где имамо падајући мени).

Такође, услов Иф Тхен Елсе се користи за проверу да ли је корисник са падајућег менија изабрао „Све“. Ако је изабрано Све, приказује се цео скуп података.

Укључите/искључите Екцел АутоФилтер помоћу ВБА

Када примењујете Аутофилтер на низ ћелија, можда су већ постављени неки филтри.

Помоћу доњег кода можете искључити све претходно примењене аутоматске филтере:

Суб ТурнОФФАутоФилтер () Радни листови ("Схеет1"). АутоФилтерМоде = Фалсе Енд Суб

Овај код проверава целе листове и уклања све филтере који су примењени.

Ако не желите да искључите филтере са целог листа, већ само из одређеног скупа података, користите доњи код:

Суб ТурнОФФАутоФилтер () Иф Ворксхеетс ("Схеет1"). Ранге ("А1"). АутоФилтер тхен Ворксхеетс ("Схеет1"). Ранге ("А1"). АутоФилтер Енд Иф Енд Суб

Горњи код проверава да ли већ постоје филтери или не.

Ако су филтери већ примењени, уклања их, иначе ништа не ради.

Слично, ако желите да укључите Аутофилтер, користите доњи код:

Суб ТурнОнАутоФилтер () Иф Нот Ворксхеетс ("Схеет1"). Ранге ("А4"). АутоФилтер тхен Ворксхеетс ("Схеет1"). Ранге ("А4"). АутоФилтер Енд Иф Енд Суб

Проверите да ли је аутофилтер већ примењен

Ако имате лист са више скупова података и желите да будете сигурни да знате да већ нема филтера, можете користити доњи код.

Суб ЦхецкфорФилтерс () Иф АцтивеСхеет.АутоФилтерМоде = Труе тхен МсгБок "Већ постоје филтери" Елсе МсгБок "Нема филтера" Енд Иф Енд Суб

Овај код користи функцију оквира за поруке која приказује поруку „Већ постоје филтери“ када пронађе филтере на листу, иначе приказује „Нема филтера“.

Прикажи све податке

Ако имате филтере примењене на скуп података и желите да прикажете све податке, користите доњи код:

Суб СховАллДата () Иф АцтивеСхеет.ФилтерМоде тхен АцтивеСхеет.СховАллДата Енд Суб

Горњи код проверава да ли је ФилтерМоде ТРУЕ или ФАЛСЕ.

Ако је тачно, то значи да је примењен филтер и користи методу СховАллДата за приказ свих података.

Имајте на уму да ово не уклања филтере. Иконе филтера су и даље доступне за употребу.

Коришћење аутоматског филтрирања на заштићеним листовима

Када заштитите лист, подразумевано филтери неће радити.

У случају да већ имате постављене филтере, можете омогућити АутоФилтер како бисте били сигурни да ради чак и на заштићеним листовима.

Да бисте то урадили, означите опцију Користи аутофилтер док штитите лист.

Иако ово функционише када већ имате филтере, у случају да покушате да додате аутоматске филтере помоћу ВБА кода, то неће радити.

Пошто је лист заштићен, не би дозволио покретање макроа и измене аутоматског филтера.

Зато морате да користите код за заштиту радног листа и уверите се да су у њему омогућени аутоматски филтри.

Ово може бити корисно када сте креирали динамички филтер (нешто што сам описао у примеру - „Филтрирај податке на основу вредности ћелије“).

Испод је код који ће заштитити лист, али вам истовремено омогућити употребу филтера, као и ВБА макронаредби у њему.

Привате Суб Воркбоок_Опен () Витх Ворксхеетс ("Схеет1") .ЕнаблеАутоФилтер = Труе. Лозинка за заштиту: = "пассворд", Садржај: = Труе, УсерИнтерфацеОнли: = Труе Енд Витх Енд Суб

Овај код треба поставити у прозор кода ТхисВоркбоок.

Ево корака за постављање кода у прозор кода ТхисВоркбоок:

1. Отворите ВБ Едитор (тастерска пречица - АЛТ + Ф11).
2. У окну Пројецт Екплорер двапут кликните на објект ТхисВоркбоок.
3. У прозору кода који се отвори копирајте и залепите горњи код.

Чим отворите радну свеску и омогућите макрое, она ће аутоматски покренути макро и заштитити Схеет1.

Међутим, пре него што то учини, он ће навести „ЕнаблеАутоФилтер = Труе“, што значи да би филтри радили и у заштићеном листу.

Такође, поставља аргумент „УсерИнтерфацеОнли“ на „Тачно“. То значи да ће, док је радни лист заштићен, код ВБА макронаредби наставити да ради.

Можда ће вам се допасти и следећи ВБА водичи:

• Екцел ВБА петље.
• Филтрирајте ћелије са подебљаним фонтом.
• Снимање макроа.
• Сортирајте податке помоћу ВБА.
• Сортирајте картице радног листа у програму Екцел.