In questa pagina pubblichiamo la metodologia per creare un calendario automatico in Excel per ad esempio:

• Gestione di attività
• Gestione di turni di lavoro
• Gestione di piani ferie

il file Excel si presenta con due fogli: il primo è un foglio che rappresenta il calendario stesso e il secondo invece contiene i dati di supporto: l’elenco delle festività e i nomi dei giorni della settimana.

Il file è completamente parametrico: è sufficiente inserire il primo giorno da cui si vuol far partire il calendario nella cella blu e aggiornare le date delle festività. Esso presenterà tutte le date in sequenza, per ogni data indicherà il nome del giorno della settimana e indicherà con i colori grigio i sabati e le domeniche e in arancione i giorni festivi:

Il trucco per realizzarlo è semplicissimo: a parte qualche piccola formula per poter recuperare dal foglio di appoggio i dati necessari sul calendario stesso, si è utilizzato per tutta l’area del calendario una formattazione condizionale fatta in questo modo:

Dove con tre semplici formule si va ad indentificare, per ogni cella del calendario, se essa è in corrispondenza di una colonna che corrisponde a un sabato, a una domenica o a una festività. In questo caso, se la festività cade di sabato o domenica, essa avrà il colore del sabato o della domenica perché Excel verifica per prima cosa se si tratta di un sabato o di una domenica. Se volete che le festività che cadano di sabato o di domenica siano comunque colorate come festività dovrete cambiare l’ordine dei controlli all’interno della formattazione condizionale.

Calendario_automatico

Se volete poi acquistare qualche libro che vi aiuta sulle formattazioni condizionali qui qualche consiglio:

La funzione CERCA.VERT è molto utile per incrociare tra di loro piccoli database. Se si hanno database con qualche migliaio di linee da incrociare non usate CERCA.VERT potreste impiegarci molto tempo o forse non riuscirci proprio!

Tale funzione è però un poco ostica. Prima di tutto, se il database su cui si ricerca non è ordinato in senso alfabetico crescente la funzione, se non impostata correttamente può dare dei risultati errati. E’ quindi praticamente sempre necessario inserire il flag che indica la necessità di ricercare la corripondenza esatta. Per farla breve, la tipica sintassi da utilizzare è:

=CERCA.VERT(E13; A2:B100;2;FALSO)

dove:
– E3 indica cosa cercare
– A2:B100 indica dove cercare
– 2 indica quale colonna del range A2:B100 considerare (qui la colonna B)
– FALSO indica che è necessario ricercare la corrispondenza esatta tra ciò che è contenuto in E3 e ciò che si trova nella prima colonna del range A2:B100.
(NOTA: FALSO può essere sostituito con 0)

Il problema nasce subito quando si cerca di elaborare poi i risultati del CERCA.VERT.

Se infatti la ricerca è infruttuosa il nostro CERCA.VERT restituisce, invece che un numero o il dato corretto, un bellissimo #N/D (in ingelse #N/A). Si tratta di un messaggio di errore di Excel. In sostanza ci comunica che la ricerca è stata senza risultato. Il punto è che è ora impossibile applicare formule che coinvolgono la cella che contiene il nostro #N/D.
Qualsisasi formula infatti che interessa la cella che contiene la cella che contiene l’#N/D infatti si trasforma immediatamente in #N/D anch’essa.

La soluzione più banale è quella di cancellare la formula quando essa dà errore. Questo comporta però che il modello Excel realizzato dipende nelle sue formule da come sono fatti i dati (qui la presenza della formula della cella dipende dalla presenza o no della variabile ricercata nei dati). Questo approccio è dannosissimo e fonte di errori. E’ meglio modificare la formula in questo modo:

=SE(VAL.NON.DISP(CERCA.VERT(E3;A2:B100;2;FALSO));0;CERCA.VERT(E3;A2:B100;2;F
ALSO))

Questa formula, per prima cosa verifica se il CERCA.VERT genera l’errore, in questo caso, invece di scrivere nella cella il fastidioso #N/A scrive 0 (o qualsiasi altra cosa volete fargli scrivere!), altrimenti restituisce il valore corretto del CERCA.VERT.

Questa soluzione permette:

1. di evitare di riempire il foglio di lavoro con fastidiosi messaggi di errore di Excel,
2. di svincolare il complesso delle formule che costituiscono il modello Excel dalla contingenza dei dati
3. di consentire una forte riduzione del tempo necessario per la manutenzione del modello stesso ogni volta che vengono aggiornati i dati
4. di ridurre la probabilità di errore

Può capitare a volte di avere a che fare con un database (un insieme di righe) che sono state lavorate da qualcuno e che per indicare i vari stati delle righe, questi ha pensato bene di usare i colori e evidenziare le righe on giallo, rosso etc.

Purtroppo però questa tecnica non permette di effettuare ulteriori elaborazioni sul database: ad esempio è impossibile contare le righe colorate di rosso, evidenziarle o altro.

In questa pagina vi presentiamo una semplicissima macro (è fatta da 2 righe!) che permette di “tradurre” il colore della riga in un numero (il suo codice colore) e di esporlo nella cella. Questa funzione vi è utile ad esempio per introdurre un’ulteriore colonna nel database dove per ogni riga si evidenzia il suo colore. A questo punto, si può utilizzare l’autofiltro, il CONTA.SE, le tabelle pivot e altro ancora basandosi non più sul colore ma su questo codice.

La funzione è la seguente:

Function colorecella(a As Range) As Integer

Application.Volatile

colorecella = a.Interior.ColorIndex

End Function

La prima istruzione (Application.Volatile) dice semplicemente a Excel di ricalcolare la funzione ogni volta che viene eseguito il ricalcolo del foglio (ad esempio con la pressione del tasto F9 o quando si inserisce o si cambia un valore in una cella.

Excel infatti, per risparmiare risorse di calcolo, non ricalcola le formule del foglio di lavoro ogni volta che eseguite un comando sul foglio: quando cambiate la formattazione, inserite un bordo, cambiate un colore (!!) Excel NON ricalcola le formule.

Quindi la nostra funzione colorecella() non verrà aggiornata appena cambierete il colore: quando cambiate un colore dovrete poi o premere F9 per forzare il calcolo di tutto il foglio e quindi della nostra funzione, oppure cambiare un valore qualsiasi in una cella, forzando ancora una volta Excel a ricalcolare il foglio di lavoro.

Cliccare sul link sottostante per scaricare il file di esempio:

colorecella