Новая статья: Разделение многоуровневого списка по столбцам
Новая статья : Мультистраничный веб-запрос в Power Query
- Создать сначала одиночный веб-запрос к любой странице из интересующего списка
- Создать параметр для года и внедрить его в запрос
- Преобразовать запрос в функцию (параметр станет её аргументом)
- Создать список интересующих нас лет (2010 - 2021) и вызвать созданную функцию, подставив ей список в качестве аргумента
- Объединить полученные результаты в единую таблицу за все годы
Новая статья: РегистроНЕчувствительность в Power Query
Конечно, рано или поздно, к этому привыкаешь и начинаешь относиться как к данности и учитывать в работе. Так, например, многие пользователи перед фильтрацией, чтобы она была регистроНечувствительной, сначала делают дубликат столбца, в котором затем преобразуют весь текст к одному регистру и только потом фильтруют. Вполне себе способ.
На самом деле, решить эту проблему можно гораздо изящнее, если использовать встроенную в языке М в Power Query функцию с громоздким названием Comparer.OrdinalIgnoreCase.
Новая статья: Динамический "Топ-N и другие" в сводной таблице
В аналитических отчетах и дашбордах весьма часто встречается потребность вывести итоговые значения в формате сводной таблицы вида "Топ-N + другие":
Сводные таблицы Excel так, к сожалению, пока не умеют, но если доработать исходные данные с помощью запроса Power Query, то задача решается относительно легко.
Новая статья: Горизонтальная фильтрация столбцов в Excel
Новая статья: Как правильно разворачивать таблицы в Power Query
Разбор типичных "граблей" - о том, как избежать проблем при разворачивании вложенных таблиц в Power Query, если в исходных данных добавляются или удаляются столбцы.
Новая статья: Столбец из примеров - искусственный интеллект в Power Query
Квинтэссенция эффективности. Волшебная кнопка "сделать всё хорошо", которую мы все так любим, правда?
На самом деле, аналог подобного инструмента есть и в Power Query - там он называется Столбец из примеров (Column from Examples). По сути, это маленький встроенный в Power Query искусственный интеллект, который умеет быстро обучаться на ваших данных и затем их преобразовывать. Давайте детально рассмотрим его возможности на нескольких практических сценариях, чтобы понять, где он может нам с вами пригодиться в реальных задачах.
Новая статья: Сборка разноформатных таблиц
Подробный и пошаговый разбор весьма типовой задачи, когда в качестве входных данных имеем Excel-файл, где на одном из листов расположены друг под другом несколько таблиц с данными по продажам следующего вида:
Новая статья: Нарастающие итоги в Excel
Что такое нарастающие итоги (Running Totals) и как вычислить в Microsoft Excel разными способами:
- формулами,
- в сводной таблице,
- в запросе Power Query.
Новая статья: Сохранение истории обновлений запросов Power Query
Почти на каждом тренинге по Power Query, когда мы доходим до способов обновления созданных запросов и люди видят как новые данные заменяют при обновлении старые, меня кто-нибудь из слушателей спрашивает: "а можно сделать так, чтобы при обновлении старые данные тоже где-нибудь сохранялись и было видно всю историю обновлений?"
Мысль не новая и стандартным ответом на неё будет "нет" - Power Query по умолчанию настроен именно на замену старых данных новыми (что и требуется в абсолютном большинстве случаев). Однако, если очень хочется, то можно обойти это ограничение. Причем способ, как вы дальше увидите, весьма простой.
Новая статья: Система учета заказов на Google Calendar и Excel
Многие бизнес-процессы (и даже целые бизнесы) в этой жизни предполагают выполнение заказов ограниченным количеством исполнителей к заданным срокам. Планирование в таких случаях происходит, что называется, "от календаря" и часто возникает потребность переноса запланированных в нём событий (заказов, встреч, поставок) в Microsoft Excel - для дальнейшего анализа формулами, сводными таблицами, построения диаграмм и т.п.
Новая статья: Поиск ключевых слов в тексте
Поиск ключевых слов в исходном тексте - одна из очень распространенных задач при работе с данными. Давайте рассмотрим её решение несколькими способами на следующем примере:
Предположим, что у нас с вами есть список ключевых слов - названия автомобильных марок - и большая таблица всевозможных запчастей, где в описаниях иногда могут встречаться один или сразу несколько таких брендов, если запчасть подходит больше, чем к одной марке автомобиля. Наша задача состоит в том, чтобы найти и вывести все обнаруженные ключевые слова в соседние ячейки через заданный символ-разделитель (например, запятую).
Сделаем это разными способами: формулами и с помощью Power Query.
Новая статья: Сборка таблиц с разными шапками из нескольких книг
Решить проблему можно весьма изящно - используя надстройку Power Query, которая умеет собирать таблицы даже с разной структурой:
Новая статья: Разделение таблицы по листам
Новая статья: Парсим нетабличные данные с сайта
Давайте разберемся с этой задачей на примере парсинга цен и описаний товаров с маркетплейса Wildberries:
Новая статья: Распределяем список по наборам
Имеем список объектов (например, товаров) с пометкой, к какому набору (корзине) каждый из них относится. Необходимо разложить объекты по своим наборам, сформировав таблицу как на рисунке справа:
Похожие задачи встречаются на практике весьма часто - в случаях, когда приходится распределять те или иные ресурсы:
- сотрудников по командам
- водителей по маршрутам
- клиентов по менеджерам
- товары по корзинам и т.д.
В прошлом я уже делал статью и видео о похожей задаче, где мы разбирали как переложить одномерный столбец с данными в двумерную таблицу, но там ситуация была проще, т.к. каждый набор (строка) имел одинаковый размер (число столбцов). Здесь же количество элементов в наборах заранее не известно и не равно друг другу, так что подход придётся изменить.
Чтобы у вас был выбор, давайте разберем несколько способов
Новая статья: Сводная по таблице с многострочной шапкой
Выручить здесь может надстройка Power Query, при помощи которой можно за несколько минут превратить эту жесть в нормализованную плоскую таблицу, по которой строить сводную можно уже легко:
Хороший пример, если вы осваиваете возможности Power Query - тут они раскрываются во всей красе.
Ну и, само-собой, видео для тех, кому лень читать
Новая статья: Массовая замена текста в Power Query функцией List.Accumulate
Использовать будем одну из не самых простых для понимания функций языка М - List.Accumulate:
Новая статья: Производственный календарь в Excel
Новая статья: Регулярные выражения (RegExp) в Power Query
Открытым, однако, остался вопрос - можно ли добавить поддержку регулярных выражений в Power Query? Справка и тех.поддержка Microsoft отвечают на этот вопрос сугубо отрицательно, но, на самом деле, есть способ обойти это ограничение...
Новая статья: Нечеткий текстовый поиск с Power Query
Давайте разберёмся, как этот инструмент работает, его плюсы, минусы и нюансы применения.
Обновление статьи: Список файлов в папке
Вышла моя книга по Power Query
330 страниц А4, качественная мелованная бумага, черно-белая печать.
Надеюсь, первая на русском языке книга о Power Query получилась достойной. Во всяком случае, я очень старался.
Несколько глав пришлось корректировать уже после написания пару раз, т.к. за последний год вышло очень много обновлений Excel, порой, весьма ощутимо меняющих интерфейс и возможности этой надстройки.
На данный момент идут переговоры с сетью книжных магазинов "Читай-Город", чтобы все мои книги в бумажном варианте появились на их полках и в интернет-магазине. Так что скоро, надеюсь, эти томики окажутся в зоне вашей досягаемости.
Новая статья: Дубликаты внутри ячеек
Хорошо видно, что некоторые имена в списках внутри ячеек повторяются. Давайте посмотрим, что можно с этим сделать, а именно:
- Как быстро найти все ячейки, содержащие повторы
- Как подсветить дубликаты цветом
- Как удалить дубликаты, оставив только неповторяющиеся значения
Новая статья: Тонкости работы с переносами строк (Alt+Enter) в Excel
Давайте-ка мы разберёмся в этом вопросе поподробнее. А именно:
- Научимся удалять нежелательные переносы заменой или формулами
- Научимся делить по Alt+Enter'ам на столбцы или строки (макросом или через Power Query)
Новая статья: Сборка данных со всех листов книги в одну таблицу
Давайте рассмотрим их подробно: