Диаграмма Ганта в 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
Отличное и оригинальное решение! Браво!
05.07.2019 13:36:10
Всем привет
В примере по дням точное совпадение, Проект – Альфа, Бюджет 7500 и итог по дням с 01-01-18 по 05-01-18 тоже 7500, а вот по месяцам, Проект – Альфа, Бюджет 7500 а итог по месяцам  с 01-2018 по 09-2018 уже 7499.52 где теряется 0.48?  Проект – Бета, Бюджет 10000 и итог по дням с 05-01-18 по 08-01-18 тоже 10000, а вот по месяцам, Проект – Бета, Бюджет 10000 а итог по месяцам  с 05-2018 по 10-2018 уже 10000.65  как исправить ошибку?
01.09.2019 01:25:36
Подскажите чем может быть вызвана ошибка


Expression.Error: Аргумент "increment" выходит за пределы диапазона.
Сведения:
   1.00:00:00
29.02.2020 19:02:33
здравствуйте Николай! все круто конечно мне очень понравилось, как можно сделать диаграмму в одной таблице при этом в разрезе План/факт . спасибо.
№ п\пОперации ПланФактбюджет
Начало датыКонец датыНачало датыКонец даты
1Создание заявок01.01.2019 05.01.2019 05.01.2019 10.01.2019 600
2ПТК06.01.2019 05.02.2019 11.01.2019 10.02.2019 1000
3договор15.02.2019 06.04.2019 20.02.2019 11.04.2019 1000
4платежи06.05.2019 05.07.2019 11.05.2019 10.07.2019 1000
5освоение20.04.2019 18.08.2019 25.04.2019 23.08.2019 1000
6ввод в эксплуатацию01.09.2019 01.10.2019 06.09.2019 06.10.2019 1000
7предоставление отчета05.10.2019 19.10.2019 10.10.2019 24.10.2019 1000
11.03.2020 15:26:49
Николай, спасибо большое за классную диаграмму, но вот проблема: в Power Query почему-то даты перескакивают, в смысле по непонятным причинам, скажем, после 20.03.20 идет 25.04.20, а этот "вырезанный" кусок идет в самом конце диаграммы. почему такое происходит? фильтрация не помогает. Спасибо!!!!
Наверх