Диаграмма Ганта в Power Query

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

Исходная таблица

.. получить что-то похожее на такую:

Результат

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

Power Query - это надстройка от компании Microsoft, которая умеет импортировать в Excel данные практически из любых источников и трансформировать их потом кучей разных способов. В Excel 2016 эта надстройка уже встроена по умолчанию, а для Excel 2010-2013 ее можно скачать с сайта Microsoft и установить затем на ваш ПК.

Сначала превратим нашу исходную таблицу в "умную", выбрав команду Форматировать как таблицу на вкладке Главная (Home - Format as Table) или нажав сочетание клавиш Ctrl+T :

Умная таблица

Затем переходим на вкладку Данные (если у вас Excel 2016) или на вкладку Power Query (если у вас Excel 2010-2013 и вы установили Power Query как отдельную надстройку) и жмем кнопку Из таблицы / диапазона (From Table/Range). :

Загружаем данные в Power Query

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

Настраиваем форматы данных

Чтобы посчитать бюджет в день, нужно вычислить длительность каждого проекта. Для этого выделим (удерживая клавишу Ctrl) сначала столбец Финиш, а потом Старт и выберем команду Добавить столбец - Дата - Вычесть дни (Add Column - Date - Subtract days):

Считаем разницу в днях

Полученные числа на 1 меньше, чем нужно, т.к. предполагается, что начинаем каждый проект мы в первый день утром, а заканчиваем в последний день вечером. Поэтому выделим полученный столбец и добавим к нему единицу с помощью команды Преобразовать - Стандартные - Добавить (Transform - Standard - Add):

Добавляем 1

Теперь добавим столбец, где вычислим бюджет в день. Для этого на вкладке Добавить столбец (Add Column) нажмем Настраиваемый столбец (Custom Column) и в появившееся окно введем имя нового поля и формулу расчета, используя имена столбцов из списка:

Вычисляем бюджет в день

Теперь самый тонкий момент - создаем еще один вычисляемый столбец со списком дат от старта до финиша с шагом 1 день. Для этого опять жмем кнопку Настраиваемый столбец (Custom Column) и используем функцию встроенного в Power Query языка М, которая называется List.Dates:

Генерируем даты от старта до финиша

У этой функции три аргумента:

  • начальная дата - в нашем случае берется из столбца Старт
  • количество дат, которые надо сгенерировать - в нашем случае это число дней по каждому проекту, которое мы посчитали ранее в столбце Вычитание
  • временной шаг - задается конструкцией #duration(1,0,0,0), означающей на языке М - один день, ноль часов, ноль минут, ноль секунд.

После нажатия на ОК получим список (List) дат, который можно развернуть в новые строки, используя кнопку в шапке таблицы:

Разворачиваем список дат

... и получаем:

Сгенерированные даты

Теперь осталось свернуть таблицу, используя сгенерированные даты как названия для новых столбцов. За это отвечает команда Столбец сведЕния (Pivot Column) на вкладке Преобразовать (Transform):

Сворачиваем даты

После нажатия на ОК получаем результат очень близкий к желаемому:

Почти готово

Null - это, в данном случае, аналог пустой ячейки в Excel.

Осталось удалить ненужные столбцы и выгрузить полученную таблицу рядом с исходными данными командой Закрыть и загрузить - Закрыть и загрузить в... (Close & Load - Close & Load to...) на вкладке Главная (Home):

Выгружаем результаты на лист

Получаем в итоге:

Результат

Для пущей красоты можно настроить вид полученных умных таблиц на вкладке Конструктор (Design): задать единый цветовой стиль, отключить кнопки фильтра, включить итоги и т.д. Дополнительно можно выделить таблицу с датами и включить для нее подсветку чисел условным форматированием на вкладке Главная - Условное форматирование - Цветовые шкалы (Home - Conditional Formatting - Color Scales):

Готовая таблица

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

Вуаля!

Ссылки по теме




13.03.2018 08:07:04
Шикарно и как всегда так изящно. Это надстройка не перестает удивлять своими возможностями. Спасибо Николай за труды. Успехов!!
13.03.2018 13:20:26
А ведь таким макаром можно и график отпусков, и т.п. строить. Спасибо, Николай.
13.03.2018 22:58:19
Не просто можно, а НУЖНО - собрал на PQ вместо формул - файл ЛЕТАЕТ!

Николай, спасибо за науку!!!
13.03.2018 14:39:27
Спасибо, как раз то что актуально. Ваша рука всегда на пульсе.  
14.03.2018 08:27:11
Николай, до развертывания статьи там показывает что комментариев 0, хотя комментарии уже добавлены. Наверное это БАГ какой-то. Просто на заметку сказал.
16.03.2018 11:35:29
Здравствуйте.Как сделать по месяцам?
17.03.2018 01:09:38
Тоже интересует. Особенно, чтобы получились столбцы вида "02/2018"; "03/2018" ...
17.03.2018 13:16:51
Нужно будет добавить столбец с номером месяца и потом сделать группировку по проектам-месяцам.
Добавил этот вариант в файл-пример - скачайте еще разок и посмотрите, там несложно, ага?
17.03.2018 23:02:28
Спасибо. Когда знаешь, всё просто ))))
21.03.2018 10:33:13
Спасибо Вам Николай! А возможно ещё эти месяца разбить по числам: Январь2018 -/1-7;8-14;15-21;22-31; Февраль 2018 1-7;8-14;15-21;22-28.
Январь
1-78-1415-2122-31
2руб
5 руб2руб
7руб
17.03.2018 16:59:00
Николай,

Спасибо за статью! Как всегда, очень познавательно!

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

Но я так понимаю данное решение этого делать не позволяет т.е. при сортировке строки не соответствую диаграмме даже после обновления данных?
20.03.2018 11:08:38
После импорта таблицы в PQ можно добавить столбец с индексом и после применения пивота отсортировать результ ат по этому индексу (ascending). Ещё можно применить сортировку по новому столбцу с датами (также ascending) сразу после разворачивания списков.
30.03.2018 19:19:38
Это еще один шедевр! в коллекцию!! Спасибо!!
31.05.2018 20:40:14
Отличное и оригинальное решение! Браво!
Наверх