Creare piano ferie Excel

In questa guida vedremo come creare un piano ferie su Excel per il 2024. Lo stesso procedimento può chiaramente essere sfruttato anche negli anni a venire!

Se ti interessa invece avere un calendario Excel per il 2024, clicca qui!

Disclaimer: la guida è stata creata con i valori del 2023, ma ho aggiornato tutti i file di download quindi troverai la stessa, identica logica traslata però sui valori del 2024!

Obiettivo del file del piano ferie 2024 su Excel

Per utilizzare il file del piano ferie basta che tu faccia le seguenti operazioni:

• Rivedi le date inserite nella tabella "TBL_FESTE" dato che sono considerate come festive
• Inserisci nelle singole tabelle il nome del dipendente e le informazioni per singolo giorno
• Aggiorna la pivot che trovi nel foglio "TBL_RECAP_AUTO_PIVOT"
• Et voilà!

Stiamo creando un file per il piano ferie da gestire su Excel che ci permetta di:

• Inserire velocemente le informazioni sui singoli dipendenti
• Differenziare le informazioni per singolo mese
• Visualizzare velocemente le giornate considerate di festa
• Accorpi i dati in una singola tabella per eventuali calcoli ulteriori
• Rivedere i dati in una pivot dedicata

Se invece vuoi scoprire di più su come è stato creato il file, vediamo insieme come procedere per creare un file completamente dinamico su Excel.

Feste da non considerare per il piano ferie su Excel

Il file presenta una tabella chiamata TBL_FESTE che indica le giornate considerate di festa. Per semplicità ho inserito tutte le date nel week-end dell'anno, oltre ad alcune feste comandate quali Natale, 25 Aprile e così via.

Il vantaggio di creare una tabella è che puoi modificare tutte queste informazioni a tuo piacimento: se ad esempio il weekend è lavorativo, puoi eliminare tutte le date che non sono pertinenti dalla tabella. Se ci sono altre feste personalizzate e importanti per l'azienda, puoi aggiungere le date che ti interessano.

Come noterai, nella colonna C c'è una ulteriore colonna chiamata "FESTE_TESTO". Questa colonna si calcola in automatico e si rende necessaria per una condizione di formattazione condizionale - vogliamo infatti visualizzare, su ogni singolo mese, quali siano le giornate considerate festive.

Vediamo la formula nel dettaglio:

=CONCAT(
SE(LUNGHEZZA(GIORNO([@FESTE]))=1;"0"&GIORNO([@FESTE]);GIORNO([@FESTE]));"/";
SE(LUNGHEZZA(MESE([@FESTE]))=1;"0"&MESE([@FESTE]);MESE([@FESTE]));"/";ANNO([@FESTE]))

Questa formula in Excel utilizza la funzione CONCAT (CONCATENA farebbe la stessa operazione) per unire diverse stringhe in un unico valore. In buona sostanza vogliamo ricostruire la data inserita nella colonna "FESTE" sfruttando le funzioni GIORNO, MESE, e ANNO per estrarre rispettivamente il giorno, il mese, e l'anno della data stessa.

La funzione SE viene utilizzata per verificare la lunghezza del valore restituito dalla funzione GIORNO o MESE e, se è uguale a 1, viene concatenato uno zero all'inizio del valore. Questo serve a garantire che la data sia sempre visualizzata in formato gg/mm/aaaa, altrimenti potremmo avere dei problemi avendo date come 01/01/2023 oppure 1/1/2023 - lavorando con le date in formato testo, non possiamo avere questi casi altrimenti alcune condizioni non funzionerebbero.

Un'alternativa più elegante poteva essere questa:

=LET(

giorno;TESTO(GIORNO([@FESTE]);"0");
mese;TESTO(MESE([@FESTE]);"0");
anno;TESTO(ANNO([@FESTE]);"0");

CONCAT(
SE(LUNGHEZZA(giorno)=1;"0"&giorno;giorno);"/";
SE(LUNGHEZZA(mese)=1;"0"&mese;mese);"/";anno))

