Përmbajtje
Kalendari i prodhimit, gjegjësisht një listë datash, ku të gjitha ditët zyrtare të punës dhe festat janë shënuar në përputhje me rrethanat – një gjë absolutisht e nevojshme për çdo përdorues të Microsoft Excel. Në praktikë, nuk mund të bëni pa të:
- në llogaritjet e kontabilitetit (paga, kohëzgjatja e shërbimit, pushimet ...)
- në logjistikë - për përcaktimin e saktë të kohës së dorëzimit, duke marrë parasysh fundjavat dhe festat (kujtoni klasiken "hajde pas pushimeve?")
- në menaxhimin e projektit - për vlerësimin e saktë të kushteve, duke marrë parasysh, përsëri, ditët e punës-jo pune
- çdo përdorim i funksioneve si DITË E PUNËS (DITË PUNE) or PUNËTORËT E PASTER (NETWORKDAYS), sepse kërkojnë si argument një listë pushimesh
- kur përdorni funksionet Time Intelligence (si TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, etj.) në Power Pivot dhe Power BI
- … etj etj. – shumë shembuj.
Është më e lehtë për ata që punojnë në sistemet ERP të korporatave si 1C ose SAP, pasi kalendari i prodhimit është i integruar në to. Por çfarë ndodh me përdoruesit e Excel?
Ju, sigurisht, mund ta mbani një kalendar të tillë me dorë. Por më pas do t'ju duhet ta përditësoni të paktën një herë në vit (apo edhe më shpesh, si në vitin 2020 "të gëzuar", duke hyrë me kujdes në të gjitha fundjavat, transfertat dhe ditët jopune të shpikura nga qeveria jonë. Dhe pastaj përsëriteni këtë procedurë çdo vit tjetër. Mërzia.
Si thua të çmendesh pak dhe të bësh një kalendar “të përhershëm” të fabrikës në Excel? Një që përditëson veten, merr të dhëna nga Interneti dhe gjeneron gjithmonë një listë të përditësuar të ditëve jo-pune për përdorim të mëvonshëm në ndonjë llogaritje? Joshëse?
Për ta bërë këtë, në fakt, nuk është aspak e vështirë.
Burim i të dhënave
Pyetja kryesore është se ku mund të merrni të dhënat? Në kërkim të një burimi të përshtatshëm, kalova nëpër disa opsione:
- Dekretet origjinale publikohen në faqen e internetit të qeverisë në formatin PDF (këtu, një prej tyre, për shembull) dhe zhduken menjëherë - informacione të dobishme nuk mund të nxirren prej tyre.
- Një opsion joshëse, në pamje të parë, dukej se ishte "Portali i të dhënave të hapura të Federatës", ku ka një grup të dhënash përkatëse, por, pas ekzaminimit më të afërt, gjithçka doli e trishtueshme. Sajti është jashtëzakonisht i papërshtatshëm për import në Excel, mbështetja teknike nuk përgjigjet (vetë-izoluar?), dhe vetë të dhënat janë të vjetruara atje për një kohë të gjatë - kalendari i prodhimit për 2020 u përditësua për herë të fundit në nëntor 2019 (turp!) dhe , natyrisht, nuk përmban “koronavirusin” tonë dhe fundjavën e “votimit” të vitit 2020, për shembull.
I zhgënjyer nga burimet zyrtare, fillova të gërmoj ato jozyrtare. Ka shumë prej tyre në internet, por shumica e tyre, përsëri, janë plotësisht të papërshtatshme për t'u importuar në Excel dhe japin një kalendar prodhimi në formën e fotografive të bukura. Por nuk na takon ne ta varim në mur, apo jo?
Dhe në procesin e kërkimit, një gjë e mrekullueshme u zbulua aksidentalisht - faqja http://xmlcalendar.ru/
Pa “përparësi” të panevojshme, një faqe e thjeshtë, e lehtë dhe e shpejtë, e mprehur për një detyrë – për t'i dhënë të gjithëve një kalendar prodhimi për vitin e dëshiruar në formatin XML. E shkëlqyeshme!
Nëse, papritmas, nuk jeni në dijeni, atëherë XML është një format teksti me përmbajtje të shënuar me të veçanta
Për çdo rast, kontaktova me autorët e faqes dhe ata konfirmuan që faqja ekziston për 7 vjet, të dhënat në të përditësohen vazhdimisht (madje kanë një degë në github për këtë) dhe nuk do ta mbyllin. Dhe nuk më shqetëson aspak që ju dhe unë ngarkojmë të dhëna prej tyre për ndonjë nga projektet dhe llogaritjet tona në Excel. Eshte falas. Është mirë të dish se ka ende njerëz të tillë! Respekt!
Mbetet për të ngarkuar këto të dhëna në Excel duke përdorur shtesën Power Query (për versionet e Excel 2010-2013 mund të shkarkohet falas nga faqja e internetit e Microsoft, dhe në versionet e Excel 2016 dhe më të reja është tashmë e integruar si parazgjedhje ).
Logjika e veprimeve do të jetë si më poshtë:
- Ne bëjmë një kërkesë për të shkarkuar të dhëna nga faqja për çdo vit
- Duke e kthyer kërkesën tonë në funksion
- Ne e zbatojmë këtë funksion në listën e të gjitha viteve të disponueshme, duke filluar nga viti 2013 e deri në vitin aktual – dhe marrim një kalendar prodhimi “të përhershëm” me përditësim automatik. Voila!
Hapi 1. Importoni një kalendar për një vit
Së pari, ngarkoni kalendarin e prodhimit për çdo vit, për shembull, për vitin 2020. Për ta bërë këtë, në Excel, shkoni te skeda Data (Ose Kërkesa e energjisënëse e keni instaluar si një shtesë të veçantë) dhe zgjidhni Nga interneti (Nga Web). Në dritaren që hapet, ngjitni lidhjen me vitin përkatës, të kopjuar nga faqja:
Pas klikimit OK shfaqet një dritare paraprake, në të cilën duhet të klikoni butonin Konvertoni të dhëna (Transformoni të dhënat) or Për të ndryshuar të dhënat (Redakto të dhënat) dhe do të arrijmë te dritarja e redaktuesit të pyetjeve të Power Query, ku do të vazhdojmë të punojmë me të dhënat:
Menjëherë mund të fshini në mënyrë të sigurt në panelin e duhur Kërkoni Parametrat (Cilësimet e pyetjes) hap lloji i modifikuar (Lloji i ndryshuar) Ne nuk kemi nevojë për të.
Tabela në kolonën e festave përmban kode dhe përshkrime të ditëve jo-pune - mund ta shihni përmbajtjen e saj duke e “kaluar” dy herë duke klikuar mbi fjalën e gjelbër. Tryezë:
Për t'u kthyer mbrapa, do të duhet të fshini në panelin e djathtë të gjithë hapat që janë shfaqur Burim (Burimi).
Tabela e dytë, e cila mund të aksesohet në mënyrë të ngjashme, përmban pikërisht atë që na nevojitet - datat e të gjitha ditëve jo pune:
Mbetet për të përpunuar këtë pjatë, përkatësisht:
1. Filtro vetëm datat e festave (dmth ato) sipas kolonës së dytë Atributi:t
2. Fshini të gjitha kolonat përveç të parës - duke klikuar me të djathtën në kokën e kolonës së parë dhe duke zgjedhur komandën Fshi kolonat e tjera (Hiq kolonat e tjera):
3. Ndani kolonën e parë me pikë veçmas për muajin dhe ditën me komandë Shtylla e ndarë - Nga kufizuesi tab Transformim (Transformimi - Kolona e ndarë - Sipas kufizuesit):
4. Dhe së fundi krijoni një kolonë të llogaritur me data normale. Për ta bërë këtë, në skedën Shtimi i një kolone klikoni në butonin Kolona e personalizuar (Shto kolonë - kolonë e personalizuar) dhe shkruani formulën e mëposhtme në dritaren që shfaqet:
=#datuar(2020, [#»Atributi:d.1″], [#»Atributi:d.2″])
Këtu, operatori #date ka tre argumente: viti, muaji dhe dita, respektivisht. Pasi të klikoni mbi OK marrim kolonën e kërkuar me datat normale të fundjavës dhe fshijmë kolonat e mbetura si në hapin 2
Hapi 2. Shndërrimi i kërkesës në funksion
Detyra jonë tjetër është të konvertojmë pyetjen e krijuar për vitin 2020 në një funksion universal për çdo vit (numri i vitit do të jetë argumenti i tij). Për ta bërë këtë, ne bëjmë sa vijon:
1. Zgjerimi (nëse nuk është zgjeruar tashmë) i panelit hetimet (Pyetje) në të majtë në dritaren e Power Query:
2. Pas konvertimit të kërkesës në një funksion, aftësia për të parë hapat që përbëjnë kërkesën dhe për t'i modifikuar lehtësisht ato, për fat të keq, zhduket. Prandaj, ka kuptim të bëjmë një kopje të kërkesës sonë dhe të gëzohemi tashmë me të, dhe ta lëmë origjinalin në rezervë. Për ta bërë këtë, klikoni me të djathtën në panelin e majtë në kërkesën tonë të kalendarit dhe zgjidhni komandën Duplicate.
Duke klikuar me të djathtën përsëri në kopjen që rezulton të kalendarit (2) do të zgjidhni komandën Riemërtoj (Riemërto) dhe vendosni një emër të ri - le të jetë, për shembull, fxViti:
3. Ne hapim kodin burimor të pyetjes në gjuhën e brendshme të Power Query (ai quhet shkurt "M") duke përdorur komandën Redaktues i avancuar tab Rishikim(Shiko - Redaktues i avancuar) dhe të bëjmë ndryshime të vogla atje për ta kthyer kërkesën tonë në funksion për çdo vit.
Ishte:
Pas:
Nëse jeni të interesuar për detajet, atëherë këtu:
- (viti si numër)=> – deklarojmë se funksioni ynë do të ketë një argument numerik – një ndryshore vit
- Ngjitja e ndryshores vit në lidhjen e internetit në hap Burim. Meqenëse Power Query nuk ju lejon të ngjitni numra dhe tekst, ne e konvertojmë numrin e vitit në tekst në lëvizje duke përdorur funksionin Numri.ToText
- Ne e zëvendësojmë variablin e vitit për vitin 2020 në hapin e parafundit #"U shtua objekt i personalizuar«, ku kemi formuar datën nga fragmentet.
Pas klikimit Fund kërkesa jonë bëhet funksion:
Hapi 3. Importoni kalendarët për të gjitha vitet
Gjëja e fundit që mbetet është të bëni pyetjen e fundit kryesore, e cila do të ngarkojë të dhëna për të gjitha vitet e disponueshme dhe do të shtojë të gjitha datat e marra të pushimeve në një tabelë. Për këtë:
1. Ne klikojmë në panelin e majtë të pyetjes në një hapësirë boshe gri me butonin e djathtë të miut dhe zgjedhim në mënyrë sekuenciale Kërkesë e re – Burime të tjera – Kërkesë boshe (Pyetje e re - Nga burime të tjera - Pyetje bosh):
2. Ne duhet të gjenerojmë një listë të të gjitha viteve për të cilat do të kërkojmë kalendarët, dmth 2013, 2014 … 2020. Për ta bërë këtë, në shiritin e formulave të pyetjes bosh që shfaqet, shkruani komandën:
Strukturë:
={NumriA..NumriB}
… në Power Query gjeneron një listë të numrave të plotë nga A në B. Për shembull, shprehja
={1..5}
… do të prodhonte një listë me 1,2,3,4,5.
Epo, për të mos u lidhur fort me 2020, ne përdorim funksionin DataTime.LocalNow() – analog i funksionit Excel SOT (SOT) në Power Query - dhe nxirrni prej tij, nga ana tjetër, vitin aktual sipas funksionit Data.Viti.
3. Grupi i viteve që rezulton, megjithëse duket mjaft i përshtatshëm, nuk është një tabelë për Power Query, por një objekt i veçantë - listë (Listë). Por konvertimi i tij në një tabelë nuk është problem: thjesht klikoni butonin Në tryezë (Në tryezë) në këndin e sipërm të majtë:
4. Linja e mbarimit! Aplikimi i funksionit që krijuam më parë fxViti në listën e viteve që rezulton. Për ta bërë këtë, në skedën Shtimi i një kolone Shtyp butonin Thirrni funksionin e personalizuar (Shto kolonë - Thirr funksionin e personalizuar) dhe vendosi argumentin e tij të vetëm - kolonën Column1 gjate viteve:
Pas klikimit OK funksionin tonë fxViti importi do të funksionojë me radhë për çdo vit dhe ne do të marrim një kolonë ku çdo qelizë do të përmbajë një tabelë me datat e ditëve jo pune (përmbajtja e tabelës është qartë e dukshme nëse klikoni në sfondin e qelizës pranë fjala Tryezë):
Mbetet për të zgjeruar përmbajtjen e tabelave të mbivendosura duke klikuar në ikonën me shigjeta të dyfishta në kokën e kolonës Datat (shënoni Përdorni emrin origjinal të kolonës si parashtesë mund të hiqet):
… dhe pasi klikoni mbi OK ne marrim atë që donim - një listë e të gjitha festave nga viti 2013 deri në vitin aktual:
Kolona e parë, tashmë e panevojshme, mund të fshihet, dhe për të dytën, vendosni llojin e të dhënave data (Data) në listën rënëse në titullin e kolonës:
Vetë pyetja mund të riemërohet diçka më kuptimplote se Kërkesa 1 dhe pastaj ngarkoni rezultatet në fletë në formën e një tabele dinamike "të zgjuar" duke përdorur komandën mbyllni dhe shkarkoni tab Fillimi (Shtëpia - Mbyll & Ngarko):
Ju mund të përditësoni kalendarin e krijuar në të ardhmen duke klikuar me të djathtën në tabelë ose duke kërkuar në panelin e djathtë përmes komandës Përditëso & Ruaj. Ose përdorni butonin Rifresko të gjitha tab Data (Data - Rifresko të gjitha) ose shkurtore të tastierës Ctrl+Ndalet+F5.
Kjo eshte e gjitha.
Tani nuk keni më nevojë të humbni kohë dhe energji për të kërkuar dhe përditësuar listën e festave – tani ju keni një kalendar prodhimi “të përhershëm”. Në çdo rast, për sa kohë që autorët e faqes http://xmlcalendar.ru/ mbështesin pasardhësit e tyre, të cilët, shpresoj, do të jenë për një kohë shumë, shumë të gjatë (përsëri falë tyre!).
- Importoni normën e bitcoin për të shkëlqyer nga interneti përmes Power Query
- Gjetja e ditës së ardhshme të punës duke përdorur funksionin WORKDAY
- Gjetja e kryqëzimit të intervaleve të datave