Стандартных инструментов Excel для простых задач, конечно, хватает. Но когда наступает время серьёзной обработки данных — они становятся неудобными, медленными и ломкими. Power Query — это скорость, простота и идеальный порядок. Делюсь пятью повседневными задачами, где я забыл о старых способах Excel и перешёл только на Power Query.
С чего начать: быстрая загрузка данных
Выделяю любую ячейку в таблице, затем на вкладке "Данные" — кликаю "Из таблицы/диапазона".
Все необходимые шаги выполняю в Power Query, а когда всё готово — перехожу на "Главную" и выбираю "Закрыть и загрузить". Новый лист с обработанными данными готов!
Забудьте про сложные IF: используйте условные столбцы
В Excel для сложной логики приходится строить формулы с невероятной вложенностью IF — запутаться очень просто, а найти ошибку иногда невозможно.
Задача из жизни
Допустим, нужно поделить продажи на категории: «малые», «средние», «крупные» — в зависимости от объёма.
Стандартный способ — добавить столбец и набросать вложенный IF:
Но стоит только запутаться с кавычками или запятой — формула ломается. А понять, что происходит внутри, — отдельный квест.
Про сложные IF забудьте: попробуй SWITCH для чистоты формул
Ваша логика — без лишнего кода и путаницы!
Как всё упростить с помощью условных столбцов
Загружаю таблицу в Power Query, иду во вкладку "Добавить столбец" — "Условный столбец".
Открывается окно, где даю имя столбцу и простыми условиями из выпадающих списков задаю логику. Например: если "Продажи" больше 1000 — вернуть "Крупные".
Писать кавычки вручную больше не надо — одно удовольствие.
Добавляю следующее условие через "Добавить условие" — на этот раз для средних продаж.
В поле "Иначе" пишу Малые и сохраняю изменения.
Готово! Новый столбец появляется в таблице — закрываю Power Query и возвращаю результат на лист.
Почему это удобнее, чем бесконечные IF
Вся логика оформлена отдельным шагом, который сразу виден и легко понятен любому — не надо рыться в формулах и искать ошибки в скобках. Если условия меняются, просто щёлкаешь по “шестерёнке” и правишь настройки — никаких новых формул не нужно.
Объедини таблицы в одно движение: забудь о ВПР и XLOOKUP
Сопоставить две таблицы в Excel — задача регулярная, но традиционные формулы типа ВПР ломаются при малейших изменениях. А на больших объёмах Excel начинает “замедляться” и летает с трудом.
Задача из практики
Есть таблица продаж, есть справочник товаров на отдельном листе. Нужно добавить цену из справочника в отчёт.
XLOOKUP или ВПР с этим справятся, но если переехал лист или поменялись названия — вся формула уже не работает. А если строк много — ждать результата приходится долго.
Как объединить таблицы за пару минут
Сначала загружаю первую таблицу в Power Query, при выходе жму “Закрыть и загрузить”, там выбираю “Только создать связь”.
В меню "Запросы и подключения" вижу созданную связь.
Так же импортирую вторую таблицу.
Обе связи на месте — кликаю правой кнопкой по нужной и выбираю “Изменить” (чтобы открыть Power Query).
Жму “Объединить запросы”, указываю вторую таблицу, выделяю нужные столбцы (например, по артикулу или SKU), далее “ОК”.
В появившемся колонке жму на значок “развернуть”, выбираю нужное поле (“Цена”) и сохраняю результат.
В "Запросах" таблица пока как связь. Чтобы вывести результат на лист, жму правой кнопкой, “Загрузить как”, выбираю “Таблица” и “Новый лист”.
Остаётся нажать “ОК” — и все данные объединены на новой странице Excel.
Нужно сшить несколько листов? Включай Power Query Append!
Больше никакого копипаста: одно объединение — и всё готово.
Главное преимущество объединения через Power Query
Любые формулы поиска работают с каждой ячейкой по отдельности, а Power Query соединяет таблицы целиком. Это особенно круто, если у вас большие массивы данных или сложные сцепки.
Преобразуйте «широкие» данные в секунду — Unpivot вместо ручной рутины!
В Excel развернуть «широкую» таблицу для анализа или сводных — сплошная морока, особенно если отчёты меняются.
Реальный кейс
Периодически приходят свежие отчёты: в первой колонке — товары, в заголовках — названия месяцев. Для нормальной аналитики надо чтобы все месяцы были в одном столбце.
Копировать и транспонировать вручную приходится каждый месяц. Надоело!
Как расправиться с этим за минуту
Загружаю таблицу в Power Query, выделяю столбцы, которые не нужно разворачивать (зажимая Ctrl), правой кнопкой — “Развернуть другие столбцы”.
Вуаля — стоячая таблица превращается в длинную: столбец названий месяцев и столбец с данными.
Двойной клик по заголовкам — переименовываю их на Месяц и Выручка, корректирую тип столбцов прямо здесь.
Готово — выгружаю на лист. Всё в правильном виде.
Почему Unpivot — лучший друг
Инструмент гоняет любые новые месяца автоматически — при следующем обновлении в отчёте все столбцы мгновенно подхватятся. Никакой ручной пересортировки и пересоздания форм!
5 приёмов моментальной очистки и упорядочивания таблицы в Excel
Приведите свои данные в идеальный вид — и сэкономьте уйму времени!
Импортируйте из папки — забудьте о копипасте навсегда!
Одна из самых частых причин ошибок в Excel — ручное добавление данных из десятков файлов: легко что-нибудь упустить или продублировать лишний раз.
Задача из реальности
Мне каждую неделю присылают новый файл с отчётом по региону. Раньше я открывал каждый, копировал данные и вклеивал в общую таблицу. Это дрянная и опасная рутина.
Как быстро собрать кучу файлов в одну таблицу
Складываю все отчёты в отдельную папку (желательно, чтобы там не было ничего лишнего).
В Excel открываю: Данные > Получить данные > Из файла > Из папки.
Указываю путь до папки, “Открыть”.
Далее — “Комбинировать” → “Комбинировать и преобразовать данные”.
Выбираю пример нужного листа — важно, чтобы названия листа и колонок совпадали во всех файлах, — и жму “ОК”.
Смотрю, как собраны данные — если всё ок, жму “Закрыть и загрузить”.
Почему загрузка “из папки” — настоящая магия
Теперь не надо вручную открывать и копировать данные. Просто складываете новый файл в папку — а на главном листе достаточно нажать “Обновить”, и свежие данные появляются сами!
Делим столбцы на части: лёгкость против головной боли
Стандартные инструменты Excel для разделения столбцов часто неудобны: новые данные не обрабатываются автоматически, а TEXTSPLIT иногда плодит лишние столбцы и только мешает.
Задача для Power Query
Есть столбец с ФИО — надо выделить отдельно имя и фамилию.
Через "Текст по столбцам" получаем только статичный результат — новые имена уже не разобьются сами. TEXTSPLIT даёт ещё и неожиданные колонки, когда попадается отчество.
Идеальный способ: деление столбца через Power Query
Импортирую данные, кликаю правой кнопкой по заголовку столбца, выбираю “Разделить столбец” → “По разделителю”.
Выбираю “Пробел” в роли разделителя.
Хочу, чтобы отчество всегда попадало к фамилии — отмечаю “По последнему разделителю”. Так всегда будет ровно две колонки, даже если встретятся двойные имена.
Жму “ОК”, переименовываю столбцы, выгружаю на лист.
Пустоты заменяю тут же: правой кнопкой по столбцу — “Заменить значения” — указываю, чем заполнить пропуски (например, 0 или N/A).
Почему так удобнее?
Power Query сам разбивает новые строки при каждом обновлении — не надо запускать мастера разделения заново. Новые столбцы уже интегрированы в общую таблицу, пустые ячейки больше не мешают. А поменять правило можно буквально в пару кликов через “шестерёнку”.
Освоили эти пять приёмов? Тогда вы уже в лиге Power Query: автоматизировать обработку даже самых запутанных таблиц теперь можно одним движением руки!
Microsoft 365 Персональный
Microsoft 365 — полный комплект: Word, Excel и PowerPoint на пяти устройствах, 1 ТБ в облаке OneDrive и масса других плюшек.
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Премиум подписка - это доступ к эксклюзивным материалам, чтение канала без рекламы, возможность предлагать темы для статей и даже заказывать индивидуальные обзоры/исследования по своим запросам!Подробнее о том, какие преимущества вы получите с премиум подпиской, можно узнать здесь
Также подписывайтесь на нас в:
- Telegram: https://t.me/gergenshin
- Youtube: https://www.youtube.com/@gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru