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

Сведения о Power Query в Excel

С помощью Power Query (известного как Получение преобразования & в Excel) можно импортировать или подключиться к внешним данным, а затем сформировать эти данные, например удалить столбец, изменить тип данных или объединить таблицы в соответствии с вашими потребностями. Затем можно загрузить запрос в Excel для создания диаграмм и отчетов. Периодически можно обновлять данные, чтобы сделать их актуальными. Power Query доступна в трех приложениях Excel: Excel для Windows, Excel для Mac и Excel для Интернета. Сводку по всем разделам справки Power Query см. в разделе справки Power Query для Excel.

Примечание: Power Query в Excel для Windows использует платформу .NET Framework, но требуется версия 4.7.2 или более поздняя. Последние платформа .NET Framework можно скачать здесь. Выберите рекомендуемую версию и скачайте среду выполнения.

Четыре этапа Power Query

Существует четыре этапа использования Power Query.

Power Query common steps

Подключения Подключение к данным в облаке, в службе или локально
Преобразования Формирование данных в соответствии с вашими потребностями, в то время как исходный источник остается неизменным
Объединить Интеграция данных из нескольких источников для получения уникального представления в данные
Нагрузки Завершите запрос, загрузите его на лист или в модель данных и периодически обновляйте его.

В следующих разделах подробно рассматриваются все этапы.

Подключение

Вы можете использовать Power Query для импорта в один источник данных, например в книгу Excel, или в несколько баз данных, веб-каналов или служб, разбросанных по всему облаку. Источники данных включают данные из Интернета, файлов, баз данных, Azure или даже таблиц Excel в текущей книге. С помощью Power Query вы сможете объединить все эти источники данных, используя собственные уникальные преобразования и сочетания, чтобы получить аналитические сведения, которые вы не видели бы в противном случае.

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

Команды импорта в пользовательском интерфейсе

Преобразовать

Преобразование данных означает, что они каким-то образом изменяются в соответствии с требованиями к анализу данных. Например, можно удалить столбец, изменить тип данных или отфильтровать строки. Каждая из этих операций является преобразованием данных. Этот процесс применения преобразований (и объединения) к одному или нескольким наборам данных также называется формированием данных.

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

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

Пользовательский интерфейс параметров запроса

Редактор Power Query отслеживает все, что вы делаете с данными, записывая и помечая каждое преобразование или шаг, применяемые к данным. Независимо от того, является ли преобразование соединением данных, удалением столбца, слиянием или изменением типа данных, каждое преобразование можно просмотреть и изменить в разделе ПРИМЕНЕННЫЕ ШАГИ области Параметры запроса .

Существует множество преобразований, которые можно выполнить в пользовательском интерфейсе. Каждое преобразование записывается как шаг в фоновом режиме. Вы даже можете изменить и написать собственные шаги с помощью языка Power Query M в Расширенный редактор.

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

Объединить

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

Добавить Операция добавления создает новый запрос, содержащий все строки из первого запроса, за которым следуют все строки из второго запроса. Вы можете выполнять два типа операций добавления:

Промежуточное добавление Создает новый запрос для каждой операции добавления.
Встроенное добавление Добавляет данные в существующий запрос до достижения конечного результата.

Концепция добавления запросов

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

Концепция запросов слиянием

Существует два основных способа загрузки запросов в книгу:

В Редактор Power Query можно использовать команды Закрыть и Загрузить в группе Закрыть на вкладке Главная.
В области Запросы книги Excel (Выберите запросы & подключения) можно щелкнуть запрос правой кнопкой мыши и выбрать команду Загрузить.

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

Поддержка приложений для Power Query в Excel

Уже более десяти лет Power Query поддерживается в Excel для Windows. Теперь Excel расширяет поддержку Power Query в Excel для Mac и добавляет поддержку Excel для Интернета. Это означает, что мы делаем Power Query доступными на трех основных платформах и демонстрируем популярность и функциональность Power Query среди клиентов Excel. Следите за будущими объявлениями о стратегии развития Microsoft 365и новых возможностях в Excel для Microsoft 365.

Журнал Power Query в Excel

Интеграция get & transform Data (теперь называется Power Query) в Excel за прошедшие годы пережила ряд изменений.

Excel 2010 и 2013 для Windows

В Excel 2010 для Windows мы впервые представили Power Query, и она была доступна в виде бесплатной надстройки, которую можно скачать здесь: Скачать надстройку Power Query. После включения функциональность Power Query была доступна на вкладке Power Query на ленте.

Лента Power Query Excel 2013

Microsoft 365

Мы обновили Power Query, чтобы стать основным интерфейсом в Excel для импорта и очистки данных. Вы можете получить доступ к мастерам и средствам импорта данных Power Query из группы Получение & преобразование данных на вкладке Данные ленты Excel.

