Contenuti
Calendario di produzione, ovvero un elenco di date, in cui tutti i giorni lavorativi e festivi ufficiali sono contrassegnati di conseguenza, una cosa assolutamente necessaria per qualsiasi utente di Microsoft Excel. In pratica, non puoi farne a meno:
- nei calcoli contabili (stipendio, anzianità di servizio, ferie…)
- nella logistica – per la corretta determinazione dei tempi di consegna, tenendo conto dei fine settimana e dei giorni festivi (ricordate il classico “dai dopo le vacanze?”)
- in project management – per la corretta stima dei termini, tenendo conto, ancora, dei giorni lavorativi non lavorativi
- qualsiasi uso di funzioni come GIORNO.LAVORATIVO (GIORNATA LAVORATIVA) or LAVORATORI PURI (GIORNI DI RETE), perché richiedono un elenco di festività come argomento
- quando si utilizzano le funzioni di Time Intelligence (come TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, ecc.) in Power Pivot e Power BI
- … ecc. ecc. – tanti esempi.
È più facile per coloro che lavorano in sistemi ERP aziendali come 1C o SAP, poiché il calendario di produzione è integrato in essi. Ma per quanto riguarda gli utenti di Excel?
Ovviamente puoi mantenere un tale calendario manualmente. Ma poi bisognerà aggiornarlo almeno una volta all'anno (o anche più spesso, come nel “jolly” 2020), inserendo con attenzione tutti i fine settimana, i trasferimenti e i giorni non lavorativi inventati dal nostro governo. E poi ripetere questa procedura ogni anno successivo. La noia.
Che ne dici di impazzire e creare un calendario di fabbrica "perpetuo" in Excel? Uno che si aggiorna, prende i dati da Internet e genera sempre un elenco aggiornato dei giorni non lavorativi per un successivo utilizzo in qualsiasi calcolo? Allettante?
Fare questo, infatti, non è affatto difficile.
Fonte di dati
La domanda principale è dove ottenere i dati? Alla ricerca di una fonte adatta, ho esaminato diverse opzioni:
- I decreti originali vengono pubblicati sul sito del governo in formato PDF (qui, uno di essi, per esempio) e scompaiono immediatamente: da essi non si possono estrarre informazioni utili.
- 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.
Disilluso dalle fonti ufficiali, ho iniziato a scavare quelle non ufficiali. Ce ne sono molti su Internet, ma la maggior parte di essi, ancora una volta, è completamente inadatta all'importazione in Excel e distribuisce un calendario di produzione sotto forma di bellissime immagini. Ma non spetta a noi appenderlo al muro, giusto?
E durante il processo di ricerca, è stata scoperta accidentalmente una cosa meravigliosa: il sito http://xmlcalendar.ru/
Senza inutili "fronzoli", un sito semplice, leggero e veloce, affinato per un compito: dare a tutti un calendario di produzione per l'anno desiderato in formato XML. Eccellente!
Se, all'improvviso, non sei al corrente, XML è un formato di testo con contenuto contrassegnato con uno speciale
Per ogni evenienza, ho contattato gli autori del sito e hanno confermato che il sito esiste da 7 anni, i dati su di esso sono costantemente aggiornati (hanno anche un ramo su github per questo) e non lo chiuderanno. E non mi dispiace affatto che tu e io carichiamo i dati da esso per nessuno dei nostri progetti e calcoli in Excel. È libero. È bello sapere che ci sono ancora persone così! Rispetto!
Resta da caricare questi dati in Excel utilizzando il componente aggiuntivo Power Query (per le versioni di Excel 2010-2013 può essere scaricato gratuitamente dal sito Web di Microsoft e nelle versioni di Excel 2016 e successive è già integrato per impostazione predefinita ).
La logica delle azioni sarà la seguente:
- Facciamo una richiesta per scaricare i dati dal sito per un anno
- Trasformare la nostra richiesta in una funzione
- Applichiamo questa funzione all'elenco di tutti gli anni disponibili, a partire dal 2013 e fino all'anno in corso, e otteniamo un calendario di produzione “perpetuo” con aggiornamento automatico. Ecco!
Passaggio 1. Importa un calendario per un anno
Innanzitutto, carica il calendario di produzione per un anno qualsiasi, ad esempio per il 2020. Per fare ciò, in Excel, vai alla scheda Dati (o Power Queryse l'hai installato come componente aggiuntivo separato) e seleziona Da Internet (Dal Web). Nella finestra che si apre, incolla il link all'anno corrispondente, copiato dal sito:
Dopo aver fatto clic su OK viene visualizzata una finestra di anteprima, in cui è necessario fare clic sul pulsante Converti dati (Trasforma dati) or Per modificare i dati (Modifica dati) e arriveremo alla finestra dell'editor di query di Power Query, dove continueremo a lavorare con i dati:
Immediatamente puoi eliminare in sicurezza nel pannello di destra Richiedi parametri (Impostazioni query) passo tipo modificato (Tipo modificato) Non abbiamo bisogno di lui.
La tabella nella colonna delle festività contiene codici e descrizioni dei giorni non lavorativi – puoi vederne il contenuto “scorrendola” due volte cliccando sulla parola verde Table:
Per tornare indietro, dovrai eliminare nel pannello di destra tutti i passaggi a cui sono apparsi Fonte (Fonte).
La seconda tabella, a cui si accede in modo simile, contiene esattamente ciò di cui abbiamo bisogno: le date di tutti i giorni non lavorativi:
Resta da elaborare questo piatto, vale a dire:
1. Filtra solo le date delle festività (cioè quelle) in base alla seconda colonna Attributo: t
2. Elimina tutte le colonne tranne la prima, facendo clic con il pulsante destro del mouse sull'intestazione della prima colonna e selezionando il comando Elimina altre colonne (Rimuovi altre colonne):
3. Dividi la prima colonna per punto separatamente per mese e giorno con il comando Colonna divisa - Per delimitatore linguetta Trasformazione (Trasforma — Dividi colonna — Per delimitatore):
4. E infine crea una colonna calcolata con date normali. Per fare ciò, nella scheda Aggiunta di una colonna clicca sul pulsante Colonna personalizzata (Aggiungi colonna — Colonna personalizzata) e inserisci la seguente formula nella finestra che appare:
=#datato(2020, [#»Attributo:d.1″], [#»Attributo:d.2″])
Qui, l'operatore #date ha tre argomenti: anno, mese e giorno, rispettivamente. Dopo aver cliccato su OK otteniamo la colonna richiesta con le normali date del fine settimana ed eliminiamo le colonne rimanenti come nel passaggio 2
Passaggio 2. Trasformare la richiesta in una funzione
Il nostro prossimo compito è convertire la query creata per il 2020 in una funzione universale per qualsiasi anno (il numero dell'anno sarà il suo argomento). Per fare ciò, eseguiamo le seguenti operazioni:
1. Espandendo (se non già espanso) il pannello Richieste (Interrogazioni) a sinistra nella finestra di Power Query:
2. Dopo aver convertito la richiesta in una funzione, purtroppo scompare la possibilità di vedere i passaggi che compongono la richiesta e di modificarli facilmente. Pertanto, ha senso fare una copia della nostra richiesta e scherzare già con essa e lasciare l'originale in riserva. Per fare ciò, fai clic con il pulsante destro del mouse nel riquadro sinistro sulla nostra richiesta di calendario e seleziona il comando Duplica.
Facendo di nuovo clic con il pulsante destro del mouse sulla copia risultante di calendar(2) si selezionerà il comando Rinominare (Rinominare) e inserisci un nuovo nome – lascia che sia, ad esempio, fxAnno:
3. Apriamo il codice sorgente della query nel linguaggio interno di Power Query (si chiama sinteticamente "M") utilizzando il comando Editor avanzato linguetta Review(Visualizza — Editor avanzato) e apportare piccole modifiche lì per trasformare la nostra richiesta in una funzione per qualsiasi anno.
Era:
Dopo:
Se sei interessato ai dettagli, allora qui:
- (anno come numero)=> – dichiariamo che la nostra funzione avrà un argomento numerico – una variabile anno
- Incollare la variabile anno al collegamento web nel passaggio Fonte. Poiché Power Query non consente di incollare numeri e testo, convertiamo il numero dell'anno in testo al volo utilizzando la funzione Numero.ToText
- Sostituiamo la variabile anno per il 2020 nel penultimo passaggio #"Aggiunto oggetto personalizzato«, dove abbiamo formato la data dai frammenti.
Dopo aver fatto clic su Fine la nostra richiesta diventa una funzione:
Passaggio 3. Importa i calendari per tutti gli anni
L'ultima cosa rimasta è fare l'ultima query principale, che caricherà i dati per tutti gli anni disponibili e aggiungerà tutte le date delle vacanze ricevute in un'unica tabella. Per questo:
1. Facciamo clic nel pannello delle query di sinistra in uno spazio vuoto grigio con il pulsante destro del mouse e selezioniamo in sequenza Nuova richiesta – Altre fonti – Richiesta vuota (Nuova query — Da altre fonti — Query vuota):
2. Dobbiamo generare un elenco di tutti gli anni per i quali richiederemo i calendari, ovvero 2013, 2014 … 2020. Per fare ciò, nella barra della formula della query vuota che appare, inserisci il comando:
struttura:
={NumeroA..NumeroB}
… in Power Query genera un elenco di numeri interi da A a B. Ad esempio, l'espressione
={1..5}
… produrrebbe un elenco di 1,2,3,4,5.
Ebbene, per non essere legati rigidamente al 2020, utilizziamo la funzione DateTime.LocalNow() – analogo della funzione Excel OGGI (OGGI) in Power Query - ed estrarre da esso, a sua volta, l'anno corrente dalla funzione Data.Anno.
3. Il set di anni risultante, sebbene sembri abbastanza adeguato, non è una tabella per Power Query, ma un oggetto speciale: stratagemma (Elenco). Ma convertirlo in una tabella non è un problema: basta cliccare sul pulsante A tavola (A tavola) nell'angolo in alto a sinistra:
4. Traguardo! Applicare la funzione che abbiamo creato in precedenza fxAnno all'elenco di anni risultante. Per fare ciò, nella scheda Aggiunta di una colonna premi il bottone Chiama la funzione personalizzata (Aggiungi colonna: richiama la funzione personalizzata) e imposta il suo unico argomento: la colonna Column1 negli anni:
Dopo aver fatto clic su OK la nostra funzione fxAnno l'importazione funzionerà a turno per ogni anno e otterremo una colonna dove ogni cella conterrà una tabella con le date dei giorni non lavorativi (il contenuto della tabella è ben visibile se si clicca sullo sfondo della cella accanto a la parola Table):
Resta da espandere il contenuto delle tabelle nidificate facendo clic sull'icona con le doppie frecce nell'intestazione della colonna Date (tic tac Usa il nome della colonna originale come prefisso può essere rimosso):
… e dopo aver cliccato su OK otteniamo ciò che volevamo: un elenco di tutte le festività dal 2013 all'anno in corso:
La prima colonna, già non necessaria, può essere eliminata e, per la seconda, impostare il tipo di dati quando (Data) nell'elenco a discesa nell'intestazione della colonna:
La query stessa può essere rinominata in qualcosa di più significativo di Richiesta1 e quindi caricare i risultati sul foglio sotto forma di tabella dinamica “intelligente” utilizzando il comando chiudi e scarica linguetta Casa (Home — Chiudi e carica):
È possibile aggiornare il calendario creato in futuro facendo clic con il pulsante destro del mouse sulla tabella o sulla query nel riquadro di destra tramite il comando Aggiorna e salva. Oppure usa il pulsante Aggiorna tutto linguetta Dati (Data — Aggiorna tutto) o scorciatoia da tastiera Ctrl+altro+F5.
È tutto.
Ora non devi più perdere tempo e pensare a cercare e aggiornare l'elenco delle festività: ora hai un calendario di produzione "perpetuo". In ogni caso, fintanto che gli autori del sito http://xmlcalendar.ru/ supportano la loro prole, che, spero, durerà per molto, molto tempo (grazie ancora a loro!).
- Importa bitcoin rate per eccellere da Internet tramite Power Query
- Trovare il giorno lavorativo successivo utilizzando la funzione GIORNO LAVORATIVO
- Trovare l'intersezione degli intervalli di date