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