Come confrontare due fogli Excel per i duplicati: 5 modi rapidi

Quando si lavora con set di dati di grandi dimensioni in Excel, è comune avere più fogli di lavoro contenenti dati simili. Il confronto di questi fogli per i record duplicati può essere una vera sfida, specialmente quando i dati sono sparsi su varie colonne e righe.

I metodi più comuni per trovare i duplicati in due fogli Excel sono:

1. CERCA.VERT, CONTA.SE o ESATTO

2. Formattazione condizionale

3. Domanda di potenza

4. Strumenti esterni e componenti aggiuntivi

5. Controlli visivi per duplicati

Questo articolo illustra dettagliatamente questi cinque metodi per individuare e gestire i duplicati su più fogli di lavoro. Alla fine, vedrai quanto è facile gestire i valori duplicati.

Iniziamo!

Sommario

• Fogli di lavoro e dati di esempio
• 1. Utilizzo delle funzioni VLOOKUP, COUNTIF o EXACT per trovare i duplicati
  • A. Come si utilizza la funzione CERCA.VERT per trovare i duplicati in due fogli?
    • Cosa succede se gestisci cartelle di lavoro diverse?
  • B. Come si utilizza la funzione COUNTIF per trovare i duplicati tra i fogli di lavoro?
  • C. Come si utilizza la funzione EXACT per trovare i duplicati tra i fogli di lavoro?
• 2. Come utilizzare la formattazione condizionale per le righe duplicate
  • Come utilizzare il gestore delle regole di formattazione condizionale
• 3. Come utilizzare Power Query per trovare duplicati tra i fogli di lavoro
• 4. Strumenti e componenti aggiuntivi per identificare i duplicati nei fogli di lavoro
• 5. Come controllare visivamente i duplicati in due fogli
• 3 suggerimenti per preparare i fogli di lavoro di Excel
• Come gestire errori e incoerenze
• Pensieri finali

Fogli di lavoro e dati di esempio

Per seguire gli esempi in questo articolo, crea una nuova cartella di lavoro di Excel con questi dati nella colonna A del primo foglio:

• Mela

• Arancia

• Pera

• Fragola

Inserisci questi dati nella colonna A del secondo foglio:

• Pera

• Fragola

• Mela

• Ananas

I tuoi fogli di lavoro hanno questo aspetto:

Dopo aver impostato i fogli di lavoro, ora possiamo esaminare i diversi modi per trovare i duplicati in due fogli, a partire dalle funzioni VLOOKUP, COUNTIF ed EXACT.

Andiamo!

1. Utilizzo delle funzioni VLOOKUP, COUNTIF o EXACT per trovare i duplicati

Excel ha tre funzioni integrate che possono rendere la ricerca dei duplicati un gioco da ragazzi: VLOOKUP , COUNTIF e EXACT .

Queste funzioni sono progettate per aiutarti a trovare, contare e confrontare i dati all'interno dei tuoi fogli di calcolo, rendendoli strumenti ideali per trovare voci duplicate.

Ogni funzione ha uno scopo unico e capire come utilizzarle può semplificare in modo significativo il processo di analisi dei dati.

Nelle sezioni seguenti, esploreremo come utilizzare le funzioni VLOOKUP, COUNTIF e EXACT per identificare efficacemente i duplicati nei fogli di lavoro di Excel.

A. Come si utilizza la funzione CERCA.VERT per trovare i duplicati in due fogli?

CERCA.VERT è l'acronimo di Ricerca verticale. Utilizzare questa funzione per trovare valori duplicati tra due colonne. Questa è la sintassi:

=CERCA.VERT(valore_ricerca, matrice_tabella, numero_indice_col, [ricerca_intervallo])

• lookup_value : il valore che desideri cercare nella prima colonna di table_array.

• table_array : l'intervallo di celle contenente i dati in cui eseguire la ricerca.

• col_index_num : il numero di colonna nell'array_tabella da cui si desidera restituire il valore.

• range_lookup : facoltativo. È VERO (corrispondenza approssimativa) o FALSO (corrispondenza esatta). L'impostazione predefinita è TRUE.

Per utilizzare la funzione su due fogli di lavoro in un file Excel, è necessario sapere come fare riferimento a un foglio separato in una formula. Per fare ciò, digita il nome del foglio seguito dal punto esclamativo (!) e la cella o l'intervallo di celle.

Ad esempio, fa riferimento alle celle da A2 a A5 nel foglio 2 della stessa cartella di lavoro: Foglio 2!$A$2:$A$5.

Per utilizzare la funzione CERCA.VERT nel foglio di calcolo di esempio, procedi nel seguente modo:

