Power query excel 2020

Power Query — обзор и обучение

Power Query — это технология подключения к данным, с помощью которой можно обнаруживать, подключать, объединять и уточнять данные из различных источников для последующего анализа. Функции Power Query доступны в классических версиях Excel и Power BI.

При использовании Power Query часто нужно выполнить несколько стандартных шагов.

Во многих задачах анализа данных используются только некоторые из этих шагов, но в Power Query важен каждый из них. Дополнительные сведения об этих шагах и примеры см. в статье Начало работы с Power Query.

Power Query — одно из трех средств анализа данных, доступных в Excel.

Ресурсы, посвященные Power Query

Приведенные ниже ссылки и сведения помогут вам приступить к работе с Power Query. Разделы и ссылки представлены в том порядке, в котором с ними следует знакомиться начинающему: в статье по первой ссылке объясняется, где взять Power Query, в следующем разделе содержится руководство по быстрому началу работы, а затем следуют учебники и ссылки на сообщества.

По мере появления новых ресурсов мы обновляем ссылки, так что рекомендуем вам время от времени возвращаться к этому списку.

Принципы работы Power Query

С помощью Power Query вы можете искать источники данных, создавать подключения, а затем формировать данные (например, удалить столбец, изменить тип данных или объединить таблицы) в соответствии со своими потребностями. Когда данные сформированы, вы можете поделиться своими выводами или использовать запрос для создания отчетов.

Чтобы понять, как работает Power Query, ознакомьтесь со статьей Начало работы с Power Query.

Где взять Power Query?

Power Query предлагается в виде загружаемого средства для Excel 2013 и Excel 2010. Базовая технология построения запросов, лежащая в основе Power Query, также используется в классической версии Power BI, которая является частью службы Power BI, предлагаемой корпорацией Майкрософт.

Чтобы скачатьтекущий выпуск Power Query, нажмите кнопку ниже.

Примечание: Надстройка Power Query предлагается в Excel 2016 в виде группы команд Скачать и преобразовать. Информация в этой статье относится и к Power Query, и к новой группе. Дополнительные сведения см. в статье Функция «Скачать и преобразовать» в Excel 2016.

Учебники по Power Query

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

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

Если вам нужна более подробная информация о Power Query, ознакомьтесь со следующими ресурсами:

Сообщества, форумы и отзывы

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

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

How to dou

Power Query. Как установить надстройку Power Query

Table of Contents:

В Excel 2016 Power Query не является надстройкой — это собственная функция Excel , так же, как диаграммы и сводные таблицы являются собственными функциями. Если вы работаете с Excel 2016, вам не нужно устанавливать какие-либо дополнительные компоненты. Вы найдете Power Query в Excel 2016, скрытом на вкладке «Данные», в группе «Получить и преобразовать».

В Excel 2016 команды Power Query находятся в группе Get & Transform на вкладке Data.

Если вы работаете с Excel 2010 или Excel 2013, вам необходимо явно загрузить и установить надстройку Power Query. На момент написания этой статьи надстройка Power Query доступна только в том случае, если у вас есть один из этих выпусков Office или Excel:

• Office 2010 Professional Plus: Доступно для покупки через любого ритейлера
• Office 2013 Professional Plus: Доступно только с помощью корпоративного лицензирования
• Office 365 Pro Plus: Доступно с постоянной подпиской на Office365. com
• Автономное издание Excel 2013: Доступно для покупки через любого ритейлера

Если у вас есть какие-либо из этих выпусков, вы можете установить и активировать надстройку Power Query. Просто введите поисковый запрос Excel Power Query add-in в свою любимую поисковую систему, чтобы найти бесплатный установочный пакет.

Обратите внимание, что Microsoft предлагает Power Query для Excel 2010 и Excel 2013 как на 32, так и на 64-разрядных платформах. Обязательно загрузите версию, соответствующую вашей версии Excel, а также платформу, на которой работает ваш компьютер.

После того, как надстройка установлена, активируйте ее, выполнив следующие шаги:

Откройте Excel и найдите команду Power Query на вкладке Insert. Если вы видите, надстройка Power Query уже активирована. Вы можете пропустить оставшиеся шаги.

