Fabrikaren egutegia Excel-en

Ekoizpen egutegia, hau da, daten zerrenda, non lanegun eta jaiegun ofizial guztiak horren arabera markatuta dauden - Microsoft Excel-eko edozein erabiltzailerentzat guztiz beharrezkoa dena. Praktikan, ezin duzu egin gabe:

• kontabilitate kalkuluetan (soldata, lanaldia, oporrak...)
• logistikan - entrega-epeak behar bezala zehazteko, asteburuak eta jaiegunak kontuan hartuta (gogoratu "oporren ostean etorri?") klasikoa.
  
• proiektuen kudeaketan – baldintzak behar bezala kalkulatzeko, kontuan hartuta, berriro ere, lan-egunak
• bezalako funtzioen erabilera edozein LANEGUNA (LANEGUNA) or LANGILE PURUAK (SARE EGUNAK), oporren zerrenda eskatzen dutelako argudio gisa
• Time Intelligence funtzioak erabiltzen dituzunean (adibidez, TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, etab.) Power Pivot eta Power BI-n
  
• … etab. etab. – adibide asko.
  

Errazagoa da 1C edo SAP bezalako ERP sistemetan lan egiten dutenentzat, produkzio-egutegia barneratuta baitago. Baina zer gertatzen da Excel erabiltzaileekin?

Halako egutegi bat eskuz gorde dezakezu, noski. Baina orduan gutxienez urtean behin eguneratu beharko duzu (edo are maizago, 2020ko "jolly"-n bezala), gure gobernuak asmatutako asteburu, transferentzia eta lanegun guztiak kontu handiz sartuz. Eta gero errepikatu prozedura hau hurrengo urtean behin. Asperdura.

Zer esan pixka bat erotu eta Excel-en fabrika-egutegi "betiko" bat egitea? Bere burua eguneratzen duena, Internetetik datuak hartzen dituena eta lanegunak ez diren egunen zerrenda eguneratua sortzen duena, gerora edozein kalkulutan erabiltzeko? Tentagarria?

Hori egitea, egia esan, ez da batere zaila.

Datuen iturria

Galdera nagusia da non lortu datuak? Iturri egoki baten bila, hainbat aukera aztertu ditut:

• Jatorrizko dekretuak gobernuaren webgunean argitaratzen dira PDF formatuan (hemen, horietako bat, adibidez) eta berehala desagertzen dira –ezin da informazio baliagarria atera.
• Aukera tentagarri bat, lehen begiratuan, “Federazioaren datu irekien ataria” zela zirudien, non dagokien datu multzoa dagoen, baina, sakonago aztertuta, dena triste geratu zen. Webgunea Excel-en inportatzeko izugarri deserosoa da, laguntza teknikoak ez du erantzuten (auto-isolatuta?), eta datuak berak zaharkituta daude denbora luzez - 2020ko produkzio-egutegia 2019ko azaroan eguneratu zen azkenekoz (lotsagarria!) eta , noski, ez du gure "koronabirusa" eta 2020ko "botoa" asteburua, adibidez.

Iturri ofizialekin desengainatuta, ez-ofizialak zulatzen hasi nintzen. Interneten asko daude, baina gehienak, berriro ere, guztiz desegokiak dira Excel-era inportatzeko eta ekoizpen-egutegi bat ematen dute argazki ederren moduan. Baina ez dagokigu horman zintzilikatzea, ezta?

Eta bilaketa prozesuan, ustekabean gauza zoragarri bat aurkitu zen: http://xmlcalendar.ru/ gunea.

Alferrikako "frills" gabe, gune sinple, arina eta azkarra, zeregin bakarrerako zorroztua: denei nahi den urteko ekoizpen-egutegia emateko XML formatuan. Bikain!

Bat-batean ezagutzen ez bazara, XML testu formatu bat da, bereziekin markatutako edukia duena . Arina, erosoa eta irakur daiteke programa moderno gehienek, Excel barne.

Badaezpada, gunearen egileekin jarri nintzen harremanetan eta baieztatu zidaten gunea 7 urtez existitzen dela, bertako datuak etengabe eguneratzen direla (github-en adar bat ere badute horretarako) eta ez dutela itxiko. Eta ez zait batere axola zuk eta biok hortik datuak kargatzea Excel-en gure edozein proiektu eta kalkuluetarako. Doakoa da. Polita da oraindik horrelako jendea dagoela jakitea! Errespetua!

Datu hauek Excel-en kargatzea geratzen da Power Query gehigarria erabiliz (Excel 2010-2013 bertsioetarako doan deskargatu daiteke Microsoft-en webgunetik, eta Excel 2016-en eta berriagoa den bertsioetan lehenetsita dago jada. ).

Ekintzen logika hau izango da:

1. Urte bateko datuak gunetik deskargatzeko eskaera egiten dugu
2. Gure eskaera funtzio bihurtuz
3. Funtzio hau erabilgarri dauden urte guztien zerrendari aplikatzen diogu, 2013tik hasi eta uneko urtera arte, eta ekoizpen-egutegi "betiko" bat lortzen dugu eguneratze automatikoarekin. Voila!
   

1. urratsa. Inportatu egutegi bat urtebeterako

Lehenik eta behin, kargatu edozein urteko produkzio-egutegia, adibidez, 2020rako. Horretarako, Excel-en, joan fitxara. Data (Edo Power kontsultaaparteko gehigarri gisa instalatu baduzu) eta hautatu Internetetik (Webetik). Irekitzen den leihoan, itsatsi dagokion urterako esteka, gunetik kopiatutakoa:

Klik egin ondoren OK aurrebista leiho bat agertzen da, eta bertan botoia sakatu behar duzu Bihurtu Datuak (Eraldatu datuak) or Datuak aldatzeko (Editatu datuak) eta Power Query kontsulta editorearen leihora iritsiko gara, non datuekin lanean jarraituko dugun:

Berehala segurtasunez ezaba dezakezu eskuineko panelean Eskatu parametroak (Kontsulta ezarpenak) urratsa aldatutako mota (Mota aldatua) Ez dugu haren beharrik.

Oporretako zutabeko taulak lanegunak ez diren lanegunen kodeak eta deskribapenak ditu; haren edukia ikus dezakezu bi aldiz "erortzen" hitz berdean klik eginez. Taula:

Atzera egiteko, eskuineko panelean atzera agertu diren urrats guztiak ezabatu beharko dituzu Iturria (Iturria).

Bigarren taulak, modu berean atzitu daitekeena, behar duguna zehatz-mehatz jasotzen du: lanegun ez diren guztien datak:

Plater hau prozesatzea geratzen da, hots:

1. Iragazi oporretako datak (hau da, direnak) bigarren zutabaren arabera Atributua: t

2. Ezabatu zutabe guztiak lehenengoa izan ezik - egin klik eskuineko botoiarekin lehen zutabearen goiburuan eta hautatu komandoa Ezabatu beste zutabe batzuk (Kendu beste zutabe batzuk):

3. Zatitu lehenengo zutabea puntuz hilabetez eta egunez komandoarekin Zatitu zutabea - Mugatzaileen arabera fitxa Eraldaketa (Eraldaketa — Zatitu zutabea — Mugatzailearen arabera):

4. Eta azkenik, sortu kalkulatutako zutabe bat data arruntekin. Horretarako, fitxan Zutabe bat gehitzea egin klik botoian Zutabe pertsonalizatua (Gehitu zutabea — Zutabe pertsonalizatua) eta sartu formula hau agertzen den leihoan:

=#datatua(2020, [#»Atributua:d.1″], [#»Atributua:d.2″])

Hemen, #date operadoreak hiru argumentu ditu: urtea, hilabetea eta eguna, hurrenez hurren. Sakatu ondoren OK beharrezko zutabea asteburuko data arruntekin lortuko dugu eta gainerako zutabeak ezabatzen ditugu 2. urratsean bezala

2. urratsa. Eskaera funtzio bihurtzea

Gure hurrengo zeregina 2020rako sortutako kontsulta edozein urtetarako funtzio unibertsal bihurtzea da (urtearen zenbakia izango da bere argumentua). Horretarako, honako hau egiten dugu:

1. Panela zabaltzen (hedatu ez bada). Kontsultak (Kontsultak) Power Query leihoan ezkerrean:

2. Eskaera funtzio bihurtu ondoren, eskaera osatzen duten urratsak ikusteko eta erraz editatzeko gaitasuna desagertzen da, zoritxarrez. Hori dela eta, zentzuzkoa da gure eskaeraren kopia bat egitea eta dagoeneko berarekin jolastea, eta jatorrizkoa erreserbatan uztea. Horretarako, egin klik eskuineko botoiarekin ezkerreko panelean gure egutegiko eskaeran eta hautatu Bikoiztu komandoa.

Egutegiaren(2) kopian berriro eskuineko botoiarekin klik eginez gero, komandoa hautatuko da Berrizendatu (Aldatu izena) eta idatzi izen berri bat; izan bedi, adibidez, fxUrtea:

3. Kontsulten iturburu kodea barne Power Query hizkuntzan irekitzen dugu (labur esanda "M" deitzen zaio) komandoa erabiliz Editore aurreratua fitxa Review(Ikusi — Editore aurreratua) eta bertan aldaketa txikiak egin gure eskaera edozein urtetarako funtzio bilakatzeko.

Zen:

Ondoren:

Xehetasunak interesatzen bazaizkizu, hemen:

• (urtea zenbaki gisa) =>  – gure funtzioak zenbakizko argumentu bat izango duela adierazten dugu – aldagai bat urteko
• Aldagaia itsatsi urteko urratsean web esteka Iturria. Power Query-k ez dizunez zenbakiak eta testua itsatsirik uzten, urtearen zenbakia testu bihurtzen dugu joan-etorrian funtzioa erabiliz. Zenbakia.Textura
• Urteko aldagaia 2020rako ordezkatzen dugu azkenaurreko urratsean #"Objektu pertsonalizatua gehitu da«, non zatietatik data osatu genuen.
  

Klik egin ondoren Amaitu gure eskaera funtzio bihurtzen da:

3. urratsa. Inportatu urte guztietako egutegiak

Azken galdera nagusia egitea da, erabilgarri dauden urte guztien datuak igoko dituena eta jasotako oporretako data guztiak taula batean gehituko dituena. Honetarako:

1. Ezkerreko kontsulta-panelean klik egiten dugu saguaren eskuineko botoiarekin hutsune gris batean eta sekuentzialki hautatzen dugu Eskaera berria – Beste iturri batzuk – Eskaera hutsa (Kontsulta berria — Beste iturri batzuetatik — Kontsulta hutsa):

2. Egutegiak eskatuko ditugun urte guztien zerrenda sortu behar dugu, hau da, 2013, 2014 ... 2020. Horretarako, agertzen den kontsulta hutsaren formula-barran, idatzi komandoa:

egitura:

={A.. ZenbakiaB}

… Power Query-k A-tik B-rako zenbaki osoen zerrenda sortzen du. Adibidez, adierazpena

={1..5}

… 1,2,3,4,5 zerrenda sortuko luke.

Tira, 2020ra zurrun lotu ez dadin, funtzioa erabiltzen dugu DateTime.LocalNow() – Excel funtzioaren analogoa GAUR (GAUR) Power Query-n – eta hortik atera, aldi berean, uneko urtea funtzioaren arabera Data.Urtea.

3. Lortutako urte multzoa, nahiko egokia dirudien arren, ez da Power Queryrako taula bat, objektu berezi bat baizik. zerrenda (Zerrenda). Baina taula bihurtzea ez da arazorik: botoia sakatu besterik ez dago Mahaira (Taulara) goiko ezkerreko izkinan:

4. Helmuga! Lehenago sortu dugun funtzioa aplikatuz fxUrtea ondoriozko urteen zerrendara. Horretarako, fitxan Zutabe bat gehitzea sakatu botoia Deitu funtzio pertsonalizatua (Gehitu zutabea — Deitu funtzio pertsonalizatua) eta ezarri bere argumentu bakarra: zutabea Column1 urteetan zehar:

Klik egin ondoren OK gure funtzioa fxUrtea inportazioak txandaka funtzionatuko du urte bakoitzeko eta zutabe bat jasoko dugu, non gelaxka bakoitzak lanegunak ez diren egunak dituen taula bat izango duen (taularen edukia ondo ikusten da ondoko gelaxkaren atzeko planoan klik eginez gero. Hitza Taula):

Habiaraturiko taulen edukia zabaltzea geratzen da zutabeen goiburuko gezi bikoitzak dituen ikonoan klik eginez Datak (tik Erabili jatorrizko zutabearen izena aurrizki gisa kendu daiteke):

… eta klikatu ondoren OK nahi genuena lortzen dugu: 2013tik aurtengo urtera arteko oporraldi guztien zerrenda:

Lehena, dagoeneko beharrezkoa ez den zutabea, ezabatu daiteke, eta bigarrenarentzat, datu mota ezarri data (Data) zutabearen goiburuko goitibeherako zerrendan:

Kontsulta bera baino esanguratsuagoa den izena alda daiteke Eskaera 1 eta, ondoren, kargatu emaitzak orrira taula "adimentsua" dinamiko baten moduan komandoa erabiliz itxi eta deskargatu fitxa Hasiera (Hasiera — Itxi eta Kargatu):

Etorkizunean sortutako egutegia egunera dezakezu taulan eskuineko botoiarekin klik eginez edo eskuineko panelean kontsultatu komandoaren bidez Eguneratu eta Gorde. Edo erabili botoia Freskatu guztiak fitxa Data (Data — Freskatu guztiak) edo teklatuko lasterbidea Ctrl+Alt+F5.

Hori da dena.

Orain ez duzu berriro denborarik galdu behar jaiegunen zerrenda bilatzen eta eguneratzen; orain produkzio-egutegia "betiko" duzu. Edonola ere, http://xmlcalendar.ru/ gunearen egileek beren ondorengoei laguntzen badiote, eta hori, espero dut, oso-oso denbora luzerako izango da (eskerrik asko berriro!).

• Inportatu bitcoin tasa Internetetik Excelerako Power Query bidez
• WORKDAY funtzioa erabiliz hurrengo laneguna aurkitzea
  
• Data-tarteen ebakidura aurkitzea