Power query как включить надстройку

Запуск надстройки Power Pivot для Excel

Power Pivot — это надстройка, с помощью которую можно выполнять мощный анализ данных в Excel. Надстройка встроена в определенные версии Office, но по умолчанию не включена.

Список версий Office, которые включают Power Pivot, а также список версий, в которых их нет, см. в вопросе Где Power Pivot?

Вот как можно включить Power Pivot перед первым использованием.

1. Перейдите на вкладку Файл >Параметры >Надстройки.
2. В поле Управление выберите Надстройки COM и нажмите Перейти.
3. Установите флажок Microsoft Office Power Pivot и нажмите кнопку ОК. Если установлены другие версии Power Pivot, то они будут также перечислены в списке надстроек COM. Выберите надстройку Power Pivot для Excel.

На ленте появится вкладка Power Pivot.

Откройте окно Power Pivot.

1. Щелкните Power Pivot. На этой вкладке можно работать со сводными таблицами, вычисляемыми полями и ключевыми показателями эффективности Power Pivot, а также создавать связанные таблицы.
2. Нажмите кнопку Управление.

Откроется окно Power Pivot. Здесь вы можете нажать кнопку «Внешние данные», чтобы использовать мастер импорта таблиц для фильтрации данных при их добавлении в файл, создания связей между таблицами, обогащения данных вычислениями и выражениями и создании сводных таблиц и сводных диаграмм на их основе.

Устранение неполадок: исчезновение ленты Power Pivot

В редких случаях лента Power Pivot исчезает из меню, когда Excel определяет, что надстройка нарушает его работу. Это может произойти, если Excel неожиданно завершает работу при открытом окне Power Pivot. Чтобы восстановить меню Power Pivot:

1. Выберите Файл >Параметры >Надстройки.
2. В поле Управление выберите Отключенные объекты >Перейти.
3. Выберите Microsoft OfficePower Pivot и нажмите кнопку Включить.

Если не удается восстановить ленту Power Pivot, выполнив указанные выше действия, или лента исчезает, когда вы закрываете и снова открываете Excel, сделайте следующее:

1. закройте Excel;
2. откройте меню Пуск >Выполнить и введите команду regedit;
3. В редакторе реестра разверните следующий раздел: Для Excel 2013: HKEY_CURRENT_USER >Software >Microsoft >Office >15.0 >User Settings. Для Excel 2016: HKEY_CURRENT_USER >Software >Microsoft >Office >16.0 >User Settings
4. щелкните правой кнопкой мыши PowerPivotExcelAddin, а затем нажмите Удалить;
5. вернитесь в верхнюю часть редактора реестра;
6. разверните раздел HKEY_CURRENT_USER >Software >Microsoft >Office >Excel >Addins;
7. щелкните правой кнопкой мыши PowerPivotExcelClientAddIn.NativeEntry.1, а затем нажмите Удалить;
8. закройте редактор реестра;
9. откройте Excel;
10. включите надстройку, выполнив действия, описанные в начале этой статьи.

Power query как включить надстройку

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Надстройка Excel Power Query: объединяем данные без потерь

Если вы находитесь в поиске эффективных способов упростить работу в Excel и ускорить подготовку отчетности, Power Query – это то, что нужно!

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

Надстройка обладает целым рядом плюсов:

• быстрый импорт из нескольких источников;
• очищение и обработка данных без сложных формул;
• создание запросов с множеством параметров.

Не будем долго останавливаться на перечислении достоинств и сразу перейдем к конкретике.

Объединяем таблицы с разными заголовками

У нас три таблицы. В одной из них «Квартал» превратился в «Кв», во второй и вовсе исчезает и только в третьей остается прежним. Та же проблема коснулась и других заголовков:

• «Заказчик» – «Клиент»
• «Количество» – «Кол-во»
• «Наименование» – «Номенклатура»

Надстройка помогает справиться с непостоянством заголовков. Информация послушно собирается из файлов и выстраивается под соответствующими заголовками.

