Kalendaryo sa pabrika sa Excel

Kalendaryo sa produksiyon, ie usa ka lista sa mga petsa, diin ang tanan nga opisyal nga mga adlaw sa pagtrabaho ug mga holiday gimarkahan sumala niana - usa ka kinahanglanon nga butang alang sa bisan kinsa nga tiggamit sa Microsoft Excel. Sa praktis, dili nimo mahimo kung wala kini:

• sa mga kalkulasyon sa accounting (suweldo, gidugayon sa serbisyo, mga bakasyon…)
• sa logistik - alang sa husto nga determinasyon sa mga oras sa pagpadala, nga gikonsiderar ang katapusan sa semana ug mga holiday (hinumdomi ang klasiko nga "moabut pagkahuman sa mga holiday?")
  
• sa pagdumala sa proyekto - alang sa husto nga pagbana-bana sa mga termino, nga gikonsiderar, pag-usab, mga adlaw sa pagtrabaho-dili-trabaho
• bisan unsang paggamit sa mga gimbuhaton sama sa ADLAW SA TRABAHO (WORKDAY) or PURO TRABAHO (NETWORKDAYS), tungod kay nagkinahanglan sila og listahan sa mga holidays isip argumento
• sa paggamit sa Time Intelligence function (sama sa TTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, ug uban pa) sa Power Pivot ug Power BI
  
• … ug uban pa ug uban pa – daghang mga pananglitan.
  

Mas sayon ​​alang niadtong nagtrabaho sa corporate ERP system sama sa 1C o SAP, tungod kay ang produksyon nga kalendaryo gitukod diha kanila. Apan komosta ang mga tiggamit sa Excel?

Siyempre, mahimo nimong tipigan ang ingon nga kalendaryo nga mano-mano. Apan unya kinahanglan nimo nga i-update kini labing menos kausa sa usa ka tuig (o mas kanunay, sama sa "jolly" 2020), mabinantayon nga mosulod sa tanan nga katapusan sa semana, pagbalhin ug mga adlaw nga wala’y trabaho nga giimbento sa atong gobyerno. Ug unya sublion kini nga pamaagi matag sunod tuig. kalaay.

Unsa man ang bahin sa pagkabuang ug paghimo usa ka "perpetual" nga kalendaryo sa pabrika sa Excel? Usa nga nag-update sa iyang kaugalingon, nagkuha sa datos gikan sa Internet ug kanunay nga nagmugna usa ka labing bag-o nga lista sa mga adlaw nga wala’y trabaho alang sa sunod nga paggamit sa bisan unsang mga kalkulasyon? Makatintal?

Sa pagbuhat niini, sa pagkatinuod, dili gayud lisud.

Tinubdan sa datos

Ang panguna nga pangutana kung asa makuha ang datos? Sa pagpangita sa usa ka angay nga tinubdan, miagi ako sa daghang mga kapilian:

• Ang orihinal nga mga mando gipatik sa website sa gobyerno sa format nga PDF (dinhi, usa niini, pananglitan) ug mawala dayon - ang mapuslanon nga kasayuran dili makuha gikan kanila.
• Ang usa ka makatintal nga kapilian, sa unang pagtan-aw, daw mao ang "Open Data Portal sa Federation", diin adunay usa ka katugbang nga set sa datos, apan, sa mas duol nga pagsusi, ang tanan nahimong masulub-on. Ang site dili kaayo kombenyente alang sa pag-import sa Excel, ang teknikal nga suporta wala motubag (nag-inusara sa kaugalingon?), Ug ang datos mismo wala na sa panahon didto sa dugay nga panahon - ang kalendaryo sa produksiyon alang sa 2020 katapusan nga na-update kaniadtong Nobyembre 2019 (kaulawan!) ug , siyempre, wala maglangkob sa among "coronavirus ' ug ang 'pagboto' katapusan sa semana sa 2020, pananglitan.

Nadismaya sa opisyal nga mga tinubdan, nagsugod ako sa pagkalot sa dili opisyal nga mga tinubdan. Adunay daghan kanila sa Internet, apan kadaghanan kanila, pag-usab, hingpit nga dili angay alang sa pag-import sa Excel ug paghatag sa usa ka kalendaryo sa produksyon sa porma sa matahum nga mga hulagway. Pero dili man nato ibitay sa bungbong, di ba?

