Как добавить Power Query в Excel: пошаговое руководство

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

Пользователям Excel 2016 или более поздней версии не нужно добавлять Power Query в Excel. Он встроен в функцию «Получить и преобразовать данные» на вкладке «Данные». Пользователи Excel 2013 и 2010 должны загрузить и установить надстройку Microsoft Power Query.

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

Оглавление

• Как установить надстройку Power Query в Excel 2010 или 2013
• Excel Power Query для Excel 2016 и более поздних версий
• Как найти инструменты Power Query на вкладке «Данные»
  • 1. Получить данные
  • 2. Из текста/CSV
  • 3. Из Интернета
  • 4. Из таблицы/диапазона
  • 5. С картинки
  • 6. Последние источники
  • 7. Существующие соединения
• Как добавить коннекторы Power Query в Excel
• Как добавить разрешения Power Query в Excel
• Как получить доступ к редактору Power Query
  • Лента и вкладки
  • Панель предварительного просмотра данных
  • Расширенный редактор
• 2 способа настроить Power Query
  • 1. Как настроить параметры запроса
  • 2. Как настроить глобальные параметры
  • 3. Как настроить параметры безопасности и конфиденциальности
• Как добавить другие инструменты для улучшения Power Query
  • 1. Power Pivot
  • 2. Карта мощности (3D-карты)
  • 3. LuckyTemplates
• Наше последнее слово или два

Как установить надстройку Power Query в Excel 2010 или 2013

Обратите внимание, что пользователям Mac потребуется как минимум Excel 2016, поскольку Power Query недоступен для предыдущих версий Excel в Mac OS. Пользователи Windows могут использовать бесплатную надстройку Excel 2000.

Перед загрузкой надстройки вам необходимо узнать, установлена ​​ли у вас 32-разрядная или 64-разрядная версия Excel. Страница загрузки спросит, что у вас есть.

На рисунке ниже показан выбранный вариант 64-битной версии.

Существует небольшая разница в поиске этой информации между 2010 и 2013 годами. В обеих версиях разверните меню «Файл» и нажмите «Справка». 32-битная/64-битная информация находится либо в:

• «О Microsoft Excel» в Excel 2010

• «Об Excel» в Excel 2013

Процесс установки надстройки практически одинаков для обеих версий. Чтобы установить надстройку Power Query, выполните следующие действия.

1. Закройте Эксель.

2. Перейдите на официальную страницу загрузки Microsoft .

3. Нажмите кнопку «Загрузить».

4. Выберите правильную версию (32-разрядную или 64-разрядную) в зависимости от вашей установки Excel.

5. Запустите загруженный файл и следуйте инструкциям мастера установки.

Если во время установки Excel показывает сообщение об ошибке, убедитесь, что у вас установлены последние обновления для вашей версии.

Когда установка будет завершена, вы должны включить надстройку в Excel, чтобы увидеть вкладку Power Query.

1. Откройте Эксель.

2. Выберите Файл -> Параметры.

3. Нажмите «Надстройки» на левой панели.

4. Выберите «Надстройки COM» в раскрывающемся меню в нижней части окна.

5. Установите флажок рядом с «Microsoft Power Query для Excel» и нажмите «ОК».

Power Query теперь доступен на отдельной вкладке на ленте Excel!

Excel Power Query для Excel 2016 и более поздних версий

В Excel 2016 и более поздних версиях Power Query теперь является частью ленты Excel. Чтобы получить к нему доступ, выполните следующие действия:

1. Откройте Excel и создайте новый документ или загрузите существующий.

2. Перейдите на вкладку «Данные».

3. Используйте кнопки в группе «Получить и преобразовать данные», чтобы получить доступ к инструментам Power Query.

Как найти инструменты Power Query на вкладке «Данные»

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

Вот описание семи кнопок и функций.

1. Получить данные

Эта функция позволяет вам подключаться и импортировать данные из внешних источников данных. Некоторые из наиболее часто используемых вариантов:

• Из файла: импорт из книг Excel, файлов CSV, текстовых, XML или JSON.

• Из базы данных: импорт из SQL Server, Access, Oracle и др.