1. Selezionare la cella B2 per visualizzare il primo risultato del confronto.

2. Digita questa formula: =CERCA.VERT(A2,Foglio2!$A$2:$A$5, 1, FALSO).

3. Premere Invio per visualizzare il risultato del confronto.

4. Compila la formula per confrontare i valori per il resto delle righe nel primo foglio.

I risultati saranno simili all'immagine qui sotto:

Puoi riordinare il display mostrando un messaggio intuitivo invece di un errore quando non viene trovato un duplicato.

Ad esempio, questa formula visualizzerà "Sì" o "No" rispettivamente per i valori trovati e non trovati:

=SE(ISNA(CERCA.VERT(A2, Foglio 2!$A$2:$A$5, 1, FALSO)), “No”, “Sì”)

Ci sono altri esempi di questa funzione nel nostro articolo sull'uso di CERCA.VERT per confrontare due colonne .

Cosa succede se gestisci cartelle di lavoro diverse?

Se i fogli di lavoro si trovano in cartelle di lavoro separate, l'utilizzo della funzione è lo stesso con due file Excel. Tuttavia, fare riferimento al secondo foglio di lavoro è un po' più complesso.

Devi:

• Racchiudere il nome della cartella di lavoro di Excel tra parentesi

• Seguire con il nome del foglio di lavoro

• Racchiudere la cartella di lavoro e il foglio di lavoro tra virgolette.

Ad esempio, se le celle si trovano in un foglio denominato Foglio2 in una cartella di lavoro denominata "WB 2.xlsx", il formato sarà simile al seguente:

'[WB 2.xlsx]Foglio2'!$A$2:$A$5

Prima di inserire la formula, chiudi la seconda cartella di lavoro. In caso contrario, riceverai un errore.

B. Come si utilizza la funzione COUNTIF per trovare i duplicati tra i fogli di lavoro?

La funzione COUNTIF in Excel viene utilizzata per contare il numero di celle all'interno di un intervallo specificato che soddisfano determinati criteri.

Per confrontare più fogli, puoi contare il numero di celle nel secondo foglio di lavoro che corrisponde a una cella nel primo foglio di lavoro.

Questa è la sintassi della funzione:

=COUNTIF(intervallo, criteri)

• Intervallo : l'intervallo di celle che vuoi contare in base ai criteri specificati.

• Criteri : la condizione che deve essere soddisfatta affinché una cella possa essere contata.

Per utilizzare la funzione con i dati di esempio, attenersi alla seguente procedura:

1. Selezionare la cella B2 per visualizzare il primo risultato del confronto.

2. Digita questa formula: =COUNTIF(Foglio2!$A$2:$A$5, A2)

3. Premere Invio per visualizzare il risultato del confronto.

4. Compila la formula per confrontare i valori per il resto delle righe nel primo foglio.

La funzione troverà una corrispondenza per alcune celle e nessuna per altre. La cella di confronto visualizza il conteggio. Ecco il risultato dei dati di esempio:

La funzione CONTA.SE può essere utilizzata per altre attività utili come il conteggio delle celle non vuote in Excel .

C. Come si utilizza la funzione EXACT per trovare i duplicati tra i fogli di lavoro?

La funzione ESATTO in Excel può essere utilizzata anche per cercare duplicati all'interno delle stesse celle in due diversi fogli di lavoro di Excel. La sintassi è:

=ESATTO(testo1, testo2)

• text1 è la prima stringa di testo che vuoi confrontare.

• text2 è la seconda stringa di testo che vuoi confrontare.

Segui questi passi:

1. Seleziona la cella B2.

2. Digita la formula =ESATTO(A2, Foglio2!A2)

3. Premere Invio per visualizzare il risultato del confronto. La formula restituirà VERO se entrambi i valori sono identici o FALSO in caso contrario.

4. Compila la formula per confrontare i valori per il resto delle righe nel primo foglio.

Note that this method doesn’t search for duplicates across a cell range. Instead, you are only looking for matches based on the same cell in a different sheet.

This can be useful with ordered data where you only expect a few exceptions.

The VLOOKUP, COUNTIF, and EXACT functions are useful for finding duplicates, but Excel is a versatile program, and there are other options. In the next section, we look at how you can use conditional formatting to identify duplicates in two sheets.

2. How to Use Conditional Formatting for Duplicate Rows

In this section, you’ll learn how to use conditional formatting to find and highlight duplicate rows in two Excel worksheets.

To create a conditional formatting rule, follow these steps:

1. Select the range of cells containing the data (A2:A5 in this case).

2. Click on the “Home” tab in the Excel ribbon.

