Come applicare la formattazione condizionale in base a VLOOKUP in Excel?

Supponiamo che tu stia gestendo un elenco di punteggi degli studenti relativi al semestre corrente e a uno precedente e che tu voglia identificare rapidamente quali studenti hanno migliorato il proprio rendimento. In questo scenario, desideri confrontare il punteggio di ogni studente nel semestre attuale con il risultato ottenuto nel semestre precedente ed evidenziare visivamente coloro che hanno ottenuto un punteggio migliore.

Per l’Intervallo riga evidenziata nel foglio di lavoro Score, se il punteggio attuale di uno studente è superiore a quello del semestre scorso, puoi utilizzare VLOOKUP all’interno di una regola di formattazione condizionale. Questo approccio è ideale per monitorare il rendimento accademico, tracciare le tappe di un progetto o effettuare qualsiasi analisi di dati con misurazioni ripetute in cui siano necessari confronti.

1. Nel foglio di lavoro Score, seleziona l’intervallo dei punteggi degli studenti che vuoi valutare (escludendo le intestazioni; in questo esempio, seleziona B3:C26). Vai alla scheda Home, fai clic su Utilizza la formattazione condizionale e seleziona Nuova regola.

2. Nella finestra di dialogo Nuova regola di formattazione, procedi come segue:

1. Seleziona Utilizza una formula per determinare le celle da formattare.
2. Immetti la seguente formula nella casella Formatta i valori per cui questa formula è vera:

   =VLOOKUP($B3,'Score of Last Semester'!$B$2:$C$26,2,FALSE) < Score!$C3

3. Fai clic sul pulsante Formato per scegliere la formattazione desiderata.

Nota:In questa formula,

• $B3 fa riferimento al nome del primo studente nel foglio Score. Quando applichi la formattazione condizionale a più righe, Excel regola automaticamente questo riferimento per ciascuna riga.
• 'Score of Last Semester'!$B$2:$C$26 definisce l’intervallo di ricerca per i punteggi del semestre precedente. Modificalo in base alle tue esigenze se l’elenco è più lungo o inizia/termina su righe diverse.
• 2 indica che i valori da recuperare dall'intervallo di ricerca si trovano nella seconda colonna.
• Score!$C3 fa riferimento al punteggio attuale dello studente nel foglio di lavoro che stai formattando.

3. Nella finestra di dialogo Imposta formato cella, vai alla scheda Riempimento, scegli un colore di evidenziazione e conferma con OK>OK per applicare le modifiche ed uscire dalle finestre di dialogo.

Una volta applicata, la formattazione evidenzierà automaticamente ogni riga relativa a uno studente il cui punteggio del semestre corrente supera il record precedente, permettendo di individuare all’istante le tendenze positive.

Se noti che alcune righe non vengono evidenziate come previsto, assicurati che entrambi i fogli utilizzino nomi identici—senza spazi aggiuntivi o errori ortografici—e che gli intervalli di riferimento includano tutti i dati necessari. Poiché VLOOKUP restituisce un errore quando non trova una corrispondenza, è fondamentale garantire una struttura coerente dei dati per ottenere risultati accurati.

Potresti voler identificare visivamente se gli elementi di un elenco sono presenti in un altro foglio, ad esempio contrassegnare i partecipanti di un elenco di vincitori che compaiono anche in un elenco principale. Utilizza la formattazione condizionale con VLOOKUP consente di evidenziare in tempo reale gli elementi corrispondenti o non corrispondenti, risultando utile in molti scenari di gestione di eventi o partecipanti.

Nota: Trascinare la barra di scorrimento verticale per visualizzare l’immagine sopra.

In questo caso d’uso, potresti avere un elenco di vincitori nel foglio Sheet1 e un elenco completo degli studenti nel foglio Sheet2. Per evidenziare gli intervalli con nome nell'elenco dei vincitori che sono anche presenti nell'elenco principale, procedi come segue:

1. Seleziona i nomi nell'elenco dei vincitori (escludendo le intestazioni), quindi vai su Home > Formattazione condizionale > Nuova regola.

2. Nella finestra di dialogo Nuova regola di formattazione, procedi nel seguente modo:

