www.valterborsato.it

Excel: creare un calendario dinamico con scelta dell'anno e del mese da un elenco a discesa

Per molteplici scopi può rendersi utile organizzare di mese in mese un calendario in un Foglio Excel. Calendari che possano essere velocemente configurati scegliendo semplicemente da un menu a discesa il mese e l'anno di riferimento.
Con poche Formule, e con l'ausilio dei Controlli modulo e della Formattazione condizionale, è possibile creare un calendario dinamico mensile automaticamente formattato.

In questo tutorial viene spiegato come creare un calendario mensile, scegliendo da un elenco a discesa l'anno e il mese e applicando una formattazione rossa alle date del fine settimana. 
Esistono molte opportunità per usufruire di calendari Excel. Non ultima quella di sfruttare i modelli online che Microsoft stessa propone. Il calendario dinamico che viene proposto nel presente tutorial, è una delle tante possibili soluzioni che permettono di organizzare un calendario mensile.

Contenuti del tutorial:
Costruire un calendario dinamico con scelta dell'anno e del mese da un elenco a discesa

[1] - I controlli modulo di Excel per impostare la scelta dell'anno e del mese
[2] - Utilizzare lo strumento controllo Casella combinata, per impostare la scelta del mese
[3] - Utilizzate lo strumento di controllo Casella di selezione per la scelta dell'anno
[4] - Organizzazione del calendario
[5] - Applicare la formattazione condizionale all'elenco delle date del mese
[6] - Download file con il calendario mensile con scelta dell'anno e mese di riferimento

I controlli modulo di Excel per impostare la scelta dell'anno e del mese

Per poter usufruire dei Controlli modulo, è necessario attivare sulla barra multifunzione di Excel la Scheda Sviluppo.
Per attivare la scheda Sviluppo: Menu File  >  Opzioni
Scegliere Personalizza barra multifunzione e nel menu a destra selezionare la checkbox Sviluppo.

Excel come creare un calendario dinamico, con scelta anno e mese da elenco a discesa: attivare la scheda sviluppo

Una volta attivata la scheda Sviluppo, nel gruppo pulsanti Controlli, fare clic sul pulsante Inserisci.
Si apre un menu a discesa che mette a disposizioni diversi tipi di Controlli di modulo da inserire nei Fogli Excel.
Alcuni di questi strumenti possono essere applicati per la selezionare elementi da un elenco. Ad esempio: le caselle di riepilogo, le caselle combinate, i pulsanti di selezione e le barre di scorrimento.
Nello specifico, per la creazione del menu di selezione del mese e dell'anno, verranno utilizzate rispettivamente la Casella combinata e la Casella di selezione.

Excel come creare un calendario dinamico, con scelta anno e mese da elenco a discesa: la scheda sviluppo con i Controlli modulo

Utilizzare lo strumento controllo Casella combinata, per impostare la scelta del mese

Per applicare questa funzionalità è in primo luogo necessario disporre sul Foglio Excel di un elenco di tutti i mesi dell'anno. Nell'esempio sotto raffigurato, nell'intervallo di celle K2:K13 è stato inserito l'elenco di tutti i mesi da Gennaio a Dicembre.
Per procedere nell'organizzazione del menu a discesa, un volta premuto sul pulsante Controllo modulo: Casella combinata, con un trascinamento del mouse tracciare sul Foglio di lavoro il rettangolo contenente la Casella combinata [1].
Successivamente, con la Casella combinata ancora selezionata, fare un clic con il pulsante destro del mouse e dal menu a discesa scegliere l'opzione Formato controllo.
Cliccato su Formato controllo si apre la Finestra di dialogo Formato oggetto.
Nella Finestra di dialogo Formato oggetto è necessario impostare [2] l'intervallo di input (ovvero l'intervallo dati del Foglio di lavoro che contiene l'elenco dei mesi), e il Collegamento cella.
Il collegamento è una cella di output nella quale verrà restituito un indice numerico relativo ai valori descritti nell'intervallo di input.
Esempio: se viene scelto il mese di Gennaio viene restituito il numero 1, se viene scelto Febbraio il numero 2 e così via per i mesi successivi.
La cella collegata potrà anche essere coperta dal controllo modulo stesso o da un'altra immagine grafica. Il valore contenuto nella cella servirà per definire in una successiva Funzione il mese di riferimento per la creazione del calendario.
Altezza righe, è possibile scegliere quante righe dovranno essere disponibili nel menu di scelta dei mesi. Se si volesse evitare uno scroll di ricerca basta impostare il numero 12, esattamente come le voci disponibili. Se, come nel caso dell'esempio, viene impostato il numero 8, nella menu a discesa compare automaticamente una barra di scorrimento.

