Работа с внешними данными в Excel 2010

• Получение данных из Access.
• Получение данных с веб­страниц.
• Получение данных из текстовых файлов.
• Получение данных с сервера MS SQL Server.
• Получение данных из XML­файлов.
• Использование Microsoft Query.

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

• базы данных различных типов;
• данные с веб-страниц;
• данные из текстовых файлов.

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

Для получения любых внешних данных и последующей работы с ними используется группа инструментов Получить внешние данные на вкладке Данные ленты инструментов (рис. 8.1).

Рис. 8.1. Инструменты получения внешних данных

Рассмотрим эти инструменты и порядок работы с ними.

Получение данных из Access

Для получения данных из баз данных Microsoft Access служит инструмент Из Access, причем он позволяет получать данные из файлов в формате как последней версии Microsoft Access, так и предыдущих версий. При щелчке на кнопке Из Access на экран выводится окно выбора источника данных. Поскольку источником данных в этом случае является файл, это стандартное окно открытия файла. В данном окне можно выбрать и открыть файлы с расширениями .mdb и .mde (форматы предыдущих версий Access), а также .accdb и .accde (форматы последней версии Access). После выбора нужного файла на экран будет выведено окно выбора таблицы, показанное на рис. 8.2 (поскольку в одном файле Access может быть множество таблиц, такое окно необходимо).

Рис. 8.2. Окно выбора таблицы Access

Как только вы выберете таблицу (пусть в нашем случае это будет таблица Клиенты из демонстрационной базы данных Борей, поставляемой с Microsoft ffice XP), на экране появится следующее окно, позволяющее более подробно настроить параметры и тип импорта данных (рис. 8.3).

Рис. 8.3. Настройка импорта данных

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

• Таблица — данные будут представлены в простом табличном виде;
• Отчет сводной таблицы — данные будут представлены как сводная

таблица, позволяющая выполнять группирование данных по отдельным полям, а затем отображать данные по группам и итоги по группам;

• Сводная диаграмма и отчет сводной таблицы — помимо сводной таблицы, на лист будет помещена также сводная диаграмма;
• Только создать подключение — создание подключения без импорта

данных (созданным подключением к данным можно будет воспользоваться позже).

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

• Имеющийся лист — данные помещаются на тот лист и в тот диапазон ячеек, которые указаны в расположенном ниже поле;
• Новый лист — для размещения импортируемых данных в книге Excel создается новый лист.

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

Рис. 8.4. Тонкая настройка подключения

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

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

• Фоновое обновление — установка этого флажка позволяет выполнять запрос к источнику данных без прекращения работы в Excel (без «подвисания»).
• Обновлять каждые … минут — установив этот флажок и задав интервал обновления, вы включаете режим, при котором через заданный вами промежуток времени данные на листе обновляются из указанного источника. Такой режим целесообразно устанавливать для тех источников данных, в которых изменения происходят часто и информация, полученная в момент импорта данных быстро устаревает.
• Обновление при открытии файла — если этот флажок установлен, то при открытии книги данные будут обновлены из источника, если сброшен — открытие книги будет проведено без обновления данных.
• Удалить данные из внешнего диапазона перед закрытием книги — этот флажок доступен только тогда, когда при открытии книги внешние данные обновляются. В этом случае при закрытии книги все данные, полученные из внешнего источника, удаляются, а запрос на получение данных сохраняется в книге. Таким образом, при установке обоих флажков данные будут импортироваться в книгу при ее открытии и удаляться из нее при закрытии. Это, естественно, значительно уменьшит размер самой книги Excel.
• Форматирование сервера OLAP — эта группа флажков включает или

отключает режимы получения соответствующих параметров форматирования данных с LAP-сервера (естественно, для того чтобы эти флажки были доступны, вы должны получать данные с такого сервера).

• Детализация OLAP — введя значение в это поле, вы можете ограничить количество «дочерних» записей, извлекаемых для каждого уровня иерархии в LAP-кубах.
• Язык — установите этот флажок, если вы хотите, чтобы сообщения об ошибках и уведомления выводились вам на том же языке, на котором у вас «разговаривают» офисные программы (то есть на русском).

