Oggi parliamo della formattazione condizionale di Excel e di come possiamo usarla per dare alle nostre tabelle un aspetto professionale, per comunicare meglio i dati in esse contenuti, per realizzare visualizzazioni dinamiche, ma anche per eseguire controlli e individuare valori anomali.
Cos’è la formattazione condizionale
La formattazione condizionale è uno strumento comodo ed efficiente, con cui possiamo impostare la formattazione delle nostre tabelle in presenza di specifiche condizioni. Cioè lo strumento presenza la formattazione o formattazioni alternative, se il valore della cella rispetta determinate condizioni secondo regole preimpostate, o se una formula restituisce valore vero.
In realtà questo ti permette di evidenziare:
- i contenuti delle celle in modo da comunicare meglio i valori contenuti,
- le celle anche con oggetti grafici e in modo professionale
- solo celle che contengono valori precisi (maggiori di, uguali a , ecc),
- celle che contengono valori secondo regole non intuitive (valori duplicati, primi 10, ecc),
- celle secondo i valori di altre celle,
- secondo i risultati di formul
Quindi la formattazione condizionale permette di assegnare formattazioni preimpostate o personalizzate, secondo regole o condizioni preimpostate o personalizzabili, allo scopo di evidenziare tutte o alcune delle celle in cui viene installato.
Dove si trova la formattazione condizionale
I comandi della formattazione condizionale si trova nella scheda “Home” della barra multifunzione, ma sono accessibili in modo più efficiente, direttamente sulla tabella, attraverso lo strumento di “Analisi rapida”.
L’analisi rapida è una piattaforma che porta numerosi strumenti a portata di clic direttamente nella pagina, anzi sulla tabella, tra cui anche la formattazione condizionale. È richiamabile tramite l’icona che compare in automatico alla selezione di intervalli di celle, o premendo Ctrl+Q, o se abbiamo già selezionato tabella o intervallo, premendo Ctrl.
Per approfondire l’analisi rapida ti invito a guardare questo tutorial.
Invece il classico menu della scheda home presenza una serie di menu di condizioni preimpostate applicabili con qualche clic, oppure da la possibilità di personalizzare finemente sia la formattazione che le condizioni.
Come funziona la formattazione condizionale
La formattazione condizionale si applica a una o più celle ed è composta da due elementi: la regola che definisce le condizioni e la formattazione da applicare quando le condizioni compaiono.
Lo strumento offre una serie di formattazioni preimpostate in cinque sottomenu, oltre ai comandi per accedere alle finestre classiche per l’impostazione “manuale”.
Il primo menu della formattazione “Regole evidenziazione celle” propone regole preimpostate classiche, come “Maggiore di”, “Minore di”, “Compreso tra”, “Uguale a”, eccetera che impostano regole di attivazione della formattazione se il valore delle celle è appunto maggiore di un valore, uguale a un valore, eccetera. Regola interessante è quella che evidenzia i duplicati o in alternativa i valori univoci.
Il secondo menu invece è quello “Regole primi/ultimi” che propone regole di applicazione della formattazione meno convenzionali, relativi a interi campi di valori numerici. Per esempio permette di evidenziare i “primi 10 elementi” del campo, o gli ultimi 10, come i valori del “primo 10%” come dell’ultimo. Naturalmente il numero o la percentuale possono essere impostati a piacere.
Questo permette di evidenziare i valori di un campo di una pivot o di una tabella di formule la cui somma restituisce il 20% del totale. Ma tra le regole troviamo anche “Sopra la media” e “Sotto la media” che permette di evidenziare le celle con i valori sopra o sotto la media, o varianti (per esempio sopra o sotto di 2 deviazioni standard). Regole utili per individuare porzioni relativamente precise della distribuzione.
Barre e scale dei colori
Il terzo menu è quello delle barre dei dati ed è forse il più utilizzato. Permette l’installazione nelle celle di barre colorate orizzontali la cui dimensione rappresenta il valore della cella. Le barre sono un ottimo modo per mostrare a colpo d’occhio i valori maggiori o minori di una serie numerica, senza dover spendere tempo a leggere i valori. Viene usato in tutte i campi numerici importanti delle tabelle di elaborazione.
Invece il quarto menu propone le scale dei colori, cioè offre una serie di regole preimpostate che colorano le celle di campi e serie numeriche di colori e toni diversi a seconda dei valori relativi. Quindi abbiamo una scala di verde o rosso, come scale bicolori, verde–rosso, verde–giallo, verde–blu, e anche tricolori. Naturalmente questo permette di evidenziare i valori maggiori o minori di colori diversi e contrastanti in modo da identificarli velocemente.
Il quinto menu invece permette di installare set di icone configurabili che cambiano colore o orientamento a seconda dei valori. Tra le icone troviamo frecce, semafori, scale, bandiere e altro ancora.
Personalmente le trovo poco interessanti, ma possono essere utili in diversi modi, per esempio per mostrare a colpo d’occhio un avvenimento o uno stato, come il ritardo di una commessa in lavorazione, o il superamento di un target di vendite, i ritardi nelle consegne, eccetera.
Impostare le regole
Le regole possono essere impostate direttamente, senza usare quelle preimpostate, usando il comando “nuova regola” presente nel menu. Si aprirà la finestra omonima che offre un’ampia scelta di opzioni e impostazioni con cui è possibile realizzare centinaia di regole diverse.
Buona parte delle regole è simile a quelle preimpostate descritte in precedenza, a cui ti rimando, a queste però si aggiunge una regola basata sulle formule. Cioè possiamo inserire come regola una formula e quando questa restituisce un valore “vero” allora la formattazione impostata verrà applicata.
Questa possibilità offre una gamma di possibilità impressionante. Approfondiamo l’argomento e facciamo qualche esempio.
Formattazione condizionale con le formule
L’utilizzo di una formula permette di cambiare prospettiva alla formattazione condizionale. Mentre le regole preimpostate sono incentrate sulla cella in cui installiamo la formattazione, o sui valori delle celle in cui abbiamo installato la stessa regola, una formula non solo permette di utilizzare formule e funzioni per controllare la visualizzazione della formattazione, ma permette di puntare ad altri riferimenti, celle e valori.
Possiamo collegare la formattazione al valore di un’altra cella, dello stesso record o posizionata altrove. Questo permette per esempio di evidenziare l’intero record basandosi sul valore di una cella, utile in molti casi, come per esempio nella realizzazione di calendari dinamici.
Ma questo permette anche di controllare la formattazione di celle, record, interi campi, intervalli o perfino tabelle, attraverso i valori impostati in una o più celle esterne. Offrendo la possibilità di installare controlli su pagina che permettono di controllare la formattazione di tabelle e pagine, anche di impostare formattazioni alternative.
Con le formule possiamo fare anche molte altre cose, come installare semafori in tabelle controllati da uno o più valori, utili per esempio in strumenti di controlli della produzione o logistica, o come installare marcatori esterni che si attivano in presenza di valori indesiderati o specifici, come errori o valori troppo grandi.
Usare la formattazione condizionale per i controlli
Quindi la formattazione condizionale può essere usata anche per eseguire controlli sui dati, o meglio per evidenziare valori specifici o categorie di valori, come errori, valori duplicati, valori zero, o nulli, eccetera. Questo ci aiuta a individuarli e a intervenire, ad agire per individuare ed eventualmente risolvere le cause delle anomalie.
In realtà la formattazione condizionale non è il modo più efficiente per eseguire controlli, in quanto gestisce solo l’individuazione dei valori anomali e lascia a noi il resto, cioè il filtraggio ed estrazione dei valori e le attività di analisi e gestione.
Nonostante questo la formattazione condizionale è talmente comoda, semplice e veloce da utilizzare, che è spesso usata anche per i controlli, soprattutto su grandi tabelle con migliaia di record per individuare facilmente valori specifici. Una volta applicata a uno o più campi della tabella, è semplice filtrarla per colore, per poi eventualmente estrarre i record e gestirli.
I controlli con la formattazione condizionale mirano a valori precisi, o intervalli precisi, come per esempio valori nulli, stringhe specifiche, o valori maggiori o minori a due o tre volte la deviazione standard della distribuzione della variabile, oppure a valori duplicati, di formato diverso, o di categorie predefinite. E via dicendo.
Come usare la formattazione condizionale in pratica
In generale la formattazione condizionale si usa in questo modo:
- si definisce il bisogno e l’obiettivo,
- si applica la formattazione preimpostata che corrisponde, o più si avvicina all’obiettivo,
- eventualmente si modifica per ottenere il risultato voluto,
- si testa ed eventualmente si revisiona.
Questo soddisfa oltre il 90% degli usi di evidenziazione e il 60–80% degli altri usi (controllo, visualizzazione, ecc). Quando l’obiettivo è complesso, prevede l’uso di formule, riguarda valori in celle esterne, eccetera, allora è necessario progettare con cura la formattazione e le regole che dobbiamo impostare.
Per esempio se in un campo numerico vogliamo individuare valori testuali, dobbiamo impostare una formula come regola che lo permette. Una formula che restituisce “vero”, quindi una comparazione o una formula con una funzione che restituisce di base vero o falso.
O per esempio se vogliamo evidenziare le date di un calendario che corrispondono alla domenica possiamo usare formule come “=Giorno.settimana(RIF, 2)=7”, dove RIF è la cella con la data, due è il conteggio dei giorni della settimana e la comparazione “=7” serve a restituire Vero o Falso.
Oppure per esempio puoi usare la funzione Val.Testo(), che restituisce vero se il valore indicato è un testo, oppure la funzione Val.Vuoto() che restituisce vero se la cella indicata non ha valore.
Una volta progettata la formattazione, puoi usare una pagina di excel per scrivere l’obiettivo, l’elenco delle regole e impostare le formule per verificare che funzionino come previsto. Poi devi impostare le regole della formattazione nello strumento e scegliere la formattazione, cioè colore di fondo, colore del testo, grandezza e stile del testo, eccetera.
Scegli la formattazione e poi modifica le regole
Anche se può apparire controintuitivo, il modo più efficiente di impostare anche le formattazioni condizionali complesse è quello di usare quelle preimpostate. Perché? Per scegliere una formattazione preimpostata della cella e non doverla costruire manualmente dopo aver impostato la regola.
Quindi scegli la formattazione della cella che preferisci da quelle preimpostate che più si avvicinano al tuo obiettivo, crea la regola e poi torna a modificarla con il comando “gestisci regole”. Questo apre la finestra con le regole applicate alla cella o alle celle selezionate che possiamo modificare anche in modo radicale con il comando “modifica regola”.
Dalla finestra omonima possiamo modificare la regola mantenendo la formattazione, per esempio aggiungendo una formula preimpostata dalla pagina. Dove necessario possiamo anche personalizzare colori e impostazioni della formattazione che vogliamo compaia.
Formattazioni condizionali multiple
Un aspetto che è importante chiarire è che possiamo impostare più formattazioni condizionali sulla stessa cella o intervallo, che controllano l’apparire anche di formattazioni diverse.
Come?
Possiamo aggiungere più regole diverse che controllano la stessa formattazione (colori, stili, ecc), piuttosto che formattazioni diverse o alternative. Possiamo farlo usando regole basate su formule che contengono più condizioni, che controllano più condizioni per restituire un valore vero.
Perché usare più regole?
Può essere molto utile in diverse situazioni, per esempio allo scopo di gestire:
- evidenziazioni e controlli insieme,
- controlli multipli esclusivi o alternativi,
- visualizzazioni dinamiche,
- visualizzazioni dinamiche e controlli.
Un esempio classico è impostare nel campo Fatturato della nostra tabella dei costi, una formattazione con le barre e una per evidenziare valori fuori trend.
Un altro è impostare nel campo “Quantità” della base dati controlli multipli su: errori, valori nulli, formati diversi, valori maggiori della media di 3 volte la deviazione standard, eccetera.
Un altro esempio è l’uso della formattazione condizionale in un gantt per evidenziare le celle del calendario con formattazioni diverse a seconda di programmazione, esecuzione, ritardi, sospensioni, eccetera.
Formattazioni condizionali multiple con le formule
Diverso invece è l’uso delle formule per gestire condizioni multiple. Si realizza di solito attraerso le funzioni condizionali e associate, come Se(), E(), O(), eccetera per impostare formule a condizioni multiple, che poi inseriremo come regola.
Un esempio elementare è “=O(Val.errore(RIF); RIF=0)”. La formula risulta vera se il valore della cella RIF contiene un errore o contiene un valore 0. In modo simile possiamo realizzare formule anche complesse che verificano molte condizioni, ma come avrai già intuito questo modo di impostare condizioni multiple ha un limite notevole. La regola è una e quindi la formattazione correlata è unica.
Quindi questo metodo viene usato raramente, di solito quando dobbiamo impostare condizioni multiple simili che non dobbiamo distinguere e che porteranno a evidenziare in un unico modo la cella. In realtà questo metodo viene usato raramente anche perché richiede una buona conoscenza di formule e funzioni.
Di solito si utilizzano formattazioni condizionali distinte e multiple con cui possiamo realizzare soluzioni evolute nei nostri report e nelle dashboard, come visualizzazioni dinamiche alternative e complementari in relazioni a valori interni alle celle o a valori di altri campi e tabella. O perfino collegati a controlli esterni creati con formule e funzioni, nomi e convalida, controlli e automazione, eccetera.
Conclusioni
Abbiamo visto che la formattazione condizionale è uno strumento di Excel molto utile che ci permette di evidenziare campi, celle o tabelle in modo dinamico ed automatico. O di eseguire controlli automatici a responso visivo con cui possiamo individuare o sottolineare valori specifici o anomali. O creare visualizzazioni dinamiche che cambiano a seconda dei valori delle cello o di celle esterne.
Quindi la formattazione condizionale diventa uno strumento fondamentale in molte situazioni dove l’aspetto di celle e tabelle è importante per comunicare meglio le informazioni o la professionalità dell’autore o dell’azienda.
Come per esempio nei report e dashboard che contengono tabelle, o negli strumenti di gestione che visualizzano stati o eseguono controlli avanzati, per comunicare stati, traguardi e allarmi. O per creare visualizzazioni dinamiche in strumenti di vario genere.
Chi usa Excel deve conoscere questo strumento, una soluzione efficiente e comoda in molte situazioni, ma chi usa Excel in ambito aziendale o professionale deve saperlo padroneggiare per ottenere le soluzioni migliori nel modo più comodo e veloce.
PS: Se il post ti è piaciuto, condividi questa pagina con gli amici e sui social.
Puoi farlo attraverso i pulsanti qui di seguito. Grazie
