Допустим, вы ведете несколько проектов с разными бюджетами и хотите визуализировать затраты по каждому из них. То есть из этой исходной таблицы:
.. получите что-то вроде этого:
Другими словами, вам нужно распределить бюджет по дням каждого проекта и получить упрощенную версию диаграммы Ганта проекта. Делать это руками долго и скучно, макросы сложны, но Power Query for Excel в такой ситуации показывает свою мощь во всей красе.
Power Query — это надстройка от Microsoft, которая может импортировать данные в Excel практически из любого источника, а затем преобразовывать их различными способами. В Excel 2016 эта надстройка уже встроена по умолчанию, а для Excel 2010-2013 ее можно скачать с сайта Microsoft, а затем установить на свой ПК.
Для начала превратим нашу исходную таблицу в «умную» таблицу, выбрав команду Форматировать как таблицу таб Главная (Главная страница — Форматировать как таблицу) или нажав сочетание клавиш Ctrl+T :
Затем перейдите на вкладку Данные (если у вас Excel 2016) или на вкладке Power Query (если у вас Excel 2010-2013 и вы установили Power Query как отдельную надстройку) и нажмите кнопку «Из таблицы/диапазона». :
Наша умная таблица загружается в редактор запросов Power Query, где первым шагом является настройка числовых форматов для каждого столбца с помощью раскрывающихся списков в заголовке таблицы:
Для расчета бюджета на день необходимо рассчитать продолжительность каждого проекта. Для этого выберите (удерживайте клавишу Ctrl) столбец первый Завершить, а затем Start и выбери команду Добавить столбец – Дата – Вычесть дни. (Добавить столбец — Дата — Вычесть дни):
Полученные числа на 1 меньше, чем необходимо, поскольку мы должны начинать каждый проект в первый день утром и заканчивать вечером в последний день. Поэтому выделите полученный столбец и добавьте в него единицу с помощью команды Трансформировать – Стандарт – Добавить (Преобразовать — Стандарт — Добавить):
Теперь добавим столбец, в котором будем рассчитывать бюджет на день. Для этого на вкладке Добавить столбец я не играю Пользовательский столбец (Пользовательский столбец) и в появившемся окне введите имя нового поля и формулу расчета, используя названия столбцов из списка:
Теперь самый тонкий момент — создаем еще один вычисляемый столбец со списком дат от начала до конца с шагом в 1 день. Для этого снова нажмите кнопку Пользовательский столбец (Пользовательский столбец) и использовать встроенный язык Power Query M, который называется Список.Даты:
Эта функция имеет три аргумента:
- дата начала – в нашем случае она берется из столбца Start
- количество генерируемых дат — в нашем случае это количество дней для каждого проекта, которое мы посчитали ранее в столбце вычитание
- шаг по времени – задан проектом #длительность(1,0,0,0), что на языке М означает – один день, ноль часов, ноль минут, ноль секунд.
После нажатия на OK получаем список (List) дат, который можно развернуть на новые строки с помощью кнопки в шапке таблицы:
… и мы получаем:
Теперь все, что осталось, — это свернуть таблицу, используя сгенерированные даты в качестве имен для новых столбцов. За это отвечает команда. Подробный столбец (Сводная колонка) таб Конвертировать (Преобразование):
После нажатия на OK получаем результат очень близкий к желаемому:
Null в данном случае является аналогом пустой ячейки в Excel.
Осталось удалить ненужные столбцы и выгрузить полученную таблицу рядом с исходными данными командой Закрыть и загрузить – Закрыть и загрузить… (Закрыть и загрузить — Закрыть и загрузить в…) таб Главная (Главная):
В результате получаем:
Для пущей красоты можно настроить внешний вид получаемых смарт-таблиц на вкладке Конструктор (Дизайн): установить единый цветовой стиль, отключить кнопки фильтра, включить итоги и т. д. Дополнительно вы можете выбрать таблицу с датами и включить для нее подсветку чисел с помощью условного форматирования на вкладке Главная — Условное форматирование — Цветовые шкалы (Главная страница — Условное форматирование — Цветовые шкалы):
А самое приятное то, что в дальнейшем вы сможете спокойно редактировать старые или добавлять новые проекты в исходную таблицу, а затем правой кнопкой мыши обновлять нужную таблицу с датами — и Power Query повторит все действия, которые мы сделали автоматически. .
Вуаля!
- Диаграмма Ганта в Excel с использованием условного форматирования
- Календарь этапов проекта
- Создание повторяющихся строк с помощью Power Query