3. Fare clic su "Formattazione condizionale" nel gruppo "Stili".

4. Scegli "Nuova regola" dal menu a discesa.

L'attività successiva consiste nel fornire una formula da utilizzare per la regola. Segui questi passi:

1. Scegli "Usa una formula per determinare quali celle formattare" nella finestra di dialogo

2. Inserisci la seguente formula: =COUNTIF(Foglio2!$A$2:$A$5, A2) > 0

Infine, applichi la formattazione che preferisci per le celle duplicate.

1. Fare clic sul pulsante Formato per aprire la finestra di dialogo Formato celle.

2. Scegli un formato, ad esempio riempi i duplicati con un colore di sfondo giallo.

3. Fare clic su OK.

I tuoi dati duplicati sono ora evidenziati in giallo.

Come utilizzare il gestore delle regole di formattazione condizionale

Dopo aver creato la regola di formattazione condizionale, puoi gestirla utilizzando Gestione regole di formattazione condizionale.

Per accedere al gestore:

1. Vai alla scheda Home.

2. Fare clic su Formattazione condizionale.

3. Scegli "Gestisci regole".

Verrà visualizzato un elenco di tutte le regole di formattazione condizionale applicate al foglio selezionato. È possibile modificare, eliminare o modificare l'ordine delle regole selezionando la regola e facendo clic sui pulsanti appropriati.

Per applicare la stessa regola all'altro foglio, procedi nel seguente modo:

1. Seleziona l'intervallo che desideri confrontare nel secondo foglio.

2. Vai al gestore delle regole di formattazione condizionale.

3. Seleziona la regola, fai clic su Regola duplicata e poi premi Modifica regola.

4. Sostituisci "Foglio2" con il nome del primo foglio da confrontare.

Ora che hai applicato la regola di formattazione condizionale a entrambi i fogli, i duplicati verranno evidenziati in base alla formattazione che hai scelto.

Assicurati di regolare l'intervallo e i riferimenti di cella nelle formule secondo necessità per coprire tutti i dati che desideri confrontare.

La formattazione condizionale potrebbe sembrare un po' primitiva. Se desideri un controllo più preciso, Power Query potrebbe essere la risposta! Nella sezione successiva, spieghiamo come utilizzare Power Query per trovare i duplicati.

3. Come utilizzare Power Query per trovare duplicati tra i fogli di lavoro

Power Query è uno strumento di trasformazione e preparazione dei dati in Microsoft Excel. L'identificazione degli stessi valori è solo una delle numerose attività di analisi che è possibile eseguire con lo strumento.

Per fare ciò, devi prima importare i dati nei due fogli di lavoro in tabelle separate. Segui questi passaggi all'interno di ogni foglio:

1. Fare clic con il pulsante destro del mouse sull'intervallo di celle.

2. Scegli "Ottieni dati da tabella/intervallo".

3. Modifica il nome della tabella in qualcosa di appropriato.

Dopo aver importato entrambi i fogli, la prima operazione è unire i dati:

1. Vai alla scheda Dati.

2. Fare clic su "Ottieni dati".

3. Seleziona "Combina query".

4. Scegli "Unisci" e seleziona le due tabelle.

5. Fare clic sulle due colonne chiave.

6. Scegli "Inner" come "Join Kind" e fai clic su OK.

L'editor di Power Query si aprirà con i dati combinati di entrambe le tabelle nel foglio Excel. Vedrai due colonne, una per ogni tabella. Poiché sei interessato solo ai valori duplicati, puoi rimuovere la seconda colonna.

È possibile fare clic su "Chiudi e carica" ​​nell'editor di Power Query per caricare i duplicati in un nuovo foglio di lavoro.

Per esplorare altri aspetti di questa potente funzionalità, segui gli esempi nel nostro articolo su come utilizzare Power Query in Excel .

Excel dispone anche di strumenti e componenti aggiuntivi di terze parti che aggiungono la possibilità di trovare facilmente duplicati, quindi diamo un'occhiata ad alcuni di questi strumenti nella sezione successiva.

4. Strumenti e componenti aggiuntivi per identificare i duplicati nei fogli di lavoro

Gli strumenti e i componenti aggiuntivi esterni possono offrire funzionalità avanzate che potrebbero non essere disponibili nelle funzionalità native di Excel. Questi strumenti possono semplificare ulteriormente il processo di confronto dei fogli per i duplicati.

Spreadsheet Compare è uno strumento Microsoft che consente di confrontare due cartelle di lavoro affiancate, evidenziando le differenze e identificando facilmente i duplicati. Puoi scaricarlo dal sito Web di Microsoft.