Utilizzate lo strumento controllo Casella combinata, per impostare la scelta del mese

CONSIGLIO. Per posizionare il pulsante di scelta dei mesi esattamente in coincidenza con i bordi delle celle del Foglio di lavoro, basta trascinare i cerchietti (Grip di trascinamento) presenti sui lati e negli angoli del rettangolo, tenendo premuto il pulsante ALT.
Il risultato finale è visibile nell'immagine di cui sotto. Aprendo la tendina e selezionando ad esempio il mese di Giugno, nella cella A1 del Foglio di lavoro verrà restituito il numero 6.
Valore che servirà successivamente per definire le date del mese di Giugno di uno specifico anno.

OSSERVAZIONE. Apparentemente, la Casella combinata, potrebbe ricordare una Convalida dati elenco.
In realtà sono due strumenti completamente diversi. La casella Combinata è un oggetto che si sovrappone sul Foglio di lavoro e che restituisce in una specifica cella un indice numerico 1, 2, 3...

Utilizzate lo strumento controllo Casella combinata, per impostare la scelta del mese

Utilizzare lo strumento di controllo Casella di selezione per la scelta dell'anno

Il processo per creare la Casella di selezione dell'anno non è concettualmente diverso dalla precedente operazione relativa alla definizione del mese.
Per procedere nell'organizzazione della Casella di selezione, una volta premuto sul pulsante Controllo modulo scegliere Casella di selezione (vedi immagini precedenti), con un trascinamento del mouse tracciare sul Foglio di lavoro un rettangolo contenente il controllo modulo [1].
Clic con il pulsante destro sulla Casella di selezione e dal menu a discesa scegliere l'opzione Formato controllo.
Cliccando su Formato controllo si apre la Finestra di dialogo Formato oggetto.
A questo punto è possibile definire il valore ricorrente che dovrà contenere la cella B1 e nelle sottostanti celle definire il valore minino (2021) e il valore massimo (2050) ovvero l'intervallo degli anni che si vogliono considerare.

Utilizzate lo strumento di controllo Casella di selezione per la scelta dell'anno

Per posizionare il pulsante di scorrimento degli anni esattamente in coincidenza con i bordi delle celle, basta trascinare i cerchietti (Grip) presenti sui lati e negli angoli del rettangolo tenendo premuto il pulsante ALT. Il risultato finale è visibile nell'immagine sottostante.
Cliccando sul pulsante basso o alto della Casella di selezione, nella cella B1, a partire dall'anno 2021 verranno proposti tutti gli anni fino al 2050.

Utilizzate lo strumento di controllo Casella di selezione per la scelta dell'anno

Organizzazione del calendario

Per individuare il primo giorno del mese, con la Funzione DATA è necessario recuperare il numero del mese e l'anno presenti rispettivamente nelle celle A1 e A2 del Foglio di lavoro.
Nello specifico dell'esempio proposto, nella cella B7, è stata inserita la seguente Funzione: =DATA(B1;A1;1)
Nella quale:
B1 rappresenta l'anno individuato con la Casella di selezione; A1 rappresenta il numero del mese, ed infine il numero 1 individua il primo giorno del mese.
Premendo invio nella B7 comparirà la data del 01/02/2022

