cuntenutu
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/
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
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:
- Facemu una dumanda per scaricà dati da u situ per ogni annu
- Trasformendu a nostra dumanda in una funzione
- 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:
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:
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:
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:
Resta à trattà sta piastra, à dì:
1. Filtrà solu e date di vacanze (vale à dì quelli) da a seconda colonna Attributu: t
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):
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):
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:
=#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
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:
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:
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:
Dopu:
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:
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):
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:
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:
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:
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):
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):
... è dopu à cliccà OK avemu ciò chì vulemu - una lista di tutte e vacanze da 2013 à l'annu currentu:
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:
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):
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