Nel tempo abbiamo pubblicato diversi articoli che coprono i vari aspetti della formattazione condizionale di Excel. Inaspettatamente, è emerso che non è la creazione di una regola e neanche la possibilità di non inserire una formula adeguata che rappresenta la sfida più grande. La parte più complessa e la fonte più comune di problemi sembra essere l’utilizzo di riferimenti di cella corretti nelle formule.
Allora, perché non investire pochi minuti per capire questa cosa? Questo vi farà sicuramente risparmiare molto più tempo a lungo termine!
Se poi volete approfondire ulteriormente l’argomento della formattazione condizionale, potete acquistare il nostro libro della collana “I quaderni di Excel Academy” dedicato all’argomento.
Come funzionano i riferimenti di cella assoluti e relativi nelle regole di formattazione condizionale
In tutte le formule di Excel, comprese le regole di formattazione condizionale, i riferimenti di cella possono essere dei seguenti tipi:
- Riferimenti di cella assoluti (con il segno $, ad esempio $A$1) rimangono sempre costanti, non importa dove essi vengano copiati.
- Riferimenti di cella relativi (senza il simbolo $, ad esempio A1) quando copiati su più celle, variano in base alla posizione relativa di righe e colonne.
- Riferimenti di cella misti (colonna assoluta e riga relativa – ad esempio, $A1 – o colonna relativa e riga assoluta – ad esempio A$1. Nelle regole di formattazione condizionale di Excel, i riferimenti di cella misti sono utilizzati più di frequente, per indicare che una lettera di colonna o un numero riga deve rimanere fissa quando la regola viene applicata a tutte le altre celle dell’intervallo selezionato.
Nelle regole di formattazione condizionale, i riferimenti di cella sono relativi alla cella più in alto a sinistra dell’intervallo selezionato. Così, quando si crea una nuova regola, si può semplicemente far finta di star scrivendo una formula solo per la cella in alto a sinistra, e Excel “copierà” la formula in tutte le altre celle nell’intervallo selezionato.
Ora, permettetemi di presentare alcuni esempi che dimostrano come formule apparentemente identiche producono risultati diversi a seconda di quali tipi di riferimenti di cella vengono utilizzati.
Esempio 1. Colonna assoluta e riga relativa
Questo è il modello più tipico per le regole di formattazione condizionale e nel 90% dei casi i riferimenti di cella nelle regole di formattazione condizionale di Excel saranno di questo tipo.
Facciamo un semplice regola che confronta i valori nelle colonne A e B ed evidenzia il valore nella colonna A se è maggiore del valore nella colonna B nella stessa riga.
Se avete bisogno di istruzioni dettagliate su come creare regole di formattazione condizionale con formule, andate qui – Come creare una regola di formattazione condizionale utilizzando una formula. In questo caso, la formula è semplice:
=$A1>$B1
Poiché si confrontano sempre i valori delle colonne A e B, “fissiamo” queste colonne utilizzando i riferimenti di colonna assoluti, notate il segno $ prima delle lettere di colonna nella formula di cui sopra. E, dal momento che si confrontano i valori in ogni riga singolarmente, si utilizzano riferimenti relativi di riga, senza $.
Esempio 2. Colonna relativa e riga assoluta
Questo tipo riferimento di cella è l’opposto del precedente. In questo caso, il numero di riga è sempre costante, mentre la colonna cambia. È possibile utilizzare tali riferimenti quando si desidera controllare i valori in una data riga rispetto ad un certo valore o rispetto ai valori di un’altra riga.
Ad esempio, la formula seguente confronta i valori nella riga 1 e 2 e la regola evidenzia il valore nella riga 1 se è maggiore del valore nella riga 2 nella stessa colonna:
=A$1>A$2
Siccome desiderate che il numero di riga rimanga fisso, si utilizzano i riferimenti di riga assoluti, con il simbolo $. E siccome volete confrontare i valori in ogni colonna singolarmente, create la regola per la colonna più a sinistra (A) e utilizzate i riferimenti di colonna relativi, senza il simbolo $.
Esempio 3. Colonna assoluta e riga assoluta
Si utilizzano riferimenti di colonna e di riga assoluti se si desidera confrontare tutti i valori nell’intervallo selezionato con un altro valore.
Ad esempio, creiamo una regola che metta in evidenza tutti i valori di colonna A che sono maggiori di un valore nella cella B1. La formula è la seguente:
=$A1>$B$1
Vi invito a prestare attenzione all’uso dei seguenti riferimenti:
- $A1 – si utilizza un riferimento di colonna assoluto e di riga relativo perché vogliamo controllare i valori in tutte le celle di colonna A rispetto al valore di cella B1
- $B$1 – si utilizza un riferimento di colonna e di riga assoluti, perché la cella B1 contiene il valore che vogliamo confrontare con tutti gli altri valori ed è necessario che questo riferimento di cella sia essere costante.
Esempio 4. Colonna relativa e riga relativa
Questo tipo di riferimento è usato raramente nelle regole di formattazione condizionale di Excel. Si utilizza un riferimento di colonna e di riga relativo quando si desidera controllare tutte le celle dell’intervallo selezionato rispetto ad un certo valore.
Supponiamo che desideriate evidenziare tutte le celle nelle colonne A e B che sono maggiori del valore presente in cella B1. Potete semplicemente copiare la formula dell’esempio precedente e sostituire $A1 con A1 dal momento che non volete fissare né la riga né la colonna:
=A1>$B$1
Ricordate, la formula si scrive per la cella in alto a sinistra dell’intervallo, A1 nel nostro caso. Quando si crea una regola con questa formula e la si applica ad un certo range, ad esempio A1:B10, il risultato sarà simile a questo:
Speriamo che questi semplici esempi vi abbiano aiutato a capire l’essenza dei riferimenti di cella relativi e assoluti in Excel. Ora che sapete come determinare il tipo di riferimento appropriato per le regole, andare avanti e sfruttate il potere della formattazione condizionale di Excel per i vostri progetti. Le seguenti risorse possono rivelarsi utili:
- Formule di Excel per la formattazione condizionale in base ad un’altra cella – Il tutorial fornisce diversi esempi che dimostrano come formattare singole celle o intere righe in base al valore di un’altra cella in Excel 2016, 2013, 2010 e 2007
- Come formattare condizionalmente le date in Excel – L’articolo spiega come sia possibile applicare la formattazione condizionale di Excel per le date utilizzando le regole e formule predefinite
- Come evidenziare righe o colonne alternate in Excel – Il tutorial spiega come si alternano righe e colonne alternate in Excel
- Come cambiare il colore di sfondo in base al valore delle celle – Due modi veloci per cambiare il colore delle celle in base ai loro valori
- Come evidenziare automaticamente i duplicati in Excel – Vedrete come utilizzare la formattazione condizionale di Excel per evidenziare i valori duplicati alla digitazione di nuovi elementi
