Kendari yefekitari muExcel

Kendari yekugadzira, kureva rondedzero yemazuva, uko mazuva ese ekushanda epamutemo uye mazororo anomakwa zvinoenderana - chinhu chakakosha kune chero mushandisi weMicrosoft Excel. Mukuita, haugone kuita pasina iyo:

• mukuverenga accounting (muhoro, kureba kwesevhisi, zororo ...)
• mune Logistics - yekumisikidza kwakaringana nguva dzekutumira, uchifunga nezvekupera kwevhiki uye mazororo (rangarira yekare "huyai mushure mezororo?")
  
• muhutungamiri hweprojekiti - nokuda kwekufungidzira kwakarurama kwemashoko, uchifunga, zvakare, mazuva ekushanda-asina kushanda
• chero kushandiswa kwemabasa akafanana ZUVA REBASA (ZUVA REBASA) or VABATI VACHENA (NETWORKDAYS), nokuti vanoda rudungwe rwemazororo senharo
• paunenge uchishandisa Nguva Intelligence mabasa (seTOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, nezvimwewo) muPower Pivot uye Power BI.
  
• … nezvimwewo. – mienzaniso yakawanda.
  

Zviri nyore kune avo vanoshanda mumakambani ERP maitiro akadai se1C kana SAP, sezvo karenda yekugadzira inovakwa mavari. Asi zvakadini nevashandisi veExcel?

Iwe unogona, hongu, kuchengeta karenda yakadaro nemaoko. Asi ipapo iwe uchafanirwa kuigadzirisa kanenge kamwe pagore (kana kutonyanya kazhinji, semu "jolly" 2020), kunyatsopinda mukati mekupera kwevhiki kwese, kutamiswa uye kusashanda kwemazuva akagadzirwa nehurumende yedu. Uye wozodzokorora nzira iyi gore rinotevera rega. Kubhowekana.

Ko nezve kupenga zvishoma uye kugadzira "risingaperi" fekitori kalendari muExcel? Imwe inozvigadziridza pachayo, inotora data kubva paInternet uye inogara ichigadzira runyorwa rwemazuva asiri ekushanda ekushandiswa kunotevera mune chero kuverenga? Kuedza?

Kuita izvi, chaizvoizvo, hazvina kuoma zvachose.

Dhata sosi

Mubvunzo mukuru ndewekuti ungawana kupi data? Mukutsvaga kwakakodzera sosi, ndakapfuura nenzira dzinoverengeka:

• Mitemo yepakutanga inoburitswa pawebhusaiti yehurumende muPDF fomati (pano, imwe yacho, semuenzaniso) uye inonyangarika nekukasira - ruzivo runobatsira harugone kudhonzwa kubva mavari.
• Sarudzo inoyedza, pakuona kwekutanga, yaiita se "Open Data Portal yeFederation", uko kune inowirirana data set, asi, pakunyatsoongorora, zvese zvakazove zvinosuruvarisa. Iyo saiti inonetsa zvakanyanya kupinza muExcel, rutsigiro rwehunyanzvi harupindure (kuzviparadzanisa nevamwe?), uye iyo data pachayo yakapera ipapo kwenguva yakareba - karenda yekugadzira ye2020 yakapedzisirwa kuvandudzwa munaNovember 2019 (zvinonyadzisa!) , hongu, haina yedu "coronavirus" uye 'yekuvhota' vhiki ye2020, semuenzaniso.

Ndaodzwa mwoyo nemanyuko ehurumende, ndakatanga kuchera zvisina kufanira. Kune akawanda awo paInternet, asi mazhinji acho, zvakare, haana kukodzera zvachose kupinza muExcel uye kupa kunze kwekarenda yekugadzira muchimiro chemifananidzo yakanaka. Asi hazvisi zvedu kuti tiisungirire pamadziro, handiti?

Uye mukutsvaga, chinhu chinoshamisa chakawanikwa netsaona - saiti http://xmlcalendar.ru/

Pasina zvisingakoshi "frills", nzvimbo iri nyore, yakajeka uye inokurumidza, yakarodzwa kune rimwe basa - kupa munhu wese karenda yekugadzira yegore rinodiwa mu XML format. Zvakanakisa!

Kana, kamwe-kamwe, iwe usiri mukuziva, saka XML ndeye mameseji fomati ine zvemukati zvakamisikidzwa neakakosha . Yakareruka, yakanakira uye inoverengeka nemapurogiramu mazhinji emazuva ano, kusanganisira Excel.

Kungodaro, ndakabata vanyori vesaiti uye vakasimbisa kuti saiti yave iripo kwemakore 7, iyo data pairi inogara ichigadziridzwa (ivo vane kunyange bazi pane github yeizvi) uye havazoivhara. Uye ini handina basa nazvo kuti iwe neni tinotakura data kubva mairi kune chero mapurojekiti edu uye maverengero muExcel. Yakasununguka. Zvinofadza kuziva kuti kuchine vanhu vakadai! Respect!

