Funzione excel per confrontare due colonne

Confrontare le colonne in Excel è qualcosa che tutti facciamo di tanto in tanto. Microsoft Excel offre una serie di opzioni per confrontare e abbinare i dati, ma la maggior parte di esse si concentra sulla ricerca in una colonna. In questo tutorial, esploreremo diverse tecniche per confrontare due colonne in Excel e trovare corrispondenze e differenze tra di loro.

Come confrontare 2 colonne in Excel riga per riga

Quando si esegue l'analisi dei dati in Excel, una delle attività più frequenti è il confronto dei dati in ogni singola riga. Questa attività può essere eseguita utilizzando la funzione SE, come illustrato negli esempi seguenti.

Esempio 1. Confrontare due colonne per corrispondenze o differenze nella stessa riga

Per confrontare due colonne in Excel riga per riga, scrivere una formula SE normale che confronta le prime due celle. Inserisci la formula in un altro nella stessa riga, quindi copiarla in altre celle trascinando il quadratino di riempimento (un quadratino nell'angolo in basso a destra della cella selezionata). Mentre si esegue questa operazione, il cursore assume la forma del segno più:

Formula per le corrispondenze

Per trovare le celle all'interno della stessa riga con lo stesso contenuto, A2 e B2 in questo esempio, la formula è la seguente:

Formula per le differenze

Per trovare le celle nella stessa riga con valori diversi, è sufficiente sostituire il segno di uguale con il segno di non uguaglianza (<>):

Corrispondenze e differenze

E, naturalmente, nulla ti impedisce di trovare sia corrispondenze che differenze con una singola formula:

Oppure

Il risultato potrebbe essere simile a questo:

Come vedi, la formula gestisce ugualmente bene numeri , date , orari e stringhe di testo.

Esempio 2. Confronta due elenchi per corrispondenze con distinzione tra maiuscole e minuscole nella stessa riga

Come probabilmente avrai notato, le formule dell'esempio precedente ignorano le maiuscole e minuscole quando si confrontano i valori di testo, come nella riga 10 nello screenshot sopra. Se si desidera trovare corrispondenze con distinzione tra maiuscole e minuscole tra 2 colonne in ogni riga, utilizzare la funzione ESATTO:

Per trovare differenze con distinzione tra maiuscole e minuscole nella stessa riga, inserire il testo corrispondente ("Unico" in questo esempio) nel 3^° argomento della funzione SE, ad esempio:

Confronta più colonne per corrispondenze nella stessa riga

Nei fogli di lavoro Excel, è possibile confrontare più colonne in base ai seguenti criteri:

• Trovare righe con gli stessi valori in tutte le colonne (Esempio 1) Trovare
• righe con gli stessi valori in 2 colonne qualsiasi (Esempio 2)

Esempio 1. Trova corrispondenze in tutte le celle all'interno della stessa riga

Se la tua tabella ha tre o più colonne e vuoi trovare righe che hanno gli stessi valori in tutte le celle, una formula SE con un'istruzione AND funzionerà a meraviglia:

Se la tua tabella ha molte colonne, una soluzione più elegante sarebbe utilizzare la funzione CONTA.SE:

Dove 5 è il numero di colonne che si stanno confrontando.

Esempio 2. Trovare corrispondenze in due celle qualsiasi della stessa riga

Se si sta cercando un modo per confrontare le colonne di due o più celle con gli stessi valori all'interno della stessa riga, utilizzare una formula SE con un'istruzione OR:

Nel caso in cui siano presenti molte colonne da confrontare, l'istruzione OR potrebbe aumentare di dimensioni. In questo caso, una soluzione migliore sarebbe quella di sommare diverse funzioni CONTA.SE. Il primo COUNTIF conta quante colonne hanno lo stesso valore come nella 1a ^colonna, il secondo COUNTIF conta quante delle colonne rimanenti sono uguali alla 2a ^{colonna e} così via. Se il conteggio è 0, la formula restituisce "Unico", "Corrispondenza" in caso contrario. Ad esempio:

Come confrontare due colonne in Excel per corrispondenze e differenze

Supponiamo di avere 2 elenchi di dati in Excel e di voler trovare tutti i valori (numeri, date o stringhe di testo) che si trovano nella colonna A ma non nella colonna B.

Per questo, è possibile incorporare la funzione COUNTIF($B:$B, $A 2)=0 nel test logico di IF e verificare se restituisce zero (nessuna corrispondenza trovata) o qualsiasi altro numero (at almeno 1 corrispondenza trovata).

Ad esempio, la seguente formula SE/CONTA.SE cerca il valore nella cella A2 nell'intera colonna B. Se non viene trovata alcuna corrispondenza, la formula restituisce "Nessuna corrispondenza in B", altrimenti una stringa vuota:

Lo stesso risultato può essere ottenuto utilizzando una formula SE con le funzioni VAL.ERRORE e CONFRONTA incorporate:

In alternativa, utilizzando la seguente formula di matrice (ricordarsi di premere CTRL + MAIUSC + INVIO per immetterla correttamente):

Se si desidera che una singola formula identifichi sia le corrispondenze (duplicati) che le differenze (valori univoci), inserire del testo per le corrispondenze tra virgolette doppie vuote ("") in una qualsiasi delle formule precedenti. Ad esempio:

Come confrontare due elenchi in Excel ed estrarre le corrispondenze

A volte potrebbe essere necessario non solo abbinare due colonne in due tabelle diverse, ma anche estrarre le voci corrispondenti dalla tabella di ricerca. Microsoft Excel fornisce una funzione speciale per questo: la funzione VLOOKUP. In alternativa, puoi utilizzare una formula INDEX MATCH più potente e versatile. Gli utenti di Excel 2021 ed Excel 365, può eseguire l'attività con la funzione XLOOKUP.

Ad esempio, le formule seguenti confrontano i nomi dei prodotti nelle colonne D con i nomi nella colonna A ed estraggono una cifra di vendita corrispondente dalla colonna B se viene trovata una corrispondenza, altrimenti viene restituito l'errore #N/D.

Per ulteriori informazioni, vedere Come confrontare due colonne utilizzando CERCA.VERT.

Se non ti senti molto a tuo agio con le formule, puoi portare a termine il lavoro utilizzando una soluzione rapida e intuitiva: la procedura guidata di unione delle tabelle.

Confrontare due elenchi ed evidenziare corrispondenze e differenze

Quando si confrontano le colonne in Excel, è possibile "visualizzare" gli elementi presenti in una colonna ma mancanti nell'altra. È possibile ombreggiare tali celle in qualsiasi colore di propria scelta utilizzando la funzione di formattazione condizionale di Excel e gli esempi seguenti illustrano i passaggi dettagliati.

Esempio 1. Evidenzia le corrispondenze e le differenze in per

confrontare due colonne ed Excel ed evidenziare le celle della colonna A che hanno voci identiche nella colonna B nella stessa riga, procedere come segue:

• Selezionare le celle che si desidera evidenziare (è possibile selezionare le celle all'interno di una colonna o in più colonne se si desidera colorare intere righe).
• Fare clic su Formattazione condizionale > Nuova regola... > Usa una formula per determinare quali celle formattare .
• Crea una regola con una formula semplice come (supponendo che la riga 2 sia la prima riga con i dati, esclusa l'intestazione di colonna). Si prega di ricontrollare di utilizzare un riferimento di riga relativo (senza il segno $) come nella formula sopra.

Per evidenziare le differenze tra le colonne A e B, crea una regola con questa formula:

Se non hai familiarità con la formattazione condizionale di Excel, consulta Come creare una regola di formattazione condizionale basata su formule per istruzioni dettagliate.

Esempio 2. Ogni

volta che si confrontano due elenchi in Excel, è possibile evidenziare 3 tipi di elementi:

• Elementi presenti solo nel primo elenco (univoci)
• Elementi presenti solo nel secondo elenco (univoci)
• Elementi presenti in entrambi gli elenchi (duplicati) illustrati nell'esempio successivo
.

Questo esempio illustra come colorare gli elementi presenti in un solo elenco.

Supponiamo che la tua Lista 1 sia nella colonna A (A2:A6) e la Lista 2 nella colonna C (C2:C5). Le regole di formattazione condizionale vengono create con le seguenti formule:

Evidenzia valori univoci nell'elenco 1 (colonna A):

Evidenzia valori univoci nell'elenco 2 (colonna C):

E ottieni il seguente risultato:

Esempio 3. Evidenzia le corrispondenze (duplicati) tra 2 colonne

Se hai seguito attentamente l'esempio precedente, non avrai difficoltà regolando le formule CONTA.SE in modo che trovino le corrispondenze anziché le differenze. Tutto quello che devi fare è impostare il conteggio maggiore di zero:

Evidenzia le corrispondenze nell'elenco 1 (colonna A):

Evidenzia le corrispondenze nell'elenco 2 (colonna C):

Evidenzia le differenze di riga e le corrispondenze in più colonne

Quando si confrontano i valori in più colonne riga per riga, il modo più rapido per evidenziare le corrispondenze è creare una regola di formattazione condizionale e il modo più veloce per ombreggiare le differenze è abbracciare il Vai allo speciale , come illustrato negli esempi seguenti.

Esempio 1. Confrontare più colonne ed evidenziare le corrispondenze delle righe

Per evidenziare le righe con valori identici in tutte le colonne , creare una regola di formattazione condizionale basata su una delle seguenti formule:

oppure

Dove A2, B2 e C2 sono le celle più in alto e 3 è il numero di colonne da confrontare.

Naturalmente, né la formula AND né la formula COUNTIF si limitano a confrontare solo 3 colonne, puoi utilizzare formule simili per evidenziare le righe con gli stessi valori in 4, 5, 6 o più colonne.

Esempio 2. Confrontare più colonne ed evidenziare le differenze di riga

Per evidenziare rapidamente le celle con valori diversi in ogni singola riga, è possibile utilizzare la funzione Vai a speciale di Excel.

1. Selezionare l'intervallo di celle che si desidera confrontare. In questo esempio, ho selezionato le celle da A2 a C8.

   Per impostazione predefinita, la cella più in alto dell'intervallo selezionato è la cella attiva e le celle delle altre colonne selezionate nella stessa riga verranno confrontate con tale cella. Come puoi vedere nello screenshot qui sopra, la cella attiva è bianca mentre tutte le altre celle dell'intervallo selezionato sono evidenziate. In questo esempio, la cella attiva è A2, quindi la colonna di confronto è la colonna A.

   Per modificare la colonna di confronto , utilizzare il tasto Tab per spostarsi tra le celle selezionate da sinistra a destra o il tasto Invio per spostarsi dall'alto verso il basso.

   mancia. Per selezionare colonne non adiacenti , selezionare la prima colonna, tenere premuto CTRL e quindi selezionare le altre colonne. La cella attiva si troverà nell'ultima colonna (o nell'ultimo blocco di colonne adiacenti). Per modificare la colonna di confronto, utilizzare il tasto TAB o INVIO come descritto in precedenza.

2. Nella scheda Home, vai al gruppo Modifica e fai clic su Trova e seleziona > Vai a Speciale... Quindi seleziona Differenze di riga e fai clic sul pulsante OK.
3. Le celle i cui valori sono diversi dalla cella di confronto in ogni riga vengono colorate. Se si desidera ombreggiare le celle evidenziate in un certo colore, è sufficiente fare clic sull'icona Colore di riempimento sulla barra multifunzione e selezionare il colore desiderato.

Come confrontare due celle in Excel

In effetti, il confronto di 2 celle è un caso particolare di confronto di due colonne in Excel riga per riga, tranne per il fatto che non è necessario copiare le formule in altre celle della colonna.

Ad esempio, per confrontare le celle A1 e C1, è possibile utilizzare le seguenti formule.

Per le corrispondenze:

Per le differenze:

Per imparare alcuni altri modi per confrontare le celle in Excel, si prega di vedere:

Modo senza formule per confrontare due colonne / elenchi in Excel

Ora che conosci le offerte di Excel per il confronto e la corrispondenza delle colonne, lascia che ti mostri la nostra soluzione per questo compito. Questo strumento si chiama Compare Two Tables ed è incluso nella nostra Ultimate Suite.

Il componente aggiuntivo è in grado di confrontare due tabelle o elenchi in base a un numero qualsiasi di colonne ed entrambi identificano corrispondenze/differenze (come abbiamo fatto con le formule) e di evidenziarle (come abbiamo fatto con la formattazione condizionale).

Ai fini di questo articolo, Confrontando i 2 elenchi seguenti per trovare i valori comuni presenti in entrambi.

Per confrontare due elenchi, ecco i passaggi che devi seguire:

1. Inizia facendo clic sul pulsante Confronta tabelle nella scheda Dati Ablebits.
2. Seleziona la prima colonna/elenco e fai clic su Avanti . In termini di componente aggiuntivo, questa è la tua Tabella 1.
3. Selezionate la seconda colonna/elenco e fate clic su Avanti . Per quanto riguarda il componente aggiuntivo, si tratta della tabella 2 e può risiedere nello stesso foglio di lavoro o in un foglio di lavoro diverso o anche in un'altra cartella di lavoro.
4. Scegli il tipo di dati da cercare:
   • Valori duplicati (corrispondenze) - gli elementi che esistono in entrambi gli elenchi.
   • Valori univoci (differenze) - gli elementi che sono presenti nell'elenco 1, ma non nell'elenco 2.

   Poiché il nostro obiettivo è trovare corrispondenze, selezioniamo la prima opzione e facciamo clic su Avanti .

5. Questo è il passaggio chiave in cui si selezionano le colonne per il confronto . Nel nostro caso, la scelta è ovvia in quanto stiamo confrontando solo 2 colonne: 2000 vincitori contro 2021 vincitori . Nelle tabelle più grandi, è possibile selezionare diverse coppie di colonne in base alle quali effettuare il confronto.
6. Nel passaggio finale, scegli come gestire gli elementi trovati e fai clic su Fine .

   Qui sono disponibili alcune opzioni diverse. Per i nostri scopi, questi due sono molto utili:

   • Evidenzia con colore : sfuma, corrisponde o differisce nel colore selezionato (come fa la formattazione condizionale di Excel).
   • Identifica nella colonna Stato : inserisce la colonna Stato con le etichette "Duplicato" o "Unico" (come fanno le formule IF).

Per questo esempio, ho deciso di evidenziare i duplicati nel seguente colore:

E in un attimo, ho ottenuto il seguente risultato:

con la colonna Stato, il risultato sarebbe il seguente:

Questo è il modo in cui si confrontano le colonne in Excel per le corrispondenze (duplicati) e le differenze (valori univoci). Se sei interessato a provare questo strumento, puoi scaricare una versione di valutazione utilizzando il link sottostante.

Ti ringrazio per aver letto e ti incoraggio a dare un'occhiata ad altri tutorial utili che abbiamo :)

Download disponibili Confronta

elenchi Excel - esempi (file .xlsx)
Ultimate Suite - versione di prova (file .exe)

Potrebbe interessarti anche