#Excel – Come usare la formattazione condizionale di Excel – Parte 4

In questo tutorial, continueremo ad esplorare l’affascinante mondo della Formattazione condizionale di Excel. Se non vi sentite a vostro agio con questo argomento, consigliamo di partire dagli articoli precedenti per rinfrescarne la basi:

Oggi ci soffermeremo su come utilizzare le formule di Excel per formattare le singole celle o intere righe in base a dei valori specificati o in base al valore di un’altra cella. Questa è spesso considerata un’acrobazia avanzata di formattazione condizionale e una volta fatta vostra, vi aiuterà a spingere la formattazione dei vostri fogli elettronici molto al di là dei comuni utilizzi.

Formule di Excel per la formattazione condizionale basata sul valore di una cella

Le regole di formattazione condizionale predefinite di Excel sono progettate principalmente per formattare le celle in base ai loro propri valori o a dei valori specificati. Sto parlando delle barre dei dati, scale di colori, set di icone e altre regole a vostra disposizione sul pulsante “Formattazione condizionale” della barra multifunzione.

Se si desidera applicare la formattazione condizionale in base al valore di un’altra cella o formattare l’intera riga sulla base del valore di una singola cella, allora si avrà bisogno di utilizzare le formule di Excel. Quindi, vediamo come si può creare una regola utilizzando una formula e dopo vi fornirò una serie di esempi e di formule adatte a scopi diversi.

Come creare una regola di formattazione condizionale utilizzando una formula

Come saprete, in tutte le versioni moderne di Excel (2013, 2010 e 2007), la funzionalità di formattazione condizionale risiede nella scheda HOME > gruppo Stili. In Excel 2003 è possibile trovarla al menu Formato.

Quindi, è possibile impostare una regola di formattazione condizionale sulla base di una formula in questo modo:

  1. Selezionate le celle da formattare. È possibile selezionare una colonna, più colonne o anche l’intera tabella.
    SUGGERIMENTO
    Se prevedete di aggiungere altri dati in futuro e desiderate che la regola di formattazione condizionale venga applicata anche alle nuove voci automaticamente, è possibile: 1) Convertire l’intervallo di celle in una tabella (scheda INSERISCI > Tabella). In questo modo, la formattazione condizionale verrà applicata automaticamente a tutti i nuovi record; 2) Selezionare alcune righe vuote sotto i vostri dati, ad esempio 100 righe vuote, in modo che la regola venga applicata anche ad esse. Tra le due soluzioni, io preferirei la prima.
  2. Fate clic su Formattazione condizionale > Nuova regola…
    Creare una nuova regola di formattazione condizionale usando una formula
  3. Nella finestra “Nuova regola di formattazione”, selezionate “Utilizza una formula per determinare le celle da formattare
  4. Immettete la formula nella casella corrispondente
  5. Fate clic sul pulsante “Formato…” per impostare il formato personalizzato
    Inserite la formula e cliccate sul pulsante Formato per impostare il formato personalizzato
  6. Scorrete tra le schede Carattere, Bordo e Riempimento e giocate con le diverse opzioni come lo stile del carattere, il Colore motivo e gli effetti di riempimento per impostare il formato che si adatti alle vostre esigenze. Se la palette standard dei colori non fosse sufficiente, fate clic su “Altri colori…” e scegliete qualsiasi colore RGB o HSL a vostro piacimento. Al termine, fate clic sul pulsante OK
    Scorrete le schede Carattere Bordo e Riempimento e impostate il formato personalizzato
  7. Assicuratevi che la sezione Anteprima visualizzi il formato desiderato e se così fosse, fate clic sul pulsante OK al fine di salvare la regola. Se non siete del tutto soddisfatti del formato di anteprima, fate di nuovo clic sul pulsante “Formato…” e apportate le modifiche
    Assicuratevi che la sezione Anteprima visualizzi il formato scelto e salvate la regola

 

Esempi di formule di Formattazione condizionale

Ora che sapete come creare e applicare la formattazione condizionale di Excel in base al valore di un’altra cella, proseguiamo e vediamo come utilizzare in pratica le varie formule di Excel:

Formule per confrontare i valori (numeri e testo)

Come sapete Microsoft Excel offre alcune regole pronte all’uso per formattare le celle che abbiano valore superiore, minore o uguale ad un altro valore specificato (Formattazione condizionale > Regole evidenziazione celle). Tuttavia, queste regole non funzionano se si desidera formattare alcune colonne o intere righe in base al valore di una cella in un’altra colonna. In questo caso, occorre utilizzare delle formule analoghe

