Kalendaryo ng pabrika sa Excel

Kalendaryo ng produksyon, ibig sabihin, isang listahan ng mga petsa, kung saan ang lahat ng opisyal na araw ng trabaho at pista opisyal ay minarkahan nang naaayon - isang bagay na talagang kinakailangan para sa sinumang gumagamit ng Microsoft Excel. Sa pagsasagawa, hindi mo magagawa nang wala ito:

• sa mga kalkulasyon ng accounting (suweldo, haba ng serbisyo, bakasyon ...)
• sa logistik - para sa tamang pagpapasiya ng mga oras ng paghahatid, isinasaalang-alang ang mga katapusan ng linggo at pista opisyal (tandaan ang klasikong "pumunta pagkatapos ng pista opisyal?")
  
• sa pamamahala ng proyekto – para sa tamang pagtatantya ng mga termino, isinasaalang-alang, muli, mga araw ng pagtatrabaho-hindi-nagtatrabaho
• anumang paggamit ng mga function tulad ng ARAW NG TRABAHO (WORKDAY) or PURO MANGGAGAWA (NETWORKDAYS), dahil nangangailangan sila ng listahan ng mga holiday bilang argumento
• kapag gumagamit ng mga function ng Time Intelligence (tulad ng TTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, atbp.) sa Power Pivot at Power BI
  
• … atbp. atbp. – maraming halimbawa.
  

Ito ay mas madali para sa mga nagtatrabaho sa mga corporate ERP system tulad ng 1C o SAP, dahil ang kalendaryo ng produksyon ay nakapaloob sa kanila. Ngunit ano ang tungkol sa mga gumagamit ng Excel?

Maaari mong, siyempre, panatilihing manu-mano ang gayong kalendaryo. Ngunit pagkatapos ay kailangan mong i-update ito kahit isang beses sa isang taon (o mas madalas, tulad ng sa "jolly" 2020), maingat na pumasok sa lahat ng katapusan ng linggo, paglilipat at mga araw na walang pasok na inimbento ng ating gobyerno. At pagkatapos ay ulitin ang pamamaraang ito bawat susunod na taon. Pagkabagot.

Paano ang tungkol sa medyo mabaliw at gumawa ng isang "perpetual" na kalendaryo ng pabrika sa Excel? Isa na nag-a-update mismo, kumukuha ng data mula sa Internet at palaging bumubuo ng napapanahon na listahan ng mga hindi pasok na araw para sa kasunod na paggamit sa anumang mga kalkulasyon? Nakatutukso?

Upang gawin ito, sa katunayan, ay hindi mahirap.

Pinanggalingan ng Datos

Ang pangunahing tanong ay kung saan kukuha ng data? Sa paghahanap ng angkop na mapagkukunan, dumaan ako sa maraming mga pagpipilian:

• Ang mga orihinal na kautusan ay nai-publish sa website ng gobyerno sa format na PDF (dito, isa sa mga ito, halimbawa) at agad-agad na nawawala – ang kapaki-pakinabang na impormasyon ay hindi maaaring makuha mula sa kanila.
• A tempting option, at first glance, seemed to be the “Open Data Portal of the Federation”, where there is a corresponding data set, but, upon closer examination, everything turned out to be sad. The site is terribly inconvenient for importing into Excel, technical support does not respond (self-isolated?), and the data itself is outdated there for a long time – the production calendar for 2020 was last updated in November 2019 (disgrace!) and, of course, does not contain our “coronavirus ‘ and the ‘voting’ weekend of 2020, for example.

Dahil sa pagkadismaya sa mga opisyal na mapagkukunan, nagsimula akong maghukay ng mga hindi opisyal. Marami sa kanila sa Internet, ngunit karamihan sa kanila, muli, ay ganap na hindi angkop para sa pag-import sa Excel at magbigay ng isang kalendaryo ng produksyon sa anyo ng mga magagandang larawan. Pero hindi naman natin ito isabit sa dingding diba?

At sa proseso ng paghahanap, isang kahanga-hangang bagay ang hindi sinasadyang natuklasan - ang site http://xmlcalendar.ru/

Nang walang mga hindi kinakailangang "frills", isang simple, magaan at mabilis na site, na hinahasa para sa isang gawain - upang bigyan ang lahat ng kalendaryo ng produksyon para sa nais na taon sa XML na format. Magaling!

Kung, bigla, wala kang alam, kung gayon ang XML ay isang format ng teksto na may nilalamang minarkahan ng espesyal . Magaan, maginhawa at nababasa ng karamihan sa mga modernong programa, kabilang ang Excel.

Kung sakali, nakipag-ugnayan ako sa mga may-akda ng site at kinumpirma nila na ang site ay umiiral sa loob ng 7 taon, ang data dito ay patuloy na ina-update (mayroon pa silang sangay sa github para dito) at hindi nila ito isasara. At wala akong pakialam na ikaw at ako ay naglo-load ng data mula rito para sa alinman sa aming mga proyekto at kalkulasyon sa Excel. Ay libre. Nakakatuwang malaman na may mga ganito pa rin! Respeto!

Nananatili itong i-load ang data na ito sa Excel gamit ang Power Query add-in (para sa mga bersyon ng Excel 2010-2013 maaari itong ma-download nang libre mula sa website ng Microsoft, at sa mga bersyon ng Excel 2016 at mas bago ito ay built-in na bilang default. ).

Ang lohika ng mga aksyon ay ang mga sumusunod:

1. Gumagawa kami ng kahilingan na mag-download ng data mula sa site para sa anumang isang taon
2. Ginagawang function ang aming kahilingan
3. Inilapat namin ang function na ito sa listahan ng lahat ng available na taon, simula sa 2013 at hanggang sa kasalukuyang taon – at nakakakuha kami ng "perpetual" na kalendaryo ng produksyon na may awtomatikong pag-update. Voila!
   

Hakbang 1. Mag-import ng kalendaryo para sa isang taon

Una, i-load ang kalendaryo ng produksyon para sa anumang isang taon, halimbawa, para sa 2020. Upang gawin ito, sa Excel, pumunta sa tab data (O kapangyarihan querykung na-install mo ito bilang isang hiwalay na add-on) at piliin Mula sa internet (Mula sa Web). Sa window na bubukas, i-paste ang link sa kaukulang taon, na kinopya mula sa site:

Pagkatapos ng pag-click sa OK lilitaw ang isang preview window, kung saan kailangan mong i-click ang button I-convert ang Data (Ibahin ang anyo ng data) or Upang baguhin ang data (I-edit ang data) at mapupunta tayo sa window ng editor ng query ng Power Query, kung saan magpapatuloy tayong magtrabaho kasama ang data:

Kaagad maaari mong ligtas na tanggalin sa kanang panel Humiling ng Mga Parameter (Mga setting ng query) hakbang binagong uri (Binago ang Uri) Hindi natin siya kailangan.

Ang talahanayan sa column ng holidays ay naglalaman ng mga code at paglalarawan ng mga araw na walang pasok – makikita mo ang mga nilalaman nito sa pamamagitan ng “pagbagsak” nito nang dalawang beses sa pamamagitan ng pag-click sa berdeng salita mesa:

Upang bumalik, kakailanganin mong tanggalin sa kanang panel ang lahat ng mga hakbang na lumitaw pabalik sa pinagmulan (Pinagmulan).

Ang pangalawang talahanayan, na maaaring ma-access sa katulad na paraan, ay naglalaman ng eksakto kung ano ang kailangan namin - ang mga petsa ng lahat ng hindi pasok na araw:

Ito ay nananatiling iproseso ang plato na ito, lalo na:

1. I-filter lamang ang mga petsa ng holiday (ibig sabihin, ang mga) ayon sa pangalawang column Katangian:t

2. Tanggalin ang lahat ng column maliban sa una – sa pamamagitan ng pag-right click sa heading ng unang column at pagpili sa command Tanggalin ang iba pang mga column (Alisin ang Iba Pang Mga Column):

