Řekněme, že provozujete několik projektů s různými rozpočty a chcete vizualizovat své náklady pro každý z nich. Tedy z této zdrojové tabulky:
.. získejte něco takového:
Jinými slovy, musíte rozložit rozpočet na dny každého projektu a získat zjednodušenou verzi projektového Ganttova diagramu. Dělat to rukama je zdlouhavé a nudné, makra jsou náročná, ale Power Query pro Excel v takové situaci ukazuje svou sílu v celé své kráse.
Dotaz na napájení je doplněk od společnosti Microsoft, který dokáže importovat data do Excelu z téměř jakéhokoli zdroje a poté je transformovat mnoha různými způsoby. V aplikaci Excel 2016 je tento doplněk již ve výchozím nastavení vestavěn a pro aplikaci Excel 2010-2013 jej lze stáhnout z webu společnosti Microsoft a poté nainstalovat do počítače.
Nejprve udělejme z naší původní tabulky „chytrou“ tabulku výběrem příkazu Formátovat jako tabulku Karta Domů (Domů — Formátovat jako tabulku) nebo stisknutím klávesové zkratky Ctrl+T :
Poté přejděte na kartu Data (pokud máte Excel 2016) nebo na záložce Dotaz na napájení (pokud máte Excel 2010-2013 a nainstalovali jste Power Query jako samostatný doplněk) a klikněte na tlačítko Z tabulky / rozsahu. :
Naše inteligentní tabulka se načte do editoru dotazů Power Query, kde je prvním krokem nastavení formátů čísel pro každý sloupec pomocí rozevíracích nabídek v záhlaví tabulky:
Chcete-li vypočítat rozpočet na den, musíte vypočítat dobu trvání každého projektu. Chcete-li to provést, vyberte (podržte klávesu Ctrl) sloupec první úprava, A pak Home a vybrat tým Přidat sloupec – Datum – Odečíst dny (Přidat sloupec — Datum — Odečíst dny):
Výsledná čísla jsou o 1 menší, než je nutné, protože se předpokládá, že každý projekt začneme první den ráno a skončíme poslední den večer. Vyberte tedy výsledný sloupec a pomocí příkazu do něj přidejte jednotku Transformace – Standardní – Přidat (Transformovat — Standardní — Přidat):
Nyní přidáme sloupec, kde počítáme rozpočet na den. Chcete-li to provést, na kartě Přidat sloupec já nehraju Vlastní sloupec (Vlastní sloupec) a v okně, které se objeví, zadejte název nového pole a výpočetní vzorec pomocí názvů sloupců ze seznamu:
Nyní ten nejjemnější moment – vytvoříme další vypočítaný sloupec se seznamem dat od začátku do konce s krokem 1 dne. Chcete-li to provést, znovu stiskněte tlačítko Vlastní sloupec (Vlastní sloupec) a používat vestavěný jazyk Power Query M, který se nazývá Seznam.Data:
Tato funkce má tři argumenty:
- počáteční datum – v našem případě je převzato ze sloupce Home
- počet dat, která mají být vygenerována – v našem případě je to počet dní pro každý projekt, který jsme počítali dříve ve sloupci Odčítání
- časový krok – stanovený designem #duration(1,0,0,0), což znamená v jazyce M – jeden den, nula hodin, nula minut, nula sekund.
Po kliknutí na tlačítko OK získáme seznam (Seznam) dat, který lze rozbalit do nových řádků pomocí tlačítka v záhlaví tabulky:
…a dostáváme:
Nyní zbývá pouze sbalit tabulku a použít vygenerovaná data jako názvy nových sloupců. Zodpovídá za to tým. Sloupec podrobností (Pivotní sloupec) Karta Konvertovat (Přeměnit):
Po kliknutí na tlačítko OK dostaneme výsledek velmi blízký požadovanému:
Null je v tomto případě obdobou prázdné buňky v Excelu.
Zbývá odstranit nepotřebné sloupce a příkazem uvolnit výslednou tabulku vedle původních dat Zavřít a načíst – Zavřít a načíst… (Zavřít a načíst — Zavřít a načíst do…) Karta Domů (Domov):
Dostaneme jako výsledek:
Pro větší krásu si na záložce můžete upravit vzhled výsledných chytrých tabulek Stavitel (Design): nastavení stylu jedné barvy, deaktivace tlačítek filtru, povolení součtů atd. Kromě toho můžete vybrat tabulku s daty a povolit pro ni zvýraznění čísel pomocí podmíněného formátování na kartě Domů — Podmíněné formátování — Barevné stupnice (Domů — Podmíněné formátování — Barevné stupnice):
A nejlepší na tom je, že v budoucnu můžete bezpečně upravovat staré nebo přidávat nové projekty do původní tabulky a poté aktualizovat pravou tabulku daty pomocí pravého tlačítka myši – a Power Query automaticky zopakuje všechny akce, které jsme provedli. .
Voila!
- Ganttův diagram v Excelu pomocí podmíněného formátování
- Kalendář milníků projektu
- Generování duplicitních řádků pomocí Power Query