Trova duplicati in Excel: evidenziali, contali ed elencali
By Mirko Peters · · Updated
Trova duplicati in Excel con formattazione condizionale, flag COUNTIF, UNIQUE, Power Query e controlli tra fogli, oltre a come individuare le corrispondenze simili prima di eliminare qualcosa.
Automatically translated with AI from the original post. Read the English original
Per trovare i duplicati in Excel senza eliminare nulla, seleziona i dati e usa Home > Formattazione condizionale > Regole evidenziazione celle > Valori duplicati. Excel colora ogni valore che compare più di una volta. Se preferisci dei numeri invece dei colori, aggiungi una colonna di supporto con una formula CONTA.SE. Questo articolo è pensato per chi eredita elenchi disordinati e deve vedere il problema prima di risolverlo. Troverai il metodo di evidenziazione, le formule di conteggio e di segnalazione, un modo per elencare solo i valori ripetuti, un'opzione con Power Query, i controlli tra fogli e un piano per le corrispondenze simili.
Punti chiave
- Il modo più rapido per trovare i duplicati in Excel è Home, Formattazione condizionale, Regole evidenziazione celle, Valori duplicati. Colora i valori ripetuti e non elimina nulla.
- Una colonna di supporto con CONTA.SE indica quante volte compare ciascun valore, e con CONTA.PIÙ.SE puoi verificare più colonne insieme.
- CONTA.SE non distingue maiuscole e minuscole e tratta ? e * come caratteri jolly, quindi il conteggio può non corrispondere alle tue aspettative.
- Per confrontare due fogli, conta ogni valore rispetto all'altro elenco con CONTA.SE, e apri entrambe le cartelle di lavoro se il secondo elenco si trova in un altro file.
- Le corrispondenze simili, come Jon e John, non vengono riconosciute come duplicati. Elimina prima gli spazi superflui e uniforma maiuscole e minuscole, poi controlla il resto a occhio oppure con il componente aggiuntivo Ricerca approssimativa.
Prima si trova, poi si rimuove. Se sai già quali righe vuoi eliminare, la nostra guida complementare su come rimuovere i duplicati in Excel spiega il passaggio di eliminazione. Qui l'obiettivo è esaminare prima i dati, così decidi tu cosa considerare un duplicato e cosa conservare.
Evidenziare i duplicati con la formattazione condizionale
È il controllo più veloce, e Microsoft lo consiglia come passaggio di verifica prima di rimuovere qualsiasi dato. Segue l'articolo Microsoft su come filtrare i valori univoci o rimuovere i valori duplicati.
- Seleziona le celle, la colonna o l'intervallo che vuoi controllare. Di solito basta una colonna.
- Nella scheda Home, apri Formattazione condizionale.
- Scegli Regole evidenziazione celle, quindi Valori duplicati.
- Scegli un formato, ad esempio un riempimento rosso chiaro, e seleziona OK.
Ogni cella il cui valore compare più di una volta è ora colorata. Scorri il foglio oppure filtra la colonna per colore per vedere solo le celle evidenziate. Per annullare, torna a Formattazione condizionale e cancella le regole dall'intervallo.
Esiste un secondo metodo che offre più controllo. Apri Formattazione condizionale > Gestisci regole, crea una Nuova regola e scegli Formatta solo valori univoci o duplicati. Nell'elenco Formatta tutto puoi scegliere univoci o duplicati, e puoi impostare formato numerico, carattere, bordo o riempimento. È utile quando vuoi colorare i valori che compaiono una sola volta e lasciare invariati quelli ripetuti.
Due limiti da conoscere. Microsoft afferma che Excel non può evidenziare i duplicati nell'area Valori di una tabella pivot. E la regola considera una sola colonna alla volta quando ne selezioni una. Una riga che si ripete solo se si guardano più colonne insieme non verrà evidenziata. Per questo serve una formula.
Contare e segnalare i duplicati con CONTA.SE
Una colonna di supporto ti fornisce un numero che puoi ordinare e filtrare. È anche lo strumento giusto quando due righe sono duplicate solo se due o tre colonne coincidono.
Segnalare ogni ripetizione
Supponi che i valori si trovino in A2:A100. In B2 inserisci:
=COUNTIF($A$2:$A$100,A2)
Trascina la formula verso il basso. Un risultato di 1 indica che il valore è univoco. Un 2 indica che compare due volte, e così via. Ogni copia di un valore ripetuto riceve lo stesso numero, compresa la prima. Per trasformare il conteggio in VERO o FALSO, aggiungi un confronto: =COUNTIF($A$2:$A$100,A2)>1.
Segnalare solo le copie successive
Quando prevedi di eliminare delle righe, di solito vuoi conservare la prima. Usa invece un intervallo espandibile:
=COUNTIF(A$2:A2,A2)>1
L'intervallo A$2:A2 si allarga man mano che copi la formula verso il basso. La prima volta che un valore compare ottieni FALSO. Ogni comparsa successiva ottiene VERO. Questo corrisponde al comportamento del comando Rimuovi duplicati di Excel, che conserva la prima occorrenza.
Controllare più colonne con CONTA.PIÙ.SE
Per un record che è un duplicato solo quando, ad esempio, nome e cognome coincidono entrambi, usa CONTA.PIÙ.SE con una coppia di intervallo e criterio per ogni colonna:
=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)
Un risultato superiore a 1 indica che la combinazione compare più di una volta. Aggiungi altre coppie per altre colonne. Non provare a costruire lo stesso controllo unendo gli intervalli dentro CONTA.SE, come in COUNTIF($A$2:$A$100&$B$2:$B$100,A2&B2). CONTA.SE richiede un vero intervallo come primo argomento, quindi quella formula non fa ciò che sembra. CONTA.PIÙ.SE è la soluzione pulita.
Il comportamento di CONTA.SE che sorprende
La pagina di CONTA.SE di Microsoft elenca alcuni comportamenti importanti quando cerchi duplicati:
- Il confronto del testo ignora le maiuscole e le minuscole. "apples" e "APPLES" contano come lo stesso valore. Excel li tratta qui come duplicati.
- I caratteri
?e*funzionano come jolly. Se i dati li contengono, ad esempio nei codici prodotto, CONTA.SE potrebbe trovare più corrispondenze del previsto. Metti una tilde prima del carattere per cercarlo alla lettera. - CONTA.SE restituisce risultati errati per stringhe più lunghe di 255 caratteri.
- Spazi iniziali o finali, virgolette diritte e curve mescolate e caratteri non stampabili possono falsare il conteggio. Microsoft suggerisce le funzioni ANNULLA.SPAZI e LIBERA per pulire prima i dati.
Quest'ultimo punto è all'origine della maggior parte delle lamentele del tipo "Excel non trova i miei duplicati". Due celle che sembrano identiche possono differire per uno spazio finale. Prima pulisci, poi conta.
Filtrare e verificare
Una volta inserita la colonna di supporto, filtrala per VERO, oppure per valori superiori a 1, e controlla le righe. Puoi ordinare per la colonna di supporto per raggruppare tutte le righe ripetute. Dai un'occhiata a un campione prima di eliminare qualsiasi cosa. Spesso un "duplicato" si rivela essere due record reali che condividono un nome.
Elencare solo i valori duplicati
A volte vuoi un elenco breve di ciò che si ripete, non un contrassegno accanto a ogni riga.
In Excel per Microsoft 365, 2024 e 2021, la funzione UNICI può fare il lavoro. Secondo la pagina di Microsoft dedicata a UNICI, =UNIQUE(array,[by_col],[exactly_once]) restituisce i valori distinti di un intervallo, e impostando exactly_once su VERO restituisce solo i valori che compaiono una volta sola. Quindi =UNIQUE(A2:A100) restituisce ogni valore una volta, e =UNIQUE(A2:A100,,TRUE) restituisce i valori che non sono affatto duplicati. Per i soli valori ripetuti, combina UNICI con FILTRA e CONTA.SE:
=UNIQUE(FILTER(A2:A100,COUNTIF(A2:A100,A2:A100)>1))
Questa formula mantiene le righe con un conteggio superiore a 1 e mostra ciascuna di esse una sola volta. Richiede una versione di Excel con matrici dinamiche. Provala su poche righe prima di fidarti di un elenco lungo.
Nelle versioni precedenti, usa una colonna di supporto. Inserisci =IF(COUNTIF($A$2:$A$100,A2)>1,A2,"") e trascinala verso il basso. La colonna ora mostra ogni valore ripetuto e lascia vuote le altre celle. Filtra via gli spazi vuoti. Vedrai ogni valore ripetuto tante volte quante compare, il che va bene per una verifica.
Con Filtro avanzato puoi copiare solo i record univoci in una nuova posizione. Vai su Dati > Avanzate, scegli Copia in un'altra posizione, inserisci una cella nella casella Copia in e seleziona Solo record univoci. Confrontando questo elenco con l'originale vedi cosa si ripeteva.
Usa Power Query per mantenere solo i duplicati
Se i tuoi dati sono numerosi o arrivano ogni settimana, lascia che Power Query trovi i duplicati. La documentazione Microsoft di Power Query descrive il comando Mantieni duplicati, l'opposto di Rimuovi duplicati. Filtra la tabella lasciando le righe i cui valori nelle colonne selezionate compaiono più di una volta e, a differenza di Rimuovi duplicati, restituisce tutte le righe corrispondenti, non una per valore.
L'articolo Microsoft su come mantenere o rimuovere le righe duplicate descrive i passaggi per la rimozione. L'avvio è identico per entrambi:
- Seleziona una cella dei dati e scegli Query > Modifica, oppure caricali con Dati > Da tabella/intervallo.
- Fai clic su un'intestazione di colonna per selezionarla. Tieni premuto Maiusc o Ctrl per selezionare altre colonne.
- Applica il comando. Per la rimozione, Microsoft indica Home > Rimuovi righe > Rimuovi duplicati. Cerca il comando corrispondente Mantieni duplicati tra gli altri comandi sulle righe nella scheda Home e controlla l'anteprima.
Microsoft segnala inoltre che Power Query distingue tra maiuscole e minuscole, quindi "Apple" e "apple" sono valori diversi, a differenza di quanto accade con COUNTIF. È una differenza reale tra i due metodi. Se il tuo obiettivo è un controllo settimanale ripetibile, scegli un comportamento e mantienilo, per esempio trasformando prima la colonna in maiuscolo.
Trova duplicati tra due fogli o elenchi
Un'attività comune è verificare se un elenco presente in un foglio compare anche in un altro: un nuovo elenco clienti rispetto a quello vecchio, oppure gli ordini di questo mese rispetto a quelli del mese scorso.
- Metti i due elenchi su fogli separati, per esempio Foglio1 e Foglio2, con i valori nella colonna A.
- In Foglio1, nella cella B2, inserisci
=COUNTIF(Foglio2!A:A;A2)>0. - Trascina la formula verso il basso. VERO significa che il valore compare anche in Foglio2.
- Filtra su VERO per vedere le corrispondenze, oppure su FALSO per vedere i nuovi valori.
Per due colonne che devono corrispondere insieme, usa COUNTIFS con una coppia di criteri per ogni colonna e gli intervalli dell'altro foglio.
Se il secondo elenco si trova in una cartella di lavoro diversa, Microsoft avverte che una formula che fa riferimento a una cartella chiusa può restituire un errore #VALORE!. Tieni aperte entrambe le cartelle mentre lavori, oppure porta il secondo elenco nella prima con Power Query. Dopo il controllo, ricorda che il risultato dipende dai dati di quel momento: se uno dei due elenchi cambia, cambiano anche i contrassegni.
Corrispondenze approssimative: Jon Smith e John Smith
Tutto quanto descritto sopra trova solo corrispondenze esatte, a parte le maiuscole. Gli elenchi reali contengono anche refusi e grafie diverse. Excel non dispone di una funzione integrata che misuri quanto due testi siano simili, quindi questi casi si gestiscono in due passaggi.
Primo passaggio: elimina le differenze false. Usa TRIM per rimuovere gli spazi in eccesso e CLEAN per eliminare i caratteri non stampabili, e costruisci il controllo sui valori puliti. In una colonna di supporto, =TRIM(A2) mostra il testo pulito. Conta i duplicati su quella colonna, non su quella originale. Poiché COUNTIF ignora le maiuscole, lì non serve cambiare il formato. In Power Query, dove le maiuscole contano, trasforma la colonna in maiuscolo o minuscolo.
Secondo passo: controlla cosa resta. Ordina l'elenco pulito in ordine alfabetico e leggi le voci vicine. Le corrispondenze quasi identiche, come "Jon Smith" e "John Smith", finiscono una accanto all'altra, così una persona può vederle. Per gli elenchi più lunghi, Microsoft Research ha pubblicato il componente aggiuntivo Fuzzy Lookup per Excel. Può trovare righe duplicate approssimative all'interno di una stessa tabella oppure abbinare righe simili tra due tabelle, e assegna a ogni corrispondenza un punteggio di somiglianza. Prima di pianificare il suo utilizzo, verifica i requisiti: la pagina di download indica Windows, con Excel 2007, 2010, 2013 e 2016, e una versione del 2020. Potrebbe non installarsi su una configurazione attuale di Microsoft 365 e non è disponibile su Mac.
Qualunque strumento usi, verifica a mano i risultati della ricerca approssimativa. Due persone con nomi simili non sono necessariamente la stessa persona.
Errori comuni
- Controllare una sola colonna quando il duplicato è definito da due. Un cognome ripetuto da solo non indica un record duplicato.
- Fidarsi dei conteggi di COUNTIF su dati con spazi superflui o caratteri anomali. Pulisci prima i dati con TRIM e CLEAN.
- Dimenticare che COUNTIF non distingue maiuscole e minuscole mentre Power Query sì, e ottenere risultati diversi dai due strumenti.
- Usare
COUNTIF($A$2:$A$100,A2)>1ed eliminare ogni riga con TRUE. In questo modo viene segnalata anche la prima copia, quindi finiresti per eliminare tutte le copie. Usa l'intervallo espandibile per segnalare solo quelle successive. - Cercare in intere colonne stringhe più lunghe di 255 caratteri, caso in cui COUNTIF restituisce risultati errati.
- Eliminare subito dopo aver evidenziato. Copia prima il foglio, perché Microsoft afferma che i duplicati rimossi vengono eliminati in modo permanente.
Argomenti correlati da esplorare
- Rimuovi i duplicati trovati. Il comando Rimuovi duplicati nella scheda Dati mantiene la prima corrispondenza ed elimina le altre. Ordina prima i dati per controllare quale riga resta.
- Conserva il record migliore, non il primo. Ordina per data o valore in modo che la riga che ti serve compaia per prima, poi segnala le copie successive con COUNTIF espandibile.
- Confronta elenchi tra file diversi. Power Query può combinare più cartelle di lavoro in un'unica tabella, così puoi confrontarle insieme.
- Previeni nuovi duplicati. La convalida dei dati con una formula personalizzata può bloccare un valore già presente. Provala su un foglio di prova.
- Conserva una traccia di controllo. Lascia la colonna di supporto in una copia del foglio, così potrai dimostrare in seguito che cosa è stato segnalato e perché.
Prossimo passo
Scegli una colonna del file attuale, evidenzia i suoi duplicati con la formattazione condizionale e conta quante celle vengono evidenziate. Poi aggiungi una colonna di supporto con COUNTIF e confronta i numeri. Se non coincidono, hai appena trovato spazi nascosti o differenze di maiuscole e minuscole, e sai già come trovare i duplicati in Excel prima di eliminare qualsiasi cosa.
Fonti
- Filtrare valori univoci o rimuovere valori duplicati (Supporto Microsoft)
- Funzione COUNTIF (Supporto Microsoft)
- Mantenere o rimuovere righe duplicate (Power Query) (Supporto Microsoft)
- Utilizzo di valori duplicati (Microsoft Learn)
- Componente aggiuntivo Fuzzy Lookup per Excel (Microsoft Download Center)