Вторая вкладка окна настройки свойств подключения, Определение (рис. 8.5), позволяет задать или сменить подключение (можно поменять параметры извлечения данных или источник данных). Ниже описаны элементы управления этой вкладки.

• Файл подключения — по умолчанию в этом поле отображается путь к файлу базы данных Access. После того как подключение настроено, его параметры сохраняются в специальном файле с расширением .odc. Все такие файлы находятся в папке Мои источники данных, и, щелкнув на кнопке Обзор, вы можете заменить существующее содержимое поля Файл подключения любым из сохраненных ранее файлов. В качестве файла подключения могут также выступать файлы источников данных DBC (.dsn) или LE DB (.udl).

Рис. 8.5. Определение параметров подключения

• Всегда использовать файл подключения — этот флажок становится доступным, если вы выбрали при помощи кнопки Обзор один из файловых источников данных. В этом случае каждый раз, когда производится обращение к источнику данных, информация об источнике извлекается из файла подключения. Если флажок сброшен, то будет использована информация об источнике данных, сохраненная в книге.
• Строка подключения — в этом поле вы можете отредактировать па-

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

• Сохранить пароль — если установить этот флажок, то имя пользова-

теля и пароль будут сохранены в строке подключения (и в файле подключения), причем в виде простого незашифрованного текста. Если флажок сбросить, пароль придется вводить при каждом подключении к данным.

• Тип команды — в этом списке вы можете выбрать между двумя предопределенными командами, Таблица и SQL. При выборе пункта Таблица вся указанная таблица данных полностью импортируется

в книгу Excel, а при выборе пункта SQL вы сможете отобрать необходимые записи с помощью запроса.

• Текст команды — при выборе в списке Тип команды пункта Таблица в этом поле отображается имя таблицы, при выборе пункта SQL в этом поле необходимо ввести запрос на языке SQL.
• Параметры проверки подлинности — щелкнув на этой кнопке, вы можете выбрать один из трех вариантов проверки подлинности:
  • Проверка подлинности Windows — применяются имя и пароль текущего пользователя Windows;
  • SSO — используется строка кода SS (вход через подключение к серверу единого входа);
  • Нет — имя пользователя и пароль сохраняются в файле подключения.
• Изменить запрос — когда в качестве источника данных используется запрос, щелчок на этой кнопке позволяет отредактировать текст запроса.
• Параметры — редактирование параметров подключения с использованием Microsoft Query или веб-запроса.
• Экспорт файла подключения — сохранение текущего набора параметров в качестве файла подключения.

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

Получение данных с веб-страниц

С веб-страниц можно получать данные, оформленные в виде таблиц. Если учесть, что почти все современные веб-страницы оформляются с использованием каскадных таблиц стилей (Cascading Style Sheets, CSS), то практически любую страницу можно получить в виде табличных данных на листе Excel. Естественно, в реальной ситуации вы будете получать именно табличные данные, например, результаты

Получение данных с веб­страниц

финансовых анализов, прайс-листы, таблицу умножения. Учиться получать данные с веб-страниц мы будем на практике.

Упражнение

Для упражнения я выбрал один из популярнейших в России поисковых веб-сайтов, на первой странице которого есть явная «однощелчковая» ссылка на табличные данные. Это сайт Rаmbler.

ВНИМАНИе

Для выполнения данного упражнения необходимо, чтобы ваш компьютер был подключен к Интернету.

1. Создайте новую книгу Excel.
2. На ленте инструментов перейдите на вкладку Данные и в группе инструментов Получить внешние данные щелкните на кнопке Из веба. На экран будет выведено окно, показанное на рис. 8.6.

Рис. 8.6. Окно получения данных с веб­страниц

1. Как видите, это окно представляет собой модифицированное окно программы Internet Explorer. Наберите в адресной строке адрес http://www.rambler.ru/ и щелкните на кнопке Пуск. После того как первая страница сайта появится в окне, переместитесь в нижний правый угол страницы, используя полосы прокрутки, и найдите там раздел Курсы валют. Щелкните на ссылке Доллар (ЦБ), как показано на рис. 8.7.

