Припустімо, що ви запускаєте кілька проектів з різними бюджетами і хочете візуалізувати свої витрати для кожного з них. Тобто з цієї вихідної таблиці:
.. отримати щось на зразок цього:
Іншими словами, вам потрібно розподілити бюджет на дні кожного проекту та отримати спрощену версію діаграми Ганта проекту. Робити це своїми руками довго і нудно, макроси складні, але Power Query для Excel в такій ситуації показує свою міць у всій красі.
PowerQuery це надбудова від Microsoft, яка може імпортувати дані в Excel практично з будь-якого джерела, а потім трансформувати їх різними способами. У Excel 2016 ця надбудова вже вбудована за замовчуванням, а для Excel 2010-2013 її можна завантажити з веб-сайту Microsoft, а потім встановити на свій ПК.
По-перше, давайте перетворимо нашу оригінальну таблицю на «розумну» таблицю, вибравши команду Відформатувати як таблицю таб Головна (Домашня сторінка — форматувати як таблицю) або натиснувши комбінацію клавіш Ctrl+T :
Потім перейдіть на вкладку дані (якщо у вас Excel 2016) або на вкладці PowerQuery (якщо у вас 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