Pieņemsim, ka jums ir vairāki projekti ar atšķirīgu budžetu un vēlaties vizualizēt savas izmaksas katram no tiem. Tas ir, no šīs avota tabulas:
.. iegūstiet kaut ko līdzīgu šim:
Citiem vārdiem sakot, jums ir jāsadala budžets pa katra projekta dienām un jāiegūst vienkāršota projekta Ganta diagrammas versija. To darīt ar savām rokām ir ilgi un garlaicīgi, makro ir grūti, bet Power Query for Excel šādā situācijā parāda savu spēku visā savā krāšņumā.
Jaudas vaicājums ir Microsoft papildinājums, kas var importēt datus programmā Excel no gandrīz jebkura avota un pēc tam pārveidot tos dažādos veidos. Programmā Excel 2016 šī pievienojumprogramma jau ir iebūvēta pēc noklusējuma, un programmai Excel 2010–2013 to var lejupielādēt no Microsoft vietnes un pēc tam instalēt datorā.
Vispirms pārvērtīsim savu sākotnējo tabulu par “gudru” tabulu, izvēloties komandu Formatēt kā tabulu tab Sākumlapa (Sākums — formatēt kā tabulu) vai nospiežot īsinājumtaustiņu Ctrl+T :
Pēc tam dodieties uz cilni Datums (ja jums ir Excel 2016) vai cilnē Jaudas vaicājums (ja jums ir Excel 2010–2013 un Power Query instalējāt kā atsevišķu pievienojumprogrammu) un noklikšķiniet uz pogas No tabulas/diapazona. :
Mūsu viedā tabula tiek ielādēta Power Query vaicājumu redaktorā, kur vispirms ir jāiestata skaitļu formāti katrai kolonnai, izmantojot tabulas galvenē esošās nolaižamās izvēlnes.
Lai aprēķinātu budžetu dienā, jums jāaprēķina katra projekta ilgums. Lai to izdarītu, atlasiet (turiet nospiestu taustiņu Ctrl) kolonna pirmajā vietā apdare, un tad mājas un izvēlieties komandu Pievienot kolonnu – datums – atņemt dienas (Pievienot kolonnu — datums — atņemt dienas):
Iegūtie skaitļi ir par 1 mazāki nekā nepieciešams, jo mums ir paredzēts, ka katrs projekts jāsāk pirmajā dienā no rīta un jāpabeidz pēdējā dienā vakarā. Tāpēc atlasiet iegūto kolonnu un pievienojiet tai vienību, izmantojot komandu Pārveidot – Standarta – Pievienot (Pārveidot — Standarta — Pievienot):
Tagad pievienosim kolonnu, kurā aprēķinām dienas budžetu. Lai to izdarītu, cilnē Pievienot sleju Es nespēlēju Pielāgota kolonna (Pielāgota kolonna) un parādītajā logā ievadiet jaunā lauka nosaukumu un aprēķina formulu, izmantojot kolonnu nosaukumus no saraksta:
Tagad vissmalkākais brīdis – mēs izveidojam vēl vienu aprēķināto kolonnu ar datumu sarakstu no sākuma līdz beigām ar 1 dienas soli. Lai to izdarītu, vēlreiz nospiediet pogu Pielāgota kolonna (Pielāgota kolonna) un izmantojiet iebūvēto Power Query valodu M, ko sauc Saraksts.Datumi:
Šai funkcijai ir trīs argumenti:
- sākuma datums – mūsu gadījumā tas ir ņemts no kolonnas mājas
- ģenerējamo datumu skaits – mūsu gadījumā tas ir dienu skaits katram projektam, ko mēs skaitījām iepriekš kolonnā Atņemšana
- laika posms – noteikts pēc dizaina #duration(1,0,0,0), kas nozīmē M valodā – viena diena, nulle stundas, nulle minūtes, nulle sekundes.
Pēc noklikšķināšanas uz OK mēs iegūstam datumu sarakstu (Sarakstu), kuru var izvērst jaunās rindās, izmantojot pogu tabulas galvenē:
... un mēs iegūstam:
Tagad atliek tikai sakļaut tabulu, izmantojot ģenerētos datumus kā jauno kolonnu nosaukumus. Par to ir atbildīga komanda. Detaļu kolonna (Rakursa kolonna) tab Pārvērst (Pārveidot):
Pēc noklikšķināšanas uz OK mēs iegūstam rezultātu ļoti tuvu vēlamajam:
Null šajā gadījumā ir tukšas šūnas analogs programmā Excel.
Atliek noņemt nevajadzīgās kolonnas un ar komandu izlādēt iegūto tabulu blakus sākotnējiem datiem Aizvērt un ielādēt – aizvērt un ielādēt… (Aizvērt un ielādēt — aizvērt un ielādēt...) tab Sākumlapa (Mājas):
Rezultātā iegūstam:
Lai iegūtu lielāku skaistumu, varat pielāgot izveidoto viedo tabulu izskatu cilnē Konstruktors (Dizains): iestatiet vienas krāsas stilu, atspējojiet filtru pogas, iespējojiet kopsummas utt. Turklāt varat atlasīt tabulu ar datumiem un iespējot skaitļu izcelšanu, izmantojot nosacītu formatējumu cilnē Sākums — Nosacījuma formatēšana — Krāsu skalas (Sākums — nosacījumformatēšana — krāsu skalas):
Un pats labākais ir tas, ka turpmāk varat droši rediģēt vecos projektus vai pievienot jaunus projektus oriģinālajai tabulai un pēc tam ar peles labo pogu atjaunināt pareizo tabulu ar datumiem – un Power Query automātiski atkārtos visas mūsu veiktās darbības. .
Voilà!
- Ganta diagramma programmā Excel, izmantojot nosacījumu formatējumu
- Projekta pagrieziena kalendārs
- Dublētu rindu ģenerēšana, izmantojot Power Query