Questo articolo spiega come utilizzare la formattazione condizionale per confrontare tutte le celle su due elenchi separati in un foglio di calcolo Excel e contrassegnare le celle che appaiono su entrambi gli elenchi. Questa funzionalità è disponibile solo nella versione desktop di Excel e l'app per dispositivi mobili non la supporta.
Passi
Metodo 1 di 2: utilizzo della formula CountIf
Passo 1. Apri il foglio di calcolo Excel che desideri modificare
Trova il file del foglio di calcolo con gli elenchi che desideri confrontare e fai doppio clic sul file per aprirlo in Microsoft Excel.
Passaggio 2. Seleziona il tuo primo elenco
Fare clic sulla prima cella del primo elenco e trascinare il mouse fino all'ultima cella dell'elenco per selezionare l'intero intervallo di dati.
Passaggio 3. Fare clic sulla scheda Formule sulla barra degli strumenti
Puoi trovare questa scheda sopra la barra degli strumenti nella parte superiore del foglio di lavoro. Si aprirà gli strumenti per le formule sulla barra degli strumenti.
Passaggio 4. Fare clic su Definisci nome sulla barra degli strumenti
Puoi trovare questa opzione al centro della barra multifunzione "Formule". Si aprirà una nuova finestra pop-up e ti permetterà di dare un nome alla tua lista.
Passaggio 5. Digitare List1 nel campo Nome
Fare clic sul campo di testo nella parte superiore della finestra a comparsa e immettere qui un nome di elenco.
- Successivamente puoi utilizzare questo nome per inserire la tua lista nella formula di confronto.
- In alternativa, puoi dare alla tua lista un nome diverso qui. Ad esempio, se si tratta di un elenco di località, è possibile denominarlo "locations1" o "locationList".
Passaggio 6. Fare clic su OK nella finestra a comparsa
Questo confermerà la tua azione e nominerà la tua lista.
Passaggio 7. Assegna un nome alla tua seconda lista come List2
Segui gli stessi passaggi del primo elenco e dai un nome al secondo elenco. Ciò ti consentirà di utilizzare rapidamente questo secondo elenco nella formula di confronto in un secondo momento.
Puoi dare alla lista il nome che vuoi. Assicurati di ricordare o annotare qui il nome che dai a ciascuna delle tue liste
Passaggio 8. Seleziona il tuo primo elenco
Fare clic sulla prima cella del primo elenco e trascinare verso il basso per selezionare l'intero intervallo di dati.
Assicurati che il tuo primo elenco sia selezionato prima di iniziare a configurare la formattazione condizionale
Passaggio 9. Fare clic sulla scheda Home sulla barra degli strumenti
Questa è la prima scheda nell'angolo in alto a sinistra della barra degli strumenti. Si aprirà gli strumenti di base del foglio di calcolo sulla barra degli strumenti.
Passaggio 10. Fare clic su Formattazione condizionale sulla barra degli strumenti
Questa opzione ha l'aspetto di una minuscola icona di un foglio di calcolo con alcune celle evidenziate in rosso e blu. Si aprirà un menu a discesa con tutte le opzioni di formattazione.
Passaggio 11. Fare clic su Nuova regola nel menu a discesa
Si aprirà una nuova finestra pop-up e ti consentirà di impostare manualmente una nuova regola di formattazione per l'intervallo selezionato.
Passaggio 12. Seleziona l'opzione "Utilizza una formula per determinare quali celle formattare"
Questa opzione ti consentirà di digitare manualmente una formula di formattazione per confrontare i tuoi due elenchi.
- Sopra finestre, lo troverai in fondo all'elenco delle regole nella casella "Seleziona un tipo di regola".
- Sopra Mac, Selezionare Classico nel menu a discesa "Stile" nella parte superiore del popup. Quindi, trova questa opzione nel secondo menu a discesa sotto il menu Stile.
Passaggio 13. Fare clic sul campo della formula nella finestra a comparsa
Puoi inserire qualsiasi formula Excel valida qui per impostare una regola di formattazione condizionale.
Passo 14. Digita =countif(List2, A1)=1 nella barra della formula
Questa formula analizzerà i tuoi due elenchi e segnerà tutte le celle del primo elenco che appaiono anche nel secondo elenco.
- Sostituisci A1 nella formula con il numero della prima cella del tuo primo elenco.
- Ad esempio, se la prima cella del tuo primo elenco è la cella D5, la tua formula sarà simile a =countif(List2, D5)=1.
- Se hai dato un nome diverso al tuo secondo elenco, assicurati di sostituire List2 nella formula con il nome effettivo del tuo elenco.
- In alternativa, cambia la formula in =countif(List2, A1)=0 se vuoi contrassegnare le celle che non compaiono nella seconda lista.
Passaggio 15. Digitare =countif(List1, B1)=1 nella barra della formula (opzionale)
Se vuoi trovare e contrassegnare le celle sul tuo secondo lista che compare anche nella prima lista, usa questa formula al posto della prima.
Sostituisci List1 con il nome del tuo primo elenco e B1 con la prima cella del tuo secondo elenco
Passo 16. Seleziona un formato personalizzato per contrassegnare le celle (opzionale)
Puoi selezionare un colore di riempimento dello sfondo personalizzato e diversi stili di carattere per contrassegnare le celle trovate dalla formula.
- Sopra finestre, clicca il Formato pulsante in basso a destra nella finestra pop-up. Puoi selezionare un colore di sfondo nella scheda "Riempimento" e stili di carattere nella scheda "Carattere".
- Sopra Mac, seleziona un formato predefinito dal menu a discesa "Formato con" in basso. Puoi anche selezionare formato personalizzato qui per selezionare manualmente un riempimento dello sfondo e gli stili dei caratteri.
Passaggio 17. Fare clic su OK nella finestra a comparsa
Questo confermerà e applicherà la tua formula di confronto. Tutte le celle del tuo primo elenco che appaiono anche nel secondo elenco saranno contrassegnate con il colore e il carattere selezionati.
- Ad esempio, se selezioni un riempimento rosso chiaro con testo rosso scuro, tutte le celle ricorrenti diventeranno di questo colore nel tuo primo elenco.
- Se utilizzi la seconda formula sopra, la formattazione condizionale contrassegnerà le celle ricorrenti nel secondo elenco anziché nel primo.
Metodo 2 di 2: utilizzo della formula VLookup
Passaggio 1. Apri il foglio di calcolo Excel
Trova il file Excel con gli elenchi che desideri confrontare e fai doppio clic sul nome del file o sull'icona per aprire il foglio di calcolo in Microsoft Excel.
Passaggio 2. Fare clic sulla cella vuota accanto al primo elemento del secondo elenco
Trova il tuo secondo elenco nel foglio di calcolo e fai clic sulla cella vuota accanto al primo elemento dell'elenco in alto.
- Puoi inserire la tua formula VLookup qui.
- In alternativa, puoi selezionare qualsiasi cella vuota sul foglio di calcolo. Questa cella ti renderà solo più comodo vedere il tuo confronto accanto al tuo secondo elenco.
Passo 3. Digita =vlookup(nella cella vuota
La formula VLookup ti consentirà di confrontare tutti gli elementi su due elenchi separati e vedere se un valore è una ripetizione o un nuovo valore.
Non chiudere la parentesi della formula finché la formula non è completa
Passaggio 4. Seleziona il primo elemento nel secondo elenco
Senza chiudere la parentesi della formula, fai clic sul primo elemento del secondo elenco. Questo inserirà la prima cella del tuo secondo elenco nella formula.
Passaggio 5. Digitare una, virgola nella formula
Dopo aver selezionato la prima cella del secondo elenco, digita una virgola nella formula. Successivamente potrai selezionare il tuo intervallo di confronto.
Passaggio 6. Tieni premuto e seleziona l'intero primo elenco
Questo inserirà l'intervallo di celle del tuo primo elenco nella seconda parte della formula VLookup.
Ciò ti consentirà di cercare nel primo elenco l'elemento selezionato dal secondo elenco (il primo elemento in cima al secondo elenco) e tornare se si tratta di una ripetizione o di un nuovo valore
Passaggio 7. Digitare una, virgola nella formula
Questo bloccherà l'intervallo di confronto nella tua formula.
Passaggio 8. Digitare 1 nella formula dopo la virgola
Questo numero rappresenta il numero di indice della colonna. Verrà richiesto alla formula VLookup di cercare la colonna dell'elenco effettivo anziché una colonna diversa accanto ad essa.
Se vuoi che la tua formula restituisca il valore dalla colonna proprio accanto al tuo primo elenco, digita 2 qui
Passaggio 9. Digitare una, virgola nella formula
Questo bloccherà il numero di indice della colonna (1) nella formula VLookup.
Passaggio 10. Digitare FALSE nella formula
Questo cercherà nell'elenco una corrispondenza esatta dell'elemento di ricerca selezionato (il primo elemento in cima al secondo elenco) invece di corrispondenze approssimative.
- Invece di FALSE puoi usare 0, è esattamente lo stesso.
- In alternativa, puoi digitare TRUE o 1 se vuoi cercare una corrispondenza approssimativa.
Passaggio 11. Digitare) alla fine per chiudere la formula
Ora puoi eseguire la tua formula e vedere se l'elemento di ricerca selezionato nel tuo secondo elenco è una ripetizione o un nuovo valore.
Ad esempio, se il tuo secondo elenco inizia da B1 e il tuo primo elenco va dalle celle A1 ad A5, la tua formula sarà simile a =vlookup(B1, $A$1:$A$5, 1, falso).
Passaggio 12. Premi Invio o ⏎ Ritorna sulla tastiera.
Questo eseguirà la formula e cercherà nel tuo primo elenco il primo elemento dal secondo elenco.
- Se questo è un valore ripetuto, vedrai lo stesso valore stampato di nuovo nella cella della formula.
- Se questo è un nuovo valore, vedrai " #N / A" stampato qui.
- Ad esempio, se stai cercando "John" nel primo elenco e ora vedi "John" nella cella della formula, è un valore di ripetizione che viene visualizzato in entrambi gli elenchi. Se vedi "#N/A", è un nuovo valore nel secondo elenco.
Passaggio 13. Seleziona la cella della formula
Dopo aver eseguito la formula e aver visualizzato i risultati per il primo elemento dell'elenco, fai clic sulla cella della formula per selezionarla.
Passaggio 14. Fare clic e trascinare verso il basso il punto verde in basso a destra della cella
Ciò espanderà la cella della formula lungo l'elenco e applicherà la formula a ogni elemento dell'elenco nel secondo elenco.
- In questo modo puoi confrontare ogni elemento del tuo secondo elenco con l'intero primo elenco.
- Questo cercherà il tuo primo elenco per ogni elemento del secondo elenco individualmente e mostrerà il risultato accanto a ciascuna cella separatamente.
- Se vuoi vedere un indicatore diverso per i nuovi valori invece di "#N/A", usa questa formula: =iferror(vlookup(B1, $A$1:$A$5, 1, false), "Nuovo valore"). Questo stamperà "Nuovo valore" per i nuovi valori invece di "#N/A".
