Recimo da vodite nekoliko projekata s različitim proračunima i želite vizualizirati svoje troškove za svaki od njih. To jest, iz ove izvorne tablice:
.. dobiti nešto poput ovoga:
Drugim riječima, trebate rasporediti proračun na dane svakog projekta i dobiti pojednostavljenu verziju gantograma projekta. Raditi to rukama je dugo i dosadno, makronaredbe su teške, ali Power Query za Excel u takvoj situaciji pokazuje svoju moć u punom sjaju.
Upit snage je Microsoftov dodatak koji može uvesti podatke u Excel iz gotovo bilo kojeg izvora i zatim ih transformirati na hrpu različitih načina. U Excelu 2016 ovaj je dodatak već ugrađen prema zadanim postavkama, a za Excel 2010-2013 može se preuzeti s web stranice Microsofta i potom instalirati na vaše računalo.
Prvo, pretvorimo našu originalnu tablicu u “pametnu” tablicu odabirom naredbe Formatiraj kao tablicu kartica Naslovna (Početna — Formatiraj kao tablicu) ili pritiskom na tipkovni prečac Ctrl+T :
Zatim idite na karticu Datum (ako imate Excel 2016) ili na kartici Upit snage (ako imate Excel 2010-2013 i instalirali ste Power Query kao zaseban dodatak) i kliknite gumb Iz tablice / raspona. :
Naša pametna tablica učitava se u uređivač upita Power Query, gdje je prvi korak postavljanje formata brojeva za svaki stupac pomoću padajućih izbornika u zaglavlju tablice:
Da biste izračunali proračun po danu, morate izračunati trajanje svakog projekta. Da biste to učinili, odaberite (držite tipku Ctrl) prvi stupac završiti, A zatim početni plan i odaberite tim Dodaj stupac – Datum – Oduzmi dane (Dodaj stupac — Datum — Oduzmi dane):
Dobiveni brojevi su za 1 manji od potrebnog, jer svaki projekt trebamo započeti prvog dana ujutro i završiti zadnjeg dana navečer. Stoga odaberite dobiveni stupac i dodajte mu jedinicu pomoću naredbe Transformiraj – Standardno – Dodaj (Transformacija — Standardno — Dodaj):
Dodajmo sada stupac u kojem izračunavamo budžet po danu. Da biste to učinili, na kartici Dodaj stupac ne igram se Prilagođeni stupac (Prilagođeni stupac) te u prozor koji se pojavi unesite naziv novog polja i formulu za izračun koristeći nazive stupaca s popisa:
Sada najsuptilniji trenutak - stvaramo još jedan izračunati stupac s popisom datuma od početka do kraja s korakom od 1 dana. Da biste to učinili, ponovno pritisnite gumb Prilagođeni stupac (Prilagođeni stupac) te koristiti ugrađeni Power Query jezik M koji se zove Popis.Datumi:
Ova funkcija ima tri argumenta:
- datum početka – u našem slučaju se uzima iz stupca početni plan
- broj datuma koje treba generirati – u našem slučaju, ovo je broj dana za svaki projekt, koji smo izbrojali ranije u stupcu Oduzimanje
- vremenski korak – postavljen dizajnom #trajanje(1,0,0,0), što znači na jeziku M – jedan dan, nula sati, nula minuta, nula sekundi.
Nakon što kliknete OK dobivamo popis (List) datuma, koji se može proširiti u nove retke pomoću gumba u zaglavlju tablice:
... i dobivamo:
Sada sve što je preostalo je sažeti tablicu, koristeći generirane datume kao nazive za nove stupce. Za to je zaslužan tim. Stupac detalja (zaokretni stupac) kartica pretvoriti (Transformirati):
Nakon što kliknete OK dobivamo rezultat vrlo blizak željenom:
Null je, u ovom slučaju, analog prazne ćelije u Excelu.
Ostaje ukloniti nepotrebne stupce i naredbom istovariti dobivenu tablicu pored izvornih podataka Zatvori i učitaj – Zatvori i učitaj u… (Zatvori i učitaj — Zatvori i učitaj u...) kartica Naslovna (Dom):
Dobivamo kao rezultat:
Za veću ljepotu, možete prilagoditi izgled dobivenih pametnih tablica na kartici Konstruktor (Oblikovati): postavite stil jedne boje, onemogućite gumbe za filtriranje, omogućite ukupne iznose itd. Dodatno, možete odabrati tablicu s datumima i omogućiti označavanje brojeva za nju pomoću uvjetnog oblikovanja na kartici Početna — Uvjetno oblikovanje — Ljestvice boja (Početna — Uvjetno oblikovanje — Ljestvice boja):
A najbolji dio je što ubuduće možete sigurno uređivati stare ili dodavati nove projekte u izvornu tablicu, a zatim ažurirati desnu tablicu s datumima desnom tipkom miša – i Power Query će ponoviti sve radnje koje smo napravili automatski .
Evo ga!
- Gantogram u Excelu korištenjem uvjetnog oblikovanja
- Kalendar prekretnica projekta
- Generiranje duplikata redaka pomoću Power Queryja