Povedzme, že prevádzkujete niekoľko projektov s rôznymi rozpočtami a chcete vizualizovať svoje náklady pre každý z nich. Teda z tejto zdrojovej tabuľky:
.. získaj niečo takéto:
Inými slovami, musíte rozložiť rozpočet na dni každého projektu a získať zjednodušenú verziu Ganttovho diagramu projektu. Robiť to rukami je zdĺhavé a nudné, makrá sú náročné, ale Power Query pre Excel v takejto situácii ukazuje svoju silu v celej svojej kráse.
mocenský dotaz je doplnok od spoločnosti Microsoft, ktorý dokáže importovať údaje do Excelu z takmer akéhokoľvek zdroja a následne ich transformovať rôznymi spôsobmi. V Exceli 2016 je tento doplnok už predvolene zabudovaný a pre Excel 2010-2013 si ho možno stiahnuť z webovej stránky spoločnosti Microsoft a potom nainštalovať do počítača.
Najprv urobme z našej pôvodnej tabuľky „inteligentnú“ tabuľku výberom príkazu Formátovať ako tabuľku pútko Domov (Domov – Formátovať ako tabuľku) alebo stlačením klávesovej skratky ctrl+T :
Potom prejdite na kartu dátum (ak máte Excel 2016) alebo na karte mocenský dotaz (ak máte Excel 2010-2013 a nainštalovali ste Power Query ako samostatný doplnok) a kliknite na tlačidlo Z tabuľky/rozsahu. :
Naša inteligentná tabuľka sa načíta do editora dotazov Power Query, kde prvým krokom je nastavenie formátov čísel pre každý stĺpec pomocou rozbaľovacích ponúk v hlavičke tabuľky:
Ak chcete vypočítať rozpočet na deň, musíte vypočítať trvanie každého projektu. Ak to chcete urobiť, vyberte (podržte kláves ctrl) stĺpec prvý úprava, a potom štart a vyberte si tím Pridať stĺpec – Dátum – Odčítať dni (Pridať stĺpec — Dátum — Odčítať dni):
Výsledné čísla sú o 1 menšie, ako je potrebné, pretože každý projekt by sme mali začať v prvý deň ráno a skončiť posledný deň večer. Preto vyberte výsledný stĺpec a pridajte do neho jednotku pomocou príkazu Transformácia – Štandardná – Pridať (Transformovať — Štandard — Pridať):
Teraz pridáme stĺpec, kde vypočítame rozpočet na deň. Ak to chcete urobiť, na karte Pridať stĺpec nehrám Vlastný stĺpec (Vlastný stĺpec) a v zobrazenom okne zadajte názov nového poľa a vzorec výpočtu pomocou názvov stĺpcov zo zoznamu:
Teraz ten najjemnejší moment – vytvoríme ďalší vypočítaný stĺpec so zoznamom dátumov od začiatku do konca s krokom 1 dňa. Ak to chcete urobiť, znova stlačte tlačidlo Vlastný stĺpec (Vlastný stĺpec) a použiť vstavaný jazyk Power Query M, ktorý je tzv Zoznam.Dátumy:
Táto funkcia má tri argumenty:
- dátum začiatku – v našom prípade je prevzatý zo stĺpca štart
- počet dátumov, ktoré sa majú vygenerovať – v našom prípade je to počet dní pre každý projekt, ktorý sme počítali skôr v stĺpci Odčítanie
- časový krok – nastavený podľa návrhu #duration(1,0,0,0), teda v jazyku M – jeden deň, nula hodín, nula minút, nula sekúnd.
Po kliknutí na OK dostaneme zoznam (Zoznam) dátumov, ktorý je možné rozbaliť do nových riadkov pomocou tlačidla v záhlaví tabuľky:
...a dostaneme:
Teraz už zostáva len zbaliť tabuľku a použiť vygenerované dátumy ako názvy nových stĺpcov. Tým je za to zodpovedný. Stĺpec podrobností (Kontingenčný stĺpec) pútko Konvertovať (Transformácia):
Po kliknutí na OK dostaneme výsledok veľmi blízky požadovanému:
Null je v tomto prípade analógom prázdnej bunky v Exceli.
Zostáva odstrániť nepotrebné stĺpce a príkazom vyložiť výslednú tabuľku vedľa pôvodných údajov Zavrieť a načítať – Zavrieť a naložiť... (Zavrieť a načítať – Zavrieť a načítať do...) pútko Domov (Domov):
Ako výsledok dostaneme:
Pre väčšiu krásu si môžete prispôsobiť vzhľad výsledných inteligentných tabuliek na karte staviteľ (Dizajn): nastaviť štýl jednej farby, deaktivovať tlačidlá filtrovania, povoliť súčty atď. Okrem toho môžete vybrať tabuľku s dátumami a povoliť pre ňu zvýraznenie čísel pomocou podmieneného formátovania na karte Domov — Podmienené formátovanie — Farebné škály (Domov — Podmienené formátovanie — Farebné stupnice):
A najlepšie na tom je, že v budúcnosti môžete bezpečne upravovať staré alebo pridávať nové projekty do pôvodnej tabuľky a potom aktualizovať pravú tabuľku dátumami pomocou pravého tlačidla myši – a Power Query automaticky zopakuje všetky akcie, ktoré sme vykonali. .
Voila!
- Ganttov diagram v Exceli pomocou podmieneného formátovania
- Kalendár míľnikov projektu
- Generovanie duplicitných riadkov pomocou Power Query