Come creare un calendario che cambia dinamicamente in Excel

In questo articolo, ti mostreremo come creare un calendario che cambia dinamicamente in Excel.

Scarica Cartella di lavoro

Popola variabili

Il primo passo è creare le variabili da utilizzare nelle formule. Queste variabili devono fornire le informazioni del giorno presente in modo che il calendario del calendario possa aggiornarsi automaticamente. Ecco l’elenco delle variabili:

• data
• Il numero dei presenti il giorno del mese
• Il numero del mese attuale
• esercizio
• La data del primo giorno del mese attuale
• Il numero del giorno della settimana
• Il numero di questa settimana dell’anno

useremo il OGGI GIORNO, MESE, ANNO, DATA, giorno della settimana, e NUM.SETTIMANA funzioni. In breve, mentre la funzione OGGI restituisce la data del giorno presente, GIORNO, MESE, ANNO, GIORNO della SETTIMANA e WEEKNUM funzioni analizzare i rispettivi valori di data. La funzione DATA restituisce il valore data dell’anno, del mese e del giorno specificati.

Nella schermata seguente, puoi vedere i nomi che abbiamo dato e le formule che sono state generate il 20 novembre 2020.

Abbiamo nominato ogni variabile per semplificare il nostro lavoro durante la creazione di formule. Il prossimo passo per creare un calendario in Excel è la creazione di un contorno per l’interfaccia del calendario.

Contorno per il calendario in Excel

Prima di creare le formule effettive che generano i giorni del mese, è necessario innanzitutto inserire un contorno per aiutare a creare formule e fornire anche un riferimento visivo.

I giorni saranno posizionati su una tabella di 7 colonne e 6 righe. Dobbiamo aumentare questi numeri di 2 anche per i titoli e le celle di supporto. Mentre i titoli delle colonne e delle righe includono i giorni della settimana per le colonne e i numeri della settimana per righe, le celle di supporto saranno numeri consecutivi a partire da 1.

Il calendario potrebbe apparire come di seguito.

Usa bordi o colori di sfondo come desideri. Il punto importante è usare un numero di settimana dinamico nel titolo. Fare riferimento alla funzione WEEKNUM per la prima settimana e aggiungere 1 per ogni settimana consecutiva.

continuiamo la creazione di un calendario in Excel inserendo i numeri dei giorni.

Generazione di giorni

Se stai usando un layout simile e non vuoi preoccuparti dei dettagli della formula, sentiti libero di copiare la formula qui sotto. Quindi incollalo nella cella in alto a sinistra nell’area del giorno del calendario e popola le celle rimanenti. Le celle G2 ed E4 fanno riferimento alle prime celle delle colonne helper. Pertanto, potrebbe essere necessario regolare questi riferimenti, a meno che la prima cella non sia G4.

Ora, diamo un’occhiata più da vicino alla formula. Innanzitutto, la formula utilizza la funzione DATA, che restituisce una data in base all’anno, al mese e al giorno specificati. I primi 2 argomenti sono forniti dalle variabili YearNumber e MonthNumber che sono già state calcolate.

La parte giorno della formula mira a generare numeri giorno su una base 7, poiché ci sono 7 giorni in una settimana. 7 * (4 E4-1) parte della formula controlla il numero della settimana nella colonna helper E e genera una base, che inizia con 0 e aumenta di 7 ogni volta.

Siamo in grado di aumentare o diminuire questo numero tanto quanto la differenza tra il giorno della settimana del primo giorno del mese, giorno della settimana numero. In altre parole, stiamo ottenendo il primo giorno del mese attuale come punto di ancoraggio. Ad esempio, è il 7 ° giorno della settimana (dal lunedì alla domenica) per il 1 ° novembre 2020.

Se la settimana del nostro calendario presuppone che il lunedì sia il primo giorno della settimana, il lunedì dovrebbe essere 6 giorni prima (=1-7) del primo giorno del mese. Quindi, otteniamo numeri come sotto.

Aggiungere questi valori da 1 a spostare i numeri. Quindi, la domenica può diventare il primo giorno come nell’esempio seguente.

Come risultato, abbiamo 2 array a 2 dimensioni. Per creare una tabella, dobbiamo aggiungere entrambi gli array insieme.

Questi numeri rappresentano il giorno del mese presente. Ci sono numeri inferiori a 1 o superiori a 31, che ovviamente non possono essere giorni del mese.

La funzione DATA gestisce questi tipi di numeri generando una data nel mese precedente o nel successivo. Ad esempio, -2 diventa due giorni prima del primo giorno del mese specificato.

Infine, se si utilizzano questi numeri nella funzione DATA, è possibile ottenere le date effettive del mese corrente.

Formattazione

Numeri giorno

Ora, cerchiamo di popolare i giorni. È possibile modificare la formattazione dei valori senza modificare il valore effettivo. Questa funzione è chiamata Formattazione del numero. Segui i passaggi per regolare la formattazione del numero per mostrare solo i giorni.

1. Selezionare una qualsiasi delle date nella zona giorno
2. Premere Ctrl + 1 per aprire la finestra di dialogo formatta Celle
3. Attivare la scheda Numero se non è già aperta
4. Selezionare Personalizzata nella Categoria list
5. Digitare la lettera d nella casella Tipo
6. fare Clic su OK per salvare
   

Ora, il calendario layout è stato fatto.

Se si desidera rimuovere il padding per le date a cifra singola, è possibile utilizzare dd anziché single d. Per saperne di più sulla formattazione dei numeri: Formattazione dei numeri in Excel – Tutto ciò che devi sapere

Evidenziando il giorno presente

Infine, useremo la formattazione condizionale per impostare colori diversi per i giorni che non sono nel mese attuale ed evidenziare il giorno presente.

Per aggiungere queste opzioni di formattazione condizionale;

1. Selezionare l’intervallo di giorni
2. fare Clic su Formattazione Condizionale icona nella scheda Home della barra Multifunzione
3. Selezionare ” Nuova Regola
   
4. Selezionare Utilizza una formula per determinare le celle da formattare
5. Inserire una formula per definire la regola per mettere in evidenza il giorno corrente
   =G4=OGGI()
   G4: Cella in alto a sinistra della gamma include giorni. Usa un riferimento relativo (non usare $).
6. Fare clic sul pulsante Formato nella finestra di anteprima della finestra di dialogo Formato celle
   
7. Nella finestra di dialogo, selezionare le opzioni di formattazione che si desidera vedere nella cella di oggi
   Abbiamo usato uno sfondo arancione in questo esempio.
8. fare Clic su OK per applicare
9. La finestra di Anteprima mostrerà le tue preferenze
10. fare Clic su OK per applicare la formattazione condizionale
    

Questo è come appare:

La formula restituisce un valore Booleano. Se il valore della cella (G4) è uguale al risultato della funzione OGGI, che restituisce il giorno presente in un formato di data, la formula restituisce TRUE. Altrimenti, FALSO. Se il risultato è VERO, Excel applica la formattazione nella cella.

Ci sono 2 punti importanti qui:

1. È necessario utilizzare un riferimento relativo, ad esempio G4, a meno che non si desideri che Excel popoli il riferimento lungo l’intervallo. Considera come cambiano le formule quando le copi altrove. Excel applica lo stesso per le formule di formattazione condizionale pure. Per ulteriori informazioni, vedere: Come creare un riferimento assoluto di Excel e un riferimento relativo
2. Le celle del giorno devono restituire i valori della data effettiva. Questo è il motivo per cui stiamo usando la formattazione del numero per mostrare le porzioni del giorno.