• Архив

    «   Сентябрь 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      

Новая статья: Система учета заказов на 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 | След.
Наверх