• Архив

    «   Июнь 2021   »
    Пн Вт Ср Чт Пт Сб Вс
      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        

  • Поиск

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

Павлов Николай
16.04.202111:4916 апреля 2021 11:49
Как разобрать данные из одной таблицы сразу на несколько листов по  заданному критерию.Разбираем два способа - с обновлением (через Power  Query) и без (только VBA).

Читать статью полностью
Павлов Николай
16.04.202111:4916 апреля 2021 11:49
Просмотров:309 Комментариев:0

Новая статья: Парсим нетабличные данные с сайта

Павлов Николай
19.01.202120:2319 января 2021 20:23
Сплошь и рядом встречаются сайты, где при попытке импортировать с них данные в Excel Power Query просто в упор не видит там таблиц с нужной нам информацией. Причин для этого может быть несколько, но  чаще всего это происходит потому, что веб-дизайнер при создании таблицы  использовал в HTML-коде страницы не стандартную конструкцию с тегом <TABLE>, а её аналог - вложенные друг в друга теги-контейнеры <DIV>.  Это весьма распространённая техника при вёрстке веб-сайтов, но, к  сожалению, Power Query пока не умеет распознавать такую разметку и  загружать такие данные в Excel.   Тем не менее, есть способ обойти это ограничение ;)

Давайте разберемся с этой задачей на примере парсинга цен и описаний товаров с маркетплейса Wildberries:




Читать статью полностью
Павлов Николай
19.01.202120:2319 января 2021 20:23
Просмотров:5695 Комментариев:0

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

Павлов Николай
19.12.202012:0719 декабря 2020 12:07

Имеем список объектов (например, товаров) с пометкой, к какому набору  (корзине) каждый из них относится. Необходимо разложить объекты по своим  наборам, сформировав таблицу как на рисунке справа:

 

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

  • сотрудников по командам
  • водителей по маршрутам
  • клиентов по менеджерам
  • товары по корзинам и т.д.

В прошлом я уже делал статью и видео о похожей задаче, где мы  разбирали как переложить одномерный столбец с данными в двумерную  таблицу, но там ситуация была проще, т.к. каждый набор (строка) имел  одинаковый размер (число столбцов). Здесь же количество элементов в  наборах заранее не известно и не равно друг другу, так что подход  придётся изменить.

Чтобы у вас был выбор, давайте разберем несколько способов решения этой задачи.

Павлов Николай
19.12.202012:0719 декабря 2020 12:07
Просмотров:1734 Комментариев:0

Новая статья: Сводная по таблице с многострочной шапкой

Павлов Николай
08.11.202012:078 ноября 2020 12:07
Одна из самых распространенных проблем (и частых вопросов на тренингах) в том, как построить сводную таблицу, если в качестве исходных данных тебе досталась вот такая "красота":



Выручить здесь может надстройка Power Query, при помощи которой можно за несколько минут превратить эту жесть в нормализованную плоскую таблицу, по которой строить сводную можно уже легко:



Хороший пример, если вы осваиваете возможности Power Query - тут они раскрываются во всей красе.
Ну и, само-собой, видео для тех, кому лень читать :)

Павлов Николай
08.11.202012:078 ноября 2020 12:07
Просмотров:965 Комментариев:2

Новая статья: Массовая замена текста в Power Query функцией List.Accumulate

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


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


Читать статью полностью
Павлов Николай
03.09.202017:023 сентября 2020 17:02
Просмотров:1607 Комментариев:0

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

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


Читать статью полностью
Павлов Николай
30.07.202019:3130 июля 2020 19:31
Просмотров:2050 Комментариев:0

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

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



Читать статью полностью >>>
Павлов Николай
17.07.202017:1117 июля 2020 17:11
Просмотров:2471 Комментариев:2

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

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


Читать статью полностью
Павлов Николай
27.03.202023:4027 марта 2020 23:40
Просмотров:2444 Комментариев:1

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

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

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

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



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

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

Подробнее о книге
 
Павлов Николай
11.08.201919:1411 августа 2019 19:14
Просмотров:5096 Комментариев:14

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

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

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

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

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

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


Читать статью полностью и смотреть видео
Павлов Николай
14.04.201923:1614 апреля 2019 23:16
Просмотров:3288 Комментариев:0

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

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

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


Читать статью полностью
Павлов Николай
07.04.201907:487 апреля 2019 7:48
Просмотров:7504 Комментариев:6

Новая статья: Генератор фраз из заданных фрагментов

Павлов Николай
21.02.201916:0821 февраля 2019 16:08
Недавно ко мне обратился один знакомый с просьбой помочь с генерацией всех возможных фраз, состоящих из набора заданных слов. Подобного рода задачи могут возникать при составлении списков ключевых слов и фраз для интернет-рекламы и SEO-продвижения, когда нужно перебрать все возможные варианты перестановок слов в поисковом запросе:


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

Читать статью полностью
Павлов Николай
21.02.201916:0821 февраля 2019 16:08
Просмотров:2771 Комментариев:0

Новая статья: Параметризация путей к данным в Power Query