Ug sa proseso sa pagpangita, usa ka talagsaon nga butang ang aksidenteng nadiskobrehan - ang site http://xmlcalendar.ru/

Kung walay wala kinahanglana nga "mga frills", usa ka yano, gaan ug paspas nga site, gipahait alang sa usa ka buluhaton - aron mahatagan ang tanan usa ka kalendaryo sa produksiyon alang sa gusto nga tuig sa format nga XML. Maayo kaayo!

Kung, sa kalit, wala ka nahibal-an, nan ang XML usa ka format sa teksto nga adunay sulud nga gimarkahan nga espesyal . Gaan, sayon ​​ug mabasa sa kadaghanan sa modernong mga programa, lakip ang Excel.

Sa kaso lang, gikontak nako ang mga tagsulat sa site ug gikumpirma nila nga ang site naglungtad na sa 7 ka tuig, ang datos niini kanunay nga gi-update (naa pa gani sila usa ka sanga sa github alang niini) ug dili nila kini isira. Ug wala gyud ko kahunahuna nga ikaw ug ako nag-load sa datos gikan niini alang sa bisan unsang among mga proyekto ug kalkulasyon sa Excel. Libre na. Nindot nga nahibal-an nga adunay mga tawo nga ingon niini! Pagtahod!

Nagpabilin nga i-load kini nga datos sa Excel gamit ang Power Query add-in (alang sa mga bersyon sa Excel 2010-2013 mahimo kini nga ma-download nga libre gikan sa website sa Microsoft, ug sa mga bersyon sa Excel 2016 ug mas bag-o kini gitukod na pinaagi sa default. ).

Ang lohika sa mga aksyon mahimong ingon sa mosunod:

1. Naghangyo kami nga i-download ang datos gikan sa site alang sa bisan unsang usa ka tuig
2. Gihimo ang among hangyo nga usa ka function
3. Among gipadapat kini nga function sa listahan sa tanang magamit nga mga tuig, sugod sa 2013 ug hangtod sa kasamtangang tuig – ug makakuha kami ug “perpetual” nga kalendaryo sa produksiyon nga adunay awtomatikong pag-update. Voila!
   

Lakang 1. Pag-import ug kalendaryo sulod sa usa ka tuig

Una, i-load ang kalendaryo sa produksiyon alang sa bisan unsang usa ka tuig, pananglitan, alang sa 2020. Aron mahimo kini, sa Excel, adto sa tab Data (o Kusog nga Pagpangutanakung imong gi-install kini isip usa ka bulag nga add-on) ug pilia Gikan sa Internet (Gikan sa Web). Sa bintana nga nagbukas, idikit ang link sa katugbang nga tuig, nga gikopya gikan sa site:

Human sa pag-klik OK usa ka preview window makita, diin kinahanglan nimo nga i-klik ang buton Kinabig Data (Pagbag-o sa datos) or Aron usbon ang datos (I-edit ang datos) ug kita moadto sa Power Query query editor window, diin kita magpadayon sa pagtrabaho uban sa data:

Diha-diha dayon mahimo nimong luwas nga mapapas sa tuo nga panel Pangayo Parameter (Mga setting sa pangutana) lakang giusab nga tipo (Gibag-o nga Uri) Wala namo siya kinahanglana.

Ang lamesa sa kolum sa holidays adunay mga code ug mga paghulagway sa mga adlaw nga wala’y trabaho - makita nimo ang mga sulud niini pinaagi sa "paghulog" niini kaduha pinaagi sa pag-klik sa berde nga pulong Table:

Aron makabalik, kinahanglan nimo nga tangtangon sa tuo nga panel ang tanan nga mga lakang nga nagpakita balik tinubdan (Gigikanan).

Ang ikaduha nga lamesa, nga mahimong ma-access sa parehas nga paagi, adunay eksakto kung unsa ang among kinahanglan - ang mga petsa sa tanan nga wala’y trabaho nga mga adlaw:

Nagpabilin kini sa pagproseso niini nga plato, nga mao:

1. I-filter lang ang mga petsa sa holiday (ie ang mga) sa ikaduhang kolum Hiyas:t

