Макрос для фильтрации данных в excel

Обновлено: 07.01.2025

Фильтры являются отличным инструментом для анализа данных в Excel. Для большинства аналитиков и частых пользователей Excel фильтры являются частью нашей повседневной жизни. Мы используем раскрывающиеся меню фильтров для применения фильтров к отдельным столбцам в наборе данных. Это помогает нам связывать цифры с отчетами и проводить исследование наших данных.

Фильтрация также может быть трудоемким процессом. Особенно, когда мы применяем фильтры к нескольким столбцам на больших листах или фильтруем данные, чтобы затем копировать / вставлять их в другие листы или книги.

В этой статье объясняется, как создавать макросы для автоматизации процесса фильтрации. Это обширное руководство по методу автофильтра в VBA.

У меня также есть статьи с примерами для различных фильтров и типов данных, в том числе: пробелы, текст, числа, даты, цвета и значки, и очищающие фильтры.

Именованный диапазон для условий

Сначала надо создать именованный диапазон, куда мы будем вводить условия, и откуда макрос их будет брать. Для этого можно прямо над таблицей вставить пару-тройку пустых строк, затем выделить ячейки для будущих критериев (на рисунке это A2:F2) и дать им имя Условия, вписав его в поле имени в левом верхнем углу и нажав клавишу Enter. Для наглядности, я выделил эти ячейки желтым цветом:

Макро-рекордер — твой друг (или враг)

Мы можем легко получить код VBA для фильтров, включив макро-рекордер, а затем применив один или несколько фильтров к диапазону / таблице.

Вот шаги для создания макроса фильтра с помощью устройства записи макросов:

Включите рекордер макросов:

Вкладка «Разработчик»> «Запись макроса».
Дайте макросу имя, выберите, где вы хотите сохранить код, и нажмите ОК.

Если вы уже использовали макрос-рекордер для этого процесса, то вы знаете, насколько он может быть полезен. Тем более, что наши критерии фильтрации становятся более сложными.

Код будет выглядеть примерно так:

Мы видим, что каждая строка использует метод AutoFilter для применения фильтра к столбцу. Он также содержит информацию о критериях фильтра.

Как сделать фильтр в сводной таблице макросом

Чтобы получить данные для одного из 4-х магазинов фирмы, достаточно лишь открыть выпадающий список со всеми магазинами и выбрать один из них. Пример результата этих действий изображено на рисунке:

Изменение настроек, которые доступны в интерактивных инструментах сводной таблице доступны так же на уровне программирования макросов из редактора VBA. Выбор магазина, который является элементом поля СТРАИЦЫ реализуется с помощью свойства CurrentPage.

Просто как параметр для этого свойства следует указать название поля. Например, напишем простой код макроса, который сам выберет «Магазин3» как критерий для фильтрования данных по оборотам в сводной таблице:

Sub Magazin3()
ActiveSheet.PivotTables( "ТаблицаМ" ).PivotFields( "Магазины" ).CurrentPage = "Магазин 3"
End Sub

Чтобы создать такой макрос сначала откройте редактор VisualBasic (ALT+F11), а потом создайте новый модуль в редакторе: «Insert»-«Module» и введете в него выше указанный VBA-код:

Результат действия этого простого макроса такой же как выше рисунке, но без использования пользователем интерактивных инструментов сводной таблицы.

В методе PivotTables указан аргумент «ТаблицаМ» – это всего лишь ссылка на внутренне имя, которое было присвоено для сводной таблицы еще на этапе ее создания. Читайте пример создания где он детально описан: Макрос для создания сводной таблицы в Excel . Далее рассмотрим, как можно скрывать часть данных из области видимости значений.

Решение № 1: Макрос VBA для фильтрации сводной таблицы по определенной дате или периоду

Мы можем использовать простой макрос, чтобы установить фильтр в сводной таблице для самой последней даты в таблице исходных данных. Фильтрация поля «Дата отчета» в сводной таблице также выберет отфильтрованный элемент в срезе и отфильтрует все связанные сводные диаграммы.

Приведенный ниже макрос может выглядеть как много кода, но на самом деле он очень прост. Чтобы его использовать, вам просто нужно будет указать все переменные для имени рабочего листа, имени сводной таблицы, имени сводного поля (Дата отчета) и критериев фильтрации (последняя дата). Эти переменные будут представлять имена объектов в вашей собственной книге.

