Recimo da vodite nekoliko projekata s različitim budžetima i želite vizualizirati svoje troškove za svaki od njih. Odnosno, iz ove izvorne tabele:
.. nabavi ovako nešto:
Drugim riječima, trebate rasporediti budžet na dane svakog projekta i dobiti pojednostavljenu verziju projektnog Ganttograma. Raditi ovo rukama je dugo i dosadno, makroi su teški, ali Power Query za Excel u takvoj situaciji pokazuje svoju moć u svoj svojoj slavi.
power query je Microsoftov dodatak koji može uvesti podatke u Excel iz gotovo bilo kojeg izvora, a zatim ih transformirati na hrpu različitih načina. U programu Excel 2016, ovaj dodatak je već ugrađen prema zadanim postavkama, a za Excel 2010-2013 može se preuzeti sa Microsoft web stranice i zatim instalirati na vaš PC.
Prvo, pretvorimo našu originalnu tabelu u „pametnu“ tabelu odabirom naredbe Formatirajte kao tabelu tabulator Početna (Početna — Format kao tabela) ili pritiskom na prečicu na tastaturi Ctrl+T :
Zatim idite na karticu podaci (ako imate Excel 2016) ili na kartici power query (ako imate Excel 2010-2013 i instalirali ste Power Query kao poseban dodatak) i kliknite na dugme Iz tabele / opsega. :
Naša pametna tabela se učitava u uređivač upita Power Query, gde je prvi korak postavljanje formata brojeva za svaku kolonu pomoću padajućih menija u zaglavlju tabele:
Da biste izračunali dnevni budžet, potrebno je izračunati trajanje svakog projekta. Da biste to učinili, odaberite (držite pritisnutu tipku Ctrl) stupac prvi završiti, i onda start i izaberite tim Dodaj kolonu – Datum – Oduzmi dane (Dodaj kolonu — Datum — Oduzmi dane):
Dobijeni brojevi su za 1 manji nego što je potrebno, jer svaki projekat treba da počnemo prvog dana ujutro i završimo poslednjeg dana uveče. Stoga, odaberite rezultirajuću kolonu i dodajte joj jedinicu pomoću naredbe Transformacija – Standardno – Dodaj (Transformacija — Standardno — Dodaj):
Sada dodajmo kolonu u kojoj izračunavamo dnevni budžet. Da biste to učinili, na kartici Dodaj kolonu Ja ne igram Prilagođena kolona (Prilagođena kolona) i u prozoru koji se pojavi unesite naziv novog polja i formulu izračuna, koristeći nazive kolona sa liste:
Sada najsuptilniji trenutak – kreiramo još jednu izračunatu kolonu sa listom datuma od početka do kraja sa korakom od 1 dana. Da biste to uradili, ponovo pritisnite dugme Prilagođena kolona (Prilagođena kolona) i koristite ugrađeni Power Query jezik M, koji se zove List.Dates:
Ova funkcija ima tri argumenta:
- datum početka – u našem slučaju se uzima iz kolone start
- broj datuma za generiranje – u našem slučaju, to je broj dana za svaki projekat, koji smo ranije brojali u koloni Oduzimanje
- vremenski korak – postavljen dizajnom #duration(1,0,0,0), što znači na jeziku M – jedan dan, nula sati, nula minuta, nula sekundi.
Posle klika na OK dobijamo listu (Lista) datuma, koja se može proširiti u nove redove pomoću dugmeta u zaglavlju tabele:
… i dobijamo:
Sada je sve što je preostalo je da skupite tabelu, koristeći generisane datume kao nazive za nove kolone. Tim je odgovoran za ovo. Kolona detalja (Pivot Column) tabulator Pretvoriti (Transformacija):
Posle klika na OK dobijamo rezultat vrlo blizak željenom:
Null je, u ovom slučaju, analog prazne ćelije u Excelu.
Ostaje ukloniti nepotrebne kolone i naredbom isprazniti rezultirajuću tablicu pored originalnih podataka Zatvori i učitaj – Zatvori i učitaj u… (Zatvori i učitaj — Zatvori i učitaj do…) tabulator Početna (Dom):
Kao rezultat dobijamo:
Za veću ljepotu, možete prilagoditi izgled rezultirajućih pametnih stolova na kartici konstruktor (Dizajn): postavite stil jedne boje, onemogućite dugmad filtera, omogućite zbrojeve itd. Dodatno, možete odabrati tabelu s datumima i omogućiti isticanje brojeva za nju pomoću uslovnog oblikovanja na kartici Početna — Uvjetno oblikovanje — Skala boja (Početna — Uvjetno oblikovanje — Ljestvice boja):
A najbolji dio je da u budućnosti možete bezbedno uređivati stare ili dodati nove projekte u originalnu tabelu, a zatim ažurirati desnu tabelu sa datumima desnim tasterom miša – i Power Query će automatski ponoviti sve radnje koje smo uradili .
Voilà!
- Gantov grafikon u Excelu koristeći uslovno oblikovanje
- Kalendar prekretnica projekta
- Generiranje duplih redova pomoću Power Queryja