Condizione Esempio di formula
Uguale a =$B2=10
Diverso da =$B2<>10
Maggiore di =$B2>10
Maggiore o uguale a =$B2>=10
Minore di =$B2<10
Minore o uguale a =$B2<=10
Tra =E($B2>5; $B2<10)

SUGGERIMENTO
Per conoscere in modo approfondito l’utilizzo degli operatori logici di Excel, vi consigliamo di consultare il seguente articolo: Come utilizzare gli operatori logici di Excel

La schermata che segue mostra un esempio di formula “Maggiore di” che mette in evidenza i nomi dei prodotti nella colonna A, se il numero di articoli in magazzino (colonna C) è superiore a 0. Vi invitiamo a notare che la formula si applica solo alla colonna A ($A$2:$A$8). Ma se selezioniamo l’intera tabella (nel nostro caso, $A$2:$E$8), questa evidenzierà intere righe in base al valore nella colonna C.

LEGGI:  #Excel - Un CERCA.VERT senza errori #N/D

Regola di formattazione di Excel basata sul valore di una altra cella

In modo simile, è possibile creare una regola di formattazione condizionale per confrontare i valori di due celle. Per esempio:

  • =$A2<$B2 – formatta le celle o le righe se un valore nella colonna A è inferiore al valore corrispondente della colonna B
  • =$A2=$B2 – formatta le celle o le righe se i valori nelle colonne A e B sono uguali
  • =$A2<>$B2 – formatta celle o righe se un valore nella colonna A non è uguale al corrispondente nella colonna B

Come potete vedere nello screenshot qui sotto, queste formule funzionano per valori di testo, così come per i numeri

Formule di Excel per confrontare valori di testo

Formule E() e O()

Se desiderate formattare una tabella di Excel in base a 2 o più condizioni, occorre utilizzare le funzioni E() oppure O():

Condizione Formula Descrizione
Se devono essere soddisfatte entrambe le condizioni =E($B2<$C2; $C2<$D2) Formatta le celle se il valore nella colonna B è inferiore a quello nella colonna C, e se il valore della colonna C è inferiore a quello nella colonna D.
Se deve essere soddisfatta solo una delle condizioni =O($B2<$C2; $C2<$D2) Formatta le celle se il valore nella colonna B è inferiore a quello nella colonna C, oppure se il valore nella colonna C è inferiore a quello nella colonna D.

Nello screenshot qui sotto, si usa la formula =E($C2>0; $D2=”Europa”) per modificare il colore di sfondo delle righe se il numero di articoli in magazzino (colonna C) è maggiore di 0 e se il prodotto viene spedito in Europa (colonna D). Vi invitiamo a notare che la formula funziona con valori di testo, così come con i numeri.

Formattazione condizionale di Excel con la funzione E

Naturalmente, è possibile utilizzare due, tre o più condizioni nelle formule E() e O().

Queste sono le formule di base della formattazione condizionale. Consideriamo ora esempi un po’ più complessi ma molto più interessanti.

La formattazione condizionale per le celle vuote e non vuote

Penso che tutti sappiate come formattare le celle vuote e non vuote in Excel: è sufficiente creare una nuova regola di tipo “Formatta solo le celle che contengono” e scegliere “Valore vuoto” o “Valore non vuoto”.

Regola per formattare celle vuote o non vuote

Ma cosa succederebbe se si desiderasse formattare le celle in una determinata colonna se una cella corrispondente in un’altra colonna fosse vuota o non vuota? In questo caso, è necessario utilizzare di nuovo formule di Excel:

  • Formula per celle vuote: =$B2=”” – formatta le celle/righe selezionate se la cella corrispondente nella colonna B è vuota
  • Formula per celle non vuote: =$B2<>”” – formatta le celle/righe selezionate se la cella corrispondente nella colonna B non è vuota
ATTENZIONE
Le formule sopra funzioneranno per le celle che sono “visivamente” vuote o non vuote. Se si utilizza una funzione di Excel che restituisce una stringa vuota, ad esempio, =SE(FALSO; “OK”; “”), e non si volesse che queste celle fossero trattate come vuote, occorrerebbe utilizzare invece le seguenti formule =VAL.VUOTO(A1)=VERO oppure =VAL.VUOTO(A1)=FALSO per formattare rispettivamente le celle vuote o non vuote.