1. In Seleziona un tipo di regola, scegli Utilizza una formula per determinare le celle da formattare.
2. Immetti la seguente formula nella casella Formatta i valori per cui questa formula è vera:

   =NOT(ISNA(VLOOKUP($C3,Sheet2!$B$2:$C$24,1,FALSE)))

3. Fai clic sul pulsante Formato per impostare lo stile di evidenziazione.

Nota:

• $C3 fa riferimento al nome nell'elenco dei vincitori; assicurati che la tua selezione corrisponda esattamente alla struttura dei tuoi dati.
• Sheet2!$B$2:$C$24 è la tabella di ricerca che contiene l'elenco completo degli studenti. Adatta l'intervallo in base alle tue esigenze per farlo corrispondere al tuo foglio.
• 1 indica che VLOOKUP cercherà nella prima colonna dell’intervallo limitato.

Se devi Evidenzia Intervalli con Nome dall'elenco dei vincitori che nonsono presenti nell'elenco degli studenti, utilizza la formula opposta:

=ISNA(VLOOKUP($C3,Sheet2!$B$2:$C$24,1,FALSE))

3. Nella finestra di dialogo Imposta formato cella, vai alla scheda Riempimento, scegli il tuo colore di sfondo preferito, quindi fai clic su OK > OK per completare.

Dopo aver applicato la regola, tutti i nomi che soddisfano i criteri di corrispondenza o mancata corrispondenza verranno evidenziati automaticamente, rendendo molto più efficiente il controllo degli errori, la convalida dei partecipanti e la riconciliazione degli elenchi.

Suggerimento: se ottieni risultati inaspettati, ricontrolla gli intervalli di ricerca per verificare la presenza di spazi aggiuntivi o valori non uniformi che potrebbero interferire con la logica di corrispondenza esatta di VLOOKUP. In caso di necessità, considera l'utilizzo della funzione ANNULLA.SPAZI in colonne ausiliarie per standardizzare i tuoi dati.

Se hai installato Kutools per Excel, ottieni maggiore flessibilità e facilità nel confrontare elenchi e applicare la formattazione condizionale. La funzionalità Seleziona celle uguali/diverse ti permette di evidenziare valori corrispondenti o non corrispondenti con pochi clic, riducendo al minimo il rischio di errori comuni legati all’inserimento manuale di formule.

Kutools per Excel include oltre 300 strumenti pratici per Excel, offrendo un supporto avanzato per il confronto e la formattazione dei dati. È la soluzione ideale per chi lavora regolarmente con elenchi estesi o set di dati diversificati. Prova gratuitamente tutte le funzionalità per 60 giorni con nessuna carta di credito richiesta.

1. Fai clic su Kutools > Seleziona > Seleziona celle uguali/diverse per avviare lo strumento.

2. Nella finestra di dialogo Seleziona celle uguali/diverse, configura le impostazioni come segue:

1. Nella casella Trova valori in, seleziona la colonna Nome dell'elenco dei vincitori.
2. Nella casella In base a, seleziona la colonna Nome dell'elenco degli studenti.
3. Se i tuoi dati contengono intestazioni, puoi selezionare l'opzione Includi titoli in base alle tue esigenze.
4. In Basato su, scegli Ogni riga per un confronto riga per riga.
5. Nella sezione Trova, seleziona Valori uguali oppure Valore diverso a seconda che tu voglia evidenziare corrispondenze o discrepanze.
6. Seleziona Colore di sfondo e scegli il colore desiderato per l'evidenziazione.
7. Se desideri evidenziare l’intera riga, seleziona Seleziona Intera Riga.

3. Fare clic su OK per eseguire l'operazione. Lo strumento evidenzierà e selezionerà immediatamente le righe con valori corrispondenti (o diversi). Inoltre, una finestra di dialogo mostra il conteggio delle righe selezionate, consentendo di valutare rapidamente i risultati del confronto.

Questo metodo è particolarmente vantaggioso quando si lavora con elenchi lunghi, poiché elimina il lavoro manuale di scrivere e risolvere formule. Inoltre, il feedback visivo e la finestra di riepilogo riducono congetture ed errori potenziali.