Как включить power query в excel 2016

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/?LinkID=313430
После скачивания необходимо закрыть 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 каждый пользователь не имея никаких навыков программирования может достаточно легко получить данные практически из любого источника и сразу же на месте привести к их нужному виду.

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

Источник: www.excel-vba.ru

Надстройки Power Query и Power Pivot

Power Query и Power Pivot — надстройки Excel для легкой работы с тяжелыми файлами. С помощью Power Query можно подключать и обрабатывать источники информации произвольного вида, а в Power Pivot – выполнять сложные расчеты и создавать модель данных. Также Power Query включен в новый продукт Microsoft – Power BI.

Надстройка Power Pivot

Те, кто часто пользуются сводными таблицами , понимают, насколько они удобны и эффективны в работе.
Power Pivot — это надстройка Excel, которая расширяет функционал сводных таблиц и позволяет работать с файлами больше 1 048 576 строк.

Как работает:
Запуск во всех версиях Excel от 2010 до 2016 выглядит одинаково: меню Power Pivot → Управление.

Возможности Power Pivot:

  • Создание сводных таблиц на основе нескольких таблиц Excel без формулы ВПР.
    В «обычном» Excel сложно создать одну таблицу из нескольких. В Power Pivot таблицы объединяются с помощью графического проектирования, в котором связи между несколькими таблицами создаются с помощью перетаскивания полей.
  • Импорт более 100 миллионов строк в рабочую книгу.
    Power Pivot позволяет импортировать данные из различных источников. Единственное ограничение по объему — размер файла книги, равный 2 Гбайт, и объем оперативной памяти. Благодаря алгоритму сжатия текстовый файл объемом 50 Мбайт сжимается до 4 Мбайт, занимаемых файлом с таблицей.
  • Создание улучшенных формул с помощью языка DAX .
    В Power Pivot можно писать формулы DAX в столбцах, и создавать меры — вычисления под таблицей.
    Подробнее о DAX-формулах: Основные формулы Power Pivot.

Как скачать Power Pivot

Надстройка Power Pivot для Excel 2010 года скачивается бесплатно с сайта Microsoft.

В Excel 2013 и 2016 года надстройка идет в составе программы, но только в версии Pro Plus . В других лицензиях эта надстройка недоступна.

О том, как работать с надстройкой Power Pivot, можно прочитать в статье:

Надстройка Power Query

Надстройка Power Query появилась в Excel относительно недавно, и доступна бесплатно для версий Excel 2010, 2013 года. В Excel 2016 надстройка уже встроена по умолчанию.


Новые возможности для анализа данных:

  • Несомненный плюс – из Excel можно подключаться напрямую к разным источникам данных (файлы excel, csv, текстовые файлы, базы данных, папки и так далее).
  • Данные в подключенных источниках можно преобразовывать, объединять, делать расчеты. При этом последовательность операций записывается на языке M , так что при последующих подключениях повторять расчеты еще раз не нужно – они выполнятся автоматически.
Читайте также:  Циклическая ссылка в excel


Как работает:

  • В меню Excel 2010, 2013 надстройка появляется в виде отдельной вкладки «Power Query»:

  • В Excel 2016 надстройка уже встроена по умолчанию на вкладке Данные – блок «Скачать и преобразовать».

Возможностей для анализа открывается очень много – например, можно объединить данные в компании, или подключаться к источникам информации напрямую.

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

Как скачать Power Query

Надстройка устанавливается бесплатно и доступна для Excel начиная с версий 2010 года. Для Excel 2010 и 2013 скачивается с сайта Microsoft.

Для Excel 2016 ничего скачивать и устанавливать не нужно, так как эта надстройка идет в составе всех лицензий.

О том, как работать с надстройкой Power Query, можно прочитать в статье:

Как добавить в Excel надстройки Power Pivot и Power Query

Для Excel 2010 и 2013 после скачивания надстроек нужно добавить их в меню Excel.
Для этого открыть Excel и перейти в меню Надстройки:
Файл → Параметры → Надстройки → в Выпадающем меню выбрать → Надстройки COM → Перейти → В открывшемся окне выбрать надстройки.

Источник: finalytics.pro

Бесплатный курс обучения 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?id=39379 и нажмите «Скачать». Потом отметьте галочкой файл для вашей разрядности операционной системы (64 или 32 бит). После скачивания файла запустите установку.

Теперь в главном меню 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. Слева список запросов, справа примененные шаги к запросу. Теперь продолжим работать с нашим запросом.

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

Читайте также:  Как в excel посчитать количество уникальных значений в excel

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

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

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

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

Источник: comrade-xl.ru

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/?LinkID=313430
После скачивания необходимо закрыть 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 каждый пользователь не имея никаких навыков программирования может достаточно легко получить данные практически из любого источника и сразу же на месте привести к их нужному виду.

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

Источник: www.excel-vba.ru

Как включить power query в excel 2016

Вопрос

I have recently using excel 2016 in newly installed windows. I just found out that power query does not work for excel 2016 yet,

so is there any beta version of power query for excel 2016 or is power query will become part of the excel 2016?

Ответы

Power Query is part of Excel 2016 and can be found under the Data tab.

  • Помечено в качестве ответа Curt Hagenlocher 1 апреля 2015 г. 11:48

Все ответы

Power Query is part of Excel 2016 and can be found under the Data tab.

  • Помечено в качестве ответа Curt Hagenlocher 1 апреля 2015 г. 11:48

PowerQuery is there but we lost Online Search (or I couldn’t find it any more)(the leftmost button on “old” powerquery)

Ahhhh. That explains it. I’ve been looking for that feature.