Макрос в настоящее время настроен на использование значения из ячейки G2 в Таблице данных для критериев фильтра (самая поздняя дата). Ячейка G2 содержит формулу, которая возвращает самую последнюю дату из столбца с помощью функции MAX

Конечно, это можно изменить, чтобы вычислить самую последнюю дату в коде макроса. Хотя приятно видеть это на листе.

Как работает макрос?

Макрос сначала очищает все фильтры для поля сводки фильтра отчетов с помощью метода ClearAllFilters.

Затем он использует цикл For Next для циклического прохождения всех элементов сводки в поле сводки, чтобы применить фильтр. Каждый уникальный элемент в поле является основным элементом. Макрос проверяет, не соответствует ли имя элемента сводки (<>) критериям. Если нет, то он скрывает элемент или фильтрует его. В результате мы видим только критерии фильтра.

Как насчет фильтрации двух сводных таблиц за разные периоды времени?

Вот еще один пример с настройкой переменных в качестве параметров макроса. Это позволяет нам вызывать макрос из другого макроса.

В этом примере у нас есть лист с двумя сводными таблицами для сравнения неделя за неделей. Таким образом, нам нужно отфильтровать одну сводную таблицу для самой последней даты и одну для даты предыдущей недели.

Макрос Filter_Multiple_Pivots вызывает макрос Filter_PivotField_Args дважды. Обратите внимание, что имя сводной таблицы и значения критериев фильтрации различны для каждого вызова. Это простой способ многократно использовать макрос сводного поля фильтра без необходимости повторения большого количества кода.

Настройка: данные конвейера продаж CRM

Для обновления данных мы экспортируем конвейерный отчет каждую неделю и вставляем данные в конец существующей таблицы. Есть и другие способы автоматизации этого процесса, но мы не будем вдаваться в подробности.

В наборе данных есть столбец «Дата отчета», в котором содержится дата запуска отчета для каждой строки. Вы можете видеть на изображении, что есть 4 набора данных, добавленных (сложенных) вместе, чтобы создать одну большую таблицу.

Сводная таблица показывает сводку доходов по этапам конвейера, а поле «Дата отчета» находится в области «Фильтры». Это позволяет нам фильтровать по любой дате отчета, чтобы увидеть сводную информацию о конвейере за эту неделю.

Когда новые данные добавляются в таблицу данных, мы хотим автоматически фильтровать все связанные сводные таблицы, диаграммы и срезы на последнюю дату отчета.

Как установить номер поля динамически

Если мы добавим / удалим / переместим столбцы в диапазоне фильтра, то номер поля для отфильтрованного столбца может измениться. Поэтому я стараюсь по возможности избегать жесткого кодирования числа для параметра Field.

Вместо этого мы можем использовать переменную и использовать некоторый код, чтобы найти номер столбца по его имени. Вот два примера для обычных диапазонов и таблиц.

Номер столбца будет найден при каждом запуске макроса. Нам не нужно беспокоиться об изменении номера поля при перемещении столбца. Это экономит время и предотвращает ошибки (беспроигрышный вариант)!

Добавляем макрос фильтрации

Теперь надо добавить к текущему листу макрос фильтрации по критериям из созданного диапазона Условия. Для этого щелкните правой кнопкой мыши по ярлычку листа и выберите команду Исходный текст (Source text) . В открывшееся окно редактора Visual Basic надо скопировать и вставить текст вот такого макроса:

Private Sub Worksheet_Change(ByVal Target As Range) Dim FilterCol As Integer Dim FilterRange As Range Dim CondtitionString As Variant Dim Condition1 As String, Condition2 As String If Intersect(Target, Range("Условия")) Is Nothing Then Exit Sub On Error Resume Next Application.ScreenUpdating = False 'определяем диапазон данных списка Set FilterRange = Target.Parent.AutoFilter.Range 'считываем условия из всех измененных ячеек диапазона условий For Each cell In Target.Cells FilterCol = cell.Column - FilterRange.Columns(1).Column + 1 If IsEmpty(cell) Then Target.Parent.Range(FilterRange.Address).AutoFilter Field:=FilterCol Else If InStr(1, UCase(cell.Value), " ИЛИ ") > 0 Then LogicOperator = xlOr ConditionArray = Split(UCase(cell.Value), " ИЛИ ") Else If InStr(1, UCase(cell.Value), " И ") > 0 Then LogicOperator = xlAnd ConditionArray = Split(UCase(cell.Value), " И ") Else ConditionArray = Array(cell.Text) End If End If 'формируем первое условие If Left(ConditionArray(0), 1) = "<" Or Left(ConditionArray(0), 1) = ">" Then Condition1 = ConditionArray(0) Else Condition1 = "=" & ConditionArray(0) End If 'формируем второе условие - если оно есть If UBound(ConditionArray) = 1 Then If Left(ConditionArray(1), 1) = "<" Or Left(ConditionArray(1), 1) = ">" Then Condition2 = ConditionArray(1) Else Condition2 = " 0" src="/images/upload/medialibrary/d26/d26aad041f645f5989744873c68403c1.jpg" width="686" height="213">