3. Hatiin ang unang column sa pamamagitan ng tuldok nang hiwalay para sa buwan at araw na may utos Hatiin ang Haligi – Ayon sa Delimiter tab Pagbabagong-anyo (Transform — Hatiin ang hanay — Sa pamamagitan ng delimiter):

4. At sa wakas ay lumikha ng isang kinakalkula na hanay na may mga normal na petsa. Upang gawin ito, sa tab Pagdaragdag ng column i-click ang pindutan Custom na column (Magdagdag ng Column — Custom Column) at ipasok ang sumusunod na formula sa lalabas na window:

=#napetsahan(2020, [#»Attribute:d.1″], [#»Attribute:d.2″])

Dito, ang operator ng #date ay may tatlong argumento: taon, buwan, at araw, ayon sa pagkakabanggit. Pagkatapos mag-click sa OK nakukuha namin ang kinakailangang column na may mga normal na petsa ng katapusan ng linggo, at tanggalin ang natitirang mga column tulad ng sa hakbang 2

Hakbang 2. Ginagawang function ang kahilingan

Ang aming susunod na gawain ay i-convert ang query na ginawa para sa 2020 sa isang unibersal na function para sa anumang taon (ang numero ng taon ang magiging argument nito). Upang gawin ito, ginagawa namin ang sumusunod:

1. Pinapalawak (kung hindi pa pinalawak) ang panel Katanungan (Mga Tanong) sa kaliwa sa Power Query window:

2. Matapos i-convert ang kahilingan sa isang function, ang kakayahang makita ang mga hakbang na bumubuo sa kahilingan at madaling i-edit ang mga ito, sa kasamaang-palad, ay nawawala. Samakatuwid, makatuwirang gumawa ng kopya ng aming kahilingan at magsayaw na kasama nito, at iwanan ang orihinal na nakalaan. Upang gawin ito, i-right-click sa kaliwang pane sa aming kahilingan sa kalendaryo at piliin ang Duplicate na command.

Ang pag-right-click muli sa resultang kopya ng kalendaryo(2) ay pipiliin ang command Bigyan ng ibang pangalan (Palitan ang pangalan) at maglagay ng bagong pangalan - hayaan ito, halimbawa, fxYear:

3. Binubuksan namin ang source code ng query sa panloob na wika ng Power Query (ito ay mabilis na tinatawag na "M") gamit ang command Advanced na Editor tab Pagsusuri(Tingnan — Advanced na Editor) at gumawa ng maliliit na pagbabago doon upang gawing function ang aming kahilingan para sa anumang taon.

Ito ay:

Pagkatapos:

Kung interesado ka sa mga detalye, dito:

• (taon bilang numero)=>  – ipinapahayag namin na ang aming function ay magkakaroon ng isang numerong argumento – isang variable taon
• Pag-paste ng variable taon sa web link sa hakbang pinagmulan. Dahil hindi ka pinapayagan ng Power Query na mag-glue ng mga numero at text, kino-convert namin ang numero ng taon sa text on the fly gamit ang function. Numero.ToText
• Pinapalitan namin ang variable ng taon para sa 2020 sa penultimate na hakbang #"Nagdagdag ng custom na bagay«, kung saan nabuo namin ang petsa mula sa mga fragment.
  

Pagkatapos ng pag-click sa Tapusin ang aming kahilingan ay nagiging isang function:

Hakbang 3. Mag-import ng mga kalendaryo para sa lahat ng taon

Ang huling bagay na natitira ay gawin ang huling pangunahing query, na mag-a-upload ng data para sa lahat ng magagamit na taon at idagdag ang lahat ng natanggap na petsa ng holiday sa isang talahanayan. Para dito:

1. Nag-click kami sa kaliwang panel ng query sa isang kulay abong walang laman na espasyo gamit ang kanang pindutan ng mouse at pumili nang sunud-sunod Bagong kahilingan – Iba pang mapagkukunan – Walang laman na kahilingan (Bagong Query — Mula sa iba pang mapagkukunan — Blangkong query):