Curt, feedback on Office2016 Preview: After witnessing how MS leaders like Jen Underwood, Miguel Llopis and others are using twitter and other forms of communication to discuss the near daily changes in either the code or online features of PowerBI components; I’m somewhat frustrated with the lack of similar organization around the Office2016 Preview.

Читайте также:  Создание в excel справочника

I can’t seem to figure out on Twitter or elsewhere who is in control of communication, where release notes are, etc. Seems very disorganized compared to the PowerBI and Windows10 (Gabe) programs.

Thanks for the feedback, Eric and Henn.

We’re still working out the communications plan for announcing new Power Query features in Excel 2016. FWIW, the desire for both Excel & PQ teams is to keep the PQ add-in and Excel 2016 at parity (or very close) in terms of new features and also timing for updates, so you may expect that, after Office 2016 RTM, there will be a single communication for PQ updates that includes both versions. This will be posted to the usual channels (Excel Blog, Update notifications in PQ add-in or via Excel Click-To-Run updates, Twitter).

Regarding Public Search, as Curt mentioned this was a trade-off. The overall feeling is that the feature isn’t ready for Office prime-time yet, given that the support is limited to English-US data (Wikipedia & Data.gov, primarily). There are also data freshness & quality issues, as well as search results relevance issues that our Search team needs to work through. We’re looking at ways to improve the feature and bring it into Excel 2016 in the future.

Out of curiosity, Eric & Henn – Would you mind elaborating a bit on why you were looking for this feature? Are you using it for “production” scenarios? If so, one workaround (someone would call it a hack) is to find the desired dataset using the PQ add-in and then manually copying the Query source into Excel 2016 (or just save the workbook in Excel 2013, then open it in Excel 2016). Public Search data is served via a publicly available OData feed which doesn’t require any special type of authentication, so despite Public Search not showing up in Excel 2016, you can still import data from those feeds.

Источник: social.technet.microsoft.com

Power View в Excel 2016

Power View в Excel 2016

Добрый день, уважаемые подписчики и читатели блога! Я, как и водится, всегда описываю нововведения в Excel с заметным опозданием. Что поделать — я консерватор (хотя теперь модно говорить — слоупок).

Итак, сегодня начнём разговор о Power инструментах в Excel. В этом видео на канале уже был обзор Power Map. В этой статье мы использовали приложение Bing Maps.

Что такое Power View?

Ответ очень прост — инструмент визуализации данных из таблицы. Есть свои ограничения (обязательна установка Silver Light от Microsoft), есть и масса плюсов в виде интерактивных отчётов, диаграмм, таблиц. Например, у нас есть таблица, нужно чтобы эти данные отобразились в диаграмме. Оговорюсь сразу — таблица большая и всю её можно найти на сайте ЦИК.

Включение Power View в Excel.

Не совсем понятные вещи стали твориться с самого начала — включить надстройку недостаточно! Нам придётся пройти небольшой квест:

  • установить MS Silverlight;
  • включить надстройку в Excel;
  • добавить кнопку Power View на вкладку «Вставка».

В первых двух пунктах трудного ничего нет — скачали к себе установщик, сняли галки с предложения использовать Bing, далее, далее, «Ок».

В плане включения надстройки ещё проще — идём в «Параметры», «Надстройки», внизу выбираем из списка «Надстройки COM» или переходим на вкладку «Разработчик», ищем пункт «Надстройки COM», в появившемся окне отмечаем галкой «Power View», жмём «Ок».

Мы не из пугливых:

  • идём в «Параметры»;
  • выбираем пункт «Настроить ленту»;
  • раскрываем список вкладки «Вставка»;
  • создаём новую группу «Отчёты» (или как вам захочется);
  • слева выбираем список «Все команды»;
  • добавляем Power VIew в созданную группу команд.

Подробно я рассказывал о настройке ленты в Excel вот в этом видео.

После добавления на вкладке «Вставка» появится новый блок кнопок и сама кнопка «Power View».

Тэк-с, своего мы добились. Теперь настало время загрузить таблицу. Как будете её загружать — дело десятое (советую Power Query), главное, чтобы она появилась на листе Excel.

Обязательно сделайте ваши таблицы умными — щёлкнуть в любую ячейку и нажать Ctrl+T, в появившемся окне «Ок», не снимать галку «Таблица с заголовками». Если будете использовать Power Query — у вас уже будут умные таблицы.

Встаём во вторую таблицу и на вкладке «Вставка» нажимаем Power View.

Полностью описывать возможности Power View — это на несколько статей. Сегодня сконцентрируемся на основных задачах. Преобразование таблицы в диаграмму и отображением на карте.

Настройка отображения в Power View

Что ж, подредактируем нашу модель данных. Для начала отключим столбец «Процент», чтобы построить круговую диаграмму (ну или столбец «Кол-во голосов», если так будет удобнее). Справа в области запросов снимаем галку со столбца «Процент».

Из представления тут же исчезнет столбец. Теперь можно преобразовать таблицу в диаграмму. Для этого на вкладке «Конструирование» нужно нажать «Другая диаграмма» и выбрать вариант «Круговая диаграмма».

Получим преобразование таблицы в диаграмму с легендой.

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

Есть небольшой минус — чтобы узнать точное количество надо наводить указатель на долю кандидата, мы же просто вставим таблицу с данными, чтобы видеть при щелчке сколько кто набрал. Для этого щёлкнем в свободном месте и отметим галками справа столбец «Кандидат» и «Кол-во голосов». Теперь при щелчке на долю в круговой диаграмме появится и число голосов.

Вот и всё! Таким образом и будем строить наш отчёт. Во второй части статьи (она будет чуть позже) мы поработаем с таблицей по регионам и попробуем отобразить данные на карте. Всем удачи!

Источник: pcandlife.ru