Come confrontare due colonne in Excel e rimuovere i duplicati (evidenziare, colorare, spostare)

La lettura di questo articolo richiederà circa 10 minuti. Nei prossimi 5 minuti, puoi facilmente confrontare due colonne in Excel e scoprire se sono presenti duplicati, eliminarli o evidenziarli a colori. Allora, è giunto il momento!

Excel è un'applicazione molto potente e davvero interessante per la creazione e l'elaborazione di grandi quantità di dati. Se hai diverse cartelle di lavoro con dati (o solo una tabella enorme), probabilmente vorrai confrontare 2 colonne, trovare valori duplicati e quindi fare qualcosa con esse, ad esempio eliminare, evidenziare o cancellare i contenuti . Le colonne possono essere nella stessa tabella, essere adiacenti o non adiacenti, possono trovarsi su 2 fogli diversi o anche in libri diversi.

Immagina di avere 2 colonne con i nomi delle persone: 5 nomi per colonna A e 3 nomi in una colonna B. Devi confrontare i nomi in queste due colonne e trovare i duplicati. Come capisci, si tratta di dati fittizi, presi esclusivamente ad esempio. Nelle tabelle reali, abbiamo a che fare con migliaia o addirittura decine di migliaia di record.

Opzione A: entrambe le colonne sono sullo stesso foglio. Ad esempio, una colonna A e colonna B.

Opzione B: Le colonne sono su fogli diversi. Ad esempio, una colonna A sul foglio Sheet2 e colonna A sul foglio Sheet3.

Excel 2013, 2010 e 2007 hanno uno strumento integrato Rimuovi duplicati (Rimuovi duplicati) ma è impotente in questa situazione in quanto non può confrontare i dati in 2 colonne. Inoltre, può solo rimuovere i duplicati. Non ci sono altre opzioni come l'evidenziazione o la modifica dei colori. E punta!

Successivamente, ti mostrerò i possibili modi per confrontare due colonne in Excel, che ti permetteranno di trovare e rimuovere i record duplicati.

• Confronta 2 colonne e cerca i duplicati usando le formule
  
  • Opzione A: entrambe le colonne sono sullo stesso foglio
  • Opzione B: le colonne si trovano su fogli diversi o in cartelle di lavoro diverse
• Elaborazione dei duplicati trovati
  
  • Mostra solo le righe duplicate nella colonna A
  • Cambia colore o evidenzia i duplicati trovati
  • Rimuovi i duplicati dalla prima colonna

Confronta 2 colonne in Excel e trova voci duplicate usando le formule

Opzione A: entrambe le colonne sono sullo stesso foglio

1. Nella prima cella vuota (nel nostro esempio, questa è la cella C1), scriviamo la seguente formula:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

   

   Nella nostra formula A1 questa è la prima cella della prima colonna che confronteremo. $ B $ 1 и $ B $ 10000 questi sono gli indirizzi della prima e dell'ultima cella della seconda colonna, con cui eseguiremo il confronto. Nota i riferimenti assoluti: le lettere delle colonne e i numeri di riga sono preceduti dal simbolo del dollaro ($). Uso i riferimenti assoluti in modo che gli indirizzi delle celle rimangano gli stessi durante la copia delle formule.

   Se vuoi trovare duplicati in una colonna B, cambia i riferimenti in modo che la formula assomigli a questa:

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

   Invece "Solo" e "duplicare» Puoi scrivere le tue etichette, ad esempio, «Non trovato" e "Trovato“, o lascia solo “duplicare' e inserisci uno spazio al posto del secondo valore. In quest'ultimo caso, le celle per le quali non vengono trovati duplicati rimarranno vuote e, credo, questa rappresentazione dei dati sia più conveniente per ulteriori analisi.

2. Ora copiamo la nostra formula in tutte le celle della colonna C, fino alla riga inferiore, che contiene i dati nella colonna A. Per fare ciò, sposta il puntatore del mouse nell'angolo inferiore destro della cella C1, il puntatore assumerà la forma di un mirino nero, come mostrato nell'immagine seguente:Fare clic e tenere premuto il tasto sinistro del mouse e trascinare il bordo della cornice verso il basso, evidenziando tutte le celle in cui si vuole inserire la formula. Dopo aver selezionato tutte le celle richieste, rilasciare il pulsante del mouse:

   

Mancia: Nelle tabelle di grandi dimensioni, la copia della formula sarà più rapida se utilizzi le scorciatoie da tastiera. Evidenzia una cella C1 e premere Ctrl + C (per copiare la formula negli appunti), quindi fare clic su Ctrl + Maiusc + Fine (per selezionare tutte le celle non vuote nella colonna C) e infine premere Ctrl + V (per inserire la formula in tutte le celle selezionate).

3. Ottimo, ora tutti i valori duplicati sono contrassegnati come "duplicare":

Opzione B: due colonne si trovano su fogli diversi (in cartelle di lavoro diverse)