Павлов Николай
10.12.201811:0410 декабря 2018 11:04
Если вы уже начали использовать в работе инструменты бесплатной надстройки Power Query в Microsoft Excel, то очень скоро столкнётесь с одной узкоспециальной, но весьма частой и надоедливой проблемой, связанной с постоянно ломающимися ссылками на исходные данные. Суть проблемы в том, что если в своём запросе вы ссылаетесь на внешние файлы или папки, то Power Query жёстко прописывает абсолютный путь к ним в тексте запроса.

У вас на компьютере всё работает прекрасно, но если вы решите отправить файл с запросом своим коллегам, то их ждёт разочарование, т.к. у них на компьютере путь к исходным данным уже другой, и наш запрос работать не будет.
Что же сделать в такой ситуации? Давайте разберемся.


Читать статью полностью
Павлов Николай
10.12.201811:0410 декабря 2018 11:04
Просмотров:2993 Комментариев:1

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

Павлов Николай
05.08.201822:445 августа 2018 22:44
Сильно обновил (по факту, практически полностью переписал статью) про 3 способа создания сводной таблицы на основе нескольких диапазонов данных. Стандартными средствами такое Excel не умеет, но, при желании, все-таки можно реализовать с помощью Power Query, макросов или Мастера Сводных таблиц из старых версий Excel:

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

P.S. Забавно, как недоступные или трудоемкие задачи, которые пару лет назад можно было решить только хардкорным программированием на VBA, сейчас легко и изящно решаются с помощью надстройки Power Query. Будущее уже здесь, да :)
Павлов Николай
05.08.201822:445 августа 2018 22:44
Просмотров:3022 Комментариев:0

Новая статья: Что такое Power Query / Pivot / Map / View / BI

Павлов Николай
22.07.201813:0922 июля 2018 13:09
Термины "Power Query", "Power Pivot", "Power BI" и прочие "пауэры" все чаще всплывают в статьях и материалах о Microsoft Excel. По моему опыту, далеко не все ясно представляют себе что скрывается за этими понятиями, как они между собой взаимосвязаны и как могут помочь простому пользователю Excel. Давайте разберемся.


Читать статью полностью
Павлов Николай
22.07.201813:0922 июля 2018 13:09
Просмотров:3973 Комментариев:2

Новая статья: Импорт плавающего фрагмента через Power Query в Excel

Павлов Николай
05.07.201810:495 июля 2018 10:49
Иногда бывают ситуации, когда заранее неизвестно сколько именно и каких строк нужно импортировать из исходных данных. Допустим, мы должны загрузить в Power Query данные из текстового файла, что, на первый взгляд, не представляет большой проблемы. Сложность в том, что файл регулярно обновляется, и завтра в нем может быть другое количество строк с данными, шапка из трех, а не двух строк и т.д.:




То есть мы заранее не можем с определенностью сказать, начиная с какой строки и сколько именно строк нужно импортировать. А это проблема, т.к. эти параметры жестко прописываются в М-коде запроса. И если сделать запрос по первому файлу (импорт 5 строк начиная с 4-ой), то он уже не будет правильно работать со вторым.
Было бы здорово, если бы наш запрос мог сам определять начало и конец «плавающего» текстового блока для импорта.

Читать статью полностью
Павлов Николай
05.07.201810:495 июля 2018 10:49
Просмотров:2030 Комментариев:0

Новая статья: Импорт данных из PDF в Excel

Павлов Николай
01.06.201816:381 июня 2018 16:38
Если вы столкнулись с задачей переноса данных из файла PDF в Microsoft Excel, то у вас есть несколько вариантов. Если вы счастливый обладатель недешевого FineReader, то все проще. А если нет? На самом деле, вполне можно выкрутиться с помощью связки Word + Power Query:


Читать статью полностью
Павлов Николай
01.06.201816:381 июня 2018 16:38
Просмотров:3106 Комментариев:1

Новая статья: Последнее слово

Павлов Николай
29.04.201815:4929 апреля 2018 15:49
 Среди задач работы с текстом, которые мы неоднократно уже разбирали, немного особняком стоит вопрос извлечения из текста последнего слова или фрагмента по заданному разделителю. Вытащить первое слово - не проблема, а вот с последним все не так легко и очевидно, т.к. количество слов в каждой ячейке может различаться. Давайте разберем несколько принципиально разных способов это сделать, а именно:
  • формулы
  • макросы
  • обновляемый запрос через Power Query



Читать статью полностью
Павлов Николай
29.04.201815:4929 апреля 2018 15:49
Просмотров:2073 Комментариев:0

Новая статья: Диаграмма Ганта в Power Query

Павлов Николай
12.03.201818:4512 марта 2018 18:45
Эта статья родилась после вопроса одного из моих слушателей с тренинга "Скульптор Данных в Microsoft Excel с Power Query". Вопрос был о том, как из вот такой таблицы по проектам:


... получить вот такую:


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

Читать статью полностью
Павлов Николай
12.03.201818:4512 марта 2018 18:45
Просмотров:3365 Комментариев:0

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

