Come confrontare due colonne in Excel (usando VLOOKUP E IF)

Quando lavori con i dati in Excel, prima o poi dovrai confrontare i dati. Questo potrebbe essere il confronto di due colonne o anche dati in diversi fogli / cartelle di lavoro.

In questo tutorial di Excel, ti mostrerò diversi metodi per confrontare due colonne in Excel e cercare corrispondenze o differenze.

Ci sono diversi modi per farlo in Excel e in questo tutorial vi mostrerò alcuni di questi (come il confronto utilizzando la formula VLOOKUP o IF formula o formattazione condizionale).

Quindi cominciamo!

Confronta due colonne (affiancate)

Questo è il tipo di confronto più semplice in cui è necessario confrontare una cella in una colonna con la cella nella stessa riga in un’altra colonna.

Supponiamo di avere un set di dati come mostrato di seguito e si desidera semplicemente verificare se il valore nella colonna A in una cella specifica è lo stesso (o diverso) rispetto al valore nella cella adiacente.

Naturalmente, puoi farlo quando hai un piccolo set di dati quando ne hai uno grande, puoi usare una semplice formula di confronto per farlo. E ricorda, c’è sempre una possibilità di errore umano quando lo fai manualmente.

Quindi lascia che ti mostri un paio di semplici modi per farlo.

Confronta fianco a fianco Usando l’operatore Uguale a segno

Supponiamo di avere il set di dati sottostante e di voler sapere quali righe hanno i dati corrispondenti e quali righe hanno dati diversi.

Di seguito è riportata una semplice formula per confrontare due colonne (affiancate):

=A2=B2

La formula di cui sopra ti darà un VERO se entrambi i valori sono uguali e FALSI nel caso in cui non lo siano.

Ora, se hai bisogno di conoscere tutti i valori che corrispondono, applica semplicemente un filtro e mostra solo tutti i valori VERI. E se vuoi conoscere tutti i valori che sono diversi, filtra tutti i valori che sono FALSI (come mostrato di seguito):

Confronta fianco a fianco utilizzando la funzione IF

Un altro metodo che è possibile utilizzare per confrontare due colonne può essere utilizzando la funzione IF.

Questo è simile al metodo sopra in cui abbiamo usato l’operatore uguale a ( = ), con un ulteriore vantaggio. Quando si utilizza la funzione IF, è possibile scegliere il valore che si desidera ottenere quando ci sono corrispondenze o differenze.

Ad esempio, se c’è una corrispondenza, puoi ottenere il testo “Corrispondenza” o puoi ottenere un valore come 1. Allo stesso modo, quando c’è una mancata corrispondenza, puoi programmare la formula per darti il testo “Mancata corrispondenza” o darti una cella 0 o vuota.

Di seguito è riportata la formula IF che restituisce ‘Match’ quando le due celle hanno il valore della cella e ‘Not a Match’ quando il valore è diverso.

=IF(A2=B2,"Match","Not a Match")

La formula precedente utilizza la stessa condizione per verificare se le due celle (nella stessa riga) hanno dati corrispondenti o meno (A2=B2). Ma poiché stiamo usando la funzione IF, possiamo chiedergli di restituire un testo specifico nel caso in cui la condizione sia Vera o Falsa.

Una volta ottenuti i risultati della formula in una colonna separata, è possibile filtrare rapidamente i dati e ottenere righe con i dati corrispondenti o righe con dati non corrispondenti.

Evidenziare righe con dati corrispondenti (o dati diversi)

Un altro ottimo modo per controllare rapidamente le righe che hanno dati corrispondenti (o hanno dati diversi), è quello di evidenziare queste righe utilizzando la formattazione condizionale.

Puoi fare entrambe le cose: evidenziare le righe che hanno lo stesso valore in una riga e il caso in cui il valore è diverso.

Supponiamo di avere un set di dati come mostrato di seguito e di voler evidenziare tutte le righe in cui il nome è lo stesso.

Di seguito sono riportati i passaggi per utilizzare la formattazione condizionale per evidenziare le righe con dati corrispondenti:

1. Selezionare l’intero set di dati (tranne le intestazioni)
2. fare Clic sulla scheda Home
3. Nel gruppo Stili, fare clic su Formattazione Condizionale
4. Nelle opzioni che appare, fare clic su “Nuova Regola’
5. In ‘la Nuova Regola di Formattazione’ di dialogo fare clic sull’opzione”Utilizza una formula per determinare le celle da formattare’
6. Formato valori in cui questa formula è vera in campo, immettere la formula: =$A2=$B2
7. fare Clic sul pulsante Formato
8. fare Clic su “Riempimento” scheda e selezionare il colore che si desidera evidenziare le righe con lo stesso valore in entrambe le colonne
9. fare Clic su OK

