power query excel обучение для начинающих
Начало работы в Power Query
Power Query — это инструмент для продвинутого бизнес-анализа, предназначенный для подключения к источникам данных и их преобразования.
Power Query в меню Excel
В зависимости от того, какая у вас версия Excel, вид надстройки Power Query может выглядеть по-разному. В Excel 2010 и 2013 надстройка появляется в виде отдельной вкладки «Power Query» (если у вас такой вкладки нет, прочитайте, как ее установить ).
В Excel после 2016 года Power Query уже встроен по умолчанию и находится в меню Данные → раздел Получить и преобразовать данные (в некоторых версиях Excel этот раздел называется Скачать & преобразовать).
Работа с данными в Power Query
Таким образом, Power Query – это полноценный ETL-инструмент (Extract, Transform, Load).
Подключение к данным в Power Query
В Power Query можно подключать данные из самых разных источников: таблицы в самом файле и других Excel-файлах, текстовые/csv файлы, папки, базы данных, источники в интернете, файлы xml и json, pdf-файлы, данные из канала OData и так далее. А также загрузить данные из Power BI и написать запрос с нуля – Пустой запрос.
Чтобы посмотреть, какие именно источники данных доступны в Excel, перейдите на вкладку Данные → Получить данные (или Создать запрос, если у вас не новая версия Excel).
Для примера добавим в Power Query данные из таблицы.
— в Excel 2010 и 2013: вкладка Power Query → Из таблицы (или С листа).
— для Excel после 2016: меню Данные → Из таблицы (Из таблицы/диапазона).
В открывшемся окне поставьте галочку «Таблица с заголовками».
Таблица с данными при этом превратится в «умную» smart-таблицу.
Чтобы открыть список запросов, нажмите в меню Данные → Запросы и подключения. В открывшейся вкладке «Запросы и подключения» отобразится список всех запросов, созданных в файле.
Редактор запросов Power Query
Разберем подробнее интерфейс редактора запросов Power Query.
Если окно редактора у вас закрыто, откройте его в меню Данные → Получить данные → Запустить редактор запросов. Или щелкните 2 раза мышкой по названию запроса на вкладке Запросы и подключения.
Итак, в редакторе Power Query есть:
Преобразование данных
Посмотрим на простом примере, как преобразовать данные в Power Query.
Допустим, у нас есть таблица с выручкой и расходами по городам за несколько лет. В таблице эти показатели разделены на две группы. Столбец с городами тоже имеет группировки (смотрите рисунок).
Если вы знакомы со сводными таблицами, то знаете, что построить сводную на основе таких данных не получится. Привести их в «нужный вид» можно в Power Query буквально за несколько щелчков мышкой:
При создании запроса Power Query сам автоматически записывает его шаги. Их можно увидеть в области справа Параметры запроса → Примененные шаги.
Шаги запроса можно редактировать, выбирая мышкой (таблица в области предварительного просмотра при этом тоже изменится). Ненужные шаги удаляются при нажатии на «крестик». Можно добавлять новые шаги в середину запроса или менять их местами, перемещая мышкой.
Обработка данных в Power Query выполняется последовательно, шаг за шагом, и каждое последующее действие использует результаты предыдущего. Поэтому при добавлении новых шагов или изменении их последовательности обязательно проверьте, все ли в порядке со следующими операциями. Проверить, все ли в порядке, можно, нажав на самый нижний шаг.
Кроме простых операций с данными, Power Query умеет выполнять и другие действия: сортировать, фильтровать, заменять, группировать, заполнять пустые значения, удалять дубликаты, работать с текстом и числами, выполнять простые вычисления, транспонировать таблицы и разворачивать их столбцы, объединять данные и многое-многое другое.
Полный курс Power Query
На этой странице находятся ссылки на все уроки моего бесплатного курса по Power Query для Excel. Если вы изучите все уроки, то станете очень продвинутым пользователем Power Query. Желаю вам успехов в освоении Power Query!
Внимание. Старица находится в процессе разработки.
Введение
Во вводном модуле вы узнаете:
Основы
В модуле Основы мы разберем основные операции, которые специалистам по Excel приходится выполнять чаще всего.
| Урок | Описание |
|---|---|
| Подключение Excel | Подключаемся к файлам Excel. Импортируем данные из таблиц, именных диапазонов, динамических именных диапазонов. |
| Подключение CSV/TXT, таблиц, диапазонов | Подключаемся к к файлам CSV/TXT, Excel |
| Подключение XML | Подключаемся к источнику в формате XML |
| Объединение таблиц по горизонтали | Учимся объединять таблицы по горизонтали — JOIN, merge |
| Виды объединения таблиц по горизонтали | Изучаем виды объединения таблиц по горизонтали — LEFT JOIN, FULL JOIN, INNER JOIN, CROSS JOIN |
| Виды объединения таблиц по горизонтали 2 | Изучаем анти-соединение и соединение таблицы с ней же самой — ANTI JOIN, SELF JOIN |
| Группировка | Изучаем операцию группировки с агрегированием — GROUP BY |
| Объединить таблицы с агрегированием | Объединить таблицы по горизонтали и сразу выполнить группировку с агрегированием — JOIN + GROUP BY |
| Объединить таблицы по вертикали | Учимся объединять две таблицы по вертикали — combine |
| Объединение таблиц по вертикали, когда не совпадают заголовки столбцов | Как объединить две таблицы по вертикали, если названия столбцов не совпадают |
| Объединить по вертикали все таблицы одной книги друг за другом | Как объединить по вертикали все таблицы одной книги, находящиеся на разных листах Excel |
| Объединить по вертикали все файлы в папке | Объединяем по вертикали таблицы, которые находятся в разных файлах в одной папке |
| Консолидация множества таблиц пользовательской функцией | Объединяем по вертикали множество таблиц с предварительной обработкой при помощи пользовательской функции |
| Анпивот (Unpivot) | Изучаем операцию Анпивот — из сводной таблицы делаем таблицу с данными |
| Многоуровневый анпивот (Анпивот с подкатегориями) | Более сложный вариант Анпивота — в строках находится несколько измерений |
| Скученные данные | Данные собраны в одном столбце, нужно правильно его разбить на несколько |
| Скученные данные 2 | Разбираем еще один пример скученных данных |
| Создание параметра | Мы можем ввести значение в какую-то ячейку Excel, а потом передать это значение в формулу Power Query |
| Таблица параметров | Создадим целую таблицу параметров и будем их использовать в запросах Power Query |
| Поиск ключевых слов | Научимся искать ключевые слова в текстовом поле |
| Поиск ключевых слов 2 | Будем искать ключевые поля в текстовом поле и присваивать этому значению какую-то категорию |
| Ссылка на другую строку | Как сослаться на другую строку |
| Ссылка на другую строку 2 | Как сослаться на другую строку, используя объединение по горизонтали |
| Уникальные значения двух столбцов | Как получить уникальные значения из двух столбцов |
| Деление на справочник и факт | Разделим один датасет на два датасета: справочник и факт |
| Импорт из PDF, Импорт из множества PDF | Научимся импортировать таблицы из одного PDF файла и из всех PDF файлов в папке |
Формулы М
В этом модуле мы будем изучать язык формул Power Query или M Language.
| Урок | Описание |
|---|---|
| Введение 1 | На примере поиска без учета регистра я показываю преимущества изучения формул Power Query |
| Введение 2 | Знакомимся с основами языка формул М: ключевые слова let, in, разбираем как устроены функции |
| Введение 3 | Зачем изучать язык формул, когда существует удобный пользовательский интерфейс |
| Объекты Power Query | Знакомимся с основными объектами Power Query: Table, List, Record |
| Объект Table | Изучаем объект Table — таблица |
| Объект Record | Изучаем объект Record — запись, каждая строка таблицы — это запись |
| Объект List 1 | Изучаем объект List — список, каждый столбец таблицы является списком |
| Объект List 2 | Продолжаем изучать объект List — список |
| Ссылки 1 | Как ссылаться на список и запись и отдельные элементы списка или записи |
| Ссылки 2 | Решаем практическую задачу по объединению таблиц и отрабатываем ссылки на строки и столбцы таблицы |
| Ссылки 3 | В процессе решения практической задачи отрабатываем ссылки на элементы записи и таблицы |
| Ссылки 4 | Закрепляем навык делать ссылки на практическом примере из жизни |
| Работа с датой, временем и длительностью, #datetime, #duration | Основы работы с датой и временем |
| Power Query аналоги текстовых функций Excel | Изучаем аналоги текстовых Excel функций ЛЕВСИМВ, ПРАВСИМВ, ДЛСТР, НАЙТИ, ПСТР |
| Группировка, Пивот, Анпивот, Сортировка | Выполняем знакомые операции, но уже без помощи пользовательского интерфейса, а ручным вводом формул |
| Вычисления в Power Query | Вычисляем процент от общей суммы, процентное изменение относительно предыдущего дня, создаем рейтинг |
| Пользовательские функции | Учимся создавать пользовательские функции на примере нарастающего итога |
| Агрегирование текста, группировка | Выполняем группировку с агрегированием текстовых значений |
| Функция Table.TransformColumns | Table.TransformColumns позволяет нам применить любую функцию преобразования к столбцу |
| Скользящее среднее в Power Query, List.Range | Изучаем функцию List.Range на примере вычисления скользящего среднего |
| Разгруппировка, скрытые возможности Table.ReplaceValues | Разгруппировка строки — разбить число на N равных частей; Вводим функцию внутри Table.ReplaceValues |
| Нюансы консолидации и List.PositionOf | Нужно выполнить объединение по вертикали с предварительной обработкой, но количество лишних строк сверху всегда разное |
| Удалить лишние пробелы в текстовом столбце, Text.Split | Изучим функцию Text.Split и удалим с помощью нее лишние пробелы |
| Обработка заголовков в двух строках, Функция List.Zip, Практика List.x, Record.x | Каждый заголовок разбит на две строки, нужно превратить эти две строки в одну объединенную |
| Фильтрация таблицы списком | Есть список интересующих нас категорий, нужно из таблицы выбрать только эти значения |
| Разделить столбец на строки, Расширенные возможности Text.Trim | Разберем на практическом примере, что еще может чистить функция Text.Trim помимо лишних пробелов по краям |
| Нарастающий итог 2 | Вычислим нарастающий итог функциями List.Range и List.Accumulate |
| Создание пользовательской функции Switch | Повторим пользовательские функции на примере создания функции Switch |
| Информация о форматах ячеек, Чтение Zip файла | Распакуем XLSX файл и получим данные о формате ячеек |
| Множественная текстовая замена с List.Generate | Ищем определенный перечень текстовых фрагментов и заменяем на другой. |
| Минимум в диапазоне строк |
Ошибки
В модуле вы научитесь избегать ошибок и составлять универсальные запросы.
| Урок | Описание |
|---|---|
| Введение | Определимся, что мы будем назвать ошибками и ловушками |
| Как развернуть все столбцы | В данные добавился новый столбец, но у вас он не разворачивается, потому что вы хардкодом прописали список для экстракта |
| Измененный тип, Неверная фильтрация в UI | Запрос, который раньше работал, вдруг, работать перестал. Возможно, дело в ошибке «Измененный тип»; В результирующей таблице меньше строк, чем должно быть. Возможно, вы попали в ловушку неверной фильтрации |
| Неверное количество столбцов при импорте CSV | Вы импортировали CSV файл, все было хорошо, но когда в источник добавились новые столбцы вы их не увидели в Power Query |
| Подключиться к последнему файлу | Каждую неделю в сетевую папку для вас добавляют новый файл источник. Вам нужно автоматически подключаться только к самому свежему файлу |
| Консолидация и MissingField.Type | Вы хотите выполнить массовое объединение таблиц по вертикали, но не во всех таблицах присутствует полный список столбцов. Что делать? |
| Автоматически удалить пустые столбцы | В вашем источнике часто присутствуют лишние пустые столбцы; Научимся автоматически удалять их |
| Ошибка Formula.Firewall | Разберем 2 способа избежать ошибки Formula.Firewall |
| Разные имена листов | |
| Разные имена листов 2 | |
| Разные названия столбцов | |
| Удалить лишние пробелы 2 | Удалим лишние пробелы в текстовых столбцах с помощью функцию Text.SplitAny и Text.Combine |
| Доступ к веб-содержимому | При подключении к веб-страницам постоянно появляется окно «Доступ к веб-содержимому»; Как от этого окна избавиться? |
| Импорт плохо структурированного TXT | TXT источник плохо структурирован и столбцы не распознаются автоматически |
| Не совпадает сумма при округлении | Выполнив округление общая сумма перестала совпадать на незначительную величину |
| Фиксировать ширину столбцов Excel | Настроенная вами ширина столбцов Excel сбивается после каждого обновления запроса. |
Веб-запросы
Разберем множество практических примеров подключения к веб-страницам и web api.
| Урок | Описание |
|---|---|
| Веб-запросы. Текстовый документ, веб-страница, практика List.Zip | На одной веб-странице находятся данные без заголовков, а заголовки для нее на другой странице. |
| Веб-запросы. Неразмеченный текст | Текс находится на веб-странице и он плохо размечен. Нужно преобразовать его в таблицу. |
| Веб-запросы. Многостраничное извлечение | Как извлечь таблицу из множества веб-страниц и объединить их всех по вертикали. |
| Веб-запросы. Получение котировок Yahoo Finance | Получим историю котировок любой акции с сайта Yahoo Finance. |
| Веб-запросы. JSON, получение котировок Yahoo Finance 2 | Получим историю котировок любой акции при помощи API Yahoo Finance. |
| Веб-запросы. HTML, получить данные по облигациям | Получим данные по облигациям из множества веб-страниц и все объединим в одну таблицу. |
| Получить котировки Московской биржи | Получим историю котировок с сайта Московской биржи. |
| YouTube Data API | Получить статистику по любому YouTube видео и каналу прямо на лист Excel. |
| Подключение к личному OneDrive | Как подключиться к файлу или папке из личного OneDrive. |
| Подключиться к книге на Google Drive | Как подключиться к файлу, который лежит в облаке Google Drive |
Практика
Отработаем изученные функции на реальных практических примерах из жизни.
| Урок | Описание |
|---|---|
| Динамическая таблица дат | Создаем саморасширяющуюся таблицу календаря |
| Продажи год назад | Отработаем SELF JOIN на примере вычисления продаж прошлого года |
| Количество позиций в текстовой строке | В ячейках столбца находится перечень товаров в чеке через точку с запятой; Сделаем так, чтобы каждая позиция находилась в своей отдельной строке |
| Консолидация листов и книг одновременно | Объединим по вертикали все таблицы, находящиеся на разных листах разных книг Excel |
| Собрать разбитую строку | Каждая строка данных разбита на несколько строк. Нужно привести данные в порядок. |
| Обработка типичной выписки | Отработаем несколько приемов, обработав банковскую выписку. |
| Прирост населения Китая | На примере анализа прироста населения Китая отработаем несколько приемов. |
| Нужные столбцы нужной книги | Как выбрать нужную книгу среди множества и из этой книги получить только определенные столбцы. |
| Объединить разбитую строку | Ряды данных разбиты на части. Нужно правильно соединить ряды обратно. |
| Группировка, пивот, анпивот, условия | Практическое применение группировки, пивота, анпивота и условной логики |
| Процент от суммы по категории в Power Query и в Power Pivot | Учимся вычислять процент от общей суммы в Power Query и в Power Pivot. |
| Выполнить пивот и пронумеровать столбцы | Делаем пивот и столбцы автоматически пронумеровываем. |
| Интересная консолидация | |
| Преобразовать строки в столбцы | |
| Повторяющиеся значения в строке | |
| Нарастающий итог 3 | |
| Минимальное значение в диапазоне строк | |
| Нарастающий итог 4. Группировки | |
| Функция List.Contains и создание пользовательской функции | |
| Скученные данные 3 |
Power Query + VBA
| Урок | Описание |
|---|---|
| Указать путь к файлу при помощи VBA | Укажем путь к файлу, выбрав его в диалоговом окне. |
| Массовая обработка файлов | По одному обработаем файлы из перечня. |
| Обновить запросы выборочно | С помощью VBA обновить только определенные Power Query запросы в книге Excel. |
Трюки и советы
В этом модуле мы изучим множество полезных трюков, которые помогут вам сэкономить время и силы при работе в Excel.
Power Query. Введение. Знакомство с Power Query
Описание
В этом уроке вы узнаете:
В этом вводном уроке курса Excel Power Query на 1-2-3 мы узнаем, что такое Power Query, зачем он нужен, как его установить и запустить.
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. Выполняем следующие действия:
Перед вам появится следующее окно редактора запросов Power Query:
Сверху мы видим меню в привычном ленточном интерфейсе как и во всех продуктах Microsoft Office. Слева список запросов, справа примененные шаги к запросу. Теперь продолжим работать с нашим запросом.
Удалим шаг Изменить тип. Для этого в списке шагов справа нажмем на крестик слева от названия шага. Укажем тип данных для каждого столбца.
По умолчанию Power Query определил тип данных каждого столбца как текстовый. Об этом говорит пиктограмма с символами ABC слева от названий столбцов.
Попробуйте нажать на пиктограмму ABC столбца Last Price и указать тип данных десятичное число. В столбце во всех строках отобразятся ошибки. Это произошло из-за того, что в нашем регионе целая и дробная части разделяются запятыми, а в таблице с этого сайта точкой. В таком случае нужно указать тип с использованием локали.
Нажмите правой кнопкой мыши на название столбца Last Price — Тип изменения — Используя локаль. Укажите тип данных целое число и языковой стандарт Английский США. Теперь все получилось. То же самое проделайте для других числовых столбцов.
Теперь перейдите на вкладку Главная, щелкните на нижнюю часть кнопки Закрыть и загрузить, в списке выберете Закрыть и загрузить в. Отметьте пункт Таблица и укажите место, куда эту таблицу поместить, потом нажмите ОК. Наш первый запрос готов!