Bari mu ce kuna gudanar da ayyuka da yawa tare da kasafin kuɗi daban-daban kuma kuna son ganin farashin ku na kowane ɗayansu. Wato daga wannan tebur mai tushe:
.. sami wani abu kamar haka:
A takaice dai, kuna buƙatar yada kasafin kuɗi a cikin kwanakin kowane aikin kuma ku sami sauƙaƙan sigar aikin Gantt ginshiƙi. Yin wannan tare da hannunka yana da tsayi kuma mai ban sha'awa, macros suna da wuyar gaske, amma Query Query don Excel a cikin irin wannan yanayin yana nuna ikonsa a cikin ɗaukakarsa.
Tambayar .arfi wani ƙari ne daga Microsoft wanda zai iya shigo da bayanai zuwa cikin Excel daga kusan kowane tushe sannan kuma ya canza shi ta hanyoyi daban-daban. A cikin Excel 2016, wannan add-in an riga an gina shi ta tsohuwa, kuma don Excel 2010-2013 ana iya sauke shi daga gidan yanar gizon Microsoft sannan a sanya shi akan PC ɗin ku.
Da farko, bari mu juya teburin mu na asali zuwa tebur “mai wayo” ta zaɓin umarni Tsara azaman tebur tab Gida (Gida - Tsarin azaman Tebur) ko ta hanyar latsa gajeriyar hanyar madannai Ctrl+T :
Sannan je zuwa shafin data (idan kuna da Excel 2016) ko akan shafin Tambayar .arfi (idan kuna da Excel 2010-2013 kuma kun shigar da Query Query azaman ƙarawa daban) kuma danna maɓallin Daga Tebur / Range. :
Teburinmu mai wayo yana lodawa cikin editan tambaya ta Power, inda mataki na farko shine saita tsarin lamba ga kowane shafi ta amfani da zazzagewa a cikin taken tebur:
Don lissafin kasafin kuɗi a kowace rana, kuna buƙatar ƙididdige tsawon lokacin kowane aikin. Don yin wannan, zaɓi (riƙe ƙasa da maɓallin Ctrl) shafi na farko Gama, sai me Fara kuma zaɓi ƙungiya Ƙara shafi - Kwanan wata - Rage kwanaki (Ƙara Shagon - Kwanan wata - Rage kwanaki):
Lambobin da aka samu sun kasance ƙasa da 1 da ake buƙata, saboda ya kamata mu fara kowane aiki a ranar farko da safe kuma mu ƙare a ranar ƙarshe da yamma. Don haka, zaɓi ginshiƙin da ke fitowa kuma ƙara raka'a gare shi ta amfani da umarnin Canza - Daidaitawa - Ƙara (Canza - Standard - Ƙara):
Yanzu bari mu ƙara wani shafi inda muke lissafin kasafin kuɗi kowace rana. Don yin wannan, a kan tab Colara Shafi Ba na wasa Rukunin al'ada (Shafin Custom) kuma a cikin taga da ya bayyana, shigar da sunan sabon filin da tsarin lissafin, ta amfani da sunayen ginshiƙan daga jerin:
Yanzu mafi kyawun lokacin - mun ƙirƙiri wani ginshiƙi ƙididdiga tare da jerin kwanakin daga farkon zuwa ƙarshe tare da mataki na kwana 1. Don yin wannan, sake danna maɓallin Rukunin al'ada (Shafin Custom) kuma yi amfani da ginanniyar Harshen Tambayar Wuta M, wanda ake kira Jerin. Kwanaki:
Wannan aikin yana da dalilai guda uku:
- ranar farawa - a cikin yanayinmu, an ɗauke shi daga ginshiƙi Fara
- adadin kwanakin da za a samar - a cikin yanayinmu, wannan shine adadin kwanakin kowane aikin, wanda muka ƙidaya a baya a cikin shafi. Ragewa
- mataki mataki - saita ta zane #tsawon lokaci (1,0,0,0), ma'ana a cikin yaren M - rana ɗaya, sa'o'i sifili, mintuna sifili, daƙiƙa sifili.
Bayan danna kan OK muna samun jerin (Jeri) na kwanakin, waɗanda za a iya faɗaɗa su zuwa sababbin layi ta amfani da maɓallin a cikin taken tebur:
…kuma muna samun:
Yanzu abin da ya rage shi ne rushe teburin, ta amfani da kwanakin da aka samar azaman sunayen sabbin ginshiƙai. Kungiyar ce ke da alhakin hakan. shafi mai cikakken bayani (Shafin Pivot) tab maida (canza):
Bayan danna kan OK muna samun sakamako kusa da wanda ake so:
Null shine, a wannan yanayin, analogue na tantanin halitta mara komai a cikin Excel.
Ya rage don cire ginshiƙan da ba dole ba kuma zazzage tebur da aka samu kusa da ainihin bayanan tare da umarnin Rufe kuma ɗauka - Rufe kuma ɗauka a cikin… (Rufe & Load - Kusa & Loda zuwa…) tab Gida (Gida):
Mun samu a sakamakon:
Don mafi kyawun kyan gani, zaku iya siffanta bayyanar da sakamakon tebur mai wayo akan shafin Constructor (Zane): saita salon launi guda ɗaya, kashe maɓallan tacewa, ba da damar jimloli, da sauransu. Bugu da ƙari, za ku iya zaɓar tebur tare da kwanakin da ba da damar nuna lamba don shi ta yin amfani da tsarin yanayi akan shafin. Gida - Tsarin Yanayi - Ma'aunin launi (Gida - Tsarin Yanayi - Ma'aunin Launi):
Kuma mafi kyawun sashi shine cewa a nan gaba zaku iya gyara tsofaffin amintattu ko ƙara sabbin ayyuka zuwa tebur na asali, sannan sabunta teburin daidai tare da kwanan wata tare da maɓallin linzamin kwamfuta na dama - kuma Query Query zai maimaita duk ayyukan da muka yi ta atomatik. .
Voilà!
- Gantt ginshiƙi a cikin Excel ta amfani da tsari na yanayi
- Kalanda mai mahimmanci na aikin
- Samar da Kwafin Layukan tare da Tambayar Wuta