Как и в случае с классическими Автофильтром (Filter) и Расширенным фильтром (Advanced Filter) , в нашем фильтре макросом можно смело использовать символы подстановки:

и операторы логической связки:

и любые математические символы неравенства (>,<,=,>=,<=,<>).

При удалении содержимого ячеек желтого диапазона Условия автоматически снимается фильтрация с соответствующих столбцов.

Метод автофильтрации

Метод AutoFilter используется для очистки и применения фильтров к одному столбцу в диапазоне или таблице в VBA. Он автоматизирует процесс применения фильтров через выпадающие меню фильтров и делает, чтобы все работало.

Его можно использовать для применения фильтров к нескольким столбцам путем написания нескольких строк кода, по одной для каждого столбца. Мы также можем использовать Автофильтр, чтобы применить несколько критериев фильтрации к одному столбцу, так же, как в выпадающем меню фильтра, установив несколько флажков или указав диапазон дат.

У любого уважающего себя тренера всегда есть запас "вау-фишек" - простых, но эффектных приемов, эдаких killing-features для быстрого очарования сложной аудитории. В сводных таблицах одной из таких фишек, безусловно, является двойной щелчок левой кнопкой мыши по любому числу в области значений:

Если это сделать, то вас вынесет на новый лист, куда Excel выгрузит детализацию по данной ячейке - всю "подноготную", объясняющую как получилось данное значение, из чего оно сложилось:

Официально, эта процедура называется drill-down, неофициально ее обычно называют "провалиться".

Ключевой нюанс в том, что полученная в результате такого проваливания двойным щелчком таблица - это копия исходных данных, а не они сами. Полученная таблица абсолютно автономна и никак не связана ни с исходными данными, ни со сводной. Иногда это нам на руку - мы можем использовать ее для своих целей, менять ее и т.д.

Но порой возникает другое желание: а можно увидеть не копию исходных данных, а сами данные? То есть отфильтровать те самые строки в исходной таблице, которые участвуют в расчете данной ячейки? Их, например, можно было бы затем изменить, подкорректировав тем самым результат в сводной таблице.

Стандартными средствами такое невозможно, но для макросов пределы возможного в Excel существенно шире :)

Откройте редактор Visual Basic:

В Excel 2003 и старше для этого нужно выбрать в меню Сервис - Макрос - Редактор Visual Basic (Tools - Macro - Visual Basic Editor)
В новых версиях Excel 2007-2013 перейти на вкладку Разработчик (Developer) и нажать кнопку Visual Basic. Если такой вкладки у вас не видно, то включите ее в настройках Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon)

В окне редактора вставьте новый модуль через меню Insert - Module и скопируйте туда текст вот этих двух макросов:

Теперь, если выделить одну любую ячейку с данными в сводной таблице и запустить наш первый макрос FilterPivot с помощью сочетания клавиш Alt+F8 или через меню Сервис - Макрос - Макросы (Tools - Macro - Macros) , то мы перейдем на лист с исходными данными для сводной, где автоматически будут применены фильтры, отбирающие только те строки, которые участвовали в расчете текущей ячейки:

Теперь их можно, например, изменить, чтобы добиться в отчете сводной таблицы нужного результата. Только не забудьте обновить сводную после внесения изменений: правой кнопкой мыши - Обновить (Refresh) .

Второй макрос ShowAllData нужен, чтобы вернуть прежний вид исходной таблицы - он делает все строки на текущем листе видимыми. Для пущего удобства можно повесить эти два макроса на удобные вам сочетания клавиш, используя кнопку Параметры (Options) в окне Макросы, которое отображается по Alt+F8.

У подавляющего большинства пользователей Excel при слове "фильтрация данных" в голове всплывает только обычный классический фильтр с вкладки Данные - Фильтр (Data - Filter) :