Inosara kurodha iyi data muExcel uchishandisa iyo Power Query yekuwedzera-mukati (yeshanduro dzeExcel 2010-2013 inogona kutorwa mahara kubva kuMicrosoft webhusaiti, uye mune shanduro dzeExcel 2016 uye nyowani yakatovakwa-mukati nekukasira. )

Iyo logic yezviito ichave seizvi:

1. Isu tinokumbira kudhawunirodha data kubva kune saiti chero kwegore
2. Kushandura chikumbiro chedu kuita basa
3. Isu tinoshandisa basa iri kune rondedzero yemakore ese aripo, kutanga kubva 2013 kusvika kune razvino gore - uye isu tinowana "risingaperi" rekarenda yekugadzira ine otomatiki kugadzirisa. Voila!
   

Danho 1. Pinza karenda kwegore rimwe

Kutanga, takura karenda yekugadzira yegore ripi neripi, semuenzaniso, ra2020. Kuti uite izvi, muExcel, enda kune tab. Data (kana Simba Querykana iwe wakaiisa seyakasiyana yekuwedzera) uye sarudza Kubva paInternet (Kubva paWebhu). Muhwindo rinovhura, isa chinongedzo kune rinoenderana gore, rakakopwa kubva pane saiti:

Mushure mekunge mavharidza OK hwindo rekuona rinoonekwa, raunoda kudzvanya bhatani Shandura Data (Shandura data) or Kuti uchinje data (Gadzirisa data) uye isu tichasvika kuPower Query query editor hwindo, kwatichaenderera mberi nekushanda nedata:

Pakarepo iwe unogona kudzima zvakachengeteka mupaneru yekurudyi Kumbira Parameter (Maseting emubvunzo) danho modified type (Changed Type) Hatimudi.

Tafura iri mukoramu yezororo ine makodhi uye tsananguro yemazuva asiri ekushanda - unogona kuona zvirimo ne "kudonha" mairi kaviri nekudzvanya paizwi rakasvibirira. tafura:

Kuti udzokere shure, iwe uchafanirwa kudzima mupaneru yekurudyi matanho ese akaonekwa kumashure mabviro (Chinhu).

Tafura yechipiri, inogona kuwanikwa nenzira yakafanana, ine chaizvo zvatinoda - mazuva emazuva ese asiri ekushanda:

Inoramba ichigadzira ndiro iyi, inoti:

1. Sefa mazuva ezororo chete (kureva mamwe) nechikamu chechipiri Chimiro:t

2. Bvisa makoramu ese kunze kwekutanga - nekudzvanya-kurudyi pamusoro wekoramu yekutanga uye nekusarudza rairo Dzima mamwe makoramu (Bvisa Mamwe Makoramu):

3. Kamura koramu yekutanga nedot zvakasiyana pamwedzi nemasikati nekuraira Split Column – By Delimiter Tab Kuchinja (Shandura - Split column - By delimiter):

4. Uye pakupedzisira gadzira koramu yakaverengerwa nemazuva akajairwa. Kuti uite izvi, pane tab Kuwedzera chikamu tinya pane bhatani Custom column (Wedzera Column - Custom Column) uye isa inotevera fomula muhwindo rinoonekwa:

=#dated(2020, [#»Chimiro:d.1″], [#»Chimiro:d.2″])

Pano, #date opareta ane nharo nhatu: gore, mwedzi, uye zuva, zvichiteerana. Mushure mekudzvanya pa OK tinowana koramu inodiwa nemazuva akajairwa evhiki, uye todzima makoramu asara sepadanho 2

Danho 2. Kushandura chikumbiro kuita basa

Basa redu rinotevera nderekushandura mubvunzo wakagadzirirwa 2020 kuita basa repasi rose kune chero gore (nhamba yegore ndiyo ichave nharo yayo). Kuti tiite izvi, tinoita zvinotevera:

1. Kuwedzera (kana isati yatowedzerwa) mapaneru Inquiries (Mibvunzo) kuruboshwe muPower Query hwindo:

2. Mushure mekushandura chikumbiro kune basa, kukwanisa kuona matanho anoita chikumbiro uye nyore kuagadzirisa, zvinosuruvarisa, anonyangarika. Naizvozvo, zvine musoro kuita kopi yechikumbiro chedu uye toita frolic nayo, uye wosiya yekutanga ichichengeterwa. Kuti uite izvi, tinya-kurudyi pane yekuruboshwe pane yedu yekarenda chikumbiro uye sarudza iyo Duplicate command.

Kudzvanya-kurudyi zvakare pane inokonzeresa kopi yekarenda (2) ichasarudza rairo Danazve (Rename) uye isa zita idzva - ngazvive, semuenzaniso, fxYear:

3. Isu tinovhura iyo yekubvunza sosi kodhi mumutauro wemukati Power Query (inodanwa muchidimbu "M") tichishandisa murairo. Advanced Editor Tab ongorora(Ona - Yepamberi Mupepeti) uye ita shanduko diki ipapo kushandura chikumbiro chedu kuita basa regore ripi neripi.

Zvanga zviri:

Mushure:

Kana iwe uchifarira ruzivo, saka pano:

• (gore senhamba)=>  - tinozivisa kuti basa redu richava nenhamba imwe nharo - shanduko gore
• Kuisa shanduko gore kune web link munhanho mabviro. Sezvo Power Query isingakubvumidze kunama nhamba uye zvinyorwa, isu tinoshandura nhamba yegore kuita mameseji panhunzi tichishandisa basa. Number.ToText
• Isu tinotsiva shanduko yegore ye2020 mudanho rekupedzisira #”Yakawedzera tsika chinhu«, kwatakagadzira zuva kubva pazvidimbu.
  

Mushure mekunge mavharidza pedza chikumbiro chedu chinova basa:

Danho 3. Ngenisa makarenda kwemakore ese

Chinhu chekupedzisira chasara kuita mubvunzo mukuru wekupedzisira, unozoisa data yemakore ese aripo uye kuwedzera ese akagamuchirwa mazuva ezororo mutafura imwe. Nokuda kweizvi:

1. Isu tinodzvanya pane yekuruboshwe yemubvunzo munzvimbo ine grey isina chinhu ine bhatani rekurudyi uye sarudza zvakateerana Chikumbiro chitsva - Mamwe masosi - Chikumbiro chisina (Mubvunzo mutsva - Kubva kune mamwe masosi - isina mubvunzo):

2. Isu tinofanirwa kugadzira rondedzero yemakore ese atichakumbira makarenda, kureva 2013, 2014 … 2020. Kuti uite izvi, mubhawa remafomula remubvunzo usina chinhu unooneka, isa murairo:

Urongwa:

={NhambaA..NhambaB}

… muPower Query inoburitsa runyoro rwemanhamba kubva kuA kusvika kuB. Semuenzaniso, chirevo

={1..5}

… yaizoburitsa runyorwa rwe1,2,3,4,5.

Zvakanaka, kuti tirege kusungirirwa zvakasimba ku2020, isu tinoshandisa basa racho DateTime.LocalNow() - analogue yeExcel basa TODAY (NHASI) mu Power Query - uye bvisa kubva mairi, zvakare, gore razvino nebasa Date.Gore.

3. Iyo yakaguma seti yemakore, kunyangwe ichitaridzika kunge yakakwana, haisi tafura yeSimba Query, asi chinhu chakakosha - Pamazita (Rondedzero). Asi kuishandura kuita tafura haisi dambudziko: ingobaya bhatani Ku tafura (KuTafura) mukona yepamusoro kuruboshwe:

4. Finish line! Kushandisa basa ratakasika kare fxYear kurondedzero yemakore inozobuda. Kuti uite izvi, pane tab Kuwedzera chikamu dzvanya bhatani Call tsika basa (Wedzera Column - Invoke Custom Function) uye isa nharo yayo chete - iyo column Column1 nekufamba kwemakore:

Mushure mekunge mavharidza OK basa redu fxYear iyo inopinza ichashanda gore rega rega uye isu tichawana koramu iyo sero rega rega rinenge riine tafura ine mazuva emazuva asiri ekushanda (zviri mukati metafura zvinooneka zvakajeka kana ukadzvanya kumashure kwesero padhuze ne shoko tafura):

Inosara yekuwedzera zviri mukati mematafura anested nekudzvanya pane icon nekaviri miseve mumusoro we column. misi (tick Shandisa zita rekutanga rechikamu sechivakashure inogona kubviswa):

... uye mushure mekudzvanya pa OK tinowana zvataida - runyorwa rwemazororo ese kubva 2013 kusvika gore razvino:

Yekutanga, yatove isingakoshi koramu, inogona kudzimwa, uye yechipiri, isa rudzi rwe data zuva (Zuva) mugwaro rekudonhedza riri muchikamu chemusoro:

Mubvunzo wacho pachawo unogona kupihwa zita rechinhu chine musoro kupfuura Chikumbiro1 wobva waisa zvawanikwa pabepa iri muchimiro chesimba re “smart” tafura uchishandisa rairo kuvhara uye kudhawunirodha Tab Home (Kumba - Vhara & Rodha):

Iwe unogona kugadzirisa karenda yakagadzirwa mune ramangwana nekudzvanya-kurudyi patafura kana kubvunza pane yekurudyi kuburikidza nekuraira. Gadziridza & Chengeta. Kana kushandisa bhatani Zorodza zvese Tab Data (Date - Zorora Zvese) kana nzira yekudimbudzira keyboard Ctrl+alt+F5.

Ndizvo zvose.

Iye zvino hauzombodi kutambisa nguva uye kufunga-mafuta kutsvaga nekugadzirisa rondedzero yezororo - ikozvino une "risingaperi" rekugadzira karenda. Chero zvazvingaitika, chero vanyori vesaiti http://xmlcalendar.ru/ vachitsigira vana vavo, izvo, ndinovimba, zvichave kwenguva yakareba kwazvo (ndinotenda kwavari zvakare!).

• Ngenisa bitcoin mwero kuti uwedzere kubva painternet kuburikidza nePower Query
• Kutsvaga zuva rebhizinesi rinotevera uchishandisa WORKDAY basa
  
• Kutsvaga mharadzano yenguva dzemazuva