2. Pagtangtang sa tanan nga mga kolum gawas sa una - pinaagi sa pag-right-click sa ulohan sa unang kolum ug pagpili sa sugo Pagtangtang sa ubang mga kolum (Kuhaa ang Ubang mga Kolum):

3. Bahina ang una nga kolum pinaagi sa tulbok nga gilain alang sa bulan ug adlaw nga adunay mando Split Column – Pinaagi sa Delimiter tab kausaban (Transform — Split column — Pinaagi sa delimiter):

4. Ug sa katapusan paghimo usa ka kalkulado nga kolum nga adunay normal nga mga petsa. Aron mahimo kini, sa tab Pagdugang ug kolum i-klik ang buton Pasadya nga kolum (Idugang ang Column — Custom Column) ug pagsulod sa mosunod nga pormula sa bintana nga makita:

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

Dinhi, ang operator sa #date adunay tulo ka argumento: tuig, bulan, ug adlaw, matag usa. Human sa pag-klik sa OK makuha namo ang gikinahanglan nga kolum nga adunay normal nga mga petsa sa katapusan sa semana, ug tangtangon ang nahabilin nga mga kolum sama sa lakang 2

Lakang 2. Pagbalhin sa hangyo ngadto sa usa ka function

Ang among sunod nga buluhaton mao ang pag-convert sa pangutana nga gihimo alang sa 2020 ngadto sa usa ka unibersal nga function alang sa bisan unsang tuig (ang numero sa tuig mao ang argumento niini). Sa pagbuhat niini, atong buhaton ang mosunod:

1. Pagpalapad (kon wala pa gipalapdan) ang panel Mga pangutana (Pangutana) sa wala sa bintana sa Power Query:

2. Pagkahuman sa pag-convert sa hangyo sa usa ka function, ang katakus nga makita ang mga lakang nga naglangkob sa hangyo ug dali nga i-edit kini, sa kasubo, nawala. Busa, makatarunganon nga maghimo usa ka kopya sa among hangyo ug maglingaw-lingaw uban niini, ug ibilin ang orihinal nga gireserba. Aron mahimo kini, pag-right-click sa wala nga pane sa among hangyo sa kalendaryo ug pilia ang Duplicate nga sugo.

Ang pag-right-click pag-usab sa resulta nga kopya sa kalendaryo(2) mopili sa sugo Usba ang ngalan (Ilisan ang ngalan) ug pagsulod ug bag-ong ngalan - pasagdi kini, pananglitan, fxYear:

3. Giablihan namo ang source code sa pangutana sa internal nga Power Query nga pinulongan (kini gitawag nga "M") gamit ang command Abanteng Editor tab Review(Tan-awa — Advanced Editor) ug paghimo og gagmay nga mga pagbag-o didto aron mahimo ang among hangyo nga usa ka function alang sa bisan unsang tuig.

Kini mao ang:

Human sa:

Kung interesado ka sa mga detalye, dinhi:

• (tuig isip numero) =>  – among gideklarar nga ang among function adunay usa ka numeric nga argumento – usa ka variable tuig
• Pag-paste sa variable tuig sa web link sa lakang tinubdan. Tungod kay ang Power Query wala magtugot kanimo sa pagpapilit sa mga numero ug teksto, among gi-convert ang numero sa tuig ngadto sa text on the fly gamit ang function. Numero.ToText
• Gipulihan namon ang variable nga tuig alang sa 2020 sa penultimate nga lakang #"Gidugang custom nga butang«, diin among giumol ang petsa gikan sa mga tipik.
  

Human sa pag-klik Humana ang among hangyo nahimong function:

Lakang 3. Import nga mga kalendaryo para sa tanang tuig

Ang katapusan nga butang nga nahabilin mao ang paghimo sa katapusan nga panguna nga pangutana, nga mag-upload sa datos alang sa tanan nga magamit nga mga tuig ug idugang ang tanan nga nadawat nga mga petsa sa holiday sa usa ka lamesa. Alang niini:

1. Nag-klik kami sa wala nga panel sa pangutana sa usa ka gray nga wanang nga wanang nga adunay tuo nga buton sa mouse ug pilia nga sunud-sunod Bag-ong hangyo – Ubang tinubdan – Walay sulod nga hangyo (Bag-ong Pangutana — Gikan sa ubang tinubdan — Blangko nga pangutana):