Questa formula è simile alla prima, ma utilizza la funzione LET per assegnare valori intermedi a variabili locali. La funzione TESTO viene utilizzata per formattare i valori estratti dalle funzioni GIORNO, MESE, e ANNO come stringhe. Sicuramente risulta più leggibile per l'utente, ma è compatibile solo con le ultime versioni di Excel (da Excel 2021 in poi) quindi non la troverai sul file. Se hai Excel 365, LET è una funzione fantastica perchè permette di chiarire, senza indecisioni, quali siano i parametri dei quali hai bisogno per effettuare i calcoli seguenti.

Inseriamo anche una tabella chiamata TBL_MESE dove a 1 corrisponde GEN, a 2 corrisponde FEB, a 3 MAR e così via. Questo passaggio è solo per una questione di visualizzazione finale.

Piano ferie mensile Excel per singolo dipendente

L'idea è quella di avere un piano ferie mensile che accorpi le informazioni per il singolo dipendente, sempre su un file Excel. Ogni mese avrà una sua tabella, che per semplicità abbiamo inserito nel trimestre di riferimento, altrimenti avremmo avuto 12 fogli e il file sarebbe potuto sembrare meno accessibile.

Ci basterà fare le seguenti operazioni:

• Inserire il nome del dipendente
• Indicare "LAVORO" per le giornate indicate come lavorative
• Non inserire niente nelle giornate considerate di festa
• Indicare altre possibilità quali "SMART", "FERIE" o "ALTRO" nelle giornate dedicate

Il file è completamente dinamico e quindi esula dalla scelta che ho eseguito io, se preferisci indicare altri nomi non c'è nessun problema. Puoi scegliere in autonomia e, volendo, sostituire quanto già presente con Home > Trova e Seleziona > Trova e sostituisci.

L'unica differenza fra le varie tabelle sarà la condizione di formattazione condizionale, che nel caso della TBL_GENNAIO è questa:

=CONTA.SE(FESTE!$C:$C;B$2)=1

Questa formula verifica se la data è inserita nel foglio FESTE, in particolare nella colonna FESTE_TESTO. La colonna FESTE_TESTO era quella creata in precedenza con le varie funzioni CONCAT e TESTO - se la data è un giorno di festa, allora sarà in rosso e con un altro sfondo. Altrimenti, sarà senza formattazione aggiuntiva.

La condizione B$2 sta indicando che vogliamo che tutta la colonna sia colpita dalla formattazione condizionale, dato che agirà sempre sulla riga 2 ma ignorando la condizione di colonna.

Le formule per le altre tabelle sono praticamente uguali, cambierà solo la riga di riferimento - per TBL_FEBBRAIO ad esempio la formula sarà:

=CONTA.SE(FESTE!$C:$C;B$8)=1

Non ci sono altre formule sul foglio, bisognerà solo stare attenti a far sì che le tabelle abbiano lo spazio corretto evitando di avere righe vuote.

Lo stesso procedimento viene ripetuto per gli altri tre fogli che presentano, ciascuno, tre mesi.

Come creare il piano ferie mensile su Excel

I piani ferie mensili devono essere accorpati per poi lavorare su una singola tabella Excel. In questo caso lo strumento fondamentale è Power Query, che ci permette di ottenere automaticamente tutte le informazioni inserite compiendo vari passaggi senza utilizzare codice. Lato formule tutta questa procedura sarebbe veramente complessa, quindi meglio lasciar perdere.

Come caricare una tabella Excel su Power Query

Dobbiamo caricare le tabelle per singolo mese, che poi creeranno una sola tabella di uscita - Power Query è perfetto per questo tipo di integrazioni, dato che abbiamo numerose complessità di fondo quali:

• Le colonne sono tutte diverse fra di loro, poichè a ognuna corrisponde un giorno
• Le righe sono diverse da tabella a tabella, poichè non tutti i dipendenti saranno attivi in tutti i mesi

Carichiamo quindi tutte le 12 tabelle seguendo la stessa procedura, che è:

• Selezionare la tabella che ci interessa
• Cliccare su Dati > Recupera Dati > Da altre origini > Da tabella/intervallo
• Cliccare su Trasforma Dati
• Rivedere le modifiche del tipo e poi cliccare su Chiudi e Carica in > Solo connessione