Лента Excel 2016 Power Query

Эта функция включает расширенные функции импорта данных, перестановку команд на вкладке Данные , новую панель «Запросы & подключение «, а также возможность эффективной обработки данных путем сортировки, изменения типов данных, разделения столбцов, агрегирования данных и т. д.

Этот новый интерфейс также заменил старые устаревшие мастеры импорта данных в команде Data в группе Получение внешних данных . Тем не менее, доступ к им по-прежнему можно получить из диалогового окна Параметры Excel(Выберите параметры > файла> данные > Показать устаревшие мастеры импорта данных).

Excel 2016 и 2019 для Windows

Мы добавили тот же интерфейс Get & Transform Data на основе технологии Power Query, что и Microsoft 365.

Excel для Microsoft 365 для Mac

В 2019 году мы начали поддержку Power Query в Excel для Mac. С тех пор мы добавили возможность обновлять запросы Power Query из файлов TXT, CSV, XLSX, JSON и XML. Мы также добавили возможность обновлять данные из SQL Server и таблиц, & диапазонов в текущей книге.

В октябре 2019 г. мы добавили возможность обновлять существующие запросы Power Query и использовать VBA для создания и редактирования новых запросов.

В январе 2021 г. добавлена поддержка обновления Power Query запросов из источников OData и SharePoint.

Примечание Поддержка Power Query в Excel 2016 и Excel 2019 для Mac отсутствует.

Каталог данных устаревание

С помощью Каталог данных можно просмотреть общие запросы, а затем выбрать их для загрузки, редактирования или использования в текущей книге. Эта функция постепенно стала устаревшей:

1 августа 2018 года мы прекратили подключение новых клиентов к Каталог данных.
3 декабря 2018 г. пользователи не смогли поделиться новыми или обновленными запросами в Каталог данных.
4 марта 2019 года Каталог данных перестал работать. После этой даты мы рекомендуем скачать общие запросы, чтобы вы могли продолжать использовать их за пределами Каталог данных с помощью параметра Открыть в области задач Мои запросы Каталог данных.

Power Query устаревание надстройки

В начале лета 2019 г. мы официально отреклись от Power Query надстройки, которая требуется для Excel 2010 и 2013 для Windows. Как любезно, вы по-прежнему можете использовать надстройку, но это может измениться позже.

Прекращение использования соединителя данных Facebook

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

Критические обновления Excel для Windows

С июня 2023 г. для Power Query в Excel для Windows требуются следующие компоненты:

Power Query в Excel для Windows использует платформу .NET Framework, но требуется версия 4.7.2 или более поздняя. Дополнительные сведения см. в разделе Обновление платформа .NET Framework.
Power Query в Excel для Windows требуется, чтобы среда выполнения WebView2 продолжала поддерживать веб-соединитель данных (получение данных из Интернета). Дополнительные сведения см. в разделе Скачивание среды выполнения WebView2.

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

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

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

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

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

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

Лента Power Pivot

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

Кнопка

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

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

Окно Power Pivot

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

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

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

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

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

Настройка Power Query

В данной статье описан порядок подключения надстройки Power Query и необходимые действия по ее настройке.

Разбор возможных ошибок при обновлении запросов и порядок их устранения см. в разделе «Возможные ошибки» статей к соответствующим кнопкам:

1. На компьютере пользователя должна быть установлена надстройка для Microsoft Excel – Power Query версии не ниже 2.49 (доступна в качестве отдельной надстройки в Microsoft Excel 2013, скачать которую можно на сайте Microsoft; в версиях 2016 и 2019 присутствует в качестве встроенной функции на вкладке «Данные»).

2. Для корректного обновления запросов установите соответствующие требования к уровням конфиденциальности Power Query в файле.

a. Для Microsoft Excel 2013

Перейдите на ленте на вкладку Power Query -> Настройки -> Параметры:

pq1

b. Для Microsoft Excel 2016/2019

Перейдите на вкладку Данные -> откройте выпадающий список под кнопкой Получить данные -> Параметры запроса:

pq2 (2016)

c. В появившемся окне в разделе Глобальные откройте пункт меню Конфиденциальность и выберите опцию « Всегда игнорировать параметры уровней конфиденциальности »:

clip0097

3. Проверьте, разрешены ли подключения к внешним данным.

Чтобы разрешить подключения к внешним данным, перейдите на вкладку Файл -> Параметры -> Центр управления безопасностью -> Параметры центра управления безопасностью , а затем в группе Параметры безопасности для подключения к данным выберите опцию « включить все подключения к данным », в группе Параметры безопасности для связей в книге — « включить автоматическое обновление для всех связей в книге ».