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

Сортировка списков

Мы уже рассмотрели вопросы создания списков Excel, представляющих локальную базу данных, рассмотрели взаимное преобразование между списками Excel и объектами базы данных Access. Теперь пришла пора заняться специальными операциями, которые облегчают работу с данными, хранящимися в этой локальной базе данных. При этом я буду рассматривать, как визуальные методы работы со списками, так и программные. Сортировка списков - одна из основных операций, которые выполняются над данными, хранящимися в базе. Эту операцию, конечно, можно выполнять вручную. При программировании следует пользоваться методом Sort объекта Range. Приведем синтаксис этого метода:

Function Sort([Key1], [Order1 As XlSortOrder = xlAscending], [Key2], [Type], [Order2 As 
XlSortOrder = xlAscending], [Key3], [Order3 As XlSortOrder = xlAscending], [Header As 
XlYesNoGuess = xlNo], [OrderCustom], [MatchCase], [Orientation As XlSortOrientation = 
xlSortRows], [SortMethod As XlSortMethod = xlPinYin])

Рассмотрим параметры метода и поясним его работу:

• Key1, Key2 и Key3 - задают имена полей, по которым сортируется список, так что одновременно можно отсортировать список не более чем по трем полям.
• Order1, Order2 и Order3 задают порядок сортировки - по возрастанию или убыванию независимо по каждому полю; значение для полей по умолчанию - "по возрастанию" ( xlAscendinig ).
• Type - задается только при сортировке сводных таблиц и указывает тип сортируемых значений, задаваемых константами: xlSortLabels и xlSortValues.
• Header - указывает, есть ли в списке строка с именами полей; если его значение - xlGuess, система сама должна определить, есть ли заголовки у полей (для этого они должны отличаться форматом).
• Значение параметра OrderCustom, отличное от 1, используется, когда список сортируется в порядке, заданном пользовательским списком или некоторым стандартным, специальным списком, например, с названиями месяцев. Приведу пример, поясняющий ситуацию. Представьте себе, что значениями некоторого поля таблицы являются названия основных цветов радуги. Вы хотите отсортировать эти значения в порядке следования цвета в радуге: "красный, оранжевый, …, фиолетовый". Тогда следует создать пользовательский список, задающий порядок следования цвета, и добавить его в коллекцию списков методом AddCustomList:

  Application.AddCustomList ListArray:=Range("F1:F7")

  Здесь предполагается, что область "F1:F7" содержит значения цветов радуги. При добавлении списка в коллекцию он получит свой номер, который и указывается, как значение параметра OrderCustom.
• MatchCase (булев) - имеет значение False, если при сортировке регистр не учитывается, большие и малые буквы не различаются.
• Orientation - задает ориентацию при перестановке сортируемых значений таблицы: сверху вниз (переставляются строки) или слева направо (переставляются столбцы).
• SortMethod - позволяет выбрать порядок, заданный на символах алфавита. Он может задаваться соответствующей кодовой страницей или являться стандартным алфавитным порядком, в котором цифры предшествуют буквам, а латынь - кириллице.

Вот пример вызова этого метода для сортировки списка "Заказчики", показанного на рис. 4.17. Список сортируется по двум полям, - вначале упорядочиваются города, а затем организации каждого города:

Public Sub Sorting()
	'Сортировка таблицы Заказчики по двум полям:
	'вначале по полю "город", затем - "организация"
	Selection.Sort Key1:=Range("D5"), Order1:=xlAscending, _
		Key2:=Range("B5"), Order2:=xlAscending, _
		Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
		Orientation:=xlTopToBottom
End Sub

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

Фильтрация записей

Под фильтрацией понимается выделение из таблицы записей, удовлетворяющих условиям запроса. Фильтровать записи можно как вручную, так и программно. В Excel есть два метода фильтрации записей списка: AutoFilter и AdvancedFilter. Первый фильтрует записи на том же месте, где находится сам список. Критерии отбора записей можно задать только для одного поля. В результате его работы исходный список делается невидимым, показываются только отобранные фильтром записи. При работе вручную можно просмотреть эти записи, в случае необходимости скорректировать их или скопировать. При программировании чаще используется метод AdvancedFilter, позволяющий не только фильтровать записи на месте, но и копировать результаты в указанное место. Важнее, что он позволяет задавать довольно сложные условия отбора записей, накладываемые, при желании, на все поля списка. Рассмотрим подробнее работу этих методов. Синтаксис метода 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. Список до начала фильтрации его записей