Рис. 8.7. Выбор ссылки в окне создания веб­запроса

1. После того как вы перейдете по ссылке, в окне будет открыта следующая страница (рис. 8.8).

Рис. 8.8. Окно с таблицей курсов валют

Каждая таблица в этом окне автоматически помечается значком в виде стрелки. Как видите, в формате таблиц на веб-страницах

Получение данных с веб­страниц

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

1. Щелкните на значке стрелки рядом с таблицей валют, таб лица будет выделена жирной рамкой, а значок стрелки пре-

вратится в значок флажка (рис. 8.9).

Рис. 8.9. Выделение таблицы для импорта

1. Значок флажка означает, что таблица выделена, и можно щелкнуть на кнопке Импорт для вставки содержимого таблицы в лист Excel. Щелкните на кнопке Импорт. На экран будет выведено диалоговое окно, позволяющее уточнить местоположение данных на листе и свойства вставленной таблицы (рис. 8.10).

Рис. 8.10. Диалоговое окно вставки данных

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

1. После того как процесс получения данных завершится, на лист Excel будет выведена выбранная таблица, причем каждая ячейка исходной таблицы будет импортирована в отдельную ячейку листа (рис. 8.11).

Рис. 8.11. Данные, полученные с сайта Rambler

Получение данных из текстовых файлов

Извлекать данные из текстовых файлов приходится не часто. Практически все современные базы данных способны обмениваться дан-

Получение данных из текстовых файлов

ными через DBC, но еще несколько лет назад в нашей стране были широко распространены текстовые редакторы, электронные таблицы и базы данных, работающие под управлением операционной системы MS-DS. Кроме того, практически все программы для Windows имеют возможность экспортировать свои данные в текстовый формат. В том случае, когда вы сталкиваетесь с сохраненными в виде текста табличными данными, которые вы хотите поместить на лист Excel, нужно воспользоваться командой ДанныеПолучить внешние данныеИз текста.

Упражнение

Как и в случае получения данных с веб-страниц, получение данных из текстовых файлов будем изучать на примере.

1. Создайте маленькую учебную базу данных в текстовом формате. Для этого запустите программу Блокнот и наберите в ней текст, показанный на рис. 8.12.

Рис. 8.12. Текстовая база данных в окне программы Блокнот

1. Данные в столбцах разделяйте между собой знаками табуляции. То есть, набрав слово Фамилия, нажмите клавишу Tab для перевода курсора к следующей позиции табуляции. На рис. 8.12 между столбцами Фамилия и Дата рождения введено по два символа табуляции в каждой строке, потому что один символ табуляции не обеспечивал наглядного представления данных. После того как таблица будет заполнена, сохраните ее в файле primer.txt.
2. Создайте новую книгу Excel.
3. Выберите команду ДанныеПолучить внешние данныеИз текста. На экран будет выведено первое окно мастера импорта текстовых данных. Это обыкновенное окно открытия файла с расширением .txt. Найдите свой файл primer.txt и откройте его.
4. Первое окно мастера импорта содержит элементы управления для распознавания формата данных и просмотра импортируемо-

го файла (рис. 8.13). В общем случае Excel корректно распознает импортируемые данные (как и в нашем случае). Если же механизм автоматического распознавания не сработает, вы можете самостоятельно задать формат и кодировку, в которой сохранен файл.

Рис. 8.13. Первое окно мастера импорта текстов

1. Щелкнув на кнопке Далее, вы перейдете ко второму шагу — анализу данных. Если вы вводили двойной знак табуляции между столбцами Фамилия и Дата рождения, как это делал я в своем примере, то результат анализа данных будет выглядеть так, как показано на рис. 8.14. Как видите, в таблице образовался пустой столбец. Для того чтобы избавится от него, необходимо в этом окне мастера установить флажок Считать последовательные разделители одним.
2. Последнее, третье, окно мастера позволяет уточнить, в каком формате будут отображаться в Excel данные каждого столбца. Мастер по умолчанию предлагает вам установить для всех столбцов формат Общий. Это приведет к тому, что все числа будут отображены в числовом формате, даты — в формате даты, а текст — в текстовом формате. Чтобы уточнить формат даты, выделите требуемый столбец в области Образец разбора данных и установите нужный

