Sabihin nating nagpapatakbo ka ng ilang proyekto na may iba't ibang badyet at gusto mong makita ang iyong mga gastos para sa bawat isa sa kanila. Iyon ay, mula sa source table na ito:
.. kumuha ng ganito:
Sa madaling salita, kailangan mong ikalat ang badyet sa mga araw ng bawat proyekto at kumuha ng pinasimpleng bersyon ng Gantt chart ng proyekto. Ang paggawa nito gamit ang iyong mga kamay ay mahaba at nakakainip, ang mga macro ay mahirap, ngunit ang Power Query para sa Excel sa ganoong sitwasyon ay nagpapakita ng kapangyarihan nito sa lahat ng kaluwalhatian nito.
kapangyarihan query ay isang add-on mula sa Microsoft na maaaring mag-import ng data sa Excel mula sa halos anumang pinagmulan at pagkatapos ay ibahin ito sa iba't ibang paraan. Sa Excel 2016, ang add-in na ito ay built-in na bilang default, at para sa Excel 2010-2013 maaari itong ma-download mula sa website ng Microsoft at pagkatapos ay mai-install sa iyong PC.
Una, gawing "matalinong" talahanayan ang aming orihinal na talahanayan sa pamamagitan ng pagpili ng command I-format bilang isang talahanayan tab Tahanan (Home — Format bilang Talahanayan) o sa pamamagitan ng pagpindot sa keyboard shortcut Ctrl+T :
Pagkatapos ay pumunta sa tab data (kung mayroon kang Excel 2016) o sa tab kapangyarihan query (kung mayroon kang Excel 2010-2013 at na-install mo ang Power Query bilang isang hiwalay na add-in) at i-click ang Mula sa Table / Range na button. :
Ang aming matalinong talahanayan ay na-load sa editor ng query ng Power Query, kung saan ang unang hakbang ay i-set up ang mga format ng numero para sa bawat column gamit ang mga dropdown sa header ng talahanayan:
Upang kalkulahin ang badyet bawat araw, kailangan mong kalkulahin ang tagal ng bawat proyekto. Upang gawin ito, piliin ang (idiin ang key Ctrl) kolum muna Tapusin, At pagkatapos ay simula at pumili ng isang pangkat Magdagdag ng column – Petsa – Magbawas ng mga araw (Magdagdag ng Column — Petsa — Magbawas ng mga araw):
Ang mga resultang numero ay 1 mas mababa kaysa sa kinakailangan, dahil dapat nating simulan ang bawat proyekto sa unang araw sa umaga at tapusin sa huling araw sa gabi. Samakatuwid, piliin ang resultang column at magdagdag ng unit dito gamit ang command Ibahin ang anyo – Pamantayan – Idagdag (Transform — Standard — Add):
Ngayon, magdagdag tayo ng column kung saan kinakalkula natin ang badyet bawat araw. Upang gawin ito, sa tab Magdagdag ng Haligi Hindi ako naglalaro Custom na column (Custom na Column) at sa lalabas na window, ipasok ang pangalan ng bagong field at ang formula ng pagkalkula, gamit ang mga pangalan ng mga column mula sa listahan:
Ngayon ang pinaka banayad na sandali - lumikha kami ng isa pang kinakalkula na column na may listahan ng mga petsa mula simula hanggang matapos na may isang hakbang na 1 araw. Upang gawin ito, pindutin muli ang pindutan Custom na column (Custom na Column) at gamitin ang built-in na Power Query language na M, na tinatawag Listahan.Mga Petsa:
Ang function na ito ay may tatlong argumento:
- petsa ng pagsisimula - sa aming kaso, ito ay kinuha mula sa hanay simula
- ang bilang ng mga petsang bubuuin – sa aming kaso, ito ang bilang ng mga araw para sa bawat proyekto, na binibilang namin kanina sa column Pagbabawas
- hakbang ng oras – itinakda ng disenyo #tagal(1,0,0,0), ibig sabihin sa wika ng M – isang araw, zero oras, zero minuto, zero segundo.
Pagkatapos ng pag-click sa OK nakakakuha kami ng isang listahan (Listahan) ng mga petsa, na maaaring mapalawak sa mga bagong linya gamit ang pindutan sa header ng talahanayan:
… at nakukuha natin:
Ngayon ang natitira na lang ay i-collapse ang talahanayan, gamit ang mga nabuong petsa bilang mga pangalan para sa mga bagong column. Ang koponan ang may pananagutan para dito. hanay ng detalye (Pivot Column) tab Palitan (Pagbabago):
Pagkatapos ng pag-click sa OK nakakakuha kami ng isang resulta na napakalapit sa ninanais:
Ang Null ay, sa kasong ito, isang analogue ng isang walang laman na cell sa Excel.
Nananatili itong alisin ang mga hindi kinakailangang column at i-unload ang resultang talahanayan sa tabi ng orihinal na data na may command Isara at i-load – Isara at i-load sa… (Isara at I-load — Isara at I-load sa…) tab Tahanan (Tahanan):
Nakukuha namin bilang isang resulta:
Para sa higit na kagandahan, maaari mong i-customize ang hitsura ng mga resultang smart table sa tab Tagagawa (Disenyo): magtakda ng isang istilo ng kulay, huwag paganahin ang mga pindutan ng filter, paganahin ang mga kabuuan, atbp. Bukod pa rito, maaari kang pumili ng isang talahanayan na may mga petsa at paganahin ang pag-highlight ng numero para dito gamit ang kondisyong pag-format sa tab Home — Conditional Formatting — Mga Kulay ng Kulay (Home — Conditional Formatting — Color Scales):
At ang pinakamagandang bahagi ay na sa hinaharap maaari mong ligtas na i-edit ang mga luma o magdagdag ng mga bagong proyekto sa orihinal na talahanayan, at pagkatapos ay i-update ang tamang talahanayan na may mga petsa gamit ang kanang pindutan ng mouse - at uulitin ng Power Query ang lahat ng mga aksyon na ginawa namin nang awtomatiko .
Voilà!
- Gantt chart sa Excel gamit ang conditional formatting
- Kalendaryo ng milestone ng proyekto
- Pagbuo ng Mga Duplicate na Row gamit ang Power Query