Такой фильтр - штука привычная, спору нет, и для большинства случаев вполне сойдет. Однако бывают ситуации, когда нужно проводить отбор по большому количеству сложных условий сразу по нескольким столбцам. Обычный фильтр тут не очень удобен и хочется чего-то помощнее. Таким инструментом может стать расширенный фильтр (advanced filter), особенно с небольшой "доработкой напильником" (по традиции).

Основа

Для начала вставьте над вашей таблицей с данными несколько пустых строк и скопируйте туда шапку таблицы - это будет диапазон с условиями (выделен для наглядности желтым):

Между желтыми ячейками и исходной таблицей обязательно должна быть хотя бы одна пустая строка.

Именно в желтые ячейки нужно ввести критерии (условия), по которым потом будет произведена фильтрация. Например, если нужно отобрать бананы в московский "Ашан" в III квартале, то условия будут выглядеть так:

Чтобы выполнить фильтрацию выделите любую ячейку диапазона с исходными данными, откройте вкладку Данные и нажмите кнопку Дополнительно (Data - Advanced) . В открывшемся окне должен быть уже автоматически введен диапазон с данными и нам останется только указать диапазон условий, т.е. A1:I2:

Обратите внимание, что диапазон условий нельзя выделять "с запасом", т.е. нельзя выделять лишние пустые желтые строки, т.к. пустая ячейка в диапазоне условий воспринимается Excel как отсутствие критерия, а целая пустая строка - как просьба вывести все данные без разбора.

Переключатель Скопировать результат в другое место позволит фильтровать список не прямо тут же, на этом листе (как обычным фильтром), а выгрузить отобранные строки в другой диапазон, который тогда нужно будет указать в поле Поместить результат в диапазон. В данном случае мы эту функцию не используем, оставляем Фильтровать список на месте и жмем ОК. Отобранные строки отобразятся на листе:

Добавляем макрос

"Ну и где же тут удобство?" - спросите вы и будете правы. Мало того, что нужно руками вводить условия в желтые ячейки, так еще и открывать диалоговое окно, вводить туда диапазоны, жать ОК. Грустно, согласен! Но "все меняется, когда приходят они ©" - макросы!

Работу с расширенным фильтром можно в разы ускорить и упростить с помощью простого макроса, который будет автоматически запускать расширенный фильтр при вводе условий, т.е. изменении любой желтой ячейки. Щелкните правой кнопкой мыши по ярлычку текущего листа и выберите команду Исходный текст (Source Code) . В открывшееся окно скопируйте и вставьте вот такой код:

Эта процедура будет автоматически запускаться при изменении любой ячейки на текущем листе. Если адрес измененной ячейки попадает в желтый диапазон (A2:I5), то данный макрос снимает все фильтры (если они были) и заново применяет расширенный фильтр к таблице исходных данных, начинающейся с А7, т.е. все будет фильтроваться мгновенно, сразу после ввода очередного условия:

Так все гораздо лучше, правда? :)

Реализация сложных запросов

Теперь, когда все фильтруется "на лету", можно немного углубиться в нюансы и разобрать механизмы более сложных запросов в расширенном фильтре. Помимо ввода точных совпадений, в диапазоне условий можно использовать различные символы подстановки (* и ?) и знаки математических неравенств для реализации приблизительного поиска. Регистр символов роли не играет. Для наглядности я свел все возможные варианты в таблицу:

Критерий	Результат
гр* или гр	все ячейки начинающиеся с Гр , т.е. Груша, Грейпфрут, Гранат и т.д.
=лук	все ячейки именно и только со словом Лук, т.е. точное совпадение
лив или *лив	ячейки содержащие лив как подстроку, т.е. Оливки, Ливер, Залив и т.д.
=п*в	слова начинающиеся с П и заканчивающиеся на В т.е. Павлов, Петров и т.д.
а*с	слова начинающиеся с А и содержащие далее С , т.е. Апельсин, Ананас, Асаи и т.д.
=*с	слова оканчивающиеся на С
=.	все ячейки с текстом из 4 символов (букв или цифр, включая пробелы)
=м. н	все ячейки с текстом из 8 символов, начинающиеся на М и заканчивающиеся на Н , т.е. Мандарин, Мангостин и т.д.
=*н??а	все слова оканчивающиеся на А , где 4-я с конца буква Н , т.е. Брусника, Заноза и т.д.
>=э	все слова, начинающиеся с Э , Ю или Я
<>о	все слова, не содержащие букву О
<>*вич	все слова, кроме заканчивающихся на вич (например, фильтр женщин по отчеству)
=	все пустые ячейки
<>	все непустые ячейки
>=5000	все ячейки со значением больше или равно 5000
5 или =5	все ячейки со значением 5
>=3/18/2013	все ячейки с датой позже 18 марта 2013 (включительно)