La Funzione DATA, per definizione, restituisce il numero seriale sequenziale che rappresenta una data specifica, la sintassi della Funzione è espressa da:
=DATA(anno;mese;giorno)
:: Per un approfondimento sulla Funzione DATA si rimanda a: Convertire date inserite in formato testuale in numeri, la Funzione DATA e DATA.VALORE

Organizzare il calendario dinamico mensile sul foglio di lavoro di Excel

Una volta ottenuta la data del primo giorno del mese, nella cella sottostante (B8) si inserisce una semplicissima Formula =B7+1 in questo modo si va aggiungere un numero al valore seriale corrispondente alla data del 01/02/2022
Una volta premuto invio, si trascina la Formula fino ad arrivare alla trentunesima cella della colonna, ovvero la cella che potrebbe contenere il trentunesimo giorno di un determinato mese.

Organizzare il calendario dinamico mensile sul foglio di lavoro di Excel

Nel caso specifico del mese di febbraio e precisamente del febbraio del 2022, l'ultima data del mese è rappresentata dal 28/2/2022 di conseguenza nel tre celle sottostanti vengono momentaneamente restituite le date dal 1 al 3 Marzo dello stesso anno.

Considerando che i mesi possono essere di 28, 29 (febbraio bisestile), 30 o 31, nelle ultime tre celle bisogna inserire una Funzione logica che valuti se la data di queste ultime celle corrisponde allo stesso mese della data contenuta nella prima cella della colonna.
Analizziamo ora come nascondere gli eventuali valori contenuti nelle ultime tre celle dell'elenco qualora non dovessero rappresentare una data che fa parte del mese corrente.

Organizzare il calendario dinamico mensile sul foglio di lavoro di Excel

Osservazione sulla Funzione logica SE applicata al calendario

La Funzione SE inserita nella cella B35, verifica se aggiungendo un giorno alla data presente nella cella B34, la data che viene restituita corrisponde alla stesso mese contenuto nella cella B7 (nella cella B7 è presente il primo giorno del mese selezionato).
Se questa comparazione fra i due mesi risulta VERA, nella cella B35, viene inserita la data successiva alla data presente nella cella B34br> Qualora questa comparazione risultasse FALSA viene inserito il valore "" che vuol dire stringa vuota!
Ovviamente, se ci trovassimo un mese di 29, 30 o 31 giorni, nelle successive celle viene effettuata la stessa comparazione logica.
Il risulta finale dell'applicazione di queste Funzioni, è che l'elenco delle date del mese presenti nella colonna B si interromperanno sempre con l'ultimo giorno disponibile del mese corrente.

OSSERVAZIONE. per confrontare il numero corrispondente ai mesi delle due date, nel Test logico della Funzione SE è stata annidata la Funzione MESE. 

Applicare la formattazione condizionale all'elenco delle date del mese

Quale ultima impostazione del calendario, si vuole prevedere l'automatica restituzione dei una formattazione testo di colore rosso per le date che coincidono con i fine settimana.
Anche in questo caso si tratta di applicare una Funzione Logica, per verificare se nelle celle è contenuta una data che corrisponde ad un sabato o una domenica.

Applicare la formattazione condizionale di Excel all'elenco delle date del mese

Impostare la Formattazione condizionale del calendario

Per ottenere automaticamente le date del sabato e domenica colorate in rosso, bisogna applicare una Formattazione condizionale che preveda l'utilizzo di una Funzione.

Una volta selezionata la prima data del mese, nella scheda principale Home, gruppo pulsanti Stili fare clic su Formattazione condizionale e nelle sottostanti opzioni scegliere Nuova Regola.
La scelta di applicare una Nuova regola apre la Finestra di dialogo Modifica regola formattazione,, (vedi immagine precedente) nella prima parte di questo menu scegliere l'opzione Utilizza una formula per determinare le celle da formattare.

