Ngatitii uri kuita mapurojekiti akati wandei ane mabhajeti akasiyana uye unoda kuona mari yako kune yega yega. Ndiko, kubva pane ino tafura tafura:
..wana chinhu chakadai:
Mune mamwe mazwi, iwe unofanirwa kuparadzira bhajeti mumazuva ega ega purojekiti uye uwane yakareruka shanduro yeprojekiti Gantt chati. Kuita izvi nemaoko ako kwakareba uye kunofinha, macros akaoma, asi Power Query yeExcel mumamiriro ezvinhu akadaro inoratidza simba rayo mukubwinya kwayo kose.
Simba Query ndeye yekuwedzera kubva kuMicrosoft iyo inogona kupinza data muExcel kubva kune chero kwainotangira uye yozoishandura muboka renzira dzakasiyana. MuExcel 2016, iyi yekuwedzera yakatovakwa-mukati nekukasira, uye yeExcel 2010-2013 inogona kutorwa kubva kuMicrosoft webhusaiti uye yozoiswa paPC yako.
Kutanga, ngatisandure tafura yedu yepakutanga kuita tafura "yakangwara" nekusarudza rairo Fomati setafura Tab Home (Kumba - Fomati seTafura) kana nekudzvanya nzira yekudimbudzira kiibhodhi Ctrl+T :
Wobva waenda kutab Data (kana iwe uine Excel 2016) kana pane tab Simba Query (kana iwe uine Excel 2010-2013 uye iwe wakaisa Power Query seyakasiyana yekuwedzera-mukati) uye tinya Kubva Tafura / Range bhatani. :
Tafura yedu yakangwara inoiswa muPower Query query edhita, uko danho rekutanga nderekumisikidza mafomati ehuwandu hwekoramu yega yega uchishandisa zvinodonha mumusoro wetafura:
Kuti uverenge bhajeti pazuva, unofanirwa kuverenga nguva yeprojekti yega yega. Kuti uite izvi, sarudza (bata pasi kiyi Ctrl) column kutanga pedza, uye ipapo kutanga uye sarudza timu Wedzera koramu - Zuva - Bvisa mazuva (Wedzera Column - Date - Bvisa mazuva):
Nhamba dzinoguma dzave 1 zvishoma pane zvakakodzera, nokuti isu tinofanirwa kutanga chirongwa chega chega pazuva rekutanga mangwanani uye kupedza zuva rekupedzisira manheru. Naizvozvo, sarudza iyo inoguma koramu uye wedzera yuniti kwairi uchishandisa rairo Shandura - Standard - Wedzera (Shandura - Standard - Wedzera):
Zvino ngatiwedzerei koramu kwatinoverenga bhajeti pazuva. Kuti uite izvi, pane tab Wedzera Column Handitambe Custom column (Custom Column) uye pahwindo rinoonekwa, isa zita remunda mutsva uye fomula yekuverenga, uchishandisa mazita emakoramu kubva pane rondedzero:
Iye zvino iyo yakanyanya kujeka nguva - tinogadzira imwe koramu yakaverengerwa ine runyorwa rwemazuva kubva pakutanga kusvika pakupedzisira nedanho rezuva rimwe. Kuti uite izvi, dzvanya bhatani zvakare Custom column (Custom Column) uye shandisa yakavakirwa-mukati Power Query mutauro M, iyo inonzi List.Dates:
Iri basa rine nharo nhatu:
- zuva rekutanga - kwatiri isu, rinotorwa kubva mukoramu kutanga
- nhamba yemazuva achagadzirwa - kwatiri isu, iyi ndiyo nhamba yemazuva epurojekiti yega yega, yatakaverenga pakutanga mumutsara. Kubvisa
- nhanho yenguva - yakatarwa nedhizaini #nguva(1,0,0,0), zvichireva mumutauro weM - rimwe zuva, zero maawa, zero maminitsi, zero seconds.
Mushure mekunge mavharidza OK tinowana rondedzero (Rondedzero) yemazuva, ayo anogona kuwedzerwa kuita mitsetse mitsva uchishandisa bhatani riri mumusoro wetafura:
... uye tinowana:
Iye zvino chasara kuputsa tafura, uchishandisa misi yakagadzirwa semazita emakoroni matsva. Chikwata ndicho chine chekuita neizvi. Detail column (Pivot Column) Tab pindura (Shandura):
Mushure mekunge mavharidza OK tinowana mhedzisiro iri padyo neinodiwa:
Null iri, mune iyi kesi, analogue yesero risina chinhu muExcel.
Inoramba ichibvisa makoramu asina kufanira uye kuburitsa tafura inoguma padivi peiyo yekutanga data nemurairo Vhara uye takura - Vhara uye rodha mukati... (Vhara & Rodha - Vhara & Rodha ku…) Tab Home (Musha):
Isu tinowana semhedzisiro:
Kuti uwedzere runako, unogona kugadzirisa kutaridzika kwematafura anokonzeresa patafura Constructor (Dhizaini): seta chimiro cheruvara rumwe chete, dzima mabhatani ekusefa, gonesa zvikamu, nezvimwe. Pamusoro pezvo, unogona kusarudza tafura ine misi uye wogonesa kuvheneka kwenhamba pairi uchishandisa mafomati ane zvirevo patebhu. Musha — Conditional Formatting — Mavara Makero (Kumba - Conditional Formatting - Ruvara Zvikero):
Uye chakanyanya kunaka ndechekuti mune ramangwana unokwanisa kugadzirisa zvekare zvakachengeteka kana kuwedzera mapurojekiti matsva patafura yekutanga, wobva wagadzirisa tafura yekurudyi nemisi ine bhatani rekurudyi rembeva - uye Power Query inodzokorora zvese zvatakaita otomatiki. .
Voilà!
- Gantt chati muExcel uchishandisa conditional fomati
- Karenda yakakosha yeprojekiti
- Kugadzira Duplicate Rows ine Simba Query