Guarda il video - Confronta due colonne in Excel per corrispondenze e differenze
L'unica query che ottengo molto è: "come confrontare due colonne in Excel?".
Questo può essere fatto in molti modi diversi e il metodo da utilizzare dipenderà dalla struttura dei dati e da ciò che l'utente vuole da essa.
Ad 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.
Dato che mi è stato chiesto così tanto, ho deciso di scrivere questo enorme tutorial con l'intento di coprire la maggior parte (se non tutti) gli scenari possibili.
Se lo trovi utile, passalo ad altri utenti di Excel.
Nota che le tecniche per confrontare le colonne mostrate in questo tutorial non sono le uniche.
In base al tuo set di dati, potresti dover modificare o modificare il metodo. Tuttavia, i principi di base rimarrebbero gli stessi.
Se pensi che ci sia qualcosa che può essere aggiunto a questo tutorial, fammelo sapere nella sezione commenti
Confronta due colonne per la corrispondenza esatta della riga
Questa è la forma più semplice di confronto. In questo caso, è necessario eseguire un confronto riga per riga e identificare quali righe hanno gli stessi dati e quali no.
Esempio: Confronta celle nella stessa riga
Di seguito è riportato un set di dati in cui è necessario verificare se il nome nella colonna A è lo stesso nella colonna B o meno.
Se c'è una corrispondenza, ho bisogno che il risultato sia "VERO", e se non corrisponde, allora ho bisogno che il risultato sia "FALSO".
La formula seguente farebbe questo:
=A2=B2
Esempio: Confronta celle nella stessa riga (usando la formula SE)
Se vuoi ottenere un risultato più descrittivo, puoi utilizzare una semplice formula SE per restituire "Corrispondenza" quando i nomi sono gli stessi e "Non corrispondenza" quando i nomi sono diversi.
=SE(A2=B2,"Corrispondenza","Mancata corrispondenza")
Nota: nel caso in cui si desideri rendere il confronto tra maiuscole e minuscole, utilizzare la seguente formula SE:
=SE(ESATTO(A2,B2);"Corrispondenza","Non corrispondenza")
Con la formula sopra, "IBM" e "ibm" sarebbero considerati due nomi diversi e la formula sopra restituirebbe "Mancata corrispondenza".
Esempio: evidenzia 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.
Ecco i passaggi per farlo:
- Seleziona l'intero set di dati.
- Fare clic sulla scheda "Home".
- Nel gruppo Stili, fai clic sull'opzione "Formattazione condizionale".
- Dal menu a discesa, fai clic su "Nuova regola".
- Nella finestra di dialogo "Nuova regola di formattazione", fai clic su "Utilizza una formula per determinare quali celle formattare".
- Nel campo della formula, inserisci la formula: =$A1=$B1
- Fare clic sul pulsante Formato e specificare il formato che si desidera applicare alle celle corrispondenti.
- Fare clic su OK.
Questo evidenzierà tutte le celle in cui i nomi sono gli stessi in ogni riga.
Confronta due colonne ed evidenzia le corrispondenze
Se desideri confrontare due colonne ed evidenziare i dati corrispondenti, puoi utilizzare la funzionalità di duplicazione nella formattazione condizionale.
Nota che questo è diverso da quello che abbiamo visto confrontando ogni riga. In questo caso, non effettueremo un confronto riga per riga.
Esempio: confrontare due colonne ed evidenziare i dati corrispondenti
Spesso otterrai set di dati in cui sono presenti corrispondenze, ma potrebbero non essere nella stessa riga.
Qualcosa come mostrato di seguito:
Nota che l'elenco nella colonna A è più grande di quello in B. Inoltre, alcuni nomi sono presenti in entrambi gli elenchi, ma non nella stessa riga (come IBM, Adobe, Walmart).
Se desideri evidenziare tutti i nomi di società corrispondenti, puoi farlo utilizzando la formattazione condizionale.
Ecco i passaggi per farlo:
- Seleziona l'intero set di dati.
- Fare clic sulla scheda Home.
- Nel gruppo Stili, fai clic sull'opzione "Formattazione condizionale".
- Passa il cursore sull'opzione Evidenzia regole cella.
- Fare clic su Valori duplicati.
- Nella finestra di dialogo Duplica valori, assicurati che "Duplica" sia selezionato.
- Specificare la formattazione.
- Fare clic su OK.
I passaggi precedenti ti daranno il risultato come mostrato di seguito.
Nota: la regola di duplicazione della formattazione condizionale non fa distinzione tra maiuscole e minuscole. Quindi "Mela" e "Mela" sono considerati uguali e verrebbero evidenziati come duplicati.
Esempio: confrontare due colonne ed evidenziare i dati non corrispondenti
Nel caso in cui desideri evidenziare i nomi che sono presenti in un elenco e non nell'altro, puoi utilizzare la formattazione condizionale anche per questo.
- Seleziona l'intero set di dati.
- Fare clic sulla scheda Home.
- Nel gruppo Stili, fai clic sull'opzione "Formattazione condizionale".
- Passa il cursore sull'opzione Evidenzia regole cella.
- Fare clic su Valori duplicati.
- Nella finestra di dialogo Duplica valori, assicurati che "Unico" sia selezionato.
- Specificare la formattazione.
- Fare clic su OK.
Questo ti darà il risultato come mostrato di seguito. Evidenzia tutte le celle che hanno un nome che non è presente nell'altro elenco.
Confronta due colonne e trova punti dati mancanti
Se si desidera identificare se un punto dati di un elenco è presente nell'altro elenco, è necessario utilizzare le formule di ricerca.
Supponiamo di avere un set di dati come mostrato di seguito e di voler identificare le aziende presenti nella colonna A ma non nella colonna B,
Per fare ciò, posso utilizzare la seguente formula CERCA.VERT.
=VAL.ERRORE(CERCA.VERT(A2,$B$2:$B$10,1,0))
Questa formula utilizza la funzione CERCA.VERT per verificare se il nome di un'azienda in A è presente nella colonna B o meno. 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 funzione ISERROR restituirebbe TRUE se il risultato VLOOKUP è un errore e FALSE se non è un errore.
Se vuoi ottenere un elenco di tutti i nomi in cui non c'è corrispondenza, puoi filtrare la colonna del risultato per ottenere tutte le celle con TRUE.
Puoi anche usare la funzione MATCH per fare lo stesso;
=NON(VAL.NUMERO(CONFRONTA(A2,$B$2:$B$10,0)))
Nota: Personalmente, preferisco usare la funzione Match (o la combinazione di INDICE/MATCH) invece di VLOOKUP. Lo trovo più flessibile e potente. Puoi leggere la differenza tra Vlookup e Index/Match qui.
Confronta due colonne e estrai i dati corrispondenti
Se si dispone di due set di dati e si desidera confrontare gli elementi di un elenco con l'altro e recuperare il punto dati corrispondente, è necessario utilizzare le formule di ricerca.
Esempio: estrarre i dati corrispondenti (esatto)
Ad esempio, nell'elenco seguente, desidero recuperare il valore di valutazione di mercato per la colonna 2. Per fare ciò, devo cercare quel valore nella colonna 1 e quindi recuperare il valore di valutazione di mercato corrispondente.
Di seguito è la formula che farà questo:
=CERCA.VERT(D2,$A$2:$B$14,2,0)
o
=INDICE($A$2:$B$14,CONFRONTA(D2,$A$2:$A$14,0),2)
Esempio: estrarre i dati corrispondenti (parziale)
Nel caso in cui si ottenga un set di dati in cui vi sia una piccola differenza nei nomi nelle due colonne, l'utilizzo delle formule di ricerca sopra mostrate non funzionerà.
Queste formule di ricerca necessitano di una corrispondenza esatta per fornire il risultato corretto. Esiste un'opzione di corrispondenza approssimativa nella funzione CERCA.VERT o CONFRONTA, ma non può essere utilizzata qui.
Supponiamo di avere il set di dati come mostrato di seguito. Nota che ci sono nomi che non sono completi nella Colonna 2 (come JPMorgan invece di JPMorgan Chase ed Exxon invece di ExxonMobil).
In tal caso, è possibile utilizzare una ricerca parziale utilizzando i caratteri jolly.
La seguente formula darà è il risultato giusto in questo caso:
=CERCA.VERT("*"&D2&"*",$A$2:$B$14,2,0)
o
=INDICE($A$2:$B$14,CONFRONTA("*"&D2&"*",$A$2:$A$14,0),2)
Nell'esempio sopra, l'asterisco (*) è un carattere jolly che può rappresentare un numero qualsiasi di caratteri. Quando il valore di ricerca è affiancato su entrambi i lati, qualsiasi valore nella colonna 1 che contiene il valore di ricerca nella colonna 2 verrebbe considerato come una corrispondenza.
Ad esempio, *Exxon* sarebbe una corrispondenza per ExxonMobil (poiché * può rappresentare qualsiasi numero di caratteri).
Potrebbero piacerti anche i seguenti suggerimenti ed esercitazioni su Excel:
- Come confrontare due fogli Excel (per differenze)
- Come evidenziare le celle vuote in Excel
- Evidenzia OGNI altra riga in Excel.
- Filtro avanzato di Excel: una guida completa con esempi.
- Evidenzia le righe in base a un valore di cella in Excel.
