contingut
Calendari de producció, és a dir, una llista de dates, on tots els dies laborables i festius oficials estan marcats en conseqüència, una cosa absolutament necessària per a qualsevol usuari de Microsoft Excel. A la pràctica, no es pot prescindir d'ell:
- en càlculs comptables (sou, antiguitat, vacances...)
- en logística: per a la correcta determinació dels terminis de lliurament, tenint en compte els caps de setmana i els festius (recordeu el clàssic "Venga després de les vacances?")
- en gestió de projectes – per a la correcta estimació dels terminis, tenint en compte, de nou, els dies laborables i no laborables
- qualsevol ús de funcions com JORNADA DE TREBALL (DIA LABORAL) or PURS TREBALLADORS (DIES DE XARXA), perquè requereixen una llista de vacances com a argument
- quan utilitzeu funcions d'Intel·ligència de temps (com TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, etc.) a Power Pivot i Power BI
- … etc. etc. – molts exemples.
És més fàcil per a aquells que treballen en sistemes ERP corporatius com 1C o SAP, ja que el calendari de producció està integrat en ells. Però, què passa amb els usuaris d'Excel?
Per descomptat, podeu mantenir aquest calendari manualment. Però aleshores l'hauràs d'actualitzar almenys una vegada a l'any (o encara més sovint, com en el “jolly” 2020), entrant amb cura tots els caps de setmana, transferències i dies no laborables inventats pel nostre govern. I després repetiu aquest procediment cada any vinent. Avorriment.
Què tal tornar-vos una mica boig i fer un calendari de fàbrica "perpetu" a Excel? Un que s'actualitza, agafa dades d'Internet i genera sempre una llista actualitzada de dies inhàbils per al seu posterior ús en qualsevol càlcul? Temptadora?
Fer-ho, de fet, no és gens difícil.
Font de dades
La pregunta principal és on obtenir les dades? A la recerca d'una font adequada, vaig passar per diverses opcions:
- Els decrets originals es publiquen a la pàgina web del govern en format PDF (aquí, un d'ells, per exemple) i desapareixen immediatament: no se'n pot extreure informació útil.
- Una opció temptadora, a primer cop d'ull, semblava ser el "Portal de dades obertes de la Federació", on hi ha un conjunt de dades corresponent, però, després d'un examen més atent, tot va resultar trist. El lloc és terriblement incòmode per importar a Excel, el suport tècnic no respon (auto-aïllat?) i les dades en si estan obsoletes durant molt de temps: el calendari de producció per al 2020 es va actualitzar per última vegada el novembre de 2019 (una vergonya!) i , per descomptat, no conté el nostre "coronavirus" i el cap de setmana de "votació" del 2020, per exemple.
Desil·lusionat amb les fonts oficials, vaig començar a cavar-ne de no oficials. N'hi ha molts a Internet, però la majoria d'ells, de nou, són completament inadequats per importar-los a Excel i ofereixen un calendari de producció en forma de belles imatges. Però no ens toca penjar-lo a la paret, oi?
I en el procés de cerca, es va descobrir accidentalment una cosa meravellosa: el lloc http://xmlcalendar.ru/
Sense "fromatges" innecessaris, un lloc senzill, lleuger i ràpid, perfeccionat per a una tasca: donar a tothom un calendari de producció per a l'any desitjat en format XML. Excel · lent!
Si, de sobte, no esteu al corrent, aleshores XML és un format de text amb contingut marcat amb especial
Per si de cas, em vaig posar en contacte amb els autors del lloc i em van confirmar que el lloc fa 7 anys que existeix, que les dades s'actualitzen constantment (fins i tot tenen una branca a github per a això) i no el tancaran. I no m'importa gens que tu i jo en carreguem dades per a qualsevol dels nostres projectes i càlculs a Excel. És gratis. És un plaer saber que encara hi ha gent així! Respecte!
Queda per carregar aquestes dades a Excel mitjançant el complement Power Query (per a les versions d'Excel 2010-2013 es pot descarregar gratuïtament des del lloc web de Microsoft, i en les versions d'Excel 2016 i posteriors ja està integrat per defecte ).
La lògica d'actuació serà la següent:
- Fem una sol·licitud per descarregar dades del lloc durant un any qualsevol
- Convertint la nostra petició en una funció
- Apliquem aquesta funció a la llista de tots els anys disponibles, a partir del 2013 i fins a l'any en curs, i obtenim un calendari de producció "perpetu" amb actualització automàtica. Voila!
Pas 1. Importa un calendari durant un any
En primer lloc, carregueu el calendari de producció per a un any qualsevol, per exemple, per al 2020. Per fer-ho, a Excel, aneu a la pestanya dades (o Power Consultasi l'heu instal·lat com a complement independent) i seleccioneu Des d'Internet (Des del web). A la finestra que s'obre, enganxeu l'enllaç a l'any corresponent, copiat del lloc:
Després de fer clic OK apareix una finestra de vista prèvia, en la qual cal fer clic al botó Converteix dades (Transformar dades) or Per canviar les dades (Edita dades) i arribarem a la finestra de l'editor de consultes de Power Query, on continuarem treballant amb les dades:
Immediatament, podeu suprimir amb seguretat al tauler dret Paràmetres de sol·licitud (Configuració de la consulta) pas tipus modificat (Tipus canviat) No el necessitem.
La taula de la columna de vacances conté codis i descripcions dels dies no laborables; podeu veure el seu contingut fent-hi clic dues vegades fent clic a la paraula verda. Taula:
Per tornar enrere, haureu d'esborrar al panell dret tots els passos que han tornat a aparèixer font (Font).
La segona taula, a la qual es pot accedir de manera similar, conté exactament el que necessitem: les dates de tots els dies no laborables:
Queda per processar aquesta placa, a saber:
1. Filtreu només les dates de vacances (és a dir, les) per la segona columna Atribut: t
2. Suprimeix totes les columnes excepte la primera, fent clic amb el botó dret a l'encapçalament de la primera columna i seleccionant l'ordre Suprimeix altres columnes (Elimina altres columnes):
3. Dividiu la primera columna per punt per separat per al mes i el dia amb l'ordre Dividir la columna: per delimitador llengüeta Transformació (Transformar — Dividir columna — Per delimitador):
4. I finalment creeu una columna calculada amb dates normals. Per fer-ho, a la pestanya Afegint una columna feu clic al botó Columna personalitzada (Afegeix una columna — Columna personalitzada) i introduïu la fórmula següent a la finestra que apareix:
=#datat(2020, [#»Atribut:d.1″], [#»Atribut:d.2″])
Aquí, l'operador #date té tres arguments: any, mes i dia, respectivament. Després de fer clic a OK obtenim la columna necessària amb les dates normals del cap de setmana i suprimim les columnes restants com al pas 2
Pas 2. Convertir la sol·licitud en una funció
La nostra següent tasca és convertir la consulta creada per al 2020 en una funció universal per a qualsevol any (el número de l'any serà el seu argument). Per fer-ho, fem el següent:
1. Ampliant (si encara no s'ha ampliat) el panell Consultes (Consultes) a l'esquerra a la finestra de Power Query:
2. Després de convertir la sol·licitud en una funció, la possibilitat de veure els passos que componen la sol·licitud i editar-los fàcilment, malauradament, desapareix. Per tant, té sentit fer una còpia de la nostra petició i divertir-se ja amb ella, i deixar l'original en reserva. Per fer-ho, feu clic amb el botó dret al panell esquerre de la nostra sol·licitud de calendari i seleccioneu l'ordre Duplicar.
Si torneu a fer clic amb el botó dret a la còpia resultant del calendari(2), seleccionareu l'ordre Rebatejar (Canviar el nom) i introduïu un nom nou, que sigui, per exemple, fxYear:
3. Obrim el codi font de la consulta en el llenguatge intern de Power Query (s'anomena succintament "M") mitjançant l'ordre Editor avançat llengüeta Crítica(Veure — Editor avançat) i fer-hi petits canvis per convertir la nostra sol·licitud en una funció per a qualsevol any.
Va ser:
Després:
Si esteu interessats en els detalls, aquí:
- (any com a nombre) => – declarem que la nostra funció tindrà un argument numèric: una variable any
- Enganxant la variable any a l'enllaç web al pas font. Com que Power Query no us permet enganxar números i text, convertim el número de l'any en text sobre la marxa mitjançant la funció Number.ToText
- Substituïm la variable de l'any per 2020 en el penúltim pas #"S'ha afegit un objecte personalitzat«, on vam formar la data a partir dels fragments.
Després de fer clic Finish la nostra petició esdevé una funció:
Pas 3. Importa els calendaris de tots els anys
L'últim que queda és fer l'última consulta principal, que carregarà les dades de tots els anys disponibles i afegirà totes les dates de vacances rebudes en una taula. Per això:
1. Fem clic al panell de consultes esquerre en un espai buit gris amb el botó dret del ratolí i seleccionem seqüencialment Sol·licitud nova – Altres fonts – Sol·licitud buida (Consulta nova — Des d'altres fonts — Consulta en blanc):
2. Hem de generar una llista de tots els anys per als quals demanarem calendaris, és a dir, 2013, 2014 … 2020. Per fer-ho, a la barra de fórmules de la consulta buida que apareix, introduïu l'ordre:
Estructura:
={NombreA..NombreB}
… a Power Query genera una llista de nombres enters de A a B. Per exemple, l'expressió
={1..5}
… produiria una llista d'1,2,3,4,5.
Bé, per no estar lligats rígidament al 2020, fem servir la funció DateTime.LocalNow() – analògic de la funció Excel AVUI (AVUI) a Power Query, i extreu-ne, al seu torn, l'any actual per la funció Data.Any.
3. El conjunt d'anys resultant, tot i que sembla bastant adequat, no és una taula per a Power Query, sinó un objecte especial: llista (Llista). Però convertir-lo en una taula no és cap problema: només cal que feu clic al botó A taula (A la taula) a la cantonada superior esquerra:
4. Línia de meta! Aplicant la funció que hem creat anteriorment fxYear a la llista d'anys resultant. Per fer-ho, a la pestanya Afegint una columna prem el botó Truqueu a la funció personalitzada (Afegeix una columna: invoca la funció personalitzada) i establiu el seu únic argument: la columna Column1 al llarg dels anys:
Després de fer clic OK la nostra funció fxYear la importació funcionarà al seu torn per a cada any i obtindrem una columna on cada cel·la contindrà una taula amb les dates dels dies no laborables (el contingut de la taula és clarament visible si feu clic al fons de la cel·la al costat de la paraula Taula):
Queda per ampliar el contingut de les taules imbricades fent clic a la icona amb fletxes dobles a la capçalera de la columna Dates (marca Utilitzeu el nom de la columna original com a prefix es pot eliminar):
… i després de fer clic OK aconseguim el que volíem: una llista de totes les vacances des del 2013 fins a l'any actual:
La primera columna, ja innecessària, es pot suprimir i, per a la segona, definir el tipus de dades data (Data) a la llista desplegable de l'encapçalament de la columna:
La consulta en si es pot canviar el nom d'una cosa més significativa que Sol·licitud 1 i després carregueu els resultats al full en forma de taula "intel·ligent" dinàmica mitjançant l'ordre tancar i descarregar llengüeta Home (Inici — Tancar i carregar):
Podeu actualitzar el calendari creat en el futur fent clic amb el botó dret a la taula o consultant al panell dret mitjançant l'ordre Actualitza i desa. O utilitzeu el botó Actualitza-ho tot llengüeta dades (Data — Actualitza-ho tot) o drecera de teclat Ctrl+Alt+F5.
Això és tot.
Ara no us caldrà perdre mai més temps i combustible buscant i actualitzant la llista de vacances; ara teniu un calendari de producció "perpetu". En qualsevol cas, sempre que els autors del lloc http://xmlcalendar.ru/ donen suport a la seva descendència, que espero que sigui durant molt de temps (gràcies a ells de nou!).
- Importeu la taxa de bitcoin per excel·lir des d'Internet mitjançant Power Query
- Trobar el següent dia laborable mitjançant la funció WORKDAY
- Trobar la intersecció d'intervals de dates