Итог: узнайте, как применять фильтры даты с VBA. Включает примеры фильтрации для диапазона между двумя датами, группами дат из раскрывающегося списка фильтра, динамическими датами в периоде и т.д.
Уровень мастерства: Средний
Скачать файл
Файл Excel, содержащий код, можно скачать ниже. Этот файл содержит код для фильтрации различных типов данных и типов фильтров. Пожалуйста, ознакомьтесь с моей статьей Фильтрация сводной таблицы или среза по самой последней дате или периоду для более подробной информации.
VBA AutoFilters Guide.xlsm (100.5 KB)
Фильтры даты в Excel
У нас есть много вариантов при фильтрации столбца, который содержит даты. Список выпадающего меню фильтра группирует даты по годам, месяцам, дням, часам, минутам, секундам. Мы можем расширить и установить эти флажки для фильтрации нескольких элементов.
Мы также можем выбрать из подменю Date Filters. Это позволяет нам фильтровать диапазоны дат, как до, после или между двумя датами. Есть также много вариантов дат в периодах (этот месяц, следующий месяц, последний квартал и т.д.).
Давайте посмотрим на некоторые примеры кода для различных фильтров даты. При применении фильтра для одного числа нам нужно использовать форматирование чисел, которое применяется в столбце. Это странная причуда VBA, которая может привести к неточным результатам, если вы не знаете правила. В приведенном ниже коде есть пример.
Фильтры диапазона дат в VBA
Следующий макрос содержит примеры основных фильтров даты. Важно отметить, что значения параметров Criteria заключены в кавычки. Операторы сравнения = <> также включены в кавычки.
Пожалуйста, ознакомьтесь с моей статьей Фильтрация сводной таблицы или среза по самой последней дате или периоду для получения более подробной информации о том, как использовать метод AutoFilter и его параметры.
Вы можете скопировать / вставить приведенный ниже код в VB Editor.
Sub AutoFilter_Date_Examples()
' Примеры фильтрации столбцов с датами
Dim lo As ListObject
Dim iCol As Long
' Установить ссылку на первую таблицу на листе
Set lo = Sheet1.ListObjects(1)
' Установить поле фильтра
iCol = lo.ListColumns("Date").Index
' Очистить фильтры
lo.AutoFilter.ShowAllData
With lo.Range
' Одиночная дата - использовать тот же формат даты, который
'применяется к столбцу
.AutoFilter Field:=iCol, Criteria1:="=1/2/2014"
'До даты
.AutoFilter Field:=iCol, Criteria1:="<1/31/2014"
'После или равно дате
.AutoFilter Field:=iCol, Criteria1:=">=1/31/2014"
'Диапазон дат (между датами)
.AutoFilter Field:=iCol, _
Criteria1:=">=1/1/2014", _
Operator:=xlAnd, _
Criteria2:="<=12/31/2015"
End Sub
Несколько групп дат
Следующий макрос содержит примеры фильтрации нескольких групп дат. Это то же самое, что выбрать конкретные годы, месяцы, дни, часы, минуты из списка в раскрывающемся меню фильтра.
Для этих фильтров мы устанавливаем для параметра Operator значение xlFilterValues. Мы используем Criteria2 (не Criteria1), чтобы указать массив элементов с помощью функции Array.
Это специальный массив шаблонов, где первое число — это период времени (год, месяцы, дни и т. Д.). Второе число — последняя дата в диапазоне. Макрос ниже содержит примеры и дальнейшие пояснения.
Sub AutoFilter_Multiple_Dates_Examples()
'Примеры фильтрации столбцов для нескольких периодов времени даты
Dim lo As ListObject
Dim iCol As Long
'Установить ссылку на первую таблицу на листе
Set lo = Sheet1.ListObjects(1)
'Установить поле фильтра
iCol = lo.ListColumns("Date").Index
'Очистить фильтры
lo.AutoFilter.ShowAllData
With lo.Range
'При фильтрации по нескольким периодам, выбранным из
'раскрывающегося меню фильтра, используйте Operator: = xlFilterValues и
'Критерии2 с узорным массивом. Первое число -
'временной период. Второе число - последняя дата в периоде.
'Первое измерение массива - группа периода времени
'0-Годы
'1-Месяцы
'2-Дни
'3-Часы
'4-Минуты
'5-Секунды
'Несколько лет (2014 и 2016) использовать последний день времени
'периода для каждого элемента массива
.AutoFilter Field:=iCol, _
Operator:=xlFilterValues, _
Criteria2:=Array(0, "12/31/2014", 0, "12/31/2016")
'Несколько месяцев (январь, апрель, июль, октябрь 2015 года)
.AutoFilter Field:=iCol, _
Operator:=xlFilterValues, _
Criteria2:=Array(1, "1/31/2015", 1, "4/30/2015", 1, "7/31/2015", 1, "10/31/2015")
'Несколько дней
'Последний день каждого месяца: январь, апрель, июль, октябрь 2015 года)
.AutoFilter Field:=iCol, _
Operator:=xlFilterValues, _
Criteria2:=Array(2, "1/31/2015", 2, "4/30/2015", 2, "7/31/2015", 2, "10/31/2015")
'Установить поле фильтра
iCol = lo.ListColumns("Date Time").Index
'Очистить фильтры
lo.AutoFilter.ShowAllData
'Несколько часов» (все даты в 11:00 часов 10 января 2008 г.
'и 11:00 часов 20.01.2008)
.AutoFilter Field:=iCol, _
Operator:=xlFilterValues, _
Criteria2:=Array(3, "1/10/2018 13:59:59", 3, "1/20/2018 23:59:59")
End With
End Sub
Динамические даты в периодах
Следующий макрос содержит примеры дат в определенных периодах. Это аналогично выбору предустановленных элементов фильтра из подменю «Фильтры даты».
Для этих фильтров мы устанавливаем параметр Operator равным xlFilterDynamic. Мы устанавливаем Criteria1 в константу, которая представляет параметр периода динамической даты. Константы перечислены ниже.
Sub AutoFilter_Dates_in_Period_Examples()
'Примеры фильтрации столбцов для ДАТЫ В ПЕРИОД
'Пресеты фильтров даты, найденные в подменю «Фильтры даты
Dim lo As ListObject
Dim iCol As Long
'Установить ссылку на первую таблицу на листе
Set lo = Sheet1.ListObjects(1)
'Установить поле фильтра
iCol = lo.ListColumns("Date").Index
'Очистить фильтры
lo.AutoFilter.ShowAllData
'Оператор: = xlFilterDynamic
'Критерии1: = одно из следующих перечислений
' Значение константы
' 1 xlFilterToday
' 2 xlFilterYesterday
' 3 xlFilterTomorrow
' 4 xlFilterThisWeek
' 5 xlFilterLastWeek
' 6 xlFilterNextWeek
' 7 xlFilterThisMonth
' 8 xlFilterLastMonth
' 9 xlFilterNextMonth
' 10 xlFilterThisQuarter
' 11 xlFilterLastQuarter
' 12 xlFilterNextQuarter
' 13 xlFilterThisYear
' 14 xlFilterLastYear
' 15 xlFilterNextYear
' 16 xlFilterYearToDate
' 17 xlFilterAllDatesInPeriodQuarter1
' 18 xlFilterAllDatesInPeriodQuarter2
' 19 xlFilterAllDatesInPeriodQuarter3
' 20 xlFilterAllDatesInPeriodQuarter4
' 21 xlFilterAllDatesInPeriodJanuary
' 22 xlFilterAllDatesInPeriodFebruray <- февраль написан с ошибкой
' 23 xlFilterAllDatesInPeriodMarch
' 24 xlFilterAllDatesInPeriodApril
' 25 xlFilterAllDatesInPeriodMay
' 26 xlFilterAllDatesInPeriodJune
' 27 xlFilterAllDatesInPeriodJuly
' 28 xlFilterAllDatesInPeriodAugust
' 29 xlFilterAllDatesInPeriodSeptember
' 30 xlFilterAllDatesInPeriodOctober
' 31 xlFilterAllDatesInPeriodNovember
' 32 xlFilterAllDatesInPeriodDecember
With lo.Range
'Все даты в январе (по всем годам)
.AutoFilter Field:=iCol, _
Operator:=xlFilterDynamic, _
Criteria1:=xlFilterAllDatesInPeriodJanuary
'Все даты во втором квартале (по всем годам)
.AutoFilter Field:=iCol, _
Operator:=xlFilterDynamic, _
Criteria1:=xlFilterAllDatesInPeriodQuarter2
End With
End Sub
Вот ссылка на справочную страницу MSDN со списком XlDynamicFilterCriteria. И спасибо Дугу Глэнси из YourSumBuddy за то, что он указал на февральскую опечатку в константе. У него есть несколько полезных советов для параметра Operator в этой статье.
Фильтры и типы данных
Параметры раскрывающегося меню фильтра изменяются в зависимости от типа данных в столбце. У нас есть разные фильтры для текста, чисел, дат и цветов. Это создает МНОГО различных комбинаций операторов и критериев для каждого типа фильтра.
Я создал отдельные статьи для каждого из этих типов фильтров. Статьи содержат пояснения и примеры кода VBA.
- Как очистить фильтры с помощью VBA
- Как отфильтровать пустые и непустые ячейки
- Как фильтровать числа с помощью VBA
- Как фильтровать текст с помощью VBA
- Как отфильтровать цвета и значки с помощью VBA
Файл в разделе загрузок выше содержит все эти примеры кода в одном месте. Вы можете добавить его в свою личную книгу макросов и использовать макросы в своих проектах.
Пожалуйста, оставьте комментарий ниже с любыми вопросами или предложениями. Спасибо!
AutoFilter Field:=iCol, Criteria1:=»>=1/1/2014″, Operator:=xlAnd, Criteria2:=»<=12/31/2015"
Если пишу так, то 31 декабря не отображается, последняя отображаемая дата 30 декабря, с началом периода такой проблемы нет, можете подсказать почему?
Вы хотите выбрать все данные за 2014 год? или с 01.012014 по 31.12.2015?
Может напутали с годами?
Если нет, то попробуйте указать не конец года, а начало следующего <01/01/2016