A questo punto, nel successivo campo, va definita una Funzione logica che permetta la restituzione del VERO o del FALSO.
Quando il valore restituito è VERO viene applicata la formattazione individuata nel campo sottostante (dove è presente l'Anteprima).
Il formato da applicare viene scelto cliccando sul pulsante strong>Formato...

:: Per un approfondimento passo per passo, sull'utilizzo di una formula per determinare le celle da formattare, si rimanda a: Creare Formattazioni condizionali applicando Formule e Funzioni.

Per individuare se la data corrisponde ad un sabato o una domenica nella Formula (per determinare le celle da formattare) è stata inserita la Funzione GIORNO.SETTIMANA
La Funzione GIORNO.SETTIMANA restituisce il giorno della settimana corrispondente ad una certa data. In base all'impostazione di default i giorni vengono espressi con un numero intero compreso tra 1 (la domenica) e il 7 (sabato).
Nel nostro caso, per verificare entrambe le possibilità (domenica =1) e (sabato = 7) le due espressioni di comparazione sono state collegate con la Funzione O (connettivo logico di disgiunzione).
La Funzione inserita è pertanto la seguente: =O(GIORNO.SETTIMANA($B7)=1;GIORNO.SETTIMANA($B7)=7)
Ovvero: se il giorno della settimana risulta 1 (domenica) oppure 2 (sabato) applicare il formato testo rosso. (vedi immagine precedente)

Applicata la formattazione condizionale alla prima cella, trascinare il pulsante di riempimento tenendo schiacciato il pulsante destro del mouse fino alla trentunesima cella del calendario. Una volta rilasciato il pulsante del mouse nel menu di scelta rapida scegliere l'opzione: Ricopia solo formattazione.

:: Per una lettura più efficace delle date presenti nel calendario, è conveniente applicare il formato data estesa anzichè il formato data in cifre.. Questa impostazione è facilmente applicabile utilizzando il pulsante delle formattazioni veloci, presenti nel gruppo pulsanti Numeri della scheda Home.

Impostare la Formattazione condizionale del calendario

Una volta applicate tutte le formattazioni. il risultato finale è consultabile nella sottostante immagine.
OSSERVAZIONE. il valore 2 (riferito al mese di febbraio) che è fondamentale per costruire con la funzione DATA il primo giorno del mese, per esigenze di spiegazione, è stato collocato nella cella A1.
Se non si vuole che questo riferimento rimanga visibile sul Foglio di lavoro, è possibile nasconderlo sotto una immagine o un oggetto.
Nella Finestra di dialogo strong>Formato oggetto relativamente alla programmazione del controllo della Casella combinata, nel campo Collegamento cella, anzichè impostare la cella A1 può essere impostata la cella B4, che rimarrà coperta (pertanto nascosta) dal controllo modulo stesso.
Ovviamente la Funzione DATA dovrà essere modificata rispetto questo nuovo riferimento.
Anche l'elenco dei mesi proposti nella colonna K del Foglio di lavoro, si può considerare di spostarli in un'altra zona del Foglio Excel o su un Foglio di lavoro diverso.

Calendario mensile con scelta dell'anno e mese di riferimento: risultato finale

Download file con il calendario mensile con scelta dell'anno e mese di riferimento

Per fornire un ulteriore supporto alla spiegazione, o anche per modificare il calendario rispetto necessità personali, viene reso disponibile il file Excel utilizzato per la spiegazione del presente tutorial

Nel sottostante link, è possibile scaricare il file contenente il calendario nel quale sono presenti i controlli modulo, Formule, Funzioni e formattazione descritte negli esempi.
Download file calendario mensile con scelta dinamica dell'anno e mese di riferimento

Indice argomenti

[Valter Borsato: Gennaio - 2021]