FORMATTAZIONE CONDIZIONALE:
COME EVIDENZIARE CON CRITERIO
La formattazione standard è stata ampiamente trattata nell’articolo Formattazione delle celle. Tuttavia, oltre alla formattazione manuale, che richiede l’intervento diretto dell’utente sull’estetica delle celle, Excel offre la possibilità di applicarla automaticamente, sulla base di criteri specifici. Questa funzionalità consente di trasformare i dati in indicatori visivi immediati, evidenziando quelli che rispettano una determinata condizione, come colorare di rosso importi negativi. I comandi si trovano dentro Home → Gruppo Stili → Formattazione condizionale.
La formattazione condizionale si rivela particolarmente utile quando si analizzano grandi insiemi di dati, perché consente di cogliere a colpo d’occhio informazioni che altrimenti richiederebbero tempo per essere ottenute. Una volta creata, la regola di formattazione condizionale può essere copiata con gli stessi metodi illustrati in Copiare e incollare la formattazione.
Regole evidenziazione celle
La prima voce presente nel menu di Formattazione condizionale riguarda le Regole evidenziazione celle (1):
- Maggiori di, specificando un numero
- Minore di, specificando un numero
- Tra, specificando due numeri e in qualsiasi ordine
- Uguale a, specificando un numero
- Testo contenente, specificando un testo
- Data corrispondente a, selezionando:
- Ieri
- Oggi
- Domani
- Ultimi 7 giorni
- Settimana scorsa
- Questa settimana
- Settimana prossima
- Mese scorso
- Questo mese
- Mese prossimo
I numeri e i testi specificati nelle regole possono essere inseriti manualmente o selezionati dai fogli oppure derivare dall’uso di formule. Inoltre, per ciascuna di queste regole, fatta eccezione di
Altre regole, si apre la
Finestra di dialogo (2) con due menu a discesa. Nel primo vengono indicati i valori, sopra menzionati, relativi alla regola stessa, nel secondo la formattazione da applicare in caso la condizione sia verificata:
- Riempimento rosso chiaro con testo rosso scuro
- Riempimento giallo con testo giallo scuro
- Riempimento verde con testo verde scuro
- Riempimento rosso chiaro
- Testo rosso
- Bordo rosso
- Formato personalizzato, conduce all’apertura della finestra Formato celle (3) contenente le seguenti schede e relative anteprime – mancano Allineamento e Protezione:
- Numero, per selezionare il formato tra numero, data, valuta e testo
- Carattere, per selezionarne tipo, stile, dimensione, sottolineatura, colore ed effetto
- Bordo, per selezionare tipo e colore della linea, se predefinito o personalizzato
- Riempimento, per selezionare colore dello sfondo e stile e colore del motivo
Regole primi e ultimi valori
La seconda voce nel menu di Formattazione condizionale riguarda le regole di formattazione per i soli dati numerici, più specificatamente le Regole primi/ultimi valori (1):
- Primi 10 elementi, con 10 come numero predefinito ma modificabile da 1 senza limiti
- Primo 10%, con 10 come numero predefinito ma modificabile da 1 a 100
- Ultimi 10 elementi, con 10 come numero predefinito ma modificabile da 1 senza limiti
- Ultimo 10%, con 10 come numero predefinito ma modificabile da 1 a 100
- Sopra la media, senza indicare alcun numero, Excel esegue la media dei numeri delle celle selezionate
- Sotto la media, senza indicare alcun numero, Excel esegue la media dei numeri delle celle selezionate
- Altre regole, apre la finestra Nuova regola di formattazione → Formatta solo i primi o gli ultimi valori (3) con tutte le opzioni appena illustrate
Anche in questo caso, per ciascuna regola, fatta eccezione di Altre regole, si apre la corrispondente Finestra di dialogo (2). I menu a discesa sono ancora due. Nel primo si indicano i valori della regola, ad eccezione di Sopra la media e Sotto la media, per i quali Excel agisce in autonomia. Nel secondo menu compaiono nuovamente le formattazioni predefinite e la possibilità di personalizzare mediante la finestra Formato celle (4).
Barre dei dati
Nel menu Formattazione condizionale sono presenti anche regole visive per dati numerici, come Barra dei dati (1). Si possono scegliere colori a Riempimento sfumato o Tinta unita. L’opzione Altre regole conduce alla finestra Nuova regola di formattazione → Formatta tutte le celle in base ai relativi valori (2) – si veda più avanti Creare una nuova regola. Nella sezione omonima si stabilisce:
Nella sezione Aspetto barra si trovano:
- Riempimento:
- Colore riempimento
- Bordo:
- Colore bordo
- Valore negativo e asse, apre la finestra omonima (3) per selezionare colore e bordi delle barre negative, colore dell’asse e posizione dell’asse tra
- Automatico, regola la visualizzazione in base ai valori negativi
- Punto intermedio della cella
- Nessuno, mostra barre negative nella stessa direzione di quelle positive
- Direzione barra, dipende dalla lingua – l’arabo e l’ebraico si leggono da destra verso sinistra
- Anteprima, per visualizzare il risultato
Scale di colori
Anche le Scale di colori (1) del menu di Formattazione condizionale riguardano le regole visive applicabili ai soli dati numerici. Con questa modalità vengono proposte alcune visualizzazioni predefinite che utilizzano sfumature a 2 o 3 colori. L’opzione Altre regole apre la finestra Nuova regola di formattazione → Formatta tutte le celle in base ai relativi valori → Scala a 2 colori (2). Le opzioni disponibili sono praticamente le stesse di Barre dei dati:
- Stile formato:
- Scala a 2 colori, opzione predefinita
- Scala a 3 colori (3), al Minimo e Massimo si aggiunge il Punto intermedio
- Barra dei dati, si veda sopra
- Set di icone, si veda il prossimo paragrafo
- Tipo, specifica la tipologia del valore usata per i 2 o 3 colori:
- Valore minimo o valore massimo – non prevista per il Punto intermedio
- Numero, si veda sopra
- Percentuale, si veda sopra
- Formula, si veda sopra
- Percentile, si veda sopra
- Valore di Minimo, Massimo ed eventualmente Punto intermedio, può essere automatico, inserito manualmente o selezionato dal foglio – i dati inferiori al Minimo e quelli superiori al Massimo non hanno sfumature cromatiche
- Colore, indica il colore iniziale (Minimo), intermedio (Punto intermedio) e finale (Massimo) delle sfumature cromatiche
- Anteprima, visualizza il risultato
Set di icone
L’ultima regola visiva limitata ai soli dati numerici del menu di Formattazione condizionale riguarda il Set di icone (1). È possibile scegliere set composti dalle 3 alle 5 icone, suddivisi in:
- Direzionale
- Forme
- Indicatori
- Classificatori
L’opzione
Altre regole apre la finestra
Nuova regola di formattazione →
Formatta tutte le celle in base ai relativi valori (2). Nella sezione
Formatta tutte le celle in base ai relativi valori è possibile impostare:
- Stile formato:
- Scala a 2 colori, si veda sopra
- Scala a 3 colori, si veda sopra
- Barra dei dati, si veda sopra
- Set di icone, opzione predefinita
- Stile icona, il tipo di set composto dalle 2 alle 5 icone
- Inverti ordine icone, per associare alla prima icona i valori più alti e all’ultima quelli più bassi
- Mostra solo icona, per nascondere il numero nella cella
Inoltre, per ciascuna delle icone del set selezionato è possibile indicare:
- Icona, il singolo simbolo può essere modificato e scelto anche da un set diverso
- Quando il valore è, selezionando “maggiore di” o “maggiore o uguale a”
- Valore, inserito manualmente o selezionato dal foglio – di default sono 33/67 per 3 icone, 25/50/75 per 4 icone e 20/40/60/80 per 5 icone
- Tipo, per determinare il criterio con cui vengono calcolati i valori associati alle icone – per maggiori dettagli si veda Barre dei dati:
-
- Numero
- Percentuale
- Formula
- Percentile
Creare una nuova regola
Si può creare una nuova regola di formattazione condizionale tramite:
- Home → Gruppo Stili → Formattazione condizionale → Nuova regola (1)
- Home → Gruppo Stili → Formattazione condizionale → Gestisci regole → Nuova regola (2)
La finestra
Nuova regola di formattazione (3) offre le seguenti opzioni con anteprime:
- Formatta tutte le celle in base ai relativi valori, si vedano i precedenti paragrafi
- Formatta solo i primi o gli ultimi valori, si veda Regole primi e ultimi valori
- Formatta solo i valori superiori o inferiori alla media, si veda il prossimo paragrafo
- Formatta solo i valori univoci o duplicati (4), si veda Regole evidenziazione celle sebbene la finestra presenti un aspetto diverso
- Utilizza una formula per determinare le celle da formattare, si veda Personalizzare una regola con formule
In realtà, compare anche un’altra opzione che merita un approfondimento. L’opzione Formatta solo le celle che contengono (5), è disponibile anche tramite Home → Gruppo Stili → Formattazione condizionale → Regole evidenziazione celle → Altre regole. Queste regole sono state in parte illustrate in Regole evidenziazione celle. Di seguito quelle aggiuntive:
- Valore, specificando uno o due numeri o selezionandoli dal foglio:
- Non compreso tra, non importa l’ordine dei due numeri
- Diverso da
- Maggiore o uguale a
- Minore o uguale a
- Testo specifico, specificando un testo o selezionandolo dal foglio:
- Non contiene
- Inizia con
- Termina con
- Valore vuoto, celle senza contenuti o con formule che producono un risultato “vuoto” – ben diverso dallo zero e dagli errori
- Valore non vuoto, celle in cui è stato inserito un contenuto o con formule che producono risultati visibili
- Errori, celle che presentano errori derivanti da formule
- Nessun errore, celle che non contengono alcun errore, comprese quelle vuote
Regole statistiche
L’ultima opzione qui presentata tra le nuove regole da creare è Formatta solo i valori superiori o inferiori alla media (1). Questa formattazione è utile in ambito statistico per visualizzare immediatamente i valori che si trovano sopra e sotto la media. Inoltre è possibile formattare i valori che si allontanano dalla media raggiungendo determinate soglie, misurate come multipli della deviazione standard. Quindi Excel calcola autonomamente questi due indici statistici a partire dai dati numerici selezionati, ignorando le celle vuote e quelle contenenti testo. Le opzioni sono:
- Superiori, equivalente alla regola Sopra la media all’interno di Regole primi/ultimi valori
- Inferiori, equivalente alla regola Sotto la media all’interno di Regole primi/ultimi valori
- Uguali o superiori
- Uguali o inferiori
- Superiori di 1 deviazione standard
- Inferiori di 1 deviazione standard
- Superiori di 2 deviazione standard
- Inferiori di 2 deviazione standard
- Superiori di 3 deviazione standard, per identificare i dati anomali nella coda destra
- Inferiori di 3 deviazione standard, per identificare i dati anomali nella coda sinistra
Anche per questa regola è possibile visualizzare un’anteprima del formato da applicare alle celle, attraverso le impostazioni di formato, carattere, bordo e riempimento.
Personalizzare una regola con formule
Oltre alle regole predefinite, Excel consente di creare formattazioni condizionali personalizzate, basate prevalentemente su formule di logica che restituiscono VERO o FALSO. Se la condizione è vera, viene applicata la formattazione. È dunque fondamentale comprendere come i riferimenti di cella si adattano quando la formula si estende a più celle. Vediamo alcuni esempi: un’azienda registra le vendite inserendo nella colonna A i codici di 15 articoli e nella colonna B i pezzi venduti.
Esempio 1-2
Vogliamo evidenziare di verde i valori della colonna B superiori a 100 (1) oppure a una soglia indicata in D2 (2):
- Selezioniamo l’intervallo B2:B16 (l’intestazione B1 è indifferente)
- Home → Formattazione condizionale → Nuova regola → Usa una formula per determinare le celle da formattare
- Inseriamo la formula:
- = B2 > 100 (esempio 1)
- = B2 > \$D\$2 (esempio 2)
- La formula va pensata sulla prima cella (Excel la replica sulle altre)
- Il dollaro ($) blocca il riferimento D2, così ciascun valore di B viene confrontato con quella soglia
- Impostiamo il riempimento verde
- Se cambiamo la soglia in D2, la formattazione si aggiorna automaticamente
Esempio 3-4
Vogliamo evidenziare di rosso i codici (3), o le righe intere (4), corrispondenti alle vendite comprese fra 20 e 60:
- Selezioniamo l’intervallo A2:A16, esempio 3, o l’intera tabella A2:B16, esempio 4
- Home → Formattazione condizionale → Nuova regola → Usa una formula per determinare le celle da formattare (5)
- Inseriamo la formula:
- = E(B2 >= 20; B2 <= 60) (esempio 3)
- = E(\$B2 >= 20; \$B2 <= 60) (esempio 4)
- La funzione E restituisce VERO se entrambe le condizioni sono vere
- La formattazione della colonna A dipende dalla colonna B, che nell’esempio 4 viene bloccata ($) per estendere la regola all’intera riga
- Impostiamo il riempimento rosso
- La regola diventa dinamica inserendo soglie modificabili in due celle come nell’esempio 2
Gestire e cancellare regole
Le regole create si gestiscono da Home → Formattazione condizionale → Gestisci regole, nella finestra Gestione regole formattazione condizionale (1). All’interno vengono elencate le regole in ordine di creazione – la più recente in alto – con indicazioni su:
- Regola, criterio che la definisce
- Formato, anteprima della formattazione applicata
- Si applica a, specifica le celle interessate
- Interrompi se vera, blocca le regole successive quando la condizione è verificata
La priorità delle regole segue l’ordine dall’alto verso il basso: in presenza di più regole sulle stesse celle, prevale quella più in alto nell’elenco. Selezionando una regola alla volta, sono disponibili le seguenti opzioni:
- Visualizza regole di formattazione per, mostra le regole della selezione, del foglio o di altri fogli
- Nuova regola, aggiunge una regola
- Modifica regola, oppure doppio clic, apre la finestra di Modifica regola di formattazione (2), identica a quella di creazione
- Elimina regola, cancella la regola
- Sposta su (CTRL + ↑) e Sposta giù (CTRL + ↓), modifica la priorità della regola
Per eliminare una regola si usa
Home → Formattazione condizionale → Cancella regole (3):
- Cancella regole dal foglio intero, rimuove tutte le regole
- Cancella regole dalle celle selezionate, rimuove le regole solo dalla selezione
- Cancella regole da questa tabella o tabella pivot, rimuove le regole dalla tabella strutturata selezionata
Inoltre, una regola può essere eliminata anche tramite Home → [icona gomma] Cancella → Cancella formati oppure Cancella tutto che rimuove anche i contenuti. Infine, è possibile individuare le celle con formattazione condizionale da Home → Gruppo Modifica → [icona lente] Trova e seleziona, scegliendo tra:
- Formattazione condizionale, identifica tutte le celle del foglio
- Vai a formato speciale → Formati condizionali:
- Tutte le celle con regole, identifica tutte le celle del foglio
- Solo le celle con regole identiche, identifica solo quelle con la stessa regola della cella selezionata
Formattazione condizionale rapida
Oltre alle modalità finora viste, Excel consente di creare la formattazione condizionale tramite il comando Analisi rapida accessibile nei seguenti modi:
- Pulsante contestuale rapido (1), appare accanto al quadratino di riempimento delle celle selezionate – si veda Riempimento e trascinamento
- Clic con il tasto destro sull’area selezionata, Menu contestuale (2) → Analisi rapida
- (CTRL + Q)
Nella scheda Formattazione i comandi cambiano a seconda che le celle contengano dati numerici, testuali o date. La formattazione predefinita e non modificabile è il riempimento rosso chiaro con testo rosso scuro. Quando le celle sono numeriche, le regole rapide selezionabili da Analisi rapida →
Formattazione (3)
sono
:
- Barre dei dati, blu a tinta unita
- Scale di colore, 3 colori (rosso, bianco e verde)
- Set di icone, 3 frecce direzionate
- Maggiore di, superiore al valore medio fra il minimo e il massimo dei dati – valore e formattazione modificabili
- Primo 10%
- Cancella formattazione, rimuove tutte le formattazioni dalla selezione
Se i contenuti sono testuali, Analisi rapida → Formattazione (4) aggiunge le regole specifiche:
- Testo che contiene, contenente il testo della prima cella della selezione – testo modificabile
- Valori duplicati
- Valori univoci
- Uguale a, coincidente con il testo della prima cella della selezione – testo modificabile
Infine, se le celle contengono date, Analisi rapida → Formattazione (5) visualizza anche le regole:
- Mese scorso
- Settimana scorsa
- Maggiore di, superiore alla data intermedia fra la più recente e la meno recente – data e formattazione modificabili
- Minore di, come Maggiore di ma riferito alle date precedenti
- Uguale a, come Maggiore di ma riferito alle date identiche