Получение данных с сервера Microsoft SQL Server

переключатель в группе Формат данных столбца. Щелчок на кнопке Подробнее позволит вам настроить вывод числовых значений в общем формате. Закончив настройку, щелкните на кнопке Готово. Результат импорта показан на рис. 8.15.

Рис. 8.14. Результат анализа данных

Рис. 8.15. Текстовый файл, импортированный в Excel

Получение данных с сервера Microsoft SQL Server

Для того чтобы получить данные с сервера Microsoft SQL Server, необходимо выполнить команду ДанныеПолучить внешние данныеИз других источниковС сервера SQL Server. Эта команда выводит на экран окно мастера подключения данных (рис. 8.16).

Рис. 8.16. Окно мастера подключения данных

Как видно из рисунка, в этом окне вы должны указать имя сервера, к которому вы собираетесь подключаться. Это имя можно посмотреть, щелкнув на значке SQL Service Manager в области уведомлений панели задач (рис. 8.17), или спросить у системного администратора.

Рис. 8.17. Определение имени сервера

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

Получение данных с сервера Microsoft SQL Server

эти пароль и имя. После указания всех необходимых на данном этапе сведений вы можете щелкнуть на кнопке Далее и перейти к следующему окну мастера (рис. 8.18).

Рис. 8.18. Окно выбора базы данных и таблицы

На этом этапе вы выбираете базу данных на сервере и таблицу, из которой будут выбраны данные.

ПРИМеЧАНИе

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

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

Щелкнув на кнопке Готово в последнем окне, а также задав параметры вставки диапазона данных в соответствующем диалоговом окне (см. рис. 8.10), вы увидите выбранные данные на листе Excel (рис. 8.19).

Рис. 8.19. Фрагмент листа Excel с импортированными данными

Получение данных из XML-файлов

Для получения данных из файла в формате XML необходимо выполнить команду ДанныеПолучить внешние данныеИз других источниковИз импорта данных XML. Если у вас есть соответствующий XML-файл с данными, то никаких затруднений работа с этим мастером у вас не вызовет. Первое окно мастера представляет собой стандартное диалоговое окно открытия файла, в котором необходимо указать XML-документ, из которого вы собираетесь импортировать данные. Следующее окно мастера также знакомо вам — это окно задания параметров вставки диапазона данных (см. рис. 8.10). Указав, на каком листе размещать данные, вы получите в Excel такую же картину, как и при импорте данных с SQL-сервера.

Использование Microsoft Query

Microsoft Query является наиболее гибким и универсальным средством получения данных путем формирования запросов к источникам данных. После выполнения команды ДанныеПолучить внешние данныеИз других источниковИз Microsoft Query на экран выводится окно мастера, позволяющее задать источник данных и тип запроса (рис. 8.20).

В этом окне на вкладке Базы данных перечислены все источники данных

DBC, зарегистрированные в системе. Вы можете выбрать один из

существующих источников или создать новый. Для создания нового источника данных на основе установленного в системе драйвера DBC необходимо выбрать в списке пункт Новый источник данных и щелкнуть на кнопке OK. На экран будет выведено стандартное диалоговое окно создания источника данных DBC. В случае выбора в списке одного из уже сконфигурированных источников, вы увидите на экране окно программы Microsoft Query, а также окно добавления таблицы из выбранной базы данных (рис. 8.21).

Рис. 8.20. Окно выбора источника данных

Рис. 8.21. Добавление таблицы в запрос

После добавления таблицы в запрос на экране остается основное окно программы Microsoft Query с добавленной в запрос таблицей (рис. 8.22).

Рис. 8.22. Окно программы Microsoft Query

Давайте подробно рассмотрим кнопки панели инструментов Microsoft Query.

• Назначение инструментов Создать запрос, Открыть