1. Nella prima cella della prima colonna vuota del foglio di lavoro Sheet2 (nel nostro caso è la colonna B) inserisci la seguente formula:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")

   Qui Sheet3 è il nome del foglio su cui si trova la 2a colonna, e $ A $ 1: $ A $ 10000 sono gli indirizzi di cella dalla prima all'ultima in questa seconda colonna.

2. Copia la formula in tutte le celle di una colonna B (come l'opzione A).
3. Otteniamo questo risultato:

Elaborazione dei duplicati trovati

Ottimo, abbiamo trovato voci nella prima colonna che sono presenti anche nella seconda colonna. Ora dobbiamo fare qualcosa con loro. L'analisi manuale di tutti i record duplicati in una tabella è piuttosto inefficiente e richiede troppo tempo. Ci sono modi migliori.

Mostra solo le righe duplicate nella colonna A

Se le tue colonne non hanno intestazioni, devi aggiungerle. Per fare ciò, posiziona il cursore sul numero che rappresenta la prima riga, e si trasformerà in una freccia nera, come mostrato nella figura seguente:

Fare clic con il tasto destro e selezionare dal menu contestuale Inserimento (Inserire):

Assegna nomi alle colonne, ad esempio "Nome" e "Duplicare?» Quindi aprire la scheda Dati (Dati) e premere Filtro (Filtro):

Dopodiché, fai clic sulla piccola freccia grigia accanto a "Duplicare?« per aprire il menu dei filtri; deseleziona tutti gli elementi in questo elenco tranne duplicaree premere OK.

Questo è tutto, ora vedi solo quegli elementi della colonna А, che sono duplicati nella colonna В. Ci sono solo due di queste celle nella nostra tabella di allenamento, ma, come capisci, in pratica ce ne saranno molte di più.

Per visualizzare nuovamente tutte le righe di una colonna А, fai clic sul simbolo del filtro nella colonna В, che ora appare come un imbuto con una piccola freccia, e seleziona Seleziona tutto (Seleziona tutto). Oppure puoi fare lo stesso attraverso la barra multifunzione facendo clic Dati (Dati) > Seleziona e filtra (Ordina e filtra) > Cancellare (Cancella) come mostrato nello screenshot qui sotto:

Cambia colore o evidenzia i duplicati trovati

Se le note “duplicare"non è sufficiente per i tuoi scopi e desideri contrassegnare le celle duplicate con un colore del carattere, un colore di riempimento o un altro metodo diverso...

In questo caso, filtra i duplicati come mostrato sopra, seleziona tutte le celle filtrate e fai clic Ctrl + 1per aprire la finestra di dialogo formato celle (formato cella). Ad esempio, cambiamo il colore di riempimento delle celle nelle righe con duplicati in giallo brillante. Naturalmente, puoi cambiare il colore di riempimento con lo strumento Riempire scheda (Colore riempimento). Casa (Home) ma vantaggio della finestra di dialogo formato celle (Cell Format) in quanto è possibile configurare tutte le opzioni di formattazione contemporaneamente.

Ora sicuramente non perderai nessuna cella con duplicati:

Rimozione dei valori duplicati dalla prima colonna

Filtra la tabella in modo che vengano visualizzate solo le celle con valori duplicati e seleziona quelle celle.

Se le 2 colonne che stai confrontando sono su fogli diversi, ovvero in tabelle diverse, fare clic con il pulsante destro del mouse sull'intervallo selezionato e selezionare Elimina riga (Rimuovi riga):

Rassegna Stampa OKquando Excel ti chiede di confermare che desideri davvero eliminare l'intera riga del foglio e quindi cancellare il filtro. Come puoi vedere, rimangono solo le righe con valori univoci:

Se 2 colonne sono sullo stesso foglio, vicini (adiacenti) o non vicini (non adiacenti), quindi il processo di rimozione dei duplicati sarà un po' più complicato. Non possiamo rimuovere l'intera riga con valori duplicati, poiché ciò rimuoverà anche le celle dalla seconda colonna. Quindi per lasciare solo voci univoche in una colonna А, Fai questo:

1. Filtra la tabella per mostrare solo i valori duplicati e seleziona quelle celle. Fare clic con il tasto destro su di essi e selezionare dal menu contestuale Contenuti chiari (contenuti chiari).
2. Pulire il filtro.
3. Seleziona tutte le celle in una colonna А, a partire dalla cella A1 fino in fondo contenente i dati.
4. Clicca su Dati (Dati) e premere Ordina dalla A alla Z (Ordina dalla A alla Z). Nella finestra di dialogo che si apre, seleziona Continua con la selezione corrente (Ordina all'interno della selezione specificata) e fare clic sul pulsante Nero (Ordinamento):
5. Elimina la colonna con la formula, non ti servirà più, d'ora in poi hai solo valori univoci.
6. Questo è tutto, ora la colonna А contiene solo dati univoci che non sono nella colonna В:

Come puoi vedere, rimuovere i duplicati da due colonne in Excel usando le formule non è così difficile.