Да речеме дека водите неколку проекти со различни буџети и сакате да ги визуелизирате вашите трошоци за секој од нив. Тоа е, од оваа изворна табела:
.. добиј нешто како ова:
Со други зборови, треба да го раширите буџетот во текот на деновите на секој проект и да добиете поедноставена верзија на графиконот на проектот Гант. Да го направите ова со вашите раце е долго и досадно, макроата се тешки, но 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 добиваме список (Список) на датуми, кој може да се прошири во нови линии користејќи го копчето во заглавието на табелата:
… и добиваме:
Сега останува само да се склопи табелата, користејќи ги генерираните датуми како имиња за новите колони. Тимот е одговорен за ова. Колона за детали (Стортна колона) табот Конвертирај (Трансформирајте):
По кликнување на OK добиваме резултат многу близок до посакуваниот:
Null е, во овој случај, аналог на празна ќелија во Excel.
Останува да се отстранат непотребните колони и да се истовари добиената табела до оригиналните податоци со командата Затвори и вчитај – Затвори и вчитај во… (Затвори и вчитај - затвори и вчитај до…) табот Почетна (Дома):
Како резултат добиваме:
За поголема убавина, можете да го приспособите изгледот на добиените паметни табели на јазичето Изведувач (Дизајн): поставете стил на една боја, оневозможете ги копчињата за филтри, овозможете збирови итн. Дополнително, можете да изберете табела со датуми и да овозможите истакнување на броеви за неа користејќи условно форматирање на картичката Дома — Условно форматирање — Скали на бои (Дома - Условно форматирање - Скали на бои):
А најдобриот дел е што во иднина можете безбедно да ги уредувате старите или да додавате нови проекти на оригиналната табела, а потоа да ја ажурирате десната табела со датуми со десното копче на глувчето – и Power Query ќе ги повтори сите дејства што сме ги направиле автоматски .
Voila!
- Гант табела во Excel користејќи условно форматирање
- Календар за пресвртници на проектот
- Генерирање дупликат редови со Power Query