Tarkime, kad vykdote kelis projektus su skirtingais biudžetais ir norite vizualizuoti kiekvieno iš jų išlaidas. Tai yra, iš šios šaltinio lentelės:
.. gaukite kažką panašaus:
Kitaip tariant, turite paskirstyti biudžetą per kiekvieno projekto dienas ir gauti supaprastintą projekto Ganto diagramos versiją. Tai daryti savo rankomis yra ilga ir nuobodu, makrokomandos yra sudėtingos, tačiau Power Query for Excel tokioje situacijoje parodo savo galią visoje savo šlovėje.
„Power Query“ yra „Microsoft“ priedas, galintis importuoti duomenis į „Excel“ iš beveik bet kurio šaltinio ir įvairiais būdais juos transformuoti. Programoje „Excel 2016“ šis priedas jau yra integruotas pagal numatytuosius nustatymus, o „Excel 2010–2013“ versijoje jį galima atsisiųsti iš „Microsoft“ svetainės ir įdiegti kompiuteryje.
Pirmiausia paverskime savo originalią lentelę „išmaniąja“ lentele pasirinkę komandą Formatuokite kaip lentelę kortelė Pagrindinis (Pagrindinis – formatuoti kaip lentelę) arba paspausdami spartųjį klavišą "Ctrl"+T :
Tada eikite į skirtuką Duomenys (jei turite Excel 2016) arba skirtuke „Power Query“ (jei turite „Excel 2010–2013“ ir „Power Query“ įdiegėte kaip atskirą priedą) ir spustelėkite mygtuką Iš lentelės / diapazono. :
Mūsų išmanioji lentelė įkeliama į Power Query užklausų rengyklę, kur pirmiausia reikia nustatyti kiekvieno stulpelio skaičių formatus, naudojant lentelės antraštėje esančius išskleidžiamuosius meniu:
Norėdami apskaičiuoti dienos biudžetą, turite apskaičiuoti kiekvieno projekto trukmę. Norėdami tai padaryti, pasirinkite (laikykite nuspaudę klavišą "Ctrl") pirmas stulpelis apdaila, O tada pradžia ir pasirinkti komandą Pridėti stulpelį – data – atimti dienas (Pridėti stulpelį – data – atimti dienas):
Gauti skaičiai yra 1 mažiau nei reikia, nes kiekvieną projektą turime pradėti pirmą dieną ryte ir baigti paskutinę dieną vakare. Todėl pasirinkite gautą stulpelį ir pridėkite prie jo vienetą naudodami komandą Transformuoti – Standartinis – pridėti (Transformuoti – standartinis – pridėti):
Dabar pridėkime stulpelį, kuriame apskaičiuojame dienos biudžetą. Norėdami tai padaryti, skirtuke pridėti stulpelį Aš nežaidžiu Pasirinktinis stulpelis (Priskirtas stulpelis) ir pasirodžiusiame lange įveskite naujo lauko pavadinimą ir skaičiavimo formulę, naudodami sąrašo stulpelių pavadinimus:
Dabar pats subtiliausias momentas – sukuriame dar vieną apskaičiuotą stulpelį su datų sąrašu nuo pradžios iki pabaigos 1 dienos žingsniu. Norėdami tai padaryti, dar kartą paspauskite mygtuką Pasirinktinis stulpelis (Priskirtas stulpelis) ir naudokite integruotą „Power Query“ kalbą M, kuri vadinama Sąrašas.Datos:
Ši funkcija turi tris argumentus:
- pradžios data – mūsų atveju ji paimta iš stulpelio pradžia
- sugeneruojamų datų skaičius – mūsų atveju tai yra dienų skaičius kiekvienam projektui, kurį stulpelyje suskaičiavome anksčiau Atimtis
- laiko žingsnis – nustatytas pagal dizainą #duration(1,0,0,0), M kalba reiškia – viena diena, nulis valandų, nulis minučių, nulis sekundžių.
Spustelėję OK gauname datų sąrašą (Sąrašą), kurį galima išplėsti į naujas eilutes, naudojant mygtuką lentelės antraštėje:
... ir mes gauname:
Dabar belieka sutraukti lentelę, naudojant sugeneruotas datas kaip naujų stulpelių pavadinimus. Už tai atsakinga komanda. Išsamios informacijos stulpelis (Sukamoji stulpelis) kortelė Konvertuoti (Transformacija):
Spustelėję OK gauname labai artimą norimam rezultatą:
Null šiuo atveju yra tuščio Excel langelio analogas.
Belieka pašalinti nereikalingus stulpelius ir su komanda iškrauti gautą lentelę šalia pirminių duomenų Uždaryti ir įkelti – uždaryti ir įkelti… (Uždaryti ir įkelti – uždaryti ir įkelti į…) kortelė Pagrindinis (Namai):
Kaip rezultatą gauname:
Norėdami gauti didesnį grožį, skirtuke galite tinkinti sukurtų išmaniųjų lentelių išvaizdą Konstruktorius (Dizainas): nustatykite vienos spalvos stilių, išjunkite filtrų mygtukus, įgalinkite sumas ir pan. Be to, galite pasirinkti lentelę su datomis ir įjungti skaičių paryškinimą naudodami sąlyginį formatavimą skirtuke. Pagrindinis puslapis — Sąlyginis formatavimas — Spalvų skalės (Pagrindinis – sąlyginis formatavimas – spalvų skalės):
Ir geriausia yra tai, kad ateityje galėsite saugiai redaguoti senus arba pridėti naujų projektų į pradinę lentelę, o tada dešiniuoju pelės mygtuku atnaujinti reikiamą lentelę su datomis – ir Power Query automatiškai pakartos visus mūsų atliktus veiksmus. .
Čia yra!
- Ganto diagrama „Excel“ naudojant sąlyginį formatavimą
- Projekto etapų kalendorius
- Pasikartojančių eilučių generavimas naudojant „Power Query“.