Ed ecco un esempio di come è possibile utilizzare le formule di cui sopra nella pratica. Supponiamo, si disponga di una colonna (B), che è “Data di vendita” e un’altra colonna (C) “Consegna”. Queste 2 colonne contengono un valore solo se, rispettivamente,  è stata fatta una vendita e se l’articolo è stato  spedito. Vogliamo che l’intera riga diventi arancione quando è stata fatta una vendita; e quando l’ordine viene spedito, la stessa riga dovrebbe diventare verde. Per raggiungere questo obiettivo, è necessario creare due regole di formattazione condizionale con le seguenti formule:

  • Righe arancioni (una cella nella colonna B non è vuota): =$B2 <> “”
  • Righe verdi (le celle nella colonna B e nella colonna C, non sono vuote): =E($B2 <> “”;$C2 <> “”)

Un’ulteriore cosa da fare è poi quella di spostare la seconda regola verso l’alto e selezionare la casella di controllo “Interrompi se vera” accanto a questa regola:

Regole di formattazione condizionale basate sullo stato di vuote e non vuote di altre celle

In questo caso particolare, l’opzione “Interrompi se vera” è in realtà superflua, e la regola funziona con o senza di essa. Si consiglia di selezionare questa casella solo come ulteriore precauzione, nel caso vengano aggiunte altre regole in futuro che potrebbero entrare in conflitto con quelle esistenti.

Formule per lavorare con i valori di testo

Se si desidera applicare la formattazione condizionale alle colonne selezionate quando un’altra cella nella stessa riga contiene una certa parola, è possibile utilizzare una semplice formula come =$D2=”Europa” (abbiamo usato una formula simile in uno degli esempi precedenti). Tuttavia, questa formula funziona solo se trova una corrispondenza esatta.

Per una corrispondenza parziale, avrete bisogno di un’altra funzione di Excel: =RICERCA(). Si usa in questo modo:

=RICERCA(“Europa”; $B2)>0 – formatta le celle o le righe selezionate se una cella corrispondente nella colonna D contiene la parola “Europa”. Questa formula troverà tutte queste celle, indipendentemente da dove il testo cercato si trovi, ad esempio “Spedizione in tutta Europa”, “In tutta Europa, ad eccezione di …” ecc.

LEGGI:  #Excel - Come trovare una corrispondenza parziale tra numeri [Quick Tip]

=RICERCA(“Europa”; $B2)>1 – formatta le celle o le righe selezionate se il contenuto della cella inizia con il testo cercato.

Formule di formattazione condizionale basate su valori di testo

Formule per evidenziare i valori duplicati

Se il vostro compito fosse quello di usare la Formattazione condizionale per evidenziare le celle con i valori duplicati, sarebbe possibile utilizzare la regola predefinita disponibile sotto Formattazione condizionale > Regole evidenziazione celle > Valori duplicati…

Tuttavia, in alcuni casi, i dati hanno un aspetto migliore se coloriamo le celle selezionate quando i valori duplicati si trovano in un’altra colonna. In questo caso, sarà necessario impiegare nuovamente una formula di formattazione condizionale di Excel, e questa volta verrà utilizzato una formula =CONTA.SE(). Come sapete, questa funzione di Excel conta il numero di celle che soddisfano un criterio all’interno di un intervallo specificato.

Evidenziare i duplicati compresa la 1^ occorrenza

=CONTA.SE($A$2:$A$8; $A2)>1 – questa formula trova valori i duplicati nell’intervallo specificato della colonna A (A2:A8 nel nostro caso), comprese le prime occorrenze.

Come potete vedere dallo screenshot qui sotto, in questa regola ho deciso di cambiare il colore del carattere, tanto per cambiare 🙂 .

Formula per evidenziare i valori duplicati compresa la prima occorrenza

Evidenziare i duplicati senza la 1^ occorrenza

Per ignorare la prima occorrenza ed evidenziare solo i valori duplicati successivi, utilizzate invece questa formula: =CONTA.SE($A$2:$A2; $A2)>1

Formula per evidenziare i duplicati senza la prima occorrenza

Evidenziare i duplicati consecutivi

Se si preferisse evidenziare solo i duplicati presenti in righe consecutive, è possibile farlo nel modo seguente. Questo metodo funziona per qualsiasi tipo di dati: numeri, valori di testo e date.

  • Selezionate la colonna in cui si desidera evidenziare i duplicati senza l’intestazione di colonna
  • Create una regola di formattazione condizionale con queste semplici formule:
    • Regola 1 (blu): =$A1=$A2 – sottolinea la 2^ occorrenza e tutte le occorrenze successive, se presenti.
    • Regola 2 (verde): =$A2=$A3 – evidenzia la 1^ occorrenza.

Nelle formule di cui sopra, A è la colonna che si desidera controllare, $A1 è l’intestazione di colonna e $A2 – la prima cella con i dati.

ATTENZIONE
Affinché le formule funzionino correttamente, è indispensabile che la regola 1, che evidenzia la 2^ e tutte le successive occorrenze, sia la prima regola della lista, soprattutto se si utilizzano due colori diversi.

