Необходимость учитывать регистр (регистрочувствительность) - одно из первых заметных принципиальных отличий, с которыми сталкиваются те, кто начинают работать в Power Query. В отличие от Excel, который прописные и строчные буквы в подавляющем большинстве случаев не различает, Power Query в этом вопросе строг. При любых операциях с данными (фильтрации, сортировке, удалении дубликатов, в исходном М-коде запросов и т.д.) Query воспринимает большие и маленькие буквы как совершенно разные.
Конечно, рано или поздно, к этому привыкаешь и начинаешь относиться как к данности и учитывать в работе. Так, например, многие пользователи перед фильтрацией, чтобы она была регистроНечувствительной, сначала делают дубликат столбца, в котором затем преобразуют весь текст к одному регистру и только потом фильтруют. Вполне себе способ.
На самом деле, решить эту проблему можно гораздо изящнее, если использовать встроенную в языке М в Power Query функцию с громоздким названием Comparer.OrdinalIgnoreCase.
В аналитических отчетах и дашбордах весьма часто встречается потребность вывести итоговые значения в формате сводной таблицы вида "Топ-N + другие":
Сводные таблицы Excel так, к сожалению, пока не умеют, но если доработать исходные данные с помощью запроса Power Query, то задача решается относительно легко.
Разбираем три способа реализовать горизонтальную фильтрацию столбцов, которая изначально отсутствует в Microsoft Excel: с помощью функции ФИЛЬТР, запроса Power Query + сводная таблица и макросом на VBA.
Разбор типичных "граблей" - о том, как избежать проблем при разворачивании вложенных таблиц в Power Query, если в исходных данных добавляются или удаляются столбцы.
Одно из самых просматриваемых видео у меня на YouTube-канале - это видео про Мгновенное заполнение (Flash Fill)в Microsoft Excel. Суть этого инструмента в том, что если вам надо как-то преобразовать ваши исходные данные, то достаточно просто начать набирать в соседнем с ними столбце тот результат, который вы хотите получить. После нескольких вручную набранных ячеек (обычно хватает 2-3) Excel "поймёт" логику нужных вам преобразований и автоматически продолжит набранное, завершив всю монотонную работу за вас.
Квинтэссенция эффективности. Волшебная кнопка "сделать всё хорошо", которую мы все так любим, правда?
На самом деле, аналог подобного инструмента есть и в Power Query - там он называется Столбец из примеров (Column from Examples). По сути, это маленький встроенный в Power Query искусственный интеллект, который умеет быстро обучаться на ваших данных и затем их преобразовывать. Давайте детально рассмотрим его возможности на нескольких практических сценариях, чтобы понять, где он может нам с вами пригодиться в реальных задачах.
Подробный и пошаговый разбор весьма типовой задачи, когда в качестве входных данных имеем Excel-файл, где на одном из листов расположены друг под другом несколько таблиц с данными по продажам следующего вида:
А на выходе нам нужно собрать данные из всех таблиц в одну плоскую нормированную таблицу, удобную для последующего анализа и построения сводной, т.е. вот в такую:
Само собой, использовать для этого будем наиболее подходящий инструмент - Power Query:
Почти на каждом тренинге по Power Query, когда мы доходим до способов обновления созданных запросов и люди видят как новые данные заменяют при обновлении старые, меня кто-нибудь из слушателей спрашивает: "а можно сделать так, чтобы при обновлении старые данные тоже где-нибудь сохранялись и было видно всю историю обновлений?"
Мысль не новая и стандартным ответом на неё будет "нет" - Power Query по умолчанию настроен именно на замену старых данных новыми (что и требуется в абсолютном большинстве случаев). Однако, если очень хочется, то можно обойти это ограничение. Причем способ, как вы дальше увидите, весьма простой.
Многие бизнес-процессы (и даже целые бизнесы) в этой жизни предполагают выполнение заказов ограниченным количеством исполнителей к заданным срокам. Планирование в таких случаях происходит, что называется, "от календаря" и часто возникает потребность переноса запланированных в нём событий (заказов, встреч, поставок) в Microsoft Excel - для дальнейшего анализа формулами, сводными таблицами, построения диаграмм и т.п.
Само-собой, хотелось бы реализовать такой перенос не тупым копированием (что как раз несложно), а с автоматическим обновлением данных, чтобы в будущем всё внесенные в календарь изменения и новые заказы "на лету" отображались бы в Excel. Реализовать такой импорт можно за считанные минуты с помощью надстройки Power Query, встроенной в Microsoft Excel.
Поиск ключевых слов в исходном тексте - одна из очень распространенных задач при работе с данными. Давайте рассмотрим её решение несколькими способами на следующем примере:
Предположим, что у нас с вами есть список ключевых слов - названия автомобильных марок - и большая таблица всевозможных запчастей, где в описаниях иногда могут встречаться один или сразу несколько таких брендов, если запчасть подходит больше, чем к одной марке автомобиля. Наша задача состоит в том, чтобы найти и вывести все обнаруженные ключевые слова в соседние ячейки через заданный символ-разделитель (например, запятую).
Сделаем это разными способами: формулами и с помощью Power Query.
Сборка таблиц - классическая задача для любого пользователя Excel. Особенно "весело" она решается, если шапки в этих таблицах точно не совпадают, а имеют какие-либо различия. Решить проблему можно весьма изящно - используя надстройку Power Query, которая умеет собирать таблицы даже с разной структурой:
Как разобрать данные из одной таблицы сразу на несколько листов по заданному критерию.Разбираем два способа - с обновлением (через Power Query) и без (только VBA).
Сплошь и рядом встречаются сайты, где при попытке импортировать с них данные в Excel Power Query просто в упор не видит там таблиц с нужной нам информацией. Причин для этого может быть несколько, но чаще всего это происходит потому, что веб-дизайнер при создании таблицы использовал в HTML-коде страницы не стандартную конструкцию с тегом <TABLE>, а её аналог - вложенные друг в друга теги-контейнеры <DIV>. Это весьма распространённая техника при вёрстке веб-сайтов, но, к сожалению, Power Query пока не умеет распознавать такую разметку и загружать такие данные в Excel. Тем не менее, есть способ обойти это ограничение
Давайте разберемся с этой задачей на примере парсинга цен и описаний товаров с маркетплейса Wildberries:
Имеем список объектов (например, товаров) с пометкой, к какому набору (корзине) каждый из них относится. Необходимо разложить объекты по своим наборам, сформировав таблицу как на рисунке справа:
Похожие задачи встречаются на практике весьма часто - в случаях, когда приходится распределять те или иные ресурсы:
сотрудников по командам
водителей по маршрутам
клиентов по менеджерам
товары по корзинам и т.д.
В прошлом я уже делал статью и видео о похожей задаче, где мы разбирали как переложить одномерный столбец с данными в двумерную таблицу, но там ситуация была проще, т.к. каждый набор (строка) имел одинаковый размер (число столбцов). Здесь же количество элементов в наборах заранее не известно и не равно друг другу, так что подход придётся изменить.
Чтобы у вас был выбор, давайте разберем несколько способов .
Одна из самых распространенных проблем (и частых вопросов на тренингах) в том, как построить сводную таблицу, если в качестве исходных данных тебе досталась вот такая "красота":
Выручить здесь может надстройка Power Query, при помощи которой можно за несколько минут превратить эту жесть в нормализованную плоскую таблицу, по которой строить сводную можно уже легко:
Хороший пример, если вы осваиваете возможности Power Query - тут они раскрываются во всей красе. Ну и, само-собой, видео для тех, кому лень читать
Как при помощи Power Query сделать в Excel "вечный" производственный календарь - автоматически обновляющийся список нерабочих дней за все годы для использования в своих расчетах.
Пару лет назад я уже делал . Это здорово развязывает нам руки при анализе и парсинге текста и предоставляет в наше распоряжение мощный и гибкий функционал, рядом с которым стандартные текстовые функции Excel и рядом не стояли. Открытым, однако, остался вопрос - можно ли добавить поддержку регулярных выражений в Power Query? Справка и тех.поддержка Microsoft отвечают на этот вопрос сугубо отрицательно, но, на самом деле, есть способ обойти это ограничение...
Я когда-то уже писал на бесплатную от Microsoft, позволяющую находить соответствия двух списков при неточном совпадении данных. Недавно, с последними обновлениями Office 365, аналогичный функционал пришёл и в Power Query в Excel. До Power BI Desktop, кстати, он тоже добрался. Давайте разберёмся, как этот инструмент работает, его плюсы, минусы и нюансы применения.
Ощутимо перелопатил и дополнил . Добавил самый универсальный, я считаю, метод через Power Query и записал уже традиционное видео с демонстрацией всех трёх способов - от скрытой функции до макроса и мегаудобного запроса в PQ с последующим подсчетом статистики в сводной таблице:
Теперь уже вполне официально можно сказать, что я стал "многодетным отцом" в книжном смысле - только что вышла моя третья книга "Скульптор данных в Excel с Power Query". Электронная версия уже с прошлой недели, а сегодня до меня доехал первый бумажный тираж из типографии:
Надеюсь, первая на русском языке книга о Power Query получилась достойной. Во всяком случае, я очень старался. Несколько глав пришлось корректировать уже после написания пару раз, т.к. за последний год вышло очень много обновлений Excel, порой, весьма ощутимо меняющих интерфейс и возможности этой надстройки. На данный момент идут переговоры с сетью книжных магазинов "Читай-Город", чтобы все мои книги в бумажном варианте появились на их полках и в интернет-магазине. Так что скоро, надеюсь, эти томики окажутся в зоне вашей досягаемости.
Про поиск и подсветку дубликатов в разных ячейках и диапазонах я уже не раз писал, но что делать если нужно найти и, возможно, удалить повторяющиеся слова внутри ячейки? Например, мы имеем вот такую таблицу с данными (разделителями могут быть не обязательно пробелы): Хорошо видно, что некоторые имена в списках внутри ячеек повторяются. Давайте посмотрим, что можно с этим сделать, а именно:
Как быстро найти все ячейки, содержащие повторы
Как подсветить дубликаты цветом
Как удалить дубликаты, оставив только неповторяющиеся значения
Переносы строк внутри одной ячейки, добавляемые с помощью сочетания клавиш Alt+Enter - дело весьма частое и привычное. Иногда их делают сами пользователи, чтобы добавить красоты длинному тексту. Иногда такие переносы добавляются автоматически при выгрузке данных из каких-либо рабочих программ (привет 1С, SAP и т.д.) Проблема в том, что на такие таблицы приходится потом не просто любоваться, а с ними работать - и вот тогда эти невидимые символы переноса могут стать проблемой. А могут и не стать - если уметь правильно с ними обращаться.
Давайте-ка мы разберёмся в этом вопросе поподробнее. А именно:
Научимся удалять нежелательные переносы заменой или формулами
Научимся делить по Alt+Enter'ам на столбцы или строки (макросом или через Power Query)
Классика жанра - вопрос из серии "как мне собрать таблицы со всех листов моей книги на один?". Представьте, что у вас есть файл с больше, чем полусотней листов-отчетов по городам. Всё это счастье нужно собрать в одну таблицу для построения в будущем, допустим, сводной. Выручить могут два основных подхода: макросы и Power Query.
Пару лет назад я уже делал . Это здорово развязывает нам руки при анализе и парсинге текста и предоставляет в наше распоряжение мощный и гибкий функционал, рядом с которым стандартные текстовые функции Excel и рядом не стояли.
