Oletame, et käitate mitut erineva eelarvega projekti ja soovite visualiseerida nende kulusid. See tähendab, et sellest allika tabelist:
.. hankige midagi sellist:
Teisisõnu peate jaotama eelarve iga projekti päevade peale ja hankima projekti Gantti diagrammi lihtsustatud versiooni. Seda oma kätega teha on pikk ja igav, makrod on keerulised, kuid Power Query for Excel näitab sellises olukorras oma võimsust kogu oma hiilguses.
Toite päring on Microsofti lisandmoodul, mis suudab importida andmeid Excelisse peaaegu igast allikast ja seejärel muuta neid mitmel erineval viisil. Excel 2016-s on see lisandmoodul juba vaikimisi sisseehitatud ja Excel 2010–2013 jaoks saab selle Microsofti veebisaidilt alla laadida ja seejärel arvutisse installida.
Esmalt muudame oma algse tabeli „nutikaks” tabeliks, valides käsu Vorminda tabelina tab Avaleht (Avaleht — vorminda tabelina) või vajutades kiirklahvi Ctrl+T :
Seejärel minge vahekaardile kuupäev (kui teil on Excel 2016) või vahekaardil Toite päring (kui teil on Excel 2010–2013 ja installisite Power Query eraldi lisandmoodulina) ja klõpsake nuppu Tabelist/vahemikust. :
Meie nutikas tabel laaditakse Power Query päringuredaktorisse, kus esimene samm on seadistada iga veeru numbrivormingud, kasutades tabeli päises olevaid rippmenüüd.
Päevaeelarve arvutamiseks peate arvutama iga projekti kestuse. Selleks valige (hoidke klahvi all Ctrl) veerg esimene lõpp, ja siis Avaleht ja vali meeskond Lisa veerg – kuupäev – päevade lahutamine (Lisa veerg — kuupäev — lahuta päevad):
Saadud arvud on 1 vähem kui vaja, sest me peaksime iga projektiga alustama esimesel päeval hommikul ja lõpetama viimasel päeval õhtul. Seetõttu valige saadud veerg ja lisage sellele käsu abil üksus Teisendus – Standardne – Lisa (Teisendus – standardne – lisa):
Nüüd lisame veeru, kus arvutame päevaeelarve. Selleks vahekaardil Lisa veerg Ma ei mängi Kohandatud veerg (Kohandatud veerg) ja sisestage ilmuvas aknas uue välja nimi ja arvutusvalem, kasutades loendi veergude nimesid:
Nüüd kõige peenem hetk – loome teise arvutatud veeru kuupäevade loendiga algusest lõpuni, sammuga 1 päev. Selleks vajutage uuesti nuppu Kohandatud veerg (Kohandatud veerg) ja kasutada sisseehitatud Power Query keelt M, mida nimetatakse Nimekiri.Kuupäevad:
Sellel funktsioonil on kolm argumenti:
- alguskuupäev – meie puhul võetakse see veerust Avaleht
- genereeritavate kuupäevade arv – meie puhul on see iga projekti päevade arv, mille loendasime veerus varem Lahutamine
- ajasamm – disaini järgi määratud #kestus(1,0,0,0), mis tähendab M keeles – üks päev, null tundi, null minutit, null sekundit.
Pärast klõpsamist nupul OK saame kuupäevade loendi (List), mida saab tabeli päises oleva nupu abil uuteks ridadeks laiendada:
… ja me saame:
Nüüd jääb üle vaid tabel ahendada, kasutades uute veergude nimedena loodud kuupäevi. Selle eest vastutab meeskond. Üksikasjade veerg (Pöördveerg) tab Muutma (teisendus):
Pärast klõpsamist nupul OK saame soovitud tulemusele väga lähedase tulemuse:
Null on antud juhul Exceli tühja lahtri analoog.
Jääb üle mittevajalikud veerud eemaldada ja saadud tabel algandmete kõrval käsuga maha laadida Sulgege ja laadige – sulgege ja laadige sisse… (Sule ja laadi – sulge ja laadi…) tab Avaleht (Kodu):
Tulemuseks saame:
Suurema ilu huvides saate kohandada saadud nutikate tabelite välimust vahekaardil Ehitaja (Kujundus): määrake ühevärviline stiil, keelake filtrinupud, lubage kogusummad jne. Lisaks saate vahekaardil tingimusvormingu abil valida kuupäevadega tabeli ja lubada selle jaoks numbrite esiletõstmise Avaleht — Tingimuslik vormindamine — Värviskaalad (Avaleht – tingimuslik vormindamine – värviskaalad):
Ja parim osa on see, et edaspidi saate turvaliselt vanu redigeerida või algsesse tabelisse uusi projekte lisada ning seejärel hiire parema nupuga õiget tabelit kuupäevadega värskendada – ja Power Query kordab automaatselt kõiki meie tehtud toiminguid. .
Viola!
- Gantti diagramm Excelis tingimusvormingu abil
- Projekti verstaposti kalender
- Dubleerivate ridade genereerimine Power Query abil