Получение внешних данных с помощью Microsoft Query

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

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

В этой статье

Дополнительные сведения о Microsoft Query

Подключение к источнику данных

Определение запроса с помощью мастера запросов

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

Обзор возможностей приложения Microsoft Query

С помощью Microsoft Query можно подключаться к внешним источникам данных, выбирать из них данные, импортировать эти данные в лист и обновлять их по мере необходимости, чтобы синхронизировать данные в листе с данными во внешних источниках.

Типы баз данных, к которым можно получить доступ     Можно извлечь данные из нескольких типов баз данных, включая Microsoft Office Access, Microsoft SQL Server и службы OLAP Microsoft SQL Server. Можно также извлечь данные из книг Excel и текстовых файлов.

Microsoft Office содержит показатели, которые можно использовать для извлечения данных из следующих источников данных:

  • Службы анализа Microsoft SQL Server Analysis Services (поставщик OLAP)

  • Microsoft Office Access

  • База данных dBASE

  • Microsoft FoxPro

  • Microsoft Office Excel

  • Oracle

  • Paradox

  • Текстовые файлы баз данных

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

Выбор данных из базы данных     Извлечение данных из базы данных производится путем создания запроса, который служит своего рода вопросом о том, какие данные хранятся во внешней базе данных. Например, если данные хранятся в базе данных Access и необходимо узнать объемы продаж указанного продукта по определенному региону, можно извлечь только ту часть данных, которые будут анализироваться, например, только данные по продукту и области.

Приложение Microsoft Query позволяет выбрать столбцы только необходимых данных и импортировать их в приложение Excel.

Обновление листа за одну операцию     После занесения внешних данных в книгу Excel при каждом изменении базы данных можно обновить данные для обновления результатов анализа — без повторного создания итоговых отчетов и диаграмм. Например, можно создать отчет о продажах за месяц и обновлять его ежемесячно при появлении новых данных.

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

Диаграмма: использование источников данных в Query

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

  1. Подключение к источнику данных.

  2. Использование мастера запросов, чтобы определить запрос.

  3. Работа с данными в Microsoft Excel.

К началу страницы

Подключение к источнику данных

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

Для настройки источника данных с использованием приложения Microsoft Query выполните следующие действия:

  1. На вкладке Данные в группе Получить внешние данные нажмите кнопку Из других источников, а затем выберите команду Из Microsoft Query.

  2. Выполните одно из следующих действий.

    • Чтобы указать источник данных для базы данных, текстового файла или книги Excel, перейдите на вкладку Базы данных.

    • Чтобы указать источник данных куба OLAP, перейдите на вкладку Кубы OLAP. Эта вкладка доступна только в том случае, если вы запустили программу Microsoft Query из Excel.

  3. Дважды щелкните пункт <Новый источник данных>.

    -или-

    Щелкните пункт <Новый источник данных>, а затем нажмите кнопку ОК.

    Откроется диалоговое окно Создание нового источника данных.

  4. На первом шаге введите имя для источника данных.

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

    Если внешняя база данных, к которой нужно получить доступ, не поддерживается драйверами ODBC, установленными вместе с приложением Microsoft Query, необходимо приобрести и установить совместимые с Microsoft Office драйверы ODBC от сторонних поставщиков, например, от производителя соответствующей базы данных. За инструкциями по установке обращайтесь к поставщику базы данных.

    Примечание : Базы данных OLAP не требуют драйверов ODBC. При установке приложения Microsoft Query устанавливаются драйверы для баз данных, созданных с использованием служб анализа Microsoft SQL Server. Для подключения к другим базам данных OLAP нужно установить драйвер источника данных и клиентское программное обеспечение.

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

    Используйте надежные пароли, состоящие из букв в верхнем и нижнем регистре, цифр и символов. В ненадежных паролях не используются сочетания таких элементов. Надежный пароль: Y6dh!et5. Ненадежный пароль: House27. Пароль должен состоять не менее чем из 8 знаков. Лучше всего использовать парольную фразу длиной не менее 14 знаков.

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

  7. После ввода необходимых сведений нажмите кнопку OK или Готово для возврата в диалоговое окно Создание нового источника данных.

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

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

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

По завершении этих шагов имя источника данных появится в диалоговом окне Выбор источника данных.

К началу страницы

Использование мастера запросов для создания запроса

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

Можно также использовать мастер для сортировки набора результатов и для простой фильтрации. На последнем шаге мастера можно передать данные в приложение Excel или продолжить работу с запросом в приложении Microsoft Query. После создания запроса можно запускать его как в Excel, так и в Microsoft Query.

Для запуска мастера запросов выполните следующие шаги.

  1. На вкладке Данные в группе Получить внешние данные нажмите кнопку Из других источников, а затем выберите команду Из Microsoft Query.

  2. Убедитесь, что в диалоговом окне Выбор источника данных установлен флажок Использовать мастер запросов.

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

    -или-

    Щелкните источник данных, который нужно использовать, а затем нажмите кнопку ОК.

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

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

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

  • Объединение данных другими способами     Внутренние объединения, создаваемые мастером запросов, чаще всего используются при создании запросов. Иногда, тем не менее, нужен другой тип объединения. Например, если есть таблица со сведениями о продажах продукта и другая таблица со сведениями о покупателях, внутреннее объединение (т. е. объединение, которое создает мастер запросов) не позволит извлечь записи о покупателях, не сделавших покупку. Microsoft Query можно использовать для объединения этих таблиц таким образом, что будут извлечены все записи о покупателях, а также данные о продажах для покупателей, совершивших покупку.

Для запуска приложения Microsoft Query выполните следующие шаги.

  1. На вкладке Данные в группе Получить внешние данные нажмите кнопку Из других источников, а затем выберите команду Из Microsoft Query.

  2. Убедитесь, что в диалоговом окне Выбор источника данных снят флажок Использовать мастер запросов.

  3. Дважды щелкните источник данных, который нужно использовать.

    -или-

    Щелкните источник данных, который нужно использовать, а затем нажмите кнопку ОК.

Повторное и совместное использование запросов     И в мастере запросов, и в Microsoft Query можно сохранить запрос как DQY-файл, который можно изменять, использовать повторно и передавать другим пользователям. Приложение Excel непосредственно открывает DQY-файлы, что позволяет другим пользователям создавать дополнительные диапазоны внешних данных из того же запроса.

Чтобы открыть сохраненный запрос из Microsoft Excel, выполните следующие действия:

  1. На вкладке Данные в группе Получить внешние данные нажмите кнопку Из других источников, а затем выберите команду Из Microsoft Query. Появится диалоговое окно Выбор источника данных.

  2. В диалоговом окне Выбор источника данных перейдите на вкладку Запросы.

  3. Дважды щелкните сохраненный запрос, который нужно открыть. Этот запрос отобразится в Microsoft Query.

Если нужно открыть сохраненный запрос и приложение Microsoft Query уже открыто, в меню Файл приложения выберите команду Открыть.

Если дважды щелкнуть имя DQY-файла, откроется приложение Excel, которое выполнит запрос и вставит результаты в новый лист.

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

К началу страницы

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

После создания запроса в мастере запросов или в Microsoft Query можно передать данные на лист Excel. Данные при этом становятся диапазон внешних данных или отчет сводной таблицы, которые можно форматировать и обновлять.

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

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

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

Можно включить (или отключить) этот параметр в любое время следующим образом:

  1. Нажмите кнопку Microsoft Office  Изображение кнопки Office , а затем — кнопку Параметры Excel и выберите категорию Дополнительно.

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

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

К началу страницы

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

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×