Gamyklos kalendorius excel

Gamybos kalendorius, ty datų sąrašas, kuriame atitinkamai pažymėtos visos oficialios darbo ir švenčių dienos – absoliučiai būtinas dalykas kiekvienam Microsoft Excel vartotojui. Praktiškai be jo neapsieisite:

• apskaitoje (atlyginimas, darbo stažas, atostogos...)
• logistikoje – už teisingą pristatymo terminų nustatymą, atsižvelgiant į savaitgalius ir švenčių dienas (prisiminkite klasikinį „atvažiuok po švenčių?“)
  
• projektų valdyme – už teisingą terminų įvertinimą, vėlgi atsižvelgiant į darbo nedarbo dienas
• bet koks tokių funkcijų naudojimas kaip DARBO DIENA (DARBO DIENA) or GRYNAI DARBUOTOJAI (TINKLO DIENOS), nes jiems kaip argumentas reikalingas švenčių sąrašas
• kai naudojate Time Intelligence funkcijas (pvz., TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR ir kt.) Power Pivot ir Power BI
  
• … ir tt ir tt – daug pavyzdžių.
  

Tai lengviau tiems, kurie dirba įmonės ERP sistemose, tokiose kaip 1C ar SAP, nes jose yra įmontuotas gamybos kalendorius. Bet kaip su „Excel“ vartotojais?

Žinoma, tokį kalendorių galite laikyti rankiniu būdu. Bet tada jūs turėsite jį atnaujinti bent kartą per metus (ar net dažniau, kaip „linksmaisiais“ 2020 m.), atidžiai įvesdami visus mūsų vyriausybės sugalvotus savaitgalius, pervedimus ir nedarbo dienas. O vėliau šią procedūrą kartokite kiekvienais kitais metais. Nuobodulys.

Kaip būtų galima šiek tiek išprotėti ir „Excel“ sukurti „amžiną“ gamyklos kalendorių? Toks, kuris atnaujina save, ima duomenis iš interneto ir visada sukuria naujausią nedarbo dienų sąrašą, kad vėliau būtų galima naudoti bet kokiuose skaičiavimuose? Gundanti?

Tai padaryti, tiesą sakant, visai nėra sunku.

Duomenų šaltinis

Pagrindinis klausimas – kur gauti duomenis? Ieškodamas tinkamo šaltinio, aš išbandžiau keletą variantų:

• Pirminiai nutarimai skelbiami Vyriausybės svetainėje PDF formatu (čia, pavyzdžiui, vienas iš jų) ir iškart dingsta – iš jų nepavyks ištraukti naudingos informacijos.
• Iš pirmo žvilgsnio viliojantis variantas atrodė „Federacijos atvirų duomenų portalas“, kuriame yra atitinkamas duomenų rinkinys, tačiau, atidžiau panagrinėjus, viskas pasirodė liūdna. Svetainėje siaubingai nepatogu importuoti į Excel, techninis palaikymas nereaguoja (išsiskiria?), o patys duomenys ten seniai pasenę – 2020 metų gamybos kalendorius paskutinį kartą atnaujintas 2019 metų lapkritį (gėda!) ir , žinoma, nėra mūsų „koronaviruso“ ir, pavyzdžiui, 2020 m. „balsavimo“ savaitgalio.

Nusivylęs oficialiais šaltiniais, ėmiau kasti neoficialius. Internete jų yra daug, tačiau dauguma jų vėlgi visiškai netinkami importuoti į „Excel“ ir išduoda gamybos kalendorių gražių paveikslėlių pavidalu. Bet ne mums tai kabinti ant sienos, tiesa?

O ieškant atsitiktinai buvo aptiktas nuostabus dalykas – svetainė http://xmlcalendar.ru/

Be bereikalingo „maivumo“, paprasta, lengva ir greita svetainė, paaštrinta vienai užduočiai – kiekvienam padovanoti norimų metų gamybos kalendorių XML formatu. Puiku!

Jei staiga nežinote, XML yra teksto formatas, kurio turinys pažymėtas specialiais . Lengvas, patogus ir skaitomas daugeliu šiuolaikinių programų, įskaitant Excel.

Bet kokiu atveju susisiekiau su svetainės autoriais ir jie patvirtino, kad svetainė egzistuoja jau 7 metus, joje esantys duomenys nuolat atnaujinami (tam net turi filialą github’e) ir nesiruošia jos uždaryti. Ir man visai neprieštarauja, kad jūs ir aš iš jo įkeliame duomenis bet kokiems mūsų projektams ir skaičiavimams Excel. Nemokamai. Smagu žinoti, kad vis dar yra tokių žmonių! Pagarba!

Belieka įkelti šiuos duomenis į Excel naudojant Power Query priedą (Excel 2010-2013 versijoms jį galima atsisiųsti nemokamai iš Microsoft svetainės, o Excel 2016 ir naujesnėse versijose jis jau yra integruotas pagal numatytuosius nustatymus ).

Veiksmų logika bus tokia:

1. Pateikiame prašymą atsisiųsti duomenis iš svetainės bet kuriems metams
2. Mūsų prašymo pavertimas funkcija
3. Šią funkciją taikome visų galimų metų sąrašui, pradedant nuo 2013 m. ir iki einamųjų metų – ir gauname „amžiną“ gamybos kalendorių su automatiniu atnaujinimu. Voila!
   

1 veiksmas. Importuokite kalendorių vieneriems metams

Pirmiausia įkelkite bet kurių vienerių metų, pavyzdžiui, 2020 m., gamybos kalendorių. Norėdami tai padaryti, programoje „Excel“ eikite į skirtuką Duomenys (Arba „Power Query“jei jį įdiegėte kaip atskirą priedą) ir pasirinkite Iš interneto (Iš interneto). Atsidariusiame lange įklijuokite atitinkamų metų nuorodą, nukopijuotą iš svetainės:

Spustelėję OK pasirodo peržiūros langas, kuriame reikia spustelėti mygtuką Konvertuoti duomenis (Keisti duomenis) or Norėdami pakeisti duomenis (Redaguoti duomenis) ir pateksime į Power Query užklausų rengyklės langą, kur toliau dirbsime su duomenimis:

Iš karto galite saugiai ištrinti dešiniajame skydelyje Parametrų užklausa (Užklausos nustatymai) žingsnis modifikuotas tipas (Pakeistas tipas) Mums jo nereikia.

Lentelėje švenčių stulpelyje yra kodai ir nedarbo dienų aprašymai – jos turinį galite pamatyti du kartus „prakritę“ paspaudę ant žalio žodžio Lentelė:

Norėdami grįžti atgal, dešiniajame skydelyje turėsite ištrinti visus veiksmus, kurie buvo rodomi atgal Šaltinis (Šaltinis).

Antroje lentelėje, kurią galima pasiekti panašiu būdu, yra būtent tai, ko mums reikia – visų nedarbo dienų datos:

Belieka apdoroti šią plokštę, būtent:

1. Pagal antrąjį stulpelį filtruokite tik atostogų datas (ty švenčių datas). Požymis: t

2. Ištrinkite visus stulpelius, išskyrus pirmąjį – dešiniuoju pelės klavišu spustelėdami pirmojo stulpelio antraštę ir pasirinkdami komandą Ištrinkite kitus stulpelius (Pašalinti kitus stulpelius):

3. Padalinkite pirmąjį stulpelį taškais atskirai mėnesiui ir dienai naudodami komandą Padalintas stulpelis – pagal skyriklį kortelė Transformacija (Transformuoti – padalinti stulpelį – pagal skyriklį):

4. Ir galiausiai sukurkite apskaičiuotą stulpelį su įprastomis datomis. Norėdami tai padaryti, skirtuke Stulpelio pridėjimas spustelėkite mygtuką Pasirinktinis stulpelis (Pridėti stulpelį – tinkintas stulpelis) ir pasirodžiusiame lange įveskite šią formulę:

=#pasimatyta(2020 m., [#»Atributas:d.1″], [#»Atributas:d.2″])

Čia #date operatorius turi tris argumentus: atitinkamai metai, mėnuo ir diena. Paspaudus ant OK gauname reikiamą stulpelį su įprastomis savaitgalio datomis, o likusius stulpelius ištriname kaip 2 veiksme

2 veiksmas. Užklausos pavertimas funkcija

Kitas mūsų uždavinys – 2020 metams sukurtą užklausą paversti universalia funkcija bet kokiems metams (jos argumentas bus metų skaičius). Norėdami tai padaryti, atliekame šiuos veiksmus:

1. Skydelio išplėtimas (jei dar neišskleistas). Paklausimai (Užklausos) kairėje Power Query lange:

2. Konvertavus užklausą į funkciją, galimybė matyti užklausą sudarančius veiksmus ir lengvai juos redaguoti, deja, dingsta. Todėl prasminga pasidaryti mūsų prašymo kopiją ir jau su juo pasilinksminti, o originalą palikti rezerve. Norėdami tai padaryti, dešiniuoju pelės mygtuku spustelėkite kalendoriaus užklausą kairiojoje srityje ir pasirinkite komandą Dubliuoti.

Dar kartą dešiniuoju pelės mygtuku spustelėjus gautą kalendoriaus (2) kopiją, bus pasirinkta komanda Pervardyti (Pervardyti) ir įveskite naują pavadinimą – tebūnie, pavyzdžiui, fxYear:

3. Užklausos šaltinio kodą atidarome vidine „Power Query“ kalba (ji glaustai vadinama „M“) naudodami komandą Išplėstinis redaktorius kortelė apžvalga(Žiūrėti – išplėstinis redaktorius) ir atlikite nedidelius pakeitimus, kad mūsų užklausa taptų bet kurių metų funkcija.

Tai buvo:

Po:

Jei jus domina detalės, tada čia:

• (metai kaip skaičius)=>  – deklaruojame, kad mūsų funkcija turės vieną skaitinį argumentą – kintamąjį metai
• Kintamojo įklijavimas metai į interneto nuorodą žingsnyje Šaltinis. Kadangi „Power Query“ neleidžia klijuoti skaičių ir teksto, metų skaičių konvertuojame į tekstą naudodami funkciją Number.To Text
• Priešpaskutiniu žingsniu 2020 m. pakeičiame metų kintamąjį #"Pridėtas pasirinktinis objektas«, kur iš fragmentų suformavome datą.
  

Spustelėję apdaila mūsų prašymas tampa funkcija:

3 veiksmas. Importuokite visų metų kalendorius

Belieka atlikti paskutinę pagrindinę užklausą, kuri įkels visų turimų metų duomenis ir sudės visas gautas švenčių datas į vieną lentelę. Už tai:

1. Dešiniuoju pelės mygtuku paspaudžiame kairiajame užklausos skydelyje pilkoje tuščioje vietoje ir pasirenkame nuosekliai Nauja užklausa – Kiti šaltiniai – Tuščia užklausa (Nauja užklausa – iš kitų šaltinių – tuščia užklausa):

2. Turime sugeneruoti visų metų, kuriems prašysime kalendorių, sąrašą, ty 2013, 2014 … 2020. Norėdami tai padaryti, pasirodžiusios tuščios užklausos formulės juostoje įveskite komandą:

Struktūra:

={SkaičiusA..SkaičiusB}

… Power Query generuoja sveikųjų skaičių nuo A iki B sąrašą. Pavyzdžiui, išraiška

={1..5}

… sudarytų 1,2,3,4,5 sąrašą.

Na, o kad nebūtume griežtai pririšti prie 2020 m., naudojame funkciją DateTime.LocalNow() – Excel funkcijos analogas ŠIANDIEN (ŠIANDIEN) programoje „Power Query“ – ir iš jos išskirkite einamuosius metus pagal funkciją Data.Metai.

3. Gautas metų rinkinys, nors ir atrodo gana adekvatus, yra ne Power Query lentelė, o specialus objektas – sąrašas (sąrašas). Tačiau konvertuoti jį į lentelę nėra problema: tiesiog spustelėkite mygtuką Prie stalo (Į lentelę) viršutiniame kairiajame kampe:

4. Finišo linija! Taikydami anksčiau sukurtą funkciją fxYear į gautą metų sąrašą. Norėdami tai padaryti, skirtuke Stulpelio pridėjimas Paspausk mygtuką Iškvieskite pasirinktinę funkciją (Pridėti stulpelį – iškviesti pasirinktinę funkciją) ir nustato vienintelį savo argumentą – stulpelį Column1 per metus:

Spustelėję OK mūsų funkcija fxYear importas veiks paeiliui kiekvienais metais ir gausime stulpelį, kuriame kiekviename langelyje bus lentelė su nedarbo dienų datomis (lentelės turinys aiškiai matomas paspaudus langelio fone šalia žodis Lentelė):

Belieka išplėsti įdėtųjų lentelių turinį spustelėjus piktogramą su dvigubomis rodyklėmis stulpelio antraštėje Datules (varnele Naudokite pradinį stulpelio pavadinimą kaip priešdėlį jį galima pašalinti):

… ir spustelėjus OK gauname tai, ko norėjome – visų švenčių sąrašą nuo 2013 m. iki einamųjų metų:

Pirmąjį, jau nereikalingą, stulpelį galima ištrinti, o antrajam – nustatyti duomenų tipą duomenys (Data) išskleidžiamajame sąraše stulpelio antraštėje:

Pati užklausa gali būti pervadinta kažkuo prasmingesniu nei Prašymas1 ir tada įkelkite rezultatus į lapą dinamiškos „išmaniosios“ lentelės pavidalu naudodami komandą uždaryti ir atsisiųsti kortelė Pagrindinis (Pagrindinis – uždaryti ir įkelti):

Ateityje sukurtą kalendorių galėsite atnaujinti dešiniuoju pelės mygtuku spustelėdami lentelę arba užklausą dešinėje srityje per komandą Atnaujinkite ir išsaugokite. Arba naudokite mygtuką Atnaujinti viską kortelė Duomenys (Data – Atnaujinti viską) arba spartusis klavišas "Ctrl"+Kitas+F5.

Tai viskas.

Dabar jums nebereikia gaišti laiko ir minčių ieškant ir atnaujinant švenčių sąrašą – dabar turite „amžiną“ gamybos kalendorių. Bet kokiu atveju, kol svetainės http://xmlcalendar.ru/ autoriai palaikys savo atžalas, kurios, tikiuosi, bus labai labai ilgai (ačiū jiems dar kartą!).

• Importuokite bitkoino kursą, kad išsiskirtumėte iš interneto naudodami „Power Query“.
• Kitos darbo dienos radimas naudojant WORKDAY funkciją
  
• Datos intervalų sankirtos radimas