2. Kailangan naming bumuo ng isang listahan ng lahat ng mga taon kung saan kami ay hihiling ng mga kalendaryo, ibig sabihin, 2013, 2014 … 2020. Upang gawin ito, sa formula bar ng walang laman na query na lilitaw, ipasok ang command:

Istraktura:

={NumberA..NumberB}

… sa Power Query ay bumubuo ng isang listahan ng mga integer mula A hanggang B. Halimbawa, ang expression

={1..5}

… gagawa ng listahan ng 1,2,3,4,5.

Buweno, upang hindi matali nang mahigpit sa 2020, ginagamit namin ang function DateTime.LocalNow() – analogue ng Excel function NGAYONG ARAW (NGAYON) sa Power Query – at kunin mula dito, sa turn, ang kasalukuyang taon sa pamamagitan ng function Petsa.Taon.

3. Ang resultang hanay ng mga taon, bagama't mukhang sapat na ito, ay hindi isang talahanayan para sa Power Query, ngunit isang espesyal na bagay - listahan (Listahan). Ngunit ang pag-convert nito sa isang talahanayan ay hindi isang problema: i-click lamang ang pindutan Sa mesa (Sa Table) sa kaliwang sulok sa itaas:

4. Tapusin ang linya! Paglalapat ng function na ginawa namin kanina fxYear sa resultang listahan ng mga taon. Upang gawin ito, sa tab Pagdaragdag ng column pindutin ang pindutan Tumawag sa custom na function (Magdagdag ng Column — I-invoke ang Custom na Function) at itakda ang tanging argumento nito – ang column Column1 sa paglipas ng mga taon:

Pagkatapos ng pag-click sa OK ating tungkulin fxYear ang pag-import ay gagana nang sunod-sunod para sa bawat taon at makakakuha kami ng isang column kung saan ang bawat cell ay maglalaman ng isang talahanayan na may mga petsa ng mga araw na hindi gumagana (ang mga nilalaman ng talahanayan ay malinaw na nakikita kung nag-click ka sa background ng cell sa tabi ng ang salita mesa):

Nananatili itong palawakin ang mga nilalaman ng mga nested table sa pamamagitan ng pag-click sa icon na may double arrow sa header ng column Petsa (lagyan ng tsek Gamitin ang orihinal na pangalan ng column bilang prefix maaari itong alisin):

… at pagkatapos mag-click sa OK nakukuha namin ang gusto namin – isang listahan ng lahat ng holiday mula 2013 hanggang sa kasalukuyang taon:

Ang una, na hindi na kailangan na column, ay maaaring tanggalin, at para sa pangalawa, itakda ang uri ng data petsa (Petsa) sa dropdown list sa column heading:

Ang query mismo ay maaaring palitan ng pangalan sa isang bagay na mas makabuluhan kaysa sa Kahilingan1 at pagkatapos ay i-upload ang mga resulta sa sheet sa anyo ng isang dynamic na "smart" na talahanayan gamit ang command isara at i-download tab Tahanan (Tahan — Isara at I-load):

Maaari mong i-update ang nilikha na kalendaryo sa hinaharap sa pamamagitan ng pag-right click sa talahanayan o query sa kanang pane sa pamamagitan ng command I-update at I-save. O gamitin ang pindutan I-refresh lahat tab data (Petsa — I-refresh Lahat) o keyboard shortcut Ctrl+Alt+F5.

Iyon lang.

Ngayon ay hindi mo na kailangang mag-aksaya ng oras at pag-isipang lakas sa paghahanap at pag-update ng listahan ng mga holiday - mayroon ka na ngayong "panghabang-buhay" na kalendaryo ng produksyon. Sa anumang kaso, hangga't sinusuportahan ng mga may-akda ng site http://xmlcalendar.ru/ ang kanilang mga supling, na, umaasa ako, ay magiging para sa isang napaka, napakatagal na panahon (salamat muli sa kanila!).

• Mag-import ng bitcoin rate para maging excel mula sa internet sa pamamagitan ng Power Query
• Paghahanap sa susunod na araw ng negosyo gamit ang function na WORKDAY
  
• Paghahanap ng intersection ng mga agwat ng petsa