В.А. Биллиг, М.И. Дехтярь
VBA и Office 97
Офисное программирование

Глава 22(1) Списки Excel как база данных

Хранение данных в списках Excel

Небольшие базы данных (БД) можно вести на Excel. Будем рассматривать БД как совокупность таблиц. Таблицы естественны для Excel и потому для поиска данных в таблицах, их отборе, сортировке предусмотрены специальные средства. Когда объем данных невелик и нет необходимости в сложных запросах, требующих одновременной работы с несколькими таблицами, работать с данными в Excel проще и быстрее. Умение вести БД на Excel особенно важно для тех, кто работает с Office 97, не содержащим Access.

БД Excel удобнее всего располагать на нескольких листах по числу таблиц, хранящих данные. На каждом листе обычно располагается одна таблица. Это ограничение накладывает метод AdvancedFilter, без которого, как правило, не обходится обработка данных в таблице. Многие запросы к таблице можно реализовать с помощью этого метода. Таблицу, хранящую данные, в Excel принято называть списком. Список состоит из строк и столбцов. Столбцы — это поля списка (таблицы БД). Первая строка списка обычно содержит имена полей. Вот несколько правил, которым должны удовлетворять списки, часть из них носит рекомендательный характер:

·         для имен полей следует задать формат (шрифт, цвет фона), отличный от формата ячеек, хранящих значения полей: это позволит системе автоматически отличать имена полей от их значений;

·         значения, хранящиеся в одном столбце (значения одного поля), должны иметь один и тот же тип;

·         рекомендуется именовать лист названием списка, возможно, с некоторым префиксом;

·         области ячеек, отведенной для списка (вместе с первой строкой, хранящей названия полей), целесообразно дать имя; эту область нужно выделить по максимуму, так чтобы она допускала дальнейшее пополнение списка;

·         список отделяется от остальной части рабочего листа пустыми строками и столбцами. Поэтому не рекомендуется, чтобы пустые строки и столбцы встречались в середине списка, т. е. записи, все поля которых пусты, нежелательны, — иногда это может привести к неприятностям.

Создание базы данных. Экспорт таблиц Access

БД на Excel создается, как правило, вручную. Это простая работа, при выполнении которой достаточно следовать правилам, приведенным в предыдущем параграфе. Еще проще создать такую базу, если есть готовая БД на Access. Давайте перенесем БД офиса "РР" на Excel. Наша дальняя цель — создание нового шаблона для бланка заказа, сохраняющего свою функциональность для конечного пользователя. Его единственное отличие должно состоять в том, что он будет взаимодействовать с БД не Access, а Excel, хранящейся в той же рабочей книге, что и сам бланк. О том, как сохранить функциональность, мы поговорим чуть позже, а сейчас займемся переносом базы. Вначале мы расскажем об использованном нами способе, а затем о способе, рекомендованном в документации. Мы использовали стандартную схему переноса данных через буфер:

·         открыли рабочую книгу с бланком заказа;

·         открыли БД офиса "dbPP", сделанную на Access;

·         в рабочей книге создали листы по числу таблиц БД и дали им соответствующие имена: "Книги", "Заказы", "Заказчики" и т. д.;

·         переключаясь между приложениями, поочередно, копировали каждую таблицу Access в буфер и вставляли ее в начало соответствующего листа рабочей книги Excel. При этом использовали режим специальной вставки, копируя таблицу как текст.

·         Затем на листе Excel для каждой таблицы подобрали нужную ширину столбцов (полей таблицы), выбрали формат для строки заголовков полей и дали таблице имя;

·         вся работа заняла несколько минут. Вот, например, как выглядит таблица "Заказано" после переноса на соответствующий лист рабочей книги:

{Рис. 22.1. Таблица "Заказано" на листе рабочей книги Excel.}

Вместе с тем Access имеет специальные, развитые средства экспорта и импорта таблиц БД и других своих объектов. Например, таблицы, запросы, формы, отчеты можно экспортировать в формат HTML. Таблицы и запросы можно экспортировать в БД, удовлетворяющие стандарту ODBC, их можно экспортировать в приложение FoxPro или dBase и, естественно, в таблицу Excel. Рекомендуемый способ таков:

·         открыть БД Access и выбрать нужную таблицу;

·         в меню "Файл" выбрать команду "Сохранить как/ Экспорт";

·         в появившемся диалоговом окне выбрать опцию "Во внешнем файле или БД" и щелкнуть кнопку OK;

·         в поле со списком "Тип файла" выбрать "Excel 97";

·         в поле со списком "Папка" выбрать папку, содержащую файл Excel, на рабочий лист которого будет помещена экспортируемая таблица.

А теперь внимание! Завершить работу можно двумя способами, различие между которыми плохо документировано. Вы можете:

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

·         если просто выбрать имя файла из списка и щелкнуть кнопку "Экспорт", будет предложено заменить существующий файл новым с одним листом, содержащим экспортируемую таблицу. Тот же результат имеет место при включении флажка "Сохранить формат при экспорте".

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

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

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

Sub 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 = xlTopToBottom],
[SortMethod As XlSortMethod = xlSyllabary],
[IgnoreControlCharacters], [IgnoreDiacritics], [IgnoreKashida])

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

·         Key1, Key2 и Key3 — задают имена полей, по которым сортируется список, так что одновременно можно отсортировать список не более чем по трем полям.

·         Order1, Order2 и Order3 задают порядок сортировки — по возрастанию или убыванию независимо по каждому полю; значение для полей по умолчанию — "по возрастанию" (xlAscendinig).

·         Header — указывает, есть ли в списке строка с именами полей; если его значение — xlGuess, система сама должна определить, есть ли заголовки у полей (для этого они должны отличаться форматом).

·         Значение параметра OrderCustom, отличное от 1, используется, когда список сортируется в порядке, заданном пользовательским списком или некоторым стандартным, специальным списком, например, с названиями месяцев. Представьте, значениями некоторого поля таблицы являются названия основных цветов радуги. Вы хотите отсортировать эти значения в порядке следования цвета в радуге: "красный, оранжевый, …, фиолетовый". Тогда следует создать пользовательский список, задающий порядок следования цвета, и добавить его в коллекцию Lists методом AddCustomList:

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

Здесь предполагается, что область "F1:F7" содержит значения цветов радуги. Коллекция Lists содержит списки, используемые как при автозаполнении, так и при специальном порядке сортировки; первый пользовательский список получает порядковый номер 6 (значение параметра OrderCustom), стандартные списки (месяцев, дней недели) имеют более ранние номера.

·         MatchCase (булев) — имеет значение False, если при сортировке регистр не учитывается, большие и малые буквы не различаются.

·         Orientation — задает ориентацию: сверху вниз (по строкам) или слева направо (по столбцам) идет сортировка (по умолчанию сортируются строки).

·         SortMethod — позволяет выбрать порядок, заданный на символах алфавита. Он может задаваться соответствующей кодовой страницей или являться стандартным алфавитным порядком, в котором цифры предшествуют буквам, а латынь — кириллице.

·         Остальные параметры позволяют игнорировать некоторые специальные символы. Вот пример вызова этого метода для сортировки списка по двум полям:

'Выделяем некоторую ячейку, принадлежащую списку

Range("A1").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, _
     Key2:=Range("D2"), Order2:=xlAscending, _
     Header:=xlGuess, OrderCustom:=1, MatchCase:= False, _
     Orientation:=xlTopToBottom

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

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

Expression.AutoFilter(Field, Criteria1, Operator, Criteria2)

Выражение 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.

·         Если метод AutoFilter вызывается без параметров, он отключает результаты предыдущей фильтрации и список показывается полностью в обычном виде.

Заметим, что при программировании этот метод можно использовать, если результаты фильтрации предъявляются пользователю, и он будет их обрабатывать. Не ясно, как можно программным путем использовать результаты фильтрации, поскольку средств доступа и перемещения по выбранным записям нет.

