Come confrontare due colonne in Excel (per corrispondenze e differenze)
Guarda il video – Confronta due colonne in Excel per corrispondenze e differenze
La domanda che ricevo spesso è – ‘come confrontare due colonne in Excel?
Questo può essere fatto in molti modi diversi, e il metodo da usare dipenderà dalla struttura dei dati e da ciò che l’utente vuole da essa.
Per esempio, potresti voler confrontare due colonne e trovare o evidenziare tutti i punti dati corrispondenti (che sono in entrambe le colonne), o solo le differenze (dove un punto dati è in una colonna e non nell’altra), ecc.
Siccome mi viene chiesto così spesso, ho deciso di scrivere questo enorme tutorial con l’intento di coprire la maggior parte (se non tutti) gli scenari possibili.
Se lo trovate utile, passatelo ad altri utenti di Excel.
Questo tutorial copre:
Nota che le tecniche per confrontare le colonne mostrate in questo tutorial non sono le uniche.
In base al tuo set di dati, potresti aver bisogno di cambiare o regolare il metodo. Tuttavia, i principi di base rimarrebbero gli stessi.
Se pensate che ci sia qualcosa che può essere aggiunto a questo tutorial, fatemelo sapere nella sezione commenti
Confronta due colonne per la corrispondenza esatta delle righe
Questa è la forma più semplice di confronto. In questo caso, è necessario fare un confronto riga per riga e identificare quali righe hanno gli stessi dati e quali no.
Esempio: Confrontare le celle nella stessa riga
Di seguito è riportato un insieme di dati in cui ho bisogno di controllare se il nome nella colonna A è lo stesso nella colonna B o no.
Se c’è una corrispondenza, ho bisogno che il risultato sia “VERO”, e se non c’è corrispondenza, allora ho bisogno che il risultato sia “FALSO”.
La formula seguente farebbe questo:
=A2=B2
Esempio: Confrontare le celle nella stessa riga (usando la formula IF)
Se volete ottenere un risultato più descrittivo, potete usare una semplice formula IF per restituire “Match” quando i nomi sono uguali e “Mismatch” quando i nomi sono diversi.
=IF(A2=B2,"Match","Mismatch")
Nota: Nel caso tu voglia rendere il confronto case sensitive, usa la seguente formula IF:
=IF(EXACT(A2,B2),"Match","Mismatch")
Con la formula precedente, ‘IBM’ e ‘ibm’ sarebbero considerati due nomi diversi e la formula precedente restituirebbe ‘Mismatch’.
Esempio: Evidenziare le righe con dati corrispondenti
Se vuoi evidenziare le righe che hanno dati corrispondenti (invece di ottenere il risultato in una colonna separata), puoi farlo usando la Formattazione Condizionale.
Questi sono i passi per farlo:
- Seleziona l’intero dataset.
- Fate clic sulla scheda ‘Home’.
- Nel gruppo Stili, cliccate sull’opzione ‘Formattazione condizionale’.
- Dalla tendina, cliccate su ‘Nuova regola’.
- Nella finestra di dialogo ‘Nuova regola di formattazione’, cliccate su ‘Usa una formula per determinare quali celle formattare’.
- Nel campo formula, inserisci la formula: =$A1=$B1
- Clicca sul pulsante Formato e specifica il formato che vuoi applicare alle celle corrispondenti.
- Clicca OK.
Questo evidenzierà tutte le celle dove i nomi sono uguali in ogni riga.
Confrontare due colonne ed evidenziare le corrispondenze
Se volete confrontare due colonne ed evidenziare i dati corrispondenti, potete usare la funzionalità di duplicazione nella formattazione condizionale.
Nota che questo è diverso da quello che abbiamo visto quando abbiamo confrontato ogni riga. In questo caso, non faremo un confronto riga per riga.
Esempio: Confrontare due colonne ed evidenziare i dati corrispondenti
Spesso, avrete insiemi di dati dove ci sono delle corrispondenze, ma queste potrebbero non essere nella stessa riga.
Qualcosa come mostrato qui sotto:
Nota che la lista nella colonna A è più grande di quella in B. Inoltre alcuni nomi sono presenti in entrambe le liste, ma non nella stessa riga (come IBM, Adobe, Walmart).
Se volete evidenziare tutti i nomi delle aziende corrispondenti, potete farlo usando la formattazione condizionale.
Questi sono i passi per farlo:
- Selezionate l’intero set di dati.
- Fate clic sulla scheda Home.
- Nel gruppo Stili, cliccate sull’opzione ‘Formattazione condizionale’.
- Passa il cursore sull’opzione Evidenzia regole cella.
- Fate clic su Valori duplicati.
- Nella finestra di dialogo Valori duplicati, assicuratevi che sia selezionato ‘Duplica’.
- Specificate la formattazione.
- Fate clic su OK.
I passi precedenti vi daranno il risultato come mostrato sotto.
Nota: La regola di duplicazione della formattazione condizionale non è sensibile alle maiuscole. Quindi ‘Apple’ e ‘apple’ sono considerati la stessa cosa e verrebbero evidenziati come duplicati.
Esempio: Confrontare due colonne ed evidenziare i dati non corrispondenti
Nel caso in cui vogliate evidenziare i nomi che sono presenti in una lista e non nell’altra, potete usare la formattazione condizionale anche per questo.
- Selezionate l’intera serie di dati.
- Fate clic sulla scheda Home.
- Nel gruppo Stili, fate clic sull’opzione ‘Formattazione condizionale’.
- Passa il cursore sull’opzione Highlight Cell Rules.
- Clicca su Duplicate Values.
- Nella finestra di dialogo Duplicate Values, assicurati che sia selezionato ‘Unique’.
- Specifica la formattazione.
- Fai clic su OK.
Questo ti darà il risultato come mostrato sotto. Evidenzia tutte le celle che hanno un nome che non è presente nell’altro elenco.
Confrontare due colonne e trovare i punti dati mancanti
Se volete identificare se un punto dati di un elenco è presente nell’altro elenco, dovete usare le formule di ricerca.
Supponiamo di avere un set di dati come mostrato di seguito e di voler identificare le aziende che sono presenti nella colonna A ma non nella colonna B,
Per fare questo, posso usare la seguente formula VLOOKUP.
=ISERROR(VLOOKUP(A2,$B:$B,1,0))
Questa formula usa la funzione VLOOKUP per controllare se un nome di azienda in A è presente o meno nella colonna B. Se è presente, restituirà quel nome dalla colonna B, altrimenti restituirà un errore #N/A.
Questi nomi che restituiscono l’errore #N/A sono quelli che mancano nella colonna B.
La funzioneISERROR restituirà TRUE se il risultato di VLOOKUP è un errore e FALSE se non è un errore.
Se vuoi ottenere una lista di tutti i nomi dove non c’è corrispondenza, puoi filtrare la colonna dei risultati per ottenere tutte le celle con TRUE.
Puoi anche usare la funzione MATCH per fare lo stesso;
=NOT(ISNUMBER(MATCH(A2,$B:$B,0)))
Nota: Personalmente, preferisco usare la funzione Match (o la combinazione di INDEX/MATCH) invece di VLOOKUP. La trovo più flessibile e potente. Puoi leggere la differenza tra Vlookup e Index/Match qui.
Confrontare due colonne ed estrarre i dati corrispondenti
Se hai due insiemi di dati e vuoi confrontare le voci di una lista con l’altra e recuperare i dati corrispondenti, devi usare le formule di lookup.
Esempio: Estrarre i dati corrispondenti (esatti)
Per esempio, nella seguente lista, voglio recuperare il valore di valutazione del mercato per la colonna 2. Per fare questo, ho bisogno di cercare quel valore nella colonna 1 e poi recuperare il corrispondente valore di valutazione di mercato.
Di seguito la formula che farà questo:
=VLOOKUP(D2,$A:$B,2,0)
o
=INDEX($A:$B,MATCH(D2,$A:$A,0),2)
Esempio: Estrarre i dati corrispondenti (parziale)
Nel caso in cui tu abbia un set di dati dove c’è una piccola differenza nei nomi delle due colonne, usare le formule di ricerca mostrate sopra non funzionerà.
Queste formule di ricerca hanno bisogno di una corrispondenza esatta per dare il giusto risultato. C’è un’opzione di corrispondenza approssimativa nella funzione VLOOKUP o MATCH, ma non può essere usata qui.
Supponiamo che abbiate l’insieme di dati come mostrato qui sotto. Notate che ci sono nomi che non sono completi nella colonna 2 (come JPMorgan invece di JPMorgan Chase e Exxon invece di ExxonMobil).
In tal caso, potete usare una ricerca parziale usando caratteri jolly.
La formula seguente darà il risultato giusto in questo caso:
=VLOOKUP("*"&D2&"*",$A:$B,2,0)
o
=INDEX($A:$B,MATCH("*"&D2&"*",$A:$A,0),2)
Nell’esempio precedente, l’asterisco (*) è un carattere jolly che può rappresentare qualsiasi numero di caratteri. Quando il valore di ricerca è affiancato da esso su entrambi i lati, qualsiasi valore nella colonna 1 che contiene il valore di ricerca nella colonna 2 sarebbe considerato come una corrispondenza.
Per esempio, *Exxon* sarebbe una corrispondenza per ExxonMobil (poiché * può rappresentare qualsiasi numero di caratteri).
Ti possono piacere anche i seguenti suggerimenti di Excel & Tutorial:
- Come confrontare due fogli Excel (per le differenze)
- Come evidenziare le celle vuote in Excel.
- Evidenzia ogni altra riga in Excel.
- Excel Filtro avanzato: Una guida completa con esempi.
- Evidenzia le righe in base al valore di una cella in Excel.