Office

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

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

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

С помощью 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  3. Дважды щелкните _Лт_нев данных саурце_гт_.

    -или-

    Нажмите кнопку _Лт_нев данные саурце_гт_и нажмите кнопку ОК.

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

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

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

    Примечания: 

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

    • Для баз данных 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. Выберите Файл > Параметры > Дополнительно.

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

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

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

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

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

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

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

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

×