Microsoft Query
Статья первоначально опубликована командой Excel (Excel Team) на английском языке здесь.
Важно: Данная статья переведена с помощью машинного перевода, см. Используйте английский вариант этой статьи, который находится, в качестве справочного материала. С помощью Microsoft Query для извлечения данных из внешних источников. С помощью Microsoft Query для извлечения данных из корпоративных баз данных и файлов, не нужно повторно вводить данные, которые требуется проанализировать в Microsoft Excel. Можно также обновить отчеты Excel и сводки автоматически из исходной базы данных при каждом обновлении базы данных с новыми данными.
Microsoft Query передает данные из внешних источников в Microsoft Excel. Microsoft Query — это необязательная возможность Excel, которая включает Мастер запросов. Непосредственная работа с Microsoft Query необходима в тех случаях, когда в запросе участвуют условия отбора, требующие вычислений, или параметры. Только пишется ручками, а не мышкой, потому что графически MS Query не умеет такие запросы отображать (не дорос он, понимаешь, до Access'а). Ниже выяснили, что умеет Так что жмете пиктограмму SQL и редактируете свой.
В этой статье Обзор возможностей приложения 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 сведения по запросу и источнику данных; таким образом, при обновлении данных возможно повторное подключение к базе данных.
Использование Microsoft Query для импорта данных Для импорта внешних данных в приложение Excel с помощью Microsoft Query выполняются следующие основные процедуры, каждая из которых более подробно описывается в последующих разделах. Подключение к источнику данных Что такое источник данных? В источнике данных хранится набор сведений, который позволяет приложениям Microsoft Excel и Microsoft Query подключаться к внешней базе данных. При использовании приложения Microsoft Query для настройки источника данных укажите имя источника данных, а затем введите имя и местоположение базы данных или сервера, тип базы данных, идентификатор, имя и пароль для входа. Данные также должны включать имя драйвера ODBC или драйвера источника данных, т. Программы, которые устанавливают подключение к определенному типу базы данных.
Для настройки источника данных с использованием приложения Microsoft Query выполните следующие действия:. На вкладке Данные в группе Получить внешние данные нажмите кнопку Из других источников, а затем выберите команду Из Microsoft Query. Выполните одно из следующих действий. Чтобы указать источник данных для базы данных, текстового файла или книги Excel, перейдите на вкладку Базы данных. Чтобы указать источник данных куба OLAP, перейдите на вкладку Кубы OLAP. Эта вкладка доступна только в том случае, если вы запустили программу Microsoft Query из Excel.
Должностная инструкция специалиста sap. Дважды щелкните пункт.или- Щелкните пункт, а затем нажмите кнопку ОК. Откроется диалоговое окно Создание нового источника данных.
На первом шаге введите имя для источника данных. На втором шаге выберите драйвер, соответствующий типу базы данных, используемой в качестве источника. Если внешняя база данных, к которой нужно получить доступ, не поддерживается драйверами ODBC, установленными вместе с приложением Microsoft Query, необходимо приобрести и установить совместимые с Microsoft Office драйверы ODBC от сторонних поставщиков, например, от производителя соответствующей базы данных. За инструкциями по установке обращайтесь к поставщику базы данных.
Примечание: Базы данных OLAP не требуют драйверов ODBC. При установке приложения Microsoft Query устанавливаются драйверы для баз данных, созданных с использованием служб анализа Microsoft SQL Server. Для подключения к другим базам данных OLAP нужно установить драйвер источника данных и клиентское программное обеспечение.
Нажмите кнопку Связь, а затем введите сведения, необходимые для подключения к базе данных. Для баз данных, листов Excel и текстовых файлов вводимые сведения зависят от типа выбранного источника данных. Может понадобиться ввести имя и пароль для входа, номер версии используемой базы данных, ее расположение или другие сведения, зависящие от типа базы данных. Используйте надежные пароли, состоящие из букв в верхнем и нижнем регистре, цифр и символов. В ненадежных паролях не используются сочетания таких элементов.
Надежный пароль: Y6dh!et5. Ненадежный пароль: House27. Пароль должен состоять не менее чем из 8 знаков. Лучше всего использовать парольную фразу длиной не менее 14 знаков. Очень важно запомнить пароль, поскольку восстановить его с помощью корпорации Майкрософт будет невозможно.
Все записанные пароли следует хранить в надежном месте вдали от сведений, для защиты которых они предназначены. После ввода необходимых сведений нажмите кнопку OK или Готово для возврата в диалоговое окно Создание нового источника данных. Если база данных содержит таблицы и вы хотите конкретной таблицы автоматически отображать в мастере запросов, установите флажок для шага 4 и нажмите кнопку таблицы, который вы хотите. Чтобы не вводить имя и пароль для входа при использовании источника данных, установите флажок Сохранить имя и пароль в определении источника данных. Сохраняемый пароль не зашифровывается. Если этот флажок недоступен, обратитесь к администратору базы данных.
Подведем некоторые итоги изучения курса “Йога для начинающих за 30 дней”. Итак, мы освоили. Упрожнения йоги. Упражнения Йоги для Начинающих, асаны. Все идет как задумано Не все идет как задумано. В каталоге асан мы собрали самые популярные и необходимые упражнения йоги, подходящие. Можно ли заниматься йогой дома? Просто нужно соблюдать осторожность и не.
Примечание по безопасности: Не сохраняйте учетные данные при подключении к источнику данных. Эта информация может сохраняться как обычный текст, что позволит злонамеренному пользователю получить доступ и нарушить безопасность источника данных. По завершении этих шагов имя источника данных появится в диалоговом окне Выбор источника данных. Использование мастера запросов для создания запроса Использование мастера запросов для создания большинства запросов Мастер запросов позволяет легко выбирать и объединять данные из разных таблиц и полей базы данных. Используя мастер запросов, можно выбирать необходимые таблицы и поля. Внутреннее объединение (операция запроса, которая объединяет строки из двух таблиц на основе одинаковых значений полей) создается автоматически, когда мастер распознает ключевое поле в одной таблице и поле с таким же именем в другой. Можно также использовать мастер для сортировки набора результатов и для простой фильтрации.
На последнем шаге мастера можно передать данные в приложение 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-файлы, что позволяет другим пользователям создавать дополнительные диапазоны внешних данных из того же запроса. Чтобы открыть сохраненный запрос из Microsoft Excel, выполните следующие действия:.
Дать Развернутый Отзыв
На вкладке Данные в группе Получить внешние данные нажмите кнопку Из других источников, а затем выберите команду Из Microsoft Query. Появится диалоговое окно Выбор источника данных. В диалоговом окне Выбор источника данных перейдите на вкладку Запросы. Дважды щелкните сохраненный запрос, который нужно открыть. Этот запрос отобразится в Microsoft Query. Если нужно открыть сохраненный запрос и приложение Microsoft Query уже открыто, в меню Файл приложения выберите команду Открыть.
Если дважды щелкнуть имя DQY-файла, откроется приложение Excel, которое выполнит запрос и вставит результаты в новый лист. Для совместного использования сводки или отчета Excel, основанных на внешних данных, можно передать другим пользователям книгу, содержащую диапазон внешних данных, или создать шаблон.
Шаблон позволяет сохранить сводку или отчет, не сохраняя внешние данные, благодаря чему уменьшается размер файла. Внешние данные загружаются при открытии шаблона отчета. Работа с внешними данными в Excel После создания запроса в мастере запросов или в Microsoft Query можно передать данные на лист Excel. Данные при этом становятся диапазон внешних данных или отчет сводной таблицы, которые можно форматировать и обновлять. Форматирование извлеченных данных Приложение Excel позволяет использовать для представления данных, извлеченных с помощью Microsoft Query, и составления сводок такие инструменты, как диаграммы и автоматические промежуточные итоги. Данные можно отформатировать, и это форматирование сохраняется при обновлении внешних данных. Можно также вводить собственные заголовки столбцов вместо имен полей и автоматически добавлять номера строк.
В приложении Excel можно автоматически форматировать новые данные, введенные в конце диапазона, чтобы они соответствовали предшествующим строкам. Приложение Excel также позволяет автоматически копировать формулы, повторяющиеся в предшествующих строках, и распространять их на добавленные строки. Примечание: Чтобы форматирование и формулы распространялись на новые строки диапазона, они должны быть отображены по крайней мере в трех из пяти предшествующих строках. Можно включить (или отключить) этот параметр в любое время следующим образом:. Нажмите кнопку Microsoft Office, а затем — кнопку Параметры Excel и выберите категорию Дополнительно. В разделе Правка установите флажок Расширять форматы и формулы в диапазонах данных, чтобы включить этот параметр.
Чтобы отключить автоматическое форматирование диапазона данных, снимите этот флажок. Обновление внешних данных При обновлении внешних данных выполните запрос для получения новых или измененных данных, который соответствует вашей спецификации. Можно обновить запрос в Microsoft Query и Excel. Excel предоставляет несколько параметров для обновления запросов, включая обновление данных при каждом открытии книги и автоматическое обновление через заданные интервалы. Можно продолжить работу в Microsoft Excel обновление данных, а также можно проверить состояние при обновлении данных. Дополнительные сведения читайте в статье.
Microsoft Power Query для Excel – это новая надстройка, которая обеспечивает удобный поиск, трансформацию и обновление данных для информационных работников, дашборд профессионалов и других пользователей. Power Query дает нам новую вкладку в ленточном интерфейсе Excel, где мы сможем импортировать, преобразовывать и объединять данные из различных источников. В дополнение к стандартным источникам данных, таких как Microsoft Access, SQL и текстовым файлам, Power Query позволяет импортировать из Active Directory, Azure, OData и Hadoop. Power Query – это бесплатная надстройка для Excel 2010 и Excel 2013, которую можно.
Обратите внимание, что Power Query ранее был известен под кодовым именем “Data Explorer”. Аналогично, Power Map ранее была известна как проект “GeoFlow”. В сегодняшней статье, я покажу вам несколько примеров, которые можно сделать с помощью надстройки Power Query. Основы использования Power Query После установки надстройки, вы увидите новую вкладку Power Query на ленте Excel. В группе Get External Data в левой части вкладки можно выбрать источники данных. В этом первом примере я выберу From Web и введу интересующий меня сайт.
Через несколько секунд, Excel активирует окно запроса с, найденными на сайте, таблицами. Вы можете щелкнуть по любой из них, чтобы посмотреть, как выглядят данные. Мы уже рассматривали в одной из предыдущих статей. Вы можете удалить данные из таблицы непосредственно в окне запроса. На картинке внизу, я щелкнул правой кнопкой мыши по заголовку столбца и выбрал Use First Row As Header, что означает Использовать первую строку в качестве заголовка. Обратите внимание на строку формул сверху.
Microsoft Query Скачать
Эта строка содержит синтаксис, необходимый для запуска шагов, которые вы выбрали. Выдвижная панель Steps позволяет просматривать или удалять любые действия, которые были применены к запросу. Вы также можете вручную менять формулу, но об этом немного позже. После того, как запрос будет настроен, можно вернуть данные на рабочий лист Excel. Как вы уже могли догадаться, чтобы обновить данные, щелкните правой кнопкой мыши по таблице и выберите Обновить. Работа с каналами данных Инструмент Power Query имеет механизм, поиска данных в интернет. К примеру, вам захотелось посмотреть календарь соревнований зимних Олимпийских игр в Сочи.
Для этого вы можете воспользоваться Power Query. Щелкаем по кнопке Online Search, в появившемся диалоговом окне вводим поисковый запрос Sochi. Результатом работы поисковой машины Excel станет список статей, где встречается слово Sochi. Если мы щелкнем по любому из них, данные появятся на рабочем листе.
Получаем список файлов в папке Появлялась ли когда-нибудь у вас нужда выгрузить список файлов в папке? И для этого я писал отдельный макрос. Ну, это дела давно минувших дней, теперь Power Query позволяет сделать тоже самое в несколько щелчков. Выбираем From File в группе Get External Data и определяем папку, из которой мы хотим извлечь список. Excel активирует окно запроса, где будет виден список всех файлов в папке. К тому же вы можете добавить колонки с дополнительными атрибутами файла, щелкнув по иконке, находящейся справа от заголовка столбца Attributes. После подтверждения изменений, мы имеем обновляемую таблицу со списком всех файлов в указанной папке.
Не уверен, что Power Query был предназначен именно для такой выгрузки, но я считаю эту функцию очень удобной. Консолидация данных, или как создать одну таблицу из нескольких Excel файлов Как я уже упоминал ранее, каждое действие в Power Query создает некую формулу, которая описывает новый шаг в процессе извлечения данных. Чтобы открыть окно с формулами запроса, щелкните по иконке в виде листочка в окне запроса: Изначально синтаксис выглядит безнадежно непроницаемым. Но немного поигравшись с Power Query, вы поймете, как он работает. Ключ в том, что необходимо сделать какие-либо действия в запросе, а затем посмотреть, как изменится код.
Через некоторое время вы сможете создавать собственные запросы для выполнения сложных действий. Вы также можете посетить для большего понимания. Для примера предположим, что мне необходимо получить данные с двух файлов Excel и поместить их в одну таблицу. Структура файлов одинакова (в каждом одинаковые колонки).
Банальный копипэйст меня не устраивает, так как эту операцию необходимо выполнять еженедельно. Требуется что-то более автоматизированное.
Microsoft Query Что Это
Нам поможет Power Query, с помощью которого мы сможем написать запрос для получения данных. Для начала необходимо включить расширенные возможности редактирования запросов, во вкладке Power Query в группе Machine Settings щелкаем по кнопке Options - Enable Advanced Query Editing. Теперь мы готовы писать запрос. В группе Get External Data выбираем From Other Sourses - Blank Query. Данная опция позволяет мне напрямую ввести необходимый код. В данном случае я скомбинировал таблицы обоих файлов, при этом первую строку использовал как заголовки. После подтверждения всех изменений, Excel выдаст консолидированную таблицу с данными обоих файлов.
И самое замечательное во всем этом – это возможность обновления в любое время. Важная мысль: если вы отправите файл содержащий запрос Power Query кому-то у кого он не был установлен, этот пользователь увидит данные таблицы, но не сможет их обновить. Вам также могут быть интересны следующие статьи. Рубрика:.