Налични са и много функционалности на Excel за използване във VBA - и Автофилтър методът е една такава функционалност.
Ако имате набор от данни и искате да го филтрирате по критерий, можете лесно да го направите, като използвате опцията Филтър в лентата с данни.
И ако искате по -усъвършенствана версия, има разширен филтър и в Excel.
Тогава защо дори да използвате автофилтъра във VBA?
Ако просто трябва да филтрирате данни и да направите някои основни неща, бих препоръчал да се придържате към вградената функционалност на филтъра, която предлага интерфейсът на Excel.
Трябва да използвате VBA Autofilter, когато искате да филтрирате данните като част от вашата автоматизация (или ако ви помага да спестите време, като ускорите филтрирането на данните).
Да предположим например, че искате бързо да филтрирате данните въз основа на падащо меню и след това да копирате тези филтрирани данни в нов работен лист.
Въпреки че това може да се направи с помощта на вградената функционалност на филтъра, заедно с някои копиращи / поставени, може да ви отнеме много време, за да направите това ръчно.
При такъв сценарий използването на VBA Autofilter може да ускори нещата и да спести време.
Забележка: Ще разгледам този пример (за филтриране на данни въз основа на падащ списък и копиране в нов лист) по-късно в този урок.
Синтаксис на автофилтър на Excel VBA
Израз. Автофилтър (_Field_, _Criteria1_, _Operator_, _Criteria2_, _VisibleDropDown_)
- Израз: Това е диапазонът, в който искате да приложите автоматичния филтър.
- Поле: [Незадължителен аргумент] Това е номерът на колоната, който искате да филтрирате. Това се брои отляво в набора от данни. Така че, ако искате да филтрирате данни въз основа на втората колона, тази стойност ще бъде 2.
- Критерии 1: [Незадължителен аргумент] Това са критериите, въз основа на които искате да филтрирате набора от данни.
- Оператор: [Незадължителен аргумент] В случай, че използвате и критерий 2, можете да комбинирате тези два критерия въз основа на оператора. На разположение са следните оператори: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
- Критерии 2: [Незадължителен аргумент] Това е вторият критерий, по който можете да филтрирате набора от данни.
- VisibleDropDown: [Незадължителен аргумент] Можете да посочите дали искате падащата икона на филтъра да се показва във филтрираните колони или не. Този аргумент може да бъде TRUE или FALSE.
Освен Expression, всички останали аргументи са по избор.
В случай, че не използвате никакъв аргумент, той просто ще приложи или премахне иконите на филтъра към колоните.
Sub FilterRows () Работни листове ("Filter Data"). Range ("A1"). Auto Sub Filter End Sub
Горният код просто би приложил метода на Autofilter към колоните (или ако вече е приложен, той ще го премахне).
Това просто означава, че ако не можете да видите иконите на филтъра в заглавките на колоните, ще започнете да го виждате, когато се изпълни горният код, и ако можете да го видите, той ще бъде премахнат.
В случай, че имате филтрирани данни, той ще премахне филтрите и ще ви покаже пълния набор от данни.
Сега нека видим някои примери за използване на Excel VBA Autofilter, които ще направят използването му ясно.
Пример: Филтриране на данни въз основа на условие за текст
Да предположим, че имате набор от данни, както е показано по -долу, и искате да го филтрирате въз основа на колоната „Елемент“.
Кодът по -долу ще филтрира всички редове, където елементът е „Принтер“.
Sub FilterRows () Работни листове ("Sheet1"). Диапазон ("A1"). Поле за автофилтър: = 2, Criteria1: = "Printer" End Sub
Горният код се отнася до Sheet1 и в него се отнася до A1 (който е клетка в набора от данни).
Обърнете внимание, че тук сме използвали Field: = 2, тъй като колоната с елементи е втората колона в нашия набор от данни отляво.
Сега, ако мислите - защо трябва да направя това с помощта на VBA код. Това може лесно да се направи с помощта на вградена функционалност на филтъра.
Прав си!
Ако това е всичко, което искате да направите, по -добре използвайте вградената функционалност на филтъра.
Но докато четете останалия урок, ще видите, че това може да се комбинира с допълнителен код, за да се създаде мощна автоматизация.
Но преди да ви ги покажа, нека първо да разгледам няколко примера, за да ви покажа какво може да направи всички методи на Автофилтър.
Натисни тук за да изтеглите примерния файл и да продължите.
Пример: Множество критерии (И/ИЛИ) в същата колона
Да предположим, че имам същия набор от данни и този път искам да филтрирам всички записи, където елементът е „Принтер“ или „Проектор“.
Кодът по -долу ще направи това:
Sub FilterRowsOR () Работни листове ("Sheet1"). Диапазон ("A1"). Поле на автофилтър: = 2, Criteria1: = "Принтер", Оператор: = xlOr, Criteria2: = "Проектор" End Sub
Обърнете внимание, че тук съм използвал xlOR оператор.
Това казва на VBA да използва и двата критерия и да филтрира данните, ако някой от двата критерия е изпълнен.
По същия начин можете да използвате и критериите И.
Например, ако искате да филтрирате всички записи, където количеството е повече от 10, но по -малко от 20, можете да използвате кода по -долу:
Sub FilterRowsAND () Работни листове ("Sheet1"). Диапазон ("A1"). Поле на автофилтър: = 4, Criteria1: = "> 10", _ Оператор: = xlAnd, Criteria2: = "<20" End Sub
Пример: Множество критерии с различни колони
Да предположим, че имате следния набор от данни.
С Автофилтър можете да филтрирате няколко колони едновременно.
Например, ако искате да филтрирате всички записи, където елементът е „Принтер“, а представителят по продажбите е „Маркирай“, можете да използвате кода по -долу:
Sub FilterRows () С работни листове ("Sheet1"). Диапазон ("A1"). Поле AutoFilter: = 2, Criteria1: = "Принтер".
Пример: Филтрирайте първите 10 записа, използвайки метода на автофилтър
Да предположим, че имате набора от данни по -долу.
По -долу е кодът, който ще ви даде първите 10 записа (въз основа на колоната за количество):
Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). Поле за автофилтър: = 4, Criteria1: = "10", Оператор: = xlTop10Items End Sub
В горния код използвах ActiveSheet. Можете да използвате името на листа, ако искате.
Обърнете внимание, че в този пример, ако искате да получите първите 5 елемента, просто променете номера в Критерии 1: = ”10 ″ от 10 до 5.
Така че за топ 5 елемента кодът ще бъде:
Sub FilterRowsTop5 () ActiveSheet.Range ("A1"). Поле за автофилтър: = 4, Criteria1: = "5", Оператор: = xlTop10Items End Sub
Може да изглежда странно, но без значение колко топ елементи искате, стойността Operator винаги остава xlTop10Items.
По същия начин кодът по -долу ще ви даде най -долните 10 елемента:
Sub FilterRowsBottom10 () ActiveSheet.Range ("A1"). Поле на автофилтър: = 4, Criteria1: = "10", Оператор: = xlBottom10Items End Sub
И ако искате долните 5 елемента, променете номера Критерии 1: = ”10 ″ от 10 до 5.
Пример: Филтрирайте топ 10 процента, използвайки метода на автофилтър
Да предположим, че имате същия набор от данни (както е използван в предишните примери).
По -долу е кодът, който ще ви даде първите 10 процента записи (въз основа на колоната за количество):
Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). Поле за автофилтър: = 4, Criteria1: = "10", Оператор: = xlTop10Percent End Sub
В нашия набор от данни, тъй като имаме 20 записа, той ще върне първите 2 записа (което е 10% от общите записи).
Пример: Използване на заместващи знаци в Автофилтър
Да предположим, че имате набор от данни, както е показано по -долу:
Ако искате да филтрирате всички редове, където името на елемента съдържа думата „Board“, можете да използвате кода по -долу:
Sub FilterRowsWildcard () Работни листове ("Sheet1"). Диапазон ("A1"). Поле за автофилтър: = 2, Criteria1: = "*Board*" End Sub
В горния код използвах заместващия знак * (звездичка) преди и след думата „Табло“ (което е критерият).
Звездичка може да представлява произволен брой знаци. Така че това ще филтрира всеки елемент, който има думата „дъска“ в него.
Пример: Копирайте филтрирани редове в нов лист
Ако искате не само да филтрирате записите въз основа на критерии, но и да копирате филтрираните редове, можете да използвате макроса по -долу.
Той копира филтрираните редове, добавя нов работен лист и след това поставя тези копирани редове в новия лист.
Sub CopyFilteredRows () Dim rng As Range Dim ws as Workheet If Worksheets ("Sheet1"). AutoFilterMode = False then MsgBox "Няма филтрирани редове" Излезте от Sub End, ако зададете rng = Worksheets ("Sheet1"). AutoFilter.Range Set ws = Работни листове. Добавете rng.Copy Range ("A1") End Sub
Горният код би проверил дали има филтрирани редове в Sheet1 или не.
Ако няма филтрирани редове, ще се покаже поле със съобщение, което гласи, че.
И ако има филтрирани редове, той ще ги копира, вмъкне нов работен лист и постави тези редове в този нововмъкнат работен лист.
Пример: Филтриране на данни въз основа на стойност на клетката
Използвайки Автофилтър във VBA заедно с падащ списък, можете да създадете функционалност, при която веднага щом изберете елемент от падащото меню, всички записи за този елемент се филтрират.
Нещо, както е показано по -долу:
Натисни тук за да изтеглите примерния файл и да продължите.
Този тип конструкция може да бъде полезна, когато искате бързо да филтрирате данни и след това да ги използвате допълнително в работата си.
По -долу е кодът, който ще направи това:
Private Sub Worksheet_Change (ByVal Target As Range) If Target.Address = "$ B $ 2" then If Range ("B2") = "All" then Range ("A5"). AutoFilter Else Range ("A5"). AutoFilter Field : = 2, Criteria1: = Range ("B2") End If End If End Sub
Това е код на събитие на работен лист, който се изпълнява само когато има промяна в работния лист и целевата клетка е B2 (където имаме падащото меню).
Също така се използва условие „Тогава след друго“, за да се провери дали потребителят е избрал „Всички“ от падащото меню. Ако е избрано Всичко, се показва целият набор от данни.
Този код НЕ се поставя в модул.
Вместо това той трябва да бъде поставен в задната част на работния лист, който съдържа тези данни.
Ето стъпките за поставяне на този код в кодовия прозорец на работния лист:
- Отворете VB Editor (клавишна комбинация - ALT + F11).
- В прозореца Project Explorer щракнете двукратно върху името на работния лист, в което искате тази функция за филтриране.
- В прозореца с код на работния лист копирайте и поставете горния код.
- Затворете редактора на VB.
Сега, когато използвате падащия списък, той автоматично ще филтрира данните.
Това е код на събитие на работен лист, който се изпълнява само когато има промяна в работния лист и целевата клетка е B2 (където имаме падащото меню).
Също така се използва условие „Тогава след друго“, за да се провери дали потребителят е избрал „Всички“ от падащото меню. Ако е избрано Всичко, се показва целият набор от данни.
Включете/изключете автофилтъра на Excel с помощта на VBA
Когато прилагате Автофилтър към диапазон от клетки, може вече да има някои филтри.
Можете да използвате кода по-долу, за да изключите всички предварително приложени автоматични филтри:
Sub TurnOFFAutoFilter () Работни листове ("Sheet1"). AutoFilterMode = Subsection False End
Този код проверява всички листове и премахва всички филтри, които са били приложени.
Ако не искате да изключите филтрите от целия лист, а само от конкретен набор от данни, използвайте кода по -долу:
Sub TurnOFFAutoFilter () If Worksheets ("Sheet1"). Range ("A1"). AutoFilter then Worksheets ("Sheet1"). Range ("A1"). AutoFilter End If End Sub
Горният код проверява дали вече има поставени филтри или не.
Ако филтрите вече са приложени, той го премахва, иначе не прави нищо.
По същия начин, ако искате да включите Автофилтър, използвайте кода по -долу:
Sub TurnOnAutoFilter () Ако не е работен лист ("Sheet1"). Диапазон ("A4"). AutoFilter Тогава работни листове ("Sheet1"). Диапазон ("A4"). AutoFilter прекратява, ако End Sub
Проверете дали автофилтърът вече е приложен
Ако имате лист с множество набори от данни и искате да сте сигурни, че знаете, че вече няма филтри, можете да използвате кода по -долу.
Sub CheckforFilters () If ActiveSheet.AutoFilterMode = True then MsgBox "Има филтри вече на място" Else MsgBox "Няма филтри" End If End Sub
Този код използва функция за поле за съобщения, която показва съобщение „Вече има филтри“, когато намери филтри на листа, иначе показва „Няма филтри“.
Показване на всички данни
Ако сте приложили филтри към набора от данни и искате да покажете всички данни, използвайте следния код:
Sub ShowAllData () Ако ActiveSheet.FilterMode След това ActiveSheet.ShowAllData End Sub
Горният код проверява дали FilterMode е TRUE или FALSE.
Ако е вярно, това означава, че е приложен филтър и той използва метода ShowAllData, за да покаже всички данни.
Имайте предвид, че това не премахва филтрите. Иконите на филтъра все още са достъпни за използване.
Използване на автофилтър върху защитени листове
По подразбиране, когато защитавате лист, филтрите няма да работят.
В случай, че вече имате поставени филтри, можете да активирате AutoFilter, за да се уверите, че работи дори на защитени листове.
За да направите това, проверете опцията Използване на автофилтър, като същевременно защитавате листа.
Въпреки че това работи, когато вече имате филтри, в случай че се опитате да добавите автофилтри с помощта на код VBA, той няма да работи.
Тъй като листът е защитен, той няма да позволи никакъв макрос да се изпълнява и да прави промени в автофилтъра.
Така че трябва да използвате код, за да защитите работния лист и да се уверите, че в него са активирани автоматичните филтри.
Това може да бъде полезно, когато сте създали динамичен филтър (нещо, което разгледах в примера - „Филтриране на данни въз основа на стойност на клетка“).
По -долу е кодът, който ще защитава листа, но в същото време ще ви позволи да използвате филтри, както и VBA макроси в него.
Private Sub Workbook_Open () С работни листове ("Sheet1") .EnableAutoFilter = True. Парола за защита: = "password", Съдържание: = True, UserInterfaceOnly: = True End With End Sub
Този код трябва да бъде поставен в прозореца с кодове на тази работна книга.
Ето стъпките за поставяне на кода в кодовия прозорец на тази работна книга:
- Отворете VB Editor (клавишна комбинация - ALT + F11).
- В прозореца Project Explorer щракнете двукратно върху обекта ThisWorkbook.
- В прозореца с кодове, който се отваря, копирайте и поставете горния код.
Веднага щом отворите работната книга и активирате макроси, тя ще стартира макроса автоматично и ще защити Sheet1.
Преди това обаче той ще посочи „EnableAutoFilter = True“, което означава, че филтрите ще работят и в защитения лист.
Също така, той задава аргумента „UserInterfaceOnly“ на „True“. Това означава, че докато работният лист е защитен, кодът за макроси на VBA ще продължи да работи.
Може да харесате и следните уроци по VBA:
- Цикли на Excel VBA.
- Филтрирайте клетки с удебелено форматиране на шрифтове.
- Запис на макрос.
- Сортиране на данни с помощта на VBA.
- Сортирайте разделите на работния лист в Excel.
