Power Query в Excel: как обрабатывать данные без формул
Power Query превращает рутинную чистку выгрузок в один повторяемый маршрут: загрузить данные из файлов, папок или базы, преобразовать их без единой формулы и обновлять результат одной кнопкой. Разбираем, где найти инструмент, как устроен редактор и шаги, чем слияние отличается от добавления и как собрать данные из папки файлов в один отчёт.
- Что такое Power Query и зачем он нужен
- Где находится Power Query в Excel
- Источники данных: откуда Power Query берёт информацию
- Редактор Power Query: интерфейс, шаги и базовые операции
- Объединение таблиц: слияние и добавление
- Группировка, сводка и отмена сведения
- Сценарий: собрать данные из папки файлов
- Типичные ошибки новичков
- Итоги
- Вопросы и ответы
Что такое Power Query и зачем он нужен
Power Query — встроенный в Excel инструмент для загрузки и обработки данных. Его задача описывается тремя буквами ETL: extract, transform, load — извлечь данные из источника, преобразовать к нужному виду и загрузить на лист. И всё это без формул и макросов: вы не пишете код, а собираете цепочку действий мышью.
Ключевое отличие от обычной работы в ячейках — Power Query запоминает каждый шаг. Один раз настроили обработку грязной выгрузки: удалили лишние столбцы, поправили типы, отфильтровали строки, подтянули справочник. В следующем месяце пришёл новый файл той же структуры — вы нажимаете «Обновить», и весь маршрут прогоняется заново за секунды. Ручная чистка на полдня превращается в одну кнопку.
Поэтому Power Query — это про повторяющиеся задачи. Если выгрузку из 1С, банка или маркетплейса приходится причёсывать каждую неделю по одному сценарию, инструмент экономит часы. Для разовой правки он избыточен, а для регламентной отчётности незаменим.
Где находится Power Query в Excel
Все команды Power Query собраны на вкладке «Данные» в группе «Получить и преобразовать данные». Точка входа — кнопка «Получить данные»: под ней список всех источников (из файла, из базы данных, из сети и другие). Рядом быстрые кнопки для частых случаев: «Из текстового/CSV-файла», «Из Интернета» и «Из таблицы/диапазона».
Про версии, чтобы не путаться. В Excel 2010 и 2013 Power Query был отдельной бесплатной надстройкой Microsoft. Начиная с Excel 2016 он встроен и живёт на вкладке «Данные» — так же в Excel 2019, 2021 и Microsoft 365. В веб-версии возможности ограничены, полноценно инструмент работает в настольном Excel под Windows.
Чтобы вернуться к готовым запросам книги, откройте «Данные» → «Запросы и подключения»: справа появится панель, где запросы можно обновлять, переименовывать и открывать двойным щелчком.
Источники данных: откуда Power Query берёт информацию
Сила инструмента в том, что дальше интерфейс для любого источника одинаковый: подключились — и попали в один и тот же редактор. Меняется только первый шаг подключения. Основные варианты — в таблице.
| Источник | Как подключить | Когда использовать |
|---|---|---|
| Таблица или диапазон в текущей книге | «Данные» → «Из таблицы/диапазона» | Данные уже на листе, нужно их очистить или преобразовать |
| Файл Excel | «Получить данные» → «Из файла» → «Из книги Excel» | Подтянуть данные из другого файла, не открывая его |
| CSV или текстовый файл | «Из текстового/CSV-файла» | Выгрузки из банка, 1С, CRM и интернет-магазинов |
| Папка с файлами | «Из файла» → «Из папки» | Собрать десятки однотипных файлов в одну таблицу |
| База данных | «Из базы данных» → SQL Server, PostgreSQL и другие | Прямой доступ к учётной или складской системе |
| Веб-страница | «Из других источников» → «Из Интернета» | Таблицы с сайта: курсы валют, справочники, каталоги |
Редактор Power Query: интерфейс, шаги и базовые операции
После подключения открывается отдельное окно — редактор Power Query. Исходная книга уходит на второй план: обработка идёт здесь, а результат вернётся на лист, когда вы нажмёте «Закрыть и загрузить». Из чего состоит окно:
- Лента сверху — вкладки «Главная», «Преобразование», «Добавление столбца», «Вид»: здесь все операции.
- Панель «Запросы» слева — список запросов книги, переключение щелчком.
- Предпросмотр по центру — первые строки таблицы с результатом каждого действия.
- «Параметры запроса» справа — самое важное: список «Примененные шаги».
«Примененные шаги» — сердце Power Query. Каждое действие записывается сюда отдельной строкой: «Источник», «Измененный тип», «Удаленные столбцы». Список читается сверху вниз как история обработки. Любой шаг можно выделить, переименовать, удалить крестиком или перетащить, поменяв порядок. Ошиблись — не переделываете всё заново, а убираете один шаг.
Под капотом каждый шаг — строка на встроенном языке M, который Power Query пишет за вас. Заглянуть в код можно через «Вид» → «Расширенный редактор», но обычно это не нужно: маршрут собирается кнопками на ленте.
Базовые операции, которые закрывают львиную долю чистки данных:
- Удалить столбцы. Правый клик → «Удалить столбцы». Надёжнее обратный приём «Выбрать столбцы»: он оставляет только нужные, и запрос не сломается, если в источнике появятся новые.
- Удалить строки. «Главная» → «Удаление строк»: верхние шапки отчёта, пустые строки, строки с ошибками, дубликаты.
- Изменить тип данных. Щёлкните значок типа слева от заголовка: текст, число, дата. Без правильных типов не сработают ни группировки, ни объединения.
- Разделить и объединить столбцы. «Разделить столбец» по разделителю или числу знаков превращает «Иванов Иван» в фамилию и имя; «Объединить столбцы» собирает адрес из частей.
- Заменить значения. Правый клик → «Замена значений»: поправить написание, убрать лишние символы, заменить «нет данных» на пусто.
- Фильтры. Стрелка у заголовка работает как автофильтр, но запоминается шагом и применяется при каждом обновлении.
- Заполнить вниз. Когда значение стоит только в первой строке группы: «Преобразование» → «Заполнить» → «Вниз» протянет его по пустым ячейкам.
Объединение таблиц: слияние и добавление
Данные почти никогда не лежат в одной таблице. Power Query собирает их двумя принципиально разными способами, и путать их — типичная ошибка новичка.
Слияние (Merge) — соединение таблиц по общему ключу, аналог ВПР, только надёжнее и без ограничений на объём. К продажам с кодом товара подтягиваются название и цена из справочника. Команда: «Главная» → «Объединить запросы»: указываете общий столбец в обеих таблицах и тип соединения.
Добавление (Append) — дописывание одной таблицы под другую, когда набор столбцов совпадает. Так три файла продаж за январь, февраль и март превращаются в таблицу за квартал. Команда: «Главная» → «Добавить запросы»; ключ здесь не нужен, таблицы складываются стопкой по вертикали.
| Признак | Слияние (Merge) | Добавление (Append) |
|---|---|---|
| Что делает | Подтягивает столбцы из другой таблицы по ключу | Дописывает строки одной таблицы под другую |
| Направление роста | Таблица становится шире (больше столбцов) | Таблица становится длиннее (больше строк) |
| Нужен общий ключ | Да, обязательно | Нет |
| Требование к структуре | Совпадает ключевой столбец | Совпадают заголовки столбцов |
| Аналог в Excel | ВПР / ПРОСМОТРX | Копирование строк вручную |
| Пример | Продажи + справочник цен | Отчёты за 12 месяцев в один |
При слиянии выбирают тип соединения: «внешнее левое» оставляет все строки первой таблицы и подставляет совпадения из второй (аналог ВПР), «внутреннее» — только строки с парой, «анти» — строки без пары, чтобы ловить товары без цены или клиентов без заказов.
Группировка, сводка и отмена сведения
Группировка. «Преобразование» → «Группировать по» сворачивает детальные строки в итоги — как сводная таблица, но результат остаётся обычным запросом. Укажите поле группировки («Менеджер»), операцию (сумма, количество, среднее) и столбец расчёта — из 20 000 строк получится компактная таблица «менеджер — сумма продаж».
Отмена сведения (Unpivot). Недооценённая, но очень полезная операция. Часто отчёты приходят в «широком» виде: строки — товары, а каждый месяц вынесен в отдельный столбец (Январь, Февраль, Март…). Для сводных и графиков это неудобно. Выделите столбцы месяцев → правый клик → «Отменить свёртывание столбцов», и широкая таблица станет узкой: три столбца «Товар», «Месяц», «Значение» — канонический плоский формат.
Загрузка результата. Нажмите «Закрыть и загрузить» — по умолчанию запрос выгружается на новый лист как умная таблица. Через «Закрыть и загрузить в…» доступны варианты: только создать подключение, построить сводную таблицу или отправить данные в модель данных.
Повторное обновление — главная выгода. Весь маршрут сохранён в запросе. Пришли свежие данные — жмёте «Обновить» на таблице или «Данные» → «Обновить всё» (Ctrl+Alt+F5), и все шаги применяются заново автоматически. Никакой ручной чистки во второй раз.
Связь с Power Pivot. Power Query отвечает за загрузку и подготовку данных, Power Pivot — за их анализ. Power Query чистит источники и складывает их в модель данных, а Power Pivot строит связи между таблицами и вычисляет показатели на языке DAX. Для отчётности на миллионы строк они работают вместе, но осваивают их по очереди — сначала Power Query.
Устали причёсывать одни и те же выгрузки вручную каждую неделю?
На курсе «Excel + Google Таблицы» вы освоите не только формулы и сводные таблицы, но и обработку данных через Power Query — на реальных рабочих выгрузках, с домашними заданиями и обратной связью преподавателя. Форматы на выбор: очно, в прямом эфире или онлайн.
Сценарий: собрать данные из папки файлов
Самая показательная задача Power Query — сборка множества однотипных файлов в один отчёт. За год накопилось 12 файлов «Продажи_Январь.xlsx», «Продажи_Февраль.xlsx» и так далее, все с одинаковыми столбцами. Вручную — час копирования; через Power Query — несколько шагов, которые повторяются одной кнопкой.
- Сложите все файлы за период в одну папку и убедитесь, что структура столбцов одинаковая. Посторонних файлов в папке быть не должно.
- Откройте «Данные» → «Получить данные» → «Из файла» → «Из папки» и укажите путь.
- В окне предпросмотра нажмите «Объединить и преобразовать данные» — Power Query возьмёт первый файл за образец структуры.
- Выберите лист или таблицу, которые собрать из каждого файла, и подтвердите. Инструмент склеит данные всех файлов в одну таблицу.
- В редакторе почистите результат: удалите лишние столбцы, задайте типы, отфильтруйте строки. В добавленном столбце с именем файла удобно найти месяц.
- Нажмите «Закрыть и загрузить» — таблица за год окажется на новом листе.
- В следующем месяце добавьте новый файл в ту же папку и нажмите «Обновить» — он подтянется в отчёт автоматически.
Дано: CSV-выгрузка заказов на 8 000 строк. Проблемы типичные — суммы записаны как текст с пробелами-разделителями, дата и время в одном столбце, ФИО слитно, внизу строки-итоги и пустые строки.
Маршрут: подключились «Из текстового/CSV-файла»; удалили нижние итоги; отфильтровали пустые; разделили «Дата-время» по пробелу на дату и время; сменили тип «Суммы» на десятичное число (лишние пробелы Power Query убирает заменой значений); разделили «ФИО» на фамилию, имя и отчество; переименовали столбцы и загрузили результат.
Итог: восемь шагов в панели «Примененные шаги». Новая выгрузка того же магазина обрабатывается нажатием «Обновить» — за секунду вместо получаса ручной работы.
Типичные ошибки новичков
- Создают запрос заново вместо «Обновить». Если структура источника прежняя, новые данные не требуют повторной настройки — достаточно кнопки «Обновить». Заново подключаются только при смене формата файла.
- Удаляют столбцы вместо «Выбрать столбцы». Если в источнике добавится новый столбец, шаг «Удаленные столбцы» может сломаться. Надёжнее оставлять нужные через «Выбрать столбцы».
- Забывают про типы данных. Пока «Сумма» имеет текстовый тип, группировка и математика по ней не работают. Первым делом проверьте значок типа у каждого столбца.
- Меняют исходный файл после настройки. Переименовали файл или папку, переставили или удалили столбцы — запрос теряет их и выдаёт ошибку. Держите структуру и расположение источника постоянными.
- Правят данные в предпросмотре. В ячейках редактора нельзя печатать вручную: любое изменение — это шаг-операция, а разовые правки делаются в источнике.
- Путают слияние и добавление. Пытаются дописать месяцы через «Объединить запросы» и получают кашу из столбцов. Запомните: добавить строки — Append, подтянуть столбцы по ключу — Merge.
- Выгружают на лист то, что должно быть подключением. Промежуточный справочник не обязательно грузить в книгу — выберите «Только создать подключение», чтобы не плодить листы и не раздувать файл.
Итоги
- Power Query — встроенный в Excel ETL-инструмент: загружает данные из файлов, папок, баз и веба и преобразует их без формул и макросов.
- Каждое действие сохраняется шагом в панели «Примененные шаги», поэтому обработку можно отредактировать, откатить и повторить одной кнопкой «Обновить».
- Базовые операции — удалить столбцы и строки, задать типы, разделить и объединить столбцы, заменить значения, отфильтровать — закрывают большинство задач по чистке данных.
- Слияние (Merge) подтягивает столбцы по ключу, как ВПР; добавление (Append) дописывает строки; отмена сведения приводит отчёт к удобному плоскому виду.
- Главная выгода — регламентные отчёты: настроили маршрут один раз, дальше новые данные обрабатываются нажатием одной кнопки вместо часов ручной работы.
Вопросы и ответы
- Power Query — это то же самое, что макросы?
Нет. Макросы записывают действия на языке VBA и требуют навыков программирования, а Power Query собирает обработку мышью и хранит её как понятную цепочку шагов. Для загрузки и чистки данных он проще, безопаснее и не требует написания кода.
- В какой версии Excel есть Power Query?
Начиная с Excel 2016 он встроен и находится на вкладке «Данные». В Excel 2010 и 2013 его ставили отдельной бесплатной надстройкой Microsoft. В веб-версии возможности ограничены — полноценно инструмент работает в настольном Excel под Windows.
- Чем слияние отличается от добавления запросов?
Слияние (Merge) подтягивает столбцы из другой таблицы по общему ключу и делает таблицу шире — это аналог ВПР. Добавление (Append) дописывает строки одной таблицы под другую и делает таблицу длиннее; общий ключ ему не нужен.
- Изменит ли Power Query мои исходные данные?
Нет. Power Query всегда работает с копией: он читает источник, преобразует данные в редакторе и выгружает результат на отдельный лист. Исходный файл или таблица остаются нетронутыми, поэтому экспериментировать можно без риска.
- Нужно ли повторять всю обработку для новых данных?
Нет, в этом главный смысл инструмента. Весь маршрут сохранён в запросе. Когда приходит новый файл прежней структуры, достаточно нажать «Обновить» — и все шаги применятся к свежим данным автоматически, за секунды.
- Что такое язык M в Power Query?
M — встроенный язык, на котором Power Query записывает каждый ваш шаг под капотом. Видеть и править код можно через «Расширенный редактор», но для большинства задач это не требуется: весь маршрут собирается кнопками на ленте.
Материал носит информационный характер.