Для извлечения данных из внешних источников можно использовать приложение Microsoft Query. Благодаря использованию Microsoft Query для извлечения данных из корпоративных баз данных и файлов вам не нужно повторно вводить данные, которые вы хотите проанализировать в Excel. Кроме того, можно автоматически обновлять отчеты и сводки Excel из исходной исходной базы данных при каждом обновлении базы данных.
Подробнее о Microsoft Query
С помощью Microsoft Query можно подключаться к внешним источникам данных, выбирать данные из этих внешних источников, импортировать их в книгу и обновлять данные по мере необходимости, чтобы данные листа синхронизировались с данными из внешних источников.
Типы баз данных, к которым можно получить доступ Вы можете получать данные из баз данных нескольких типов, в том числе Microsoft Office Access, Microsoft SQL Server и Microsoft SQL Server OLAP Services. Можно также извлекать данные из книг 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:
На вкладке " Данные " в группе "Внешние данные " щелкните "Из других источников", а затем выберите "Из Microsoft Query".
Примечание
Excel 365 переместил Microsoft Query в группу меню мастеров прежних версий . Это меню не отображается по умолчанию. Чтобы включить эту функцию, перейдите в меню "Файл", "Параметры", "Данные" и включите этот параметр в разделе "Показать мастера импорта устаревших версий ".
Выполните одно из следующих действий:
- Чтобы указать источник данных для базы данных, текстового файла или книги Excel, откройте вкладку "Базы данных ".
- Чтобы указать источник данных куба OLAP, перейдите на вкладку Кубы OLAP . Эта вкладка доступна только в том случае, если вы запустили Microsoft Query из Excel.
Дважды щелкните <"Новый источник> данных".
ИЛИ
Выберите пункт< "Новый источник> данных" и нажмите кнопку ОК.
Откроется диалоговое окно "Создание источника данных ".На шаге 1 введите имя, которое идентифицирует источник данных.
На шаге 2 выберите драйвер для типа базы данных, используемой в качестве источника данных.
Примечание
- Если драйверы ODBC, устанавливаемые с Microsoft Query, не поддерживают внешнюю базу данных, к которой вы хотите получить доступ, получите и установите совместимый с Microsoft Office драйвер ODBC от стороннего поставщика, например производителя базы данных. Обратитесь к поставщику базы данных для получения инструкций по установке.
- Для баз данных OLAP не требуются драйверы ODBC. При установке Microsoft Query драйверы устанавливаются для баз данных, созданных с помощью служб Microsoft SQL Server Analysis Services. Для подключения к другим базам данных OLAP необходимо установить драйвер источника данных и клиентское программное обеспечение.
Нажмите кнопку "Подключиться" и введите сведения, необходимые для подключения к источнику данных. Предоставляемые сведения для баз данных, книг Excel и текстовых файлов зависят от выбранного типа источника данных. Вам может потребоваться указать имя для входа, пароль, версию используемой базы данных, расположение базы данных или другие сведения, относящиеся к типу базы данных.
Важно
- Используйте надежные пароли, состоящие из букв в верхнем и нижнем регистре, цифр и символов. В ненадежных паролях не используются сочетания таких элементов. Надежный пароль: Y6dh!et5. Ненадежный пароль: House27. Пароль должен состоять не менее чем из 8 знаков. Лучше всего использовать парольную фразу длиной не менее 14 знаков.
- Очень важно запомнить свой пароль. Если вы забудете пароль, корпорация Майкрософт не сможет его восстановить. Все записанные пароли следует хранить в надежном месте отдельно от сведений, для защиты которых они предназначены.
После ввода необходимых сведений нажмите кнопку ОК или Готово , чтобы вернуться к диалоговому окну "Создание источника данных ".
Если в базе данных есть таблицы и требуется, чтобы определенная таблица автоматически отображалась в мастере запросов, установите флажок для шага 4 и выберите нужную таблицу.
Если вы не хотите вводить имя пользователя и пароль при использовании источника данных, установите флажок Сохранить мой идентификатор пользователя и пароль в поле проверки определения источника данных. Сохраненный пароль не шифруется. Если поле для проверки недоступно, обратитесь к администратору базы данных, чтобы определить, можно ли сделать этот параметр доступным.
Примечание
Не рекомендуется сохранять сведения о входе при подключении к источникам данных. Эта информация может храниться в виде обычного текста, и злоумышленники смогут получить с ее помощью доступ к источнику данных.
После выполнения этих действий в диалоговом окне " Выбор источника данных " появится имя источника данных.
Определение запроса с помощью мастера запросов
Для большинства запросов используйте мастер запросов Мастер запросов упрощает выбор и объединение данных из разных таблиц и полей базы данных. С помощью мастера запросов можно выбрать таблицы и поля, которые требуется включить. Внутреннее соединение (операция запроса, определяющая, что строки из двух таблиц объединяются на основе одинаковых значений полей) создается автоматически, когда мастер распознает поле первичного ключа в одной таблице и поле с тем же именем в другой таблице.
Мастер также можно использовать для сортировки результирующего набора и простой фильтрации. На последнем этапе мастера можно выбрать возврат данных в Excel или уточнить запрос в Microsoft Query. После создания запроса его можно запустить в Excel или Microsoft Query.
Чтобы запустить мастер запросов, выполните указанные ниже действия.
- На вкладке " Данные " в группе "Внешние данные " щелкните "Из других источников", а затем выберите "Из Microsoft Query".
- Убедитесь, что в диалоговом окне "Выбор источника данных" установлен флажок "Проверка с помощью мастера запросов".
- Дважды щелкните источник данных, который нужно использовать.
ИЛИ
Выберите источник данных, который вы хотите использовать, и нажмите кнопку ОК.
Работа непосредственно в Microsoft Query для других типов запросов Если вы хотите создать более сложный запрос, чем разрешено мастером запросов, вы можете работать непосредственно в Microsoft Query. Microsoft Query можно использовать для просмотра и изменения запросов, создаваемых в мастере запросов, или создавать новые запросы без использования мастера. Работайте непосредственно в Microsoft Query, если необходимо создать запросы, которые выполняют следующие задачи:
- Выделение определенных данных из поля В больших базах данных может потребоваться выбрать часть данных в поле и опустить ненужные данные. Например, если вам нужны данные о двух товарах в поле, содержащем сведения о многих товарах, можно использовать условия для выбора данных только для двух нужных продуктов.
- Получайте данные на основе разных условий при каждом выполнении запроса Если вам нужно создать одинаковый отчет Excel или сводку по нескольким областям с одними и теми же внешними данными (например, отдельный отчет о продажах для каждого региона), вы можете создать запрос с параметрами. При выполнении запроса с параметрами вам предлагается указать значение, которое будет использоваться в качестве условия при выборе записей. Например, с помощью запроса с параметрами можно указать определенный регион, и вы можете повторно использовать этот запрос для создания каждого из региональных отчетов о продажах.
- Объединение данных различными способами Внутренние соединения, создаваемые мастером запросов, — это наиболее распространенный тип соединения, используемый при создании запросов. Однако иногда требуется использовать другой тип объединения. Например, если есть таблица сведений о продажах продуктов и таблица сведений о клиентах, внутреннее объединение (тип, создаваемый мастером запросов) не позволит получить записи о клиентах, которые не совершали покупок. С помощью Microsoft Query можно объединить эти таблицы, чтобы получать все записи о клиентах, а также данные о продажах тех клиентов, которые совершили покупки.
Чтобы запустить Microsoft Query, выполните следующие действия.
- На вкладке " Данные " в группе "Внешние данные " щелкните "Из других источников", а затем выберите "Из Microsoft Query".
- В диалоговом окне "Выбор источника данных" убедитесь, что поле "Проверка с помощью мастера запросов с помощью мастера запросов" снято.
- Дважды щелкните источник данных, который нужно использовать.
ИЛИ
Выберите источник данных, который вы хотите использовать, и нажмите кнопку ОК.
Повторное использование и совместное использование запросов Как в мастере запросов, так и в Microsoft Query запросы можно сохранять в виде файла DQY, который можно изменять, использовать повторно и совместно. Excel может открывать файлы DQY напрямую, что позволяет вам или другим пользователям создавать дополнительные диапазоны внешних данных из того же запроса.
Чтобы открыть сохраненный запрос из Excel:
- На вкладке " Данные " в группе "Внешние данные " щелкните "Из других источников", а затем выберите "Из Microsoft Query". Откроется диалоговое окно "Выбор источника данных ".
- В диалоговом окне "Выбор источника данных " откройте вкладку "Запросы ".
- Дважды щелкните сохраненный запрос, который нужно открыть. Запрос отображается в Microsoft Query.
Если вы хотите открыть сохраненный запрос, если Microsoft Query уже открыт, щелкните меню "Файл Microsoft Query" и выберите пункт "Открыть".
Если дважды щелкнуть DQY-файл, приложение Excel откроет его, выполнит запрос, а затем вставит результаты на новый лист.
Если вы хотите поделиться сводкой или отчетом Excel, основанными на внешних данных, вы можете предоставить другим пользователям книгу, содержащую диапазон внешних данных, или создать шаблон. Шаблон позволяет сохранить сводку или отчет без сохранения внешних данных, чтобы уменьшить размер файла. Внешние данные извлекаются, когда пользователь открывает шаблон отчета.
Работа с данными в Excel
После создания запроса в мастере запросов или Microsoft Query можно вернуть данные на лист Excel. Данные становятся внешним диапазоном данных или отчетом сводной таблицы, который можно форматировать и обновлять.
Форматирование полученных данных В Excel можно использовать такие инструменты, как диаграммы или автоматические промежуточные итоги, для представления и обобщения данных, полученных с помощью Microsoft Query. Вы можете отформатировать данные, и форматирование сохранится при обновлении внешних данных. Вместо имен полей можно использовать собственные метки столбцов и автоматически добавлять номера строк.
Excel может автоматически форматировать новые данные, вводимые в конце диапазона, чтобы они соответствовали предыдущим строкам. Excel также может автоматически копировать формулы, повторенные в предыдущих строках, и расширять их на дополнительные строки.
Примечание
Чтобы форматы и формулы были расширены до новых строк в диапазоне, они должны отображаться по крайней мере в трех из пяти предыдущих строк.
Этот параметр можно включить (или отключить снова) в любое время.
- Выберите пункт "Дополнительные параметры> файла".>
- В разделе "Параметры редактирования" установите флажок Расширить форматы диапазонов данных и формулы проверка. Чтобы снова отключить автоматическое форматирование диапазона данных, снимите этот флажок "Проверка".
Обновление внешних данных При обновлении внешних данных запрос выполняется для получения любых новых или измененных данных, соответствующих спецификациям. Вы можете обновить запрос как в Microsoft Query, так и в Excel. В Excel доступно несколько вариантов обновления запросов, включая обновление данных при открытии книги и их автоматическое обновление через определенные интервалы времени. Вы можете продолжать работать в Excel во время обновления данных, а также выполнять проверку состояния во время обновления данных. Дополнительные сведения см. в статье "Обновление подключения к внешним данным в Excel".