Диаграмма Ганта в 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, где, первым делом, нужно настроить числовые форматы для каждого столбца с помощью выпадающих списков в шапке таблицы:
Чтобы посчитать бюджет в день, нужно вычислить длительность каждого проекта. Для этого выделим (удерживая клавишу Ctrl) сначала столбец Финиш, а потом Старт и выберем команду Добавить столбец - Дата - Вычесть дни (Add Column - Date - Subtract days):
Полученные числа на 1 меньше, чем нужно, т.к. предполагается, что начинаем каждый проект мы в первый день утром, а заканчиваем в последний день вечером. Поэтому выделим полученный столбец и добавим к нему единицу с помощью команды Преобразовать - Стандартные - Добавить (Transform - Standard - Add):
Теперь добавим столбец, где вычислим бюджет в день. Для этого на вкладке Добавить столбец (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 повторит автоматически.
Вуаля!
Ссылки по теме
- Диаграмма Ганта в Excel с помощью условного форматирования
- Календарь этапов проекта
- Генерация дубликатов строк через Power Query
Николай, спасибо за науку!!!
Добавил этот вариант в файл-пример - скачайте еще разок и посмотрите, там несложно, ага?
Спасибо за статью! Как всегда, очень познавательно!
На мой взгляд было бы интересно еще иметь возможность сортировать исходную таблицу, например: по полю «Старт» или «Бюджет».
Но я так понимаю данное решение этого делать не позволяет т.е. при сортировке строки не соответствую диаграмме даже после обновления данных?
В примере по дням точное совпадение, Проект – Альфа, Бюджет 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 как исправить ошибку?
Expression.Error: Аргумент "increment" выходит за пределы диапазона.
Сведения:
1.00:00:00
Да сделать это можно. По ссылке, например, сделал один из возможных вариантов отчета с преобразованием ваших данных. Форматирование можете сделать свое.
Если у вас такая ошибка, то левой кнопкой мыши нажмите на отмеченный фрагмент заголовка столбца (показан на изображении ниже) и выберите нужный тип данных:
Общеизвестно что всегда приятнее когда не ты сам а тебя из ложечки кормят перожеваной кашкой )))