Calendar de fabrică în Excel

Calendarul de producție, adică o listă de date, în care toate zilele lucrătoare oficiale și sărbătorile sunt marcate corespunzător – un lucru absolut necesar pentru orice utilizator de Microsoft Excel. În practică, nu te poți descurca fără ea:

• în calculele contabile (salariu, vechime în muncă, concedii...)
• în logistică – pentru determinarea corectă a timpilor de livrare, ținând cont de weekenduri și sărbători (amintiți-vă de clasicul „hai după sărbători?”)
  
• în managementul proiectelor – pentru estimarea corectă a termenelor, luând în considerare, din nou, zilele lucrătoare- nelucrătoare
• orice utilizare a funcțiilor precum ZI DE LUCRU (ZI DE LUCRU) or MUNCITORI PURI (ZIILE DE REȚEA), pentru că necesită o listă de sărbători ca argument
• când utilizați funcții Time Intelligence (cum ar fi TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR etc.) în Power Pivot și Power BI
  
• … etc. etc. – o mulțime de exemple.
  

Este mai ușor pentru cei care lucrează în sisteme ERP corporative, cum ar fi 1C sau SAP, deoarece calendarul de producție este încorporat în acestea. Dar cum rămâne cu utilizatorii Excel?

Puteți, desigur, să păstrați un astfel de calendar manual. Dar atunci va trebui să-l actualizați cel puțin o dată pe an (sau chiar mai des, ca în „jolly” 2020), intrând cu atenție în toate weekendurile, transferurile și zilele nelucrătoare inventate de guvernul nostru. Și apoi repetați această procedură în fiecare an. Plictiseală.

Ce zici să înnebunești puțin și să faci un calendar de fabrică „perpetuu” în Excel? Unul care se actualizează singur, preia date de pe Internet și generează întotdeauna o listă actualizată de zile nelucrătoare pentru utilizare ulterioară în orice calcule? Ispititor?

A face acest lucru, de fapt, nu este deloc dificil.

Sursă de date

Întrebarea principală este de unde să obțineți datele? În căutarea unei surse potrivite, am trecut prin mai multe opțiuni:

• Decretele originale sunt publicate pe site-ul guvernului în format PDF (aici, unul dintre ele, de exemplu) și dispar imediat – informații utile nu pot fi scoase din ele.
• A tempting option, at first glance, seemed to be the “Open Data Portal of the Federation”, where there is a corresponding data set, but, upon closer examination, everything turned out to be sad. The site is terribly inconvenient for importing into Excel, technical support does not respond (self-isolated?), and the data itself is outdated there for a long time – the production calendar for 2020 was last updated in November 2019 (disgrace!) and, of course, does not contain our “coronavirus ‘ and the ‘voting’ weekend of 2020, for example.

Deziluzionat de sursele oficiale, am început să sape pe cele neoficiale. Există multe dintre ele pe Internet, dar cele mai multe dintre ele, din nou, sunt complet nepotrivite pentru importul în Excel și oferă un calendar de producție sub formă de imagini frumoase. Dar nu e pentru noi să-l atârnăm pe perete, nu?

Și în procesul de căutare, un lucru minunat a fost descoperit accidental - site-ul http://xmlcalendar.ru/

Fără „bibelouri” inutile, un site simplu, ușor și rapid, ascuțit pentru o singură sarcină – pentru a oferi tuturor un calendar de producție pentru anul dorit în format XML. Excelent!

Dacă, dintr-o dată, nu știți, atunci XML este un format text cu conținut marcat cu special . Ușor, convenabil și ușor de citit de majoritatea programelor moderne, inclusiv Excel.

Pentru orice eventualitate, am luat legătura cu autorii site-ului și mi-au confirmat că site-ul există de 7 ani, datele de pe el sunt actualizate constant (au chiar și o sucursală pe github pentru asta) și nu au de gând să-l închidă. Și nu mă deranjează deloc că tu și cu mine încărcăm date din el pentru oricare dintre proiectele și calculele noastre în Excel. Este gratuit. E bine să știi că mai există oameni ca ăsta! Respect!

Rămâne să încărcați aceste date în Excel folosind programul de completare Power Query (pentru versiunile de Excel 2010-2013 poate fi descărcat gratuit de pe site-ul Microsoft, iar în versiunile de Excel 2016 și mai noi este deja încorporat în mod implicit ).