Evidenziare i duplicati consecutivi

Verificare la presenza di interi record duplicati

Se si desiderasse applicare la formattazione condizionale verificando l’univocità di interi record (più colonne), è necessario aggiungere una colonna in più alla nostra tabella in cui concateneremo i valori delle colonne chiave mediante una semplice formula come questa =A2&B2. Dopo di che si crea una regola utilizzando una delle variazioni viste poc’anzi della funzione =CONTA.SE (con o senza prima occorrenza). Naturalmente, dopo aver creato la regola è possibile nascondere la colonna aggiuntiva.

Formula per evidenziare duplicati attraverso diverse colonne

Confrontare 2 colonne per trovare valori duplicati

Una delle attività più frequenti in Excel è quella di verificare 2 colonne alla ricerca di valori duplicati. Vale a dire trovare ed evidenziare i valori che esistono in entrambe le colonne. Per fare questo, è necessario creare una regola di formattazione condizionale per ogni colonna con una combinazione delle funzioni =VAL.ERRORE() e =CONFRONTA():

ATTENZIONE
Per fare in modo che tali formule condizionali funzionino correttamente, è molto importante che le regole vengano applicate alle colonne intere, ad esempio, =$A:$A e =$B:$B.

Potete vedere un esempio di utilizzo pratico nello screenshot seguente che mette in evidenza i duplicati nelle colonne A ed B.

Formattazione condizionale per confontare 2 colonne alla ricerca di duplicati

 

Formule per evidenziare valori al di sopra o al di sotto della media

Quando si lavora con diverse serie di dati numerici, la funzione =MEDIA() può tornare utile per formattare le celle i cui valori sono al di sotto o al di sopra della media di una colonna.

Ad esempio, è possibile utilizzare la formula =$E2<MEDIA($E$2:$E$8) per la formattazione condizionale di righe in cui il fatturato sia al di sotto della media, come mostrato nello screenshot qui sotto. Se state cercando il contrario, vale a dire evidenziare i prodotti performanti sopra la media, occorre semplicemente sostituire “<” con “>” nella formula: =$E2>MEDIA($E$2:$E$8).

Una regola di formattazione condizionale per evidenziare i valori sotto la media

Come evidenziare il valore più vicino ad un dato numero

Se avessi un insieme di numeri, c’è un modo per evidenziare il numero più vicino a zero, utilizzando la formattazione condizionale di Excel?

Esempio 1. Trovare il valore più vicino, inclusa la corrispondenza esatta

Nel nostro esempio, troveremo ed evidenzieremo il numero che più si avvicina a zero. Se l’insieme di dati contiene uno o più zeri, tutti saranno evidenziati. Se non vi è nessuno 0, allora il valore più vicino a zero, positivo o negativo, sarà evidenziato.

Prima di tutto, è necessario inserire la seguente formula in una qualsiasi cella vuota del foglio di lavoro, potrete nascondere quella cella in seguito, se necessario. La formula trova il numero in un dato intervallo che è più vicino al numero specificato e restituisce il valore assoluto di tale numero (valore assoluto è il numero senza il suo segno):

=MIN(ASS(B3:D13-(0)))

Nella formula di cui sopra, B3:D13 è il vostro intervallo di celle e 0 è il numero per il quale si desidera trovare la corrispondenza più vicina. Per esempio, se siete alla ricerca del valore più vicino a 5, la formula cambierà così: =MIN(ASS(B3:D13-(5))).

ATTENZIONE
Si tratta di una formula matriciale, pertanto è necessario premere Ctrl + Maiusc + Invio invece di un semplice Invio per inserirla. Per maggiori informazioni in merito alle matrici e alle formule matriciali, consigliamo la lettura del seguente articolo e degli altri ad esso collegati: Come usare le matrici e le formule matriciali

E ora creiamo una regola di formattazione condizionale con la seguente formula, dove B3 è la cella in alto a destra del vostro intervallo e $C$2 è la cella con la formula matriciale di cui sopra:

=O(B3=0-$C$2;B3=0+$C$2)

Vi invitiamo a notare l’uso dei riferimenti assoluti nell’indirizzo della cella contenente la formula matriciale ($C$2), in quanto questa cella è fissa. Inoltre, è necessario sostituire 0 con il numero per il quale si desidera evidenziare la corrispondenza più vicina. Ad esempio, se voleste evidenziare il valore più vicino a 5, la formula cambierebbe così: =O(B3=5-$C$2;B3=5+$C$2).

Evidenziare il valore piu vicino ad un dato numero incluso il numero stesso

