Tegyük fel, hogy több projektet futtat különböző költségvetéssel, és mindegyiknél szeretné elképzelni költségeit. Vagyis ebből a forrástáblázatból:
.. kap valami ilyesmit:
Más szóval, el kell osztania a költségvetést az egyes projektek napjaira, és meg kell kapnia a projekt Gantt-diagramjának egyszerűsített változatát. Ezt kézzel csinálni hosszú és unalmas, a makrók bonyolultak, de a Power Query for Excel ilyen helyzetben teljes dicsőségében mutatja meg erejét.
Teljesítmény lekérdezés a Microsoft egy kiegészítője, amely szinte bármilyen forrásból képes adatokat importálni az Excelbe, majd számos különböző módon átalakítani. Az Excel 2016-ban ez a bővítmény már alapértelmezés szerint be van építve, Excel 2010-2013 esetén pedig letölthető a Microsoft webhelyéről, majd telepíthető a számítógépére.
Először alakítsuk át eredeti táblázatunkat „okos” táblává a parancs kiválasztásával Formázás táblázatként lap Kezdőlap (Főoldal — Táblázat formázása) vagy a billentyűparancs megnyomásával Ctrl+T :
Ezután lépjen a lapra dátum (ha rendelkezik Excel 2016-tal) vagy a lapon Teljesítmény lekérdezés (ha az Excel 2010–2013-as verziója van, és a Power Query-t külön bővítményként telepítette), majd kattintson a Táblázatból/Tartományból gombra. :
Intelligens táblázatunk betöltődik a Power Query lekérdezésszerkesztőbe, ahol első lépésként be kell állítani a számformátumokat az egyes oszlopokhoz a táblázat fejlécében található legördülő menük segítségével:
A napi költségvetés kiszámításához ki kell számítania az egyes projektek időtartamát. Ehhez válassza a (tartsa lenyomva a gombot Ctrl) oszlop elején befejez, és akkor Rajt és válassz egy csapatot Oszlop hozzáadása – Dátum – Napok kivonása (Oszlop hozzáadása — Dátum — Napok kivonása):
Az így kapott számok 1-gyel kisebbek a szükségesnél, mivel minden projektet az első napon reggel kell indítanunk, és az utolsó napon este kell befejeznünk. Ezért válassza ki az eredményül kapott oszlopot, és adjon hozzá egy egységet a paranccsal Átalakítás – Standard – Hozzáadás (Átalakítás – Standard – Hozzáadás):
Most adjunk hozzá egy oszlopot, ahol kiszámítjuk a napi költségkeretet. Ehhez a lapon Oszlop hozzáadása nem játszom Egyéni oszlop (Egyéni oszlop) és a megjelenő ablakban írja be az új mező nevét és a számítási képletet a lista oszlopainak nevével:
Most a legfinomabb pillanat – hozunk létre egy másik számított oszlopot a dátumok listájával az elejétől a végéig, 1 napos lépéssel. Ehhez nyomja meg ismét a gombot Egyéni oszlop (Egyéni oszlop) és használja a beépített M Power Query nyelvet, amely az ún Lista.Dátumok:
Ennek a függvénynek három argumentuma van:
- kezdési dátum – esetünkben az oszlopból vettük át Rajt
- a generálandó dátumok száma – esetünkben ez az egyes projektekhez tartozó napok száma, amit korábban az oszlopban számoltunk Kivonás
- időlépés – tervezés által meghatározott #duration(1,0,0,0), jelentése M nyelvén – egy nap, nulla óra, nulla perc, nulla másodperc.
Miután rákattintott OK dátumlistát (Listát) kapunk, amelyet a táblázat fejlécében található gomb segítségével új sorokra bővíthetünk:
… és kapjuk:
Most már csak a táblázat összecsukása van hátra, a generált dátumokat használva az új oszlopok neveként. A csapat felelős ezért. Részletek oszlop (Pivot Oszlop) lap Megtérít (Átalakítás):
Miután rákattintott OK a kívánthoz nagyon közeli eredményt kapunk:
A Null ebben az esetben az Excel üres cellájának analógja.
Marad a felesleges oszlopok eltávolítása és a kapott tábla eltávolítása az eredeti adatok mellett a paranccsal Zárja be és töltse be – Zárja be és töltse be… (Bezárás és betöltés – Bezárás és betöltés…) lap Kezdőlap (Itthon):
Eredményként kapjuk:
A nagyobb szépség érdekében testreszabhatja az eredményül kapott intelligens táblázatok megjelenését a lapon építész (Tervezés): állítson be egy színstílust, tiltsa le a szűrőgombokat, engedélyezze az összesítést stb. Ezenkívül kiválaszthat egy táblázatot dátumokkal, és engedélyezheti a számok kiemelését a lap feltételes formázásával Kezdőlap — Feltételes formázás — Színskálák (Főoldal – Feltételes formázás – Színskálák):
És a legjobb az egészben az, hogy a jövőben biztonságosan szerkesztheti a régieket, vagy új projekteket adhat hozzá az eredeti táblához, majd a jobb egérgombbal frissítheti a megfelelő táblázatot dátumokkal – és a Power Query automatikusan megismétli az összes általunk elvégzett műveletet. .
Voálá!
- Gantt-diagram Excelben feltételes formázással
- Projekt mérföldkő naptár
- Ismétlődő sorok generálása a Power Query segítségével