L'aggiunta della formattazione condizionale in Excel consente di applicare diverse opzioni di formattazione a una cella oa un intervallo di celle che soddisfano condizioni specifiche impostate. Le opzioni di formattazione vengono applicate solo quando le celle selezionate soddisfano queste condizioni impostate.
Le opzioni di formattazione che possono essere applicate includono le modifiche ai colori dei caratteri e dello sfondo, gli stili dei caratteri, i bordi delle celle e l'aggiunta della formattazione numerica ai dati.
Excel ha avuto un numero di opzioni integrate per condizioni di uso comune come la ricerca di numeri che sono maggiori o minori di un certo valore o di trovare numeri che sono sopra o sotto il valore medio.
Oltre a queste opzioni preimpostate, è anche possibile creare regole di formattazione condizionale personalizzate utilizzando le formule di Excel per verificare le condizioni specificate dall'utente.
01 di 04Applicazione di più regole in Excel
Più di una regola può essere applicata agli stessi dati per verificare condizioni diverse. Ad esempio, i dati di budget potrebbero avere condizioni impostate che applicano modifiche di formattazione quando vengono utilizzati determinati livelli, ad esempio il 50%, il 75% e il 100% del budget totale.
In tali circostanze, Excel determina innanzitutto se le varie regole sono in conflitto e, in tal caso, il programma segue un ordine di precedenza impostato per determinare quale regola di formattazione condizionale viene applicata ai dati.
Ricerca di dati superiori al 25% e al 50% in aumento
Nell'esempio seguente, verranno applicate due regole di formattazione condizionale personalizzate all'intervallo di celle B2 a B5.
- La prima regola controlla se i dati sono in celle A2 a A5 è maggiore del valore corrispondente in B2 a B5 di oltre il 25%.
- La seconda regola controlla se gli stessi dati in A2: A5 supera il valore corrispondente in B2: B5 di oltre il 50%.
Come si può vedere nell'immagine sopra, se una delle condizioni precedenti è vera, il colore di sfondo della cella o delle celle nell'intervallo B1: B4 cambierà.
- Per i dati in cui la differenza è superiore al 25%, il colore di sfondo della cella diventa verde.
- Se la differenza è superiore al 50%, il colore di sfondo della cella diventerà rosso.
Le regole utilizzate per completare questa attività saranno inserite usando la formattazione condizionale Nuova regola di formattazione la finestra di dialogo. Inizia inserendo i dati di esempio nelle celle da A1 a C5 come mostrato nell'immagine sopra
Nel terzo passo del tutorial aggiungerà le formule alle celle C2: C4 che mostra l'esatta differenza percentuale tra i valori nelle celle A2: A5 e B2: B5 per verificare l'accuratezza delle regole di formattazione condizionale.
02 di 04Impostazione delle regole di formattazione condizionale
Come accennato, le regole di formattazione condizionale che controllano le due condizioni saranno inserite usando la formattazione condizionale Nuova regola di formattazione la finestra di dialogo. Iniziamo impostando la nostra formattazione condizionale personalizzata:
Formattazione condizionale per trovare un aumento superiore al 25%
= (A2-B2) / A2> 25%
- Evidenziare celle B2 a B5 nel foglio di lavoro.
- Clicca sul Scheda Home del nastro.
- Clicca sul Formattazione condizionale icona nel nastro per aprire il menu a discesa.
- Scegliere Nuova regola per aprire il Nuova regola di formattazione la finestra di dialogo.
- Dal Stile: casella, selezionare il Classico opzione.
- Nel secondo menu a discesa, fai clic sull'ultima opzioneUtilizzare una formula per determinare quali celle formattare.
- Digitare il formula fornito sopra nello spazio fornito.
- Clicca sul Formato con: menu a discesa quindi selezionare il Rosso chiaro Riempire con testo rosso scuro opzione.
- Clic ok per chiudere le finestre di dialogo e tornare al foglio di lavoro.
- A questo punto, il colore di sfondo di celle B3 e B5 dovrebbe essere verde
Formattazione condizionale per individuare un aumento superiore al 50 percento
= (A2-B2) / A2> 50%
Evidenziare celle B2 a B5 nel foglio di lavoro.
- Clicca sul Scheda Home del nastro.
- Clicca sul Formattazione condizionale icona nel nastro per aprire il menu a discesa.
- Scegliere Nuova regola per aprire il Nuova regola di formattazione la finestra di dialogo.
- Dal Stile: casella, selezionare il Classico opzione.
- Nel secondo menu a discesa, fai clic sull'ultima opzioneUtilizzare una formula per determinare quali celle formattare.
- Digitare il formula fornito sopra nello spazio fornito.
- Clicca sul Formato con: menu a discesa quindi selezionare il Verde chiaro Riempire con testo verde scuro opzione.
- Clic ok per chiudere le finestre di dialogo e tornare al foglio di lavoro.
Il colore di sfondo di cella B3 dovrebbe essere ancora verde indicando che la differenza percentuale tra i numeri in celle A3 e B3 è maggiore del 25% ma inferiore o uguale al 50%. Il colore di sfondo di cella B5 dovrebbe cambiare in rosso indicando che la differenza percentuale tra i numeri in celle A5 e B5 è superiore al 50 percento.
03 di 04Controllo delle regole di formattazione condizionale
Per verificare che le regole di formattazione condizionale immesse siano corrette, possiamo inserire le formule nelle celle C2: C5 che calcolerà l'esatta differenza percentuale tra i numeri nelle gamme A2: A5 e B2: B5.
= (A2-B2) / A2
- Clicca su cella C2 per renderlo la cella attiva.
- Digitare la formula sopra e premere il tasto accedere tasto sulla tastiera.
- La risposta 10% dovrebbe apparire in cella C2, indicando che il numero in cella A2 è il 10% più grande del numero in cella B2.
- Potrebbe essere necessario modificare la formattazione in cella C2 per visualizzare la risposta in percentuale.
- Utilizzare il riempire la maniglia copiare la formula da cella C2 a celle C3 a C5.
- Le risposte per celle C3 a C5 dovrebbe essere 30%, 25% e 60%.
Le risposte in queste celle mostrano che le regole di formattazione condizionale create sono corrette poiché la differenza tra celle A3 e B3 è maggiore del 25 percento e la differenza tra celle A5 e B5 è superiore al 50 percento.
Cella B4 non ha cambiato colore perché la differenza tra celle A4 e B4 equivale al 25 percento e la nostra regola di formattazione condizionale ha specificato che era necessaria una percentuale superiore al 25 percento affinché il colore dello sfondo diventasse verde.
04 di 04Ordine di precedenza per la formattazione condizionale
Quando vengono applicate più regole allo stesso intervallo di dati, Excel determina innanzitutto se le regole sono in conflitto. Le regole in conflitto sono quelle in cui le opzioni di formattazione scelte per ciascuna regola non possono essere applicate agli stessi dati.
Nell'esempio utilizzato in questo tutorial, le regole entrano in conflitto poiché entrambe le regole utilizzano la stessa opzione di formattazione, ovvero quella di modificare il colore della cella di sfondo.
Nella situazione in cui la seconda regola è vera (la differenza di valore è maggiore del 50% tra due celle), allora anche la prima regola (la differenza di valore è maggiore del 25%) è vera.
Excel's Order of Precedence
Poiché una cella non può avere contemporaneamente uno sfondo rosso e uno verde, Excel deve sapere quale regola di formattazione condizionale deve applicare.
La regola applicata viene determinata dall'ordine di precedenza di Excel, in base al quale la regola che è più in alto nell'elenco nella finestra di dialogo Gestore regole formattazione condizionale ha la precedenza.
Come mostrato nell'immagine sopra, la seconda regola utilizzata in questo tutorial è più in alto nella lista e, quindi, ha la precedenza sulla prima regola. Di conseguenza, il colore di sfondo della cella B5 diventa rosso.
Per impostazione predefinita, le nuove regole vengono aggiunte all'inizio dell'elenco e, pertanto, hanno una precedenza più alta. Per cambiare l'ordine di precedenza usa il Su e giù i pulsanti freccia nella finestra di dialogo come identificati nell'immagine sopra.
Applicazione di regole non in conflitto
Se due o più regole di formattazione condizionale non sono in conflitto, vengono applicate entrambe quando la condizione in cui ciascuna regola è in fase di test diventa vera.
Se la prima regola di formattazione condizionale nel nostro esempio ha formattato l'intervallo di celle B2: B5 con un bordo verde invece di un colore di sfondo verde, le due regole di formattazione condizionale non entrerebbero in conflitto poiché entrambi i formati possono essere applicati senza interferire con l'altro.
Di conseguenza, cella B5 avrebbe sia un bordo verde che un colore di sfondo rosso, poiché la differenza tra i numeri in celle A5 e B5 è maggiore del 25 e 50 percento.
Formattazione condizionale rispetto a formattazione regolare
In caso di conflitti tra le regole di formattazione condizionale e le opzioni di formattazione applicate manualmente, la regola di formattazione condizionale ha sempre la precedenza e verrà applicata al posto di qualsiasi opzione di formattazione aggiunta manualmente.
Se inizialmente è stato applicato un colore di sfondo giallo celle B2 a B5 nell'esempio, una volta aggiunte le regole di formattazione condizionale, solo celle B2 e B4 rimarrebbe giallo Perché le regole di formattazione condizionale immesse si applicano alle celle B3 e B5, i loro colori di sfondo cambierebbero rispettivamente da giallo a verde e rosso.