запрос и Сохранить запрос вполне очевидно. Они по-

зволяют, соответственно, создать новый запрос, открыть запрос, сохраненный на диске, и сохранить созданный запрос на диске.

• Инструмент Вернуть данные возвращает данные в Excel.

• Инструмент Режим SQL служит для открытия окна с текстом SQL-запроса.
• Инструменты Отображение таблиц и Отображение условий позволяют вывести на экран или скрыть, соответственно, список выбранных таблиц или заданных условий запроса.

• Инструмент Добавить таблицу предназначен для добавления таблицы к запросу.
• Инструмент Фильтр по выделенному обеспечивает добавление в таблицу условий значения из выделенной ячейки.
• Инструмент Цикл по групповым операциям служит для выбора групповой операции. Групповые операции — это операции, которые можно задавать при формировании вычисляемого поля для используемого источника данных.
• Назначение инструментов Сортировать по возрастанию и Сортировать по убыванию вполне очевидно. Они обеспечивают сортировку данных в выбранном столбце.

• Инструмент Выполнить запрос инициирует выполнение за- проса.
• Инструмент Автоматический режим предназначен для включения режима автоматического выполнения запроса.

Как уже отмечалось, при выполнении команды ДанныеПолучить внешние данныеИз других источниковИз Microsoft Query на экран выводится окно выбора источника данных (см. рис. 8.20). Из трех вкладок в этом окне на данном этапе вам потребуется только вкладка Базы данных. В ней имеется список баз данных, доступных в вашей системе, непосредственно на вашем компьютере или же в локальной сети. В этом списке вы можете выбрать необходимую базу данных. Список доступных баз данных хранится в реестре Windows. Кнопка Обзор позволяет выбрать источники данных, сохраненные в специальных файлах с расширением .dsn. Такие источники данных обычно поставляются производителями баз данных, источники для которых не входят в стандартную поставку Windows. Кнопка Параметры позволяет задать постоянный путь к папкам, в которых у вас хранятся файлы источников данных. Флажок Использовать мастер запросов позволяет составить запрос к базе данных в интерактивном режиме. Если вы сбросите этот флажок, то усложните себе жизнь, поскольку тогда весь запрос придется формировать самостоятельно. Сбрасывать флажок Использовать мастер запросов нужно только в том случае, если вы хотите создать очень сложный запрос, а мастер запросов не справляется с этой задачей.

Для примера выберем в качестве источника данных базу данных MS Access, поскольку она входит в состав пакета Microsoft ffice и подключение к ней не требует настройки никаких дополнительных параметров. После того как вы выберете пункт MS Access Database в списке источников данных и щелкнете на кнопке OK, на экран будет выведено окно выбора файла с данными (рис. 8.23).

Рис. 8.23. Выбор файла с данными

С помощью этого окна необходимо найти на диске соответствующий файл с расширением .mdb. Обычно подобные файлы можно найти в каталоге C:\Program Files\Microsoft Office\Office\Samples. В случае если вы установили Microsoft ffice в другой каталог, то вам придется внести соответствующую поправку в путь поиска. Выберите в каталоге Samples файл Борей.mdb (или Northwind.mdb в некоторых версиях Microsoft ffice) и щелкните на кнопке OK. После открытия источника данных в работу вступает мастер выбора таблиц и столбцов (рис. 8.24).

Рис. 8.24. Выбор таблиц и столбцов

Щелчок на кнопке Параметры позволяет выбрать таблицы, данные из которых должны присутствовать в списке Имеющиеся таблицы и столбцы (это могут быть не только таблицы пользовательской базы данных, а еще и другие таблицы, в частности системные). Каждую из таблиц в списке Имеющиеся таблицы и столбцы можно раскрыть щелчком на значке «плюс» слева от названия таблицы. В этом случае под названием таблицы отобразятся входящие в состав таблицы столбцы. Выбрав один из столбцов и щелкнув на кнопке Просмотр, вы выведете в списке Данные выбранного столбца содержимое этого столбца (рис. 8.25).