Magari ti starai chiedendo perchè sia necessario ricaricare la stessa tabella su Power Query. Il punto fondamentale è che questi dati devono essere lavorati e teniamo quindi separate la parte di inserimento (la singola tabella) con la parte finale di analisi. Se caricassimo i dati non come Solo connessione, allora avremmo una replica della stessa tabella in un'altra tabella e questa informazione non ci serve. Ci basta caricare come sola connessione perchè poi accorperemo i dati per trimestre e infine li metteremo assieme per tutti gli anni.

Come fare un merge di query su Power Query in Excel

Una volta che avremo caricato tutte le 12 tabelle su Power Query, il passaggio successivo sarà creare una tabella che riassuma il primo trimestre:

1. Prendiamo la TBL_GENNAIO e poi clicchiamo su Home > Merge di query > Unisci query come nuova selezionando la TBL_FEBBRAIO. Attenzione a selezionare correttamente il tipo di Join che sarà Full Outer: dato che alcuni dipendenti potrebbero non esserci in alcuni mesi, dobbiamo assicurarci di non perderceli per strada. Con Full Outer, avremo i vari dipendenti o sulla stessa riga (se presenti in più tabelle) o uno sotto l'altro (se presenti solo in alcuni mesi)
2. Ripetiamo poi la procedura inserendo anche la TBL_MARZO, sempre come Merge di query
3. Facciamo esplodere le singole colonne cliccando sulla freccia nella singola tabella, sia su TBL_FEBBRAIO sia su TBL_MARZO
4. Creiamo la colonna condizionale con Aggiungi colonna > Colonna condizionale per recuperare il nome del dipendente: la prima volta che viene trovato un valore testuale in tutte le colonne dipendenti, vogliamo che questo sia restituito in una nuova colonna che diventerà la nostra colonna fondante
5. Selezioniamo le colonne intermedie con il nome del dipendente e le cancelliamo con Home > Rimuovi colonne, dato che ormai l'abbiamo recuperato con il passaggio precedente

Vediamolo più nel dettaglio.

Se hai dato un'occhiata, vedrai che sembra lunghissimo ma il procedimento è relativamente semplice:

Dobbiamo utilizzare Table.NestedJoin di M (il linguaggio di Power Query) per unire tre tabelle: "TBL_GENNAIO", "TBL_FEBBRAIO" e "TBL_MARZO". Prima facciamo il merge fra TBL_GENNAIO e TBL_FEBBRAIO, per poi unire anche, con una nuova merge, la TBL_MARZO. Dobbiamo fare una outer join (JoinKind.FullOuter) basata sulla colonna "DIPENDENTE" presente in ogni tabella, dato che alcuni dipendenti potrebbero essere presenti in un mese ma non risultare in organico per altri.

Dobbiamo poi espandere tutte le tabelle utilizzando Table.ExpandTableColumn, dalla quale ricaviamo i valori per i singoli giorni e i singoli dipendenti. Una volta espanse, dobbiamo riportare tutte le dimensioni a un solo nome di dipendente e possiamo farlo creando una colonna condizionale tramite Table.AddColumn. In questo caso, la funzione verifica tutte le colonne con il nome del dipendente ("DIPENDENTE", "DIPENDENTE.1", "DIPENDENTE.2") e, non appena trova un valore non vuoto, lo restituisce.

La nostra colonna si chiamerà "DIPENDENTE_LAST", che useremo poi come chiave per aggregarla con gli altri trimestri. L'ultimo passaggio è rimuovere le tre colonne intermedie tramite Table.RemoveColumns, dato che non ci interessano più ("DIPENDENTE", "DIPENDENTE.1", "DIPENDENTE.2").

Replichiamo il passaggio anche per gli altri 3 trimestri e avremo quindi 4 query che aggregano i singoli trimestri. A questo punto non ci resta che unirle fra di loro:

Il procedimento è praticamente lo stesso visto in precedenza, dato che prima di tutto mettiamo assieme le varie query tramite Table.NestedJoin, sempre Full Outer, per poi espanderle tramite Table.ExpandTableColumn singolarmente. Avremo quindi tutti i giorni dell'anno per singolo dipendente, ma dovremo nuovamente creare una colonna condizionale con Table.AddColumn per poi rimuovere le colonne intermedie con Table.RemoveColumns. L'unica differenza rispetto alla query precedente è che utilizzeremo anche Table.ReorderColumns per avere la colonna "DIPENDENTE" come la prima della nostra tabella finale, per poi avere tutti i valori per i singoli giorni.