• Из Azure: импорт из базы данных SQL Azure, хранилища BLOB-объектов Azure и других источников.

2. Из текста/CSV

Кнопка «Из текста/CSV» позволяет подключаться и импортировать данные из текстового файла или файла CSV (значения, разделенные запятыми). Откроется диалоговое окно «Импорт данных», в котором можно выбрать файл.

Вы можете получить доступ к тому же файлу TXT или CSV, используя раскрывающийся список «Получить данные». Кнопка «Из текста/CSV» обеспечивает быстрый доступ.

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

3. Из Интернета

Это еще один ярлык для функции, доступной в раскрывающемся списке «Получить данные». Кнопка «Из Интернета» позволяет подключаться и импортировать данные с веб-страниц, используя их URL-адреса.

4. Из таблицы/диапазона

Эта кнопка позволяет преобразовать выбранный диапазон ячеек или существующую таблицу в книге Excel в запрос Power Query.

Когда вы выбираете диапазон и нажимаете эту кнопку, автоматически открывается редактор Power Query. Этот редактор рассматривается в следующем разделе.

5. С картинки

Хотя функция «Из изображения» сгруппирована в разделе Power Query, на самом деле она не является частью Power Query.

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

Он использует технологию оптического распознавания символов (OCR) для распознавания и извлечения текста и чисел из изображения.

6. Последние источники

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

Когда вы нажимаете кнопку «Недавние источники», в раскрывающемся меню отображается список ваших недавно использованных источников данных, таких как онлайн-подключения и файлы Excel.

7. Существующие соединения

Это еще одна экономия времени. Он дает вам список всех подключений к данным, которые были ранее установлены в вашей текущей книге.

Как добавить коннекторы Power Query в Excel

Некоторые расширенные функции или соединители Power Query могут быть доступны только в определенных выпусках Excel, таких как Excel 2016 Professional Plus или Excel для Microsoft Office 365.

Например, соединитель SharePoint, позволяющий подключаться к папкам SharePoint, доступен только в корпоративных подписках.

Если у вас домашняя или бизнес-подписка, вы не увидите эту опцию в списке соединителей.

Приведенный ниже отрывок из списка доступности Microsoft показывает, что подключения к SharePoint и PDF доступны не во всех выпусках.

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

Как добавить разрешения Power Query в Excel

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

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

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

Как получить доступ к редактору Power Query

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

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

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

Кроме того, вы можете открыть редактор как пустой запрос и настроить подключение из него. Вот как это сделать в группе Get & Transform Data:

1. Перейдите на вкладку Данные.

2. Нажмите «Получить данные» в группе «Получить и преобразовать данные».

3. Выберите «Запустить редактор Power Query».

4. Используйте параметры справа на ленте для доступа к своим данным.

Группа параметров «Новый запрос» находится в правой части ленты. Отсюда вы можете импортировать файл, подключиться к базе данных и даже ввести данные вручную.

Лента и вкладки

Лента в редакторе Power Query содержит несколько вкладок с параметрами, организованными в группы для удобства доступа.

Эти вкладки служат разным целям, предоставляя инструменты и функции, которые вам понадобятся при работе с Power Query. Четыре вкладки:

• Дом

• Трансформировать

• Добавить столбец

• Вид

На этом рисунке показаны вкладки в верхней части окна. Предварительный просмотр данных под лентой показывает содержимое таблицы Excel. Панель «Запросы» находится слева.

Панель предварительного просмотра данных

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

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

Расширенный редактор

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

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

Чтобы получить доступ к расширенному редактору:

1. Перейдите на вкладку «Главная» в редакторе Power Query.

2. Нажмите «Расширенный редактор» в группе «Запрос».

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

• с помощью функции Coalesce в Power Query

• проекции в Power Query/M Language

• используя Power Query каждое выражение

Вот видео пошаговое руководство по использованию каждого выражения.

2 способа настроить Power Query

Вы можете настроить работу с Power Query следующим образом:

• настройка параметров запроса

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

1. Как настроить параметры запроса

Чтобы получить доступ к настройкам запроса:

1. Откройте редактор Power Query.