Щелчок на кнопке с направленной вправо стрелкой добавляет выбранный столбец в список Столбцы запроса. Щелчок на кнопке с направленной влево стрелкой удаляет из списка Столбцы запроса выбранный там столбец, а щелчок на кнопке с направленной влево сдвоенной стрелкой удаляет из этого списка все столбцы. Кнопки с направленными вверх и вниз стрелками позволяют устанавливать порядок следования столбцов в тексте запроса. После того как вы определите, из каких столбцов нужно отбирать данные, и щелкните на кнопке Next, вы перейдете в следующее окно мастера построения запросов — окно отбора данных (рис. 8.26).

Рис. 8.25. Просмотр содержимого выбранного столбца

Рис. 8.26. Окно отбора данных

В этом окне для каждого из отобранных столбцов вы можете задать условия отбора записей. Для этого в списке Столбцы для отбора нужно выбрать столбец (в нашем случае это Должность) и в области справа — задать условия отбора. Сами условия нужно выбирать, пользуясь раскрывающимися списками, причем в левых списках можно только выбирать значения, а в правых можно как выбирать значения в списках, так и вводить значения вручную в поля списков. После задания условий отбора для одного столбца можно в списке Столбцы для отбора выбрать другой столбец и задать условия для его значений. Щелкнув после этого на кнопке Next, вы перейдете в окно задания порядка сортировки (рис. 8.27).

Рис. 8.27. Задание порядка сортировки

Следующий шаг позволяет вам решить, что именно вы будете делать с выбранными при помощи мастера запросов данными — передадите их в Excel, продолжите редактирование запроса внутри Microsoft Query или сформируете на их основе LAP-куб. Поскольку данный раздел посвящен изучению Microsoft Query, установите в завершающем диалоговом окне переключатель Просмотр или изменение данных в Microsoft Query и щелкните на кнопке Finish.

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

Рис. 8.28. Таблица в Microsoft Query

Данные в этой таблице отобраны при помощи мастера запросов. Текст созданного им запроса можно увидеть, щелкнув на кнопке Режим SQL. На экран будет выведено окно, в котором можно не только просмотреть текст запроса, но и отредактировать его вручную (рис. 8.29).

Рис. 8.29. Текст запроса в режиме SQL

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

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

• Создать запрос — вывод на экран окна мастера запросов и создание нового запроса.
• Открыть запрос — открытие файла запроса, сохраненного на диске.
• Закрыть запрос — закрытие текущего запроса, при этом окно Microsoft Query остается активным, но из него удаляются все данные, относящиеся к текущему запросу.
• Сохранить запрос — сохранение текущего запроса на диске .
• Сохранить как — сохранение текущего запроса с новым именем.
• Определение таблицы — вызов на экран окна настройки, позволяю-

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

Рис. 8.30. Создание индекса

• Выполнить запрос SQL — вывод на экран окна, в котором можно ввести с клавиатуры SQL-запрос к любой доступной базе данных и затем выполнить его. В запрос можно встраивать хранимые процедуры, если таковые определены в запрашиваемой базе данных. Хранимую процедуру можно выбрать в списке, выводимом щелчком на кнопке Процедуры (рис. 8.31).
• Создать куб OLAP — эта команда выводит на экран окно мастера создания LAP-кубов, при помощи которого вы можете создать, сохранить и вернуть в Excel в виде сводной таблицы или диаграммы LAP-куб на основе текущего запроса.
• Отмена и возврат в Microsoft Excel — эта команда отменяет редакти-

рование запроса и закрывает окно программы Microsoft Query, не возвращая данные в Excel.

• Вернуть данные в Microsoft Excel — эта команда закрывает окно про-

граммы Microsoft Query, возвращая в Excel данные, выбранные с помощью запроса.

Рис. 8.31. Выбор запроса и хранимой процедуры

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

