Мир объектов Excel 2000

Метод AdvancedFilter


Перейдем теперь к рассмотрению более полезного для программистов и в любом случае более универсального метода фильтрации записей - AdvancedFilter. Вот его синтаксис:

Function AdvancedFilter(Action As XlFilterAction, [CriteriaRange], [CopyToRange], [Unique])

Это метод вызывается объектами Range и возвращает объект Range, задающий список. Параметры метода имеют следующий смысл:

  • Action - может принимать одно из двух значений: xlFilterInPlace, xlFilterCopy.; первое из них указывает, что список фильтруется на месте, второе - задает возможность копирования результатов на новое место. О недостатках фильтрации на месте я уже говорил, поэтому при программировании целесообразно копировать значения, - это облегчает доступ к выбранным записям.
  • CriteriaRange - задает область, в которой можно записать логическую формулу, задающую условие выбора записей. Заметьте, что здесь формула позволяет задать условия, одновременно налагаемые на все поля записи. О том, что собой представляет область критериев и как строится формула, задающая условие, я скажу чуть ниже.
  • CopyToRange - задает область копирования результатов фильтрации.
  • Unique - булев параметр, значение True которого позволяет отобрать только один экземпляр записи в случае, когда она многократно встречается в списке. Если параметр имеет значение False, то выдаются все имеющиеся экземпляры записи.

Чтобы понять, как работает метод, нужно четко представлять, что собой представляет область критериев и как в ней формируется условие выбора. Этим мы сейчас и займемся. Для создания области критериев нужно выбрать любую свободную область листа и задать в ней имена полей. Заметьте, в этой области имя одного и того же поля может появиться дважды. На следующем шаге следует задать логическую формулу, накладывающую ограничения на поля выбираемых записей. Для тех, кто знаком с логикой, скажем, что эта формула записана в дизъюнктивной нормальной форме и представляет собой дизъюнкцию конъюнктов. Каждый конъюнкт записывается в отдельной строке под именами полей. Все члены в одной строке соединены знаком конъюнкции "And", а отдельные строки - знаком дизъюнкции "Or". Теперь попробуем сказать то же самое, но проще. Условия выбора можно задавать в нескольких строчках. Если есть две строки и условие в первой из них обозначить через F1, а во второй - F2 , то общее условие будет иметь вид F1 Or F2. Сами условия F1 и F2 могут быть достаточно сложными. Они объединяют знаком "And" элементарные условия, накладываемые на каждое поле в отдельности. Элементарные условия - это известные нам по методу AutoFilter отношения ">, <, = и т. д.". Поэтому в области критериев имена полей могут встречаться дважды, чтобы была возможность задать условие вида "(Поле1 > 10) And (Поле1<= 20)".

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

  • Если в образце используется символ "?", то в записи ему соответствует любой символ; образцу "к?т" соответствуют, например, записи с полями "кит" и "кот".
  • Если в образце используется символ "*", то в записи ему соответствует любая последовательность символов. Так, образцу "*он" соответствуют записи "он", "кон" и "Наполеон".
  • Чтобы символы "?" и "*" могли использоваться как обычные, им должен предшествовать символ тильда "~"; образцу "Кто~?" соответствует строка "Кто?".
  • Любому образцу соответствуют все строки, чьим префиксом он является. Так, образцу "Петр" соответствуют строки: Петр, Петрушка, Петрович. Чтобы задать точное соответствие, условие сравнения нужно ввести в виде: ="=Петр".


Внимательно взгляните на рисунок, где показан слегка видоизмененный список из предыдущего примера. Здесь же показаны две области, отведенные под критерии, и результаты двух запросов, реализуемых двумя вызовами метода AdvancedFilter:


увеличить изображение
Рис. 4.24.  Результаты работы расширенного фильтра

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

Sub РасширенныйВыбор() 'Сложное условие фильтрации. Range("МойСписок").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Range("F7:K14"), _ CopyToRange:=Range("A13:D13"), Unique:=True 'Здесь условие проще. Range("МойСписок").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Range("F16:G21"), _ CopyToRange:=Range("I16:L16"), Unique:=False End Sub

Первый вызов метода AdvancedFilter стоит обсудить подробнее. Метод вызывается объектом Range, задающим список. Как мы говорили ранее, список целесообразно именовать. В нашем примере его имя - "МойСписок". Результаты фильтрации копируются в область, заданную параметром CopyToRange. Размер этой области заранее не известен, но знать его и не нужно - достаточно указать диапазон для записи заголовков полей. Под строкой заголовков будут располагаться записи, выбранные при фильтрации. А вот область, отведенную для критериев, следует указать точно. При этом не следует ее именовать, как рекомендовано в документации. Во всяком случае, этого не стоит делать, если предполагается несколько различных запросов или они будут модифицироваться.

Что же записано в области "F7:K14", задающей условие выборки? В строке заголовков - имена полей, причем имена второго и третьего полей повторены дважды. Хотя исходный список содержит 4 поля, область критериев имеет 6 столбцов. Ниже строки заголовков расположены 7 строк, каждая из которых задает некоторое условие выбора записей. Выпишем явно условие, которое задают строки этой области. Вы можете проверить запись формулы, глядя на предыдущий рисунок. Конечно, для меня на самом деле исходной являлась эта формула, в соответствии с ней заполнялись ячейки в области критериев. Вот эта довольно сложная формула:

( (Поле3 >=5) And (Поле3 < 7) And (Поле4 = "low") ) OR ( (Поле1 = "Мария") And (Поле4 = "high") ) OR (Поле2 = 37) OR ( (Поле2 > 20) And (Поле3 < 20) And (Поле3 > 15) And (Поле4 ="middle") ) OR ( (Поле1 = "Анна") And (Поле4 = "high") ) OR ( (Поле1 = "Петр") And (Поле4 = "low") ) OR ( (Поле1 = "Алиса") And (Поле2 = 24) And (Поле4 = "low") )



Формула состоит из 7 дизъюнктов, каждый из них независимо добавляет новые записи в результирующую выборку. Рассмотрим их:

  • первый - выделяет из списка две одинаковые записи со значением Anna в первом поле. Однако, поскольку параметр Unique имеет значение True, выбираться будет только одна уникальная запись.
  • второй дизъюнкт выделяет из списка запись с именем Мария, имеющей в 4-м поле значение high;
  • третий - самый простой: он должен выделить записи, имеющие в поле 2 значение 37; такая запись есть в списке, это запись с именем Петр (high);
  • четвертый дизъюнкт правильно выделяет запись со значением middle -это запись с именем Петр (middle);
  • пятый - выделяет запись с именем Анна (high);
  • шестой дизъюнкт выделяет две записи с именами Петр (low) и Петрович (low);
  • седьмой - выделяет еще одну запись.


В результате этого сложного запроса из списка выделяется 8 записей из 9. По сути, выбраны все записи без дублирования. Следующий запрос мы не будем рассматривать подробно. Заметьте лишь, что в запросе изменено значение параметра Unique, и потому оба экземпляра продублированной записи появятся в результирующей выборке.


Содержание раздела