Recimo, da izvajate več projektov z različnimi proračuni in želite vizualizirati svoje stroške za vsakega od njih. To je iz te izvorne tabele:
.. dobite nekaj takega:
Z drugimi besedami, proračun morate razdeliti na dneve vsakega projekta in dobiti poenostavljeno različico gantograma projekta. Delati to z lastnimi rokami je dolgo in dolgočasno, makri so težki, a Power Query za Excel v takšni situaciji pokaže svojo moč v vsem svojem sijaju.
poizvedba o moči je Microsoftov dodatek, ki lahko uvozi podatke v Excel iz skoraj vseh virov in jih nato preoblikuje na veliko različnih načinov. V Excelu 2016 je ta dodatek že privzeto vgrajen, za Excel 2010–2013 pa ga je mogoče prenesti z Microsoftovega spletnega mesta in nato namestiti v svoj osebni računalnik.
Najprej spremenimo našo izvirno tabelo v »pametno« tabelo z izbiro ukaza Oblikuj kot tabelo tab Domov (Domov — Oblikuj kot tabelo) ali s pritiskom na bližnjico na tipkovnici Ctrl+T :
Nato pojdite na zavihek datum (če imate Excel 2016) ali na zavihku poizvedba o moči (če imate Excel 2010-2013 in ste Power Query namestili kot ločen dodatek) in kliknite gumb Iz tabele / obsega. :
Naša pametna tabela se naloži v urejevalnik poizvedb Power Query, kjer je prvi korak nastavitev številskih oblik za vsak stolpec s spustnimi meniji v glavi tabele:
Če želite izračunati proračun na dan, morate izračunati trajanje vsakega projekta. Če želite to narediti, izberite (držite tipko Ctrl) prvi stolpec Konec, in potem Začetek in izberite ekipo Dodaj stolpec – Datum – Odštej dneve (Dodaj stolpec — Datum — Odštej dneve):
Dobljena števila so za 1 manjša od potrebnih, saj naj bi vsak projekt začeli prvi dan zjutraj in končali zadnji dan zvečer. Zato izberite nastali stolpec in mu dodajte enoto z ukazom Pretvori – Standardno – Dodaj (Pretvori — Standardno — Dodaj):
Zdaj pa dodamo stolpec, kjer izračunamo dnevni proračun. Če želite to narediti, na zavihku Dodaj stolpec ne igram Stolpec po meri (stolpec po meri) in v okno, ki se prikaže, vnesite ime novega polja in formulo za izračun z uporabo imen stolpcev s seznama:
Zdaj najbolj subtilen trenutek - ustvarimo še en izračunani stolpec s seznamom datumov od začetka do konca s korakom 1 dan. Če želite to narediti, znova pritisnite gumb Stolpec po meri (stolpec po meri) in uporabite vgrajeni jezik Power Query M, ki se imenuje Seznam. Datumi:
Ta funkcija ima tri argumente:
- začetni datum – v našem primeru je vzet iz stolpca Začetek
- število datumov, ki jih je treba generirati – v našem primeru je to število dni za vsak projekt, ki smo jih prešteli prej v stolpcu Odštevanje
- časovni korak – določeno z zasnovo #trajanje(1,0,0,0), kar pomeni v jeziku M – en dan, nič ur, nič minut, nič sekund.
Po kliku OK dobimo seznam (List) datumov, ki ga lahko razširimo v nove vrstice z gumbom v glavi tabele:
... in dobimo:
Zdaj je vse, kar ostane, strniti tabelo z uporabo ustvarjenih datumov kot imen za nove stolpce. Za to je zaslužna ekipa. Stolpec s podrobnostmi (Vrtilni stolpec) tab Pretvarjanje (preobrazba):
Po kliku OK dobimo rezultat zelo blizu želenemu:
Null je v tem primeru analog prazne celice v Excelu.
Ostaja še odstraniti nepotrebne stolpce in z ukazom razložiti nastalo tabelo poleg izvirnih podatkov Zapri in naloži – Zapri in naloži v… (Zapri & naloži — Zapri in naloži v ...) tab Domov (Domov):
Kot rezultat dobimo:
Za večjo lepoto lahko prilagodite videz nastalih pametnih tabel na zavihku Konstruktor (Oblikovanje): nastavite enobarvni slog, onemogočite gumbe za filtre, omogočite vsote itd. Poleg tega lahko izberete tabelo z datumi in omogočite označevanje številk zanjo s pogojnim oblikovanjem na zavihku Domov — Pogojno oblikovanje — Barvne lestvice (Domov — Pogojno oblikovanje — Barvne lestvice):
In najboljše pri tem je, da lahko v prihodnosti varno urejate stare ali dodajate nove projekte izvirni tabeli in nato z desnim gumbom miške posodobite desno tabelo z datumi – in Power Query bo ponovil vsa dejanja, ki smo jih naredili samodejno. .
Voilà!
- Gantogram v Excelu z uporabo pogojnega oblikovanja
- Koledar projektnih mejnikov
- Ustvarjanje podvojenih vrstic s Power Query