Мы создали некоторый список и хотим поэкспериментировать с ним:

{Рис. 22_2. Список до начала фильтрации его записей}

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

Sub Автовыбор()

     'Выбор 3-х элементов с максимальными значениями 2-го поля.

     Range("B1").Select

     Selection.AutoFilter Field:=2, Criteria1:="3", Operator:=xlTop10Items

     'Типичное условие выбора с двумя критериями

     Selection.AutoFilter

     Range("B1").Select

     Selection.AutoFilter Field:=2, Criteria1:="<12", Operator:=xlOr, Criteria2:=">30"

     'Некорректно заданы условия выбора (xlOr). Выбираются все записи.

     Selection.AutoFilter

     Range("B1").Select

     Selection.AutoFilter Field:=2, Criteria1:="<>12", Operator:=xlOr, _

                   Criteria2:="<>35"

     'Здесь условия заданы корректно. Будут выбраны 5 записей из списка

     Selection.AutoFilter

     Range("B1").Select

     Selection.AutoFilter Field:=2, Criteria1:="12", Operator:=xlOr, _

                   Criteria2:=">30"

     'Неудачная попытка копирования выбранных записей.

     'Будут скопированы только 3 (!) записи в данном примере.

     Range("A2:D6").Select

     Range("A2:D6").Copy

     Range("F2").PasteSpecial

End Sub

Процедура  подробно прокомментирована. Мы хотим только, чтобы Вы взглянули на заключительные результаты ее работы:

{Рис. 22.3. Результаты фильтрации списка и его копирования}

Обратите внимание, в результате последней фильтрации из списка выбраны 5 записей, имеющие номера: 2, 3, 4,7, 8. При попытке выделить или копировать первые 5 строк списка выделяются и копируются только две. Это связано с тем, что эти операции определены не над выделенным списком, а, по-прежнему, над основным. Но в текущий момент, пока не выключен AutoFilter, часть записей списка недоступна.

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

Expression.AdvancedFilter(Action, 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:

{Рис.22.4 Результаты работы расширенного фильтра}

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

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") ) OR

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

·         первый — выделяет из списка две одинаковые записи со значением Anna в первом поле. Однако, поскольку параметр Unique имеет значение True, выбираться будет только одна уникальная запись.

·         второй дизъюнкт выделяет из списка запись с именем Мария, имеющей в 4-м поле значение high;

·         третий — самый простой: он должен выделить записи, имеющие в поле 2 значение 37; такая запись есть в списке, это запись с именем Петр (high);

·         четвертый дизъюнкт правильно выделяет запись со значением middle —это запись с именем Петр (middle);

·         пятый — выделяет запись с именем Анна (high);

·         шестой дизъюнкт выделяет две записи с именами Петр (low) и Петрович (low);

·         седьмой — выделяет еще одну запись.

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

Изменение данных в списке

Расширенный фильтр позволяет выделить из списка записи, удовлетворяющие некоторому критерию. Дальше с этими записями можно работать, используя обычные методы работы с Range -объектами. Но как быть, если необходимо провести корректировку выделенных записей. Понятно, что эту корректировку чаще всего нужно делать непосредственно в БД, т. е. в исходном списке. Но у нас нет информации о том, какой порядковый номер в списке имеет первая выделенная запись. Как всегда, в таких ситуациях нечего надеяться на систему и следует самому позаботиться о себе. Включайте в каждый список первым полем порядковый номер записи (во многих таблицах обычная практика, когда первым идет счетчик). Тогда в каждой выделенной записи есть поле, задающее ее порядковый номер, а этого достаточно, чтобы добраться до записи в списке и изменить ее нужным образом. В заключение заметим, что если стандартных методов сортировки и фильтрации данных списков не хватает, то на VBA можно написать обработку любого сколь угодно сложного запроса.

Начало главы 22             Вперед