Знак * подразумевает под собой любое количество любых символов, а ? - один любой символ.
Логика в обработке текстовых и числовых запросов немного разная. Так, например, ячейка условия с числом 5 не означает поиск всех чисел, начинающихся с пяти, но ячейка условия с буквой Б равносильна Б*, т.е. будет искать любой текст, начинающийся с буквы Б.
Если текстовый запрос не начинается со знака =, то в конце можно мысленно ставить *.
Даты надо вводить в штатовском формате месяц-день-год и через дробь (даже если у вас русский Excel и региональные настройки).

Логические связки И-ИЛИ

Условия записанные в разных ячейках, но в одной строке - считаются связанными между собой логическим оператором И (AND) :

Т.е. фильтруй мне бананы именно в третьем квартале, именно по Москве и при этом из "Ашана".

Если нужно связать условия логическим оператором ИЛИ (OR) , то их надо просто вводить в разные строки. Например, если нам нужно найти все заказы менеджера Волиной по московским персикам и все заказы по луку в третьем квартале по Самаре, то это можно задать в диапазоне условий следующим образом:

Если же нужно наложить два или более условий на один столбец, то можно просто продублировать заголовок столбца в диапазоне критериев и вписать под него второе, третье и т.д. условия. Вот так, например, можно отобрать все сделки с марта по май:

В общем и целом, после "доработки напильником" из расширенного фильтра выходит вполне себе приличный инструмент, местами не хуже классического автофильтра.

Итог: узнайте, как применять фильтры для пустых ячеек с VBA. Включает примеры фильтрации пустых и непустых ячеек с использованием метода автофильтра.

Уровень мастерства: Средний

Файл Excel, содержащий код, можно скачать ниже. Этот файл содержит код для фильтрации различных типов данных и типов фильтров. Пожалуйста, ознакомьтесь с моей статьей Фильтрация сводной таблицы или среза по самой последней дате или периоду для более подробной информации.

VBA AutoFilters Guide.xlsm (100.5 KB)

Фильтр для пустых ячеек

Мы также можем отфильтровать ячейки без пропусков, сняв флажок (Пробелы).

Следующий макрос содержит примеры фильтрации пустых и непустых ячеек (исключая пустые). Пожалуйста, ознакомьтесь с моей статьей Фильтрация сводной таблицы или среза по самой последней дате или периоду для получения более подробной информации о том, как использовать метод AutoFilter и его параметры.

Образцы кода VBA для текстовых фильтров

Код в поле ниже можно скопировать / вставить в VB Editor.

Фильтры и типы данных

Параметры раскрывающегося меню фильтра изменяются в зависимости от типа данных в столбце. У нас есть разные фильтры для текста, чисел, дат и цветов. Это создает МНОГО различных комбинаций операторов и критериев для каждого типа фильтра.

Я создал отдельные статьи для каждого из этих типов фильтров. Статьи содержат пояснения и примеры кода VBA.

Как очистить фильтры с помощью VBA
Как фильтровать текст с помощью VBA
Как фильтровать числа с помощью VBA
Как отфильтровать даты по VBA
Как отфильтровать цвета и значки с помощью VBA

Файл в разделе загрузок выше содержит все эти примеры кода в одном месте. Вы можете добавить его в свою личную книгу макросов и использовать макросы в своих проектах.

Пожалуйста, оставьте комментарий ниже с любыми вопросами или предложениями. Спасибо!

Итог: научиться создавать макросы, использовать фильтры на диапазоны и таблицы с помощью метода AutoFilter VBA. Статья содержит ссылки на примеры для фильтрации различных типов данных, включая текст, цифры, даты, цвета и значки.

Уровень мастерства: средний

Файл Excel, содержащий код, можно скачать ниже. Этот файл содержит код для фильтрации различных типов данных и типов фильтров.

VBA AutoFilters Guide.xlsm (100.5 KB)

Написание макросов для фильтров

Вот шаги для создания макроса фильтра с помощью устройства записи макросов:

Включите рекордер макросов:
1. Вкладка «Разработчик»> «Запись макроса».
2. Дайте макросу имя, выберите, где вы хотите сохранить код, и нажмите ОК.
Если вы уже использовали макрос-рекордер для этого процесса, то вы знаете, насколько он может быть полезен. Тем более, что наши критерии фильтрации становятся более сложными.

Код будет выглядеть примерно так:

Мы видим, что каждая строка использует метод AutoFilter для применения фильтра к столбцу. Он также содержит информацию о критериях фильтра.

Мы видим, что каждая строка использует метод AutoFilter для применения фильтра к столбцу. Он также содержит информацию о критериях фильтра.

Метод автофильтрации

Метод AutoFilter используется для очистки и применения фильтров к одному столбцу в диапазоне или таблице в VBA. Он автоматизирует процесс применения фильтров через выпадающие меню фильтров и делает, чтобы все работало.

Его можно использовать для применения фильтров к нескольким столбцам путем написания нескольких строк кода, по одной для каждого столбца. Мы также можем использовать Автофильтр, чтобы применить несколько критериев фильтрации к одному столбцу, так же, как в выпадающем меню фильтра, установив несколько флажков или указав диапазон дат.

Написание кода автофильтра

Вот пошаговые инструкции по написанию строки кода для автофильтра.

Шаг 1: Ссылка на диапазон или таблицу

Метод AutoFilter является частью объекта Range. Поэтому мы должны ссылаться на диапазон или таблицу, к которым применяются фильтры на листе. Это будет весь диапазон, к которому применяются фильтры.

Следующие примеры включают / отключают фильтры в диапазоне B3: G1000 на листе автофильтра.

Вот пример использования таблиц Excel.

Метод AutoFilter имеет 5 необязательных параметров, которые мы рассмотрим далее. Если мы не укажем ни один из параметров, как в приведенных выше примерах, метод AutoFilter включит / выключит фильтры для указанного диапазона. Это переключение. Если фильтры включены, они будут выключены, и наоборот.

Диапазоны или таблицы?

Фильтры работают одинаково как для обычных диапазонов, так и для таблиц Excel.

Я отдаю предпочтение методу использования таблиц, потому что нам не нужно беспокоиться об изменении ссылок на диапазон при увеличении или уменьшении таблицы. Однако код будет одинаковым для обоих объектов. В остальных примерах кода используются таблицы Excel, но вы можете легко изменить это для обычных диапазонов.

5 (или 6) параметров автофильтра

Метод AutoFilter имеет 5 (или 6) необязательных параметров, которые используются для указания критериев фильтрации для столбца. Вот список параметров.

Мы можем использовать комбинацию этих параметров, чтобы применять различные критерии фильтрации для разных типов данных. Первые четыре являются наиболее важными, поэтому давайте посмотрим, как их применять.

Шаг 2: Параметр поля

В приведенном ниже примере поле 4 является столбцом «Продукт», поскольку это 4-й столбец в диапазоне фильтра / таблице.

Фильтр столбца очищается, когда мы указываем только параметр Field, а другие критерии отсутствуют.

Мы также можем использовать переменную для параметра Field и установить ее динамически. Я объясню это более подробно ниже.

Шаг 3: Параметры критериев

Существует два параметра, которые можно использовать для указания фильтра Критерии, Criteria1 и Criteria2 . Мы используем комбинацию этих параметров и параметра Operator для разных типов фильтров. Здесь все становится сложнее, поэтому давайте начнем с простого примера.

Это то же самое, что выбрать один элемент из списка флажков в раскрывающемся меню фильтра.

Общие правила для Criteria1 и Criteria2

Значения, которые мы указываем для Criteria1 и Criteria2, могут быть хитрыми. Вот несколько общих рекомендаций о том, как ссылаться на значения параметра Criteria.
- Значением критерия является строка, заключенная в кавычки. Есть несколько исключений, когда критерии являются постоянными для периода времени даты и выше / ниже среднего.
- При указании фильтров для отдельных чисел или дат форматирование чисел должно соответствовать форматированию чисел, применяемому в диапазоне / таблице.
- Оператор сравнения больше / меньше чем также включен в кавычки перед числом.
- Кавычки также используются для фильтров для пробелов «=» и не пробелов «<>».
Шаг 4: Параметр оператора

Что если мы хотим выбрать несколько элементов из раскрывающегося списка фильтров? Или сделать фильтр для диапазона дат или чисел?