• Разрывать связь при отсутствии активности через — при установленном флажке Microsoft Query будет осуществлять попытку подключения к заданному источнику данных в указанное время (время в секундах вводится в расположенное рядом поле), после чего, если подключение не состоится, выведет сообщение об этом. Если флажок сбросить, то Microsoft Query будет осуществлять подключение все время своей активности. Прервать этот процесс удастся лишь нажатием клавиши Esc.
• Максимальное число возвращаемых записей — установив этот флажок, вы получаете возможность ограничить количество возвращаемых в результате запроса записей (число записей вводится в расположенное рядом поле). При сброшенном флажке запрос вернет все записи, соответствующие параметрам запроса.
• Поддерживать открытые каналы связи до закрытия Microsoft Query — при установке этого флажка связь с источником данных будет сохраняться до тех пор, пока вы не закроете Microsoft Query, даже если запрос, использующий этот источник данных, будет закрыт. Это ускоряет повторные запросы к тому же самому источнику данных.
• Запретить изменение результатов запроса — когда этот флажок сбро-

шен, вы можете редактировать поля записей, и эти изменения будут внесены в базу данных, из которой они получены. Установка флажка предотвращает внесение изменений в исходные данные.

ВНИМАНИе

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

• Проверять запросы перед сохранением или возвратом данных — если этот флажок установлен, то при получении команды на возврат данных в Microsoft Excel или на сохранение запроса программа Microsoft Query сначала выполнит запрос и только при успешном его завершении сохранит этот запрос на диске или передаст данные в Microsoft Excel. Если флажок сбросить, то такая проверка выполнена не будет.
• Указывать имена таблиц в инструкции SQL — этот флажок устанавли-

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

Ниже перечислены команды меню Вид.

• Таблицы и Условия — установка флажков рядом с именами этих команд в меню приводит к появлению соответствующих областей в окне Microsoft Query (рис. 8.32).
• Область ввода — эта команда доступна только тогда, когда в режиме редактирования курсор находится в определенной ячейке таблицы запроса. При ее выборе содержимое соответствующей ячейки выводится на экран внутри диалогового окна и может быть отредактировано в этом окне. Это бывает удобно, когда содержимое ячеек в таблице трудноразличимо.

Рис. 8.32. Области таблиц и условий в окне Microsoft Query

• Свойства запроса — вывод на экран окна с двумя флажками, по-

зволяющими показывать в запросе только записи с уникальными значениями, а также осуществлять группировку записей по определенному признаку (соответствует SQL-команде GROUP BY).

• Запрос SQL — вывод на экран окна с текстом запроса, который в этом окне можно отредактировать вручную.

Команды меню Формат не нуждаются в особых комментариях, они позволяют установить параметры шрифта, которым будут отображаться данные в таблице Microsoft Query, ширину и высоту ячеек, а также предоставляют возможность скрывать и отображать столбцы.

Ниже перечислены команды меню Таблица.

• Добавить таблицу — добавление таблицы к текущему запросу.
• Удалить таблицу — удаление таблицы из текущего запроса.
• Объединение — вывод на экран окна настройки (рис. 8.33), позволяющего задать параметры объединения записей из двух таблиц (соответствующая SQL-команда — INNER JOIN).

Рис. 8.33. Задание параметров объединения записей из двух таблиц

Ниже перечислены команды меню Условия.

• Добавить условие — добавление в запрос условия с помощью специ-

ального окна (рис. 8.34). Например, код показанного на рисунке условия выглядит так:

HAVING (Клиенты.КодКлиента>’PICCO’) AND

(Клиенты.КодКлиента=’PRINI’)

Рис. 8.34. Добавление условия в запрос  Удалить все условия — удаление из запроса всех условий.

Ниже перечислены команды меню Записи.

• Добавить столбец — добавление к запросу столбца (поля) с заданными параметрами.
• Удалить столбец — удаление из запроса выделенного столбца.
• Изменить столбец — изменение параметров выделенного столбца.
• Сортировать — сортировка записей по убыванию или возрастанию для выделенного поля.
• Перейти — перевод курсора на запись с указанным номером.
• Разрешить правку — включение режима внесения изменений в дан-

ные запроса, ведущих к изменениям данных в источнике (в базе) данных.

• Выполнить запрос — выполнение текущего запроса с заданными параметрами.
• Автоматический режим — включение режима автоматического вы-

