Rūpnīcas kalendārs programmā Excel

Ražošanas kalendārs, proti, datumu saraksts, kurā attiecīgi atzīmētas visas oficiālās darba dienas un svētku dienas – absolūti nepieciešama lieta jebkuram Microsoft Excel lietotājam. Praksē bez tā nevar iztikt:

• grāmatvedības aprēķinos (alga, darba stāžs, atvaļinājumi...)
• loģistikā - pareizai piegādes laiku noteikšanai, ņemot vērā nedēļas nogales un svētku dienas (atcerieties klasisko "atnāc pēc brīvdienām?")
  
• projektu vadībā – par pareizu termiņu aprēķinu, ņemot vērā atkal darba dienas
• jebkura tādu funkciju izmantošana kā DARBADIENA (DARBADIENA) or TĪRI STRĀDNIEKI (NETWORKDAYS), jo viņiem kā arguments ir nepieciešams brīvdienu saraksts
• izmantojot Time Intelligence funkcijas (piemēram, TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR u.c.) programmās Power Pivot un Power BI
  
• … utt utt – daudz piemēru.
  

Tas ir vieglāk tiem, kas strādā korporatīvajās ERP sistēmās, piemēram, 1C vai SAP, jo tajās ir iebūvēts ražošanas kalendārs. Bet kā ir ar Excel lietotājiem?

Jūs, protams, varat saglabāt šādu kalendāru manuāli. Bet tad jums tas būs jāatjaunina vismaz reizi gadā (vai pat biežāk, kā “jauktajā” 2020), rūpīgi ievadot visas mūsu valdības izdomātās nedēļas nogales, pārskaitījumus un brīvdienas. Un pēc tam atkārtojiet šo procedūru katru nākamo gadu. Garlaicība.

Kā būtu mazliet traka un izveidot "mūžīgu" rūpnīcas kalendāru programmā Excel? Tāds, kas pats atjauninās, ņem datus no interneta un vienmēr ģenerē aktuālo brīvdienu sarakstu, ko vēlāk izmantot jebkādos aprēķinos? Vilinošs?

To izdarīt patiesībā nemaz nav grūti.

Datu avots

Galvenais jautājums ir, kur iegūt datus? Meklējot piemērotu avotu, es izmantoju vairākas iespējas:

• Oriģinālie dekrēti tiek publicēti valdības mājaslapā PDF formātā (šeit, piemēram, viens no tiem) un uzreiz pazūd – no tiem nevar izvilkt noderīgu informāciju.
• Vilinošs variants no pirmā acu uzmetiena šķita “Federācijas atvērto datu portāls”, kur ir atbilstoša datu kopa, taču, rūpīgāk izpētot, viss izrādījās bēdīgi. Vietne ir šausmīgi neērta importēšanai Excel, tehniskais atbalsts nereaģē (pašizolēts?), un arī paši dati tur jau sen ir novecojuši – 2020. gada ražošanas kalendārs pēdējo reizi tika atjaunināts 2019. gada novembrī (kauns!) un , protams, nesatur mūsu “koronavīrusu” un, piemēram, 2020. gada “balsošanas” nedēļas nogali.

Vīlies oficiālajos avotos, es sāku rakt neoficiālos avotus. Internetā to ir daudz, taču lielākā daļa atkal ir pilnīgi nepiemēroti importēšanai Excel un izsniedz ražošanas kalendāru skaistu attēlu veidā. Bet tas nav mūsu uzdevums to piekārt pie sienas, vai ne?

Un meklēšanas procesā nejauši tika atklāta brīnišķīga lieta - vietne http://xmlcalendar.ru/

Bez liekām “frībām” vienkārša, viegla un ātra vietne, uzasināta vienam uzdevumam – katram uzdāvināt XML formāta ražošanas kalendāru vēlamajam gadam. Lieliski!

Ja pēkšņi jūs nezināt, tad XML ir teksta formāts ar saturu, kas atzīmēts ar īpašu . Viegls, ērts un lasāms lielākajā daļā mūsdienu programmu, tostarp Excel.

Katram gadījumam sazinājos ar vietnes autoriem, un viņi apstiprināja, ka vietne pastāv jau 7 gadus, dati tajā tiek pastāvīgi atjaunināti (viņiem pat ir filiāle githubā šim nolūkam), un viņi to negrasās slēgt. Un man nav nekas pretī, ka jūs un es no tā ielādējam datus jebkuram mūsu projektam un aprēķiniem programmā Excel. Par brīvu. Prieks apzināties, ka vēl ir šādi cilvēki! Respekt!

Atliek ielādēt šos datus programmā Excel, izmantojot Power Query pievienojumprogrammu (Excel 2010-2013 versijām to var lejupielādēt bez maksas no Microsoft vietnes, un Excel 2016 un jaunākās versijās tas jau ir iebūvēts pēc noklusējuma ).

Darbību loģika būs šāda:

1. Mēs pieprasām lejupielādēt datus no vietnes par vienu gadu
2. Mūsu pieprasījuma pārvēršana par funkciju
3. Šo funkciju piemērojam visu pieejamo gadu sarakstam, sākot no 2013. gada un līdz pat kārtējam gadam – un iegūstam “mūžīgo” ražošanas kalendāru ar automātisku atjaunināšanu. Voila!
   

1. darbība. Importējiet kalendāru vienam gadam

Vispirms ielādējiet ražošanas kalendāru jebkuram gadam, piemēram, 2020. gadam. Lai to paveiktu, programmā Excel atveriet cilni Datums (Vai Jaudas vaicājumsja instalējāt to kā atsevišķu papildinājumu) un atlasiet No interneta (No tīmekļa). Atvērtajā logā ielīmējiet saiti uz atbilstošo gadu, kas nokopēta no vietnes:

Pēc noklikšķināšanas uz OK tiek parādīts priekšskatījuma logs, kurā jānoklikšķina uz pogas Konvertēt datus (Pārveidot datus) or Lai mainītu datus (Rediģēt datus) un mēs nonāksim Power Query vaicājumu redaktora logā, kur turpināsim darbu ar datiem:

Tūlīt varat droši dzēst labajā panelī Pieprasīt parametrus (Vaicājuma iestatījumi) solis modificēts tips (Mainīts veids) Mums viņš nav vajadzīgs.

Tabulā brīvdienu ailē ir kodi un brīvdienu apraksti – tās saturu var redzēt, divreiz “izkrītot cauri”, noklikšķinot uz zaļā vārda. Tabula:

Lai atgrieztos, labajā panelī būs jāizdzēš visas darbības, kas tika parādītas atpakaļ avots (Avots).

Otrajā tabulā, kurai var piekļūt līdzīgi, ir tieši tas, kas mums nepieciešams – visu brīvdienu datumi:

Atliek apstrādāt šo plāksni, proti:

1. Filtrējiet tikai svētku datumus (ti, pēc otrās kolonnas). Atribūts:t

2. Dzēst visas kolonnas, izņemot pirmo – ar peles labo pogu noklikšķinot uz pirmās kolonnas virsraksta un atlasot komandu Dzēst citas kolonnas (Noņemt citas kolonnas):

3. Sadaliet pirmo kolonnu pa punktiem atsevišķi mēnesim un dienai, izmantojot komandu Sadalīta kolonna — pēc norobežotāja tab transformācija (Pārveidot — sadalīt kolonnu — pēc norobežotāja):

4. Un visbeidzot izveidojiet aprēķinātu kolonnu ar parastajiem datumiem. Lai to izdarītu, cilnē Kolonnas pievienošana noklikšķiniet uz pogas Pielāgota kolonna (Pievienot kolonnu — pielāgota kolonna) un parādītajā logā ievadiet šādu formulu:

=#datēts(2020, [#»Atribūts:d.1″], [#»Atribūts:d.2″])

Šeit operatoram #date ir trīs argumenti: attiecīgi gads, mēnesis un diena. Pēc noklikšķināšanas uz OK mēs iegūstam vajadzīgo kolonnu ar parastajiem nedēļas nogales datumiem un izdzēšam pārējās kolonnas, kā norādīts 2. darbībā

2. darbība. Pieprasījuma pārvēršana par funkciju

Mūsu nākamais uzdevums ir 2020. gadam izveidoto vaicājumu pārvērst par universālu funkciju jebkuram gadam (gada skaitlis būs tā arguments). Lai to izdarītu, mēs rīkojamies šādi:

1. Paplašinot (ja vēl nav izvērsts) panelis Jautājumi (Vaicājumi) Power Query loga kreisajā pusē:

2. Pēc pieprasījuma konvertēšanas par funkciju, diemžēl pazūd iespēja redzēt soļus, kas veido pieprasījumu, un viegli tos rediģēt. Tāpēc ir jēga izgatavot mūsu pieprasījuma kopiju un jau ar to draiskoties, bet oriģinālu atstāt rezervē. Lai to izdarītu, ar peles labo pogu noklikšķiniet kreisajā rūtī uz mūsu kalendāra pieprasījuma un atlasiet komandu Dublēt.

Ar peles labo pogu vēlreiz noklikšķinot uz iegūtās kalendāra kopijas (2), tiks atlasīta komanda pārdēvēt (Pārdēvēt) un ievadiet jaunu nosaukumu — lai tas būtu, piemēram, fxYear:

3. Mēs atveram vaicājuma avota kodu iekšējā Power Query valodā (to īsi sauc par “M”), izmantojot komandu Uzlabots redaktors tab Pārskats(Skatīt — uzlabotais redaktors) un veiciet tur nelielas izmaiņas, lai mūsu pieprasījumu pārvērstu par funkciju jebkuram gadam.

Tas bija:

Pēc:

Ja jūs interesē detaļas, tad šeit:

• (gads kā skaitlis)=>  – deklarējam, ka mūsu funkcijai būs viens skaitlisks arguments – mainīgais gads
• Mainīgā ielīmēšana gads uz tīmekļa saiti solī avots. Tā kā Power Query neļauj līmēt skaitļus un tekstu, mēs lidojumā pārvēršam gada skaitli par tekstu, izmantojot funkciju Number.To Text
• Mēs priekšpēdējā solī aizstājam 2020. gada mainīgo #”Pievienots pielāgots objekts«, kur no fragmentiem veidojām datumu.
  

Pēc noklikšķināšanas uz apdare mūsu pieprasījums kļūst par funkciju:

Solis 3. Importēt kalendārus visiem gadiem

Pēdējā lieta, kas atliek, ir veikt pēdējo galveno vaicājumu, kas augšupielādēs datus par visiem pieejamajiem gadiem un saliks visus saņemtos svētku datumus vienā tabulā. Priekš šī:

1. Ar peles labo pogu noklikšķinām kreisajā vaicājuma panelī pelēkā tukšā vietā un secīgi atlasām Jauns pieprasījums — Citi avoti — Tukšs pieprasījums (Jauns vaicājums — no citiem avotiem — tukšs vaicājums):

2. Mums ir jāģenerē saraksts ar visiem gadiem, kuriem pieprasīsim kalendārus, ti, 2013., 2014. … 2020. Lai to izdarītu, parādītā tukšā vaicājuma formulas joslā ievadiet komandu:

Struktūra:

={NumursA..NumursB}

… programmā Power Query ģenerē veselu skaitļu sarakstu no A līdz B. Piemēram, izteiksme

={1..5}

… izveidotu sarakstu ar 1,2,3,4,5.

Nu, lai mēs nebūtu stingri piesaistīti 2020. gadam, mēs izmantojam funkciju DateTime.LocalNow() – Excel funkcijas analogs ŠODIEN (ŠODIEN) programmā Power Query – un izvelciet no tā, savukārt, kārtējo gadu pēc funkcijas Datums.Gads.

3. Iegūtais gadu kopums, lai arī izskatās diezgan adekvāts, nav Power Query tabula, bet gan īpašs objekts – saraksts (Saraksts). Taču tā konvertēšana par tabulu nav problēma: vienkārši noklikšķiniet uz pogas Pie galda (Uz tabulu) augšējā kreisajā stūrī:

4. Finiša līnija! Lietojot iepriekš izveidoto funkciju fxYear uz iegūto gadu sarakstu. Lai to izdarītu, cilnē Kolonnas pievienošana nospiediet pogu Izsaukt pielāgoto funkciju (Pievienot kolonnu — izsaukt pielāgoto funkciju) un uzstādiet savu vienīgo argumentu – kolonnu Column1 gadu gaitā:

Pēc noklikšķināšanas uz OK mūsu funkcija fxYear importēšana darbosies pēc kārtas katram gadam un iegūsim kolonnu, kurā katrā šūnā būs tabula ar brīvdienu datumiem (tabulas saturs ir skaidri redzams, ja noklikšķināsiet uz šūnas fonā blakus vārds Tabula):

Atliek izvērst ligzdoto tabulu saturu, kolonnas galvenē noklikšķinot uz ikonas ar dubultbultiņām Datumi (atzīmējiet Izmantojiet sākotnējo kolonnas nosaukumu kā prefiksu to var noņemt):

… un pēc noklikšķināšanas uz OK mēs iegūstam to, ko gribējām - visu svētku sarakstu no 2013. gada līdz kārtējam gadam:

Pirmo, jau nevajadzīgo kolonnu, var izdzēst, bet otrajai - iestatīt datu veidu dati (Datums) kolonnas virsraksta nolaižamajā sarakstā:

Pats vaicājums var tikt pārdēvēts par kaut ko jēgpilnāku Pieprasījums1 un pēc tam augšupielādējiet rezultātus lapā dinamiskas “viedās” tabulas veidā, izmantojot komandu aizveriet un lejupielādējiet tab Sākumlapa (Sākums — aizvērt un ielādēt):

Izveidoto kalendāru var atjaunināt nākotnē, ar peles labo pogu noklikšķinot uz tabulas vai vaicājuma labajā rūtī, izmantojot komandu Atjaunināt un saglabāt. Vai arī izmantojiet pogu Atsvaidzināt visu tab Datums (Datums — atsvaidzināt visu) vai īsinājumtaustiņš Ctrl+cits+F5.

Tas ir viss.

Tagad jums vairs nav jātērē laiks un pārdomas, meklējot un atjauninot brīvdienu sarakstu – tagad jums ir "mūžīgs" ražošanas kalendārs. Jebkurā gadījumā, ja vien vietnes http://xmlcalendar.ru/ autori atbalstīs savas atvases, kas, ceru, būs ļoti, ļoti ilgi (vēlreiz paldies viņiem!).

• Importējiet bitcoin likmi, lai izceltos no interneta, izmantojot Power Query
• Nākamās darbadienas atrašana, izmantojot funkciju WORKDAY
  
• Datumu intervālu krustpunkta atrašana