Calendario di fabbrica in Excel

U calendariu di produzzione, vale à dì una lista di e date, induve tutti i ghjorni di travagliu ufficiali è e vacanze sò marcati in cunseguenza - una cosa assolutamente necessaria per qualsiasi utilizatore di Microsoft Excel. In pratica, ùn pudete micca fà senza:

  • in i calculi contabili (salariu, durata di u serviziu, vacanze ...)
  • in logistica - per a determinazione curretta di i tempi di consegna, tenendu in contu i weekend è i vacanze (ricurdatevi di u classicu "vene dopu à e vacanze?")
  • in a gestione di u prughjettu - per l'estimazione curretta di i termini, tenendu in contu, di novu, ghjorni di travagliu-non-travagliu
  • ogni usu di funzioni cum'è GIORNU DI LAVORO (ghjornu di travagliu) or LAVORATORI PURI (GIORNI DI RETE), perchè necessitanu una lista di vacanze cum'è argumentu
  • quandu si usanu funzioni di Time Intelligence (cum'è TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, etc.) in Power Pivot è Power BI
  • ... etc etc. - assai esempi.

Hè più faciule per quelli chì travaglianu in sistemi ERP corporativi cum'è 1C o SAP, postu chì u calendariu di produzzione hè custruitu in elli. Ma chì circa l'utilizatori di Excel?

Pudete, sicuru, mantene un tali calendariu manualmente. Ma allora vi tuccherà à aghjurnà almenu una volta à l'annu (o ancu più spessu, cum'è in u "jolly" 2020), ingressu cù cura tutti i weekend, trasferimenti è ghjorni non-working inventati da u nostru guvernu. E poi ripetite sta prucedura ogni annu dopu. A noia.

Chì ne dite di andà un pocu pazzi è di fà un calendariu di fabbrica "perpetuu" in Excel? Quellu chì si aghjurnà, piglia dati da Internet è sempre genera una lista aghjurnata di ghjorni non-travagliati per l'usu sussegwente in ogni calculu? Tentazione ?

Per fà questu, in fattu, ùn hè micca difficiule.

Surghjente di dati

A quistione principale hè induve uttene i dati? In cerca di una fonte adatta, aghju passatu parechje opzioni:

  • I decreti originali sò publicati nantu à u situ web di u guvernu in formatu PDF (quì, unu di elli, per esempiu) è sparisce subitu - l'infurmazioni utili ùn ponu esse tirati fora di elli.
  • 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.

Disillusioned with official sources, aghju cuminciatu à scavà micca ufficiali. Ci sò assai di elli in Internet, ma a maiò parte di elli, di novu, sò completamente inadatti per impurtà in Excel è dà un calendariu di produzzione in forma di belli ritratti. Ma ùn tocca à noi di appiccà lu à u muru, nò ?

È in u prucessu di ricerca, una cosa maravigliosa hè stata scuperta accidentalmente - u situ http://xmlcalendar.ru/

Calendario di fabbrica in Excel

Senza "frills" innecessarii, un situ simplice, ligeru è veloce, affilatu per un compitu - per dà à tutti un calendariu di produzzione per l'annu desideratu in u format XML. Eccellente!

Sè, di colpu, ùn site micca in u sapè, allura XML hè un furmatu di testu cù cuntenutu marcatu cù speciale . Ligeru, cunvene è leghjite da a maiò parte di i prugrammi muderni, cumpresu Excel.

In casu, aghju cuntattatu l'autori di u situ è ​​anu cunfirmatu chì u situ esiste per 7 anni, i dati nantu à questu hè constantemente aghjurnatu (ancu anu un ramu in github per questu) è ùn anu micca chjusu. È ùn m'importa micca chì tù è aghju caricate dati da ellu per qualsiasi di i nostri prughjetti è calculi in Excel. Hè liberu. Hè bellu di sapè chì ci hè sempre ghjente cusì ! rispettu !

Resta à carica queste dati in Excel utilizendu l'add-in Power Query (per e versioni di Excel 2010-2013 pò esse scaricatu gratuitamente da u situ di Microsoft, è in versioni di Excel 2016 è più recenti hè digià integratu per difettu. ).

A logica di l'azzioni serà a siguenti:

  1. Facemu una dumanda per scaricà dati da u situ per ogni annu
  2. Trasformendu a nostra dumanda in una funzione
  3. Applicamu sta funzione à a lista di tutti l'anni dispunibuli, à partesi da 2013 è finu à l'annu currentu - è avemu un calendariu di produzzione "perpetuu" cù l'aghjurnamentu automaticu. Voila!

Passu 1. Import un calendariu per un annu