В Excel 2010 и 2013 надстройка Power Query открывается через собственную вкладку на ленте.

Успешная установка приведет к появлению новой вкладки Power Query на ленте Excel.

Power Query — что такое и почему её необходимо использовать в работе?

Power Query – специальная надстройка для для Excel 2010 и выше. Начиная с версии Excel 2016 эта надстройка встроена в Excel и все команды расположены на вкладке Данные (Data) -группа Скачать и преобразовать (Get & Transform)

Для 2010 и 2013 после подключения надстройки появится новая вкладка — POWER QUERY
Скачать Power Query для 2010 и 2013 можно по ссылке: http://go.microsoft.com/fwlink/?Link > После скачивания необходимо закрыть Excel и запустить скачанный файл. После следующего запуска Excel на панели должна появится новая вкладка — POWER QUERY
Что же дает эта надстройка и почему её надо устанавливать? Если в общих чертах: Power Query является очень мощным инструментом и позволяет получить данные из множества различных источников: Excel, CSV, XML, бд Access и SQL, интернет-страницы, OneDrive, сервисы Google и многие другие:

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

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

А далее с помощью редактора мы можем:

• двумя щелчками мыши объединить данные двух и более столбцов с указанным разделителем
  
• разделить данные одного столбца на несколько отдельных так же по разделителю
  
• без создания всяких функций извлечь из даты только месяц, год, день, количество дней, день недели и т.п.
  
• быстро объединить данные двух и более таблиц(запросов) по ключу. Грубо говоря, аналог ВПР по точному совпадению
  
• нажатием одной кнопки развернуть столбцы таблицы в строки
  
• еще куча всего, чего в одном обзоре не рассмотришь 🙂

Конечно, напрашивается вопрос: что будет, если данные в том же файле Excel изменятся? Например, добавятся строки? А все просто — надо будет лишь обновить запрос — он обновит подключение и данные будут опять обработаны и преобразованы с учетом изменений.

Напоследок еще несколько доводов в пользу Power Query:

• она абсолютна бесплатна
• раньше, чтобы создать полноценную сводную таблицу из нескольких файлов или листов необходимо было писать код на VBA. С помощью PowerQuery это может сделать каждый без всяких кодов(пошаговая видеоинструкция как это сделать приведена в статье План-фактный анализ в Excel при помощи Power Query)
• можно легко и просто получить данные из файлов XML, текстовых файлов и CSV, а потом объединить всю информацию в одну таблицу и. Полагаю сами додумаете — главное собрать данные, а что с ними делать мы всегда придумаем исходя из задачи
• можно получать данные из интернета — и это опять же без написания кодов Visual Basic for Applications(VBA). Тот же курс доллара PowerQuery способна «вытащить» на основании ссылки на сайт и обновлять это автоматически, да еще и «привязать» к расчетам в других таблицах
• можно легко и быстро получить обновляемую информацию даже из файлов Google sheets, что до этого момента даже из VBA было занятием не из простых
• есть встроенные возможности получения данных из аккаунта Facebook. Мне лично этого никогда не требовалось, но краем правого уха подмечал, что кому-то это очень было нужно 🙂

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

Статья помогла? Поделись ссылкой с друзьями!

Бесплатный курс обучения Power Query на 1-2-3. Вводный урок

Файлы к уроку можно скачать по ссылке Файлы к уроку.

В этом вводном уроке курса Excel Power Query на 1-2-3 мы узнаем, что такое Power Query, зачем он нужен, как его установить и запустить.

Если вам удобнее воспринимать видео, то смотрите этот урок на YouTube.

Power Query — это встроенный инструмент Excel для получения и преобразования данных (Get and Transform).

Специалистам по Excel приходится каждый день тратить уйму времени на преобразование данных. Мы работаем с разными источниками и данные далеко не всегда приходят в удобном виде.

Например, каждый месяц вы получаете таблицу с ответами на вопросы теста сотрудников компании, где вы работаете в таком виде:

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

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

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

