Mukati
Maitiro mazhinji ebhizinesi (uye kunyangwe mabhizinesi ese) muhupenyu huno anosanganisira kuzadzikiswa kwemirairo nenhamba shoma yevaiti nenguva yakatarwa. Kuronga mumamiriro ezvinhu akadaro kunoitika, sezvavanotaura, "kubva pakarenda" uye kazhinji pane chikonzero chekuendesa zviitiko zvakarongwa mairi (mirairo, misangano, kuendesa) kuMicrosoft Excel - kuti iwedzere kuongororwa nemafomati, matafura epivot, charting, etc.
Ehe, ndinoda kuita kuendesa kwakadaro kwete nekukopa benzi (izvo zvisina kuoma), asi nekuvandudza otomatiki data kuitira kuti mune ramangwana shanduko dzese dzakaitwa kukarenda uye maodha matsva panhunzi aonekwe mukati. Excel. Iwe unogona kuita kupinza kwakadaro mukati memaminitsi mashoma uchishandisa iyo Power Query yekuwedzera yakavakirwa muMicrosoft Excel, kutanga kubva ku2016 vhezheni (yeExcel 2010-2013, inogona kutorwa kubva pawebhusaiti yeMicrosoft uye kuisirwa zvakasiyana kubva pane chinongedzo) .
Ngatitii isu tinoshandisa iyo yemahara Google Calendar kuronga, umo ini, kuitira nyore, ndakagadzira yakaparadzana karenda (bhatani rine chiratidzo chekuwedzera mukona yezasi yekurudyi pedyo ne. Mamwe makarenda) ine musoro basa. Pano tinoisa maodha ese anoda kupedzwa uye kuendeswa kune vatengi kumakero avo:
Nekudzvanya-kaviri chero odha, unogona kuona kana kugadzirisa zvaro:
Cherechedza kuti:
- Zita rechiitiko ndere menejauyo anozadzisa kurongeka uku (Elena) uye Order number
- Yakaratidzwa adhiresi dhirivahari
- Chinyorwa chine (mumitsetse yakaparadzana, asi mune chero kurongeka) maodha paramita: rudzi rwekubhadhara, huwandu, zita remutengi, nezvimwe mufomati. Parameter=Kukosha.
Kuti zvijekese, mirairo yemaneja wega wega inoratidzwa muvara ravo, kunyangwe izvi zvisingakodzeri.
Danho 1. Wana chinongedzo kuGoogle Calendar
Kutanga isu tinoda kuwana webhu link kune yedu yekuodha karenda. Kuti uite izvi, tinya bhatani rine madonhwe matatu Karenda Sarudzo Dzinoshanda pedyo nezita rekarenda uye sarudza murairo Zvirongwa uye Kugovera:
Muhwindo rinovhurika, unogona, kana uchida, kuita kuti karenda rive pachena kana kuvhura kupinda kwairi kune vamwe vashandisi. Isu tinodawo chinongedzo chekuwana yakavanzika kukarenda mune iCal fomati:
Danho 2. Rodha data kubva pakarenda kupinda Power Query
Zvino vhura Excel uye pane tab Data (kana iwe uine Excel 2010-2013, ipapo pane tab Simba Query) sarudza murairo Kubva paInternet (Data - Kubva paInternet). Wobva waisa nzira yakakopwa kukarenda uye tinya OK.
Iyo iCal Power Query haizive iyo fomati, asi zviri nyore kubatsira. Chaizvoizvo, iCal ifaira remavara rakajeka rine colon se delimiter, uye mukati mayo rinotaridzika seizvi:
Saka iwe unogona kungobaya-kurudyi pane icon yefaira rakadhawunirodha uye sarudza iyo fomati iri padyo mune zvinoreva CSV - uye yedu data nezve ese maodha ichaiswa muPower Query query edhita uye yakakamurwa kuita makoramu maviri nekoloni:
Kana iwe ukanyatsotarisisa, unogona kuona zvakajeka kuti:
- Ruzivo nezve chiitiko chega chega (kurongeka) akaiswa mubhuroko kutanga nezwi rokuti BEGIN uye kupera na END.
- Nguva dzekutanga nekupera dzinochengetwa mutambo dzakanzi DTSTART uye DTEND.
- Kero yekutumira ndeye LOCATION.
- Odha yetsamba – DESCRIPTION ndima.
- Zita rechiitiko (zita remaneja uye nhamba yekuraira) - SUMMARY munda.
Inoramba ichiburitsa ruzivo urwu runobatsira uye nekuishandura kuita tafura iri nyore.
Danho 3. Shandura kuMaonero Akajairika
Kuti uite izvi, ita zvinotevera ketani yezviito:
- Ngatibvisei mitsetse ye7 yatisingade pamberi pekutanga BEGIN kuraira Musha — Delete Rows — Delete Top Rows (Kumba - Bvisa mitsara - Bvisa mitsara yepamusoro).
- Sefa nechikamu Column1 mitsetse ine minda yatinoda: DTSTART, DTEND, DESCRIPTION, LOCATION uye SUMMARY.
- Pa Advanced tabo Kuwedzera chikamu sarudza Index column (Wedzera chikamu - Index column)kuwedzera mutsara wenhamba nhamba kune yedu data.
- Ikoko pane tab. Kuwedzera chikamu sarudza timu Conditional column (Wedzera koramu - Conditional column) uye pakutanga kwebhuroka yega yega (kurongeka) tinoratidza kukosha kweiyo index:
- Zadza masero asina chinhu mumutsara unobuda misanekudzvanya-kurudyi pane zita rayo uye kusarudza rairo Zadza – Pasi (Zadza - Pasi).
- Bvisa koramu isingakoshi indekisi.
- Sarudza chikamu Column1 uye ita convolution ye data kubva pa column Column2 kushandisa murairo Shandura – Pivot Column (Shandura - Pivot column). Iva nechokwadi chekusarudza mune zvingasarudzwa Usaunganidza (Usabatanidza)kuitira kuti pasave nebasa remasvomhu rinoshandiswa kune data:
- Mune inokonzeresa maviri-dimensional (muchinjiko) tafura, bvisa mabhanhire kumashure mukoramu yekero (tinya-kurudyi pamusoro wekoramu - Kutsiva tsika) uye bvisa koramu isingakoshi misa.
- Kushandura zviri mukati memakoramu DTSTART и DTEND munguva yakazara-nguva, uchivasimbisa, sarudza pane tab Shandura – Date – Run Analysis (Shandura - Date - Parse). Zvadaro tinogadzirisa kodhi mubhari yefomula nekutsiva basa Date.Kubva on DateTime.Kubvakuitira kuti usarase kukosha kwenguva:
- Zvadaro, nekudzvanya-kurudyi pamusoro, tinotsemura mbiru KUTSANANGURWA ine kurongeka parameters by separator - chiratidzo n, asi panguva imwe chete, mumiganhu, isu tichasarudza kupatsanurwa mumitsara, uye kwete mumakoramu:
- Zvekare, tinogovanisa koramu inoguma kuita maviri akaparadzana - parameter uye kukosha, asi neakaenzana chiratidzo.
- Kusarudza chikamu TSANANGURO.1 ita convolution, sezvatakaita pakutanga, nemurairo Shandura – Pivot Column (Shandura - Pivot column). Iko kukosha kwekoramu mune iyi kesi ichave iyo column ine parameter values− TSANANGURO.2 Iva nechokwadi chekusarudza basa mune parameters Usaunganidza (Usabatanidza):
- Izvo zvinosara kuseta mafomati emakoramu ese uye kuatumidza zita sezvaunoda. Uye iwe unogona kurodha mhinduro kudzokera kuExcel nemurairo Imba - Vhara uye Rodha - Vhara uye Rodha mukati... (Kumba - Vhara & Rodha - Vhara & Rodha ku…)
Uye heino runyorwa rwedu rwemaodha akaiswa muExcel kubva kuGoogle Calendar:
Mune ramangwana, pakuchinja kana kuwedzera maodha matsva kukarenda, zvinongokwana kugadzirisa chikumbiro chedu nemurairo. Dhata - Zorora Zvese (Data - Zorora Zvese).
- Karenda yefekitari muExcel yakagadziridzwa kubva painternet kuburikidza nePower Query
- Kushandura mbiru kuita tafura
- Gadzira dhatabhesi muExcel