La procedura di cui sopra avrebbe immediatamente evidenziare le righe in cui il nome è lo stesso in entrambe le colonne A e B (nella stessa riga). E nel caso in cui il nome sia diverso, quelle righe non verranno evidenziate.

Nel caso in cui si desideri confrontare due colonne ed evidenziare righe in cui i nomi sono diversi, utilizzare la formula seguente nella finestra di dialogo formattazione condizionale (nel passaggio 6).

=$A2<>$B2

Come funziona?

Quando usiamo la formattazione condizionale con una formula, evidenzia solo le celle in cui la formula è vera.

Quando usiamo $A2=B B2, controllerà ogni cella (in entrambe le colonne) e vedrà se il valore in una riga nella colonna A è uguale a quello nella colonna B o meno.

Nel caso in cui sia una corrispondenza esatta, lo evidenzierà nel colore specificato e, nel caso in cui non corrisponda, non lo farà.

La parte migliore della formattazione condizionale è che non richiede l’uso di una formula in una colonna separata. Inoltre, quando si applica la regola su un set di dati, rimane dinamica. Ciò significa che se si modifica un nome nel set di dati, la formattazione condizionale verrà modificata di conseguenza.

Confronta due colonne Usando VLOOKUP (Trova dati corrispondenti/diversi)

Negli esempi precedenti, ti ho mostrato come confrontare due colonne (o elenchi) quando stiamo solo confrontando le celle affiancate.

In realtà, questo raramente sarà il caso.

Nella maggior parte dei casi, avrai due colonne con dati e dovresti scoprire se un punto dati in una colonna esiste nell’altra colonna o meno.

In questi casi, non è possibile utilizzare un semplice segno uguale o anche una funzione IF.

Hai bisogno di qualcosa di più potente

something qualcosa che è proprio nel vicolo di VLOOKUP!

Lascia che ti mostri due esempi in cui confrontiamo due colonne in Excel usando la funzione VLOOKUP per trovare corrispondenze e differenze.

Confronta due colonne usando VLOOKUP e Trova le corrispondenze

Supponiamo di avere un set di dati come mostrato di seguito in cui abbiamo alcuni nomi nelle colonne A e B.

Se si dispone di scoprire quali sono i nomi che sono nella colonna B, che sono anche in Una colonna, è possibile utilizzare il seguente formula VLOOKUP:

=IFERROR(VLOOKUP(B2,$A:$A,1,0),"No Match")

La formula mette a confronto le due colonne (A e B) e si dà il nome nel caso in cui il nome è nella colonna B e A, e restituisce “No Match” nel caso in cui il nome è nella Colonna B e non in Colonna A.

per impostazione predefinita, la funzione VLOOKUP restituirà un errore #N/d in caso di non trovare una corrispondenza esatta. Quindi, per evitare l’errore, ho avvolto la funzione VLOOKUP in funzione SE.ERRORE, in modo che si dà “Nessuna Corrispondenza” quando il nome non è disponibile nella colonna A.

Si può anche fare il contrario di confronto per verificare se il nome è nella Colonna A e Colonna B. La formula qui di seguito faccio:

=IFERROR(VLOOKUP(A2,$B:$B,1,0),"No Match")

Confrontare Due Colonne Utilizzando VLOOKUP e Trovare le Differenze (Dati Mancanti)

Mentre nell’esempio precedente, abbiamo verificato se i dati in una colonna c’era in un’altra colonna o non.

È inoltre possibile utilizzare lo stesso concetto per confrontare due colonne utilizzando la funzione VLOOKUP e trovare i dati mancanti.

Supponiamo di avere un set di dati, come mostrato qui sotto, dove ci sono alcuni nomi delle colonne a e B.

Se si dispone di scoprire quali sono i nomi che sono nella colonna B che non c’è nella colonna A, è possibile utilizzare il seguente formula VLOOKUP:

=IF(ISERROR(VLOOKUP(B2,$A:$A,1,0)),"Not Available","Available")

La formula precedente verifica il nome nella colonna B, contro tutti i nomi nella Colonna A. Nel caso in cui trovi una corrispondenza esatta, restituirebbe quel nome, e nel caso in cui non trovi e corrisponda esattamente, restituirà l’errore #N/A.

Poiché sono interessato a trovare i nomi mancanti che ci sono nella colonna B e non nella colonna A, ho bisogno di conoscere i nomi che restituiscono l’errore #N/A.