Prima, caricate u calendariu di produzzione per ogni annu, per esempiu, per 2020. Per fà questu, in Excel, andate à a tabulazione. Dati (o dumanda di putenzase l'avete installatu cum'è un add-on separatu) è selezziunate Da Internet (Da u Web). In a finestra chì si apre, incollà u ligame à l'annu currispundente, copiatu da u situ:

Calendario di fabbrica in Excel

Dopu avè cliccatu OK una finestra di vista previa, in quale avete bisognu di cliccà u buttone Cunvertite Dati (Trasforma i dati) or Per cambià i dati (Modificà i dati) è andemu à a finestra di l'editore di query Power Query, induve continueremu à travaglià cù i dati:

Calendario di fabbrica in Excel

Immediatamente vi ponu sguassà in modu sicuru in u panel dritta Richiesta di Parametri (Configurazione di a dumanda) passu tipu mudificatu (Tipu cambiatu) Ùn avemu micca bisognu di ellu.

A tavula in a colonna di vacanze cuntene codici è descrizzioni di i ghjorni non-lavorativi - pudete vede u so cuntenutu "fallendu" duie volte clicchendu nantu à a parolla verde. Table:

Calendario di fabbrica in Excel

Per andà in daretu, vi tuccherà à sguassà in u panel dritta tutti i passi chì sò apparsu daretu à surghjente (Source).

A seconda tavula, chì pò esse accessu in una manera simile, cuntene esattamente ciò chì avemu bisognu - e date di tutti i ghjorni non-working:

Calendario di fabbrica in Excel

Resta à trattà sta piastra, à dì:

1. Filtrà solu e date di vacanze (vale à dì quelli) da a seconda colonna Attributu: t

Calendario di fabbrica in Excel

2. Sguassate tutte e culonne eccettu u primu - clicchendu u dirittu nantu à l'intestazione di a prima colonna è selezziunate u cumandamentu Elimina altre colonne (Eliminate l'altri colonne):

Calendario di fabbrica in Excel

3. Divide a prima colonna per puntu separatamente per u mese è u ghjornu cù u cumandamentu Split Colonna - By Delimiter Insignia Mutazioni (Trasforma - Split column - By delimiter):

Calendario di fabbrica in Excel

4. È infine crea una colonna calculata cù date nurmale. Per fà questu, nantu à a tabulazione Aghjunghjendu una colonna cliccate nantu à u buttone Colonna persunalizata (Aggiungi Colonna - Colonna Personalizzata) è inserite a seguente formula in a finestra chì appare:

Calendario di fabbrica in Excel

=#datatu(2020, [#»Attributu:d.1″], [#»Attributu:d.2″])

Quì, l'operatore #date hà trè argumenti: annu, mese è ghjornu, rispettivamente. Dopu à cliccà nantu OK avemu a colonna necessaria cù e date nurmale di u weekend, è sguassate e colonne rimanenti cum'è in u passu 2

Calendario di fabbrica in Excel

Step 2. Turning a dumanda in una funzione

U nostru prossimu compitu hè di cunvertisce a quistione creata per 2020 in una funzione universale per ogni annu (u numeru di l'annu serà u so argumentu). Per fà questu, facemu i seguenti:

1. Espansione (se micca digià allargatu) u pannellu Richieste (Domande) à a manca in a finestra Power Query:

Calendario di fabbrica in Excel

2. Dopu à cunvertisce a dumanda à una funzione, a capacità di vede i passi chì custituiscenu a dumanda è facilmente edità, sfurtunatamenti, sparisce. Per quessa, hè sensu di fà una copia di a nostra dumanda è frolic digià cun ella, è lasciate l'uriginale in riserva. Per fà questu, fate un clic right in u pane di manca nantu à a nostra dumanda di calendariu è selezziunate u cumandamentu Duplicate.

Facendu clic drittu novu nantu à a copia resultanti di u calendariu (2) selezziunà u cumandamentu Rename (Rinomina) è entre un novu nome - lasciate esse, per esempiu, fxYear:

Calendario di fabbrica in Excel

3. Apermu u codice fonte di a dumanda in a lingua interna Power Query (hè chjamatu succintamente "M") usendu u cumandamentu Editor avanzatu Insignia recensione(Vede - Editor avanzatu) è fà picculi cambiamenti quì per trasfurmà a nostra dumanda in una funzione per ogni annu.

Hè statu:

Calendario di fabbrica in Excel

Dopu:

Calendario di fabbrica in Excel

Sè site interessatu à i dettagli, allora quì:

  • (annu cum'è numeru) =>  - dichjaremu chì a nostra funzione averà un argumentu numericu - una variàbile annu
  • Incolla a variabile annu à u ligame web in u passu surghjente. Siccomu Power Query ùn vi permette micca di incollare numeri è testu, cunvertemu u numeru di l'annu in testu à a mosca usendu a funzione Number.ToText
  • Sustituemu a variabile annu per 2020 in u penultimu passu #"Oggettu persunalizatu aghjuntu«, induve avemu furmatu a data da i frammenti.

Dopu avè cliccatu Finisci a nostra dumanda diventa una funzione:

Calendario di fabbrica in Excel

Step 3. Import calendari per tutti l'anni

L'ultima cosa chì resta hè di fà l'ultima quistione principale, chì caricarà dati per tutti l'anni dispunibili è aghjunghje tutte e date di vacanze ricevute in una sola tavola. Per questu:

1. Facemu clic in u pannellu di dumanda di manca in un spaziu viotu grisgiu cù u buttone drittu di u mouse è selezziunate in sequenza Nova dumanda - Altre fonti - Richiesta viota (Nova Query - Da altre fonti - Query in biancu):

Calendario di fabbrica in Excel

2. Avemu bisognu di generà una lista di tutti l'anni per quale avemu da dumandà calendari, vale à dì 2013, 2014 ... 2020. Per fà questu, in a barra di formula di a dumanda viota chì appare, entre u cumandimu:

Calendario di fabbrica in Excel

Struttura:

={NumberA..NumberB}

... in Power Query genera una lista di interi da A à B. Per esempiu, l'espressione

= {1..5}

... pruducerà una lista di 1,2,3,4,5.

Ebbè, per ùn esse ligatu rigidamente à 2020, usemu a funzione DateTime.LocalNow() - analogu di a funzione Excel O'zbekcha (OGGI) in Power Query - è estrae da ellu, à u turnu, l'annu currentu da a funzione Data.Annu.

3. U risultatu d'anni, ancu s'ellu pare abbastanza adattatu, ùn hè micca una tavola per Power Query, ma un oggettu speciale - lists (Lista). Ma cunvertisce à una tavula ùn hè micca un prublema: basta à cliccà u buttone À a tavula (à a tavula) in l'angulu superiore manca:

Calendario di fabbrica in Excel

4. Finish line! Applicà a funzione chì avemu creatu prima fxYear à a lista resultanti di anni. Per fà questu, nantu à a tabulazione Aghjunghjendu una colonna premi u buttone Chjama a funzione persunalizata (Aggiungi Colonna - Invoca Funzione Personalizzata) è stabilisce u so solu argumentu - a colonna Column1 annantu à l'anni:

Calendario di fabbrica in Excel

Dopu avè cliccatu OK a nostra funzione fxYear l'importazione hà da travaglià à turnu per ogni annu è avemu da ottene una colonna induve ogni cellula cuntene una tavula cù e date di i ghjorni non di travagliu (u cuntenutu di a tavula hè chjaramente visibule se cliccate in u fondu di a cellula vicinu à a parolla Table):

Calendario di fabbrica in Excel

Resta per espansione u cuntenutu di e tavule nidificate clicchendu nantu à l'icona cù freccia doppia in l'intestazione di a colonna Dates (tick Aduprate u nome di a colonna originale cum'è prefissu pò esse eliminata):

Calendario di fabbrica in Excel

... è dopu à cliccà OK avemu ciò chì vulemu - una lista di tutte e vacanze da 2013 à l'annu currentu:

Calendario di fabbrica in Excel

A prima colonna, digià innecessaria, pò esse sguassata, è per a seconda, stabilisce u tipu di dati data (Data) in a lista a tendina in l'intestazione di a colonna:

Calendario di fabbrica in Excel

A dumanda stessu pò esse rinominata qualcosa di più significativu chè dumanda 1 è poi caricate i risultati à u fogliu in a forma di una tavula dinamica "intelligente" cù u cumandimu chiudi e scarica Insignia Home (Casa - Chiudi & Carica):

Calendario di fabbrica in Excel

Pudete aghjurnà u calendariu creatu in u futuru clicchendu u dirittu nantu à a tavola o dumandate in u pane drittu attraversu u cumandimu. Aghjurnà & Salvà. O utilizate u buttone Refresh tuttu Insignia Dati (Data - Refresh All) o scorciatoia da tastiera Ctrl+Alt+F5.

Eccu tuttu.

Avà ùn avete mai più bisognu di perde u tempu è u pensamentu di carburante per circà è aghjurnà a lista di vacanze - avà avete un calendariu di produzzione "perpetu". In ogni casu, sempre chì l'autori di u situ http://xmlcalendar.ru/ sustene a so prole, chì, spergu, serà per un tempu assai, assai longu (grazie à elli di novu!).

  • Import bitcoin rate to excel from the internet via Power Query
  • Truvà u prossimu ghjornu di travagliu cù a funzione WORKDAY
  • Truvà l'intersezzione di intervalli di data

Lascia un Audiolibro