Отлично, а теперь усложним условия!

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

Умная надстройка Excel Power Query и несколько функций языка М с легкостью выполняют задачу!

Все данные таблиц объединяются в одну без потерь. И непостоянство заголовков – не помеха!

Добавляем столбцы

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

Удобнее всего увидеть эффективность Power Query в наглядном сравнении с Excel.

К примеру, в таблицу с затратами требуется добавить столбцы:

Обращаемся к функционалу Excel.

1. Применяем функцию =ГОД, чтобы извлечь год. Пока все просто.

2. А вот с названием месяца посложнее. Если выбрать функцию =МЕСЯЦ, то мы извлекаем порядковый номер. Поэтому воспользуемся функцией =ТЕКСТ.

3. При это обязательно поставим вторым аргументом формат. Вспоминаем, что надо указать, чтобы получить полное название месяца. Формат такой – «ММММ».

4. Теперь займемся кварталом. Подходящей функции для извлечения квартала нет. Придется подключить фантазию:

• извлекаем из даты порядковый номер месяца – функция =МЕСЯЦ
• делим получившееся число на 3 по количеству месяцев в квартале
• округляем до 0 знаков после запятой – функция =ОКРУГЛВВЕРХ

И, наконец, приложив массу усилий, получаем желаемый результат.

А теперь посмотрим, как добавить столбцы и объединить данные в Power Query:

• загружаем таблицу в редактор
• переходим на вкладку «Добавление столбца»
• выбираем в меню «Дата»: «Год», «Название месяца», «Квартал»

Готово! Надстройка мгновенно создает три столбца. На все ушло меньше минуты.

А если вам понадобится добавить новые данные, то возвращаться к таблице и использовать формулы не нужно. Все обновляется автоматически.

Объединяем данные из файлов

Еще одно значимое преимущество расширенного редактора Power Query в удобном импорте и преобразовании данных.

Например, вы готовите отчет по расходам. Все данные хранятся в обычных txt-файлах и собираются вручную в общую таблицу.

Но при таком способе отслеживать расходы неудобно, значит отчетность желательно преобразить:

• показать структуру расходов
• отразить динамику по месяцам
• сделать подробную таблицу по фондам расходов
• сократить ручную работу

Список немалый, но на помощь приходит Power Query! Решить проблемы с удобным инструментом можно всего за 3 этапа:

Первый этап

• подключаемся к папке
• объединяем текстовые файлы в один мастер-запрос

Второй этап

• распределяем даты, статьи расходов и суммы по столбцам
• добавляем столбцы с фондами расходов, названием месяца и годом

Третий этап

• загружаем чистые данные в формат сводной таблицы
• подготавливаем необходимые агрегации
• строим диаграммы
• добавляем срезы

Power Query легко объединяет данные из различных источников и позволяет сформировать автоматизированный отчет!

С надстройкой вам достаточно только добавить в папку с исходными данными новую выгрузку и нажать на ленте кнопку «Обновить». И тут же сводные таблицы пополняются актуальными сведениями, а диаграммы перестраиваются!

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

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

Приводите таблицы и диаграммы в порядок с помощью Power Query и управляйте данными по вашему запросу!

Начало работы в Power Query

Power Query — это инструмент для продвинутого бизнес-анализа, предназначенный для подключения к источникам данных и их преобразования.

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

Power Query в меню Excel

В зависимости от того, какая у вас версия Excel, вид надстройки Power Query может выглядеть по-разному. В Excel 2010 и 2013 надстройка появляется в виде отдельной вкладки «Power Query» (если у вас такой вкладки нет, прочитайте, как ее установить ).

В Excel после 2016 года Power Query уже встроен по умолчанию и находится в меню Данные → раздел Получить и преобразовать данные (в некоторых версиях Excel этот раздел называется Скачать & преобразовать).

Работа с данными в Power Query

Power Query умеет:

• напрямую подключаться к данным в различных источниках;
• очищать данные и выполнять преобразования;
• подготовленные данные выгружать на лист, в сводную таблицу или добавлять в модель данных Excel.

Таким образом, Power Query – это полноценный ETL-инструмент (Extract, Transform, Load).

Подключение к данным в Power Query

В Power Query можно подключать данные из самых разных источников: таблицы в самом файле и других Excel-файлах, текстовые/csv файлы, папки, базы данных, источники в интернете, файлы xml и json, pdf-файлы, данные из канала OData и так далее. А также загрузить данные из Power BI и написать запрос с нуля – Пустой запрос.

Чтобы посмотреть, какие именно источники данных доступны в Excel, перейдите на вкладку Данные → Получить данные (или Создать запрос, если у вас не новая версия Excel).

Для примера добавим в Power Query данные из таблицы.

• Выделите любую таблицу на листе Excel и перейдите в меню: — в Excel 2010 и 2013: вкладка Power Query → Из таблицы (или С листа).
  — для Excel после 2016: меню Данные → Из таблицы (Из таблицы/диапазона).

В открывшемся окне поставьте галочку «Таблица с заголовками».

Таблица с данными при этом превратится в «умную» smart-таблицу.

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

Чтобы открыть список запросов, нажмите в меню Данные → Запросы и подключения. В открывшейся вкладке «Запросы и подключения» отобразится список всех запросов, созданных в файле.

Редактор запросов Power Query

Разберем подробнее интерфейс редактора запросов Power Query.

Если окно редактора у вас закрыто, откройте его в меню Данные → Получить данные → Запустить редактор запросов. Или щелкните 2 раза мышкой по названию запроса на вкладке Запросы и подключения.

Итак, в редакторе Power Query есть:

1. Лента редактора запросов для вкладок меню: Главная, Преобразование, Добавить столбец, Просмотр.
2. Перечень созданных запросов, который можно свернуть / развернуть.
3. Строка формул.
4. Название самого запроса.
5. Примененные шаги запроса: записанные шаги получения или преобразования данных. Их можно редактировать, выбирая в списке, изменять последовательность шагов, добавлять новые или удалять.
6. Область предварительного просмотра, в которой выводится результат преобразования данных для каждого шага.
7. Меню для данных, которое открывается при нажатии правой кнопкой мышки.
8. При выборе правой кнопкой мыши названия шага появляется его контекстное меню.

Преобразование данных

Посмотрим на простом примере, как преобразовать данные в Power Query.

Допустим, у нас есть таблица с выручкой и расходами по городам за несколько лет. В таблице эти показатели разделены на две группы. Столбец с городами тоже имеет группировки (смотрите рисунок).

Если вы знакомы со сводными таблицами, то знаете, что построить сводную на основе таких данных не получится. Привести их в «нужный вид» можно в Power Query буквально за несколько щелчков мышкой:

• выделите таблицу (можно выделить таблицу целиком или одну из ячеек);
• выберите в меню Данные → Из таблицы (Из таблицы/диапазона);
• в появившемся окне поставьте галочку рядом с «Таблица с заголовками» → ОК;
• в открывшемся редакторе запросов выделите столбцы «показатель» и «город», нажав мышкой на названия столбцов с зажатым Ctrl;
• в меню нажмите Преобразование → Заполнить → Вниз.

• Готово! Пустые строки заполнены значениями из ячеек сверху.

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

Шаги запроса можно редактировать, выбирая мышкой (таблица в области предварительного просмотра при этом тоже изменится). Ненужные шаги удаляются при нажатии на «крестик». Можно добавлять новые шаги в середину запроса или менять их местами, перемещая мышкой.

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

Кроме простых операций с данными, Power Query умеет выполнять и другие действия: сортировать, фильтровать, заменять, группировать, заполнять пустые значения, удалять дубликаты, работать с текстом и числами, выполнять простые вычисления, транспонировать таблицы и разворачивать их столбцы, объединять данные и многое-многое другое.