На этом шаге мы рассмотрим алгоритм создания запросов с помощью Microsoft Query.

    В Microsoft Office входит отдельная программа, которая называется Microsoft Query, предоставляющая доступ к базам данных, созданных с помощью таких программных средств, как dBASE, Access, Paradox, Microsoft SQL Server и т.п. Эта программа позволяет отбирать информацию из базы данных, сортировать, редактировать и копировать ее на рабочий лист. Используя Microsoft Query, можно получить данные на основе заданных пользователем критериев. Excel связывается с Microsoft Query посредством механизма DDE (Dynamic Data Exchange — динамический обмен данными). Microsoft Query работает с внешними источниками данных через драйверы ODBC (Open Database Connectivity), которые являются разработанным Microsoft стандартом для работы с базами данных. Для получения данных по запросу посредством драйвера ODBC используется язык запросов SQL (Structured Query Language).

    Рассмотрим последовательность создания запроса при помощи Microsoft Query на основе простой базы данных о студентах, созданной в Access (рисунок 1).

Рис.1. База данных о студентах

    Выполните команду Данные | Импорт внешних данных | Создать запрос (Data | Get External Data | New Database Query). Появится диалоговое окно Выбор источника данных (Choose Data Source) (рисунок 2).

Рис.2. Диалоговое окно Выбор источника данных

    В этом окне выберите источник данных, например базу данных, составленную в Access. Нажмите кнопку Параметры (Options), при помощи появившегося окна Источник данных (Data Source) (рисунок 3) можно установить папки, в которых следует искать источник данных.

Рис.3. Окно Источник данных

    Итак, выберите базу данных, составленную в Access, в качестве источника и нажмите кнопку ОК.

    В появившемся диалоговом окне Выбор базы данных Select Database (рисунок 4) в списке Имя базы данных Database Name выберите имя файла базы данных (в данном случае Студенты.mdb) и нажмите кнопку ОК, что приведет к запуску мастера запросов.

Рис.4. Диалоговое окно Выбор базы данных

    Откроется диалоговое окно Создание запроса: выбор столбцов (Query Wizard — Choose Columns) (рисунок 5), которое позволяет добавлять столбцы или поля в список Столбцы запроса (Query Columns).

Рис.5. Диалоговое окно Создание запроса: выбор столбцов

    При нажатии на кнопку >, расположенную в средней части диалогового окна Создание запроса: выбор столбцов (Query Wizard — Choose Columns), выбранный столбец добавляется в запрос, кнопка < служит для удаления выбранного столбца из списка столбцов запроса, а кнопка << позволяет удалить все столбцы запроса. В рассматриваемом случае в список Столбцы запроса (Query Columns) добавим все поля базы данных, восподбзовавшись кнопкой >>. Нажмите кнопку Далее > (Next >), переходя к следующему шагу работы мастера запросов.

    Откроется диалоговое окно Создание запроса: отбор данных (Query Wizard — Filter Data) (рисунок 6), которое позволяет отфильтровать данные в выбранных полях.

Рис.6. Диалоговое окно Создание запроса: отбор данных

    Для фильтрации данных в списке Столбцы для отбора, выделите столбец, по которому будет производиться фильтрация. В первом раскрывающемся списке выбирается операция отбора, а во втором, содержащем все имеющиеся в выбранном поле значения, выберите нужное или введите свое. При необходимости ввести дополнительную операцию отбора выберите переключатель И (AND) или Или (OR), и воспользуйтесь следующими раскрывающимися списками для ввода критериев фильтрации и т.д. Завершив создание критериев фильтрации, нажмите кнопку Далее > (Next >), переходя к следующему шагу работы мастера запросов. В рассматриваемом случае проведем фильтрацию по полю Группа, установив критерий Равно значению Экономика, и полю Предмет с критерием Равно значению Информатика, т.е. отобразим только информацию о результатах сдачи информатики студентов-экономистов. Отметим, что визуально выбранные поля отличаются от невыбранных тем, что они выделяются полужирным шрифтом.

    Откроется диалоговое окно Создание запроса: порядок сортировки (Query Wizard — Sort Data) (рисунок 7), которое позволяет отсортировать данные в выбранных полях в порядке возрастания или убывания.

Рис.7. Диалоговое окно Создание запроса: порядок сортировки

    Отсортируем данные по фамилиям в порядке возрастания. Нажмите кнопку Далее > (Next >), переходя к следующему шагу работы мастера запросов.

    Откроется диалоговое окно Создание запроса: заключительный шаг (рисунок 8).

Рис.8. Диалоговое окно Создание запроса: заключительный шаг

    При желании еще раз воспользоваться тем же запросом его можно сохранить, используя кнопку Сохранить запрос (Save Query). Для завершения создания запроса выберите флажок Вернуть данные в Microsoft Excel (Return Data to Microsoft Excel) и нажмите кнопку Готово (Finish).

    На экране отобразится диалоговое окно Импорт данных (рисунок 9). выберите переключатель Имеющийся лист и укажите адрес верхней левой ячейки, куда надо поместить данные, нажмите кнопку ОК.

Рис.9. Диалоговое окно Импорт данных

    Полученный результат запроса данного примера показан на рисунке 10.

Рис.10. Результат запроса

    Макрорекордер записывает перечисленные выше действия в виде следующего макроса:

Sub Макрос1()
'
' Макрос1 Макрос
' Макрос записан 10.01.2014 (user)
'
'
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=База данных MS Access;DBQ=D:\MyDocs\Студенты.mdb;
       DefaultDir=D:\MyDocs;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTime" _ 
        ), Array("out=5;")), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT ПервыйКурс.Номер, ПервыйКурс.Фамилия, ПервыйКурс.Группа, 
         ПервыйКурс.Предмет, ПервыйКурс.Оценка" & Chr(13) & "" & Chr(10) & 
         "FROM `D:\MyDocs\Студенты`.ПервыйКурс ПервыйКурс" & Chr(13) & "" 
         & Chr(10) & "WHERE (ПервыйКурс.Предмет='Информатика')" & 
          Chr(13) & "" & Chr(10) & "ORDER " _ 
        , "BY ПервыйКурс.Фамилия")
        .Name = "Запрос из База данных MS Access"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

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

    Центральную роль в данном макросе играют: инструкция SELECT, указывающая выбираемые поля, инструкция WHERE, задающая критерий отбора записей и инструкция ORDER BY, устанавливающая порядок вывода записей.

    В макросе используется семейство QueryTables. Семейство QueryTables является семейством всех объектов QueryTable, представляющих собой внедренные на рабочий лист результаты запроса по внешней базе данных посредством Microsoft Query. Основными методами семейства QueryTables являются Add и Item. Несмотря на громоздкость макроса Макрос1, метод Add имеет простую синтаксическую структуру:

  Add (Connection, Destination, Sql)

    Аргументы:

• Connection - ODBC-строковое выражение, указывающее базу данных, к которой производится запрос.
• Destination - устанавливает верхнюю левую ячейку диапазона рабочего листа, в который выводится результат запроса.
• Sql - ODBC-строковое выражение, указывающее критерии, по которым создается запрос.

    На следующем шаге мы немного поговорим об ODBC.

Предыдущий шаг Содержание Следующий шаг