полнения запроса после каждого изменения параметров запроса или данных.

Упражнение

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

1. Создайте в Excel новую книгу.
2. Выберите команду ДанныеПолучить внешние данныеИз других источниковИз Microsoft Query.
3. Выберите в качестве источника данных MS Access. В списке баз данных окна Выбор источника данных это будет либо пункт База данных MS Access, либо пункт MS Access database (точное название зависит от вашего программного обеспечения).
4. В следующем окне откройте базу данных Борей.mdb, при стандартной установке расположенную по адресу C:\Program Files\Microsoft Office\Office\Samples\.
5. Включите в запрос таблицу Клиенты (поля КодКлиента, Название и Должность), таблицу Заказы (поля КодЗаказа и КодКлиента) и таблицу Сведения о заказах (все поля).
6. Задайте условие отбора данных — поле КодКлиента больше CACTU.
7. Выберите сортировку по полю КодЗаказа.
8. Установите переключатель Просмотр или изменение данных в MS Query и щелкните на кнопке Готово.
9. В открывшемся окне MS Query раскройте меню Вид и установите флажки возле команд Таблицы и Условия. В результате в окне MS Query вы должны увидеть область результата выполнения запроса, довольно большую таблицу с запрошенными полями

и данными в них, область таблиц (рис. 8.35) и область условий (рис. 8.36). Вы можете видеть, что три таблицы связаны между собой, причем линии связи показывают, какие поля связывают таблицы. В области условий отображено условие, которое мы задали с помощью мастера запросов. Полный текст запроса, согласно которому из базы данных были извлечены данные, можно увидеть, щелкнув на кнопке Режим SQL. Запрос получился довольно объемным. Последняя его строка (ORDER BY Заказы.КодЗаказа) указывает на то, что сортировка записей будет произведена по полю КодЗаказа из таблицы Заказы.

Рис. 8.35. Область таблиц

Рис. 8.36. Область условий

1. Как вы помните, из таблицы Заказы мы включили в запрос только два поля. В области просмотра таблиц очень легко добавить к запросу недостающие поля. Щелкните дважды на поле КодСотрудника прямо в области просмотра таблицы Заказы, и соответствующее поле будет добавлено как последний столбец к основной таблице вывода результата запроса.
2. Щелкните в области просмотра таблиц на таблице Сведения о Заказах. Откройте меню Таблица и выберите команду Удалить таблицу. Вы немедленно увидите результат в основном поле данных: все поля (столбцы) этой таблицы пропадут из таблицы данных.
3. Добавим в запрос еще одно условие. Для этого можно непосредственно вводить условия в таблицу или выбрать команду УсловияДобавить условие, которая выводит на экран окно задания условия. Выберем то же поле КодКлиента, зададим объединение условий оператором И, само условие (меньше) и значение (EASTC). После щелчка на кнопке Добавить количество строк в таблице данных резко уменьшится.
4. Теперь проверим, как работает команда Специальное копирование меню Правка. Для этого выделите все записи в таблице, начинающиеся с символа С. Раскройте меню Правка и выберите команду Специальное копирование. В открывшемся диалоговом окне установите флажки Включить заголовки столбцов и Включить номера строк, а затем щелкните на кнопке OK. Перейдите в книгу Excel, выделите ячейку С3 и нажмите сочетание клавиш Ctrl+V. Результатом будет помещение в таблицу не только скопированных данных, но и номеров строк и заголовков столбцов. Далее можно выделить этот фрагмент и задать ему параметры форматирования при помощи команды Автоформат. На рис. 8.37 вы можете видеть, как выглядит этот фрагмент данных после назначения ему встроенного формата Список 2.

Рис. 8.37. Фрагмент внешних данных в Excel

ПРИМеЧАНИе

В данном случае мы передали часть данных из запроса в таблицу через буфер обмена. Естественнее воспользоваться командой Вернуть данные в Microsoft Excel меню Файл. Все данные, соответствующие параметрам запроса, будут вставлены на текущий лист книги или на новый лист.