• Архив

    «   Март 2024   »
    Пн Вт Ср Чт Пт Сб Вс
            1 2 3
    4 5 6 7 8 9 10
    11 12 13 14 15 16 17
    18 19 20 21 22 23 24
    25 26 27 28 29 30 31
                 

Новая статья: Разделение многоуровневого списка по столбцам

Три способа (формулы, Power Query и макрос) чтобы разложить многоуровневый список с вложенной нумерацией по отдельным столбцам:


Читать статью полностью

Новая статья : Мультистраничный веб-запрос в Power Query

Мультистраничный веб-запрос в Power Query - это подход, который позволяет загрузить данные не с одной, а сразу с нескольких веб-страниц, объединив их в единую таблицу. Например, загрузить результаты чемпионатов по гольфу из википедии за несколько лет (где каждый год - отдельная страница). Чтобы провернуть такую штуку, нам придётся пройти следующие этапы:

  1. Создать сначала одиночный веб-запрос к любой странице из интересующего списка
  2. Создать параметр для года и внедрить его в запрос
  3. Преобразовать запрос в функцию (параметр станет её аргументом)
  4. Создать список интересующих нас лет (2010 - 2021) и вызвать созданную функцию, подставив ей список в качестве аргумента
  5. Объединить полученные результаты в единую таблицу за все годы


Читать статью полностью

Новая статья: РегистроНЕчувствительность в Power Query

Необходимость учитывать регистр (регистрочувствительность) - одно из первых заметных принципиальных отличий, с которыми сталкиваются те, кто начинают работать в Power Query. В отличие от Excel, который прописные и строчные буквы в подавляющем большинстве случаев не различает, Power Query в этом вопросе строг. При любых операциях с данными (фильтрации, сортировке, удалении дубликатов, в исходном М-коде запросов и т.д.) Query воспринимает большие и маленькие буквы как совершенно разные.

Конечно, рано или поздно, к этому привыкаешь и начинаешь относиться как к данности и учитывать в работе. Так, например, многие пользователи перед фильтрацией, чтобы она была регистроНечувствительной, сначала делают дубликат столбца, в котором затем преобразуют весь текст к одному регистру и только потом фильтруют. Вполне себе способ.

На самом деле, решить эту проблему можно гораздо изящнее, если использовать встроенную в языке М в Power Query функцию с громоздким названием Comparer.OrdinalIgnoreCase.



Читать статью полностью

Новая статья: Динамический "Топ-N и другие" в сводной таблице

В аналитических отчетах и дашбордах весьма часто встречается потребность вывести итоговые значения в формате сводной таблицы вида "Топ-N + другие":

Сводные таблицы Excel так, к сожалению, пока не умеют, но если доработать исходные данные с помощью запроса Power Query, то задача решается относительно легко.


Читать статью полностью

Новая статья: Горизонтальная фильтрация столбцов в Excel

Разбираем три способа реализовать горизонтальную фильтрацию столбцов, которая изначально отсутствует в Microsoft Excel: с помощью функции ФИЛЬТР, запроса Power Query + сводная таблица и макросом на VBA.



Читать статью полностью

Новая статья: Как правильно разворачивать таблицы в Power Query

Разбор типичных "граблей" - о том, как избежать проблем при разворачивании вложенных таблиц в Power Query,  если в исходных данных добавляются или удаляются столбцы.  

Читать статью полностью

Новая статья: Столбец из примеров - искусственный интеллект в Power Query

Одно из самых просматриваемых видео у меня на YouTube-канале - это видео про Мгновенное заполнение (Flash Fill) в Microsoft Excel. Суть этого инструмента в том, что если вам надо как-то преобразовать ваши исходные данные, то достаточно просто начать набирать в соседнем с ними столбце тот результат, который вы хотите получить. После нескольких вручную набранных ячеек (обычно хватает 2-3) Excel "поймёт" логику нужных вам преобразований и автоматически продолжит набранное, завершив всю монотонную работу за вас.

Квинтэссенция эффективности. Волшебная кнопка "сделать всё хорошо", которую мы все так любим, правда?

На самом деле, аналог подобного инструмента есть и в Power Query - там он называется Столбец из примеров (Column from Examples). По сути, это маленький встроенный в Power Query искусственный интеллект, который умеет быстро обучаться на ваших данных и затем их преобразовывать.  Давайте детально рассмотрим его возможности на нескольких практических сценариях, чтобы понять, где он может нам с вами пригодиться в реальных задачах.



Читать статью полностью

Новая статья: Сборка разноформатных таблиц

Подробный и пошаговый разбор весьма типовой задачи, когда в качестве входных данных имеем Excel-файл, где на одном из листов  расположены друг под другом несколько таблиц с данными по продажам  следующего вида:

 

А на выходе нам нужно собрать данные из всех таблиц в одну плоскую  нормированную таблицу, удобную для последующего анализа и построения  сводной, т.е. вот в такую:

 

Само собой, использовать для этого будем наиболее подходящий инструмент - Power Query:



Читать статью полностью

Новая статья: Нарастающие итоги в Excel


Что такое нарастающие итоги (Running Totals) и как вычислить в Microsoft  Excel разными способами:
  • формулами,
  • в сводной таблице,
  • в запросе Power  Query.


Читать статью полностью

Новая статья: Сохранение истории обновлений запросов Power Query

Почти на каждом тренинге по Power Query, когда мы доходим до способов  обновления созданных запросов и люди видят как новые данные заменяют при  обновлении старые, меня кто-нибудь из слушателей спрашивает: "а можно  сделать так, чтобы при обновлении старые данные тоже где-нибудь  сохранялись и было видно всю историю обновлений?"