Для этого нам нужен Operator . Параметр Operator используется для указания типа фильтра, который мы хотим применить. Он может варьироваться в зависимости от типа данных в столбце. Для
Operator должна использоваться одна из следующих 11 констант.

Вот ссылка на страницу справки MSDN, которая содержит список констант для перечисления XlAutoFilterOperator.

Operator используется в сочетании с Criteria1 и / или Criteria2, в зависимости от типа данных и типа фильтра. Вот несколько примеров.

Это основы написания строки кода для метода AutoFilter. Будет сложнее с различными типами данных.

Итак, я привел много примеров ниже, которые содержат большинство комбинаций критериев и операторов для разных типов фильтров.

Автофильтр не является дополнением

При запуске строки кода автофильтра сначала удаляются все фильтры, примененные к этому столбцу (полю), а затем применяются критерии фильтра, указанные в строке кода.

Это означает, что это не дополнение. Следующие 2 строки НЕ создадут фильтр для Продукта 1 и Продукта 2. После запуска макроса столбец Продукт будет отфильтрован только для Продукта 2.

Если вы хотите применить фильтр с несколькими критериями к одному столбцу, вы можете указать это с помощью параметров
Criteria и Operator .

Как установить номер поля динамически

Если мы добавим / удалим / переместим столбцы в диапазоне фильтра, то номер поля для отфильтрованного столбца может измениться. Поэтому я стараюсь по возможности избегать жесткого кодирования числа для параметра Field.

Вместо этого мы можем использовать переменную и использовать некоторый код, чтобы найти номер столбца по его имени. Вот два примера для обычных диапазонов и таблиц.

Номер столбца будет найден при каждом запуске макроса. Нам не нужно беспокоиться об изменении номера поля при перемещении столбца. Это экономит время и предотвращает ошибки (беспроигрышный вариант)!

Используйте таблицы Excel с фильтрами

Использование таблиц Excel дает множество преимуществ, особенно при использовании метода автофильтрации. Вот несколько основных причин, по которым я предпочитаю таблицы.
- Нам не нужно переопределять диапазон в VBA, поскольку диапазон данных изменяет размер (строки / столбцы добавляются / удаляются). На всю таблицу ссылается объект ListObject.
- Данные в таблице легко ссылаться после применения фильтров. Мы можем использовать свойство DataBodyRange для ссылки на видимые строки для копирования / вставки, форматирования, изменения значений и т.д.
- Мы можем иметь несколько таблиц на одном листе и, следовательно, несколько диапазонов фильтров. С обычными диапазонами у нас может быть только один отфильтрованный диапазон на лист.
- Код для очистки всех фильтров в таблице легче написать.
Фильтры и типы данных

Параметры раскрывающегося меню фильтра изменяются в зависимости от типа данных в столбце. У нас есть разные фильтры для текста, чисел, дат и цветов. Это создает МНОГО различных комбинаций операторов и критериев для каждого типа фильтра.

Я создал отдельные посты для каждого из этих типов фильтров. Посты содержат пояснения и примеры кода VBA.

Файл в разделе загрузок выше содержит все эти примеры кода в одном месте. Вы можете добавить его в свою личную книгу макросов и использовать макросы в своих проектах.

Почему метод автофильтрации такой сложный?

Этот пост был вдохновлен вопросом от Криса, участника The VBA Pro Course. Комбинации Критерии и Операторы могут быть запутанными и сложными. Почему это?

Ну, фильтры развивались на протяжении многих лет. Мы увидели много новых типов фильтров, представленных в Excel 2010, и эта функция продолжает улучшаться. Однако параметры метода автофильтра не изменились. Они отлично подходят для совместимости со старыми версиями, но также означает, что новые типы фильтров работают с существующими параметрами.

Большая часть кода фильтра имеет смысл, но сначала может быть сложно разобраться. К счастью, у нас есть макро рекордер, чтобы помочь с этим.

Я надеюсь, что вы можете использовать эту статью и файл Excel в качестве руководства по написанию макросов для фильтров. Автоматизация фильтров может сэкономить нам и нашим пользователям массу времени, особенно при использовании этих методов в более крупном проекте автоматизации данных.

Читайте также:

Метод автофильтрации

Написание кода автофильтра

Автофильтр не является дополнением

Как установить номер поля динамически

Используйте таблицы Excel с фильтрами

Фильтры и типы данных

Почему метод автофильтрации такой сложный?