Esempio 2. Evidenziare il valore più vicino, ma NON la corrispondenza esatta

Nel caso in cui non si volesse evidenziare la corrispondenza esatta, è necessaria una formula matriciale diversa che troverà il valore più vicino, ignorando la corrispondenza esatta.

LEGGI:  #Excel - Come scoprire quali parole appaiono in una stringa

Ad esempio, la seguente formula matriciale trova il valore più vicino a 0 nell’intervallo specificato, ignorando gli zeri, se ci fossero:

=MIN(ASS(B3:C13-(0))+(10^0*(B3:C13=0)))

Si ricorda di premere Ctrl + Maiusc + Invio dopo aver finito di digitare la formula matriciale.

La formula di formattazione condizionale è la stessa dell’esempio di cui sopra:

=O(B3=0-$C$2;B3=0+$C$2)

Tuttavia, dato che la nostra formula matriciale nella cella C2 ignora la corrispondenza esatta, la regola di formattazione condizionale ignora gli zeri e mette in evidenza il valore di 0,029744696 che è la corrispondenza più vicina.

Evidenziare il valore piu vicino ad un dato numero escluso il numero stesso

Se si volesse trovare il valore più vicino ad un altro numero, basta sostituire “0” con il numero che si desidera, sia nella matrice che nella formula di formattazione condizionale.

Perché la mia formattazione condizionale non funziona correttamente?

Se la regola di formattazione condizionale non funziona come previsto, anche se la formula è apparentemente corretta, non arrabbiamoci! Molto probabilmente non è a causa di qualche strano bug nella formattazione condizionale di Excel, piuttosto la causa sarà un piccolo errore, non evidente a prima vista. Vi invitiamo a provare i 6 semplici passaggi qui di seguito e siamo sicuri che farete funzionare la vostra formula:

  1. Utilizzate correttamente gli indirizzi di cella assoluti e relativi. E’ molto difficile esporre una regola generale funzionante nel 100 per cento dei casi. Ma il più delle volte, nei vostri riferimenti di cella, dovrete utilizzare un riferimento di colonna assoluto (con $) e un riferimento di riga relativo (senza $) , ad esempio, =$A1>1. Tenete presente che le formule =A1=1, =$A$1=1 e =A$1=1 produrranno risultati diversi.
  2. Verificate l’intervallo di applicazione. Controllate se la regola di formattazione condizionale viene applicata al corretto intervallo di celle. Una regola generale è questa: selezionate tutte le celle/righe da formattare, ma non includete le intestazioni di colonna.
  3. Scrivete la vostra formula per la cella più in alto a sinistra. Nelle regole di formattazione condizionale, i riferimenti di cella sono relativi alla cella più in alto a sinistra dell’intervallo di applicazione. Pertanto, scrivete sempre la formula di formattazione condizionale per la 1^ riga con i dati. Ad esempio, se i dati iniziano nella riga 2, inserite in tutte le righe =A$2=10 per evidenziare le celle con valori pari a 10. Un errore comune è quello di usare il riferimento alla prima riga (ad esempio =A$1=10). Ricordate, nella formula si fa riferimento a riga 1 solo se la tabella non ha intestazioni e i dati partono davvero dalla riga 1. In questo caso potreste vedere la vostra regola funzionare, ma le righe formattate sarebbero diverse da quelle che dovrebbero.
  4. Controllate la regola creata. Controllate due volte la regola nella finestra di dialogo “Gestione regole formattazione condizionale”. A volte, per nessuna ragione, Microsoft Excel distorce la regola che avete appena creato. Quindi, se la regola non funziona, andate su Formattazione condizionale > Gestisci regole… e verificare sia la formula che l’intervallo di applicazione. Se avete copiato la formula dal web o da qualche altra fonte esterna, assicuratevi che siano utilizzate le virgolette semplici.
  5. Sistemate i riferimenti di cella quando copiate la regola. Se si copia la formattazione condizionale di Excel utilizzando “Copia formato”, non dimenticate di sistemare tutti i riferimenti di cella nella formula.
  6. Suddividete le formule complesse in elementi semplici. Se si utilizza una formula di Excel complessa, che comprende diverse funzioni, dividetela in elementi più semplici e verificate ogni funzione singolarmente.

Speriamo che le formule di formattazione condizionale che avete imparato in questo tutorial vi aiuteranno a dare un senso a qualunque progetto stiate lavorando.

In uno dei prossimi articoli esamineremo le funzionalità di formattazione condizionale di Excel per le date. Arrivederci a presto e grazie per la lettura!

Copyright © 2014-2017  Office Academy. Tutti i diritti riservati.
Vai alla barra degli strumenti