A questo punto non ci resta che mettere assieme tutte le informazioni in una singola tabella.

Come riassumere le informazioni del piano ferie su Excel

Ci sono vari passaggi necessari per arrivare al nostro risultato, dato che i 4 file dei singoli trimestri vanno aggiustati per essere poi utilizzati correttamente su Power Query.

Vediamo quali siano i passaggi necessari:

Questo codice in Power Query (M) effettua una serie di trasformazioni su una tabella di origine (TBL_ALL). Il processo, in sintesi, include questi passaggi:

1. Unpivot delle colonne esistenti ad eccezione della colonna "DIPENDENTE". Questa modifica accade grazie alla funzione Table.UnpivotOtherColumns - visto che dobbiamo lavorare per singolo dipendente, vogliamo che tutti i singoli valori siano riferiti ai dipendenti passando a un formato dipendente / data / attività anzichè dipendente / giorno1 / giorno2 / giorno3. Selezioniamo la colonna "DIPENDENTE" per poi cliccare su Trasforma > Trasforma altre colonne tramite UnPivot
2. Conversione del tipo di dati della colonna "DATA" in data. Questo passaggio viene eseguito con la funzione Table.TransformColumnTypes che ci permette di modificare i formati delle singole colonne
3. Estrazione del mese dalla colonna "DATA". Dobbiamo sempre stare attenti perchè su Power Query questa operazione si può fare sia aggiungendo una nuova colonna sia modificando la colonna esistente - dato che non ci interessa tenere il valore del singolo giorno, possiamo farlo con la funzione Table.TransformColumns che riassume solo il valore del mese considerato (1 per Gennaio, 2 per Febbraio e così via)
4. Rinomina la colonna "DATA" in "MESE". In questo caso dobbiamo rinominare la singola colonna dato che stiamo lavorando per mese e non più per singola data. Questo passaggio si rende possibile grazie a Table.RenameColumns
5. Unione esterna (Left Outer Join) con un'altra tabella (TBL_MESE) sulla colonna "MESE". Questo è un passaggio che potremmo anche evitare perchè Power Query potrebbe darci direttamente valori quali "gennaio", "febbraio" o "marzo" se selezioniamo nel menu a tendina nella sezione "Trasforma". Tuttavia, volendo visualizzare "GEN" per Gennaio, "FEB" per Febbraio e così via, aggiungiamo una tabella che presenta questa informazioni in formato testuale. Anche in questo caso, come in quelli precedenti dove accorpavamo dei dati per trimestre, si rende necessaria Table.NestedJoin
6. Espansione della colonna univoca "TBL_MESE" in una colonna separata "MESE_DETTAGLIO". Con Table.ExpandTableColumn possiamo espandere il valore testuale del mese in un singolo passaggio
7. Raggruppamento delle righe per "DIPENDENTE", "MESE_DETTAGLIO", "LAVORO" e "MESE" e conteggio del numero di righe per ogni gruppo. In questo caso stiamo contando il numero di righe corrispondenti a ogni dipendente per mese in base alla loro attività, definita nella colonna "LAVORO". Avremo quindi, grazie a Table.Group, una colonna chiamata "GIORNATE" che indicherà il totale dei giorni per singolo dipendente
8. Ordinamento delle righe in base a "DIPENDENTE", "MESE" e "LAVORO" in ordine crescente. Questo è un passaggio puramente a discrezione dell'utente, ma in questo caso la tabella finale sarà ordinata con Table.Sort prima per il cognome del dipendente, poi per il valore numerico del mese e poi per la tipologia di attività

Alla fine, il codice restituisce la tabella ordinata. Possiamo poi creare dei gruppi di query cliccando col tasto destro su una singola query > Nuovo gruppo. In questo modo avremo un file coerente e facile da aggiornare.

La nostra struttura di query sarà così suddivisa:

• Trimestre 1,2,3,4: qui abbiamo inserito i singoli valori delle tabelle mensili
• TRIMESTRI_OVERALL: qui abbiamo il recap dei singoli trimestri
• RECAP: qui abbiamo la TBL_ALL (che aggrega i singoli trimestri), la TBL_RECAP che ne è la diretta lavorazione e la TBL_RECAP_AUTO_PIVOT che è esattamente come TBL_RECAP ma viene caricata direttamente sotto forma di tabella pivot
• QUERY_RACCORDO: qui abbiamo TBL_MESE e TBL_FESTE

Come visualizzare il piano ferie su Excel

A questo punto si aprono due strade, prima di caricare i dati del piano ferie per poi analizzarli su Excel:

• Se non ci interessa avere una tabella intermedia, dato che non faremo ulteriori operazioni di analisi, possiamo fare direttamente Chiudi e Carica in > In un rapporto di tabella pivot. Il vantaggio è che l'aggiornamento della pivot sarà automatico: ogni volta che modifichiamo i dati di partenza, ci basterà aggiornare i dati della pivot per avere gli ultimi dati disponibili
• Se invece ci interessa avere una tabella intermedia, possiamo invece selezionare Chiudi e Carica in > Tabella. In questo caso il vantaggio è che il dataset può essere sfruttato anche per altri calcoli, più complessi se caricassimo direttamente le informazioni su pivot

Nel secondo caso, se creassimo una pivot dovremmo ricordarci, post modifica dei dati di partenza, di:

• Aggiornare la tabella (Dati > Query e Connessioni > Aggiorna tutti)
• Aggiornare la pivot (cliccare sulla tabella pivot > Analisi tabella pivot > Aggiorna)

Attenzione a non saltare questo passaggio perchè aggiornare solamente la pivot non porterebbe ad alcuna modifica, lato analisi. Dobbiamo prima aggiornare la tabella (che aggiorna i vari passaggi visti in precedenza con Power Query) per poi aggiornare la pivot. Se hai qualche dubbio su come creare le tabelle pivot, puoi consultare questa guida dedicata.

Se scarichi il file, troverai entrambe le versioni: TBL_RECAP_AUTO_PIVOT crea direttamente la pivot, mentre TBL_RECAP è la tabella intermedia che è l'origine della PIVOT presente nel foglio PIVOT.

Come noti dall'immagine, ho inserito:

• DIPENDENTE in Righe
• LAVORO in Colonne
• REVIEW in Valori

In questo modo avremo tutti i dipendenti sulle singole righe e potremo visualizzare velocemente tutte le informazioni con un grafico cliccando su Inserisci > Grafico. Inseriamo anche un filtro dati cliccando su Inserisci > Filtro dei dati e selezionando "MESE_DETTAGLIO" - in questo modo lo slicer avrà i nomi dei mesi come GEN, FEB, MAR e così via.

Recap: come creare un piano ferie su Excel

Come abbiamo visto, creare un piano ferie su Excel può essere più complesso del previsto a causa di alcuni problemi strutturali nell'inserimento delle informazioni. Dovremo stare attenti a inserire informazioni coerenti in modo da semplificare tutta la parte finale di analisi - in questo Power Query ci permette di creare una struttura completamente dinamica, aggiornabile con un solo click. Tutto lo sforzo di progettazione viene ripagato perchè la stessa struttura, con piccole modifiche, potrebbe essere utilizzata anche negli anni a venire: basterà aggiornare le singole tabelle mensili e le feste per poi avere tutto nuovamente funzionante senza ulteriori passaggi aggiuntivi.

Disclaimer: il codice di M è stato reso più semplice da visualizzare tramite Power Query Formatter, un tool online completamente gratuito per migliorare la leggibilità di M.

Marco Filocamo

Sono un formatore e consulente esperto nell’uso e nell’insegnamento di Microsoft Excel.

Negli ultimi 3 anni ho tenuto corsi presso realtà in multinazionali come Aruba, Bridor, IMI Orton, Primadonna e SISAL, oltre a PMI e startup di diverso genere.

Realizzo corsi di formazione Excel dedicati per aziende, supporto professionisti 1:1 a distanza con call mirate e collaboro con aziende offrendo servizi di consulenza quali creazione di business plan, dashboard di vendita e non solo.

Vuoi saperne di più? Prenota una call gratuita di 15′!