Мысль не новая и стандартным ответом на неё будет "нет" - Power Query  по умолчанию настроен именно на замену старых данных новыми (что и  требуется в абсолютном большинстве случаев). Однако, если очень хочется,  то можно обойти это ограничение. Причем способ, как вы дальше увидите,  весьма простой.

Читать статью полностью

Новая статья: Система учета заказов на Google Calendar и Excel

Многие бизнес-процессы (и даже целые бизнесы) в этой жизни  предполагают выполнение заказов ограниченным количеством исполнителей к  заданным срокам. Планирование в таких случаях происходит, что  называется, "от календаря" и часто возникает потребность переноса  запланированных в нём событий (заказов, встреч, поставок) в 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 функцией List.Accumulate

Продолжая начатую в прошлой статье тему про массовую замену текста, разбираемся с решением этой задачи уже в Power Query.


Использовать будем одну из не самых простых для понимания функций языка М - List.Accumulate:


Читать статью полностью

Новая статья: Производственный календарь в Excel

Как при помощи Power Query сделать в Excel "вечный" производственный  календарь - автоматически обновляющийся список нерабочих дней за все  годы для использования в своих расчетах.


Читать статью полностью

Новая статья: Регулярные выражения (RegExp) в Power Query

Пару лет назад я уже делал большую статью с видео о том, как добавить в Microsoft Excel поддержку регулярных выражений (RegExp). Это здорово развязывает нам руки при анализе и парсинге текста и предоставляет в наше распоряжение мощный и гибкий функционал, рядом с которым стандартные текстовые функции Excel и рядом не стояли.
Открытым, однако, остался вопрос - можно ли добавить поддержку регулярных выражений в Power Query? Справка и тех.поддержка Microsoft отвечают на этот вопрос сугубо отрицательно, но, на самом деле, есть способ обойти это ограничение...



Читать статью полностью >>>

Новая статья: Нечеткий текстовый поиск с Power Query

Я когда-то уже писал подробный обзор на бесплатную надстройку Fuzzy Lookup от Microsoft, позволяющую находить соответствия двух списков при неточном совпадении данных. Недавно, с последними обновлениями Office 365, аналогичный функционал пришёл и в Power Query в Excel. До Power BI Desktop, кстати, он тоже добрался.
Давайте разберёмся, как этот инструмент работает, его плюсы, минусы и нюансы применения.


Читать статью полностью

Обновление статьи: Список файлов в папке

Ощутимо перелопатил и дополнил статью о том, как получить список файлов из заданной папки в Excel. Добавил самый универсальный, я считаю, метод через Power Query и записал уже традиционное видео с демонстрацией всех трёх способов - от скрытой функции до макроса и мегаудобного запроса в PQ с последующим подсчетом статистики в сводной таблице:

Вышла моя книга по Power Query

Теперь уже вполне официально можно сказать, что я стал "многодетным отцом" в книжном смысле - только что вышла моя третья книга "Скульптор данных в Excel с Power Query". Электронная версия уже доступна на сайте с прошлой недели, а сегодня до меня доехал первый бумажный тираж из типографии:



330 страниц А4, качественная мелованная бумага, черно-белая печать.

Надеюсь, первая на русском языке книга о Power Query получилась достойной. Во всяком случае, я очень старался.
Несколько глав пришлось корректировать уже после написания пару раз, т.к. за последний год вышло очень много обновлений Excel, порой, весьма ощутимо меняющих интерфейс и возможности этой надстройки.
На данный момент идут переговоры с сетью книжных магазинов "Читай-Город", чтобы все мои книги в бумажном варианте появились на их полках и в интернет-магазине. Так что скоро, надеюсь, эти томики окажутся в зоне вашей досягаемости.

Подробнее о книге
 

Новая статья: Дубликаты внутри ячеек

Про поиск и подсветку дубликатов в разных ячейках и диапазонах я уже не раз писал, но что делать если нужно найти и, возможно, удалить повторяющиеся слова внутри ячейки? Например, мы имеем вот такую таблицу с данными (разделителями могут быть не обязательно пробелы):

Хорошо видно, что некоторые имена в списках внутри ячеек повторяются. Давайте посмотрим, что можно с этим сделать, а именно:
  • Как быстро найти все ячейки, содержащие повторы
  • Как подсветить дубликаты цветом
  • Как удалить дубликаты, оставив только неповторяющиеся значения
Читать статью полностью

Новая статья: Тонкости работы с переносами строк (Alt+Enter) в Excel

Переносы строк внутри одной ячейки, добавляемые с помощью сочетания клавиш Alt+Enter - дело весьма частое и привычное. Иногда их делают сами пользователи, чтобы добавить красоты длинному тексту. Иногда такие переносы добавляются автоматически при выгрузке данных из каких-либо рабочих программ (привет 1С, SAP и т.д.) Проблема в том, что на такие таблицы приходится потом не просто любоваться, а с ними работать - и вот тогда эти невидимые символы переноса могут стать проблемой. А могут и не стать - если уметь правильно с ними обращаться.

Давайте-ка мы разберёмся в этом вопросе поподробнее.  А именно:
  • Научимся удалять нежелательные переносы заменой или формулами
  • Научимся делить по Alt+Enter'ам на столбцы или строки (макросом или через Power Query)


Читать статью полностью и смотреть видео

Новая статья: Сборка данных со всех листов книги в одну таблицу

Классика жанра - вопрос из серии "как мне собрать таблицы со всех листов моей книги на один?". Представьте, что у вас есть файл с больше, чем полусотней листов-отчетов по городам. Всё это счастье нужно собрать в одну таблицу для построения в будущем, допустим, сводной. Выручить могут два основных подхода: макросы и Power Query.

Давайте рассмотрим их подробно:


Читать статью полностью
Страницы: 1 | 2 | След.
Наверх