2. Просмотрите настройки на правой боковой панели.

3. Щелкните ссылку «Все свойства».

4. При необходимости изменяет сведения во всплывающем окне.

Вы можете изменить несколько аспектов вашего запроса Microsoft, например:

• Имя: переименуйте свой запрос во что-то более описательное.

• Описание: добавьте описание, чтобы предоставить больше контекста.

• Загрузить настройки: выберите, следует ли загружать данные в модель данных или только как соединение.

На картинке ниже я меняю название «стол» на более информативное «продажи» и добавляю описание.

2. Как настроить глобальные параметры

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

1. В Excel перейдите на вкладку «Данные».

2. Щелкните раскрывающееся меню «Получить данные».

3. Выберите Параметры запроса внизу.

Откроется диалоговое окно Параметры запроса. Здесь вы можете изменить макет и поведение редактора Power Query, установив или сняв флажок.

3. Как настроить параметры безопасности и конфиденциальности

Параметры запроса включают раздел безопасности и конфиденциальности.

Раздел безопасности позволяет вам управлять проверками сертификатов и веб-предупреждениями, если ваши запросы обращаются к внешнему онлайн-источнику данных.

В разделе «Конфиденциальность» вы можете настроить, как Power Query обрабатывает и защищает конфиденциальные данные.

Как добавить другие инструменты для улучшения Power Query

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

1. Power Pivot

Power Pivot — это мощный инструмент моделирования и анализа данных, встроенный в новые версии Excel, которые хорошо работают с Power Query.

Это позволяет вам:

• создавать сложные модели данных.

• управлять отношениями между таблицами.

• выполнять расширенные вычисления с использованием выражений анализа данных (DAX).

Если вы используете Excel для Microsoft 365, Excel 2016 или Excel 2019, выполните следующие действия, чтобы включить Power Pivot для сводных таблиц.

1. Перейдите на вкладку «Файл».

2. Выберите «Параметры», чтобы открыть диалоговое окно «Параметры Excel».

3. Нажмите на категорию «Надстройки» на левой панели.

4. Выберите «Надстройки COM» в раскрывающемся меню внизу и нажмите «Перейти».

5. Найдите в списке «Microsoft Power Pivot for Excel» и установите напротив него флажок.

6. Нажмите «ОК», чтобы включить Power Pivot.

После включения Power Pivot на ленте Excel появится новая вкладка Power Pivot. Ваш следующий шаг — научиться работать со сводной таблицей с помощью окна Power Pivot.

На этом рисунке показаны как Power Pivot, так и Power Map в окне надстроек:

2. Карта мощности (3D-карты)

Power Map, также известная как 3D Maps, представляет собой инструмент визуализации данных для Excel, который позволяет создавать интерактивные трехмерные геопространственные визуализации с использованием ваших данных. Это отличный способ визуализации географических данных из Power Query.

Если вы используете Excel для Microsoft 365, Excel 2016 или Excel 2019, выполните следующие действия, чтобы включить Power Map:

1. Перейдите на вкладку «Вставка».

2. Нажмите на кнопку «3D-карта» в группе «Туры».

3. Если вас попросят включить надстройки для анализа данных, нажмите «Включить».

Откроется окно 3D Карты, где вы можете:

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

• создавать интерактивные туры

• настроить внешний вид ваших карт.

3. LuckyTemplates

LuckyTemplates — это набор инструментов бизнес-аналитики , которые помогут вам анализировать и визуализировать данные.

Это не надстройка для Excel. Вместо этого это отдельное автономное приложение под названием LuckyTemplates Desktop. Его можно установить в Windows 10 или более поздней версии, для него требуется 64-разрядная ОС.

Пользователи Excel Mac могут использовать службу LuckyTemplates, которая представляет собой облачную версию LuckyTemplates, работающую в веб-браузере.

Наше последнее слово или два

Теперь вы узнали, как добавить или получить доступ к инструменту Power Query на листе Excel. Этот инструмент позволяет проводить сложный бизнес-анализ несколькими щелчками мыши.

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

Вскоре вы будете на пути к использованию возможностей Power Query.