Power Query: продвинутая обработка одной таблицы. От А до Я

Привет, дорогой читатель! 👋

В прошлый раз мы познакомились с Power Query и научились удалять лишние строки и заменять «н/д». Сегодня пойдём дальше. Я приготовил реальную «грязную» таблицу — такую, какие часто приходят из учётных систем. Мы пройдём по ней все основные операции: разделим столбцы, исправим регистр, добавим логику, свернём итоги и даже превратим «широкую» таблицу в «длинную» одним движением.

Держитесь за клавиатуру — будет жарко! 🚀

Предположим, что нам пришла такая таблица.

Что здесь не так?

• ФИО написаны в разном регистре (ИВАНОВ, Петрова, иВАНОВ, Фёдорова…)
• В столбце Город_адрес смешаны город, улица и номер дома — нужно разделить.
• Таблица «широкая»: месяцы в отдельных столбцах, а для анализа удобнее «длинный» формат.
• Есть дубликаты (один и тот же Иванов с разным регистром).

Исправим всё это в Power Query. Поехали! 🧙‍♂️

Шаг 1. Загружаем таблицу в Power Query

1. Выделите любую ячейку внутри таблицы.
2. Вкладка «Данные» → «Из таблицы/диапазона».
3. Откроется редактор Power Query. Наша таблица появится в виде предпросмотра.

Если Excel спросит «Таблица с заголовками?» — да, с заголовками.

Внешний вид окна Power Query

Шаг 2. Приводим ФИО к нормальному регистру

В столбце ФИО_менеджера у нас хаос: ИВАНОВ, Петрова, иВАНОВ, Фёдорова. Сделаем так, чтобы фамилия и инициалы были с заглавной буквы, а остальные — строчные.

1. Щёлкните по заголовку столбца ФИО_менеджера.
2. Вкладка «Главная» → «Форматирование» (или «Преобразование» → «Форматирование») → выберите «С прописной буквы каждое слово».

✅ Результат: Иванов А.И., Петрова Е.В., Смирнов К.Л., Иванов А.И., Петрова Е.В., Смирнов К.Л., Фёдорова М.И., Иванов А.И., Козлов Д.Д..

Теперь все имена выглядят аккуратно. Если нужно отдельно фамилию и инициалы — сделаем дальше.

Шаг 3. Разделяем столбец «ФИО_менеджера» на фамилию и инициалы

В одном столбце хранятся и фамилия, и инициалы. Разделим их по пробелу.

1. Выделите столбец ФИО_менеджера.
2. Вкладка «Главная» → «Разделить столбец» → «По разделителю».
3. В появившемся окне:
   Разделитель: «Пробел»
   Нажмите «ОК».

Power Query создаст два столбца: ФИО_менеджера.1 (фамилия) и ФИО_менеджера.2 (инициалы). Переименуйте их (двойной щелчок по заголовку): «Фамилия» и «Инициалы».

Результат выполнения шага 3.

Шаг 4. Приводим город и адрес в порядок (разделение и регистр)

Столбец Город_адрес содержит город, улицу, номер через запятую. Причём город написан то с маленькой, то с большой буквы.

Сначала разделим по запятой:

1. Выделите столбец Город_адрес.
2. «Разделить столбец» → «По разделителю» → разделитель «Запятая», место разделения: «В позиции левого разделителя».
3. Получится два столбца: Город_адрес.1, Город_адрес.2.
4. Переименуйте их: «Город», «Адрес».

Результат выполнения шага 4.

Теперь исправим регистр города:

1. Выделите столбец «Город».
2. «Форматировать» → «Каждое слово с заглавной» → Москва, Санкт-Петербург, Казань, Ростов-На-Дону.
   (Обратите внимание: «Ростов-на-дону» станет «Ростов-На-Дону» — можно оставить или поправить вручную, но для демонстрации сойдёт.)

Обрежем лишние пробелы (если есть):

Выделите все три столбца (Город, Улица, Дом) → «Форматировать» → «Усечь» — удалит пробелы по краям.

Шаг 5. Условный столбец: добавим «Класс продаж» по итогам января

Хотим, чтобы если продажи в январе больше 100 — ставилось «Высокий», иначе «Низкий».

1. Вкладка «Добавление столбца» → «Добавить пользовательский столбец».
2. Заполняем:
   Имя нового столбца: Класс продаж
   Пользвательская формула:

if [Янв] > 100 then "высокий" else "низкий"

3. Нажимаем «ОК».

Появится новый столбец, где у Ивановых, Смирновых, Козлова — «Высокий», у Петровой с 98 — «Низкий» (но в другой строке у Петровой 118, там Высокий — так и задумано).

Результат выполнения шага 5.

Шаг 6. Группировка строк: считаем суммарные продажи по товарным группам за март

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

1. Выделите столбец Товарная_группа (или просто кликните в любом месте).
2. Вкладка «Главная» → «Группировать по».
3. В диалоговом окне:
   Группировать по: Товарная_группа
   Новое имя столбца: Всего_март
   Операция: Сумма
   Столбец: Март
4. Нажмите «ОК».

Результат: три строки — Обувь, Одежда, Аксессуары — с суммами продаж за март. Это удобно для итоговых отчётов.

Чтобы вернуться к исходной детальной таблице, просто удалите шаг «Grouped Rows» в правой панели «Применённые шаги». Если панели "Примененные шаги" у Вас нет, то на вкладке "Просмотр" нажмите "Параметры запроса" - панель появится справа.

Шаг 7. Чистка: удаляем дубликаты строк

В нашей таблице есть почти одинаковые строки (Иванов А.И. с разным регистром — после исправления регистра они стали идентичными). Удалим дубликаты.

1. Выделите все столбцы (или кликните на любой ячейке).
2. Вкладка «Главная» → «Удалить строки» → «Удалить дубликаты».

Power Query оставит только уникальные строки. (В нашем случае — одна запись Иванова А.И., одна Петровой и т.д. — но будьте осторожны: если дубликаты были с разными данными, они тоже удалятся. В реальной жизни сначала проверяйте.)

Шаг 8. Замена значений: переименуем товарную группу «Обувь» в «Обувь/Аксессуары»

1. Щёлкните правой кнопкой по столбцу Товарная_группа.
2. «Заменить значения».
3. Найти: Обувь → Заменить на: Обувь/Аксессуары.
4. ОК.

Теперь все строки с обувью и аксессуарами? Нет, аксессуары останутся отдельно. Если нужно объединить — можно заменить Аксессуары на то же самое.

Шаг 9. Закрываем и загружаем результат

На вкладке «Главная» нажмите «Закрыть и загрузить».

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

Если вам пришлют новую такую же таблицу (с теми же именами столбцов), вы просто вставите её поверх старой, нажмёте «Данные» → «Обновить всё» — и Power Query сделает всю работу за вас. Автоматически! 🤖

💎 Что мы научились делать с одной таблицей?

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

🔥 Вместо послесловия

Попробуйте повторить все шаги на своей таблице. Начните с малого: приведите регистр в каком-нибудь столбце с именами. Потом добавьте условный столбец. Увидите, как быстро это войдёт в привычку.

А если хотите научиться объединять несколько файлов из папки или подтягивать данные из интернета — ставьте лайк и подписывайтесь. В следующих статьях разберём эти суперспособности Power Query! 🚀

Вопросы или свои примеры использования? Пишите в комментариях — обсудим. 👇

Успешной автоматизации! 😊

⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇

Материалы по Эксель. Содержание данного канала:

https://dzen.ru/a/ZhpQXTxmQDShWlXf

⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆

На сегодня все!

Спасибо за внимание!