Questo è il motivo per cui ho avvolto la funzione VLOOKUP nelle funzioni IF e ISERROR. L’intera formula fornisce il valore- ” Non disponibile “quando il nome manca nella colonna A e” Disponibile ” quando è presente.

Per conoscere tutti i nomi mancanti, è possibile filtrare la colonna dei risultati in base al valore “Non disponibile”.

È inoltre possibile utilizzare la funzione di corrispondenza qui sotto per ottenere lo stesso risultato:

=IF(ISNUMBER(MATCH(B2,$A:$A,0)),"Available","Not Available")

Query comuni quando si confrontano due colonne

Di seguito sono riportate alcune query comuni che di solito ricevo quando le persone cercano di confrontare i dati in due colonne in Excel.

Q1. Come confrontare più colonne in Excel nella stessa riga per le partite? Contare anche i duplicati totali.

Ans. Abbiamo dato la procedura per confrontare due colonne in Excel per la stessa riga sopra. Ma se vuoi confrontare più colonne in Excel per la stessa riga, vedi l’esempio

=IF(AND(A2=B2, A2=C2),"Full Match", "")

Qui abbiamo confrontato i dati della colonna A, della colonna B e della colonna C. Dopo questo, ho applicato la formula sopra nella colonna D e ottenuto il risultato.

Ora per contare i duplicati, è necessario utilizzare la funzione Countif.

=IF(COUNTIF($A2:$E2, $A2)=5, "Full Match", "")

Q2. Quale operatore si usa per le partite e le differenze?

Ans. Di seguito sono riportati gli operatori da utilizzare:

• Per trovare corrispondenze, usa il segno uguale a ( = )
• Per trovare differenze (disallineamenti), usa il segno non uguale a(<>)

Q3. Come confrontare due tabelle diverse e tirare i dati corrispondenti?

Ans. Per questo, è possibile utilizzare la funzione VLOOKUP o INDEX & funzione MATCH. Per capire questa cosa in un modo migliore prenderemo un esempio.

Qui prenderemo due tabelle e ora vogliamo fare tirare i dati corrispondenti. Nella prima tabella, si dispone di un set di dati e nella seconda tabella, prendere l’elenco dei frutti e quindi utilizzare pull dati corrispondenti in un’altra colonna. Per la corrispondenza di tiro, utilizzare la formula

=INDEX($B:$B,MATCH($D2,$A:$A,0))

Q4. Come rimuovere i duplicati in Excel?

Ans. Per rimuovere i dati duplicati è necessario prima trovare i valori duplicati.

Per trovare il duplicato, è possibile utilizzare vari metodi come formattazione condizionale, Vlookup, Istruzione If e molti altri. Excel ha anche uno strumento integrato in cui è possibile selezionare i dati e rimuovere i duplicati da una colonna o anche più colonne

Q5. Posso vedere che c’è un valore corrispondente in entrambe le colonne. Tuttavia, le formule che hai condiviso sopra non le considerano come corrispondenze esatte. Perché?

Ans: Excel considera qualcosa una corrispondenza esatta quando ogni carattere di una cella è uguale all’altro. C’è un’alta probabilità che nel tuo set di dati ci siano spazi iniziali o finali.

Sebbene questi spazi possano ancora far sembrare i valori uguali a un occhio nudo, per Excel questi sono diversi. Se si dispone di un set di dati di questo tipo, è meglio sbarazzarsi di questi spazi (è possibile utilizzare funzioni di Excel come TRIM per questo).

Q7. Come confrontare due colonne che danno il risultato come TRUE quando i valori interi di tutte le prime colonne non sono inferiori ai valori interi della seconda colonna. Per risolvere questo problema, non ho bisogno di formattazione condizionale, funzione Vlookup, Istruzione If e altre formule. Ho bisogno della formula per risolvere questo problema.

Ans. È possibile utilizzare la formula array per risolvere questo problema.

La sintassi è {=AND (H6:H12>I6:I12)}. Questo ti darà ” True “di conseguenza ogni volta che il valore della colonna H è maggiore del valore nella colonna I altrimenti” False ” sarà il risultato.

Potrebbero piacerti anche i seguenti tutorial di Excel:

• Confronto Due Colonne in Excel (per le corrispondenze e differenze)
• Come Nascondere le Colonne In Base al Valore di una Cella in Excel
• Come si fa a Dividere Una Colonna in Più Colonne in Excel
• Come Selezionare Colonne in Excel (o ogni n-esima Colonna)
• Come Incollare in una Colonna di filtrazione Saltare le Celle Nascoste
• Migliori Libri Excel (che vi farà Excel Pro 2020)

segnala questo annuncio