Как вы думаете, сколько времени уйдет на решение этой задачи в Excel Power Query? Всего несколько минут. Более того, вам достаточно решить эту задачу всего 1 раз. А когда придут новые данные достаточно будет всего лишь нажать «Обновить».

Как установить Power Query

Если у вас Excel от 2016 версии и новее, то Power Query устанавливать не нужно. Он уже встроен в Excel.

Если у вас Excel 2010 или 2013, то Power Query нужно сначала скачать. Перейдите по ссылке https://www.microsoft.com/ru-ru/download/details.aspx? >

Теперь в главном меню Excel должна появиться еще одна вкладка. Если вкладка не появилась, то перейдите в меню Файл — Параметры — Настройки — Управление — Надстройки СОМ. В открывшемся окне отметьте галочкой пункт Power Query.

Добавить кнопки Power Query на панель быстрого доступа

Я рекомендую добавить кнопки для работы с Power Query на панель быстрого доступа. Это ускоряет работу. Я настоятельно рекомендую добавить 3 команды:

• Изменить запрос
• Запросы и подключения
• Запустить редактор запросов

Если вы не знаете как добавлять кнопки в панель быстрого доступа, то посмотрите видео-версию урока на YouTube.

Также вы можете скачать готовые настройки панели быстрого доступа по ссылке Файлы к уроку и импортировать их. Кликните правой кнопкой мыши по панели быстрого доступа.

Нажмите «Настройка панели быстрого доступа». В появившемся окне справа снизу найдите кнопку «Экспорт/импорт». Нажмите ее и укажите путь к скачанному файлу.

Предварительные настройки Power Query

Откройте редактор запросов любым удобным способом:

• При помощи созданной кнопки в панели быстрого доступа
• Вкладка Данные — Группа Получить и преобразовать данные —Получить данные — Запустить редактор запросов

В окне Редактора запросов нажмите Файл — Параметры и настройки — Параметры запроса. В пункте Загрузка данных сделайте настройки как на картинке:

Теперь перейдите в пункт Редактор Power Query и поставьте галочку «Отобразить редактор запросов».

Далее переходим в пункт Конфиденциальность и выбираем «Всегда игнорировать уровни конфиденциальности».

Жмем ОК. Настройки вступят в силу при следующем запуске редактора запросов.

Создаем первый запрос Power Query

Создадим первый запрос к веб-странице с таблицей состава индекса Dow Jones. Выполняем следующие действия:

1. Запускаем редактор запросов
2. Вкладка Главная — Создать источник — Другие источники — Интернет
3. Вводим адрес https://finance.yahoo.com/quote/%5EDJI/components?ltr=1
4. Нажимаем Подключение
5. Слева указываем нужную таблицу и жмем ОК

Перед вам появится следующее окно редактора запросов Power Query:

Сверху мы видим меню в привычном ленточном интерфейсе как и во всех продуктах Microsoft Office. Слева список запросов, справа примененные шаги к запросу. Теперь продолжим работать с нашим запросом.

Удалим шаг Изменить тип. Для этого в списке шагов справа нажмем на крестик слева от названия шага. Укажем тип данных для каждого столбца.

По умолчанию Power Query определил тип данных каждого столбца как текстовый. Об этом говорит пиктограмма с символами ABC слева от названий столбцов.

Попробуйте нажать на пиктограмму ABC столбца Last Price и указать тип данных десятичное число. В столбце во всех строках отобразятся ошибки. Это произошло из-за того, что в нашем регионе целая и дробная части разделяются запятыми, а в таблице с этого сайта точкой. В таком случае нужно указать тип с использованием локали.

Нажмите правой кнопкой мыши на название столбца Last Price — Тип изменения — Используя локаль. Укажите тип данных целое число и языковой стандарт Английский США. Теперь все получилось. То же самое проделайте для других числовых столбцов.

Теперь перейдите на вкладку Главная, щелкните на нижнюю часть кнопки Закрыть и загрузить, в списке выберете Закрыть и загрузить в. Отметьте пункт Таблица и укажите место, куда эту таблицу поместить, потом нажмите ОК. Наш первый запрос готов!