Logica acțiunilor va fi următoarea:

1. Facem o solicitare de a descărca date de pe site pentru orice an
2. Transformând cererea noastră într-o funcție
3. Aplicăm această funcție listei cu toți anii disponibili, începând din 2013 și până în anul curent – ​​și obținem un calendar de producție „perpetuu” cu actualizare automată. Voila!
   

Pasul 1. Importați un calendar pentru un an

Mai întâi, încărcați calendarul de producție pentru orice an, de exemplu, pentru 2020. Pentru a face acest lucru, în Excel, accesați fila Date (Sau Interogare de alimentaredacă l-ați instalat ca supliment separat) și selectați De pe internet (De pe web). În fereastra care se deschide, lipiți linkul către anul corespunzător, copiat de pe site:

După ce faceți clic pe OK apare o fereastră de previzualizare, în care trebuie să faceți clic pe butonul Convertiți datele (Transformă datele) or Pentru a schimba datele (Editați datele) și vom ajunge la fereastra editorului de interogări Power Query, unde vom continua să lucrăm cu datele:

Imediat puteți șterge în siguranță în panoul din dreapta Parametri de solicitare (Setări de interogare) pas tip modificat (Tipul schimbat) Nu avem nevoie de el.

Tabelul din coloana de sărbători conține coduri și descrieri ale zilelor nelucrătoare – puteți vedea conținutul lui „căzând” de două ori făcând clic pe cuvântul verde Tabel:

Pentru a reveni, va trebui să ștergeți în panoul din dreapta toți pașii la care au apărut înapoi Sursă (Sursă).

Al doilea tabel, care poate fi accesat într-un mod similar, conține exact ceea ce avem nevoie – datele tuturor zilelor nelucrătoare:

Rămâne să procesăm această placă și anume:

1. Filtrați numai datele de vacanță (adică cele) după a doua coloană Atribut: t

2. Ștergeți toate coloanele, cu excepția primei - făcând clic dreapta pe titlul primei coloane și selectând comanda Ștergeți alte coloane (Eliminați alte coloane):

3. Împărțiți prima coloană cu punct separat pentru lună și zi cu comanda Coloana împărțită – După delimitare fila Transformare (Transformare — Împărțire coloană — După delimitator):

4. Și în sfârșit creați o coloană calculată cu date normale. Pentru a face acest lucru, pe fila Adăugarea unei coloane faceți clic pe buton Coloană personalizată (Adăugați o coloană — Coloană personalizată) și introduceți următoarea formulă în fereastra care apare:

=#datat(2020, [#»Atribut:d.1″], [#»Atribut:d.2″])

Aici, operatorul #date are trei argumente: an, lună și, respectiv, zi. După ce faceți clic pe OK obținem coloana necesară cu date normale de weekend și ștergem coloanele rămase ca la pasul 2

Pasul 2. Transformarea cererii într-o funcție

Următoarea noastră sarcină este să transformăm interogarea creată pentru 2020 într-o funcție universală pentru orice an (numărul anului va fi argumentul său). Pentru a face acest lucru, facem următoarele:

1. Extinderea (dacă nu este deja extins) panoul Cereri (Întrebări) din stânga în fereastra Power Query:

2. După conversia cererii într-o funcție, abilitatea de a vedea pașii care compun cererea și de a le edita cu ușurință, din păcate, dispare. Prin urmare, este logic să facem o copie a cererii noastre și să ne zbucim deja cu ea și să lăsăm originalul în rezervă. Pentru a face acest lucru, faceți clic dreapta în panoul din stânga pe cererea noastră de calendar și selectați comanda Duplicare.

Făcând clic dreapta din nou pe copia rezultată a calendarului (2) va selecta comanda Redenumiți (Redenumiți) și introduceți un nume nou - să fie, de exemplu, fxYear:

3. Deschidem codul sursă al interogării în limbajul intern Power Query (se numește succint „M”) folosind comanda Editor avansat fila Recenzie(Vizualizare — Editor avansat) și faceți mici modificări acolo pentru a transforma solicitarea noastră într-o funcție pentru orice an.

Era:

După:

Dacă ești interesat de detalii, atunci aici:

• (anul ca număr) =>  – declarăm că funcția noastră va avea un argument numeric – o variabilă an
• Lipirea variabilei an la linkul web în pas Sursă. Deoarece Power Query nu vă permite să lipiți numere și text, convertim numărul anului în text din mers folosind funcția Number.ToText
• Inlocuim variabila an cu 2020 in penultimul pas #”Adăugat obiect personalizat«, unde am format data din fragmente.
  

După ce faceți clic pe finalizarea cererea noastră devine o funcție:

Pasul 3. Importați calendare pentru toți anii

Ultimul lucru rămas este să faceți ultima interogare principală, care va încărca date pentru toți anii disponibili și va adăuga toate datele de vacanță primite într-un singur tabel. Pentru aceasta:

1. Facem clic în panoul de interogări din stânga într-un spațiu gol gri cu butonul din dreapta al mouse-ului și selectăm secvențial Cerere nouă – Alte surse – Cerere goală (Interogare nouă — Din alte surse — Interogare goală):

2. Trebuie să generăm o listă cu toți anii pentru care vom solicita calendare, adică 2013, 2014 … 2020. Pentru a face acest lucru, în bara de formule a interogării goale care apare, introduceți comanda:

Structura:

={NumărA..NumărB}

… în Power Query generează o listă de numere întregi de la A la B. De exemplu, expresia

={1..5}

… ar produce o listă de 1,2,3,4,5.

Ei bine, pentru a nu fi legați rigid de 2020, folosim funcția DateTime.LocalNow() – analog al funcției Excel AZI (ASTĂZI) în Power Query – și extrageți din acesta, la rândul său, anul curent prin funcție Data.Anul.

3. Setul de ani rezultat, deși pare destul de adecvat, nu este un tabel pentru Power Query, ci un obiect special - listă (Listă). Dar convertirea acestuia într-un tabel nu este o problemă: doar faceți clic pe butonul La masă (la tabel) în colțul din stânga sus:

4. Linie de sfârșit! Aplicând funcția pe care am creat-o mai devreme fxYear la lista de ani rezultată. Pentru a face acest lucru, pe fila Adăugarea unei coloane apasa butonul Apelați funcția personalizată (Adăugați o coloană — Invocați funcția personalizată) și setați singurul său argument – ​​coloana Column1 de-a lungul anilor:

După ce faceți clic pe OK funcția noastră fxYear importul va funcționa pe rând pentru fiecare an și vom obține o coloană în care fiecare celulă va conține un tabel cu datele zilelor nelucrătoare (conținutul tabelului este clar vizibil dacă dați clic în fundalul celulei de lângă cuvantul Tabel):

Rămâne să extindem conținutul tabelelor imbricate făcând clic pe pictograma cu săgeți duble din antetul coloanei Date (bifați Folosiți numele coloanei originale ca prefix poate fi eliminat):

… și după ce faceți clic pe OK obținem ceea ce ne-am dorit – o listă cu toate vacanțele din 2013 până în anul curent:

Prima coloană, deja inutilă, poate fi ștearsă, iar pentru a doua, setați tipul de date data (Data) în lista derulantă din antetul coloanei:

Interogarea în sine poate fi redenumită ceva mai semnificativ decât Solicitare 1 și apoi încărcați rezultatele în foaie sub forma unui tabel „inteligent” dinamic folosind comanda închideți și descărcați fila Acasă (Acasă — Închideți și încărcați):

Puteți actualiza calendarul creat în viitor făcând clic dreapta pe tabel sau interogând în panoul din dreapta prin comanda Actualizați și salvați. Sau folosește butonul Reîmprospătați totul fila Date (Data — Actualizează tot) sau scurtătură de la tastatură Ctrl+Alt+F5.

Asta e tot.

Acum nu mai trebuie să pierzi niciodată timp și combustibil pentru a căuta și actualiza lista de sărbători – acum ai un calendar de producție „perpetuu”. În orice caz, atâta timp cât autorii site-ului http://xmlcalendar.ru/ își susțin descendenții, ceea ce, sper, va fi pentru foarte, foarte mult timp (le mulțumesc din nou!).

• Importați rata bitcoin pentru a excela de pe internet prin Power Query
• Găsirea următoarei zile lucrătoare folosind funcția WORKDAY
  
• Găsirea intersecției intervalelor de date