2. Kinahanglan namon nga maghimo usa ka lista sa tanan nga mga tuig diin kami mangayo mga kalendaryo, ie 2013, 2014 ... 2020. Aron mahimo kini, sa formula bar sa walay sulod nga pangutana nga makita, isulod ang mando:

Structure:

={NumberA..NumberB}

… sa Power Query nagmugna og listahan sa mga integer gikan sa A ngadto sa B. Pananglitan, ang ekspresyon

={1..5}

… maghimo ug listahan sa 1,2,3,4,5.

Aw, aron dili mahigot nga higpit sa 2020, gigamit namon ang function DateTime.LocalNow() - analogue sa function sa Excel KARON (KARON) sa Power Query - ug kuhaa gikan niini, sa baylo, ang karon nga tuig pinaagi sa function Petsa.Tuig.

3. Ang resulta nga hugpong sa mga tuig, bisan kung kini tan-awon nga igo, dili usa ka lamesa alang sa Power Query, apan usa ka espesyal nga butang - listahan (Listahan). Apan ang pag-convert niini ngadto sa usa ka lamesa dili usa ka problema: i-klik lang ang buton Sa lamesa (Sa lamesa) sa ibabaw nga wala nga suok:

4. Tapuson nga linya! Ang pagpadapat sa function nga atong gibuhat sa sayo pa fxYear ngadto sa resulta nga listahan sa mga tuig. Aron mahimo kini, sa tab Pagdugang ug kolum ipadayon ang buton Tawga ang custom function (Idugang ang Column — I-invoke ang Custom Function) ug ibutang ang bugtong argumento niini - ang kolum Column1 sa mga tuig:

Human sa pag-klik OK atong function fxYear ang pag-import molihok sa baylo alang sa matag tuig ug makakuha kami usa ka kolum diin ang matag cell adunay usa ka lamesa nga adunay mga petsa sa mga adlaw nga wala’y trabaho (ang mga sulud sa lamesa klaro nga makita kung imong i-klik ang background sa cell sunod sa ang pulong Table):

Nagpabilin kini aron mapalapad ang sulud sa mga nested nga mga lamesa pinaagi sa pag-klik sa icon nga adunay doble nga mga pana sa ulohan sa kolum. Petsa (tik Gamita ang orihinal nga ngalan sa kolum isip prefix pwede tangtangon):

… ug human sa pag-klik sa OK makuha namo ang among gusto – lista sa tanang holidays gikan sa 2013 hangtod sa kasamtangang tuig:

Ang una, wala na kinahanglana nga kolum, mahimong mapapas, ug alang sa ikaduha, itakda ang tipo sa datos petsa (Petsa) sa dropdown list sa column heading:

Ang pangutana mismo mahimong usbon ang ngalan sa usa ka butang nga mas makahuluganon kaysa Hangyo1 ug dayon i-upload ang mga resulta sa sheet sa porma sa usa ka dinamikong "smart" nga lamesa gamit ang command pagsira ug pag-download tab Home (Balay — Close & Load):

Mahimo nimong i-update ang gihimo nga kalendaryo sa umaabot pinaagi sa pag-right-click sa lamesa o pangutana sa tuo nga pane pinaagi sa mando I-update ug I-save. O gamita ang buton Refresh tanan tab Data (Petsa — I-refresh Tanan) o keyboard shortcut Ctrl+alt+F5.

Kana tanan.

Karon dili na nimo kinahanglan nga mag-usik ug oras ug panghuna-huna sa pagpangita ug pag-update sa lista sa mga holiday - karon ikaw adunay usa ka "walay katapusan" nga kalendaryo sa produksiyon. Sa bisan unsa nga kaso, basta ang mga tagsulat sa site http://xmlcalendar.ru/ nagsuporta sa ilang mga anak, nga, nanghinaut ko, nga sa usa ka kaayo, kaayo nga panahon (salamat kanila pag-usab!).

• Pag-import sa rate sa bitcoin aron magmalampuson gikan sa internet pinaagi sa Power Query
• Pagpangita sa sunod nga adlaw sa negosyo gamit ang WORKDAY function
  
• Pagpangita sa intersection sa mga agwat sa petsa