Извлечение внешних данных с помощью 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

• Парадокс

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

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

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

С помощью Microsoft Query можно выбрать нужные столбцы данных и импортировать только эти данные в Excel.

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

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

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

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

Чтобы настроить источник данных с помощью Microsoft Query, выполните следующее:

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

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

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

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

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

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

   ИЛИ

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

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

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

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

   Примечания: 

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

   • Для баз данных OLAP драйверы ODBC не требуются. При установке Microsoft Query устанавливаются драйверы для баз данных, созданных с помощью microsoft SQL Server Analysis Services. Чтобы подключиться к другим базам данных OLAP, необходимо установить драйвер источника данных и клиентское программное обеспечение.

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

   Важно: 

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

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

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

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-файлы напрямую, что позволяет вам или другим пользователям создавать дополнительные диапазоны внешних данных из того же запроса.

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

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

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

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

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

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

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

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

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

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

Этот параметр можно включить (или снова отключить) в любое время:

1. Щелкните Файл > Параметры > Дополнительно.

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

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

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