Павлов Николай
04.12.201719:184 декабря 2017 19:18
Есть много способов сравнить две таблицы в Microsoft Excel и на эту тему я уже неоднократно писал статьи и делал видеоуроки. Теперь же захотелось "подвести черту" и подробно описать три самых любимых мной способа сравнения:
  • функцией ВПР (VLOOKUP) - классика жанра
  • с помощью сводной таблицы - хороший вариант для больших таблиц
  • с помощью надстройки Power Query - самый красивый способ, да еще и с автоматическим обновлением
Разбор вариантов будем делать на двух версиях прайс-листа - старой и новой. Задача: быстро найти отличия, т.е. новые и удаленные товары и товары, у которых изменилась цена:



Читать статью полностью
Павлов Николай
04.12.201719:184 декабря 2017 19:18
Просмотров:4950 Комментариев:2

Новая статья: Трансформация столбца в таблицу

Павлов Николай
08.11.201715:408 ноября 2017 15:40
Иногда при выгрузке из каких-либо корпоративных программ, данные достаются нам в не очень удобном виде. Один из самых неприятных вариантов - это вся информация в одном длинном столбце, из которого придется делать нормальную двумерную таблицу:


Давайте разберем несколько способов сделать это быстро и красиво, а именно:
  • формулами и, в частности, функцией ДВССЫЛ (INDIRECT)
  • через Power Query
  • макросом из надстройки PLEX
Читать статью полностью
Павлов Николай
08.11.201715:408 ноября 2017 15:40
Просмотров:3998 Комментариев:0

Новая статья: Генерация дубликатов строк

Павлов Николай
31.08.201711:4531 августа 2017 11:45
В большинстве случаев повторы в наших данных нежелательны и мы с вами стараемся от них избавиться разными способами. Но иногда случается, что дубликаты нужны и полезны, и более того - нам необходимо их создавать!


Сделать это можно двумя основными способами: при помощи макросов и через Power Query.

Читать статью полностью
Павлов Николай
31.08.201711:4531 августа 2017 11:45
Просмотров:3603 Комментариев:0

Новая статья: Поиск совпадений в двух списках

Павлов Николай
25.07.201700:3325 июля 2017 0:33
Тема сравнения двух списков поднималась уже неоднократно и с разных сторон, но остается одной из самых актуальных везде и всегда. Давайте рассмотрим один из ее аспектов - подсчет количества и вывод совпадающих значений в двух списках. Подробно разберем три основных подхода для решения этой распространенной задачи:
  • формулы
  • макросы
  • Power Query


Читать статью полностью
Павлов Николай
25.07.201700:3325 июля 2017 0:33
Просмотров:4640 Комментариев:0

Новое видео: Сводная таблица с текстом в значениях в Power Query

Павлов Николай
15.07.201715:3915 июля 2017 15:39
По мотивам одноименной статьи записал видео решения задачи по созданию сводной с текстом через PowerQuery:



Читать статью целиком
Павлов Николай
15.07.201715:3915 июля 2017 15:39
Просмотров:4150 Комментариев:1

Новая статья: Слияние двух списков без дубликатов

Павлов Николай
17.06.201709:4317 июня 2017 9:43
Классическая ситуация: у вас есть два списка, которые надо слить в один. Причем в исходных списках могут быть как уникальные элементы, так и совпадающие (и между списками и внутри), но на выходе нужно получить список без дубликатов (повторений):



Давайте подробно разберем три принципиально разных способа:
  • удаление дубликатов
  • сводная таблица
  • формулу массива
  • через Power Query
Читать статью полностью
Павлов Николай
17.06.201709:4317 июня 2017 9:43
Просмотров:4666 Комментариев:0

Новая статья: Сборка таблиц из разных файлов с помощью Power Query

Павлов Николай
05.02.201723:295 февраля 2017 23:29
Что делать, если нужно быстро собрать данные из большого количества файлов Excel в одну таблицу? Вариантов тут, обычно, несколько:
  • тупо копипастить вручную (с кучей ошибок в процессе)
  • писать макрос для сбора данных или заказывать его знакомому VBA-программисту
  • делегировать задачу подчиненным :)
На самом деле, начиная с Excel 2013 есть еще один путь - сильно проще и на порядок удобнее - надстройка Power Query. Для Excel 2013 ее можно совершенно бесплатно скачать и подключить, а в Excel 2016 она уже входит по-умолчанию. Написал подробную статью о том, как использовать ее в подобной ситуации. Основные шаги тут следующие:
  1. импорт одного файла для примера
  2. преобразование процедуры импорта и "причесывания" данных в функцию
  3. импорт всех файлов из папки
  4. добавление созданной пользовательской функции для загрузки данных из каждой книги
Быстро и изящно.



Если у вас пока нет Excel 2013-2016 - все равно гляньте, чтобы оценить красоту решения :)

Читать статью полностью
Павлов Николай
05.02.201723:295 февраля 2017 23:29
Просмотров:9661 Комментариев:7
Наверх