Esistono diversi componenti aggiuntivi che è possibile installare per automatizzare il processo di ricerca dei duplicati. Un esempio è " Duplicate Remover ". Per installare un componente aggiuntivo:

1. Vai alla scheda Inserisci.

2. Fare clic su "Ottieni componente aggiuntivo".

3. Cerca "Duplicato".

4. Fai clic su "Aggiungi" sullo strumento di tua scelta.

5. Come controllare visivamente i duplicati in due fogli

Se tutto il resto fallisce, usa i tuoi occhi! La finestra di dialogo Disponi finestre in Excel consente di visualizzare più fogli di lavoro o cartelle di lavoro affiancati.

Sebbene non trovi direttamente i duplicati, può aiutarti a confrontare visivamente i dati tra fogli di lavoro o cartelle di lavoro per individuare i duplicati. Segui questi passi:

1. Fare clic sulla scheda "Visualizza" nella barra multifunzione di Excel.

2. Fare clic su "Disponi tutto" nel gruppo "Finestra".

3. Scegli un'opzione di disposizione, ad esempio "Verticale" o "Orizzontale".

Questo visualizzerà entrambi i fogli uno accanto all'altro o uno sopra l'altro. Ora puoi confrontare manualmente i dati in ogni foglio per identificare i duplicati.

È necessario scorrere i dati e ispezionare visivamente ogni valore per trovare le corrispondenze.

Si noti che questo metodo non è efficiente per set di dati di grandi dimensioni, in quanto richiede il confronto manuale. L'uso degli altri metodi in questo articolo sarà più efficace per trovare duplicati in set di dati più grandi.

E questo è l'ultimo dei nostri metodi comuni per trovare valori duplicati nei fogli Excel! Nella prossima sezione, ti daremo alcuni suggerimenti per preparare i tuoi fogli di lavoro.

3 suggerimenti per preparare i fogli di lavoro di Excel

Prima di iniziare a confrontare più fogli, assicurati che le colonne e le righe dei tuoi set di dati siano allineate correttamente.

Assicurati che entrambi i fogli Excel abbiano la stessa struttura e gli stessi nomi di intestazione. Se necessario, puoi riorganizzare le colonne in entrambi i fogli in modo che corrispondano l'una all'altra.

Ecco tre suggerimenti per garantire confronti accurati:

1. Disponi i tuoi dati nello stesso ordine in entrambi i fogli. Ciò semplifica il funzionamento efficace delle funzioni di Excel.

2. Normalizza i dati utilizzando formattazione, lettere maiuscole e tipi di dati coerenti. Ciò impedirà voci non corrispondenti a causa di piccole differenze.

3. Rimuovi righe o colonne vuote non necessarie, in quanto potrebbero interferire con il processo di confronto.

Puoi valutare quanta duplicazione hai in un set di dati contando i valori univoci. Questo video illustra diversi metodi:

Come gestire errori e incoerenze

Le incoerenze nei dati possono influire sul processo di confronto. Ecco quattro consigli per risolvere le incoerenze:

1. Verificare la presenza di discrepanze nei tipi di dati, ad esempio la combinazione di testo e valori numerici nella stessa colonna.

2. Assicurati che venga utilizzata una formattazione coerente per date, numeri e altri tipi di dati.

3. Esamina i tuoi dati per voci mancanti o errate e aggiorna se necessario.

4. Standardizza le abbreviazioni o le convenzioni di denominazione incoerenti all'interno dei tuoi set di dati.

Pensieri finali

Trovare duplicati in due fogli di lavoro Excel è un'attività essenziale per la gestione e l'analisi dei dati, garantendo l'integrità e l'accuratezza dei dati. Excel offre più tecniche per identificare i duplicati, ognuna con i propri vantaggi e limiti.

La scelta del metodo dipende dalle esigenze dell'utente, dalla dimensione e dalla complessità del set di dati e dal risultato desiderato. Per set di dati più piccoli e confronti diretti, l'utilizzo di VLOOKUP , COUNTIF o la formattazione condizionale può essere sufficiente.

Per set di dati più grandi o trasformazioni di dati più complesse, Power Query è uno strumento potente e flessibile in grado di gestire un'ampia gamma di attività di preparazione dei dati, inclusa la ricerca di duplicati.

Per concludere, confrontare i fogli Excel per i duplicati è un'abilità super utile da avere nella tua cassetta degli attrezzi. Con i trucchi di questo articolo, puoi individuare quei fastidiosi duplicati e mantenere i tuoi dati perfettamente puliti.

Fidati di noi, man mano che migliorerai in questo, supererai le tue attività relative ai dati e impressionerai tutti intorno a te!