Excel и базы данных

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

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