Oletetaan, että sinulla on käynnissä useita projekteja eri budjeteilla ja haluat visualisoida kustannukset kustakin niistä. Eli tästä lähdetaulukosta:
.. hanki jotain tällaista:
Toisin sanoen sinun on jaettava budjetti kunkin projektin päiville ja hankittava yksinkertaistettu versio projektin Gantt-kaaviosta. Tämän tekeminen omin käsin on pitkää ja tylsää, makrot ovat vaikeita, mutta Power Query for Excel näyttää tällaisessa tilanteessa voimansa kaikessa loistossaan.
Tehokysely on Microsoftin lisäosa, joka voi tuoda tietoja Exceliin melkein mistä tahansa lähteestä ja sitten muuntaa niitä monilla eri tavoilla. Excel 2016:ssa tämä apuohjelma on jo oletusarvoisesti sisäänrakennettu, ja Excel 2010-2013:ssa se voidaan ladata Microsoftin verkkosivustolta ja asentaa sitten tietokoneellesi.
Ensin muutetaan alkuperäinen taulukko "älykkääksi" taulukoksi valitsemalla komennon Muotoile taulukkona kieleke Koti (Etusivu — Muotoile taulukkona) tai painamalla pikanäppäintä Ctrl+T :
Siirry sitten välilehdelle Päiväys (jos sinulla on Excel 2016) tai välilehdellä Tehokysely (jos sinulla on Excel 2010–2013 ja asensit Power Queryn erillisenä apuohjelmana) ja napsauta Taulukosta/alueesta -painiketta. :
Älykäs taulukkomme ladataan Power Queryn kyselyeditoriin, jossa ensimmäinen vaihe on määrittää kunkin sarakkeen numeromuodot käyttämällä taulukon otsikon avattavia valikoita:
Päiväbudjetin laskemiseksi sinun on laskettava kunkin projektin kesto. Voit tehdä tämän valitsemalla (pidä näppäintä painettuna Ctrl) sarake ensin Suorittaa loppuunJa sitten Aloita ja valitse joukkue Lisää sarake – Päivämäärä – Vähennä päivät (Lisää sarake — päivämäärä — vähennä päivät):
Tuloksena olevat luvut ovat 1 vähemmän kuin on tarpeen, koska meidän on tarkoitus aloittaa jokainen projekti ensimmäisenä päivänä aamulla ja päättyä viimeisenä päivänä illalla. Valitse siksi tuloksena oleva sarake ja lisää siihen yksikkö komennolla Muunna – Vakio – Lisää (Muunna – Vakio – Lisää):
Lisätään nyt sarake, jossa laskemme päiväbudjetin. Voit tehdä tämän välilehdellä Lisää sarake En pelaa Muokattu sarake (Muokattu sarake) ja kirjoita näkyviin tulevaan ikkunaan uuden kentän nimi ja laskentakaava käyttäen luettelon sarakkeiden nimiä:
Nyt hienovaraisin hetki – luomme toisen lasketun sarakkeen, jossa on päivämäärien luettelo alusta loppuun 1 päivän askeleella. Voit tehdä tämän painamalla painiketta uudelleen Muokattu sarake (Muokattu sarake) ja käytä sisäänrakennettua Power Query -kieltä M, jota kutsutaan Lista.Päivämäärät:
Tällä funktiolla on kolme argumenttia:
- aloituspäivämäärä – meidän tapauksessamme se on otettu sarakkeesta Aloita
- luotavien päivämäärien määrä – meidän tapauksessamme tämä on kunkin projektin päivien lukumäärä, jonka laskemme aiemmin sarakkeessa Vähennys
- aikavaihe – suunnittelun mukaan #kesto(1,0,0,0), tarkoittaa M:n kielellä – yksi päivä, nolla tuntia, nolla minuuttia, nolla sekuntia.
Napsauttamisen jälkeen OK saamme listan (List) päivämääristä, jotka voidaan laajentaa uusille riveille käyttämällä taulukon otsikossa olevaa painiketta:
… ja saamme:
Nyt ei jää enää muuta kuin kutistaa taulukko käyttämällä luotuja päivämääriä uusien sarakkeiden niminä. Joukkue on vastuussa tästä. Yksityiskohta sarake (Pivot Column) kieleke Muuntaa (Muuttaa):
Napsauttamisen jälkeen OK saamme tuloksen, joka on hyvin lähellä haluttua:
Null on tässä tapauksessa tyhjän solun analogi Excelissä.
On vielä poistettava tarpeettomat sarakkeet ja purettava tuloksena oleva taulukko alkuperäisten tietojen viereen komennolla Sulje ja lataa – Sulje ja lataa… (Sulje ja lataa – Sulje ja lataa…) kieleke Koti (Koti):
Saamme tuloksena:
Paremman kauneuden saamiseksi voit mukauttaa tuloksena olevien älykkäiden pöytien ulkoasua välilehdellä Rakentaja (Design): aseta yksivärinen tyyli, poista suodatinpainikkeet käytöstä, ota summat käyttöön jne. Lisäksi voit valita päivämäärät sisältävän taulukon ja ottaa käyttöön numerokorostuksen käyttämällä välilehden ehdollista muotoilua Etusivu — Ehdollinen muotoilu — Väriasteikot (Etusivu — ehdollinen muotoilu — väriasteikot):
Ja mikä parasta, voit jatkossa turvallisesti muokata vanhoja tai lisätä uusia projekteja alkuperäiseen taulukkoon ja päivittää sitten oikeaan taulukkoon päivämäärät hiiren oikealla painikkeella – ja Power Query toistaa kaikki tekemämme toiminnot automaattisesti. .
Voilà!
- Gantt-kaavio Excelissä ehdollista muotoilua käyttäen
- Projektin virstanpylväskalenteri
- Päällekkäisten rivien luominen Power Querylla