Фильтрация записей
Под фильтрацией понимается выделение из таблицы записей, удовлетворяющих условиям запроса. Фильтровать записи можно как вручную, так и программно. В Excel есть два метода фильтрации записей списка: AutoFilter и AdvancedFilter. Первый фильтрует записи на том же месте, где находится сам список. Критерии отбора записей можно задать только для одного поля. В результате его работы исходный список делается невидимым, показываются только отобранные фильтром записи. При работе вручную можно просмотреть эти записи, в случае необходимости скорректировать их или скопировать. При программировании чаще используется метод AdavncedFilter, позволяющий не только фильтровать записи на месте, но и копировать результаты в указанное место. Важнее, что он позволяет задавать довольно сложные условия отбора записей, накладываемые, при желании, на все поля списка. Рассмотрим подробнее работу этих методов. Синтаксис метода AutoFilter таков:
expression.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)
Выражение Expression должно возвращать Range-объект, задающий список. Параметры метода имеют следующий смысл:
- Field - задает порядковый номер поля списка, используемого для отбора записей; критерии отбора накладываются именно на это поле;
- Criteria1 и Criteria2 - задают два возможных условия, накладываемых на поле. Если заданы оба эти параметра, то параметр Operator может принимать значение xlAnd или xlOr. Каждый из параметров представляет собой строку вида
<знак операции отношения><значение>",
где могут быть использованы все обычные знаки операции отношения: " >, >=, <, <=, =, <>". Если опущен знак операции, - подразумевается "равенство"; если опущено значение, а знак операции - "=" или "<>", условие означает проверку поля на пустоту. Если параметры Criteria1 и Criteria2 опущены, то никакие условия не накладываются и выбираются все записи. Чаще всего опускается один параметр - Criteria2. В этом случае могут быть заданы дополнительные условия выборки. Можно выбрать первые N записей, имеющих максимальные или минимальные значения. Параметр Critria1 указывает тогда число N, а значение параметра Operator указывает, задает ли N число записей или число процентов записей, максимальные или минимальные значения которых будут отбираться.
- Operator - принимает одно из значений: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent; первые два используются, если заданы оба критерия, остальные задаются, если задан только первый критерий и он определяет не отношение, а специальные условия выборки записей. По умолчанию значение параметра Operator - xlAnd.
- VisibleDropDown - булев параметр, значение True которого позволяет сделать видимым поле фильтрации. Для этого подсвечивается стрелка выпадающего списка, связанного с именем поля.
- Если метод AutoFilter вызывается без параметров, он отключает результаты предыдущей фильтрации и список показывается полностью в обычном виде.
Я создал некоторый список и хочу поэкспериментировать с ним:
увеличить изображение
Рис. 4.22. Список до начала фильтрации его записей
Я создал некоторый список и хочу поэкспериментировать с ним:
увеличить изображение
Рис. 4.22. Список до начала фильтрации его записей
Ниже Вы видите процедуру, в которой проводятся эксперименты по фильтрации записей этого списка. Конечно, предполагается пошаговое ее выполнение, чтобы видеть результат каждой фильтрации.
Sub Автовыбор() 'Эксперименты с автофильтрацией Range("B1").Select 'Эксперимент 1. 'Выбор 3-х элементов с максимальными значениями 2-го поля. Selection.AutoFilter Field:=2, Criteria1:="3", _ Operator:=xlTop10Items, VisibleDropDown:=True
'Эксперимент 2. 'Типичное условие выбора с двумя критериями Selection.AutoFilter Selection.AutoFilter Field:=2, Criteria1:="<12", _ Operator:=xlOr, Criteria2:=">30", VisibleDropDown:=False
'Эксперимент 3. 'Условие выбора эквивалентно True. Выбираются все записи. Selection.AutoFilter Selection.AutoFilter Field:=2, Criteria1:="<>12", _ Operator:=xlOr, Criteria2:="<>35", VisibleDropDown:=True
'Эксперимент 4. 'Будут выбраны 5 записей из списка Selection.AutoFilter Range("B1").Select Selection.AutoFilter Field:=2, Criteria1:="12", _ Operator:=xlOr, Criteria2:=">30" End Sub
Процедура подробно прокомментирована и, по-видимому, не нуждается в особых пояснениях. Взгляните на заключительные результаты ее работы:
увеличить изображение
Рис. 4.23. Результаты фильтрации списка
Обратите внимание, в результате последней фильтрации из списка выбраны 5 записей, имеющие номера: 2, 3, 4, 7, 8. Только эти записи отображаются на рабочей странице, остальные записи недоступны, пока фильтр остается включенным.
Я хочу теперь рассказать еще об одном объекте, связанном с автофильтрацией. Напомню, что каждый список следует располагать на отдельном листе, и методы фильтрации могут быть применены только к единственному списку на листе. В терминах объектов это означает, что объект Worksheet имеет свойство AutoFilter, возвращающее одноименный объект. Объект AutoFilter хранит информацию о фильтрах, применяемых к списку. Главное свойство этого объекта - Filters возвращает одноименную коллекцию, элементы которой являются объектами класса Filter. Число элементов в коллекции совпадает с числом полей списка. Каждый из объектов Filter сохраняет параметры фильтра, применяемого к соответствующему столбцу списка. Я покажу на примере, какими свойствами обладают эти объекты. Кроме свойства Filters можно еще отметить и свойство Range объекта AutoFilter, возвращающее, по существу, область, занятую списком.
Вот пример работы с объектом AutoFilter:
Public Sub FilterAuto() 'Работа с объектом AutoFilter и результатами фильтрации Dim Myf As AutoFilter, filt As Filter 'Определить объект AutoFilter, связанный со страницей Set Myf = ThisWorkbook.Worksheets("Лист2").AutoFilter Debug.Print "Число фильтров = ", Myf.Filters.Count For Each filt In Myf.Filters Debug.Print "Включен = ", filt.On If filt.On Then Debug.Print "Фильтр:", filt.Criteria1, _ filt.Operator, filt.Criteria2 End If Next filt
End Sub
Вот результаты отладочной печати:
Число фильтров = 4 Включен = False Включен = True Фильтр: =12 2 >30 Включен = False Включен = False
Как видите, булево свойство On объекта AutoFilter позволяет определить, включен ли фильтр. Свойства Criteria1, Criteria2 и Operator возвращают одноименные параметры фильтра.
Давайте теперь разберемся еще с одним важным вопросом, - как программно можно получить и использовать результаты фильтрации. Прежде всего, взгляните на процедуру, которую я разработал, чтобы продемонстрировать возможный подход к решению этой задачи:
Public Sub FilterRes() 'Работа с результатами фильтрации Dim Myf As AutoFilter Dim Myr As Range, Myr1 As Range Dim MyrBeg As Range, MyrEnd As Range Dim cel As Range 'Определить объект AutoFilter, связанный со страницей Set Myf = ThisWorkbook.Worksheets("Лист2").AutoFilter 'Область списка Set Myr = Myf.Range 'Отрезаю строку заголовков списка Set MyrBeg = Myr.Offset(1, 0).Cells(1, 1) Set MyrEnd = Myr.Cells(Myr.Rows.Count, Myr.Columns.Count) Set Myr = Range(MyrBeg, MyrEnd) 'Выделяю область фильтрации Myr.Select Myr.Copy 'Получаю на другом листе результаты фильтрации Set Myr1 = Range("Лист3!F2") Myr1.PasteSpecial Set Myr1 = Myr1.CurrentRegion 'Объект Myr содержит все данные списка, 'Объект Myr1 содержит только отфильтрованные данные. Debug.Print "Число ячеек исходной области = ", Myr.Cells.Count Debug.Print "Число ячеек области фильтрации = ", Myr1.Cells.Count 'Обработка результатов фильтрации For Each cell In Myr1.Cells Debug.Print cell Next cell
End Sub
Цель этой программы показать, как можно получить результаты применения фильтра и программно работать с ними. Для этого я первым делом получаю в объекте Myr область, занятую списком, для чего использую метод Range объекта Autofilter. Затем я отрезаю строку, занятую заголовками списка, после чего копирую содержимое объекта Myr в буфер. Заметьте, хотя объект Myr содержит всю область списка, в буфер передается только видимая часть списка - результаты фильтрации. Так что, скопировав результаты из буфера в объект Myr1, я получаю результаты фильтрации и могу с ними работать, так как мне нужно, - для простоты я их просто печатаю. Обратите внимание, результаты я копирую на новый лист рабочей книги, иначе они будут восприниматься как список. Таким образом, в объекте Myr у меня хранятся результаты до фильтрации, а в объекте Myr1 - после фильтрации. Вот результаты отладочной печати:
Число ячеек исходной области = 32 Число ячеек области фильтрации = 20 Anna 12 5 low Mary 12 7 low Anna 12 5 low Петр 35 18 middle Петр 37 18 high
