Quando si cercano celle duplicate in una sola colonna o elenco, è possibile utilizzare la COUNTIF funzione per trovare e contare facilmente i dati duplicati.
11. Seleziona una cella vuota accanto alla colonna in cui vuoi trovare i duplicati.

2. Digita la formula =COUNTIF($C$3:$C$12, C3)>1 e premi il tasto Invio.

3. Trascina il quadratino di riempimento automatico della cella della formula per copiarla nelle altre celle.

Note: Nella formula =COUNTIF($C$3:$C$12, C3)>1,
(1) $C$3:$C$12 è la colonna o l'elenco in cui vuoi trovare i valori duplicati. Poiché la colonna rimane fissa quando si copia la formula in altre celle, di solito si utilizza il riferimento assoluto con “$”.
(2) C3 è la prima cella della colonna specificata. È un riferimento relativo perché deve cambiare automaticamente quando si copia la formula in altre celle.
(3) Questa formula restituirà Sì o No. Sì significa che il valore corrispondente è duplicato, mentre No indica che il valore corrispondente è unico nella colonna.
(4) A volte, Sì o No potrebbero non essere intuitivi. Possiamo combinare la formula originale con la funzione SE per restituire Duplicati direttamente.
=IF(COUNTIF($C$3:$C$12, C3)>1,"Duplicati","")

In alcuni casi, è necessario confrontare due colonne e trovare i valori duplicati. Ad esempio, hai due elenchi di nomi e vuoi trovare i nomi duplicati nel secondo elenco rispetto al primo. Puoi utilizzare le funzioni CERCA.VERT e SE.ERRORE per ottenere facilmente il risultato.

1. Seleziona una cella vuota accanto al secondo elenco di nomi.

2. Inserisci la formula =IFERROR(VLOOKUP(D3,$B$3:$B$18,1,0),"") e premi il tasto Invio.

3. Trascina il quadratino di riempimento automatico della cella della formula per copiarla nelle altre celle secondo necessità.

Note: Nella formula sopra,
(1) D3 è la prima cella del secondo elenco di nomi. Il riferimento deve cambiare automaticamente quando si copia la formula in altre celle, quindi è relativo.
(2) $B$3:$B$18 è il primo elenco di nomi. È un riferimento assoluto perché l'intervallo deve rimanere fisso quando si copia la formula in altre celle.
(3) Quando un nome è duplicato rispetto ai nomi del primo elenco, la formula restituirà il nome; altrimenti restituirà una cella vuota.
(4) Puoi anche utilizzare la formula =IF(COUNTIF($B$3:$B$18,D3)>0,"Duplicati","") per trovare i nomi duplicati nel secondo elenco rispetto al primo. Questa formula restituirà "Duplicati" se il nome corrispondente è duplicato.

(5) Se hai bisogno di trovare duplicati in due colonne su due fogli diversi, basta aggiungere il nome del foglio prima del riferimento della colonna da confrontare. Nel nostro esempio, basta cambiare $B$3:$B$18 in Sheet1!$B$3:$B$18 nella formula.

Le formule sopra introdotte non considerano le maiuscole/minuscole quando trovano i duplicati, quindi "apple" viene trattato come duplicato di "APPLE". Puoi usare una formula array per trovare valori duplicati in una singola colonna distinguendo tra maiuscole e minuscole.

1. Seleziona una cella vuota accanto alla colonna in cui vuoi trovare i duplicati.

2. Digita la formula array =IF(SUM((--EXACT($C$3:$C$12,C3)))<=1,"","Duplicato") e premi contemporaneamente Ctrl + Shift + Invio.

3. Trascina il quadratino di riempimento automatico della cella della formula per copiarla nelle altre celle.

Note: Nella formula array sopra,
(1) $C$3:$C$12 è la colonna in cui devi trovare i valori duplicati. Il riferimento è assoluto perché rimane fisso quando si copia la formula array in altre celle.
(2) C4 è la prima cella della colonna. Il riferimento è relativo, perché deve cambiare automaticamente quando si copia la formula array in altre celle.
(3) Se la cella corrispondente è duplicata, la formula array restituirà "Duplicato"; altrimenti restituirà una cella vuota.

Puoi utilizzare la funzione Formattazione condizionale per evidenziare rapidamente le celle duplicate in una colonna o in un intervallo.

1. Seleziona la colonna in cui vuoi evidenziare le celle duplicate.

2. Fai clic su Home > Formattazione condizionale > Regole evidenziazione celle > Valori duplicati. Vedi screenshot sotto:

3. Nella finestra di dialogo Valori duplicati che si apre, seleziona Duplicato dal primo menu a discesa, scegli uno scenario di evidenziazione dal secondo menu a discesa e fai clic su OK.

Note: Se gli scenari di evidenziazione preimpostati non soddisfano le tue esigenze, puoi selezionare Formato personalizzato dal secondo menu a discesa e poi scegliere colore di evidenziazione, carattere e bordi delle celle nella finestra di dialogo Formato celle che si apre.

Ora vedrai che tutte le celle duplicate sono evidenziate nella colonna selezionata come mostrato nello screenshot sotto.

Note:
(1) Dopo aver evidenziato le celle duplicate, puoi facilmente filtrare questi duplicati. (Clicca per vedere come)
(2) Dopo aver evidenziato le celle duplicate, puoi anche rimuovere facilmente i duplicati in blocco. (Clicca per vedere come)

Alcuni utenti preferiscono evidenziare le righe in base alle celle duplicate in una determinata colonna. In questo caso, possiamo creare una regola personalizzata di formattazione condizionale per ottenere il risultato desiderato.

1. Seleziona l'intervallo (escludendo la riga di intestazione) in cui vuoi evidenziare le righe basate su celle duplicate.

2. Fai clic su Home > Formattazione condizionale > Nuova regola.

3. Nella finestra di dialogo Nuova regola di formattazione,
(1) Fai clic per selezionare Usa una formula per determinare le celle da formattare opzione;
(2) Digita la formula =COUNTIF($C$3:$C$12,$C3)>1 nella Casella Formatta i valori per cui questa formula restituisce VERO casella;
Suggerimenti: Nella formula, $C$3:$C$12 è la colonna che contiene le celle duplicate e $C3 è la prima cella della colonna.
(3) Fai clic sul Formato pulsante.

4. Nella finestra di dialogo Formato celle, specifica colore di riempimento, carattere e bordi delle celle secondo necessità, quindi fai clic sui pulsanti OK per salvare le impostazioni.

A questo punto, nell'intervallo selezionato, le righe sono evidenziate in base alle celle duplicate nella colonna specificata. Vedi screenshot:

Per evidenziare le righe duplicate in un determinato intervallo, puoi utilizzare anche la funzione Formattazione condizionale.

1. Seleziona l'intervallo esclusa la riga di intestazione.

2. Fai clic su Home > Formattazione condizionale > Nuova regola.

3. Nella finestra di dialogo Nuova regola di formattazione:
(1) Fai clic per selezionare la Usa una formula per determinare le celle da formattare opzione;
(2) Nella Casella Formatta i valori per cui questa formula restituisce VERO casella, digita la formula =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1;
(3) Fai clic sul Formato pulsante.

Note: Nella formula =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1:
(1) $B$3:$B$12 è la prima colonna dell'intervallo, e $B3 è la prima cella di questa colonna;
(2) $C$3:$C$12 è la seconda colonna dell'intervallo, e $C3 è la prima cella della colonna;
(3) $D$3:$D$12 è la terza colonna dell'intervallo, e $D3 è la prima cella della colonna;
(4) Se ci sono più colonne nel tuo intervallo, puoi aggiungere i riferimenti delle colonne e le loro prime celle nella formula.

4. Nella finestra di dialogo Formato celle, specifica colore di evidenziazione, carattere, bordi delle celle, ecc. secondo necessità, quindi fai clic sui pulsanti OK per salvare l'impostazione.

A questo punto, le righe duplicate sono identificate ed evidenziate nell'intervallo selezionato. Vedi screenshot:

Avrai notato che tutti i duplicati vengono identificati o evidenziati con i metodi sopra. A volte, vuoi solo vedere quali elementi sono duplicati eccetto la prima occorrenza. Puoi ottenere questo risultato anche con la funzione Formattazione condizionale e una formula diversa.

1. Seleziona la colonna con intestazione.
Suggerimenti: Se vuoi evidenziare le righe basate su duplicati in una colonna eccetto la prima occorrenza, seleziona l'intervallo senza la riga di intestazione.

2. Fai clic su Home > Formattazione condizionale > Nuova regola.

3. Nella finestra di dialogo Nuova regola di formattazione:
(1) Fai clic per evidenziare la Usa una formula per determinare le celle da formattare opzione;
(2) Nella Casella Formatta i valori per cui questa formula restituisce VERO casella, digita la formula =COUNTIF($C$3:$C3, C3)>1;
Suggerimenti: Per evidenziare le righe basate su duplicati in una colonna, digita la formula =COUNTIF($C$3:$C3, $C3)>1.
(3) Fai clic sul Formato pulsante.

4. Nella finestra di dialogo Formato celle che si apre, specifica colore di evidenziazione, carattere, bordi delle celle, ecc. secondo necessità, quindi fai clic sui pulsanti OK per salvare le impostazioni.

Ora vedrai che le celle duplicate eccetto la prima occorrenza nella colonna selezionata (o le righe basate su duplicati nella colonna specificata) sono evidenziate. Vedi screenshot:

Quando evidenziamo celle o righe duplicate con la funzione Formattazione condizionale, tutti i duplicati vengono evidenziati con lo stesso colore. Tuttavia, se serie diverse di valori duplicati vengono evidenziate con colori diversi, sarà più facile leggere e distinguere le differenze. Qui, una macro VBA può aiutarti a ottenere questo risultato in Excel.

1. Premi contemporaneamente i tasti Alt + F11 per aprire la finestra Microsoft Visual Basic for Applications.

2. Nella finestra, fai clic su Inserisci > Modulo, quindi incolla il codice qui sotto nella nuova finestra del modulo.

VBA: Evidenzia celle duplicate con colori diversi:

Sub HighlightDuplicatesInDifferentColors()
'Update by Extendoffice 20201013
Dim xURg, xRg, xFRg, xRgPre As Range
Dim xAddress As String
Dim xDt As Object
Dim xFNum, xCInt As Long
Dim xBol As Boolean
Dim xWs As Worksheet
Dim xSArr
Set xRg = Application.ActiveWindow.RangeSelection
If xRg.Count > 1 Then
xAddress = xRg.AddressLocal
Else
xAddress = xRg.Worksheet.UsedRange.AddressLocal
End If
On Error Resume Next
Set xURg = Application.InputBox("Select range:", "Kutools for Excel", xAddress, , , , , 8)
If xURg Is Nothing Then Exit Sub
Set xURg = Intersect(xURg.Worksheet.UsedRange, xURg)
Set xDt = CreateObject("scripting.dictionary")
Set xWs = xURg.Worksheet
xCInt = 5
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
For xFNum = 1 To xURg.Count
Set xFRg = xURg.Item(xFNum)
If Not IsError(xFRg) Then
If xFRg.Value <> "" Then
If xDt.exists(xFRg.Text) Then
xSArr = Split(xDt(xFRg.Text), ";")
If xSArr(1) = "Only" Then
xCInt = xCInt + 1
xSArr(1) = xCInt
Set xRgPre = xWs.Range(xSArr(0))
xRgPre.Interior.ColorIndex = xCInt
xDt(xFRg.Text) = xSArr(0) & ";" & xSArr(1)
End If
xFRg.Interior.ColorIndex = xSArr(1)

Else
xDt(xFRg.Text) = xFRg.Address & ";Only"
End If

End If
End If
Next
xURg.Worksheet.Active
xURg.Select
Application.ScreenUpdating = xBol
End Sub

3. Premi il tasto F5 o fai clic sull'icona Esegui per avviare questa macro VBA.

4. Nella finestra di dialogo Kutools per Excel che si apre, seleziona la colonna in cui vuoi evidenziare le celle duplicate con colori diversi e fai clic su OK.

Ogni serie di valori duplicati ora sarà evidenziata con un colore distinto.

Il primo strumento che presento è la funzione Seleziona celle duplicate e uniche, fornita da Kutools per Excel. Questa funzione consente di trovare rapidamente le celle uniche o duplicate.

1. Seleziona la colonna o l'intervallo in cui vuoi trovare ed evidenziare le celle duplicate.

2. Fai clic su Kutools > Seleziona > Seleziona celle duplicate e uniche.

3. Nella finestra di dialogo Seleziona celle duplicate e uniche, seleziona le opzioni desiderate e fai clic su Ok pulsanti per completare l'operazione.

Note: Nella finestra di dialogo Seleziona celle duplicate e uniche,
(1) Se vuoi selezionare o evidenziare tutti i duplicati eccetto la prima occorrenza, seleziona l'opzione Valori duplicati (Escludi la prima corrispondenza) opzione. Altrimenti, seleziona l'opzione Valori duplicati (Includi la prima corrispondenza) opzione.
(2) Se vuoi evidenziare i duplicati, seleziona l'opzione Colore di sfondo di riempimento e specifica un colore di evidenziazione secondo necessità.
(3) Se vuoi selezionare o evidenziare le righe basate su duplicati nella colonna selezionata, seleziona l'opzione Seleziona intera riga opzione.
(4) Se vuoi selezionare o evidenziare valori duplicati distinguendo tra maiuscole e minuscole, seleziona l'opzione Sensibile alle maiuscole/minuscole opzione.

Kutools per Excel offre anche uno strumento straordinario – Confronta celle, per aiutarci a trovare ed evidenziare facilmente celle duplicate in due colonne.

1. Fai clic su Kutools > Confronta Celle per aprire la finestra di dialogo Seleziona celle uguali e diverse.

2. Nella finestra di dialogo Seleziona celle uguali e diverse, specifica le due colonne nelle caselle Trova valori in e Secondo, seleziona l'opzione Stesso valore e seleziona altre opzioni secondo necessità.

Note:
(1) Se vuoi trovare righe duplicate, seleziona Per riga opzione; e per trovare celle duplicate, seleziona Per cella opzione nella Metodo sezione;
(2) Seleziona Colore di sfondo di riempimento opzione e specifica un colore di evidenziazione se desideri evidenziare le righe o celle duplicate;
(3) Seleziona Seleziona intera riga opzione se vuoi selezionare o evidenziare l'intera riga in base ai duplicati;
(4) Seleziona Sensibile alle maiuscole/minuscole opzione se vuoi trovare o evidenziare duplicati sensibili alle maiuscole/minuscole.

3. Fai clic sui pulsanti Ok per completare le impostazioni.

Ora vedrai che i duplicati nella colonna specificata nella casella Trova valori in sono identificati ed evidenziati.

Questo metodo ti guiderà a identificare ed evidenziare le celle duplicate tramite una regola di formattazione condizionale, quindi filtrare facilmente per colore di evidenziazione in Excel.

1. Applica la formattazione condizionale per trovare ed evidenziare i duplicati nella colonna specificata. (Clicca per vedere come)

2. Fai clic per selezionare l'intestazione della colonna specificata, quindi fai clic su Dati > Filtro.

3. Procedi cliccando sull'icona del filtro nell'intestazione della colonna e seleziona Filtra per colore, quindi seleziona il colore di formattazione condizionale specificato nell'elenco a discesa. Vedi screenshot:

Ora vedrai che solo le righe con celle duplicate sono filtrate. Vedi screenshot:

In alternativa, possiamo anche identificare i duplicati con una formula in una colonna di supporto e poi filtrare facilmente i duplicati tramite la colonna di supporto in Excel.

1. Accanto ai dati originali, aggiungi una colonna di supporto e inserisci Duplicato come intestazione della colonna.

2. Seleziona la prima cella vuota sotto l'intestazione della colonna, inserisci la formula =IF(COUNTIF($C$3:$C$12,C3)>1,"Duplicato","") e trascina il quadratino di riempimento automatico per copiare la formula nelle altre celle.

Note: Nella formula sopra, $C$3:$C$12 è la colonna che contiene i dati duplicati e C3 è la prima cella (esclusa l'intestazione) della colonna.

3. Fai clic per selezionare l'intestazione della colonna – Duplicati, quindi fai clic su Dati > Filtro.

4. Quindi fai clic sull'icona del filtro nell'intestazione della colonna, seleziona solo Duplicato, e fai clic su OK pulsante. Vedi screenshot:

A questo punto, sono filtrate solo le righe con valori duplicati. Vedi screenshot:

In generale, possiamo utilizzare la formula =COUNTIF(intervallo, criterio) per contare il numero totale di una determinata occorrenza in un intervallo specificato. Ad esempio, per contare quante volte “Apple” appare nell'elenco A2:A10, possiamo usare la formula =COUNTIF(A2:A10, "Apple") per ottenere il numero di questa occorrenza duplicata.

Tuttavia, la formula =COUNTIF(intervallo, criterio) conta solo il valore duplicato specificato. Come contare invece un valore duplicato con due o più criteri? E come contare i duplicati sensibili alle maiuscole/minuscole con criteri? I metodi seguenti possono aiutarti a risolvere questi problemi.

Possiamo utilizzare una formula array per contare i valori duplicati sensibili alle maiuscole/minuscole con criteri in Excel. Ad esempio, per contare quante volte il valore "Apple" appare nell'elenco B2:B21 distinguendo tra maiuscole e minuscole, procedi così:

1. Seleziona una cella vuota.

2. Inserisci la formula =SUM(--EXACT(B2:B20,E2)).

3. Premi Ctrl + Shift + Invio per ottenere il risultato del conteggio.

Note: Nella formula array,
(1) B2:B20 è la colonna in cui vuoi contare i duplicati. (2) E2 è la cella che contiene il valore specificato di cui vuoi contare le occorrenze. Puoi cambiare il riferimento della cella con il valore tra virgolette, ad esempio "Apple".

A volte potresti voler contare i duplicati con due o più criteri. Puoi utilizzare la funzione COUNTIFS per ottenere il risultato.
Ad esempio, c'è una tabella di vendita di frutta come mostrato nello screenshot sotto. Qui dobbiamo contare quante volte è stata venduta la mela il5/7/2020 e con importo superiore a300. Puoi contare i duplicati con questi criteri come segue:

1. Seleziona una cella vuota.

2. Inserisci la formula =COUNTIFS(B3:B20,G4,C3:C20,G3,D3:D20,">300").

3. Premi il tasto Invio per ottenere il risultato del conteggio.

Note: Nella formula sopra,
(1) B3:B20 è la colonna delle date (prima colonna), e G4 è il criterio della data;
(2) C3:C20 è la colonna della frutta (seconda colonna), e G3 è il criterio della frutta;
(3) D3:D20 è la colonna degli importi (terza colonna), e ">300" è il criterio dell'importo.
(4) Se ci sono più colonne e criteri nella tua tabella, puoi aggiungere il riferimento di colonna e il criterio.

Per contare tutti i duplicati in una colonna eccetto la prima occorrenza, procedi così:

1. Seleziona una cella vuota accanto alla colonna.

2. Inserisci la formula =IF(COUNTIF($B$3:B3,B3)>1,"SÌ",""), quindi trascina il quadratino di riempimento automatico verso il basso per copiare la formula nelle altre celle.

Note: Nella formula sopra,
(1) $B$3:B3 è l'intervallo in cui conti i duplicati. In $B$3:B3, B3 cambierà automaticamente quando copi la formula nelle altre celle.
(2) B3 è la prima cella della colonna specificata.
(3) Questa formula restituirà SÌ o vuoto. SÌ indica che il valore corrispondente è duplicato, mentre vuoto significa unico.

Ora tutti i duplicati nella colonna specificata sono identificati. Possiamo contare i risultati della formula per ottenere il numero totale di duplicati.

3. Seleziona una cella vuota.

4. Inserisci la formula =COUNTIF(C3:C16,"SÌ"), e premi il Invio tasto.

Note: Nella formula sopra,
(1) C3:C16 è l'intervallo in cui abbiamo applicato la formula per identificare i duplicati nel passaggio precedente.
(2) SÌ è il valore restituito dalla formula precedente.

Ora otteniamo il numero totale di valori duplicati nella colonna specificata. Il numero totale di duplicati non include la prima occorrenza.

Per contare il numero di tutti i duplicati inclusa la prima occorrenza in Excel, puoi utilizzare una formula array.

1. Seleziona una cella vuota.

2. Inserisci la formula =RIGHE(B3:B16)-SOMMA(SE(CONTA.SE(B3:B16;B3:B16)=1;1;0)).

3. Premi Ctrl + Shift + Invio per ottenere il risultato del conteggio.

Note: Nella formula sopra, B3:B16 è la colonna specificata in cui vogliamo contare i duplicati inclusa la prima occorrenza.

Per semplificare il lavoro e non dover ricordare formule lunghe e complesse, puoi provare la funzione Seleziona celle duplicate e uniche, fornita da Kutools per Excel, per contare rapidamente il numero di valori duplicati nell'elenco o colonna specificata includendo o escludendo la prima occorrenza.

1. Seleziona la colonna in cui vuoi contare il numero di valori duplicati e fai clic su Kutools > Seleziona > Seleziona celle duplicate e uniche.

2. Nella finestra di dialogo Seleziona celle duplicate e uniche, seleziona l'opzione Valori duplicati (Escludi la prima corrispondenza) o Valori duplicati (Includi la prima corrispondenza) secondo necessità e fai clic su Ok.

3. Ora tutti i valori duplicati inclusa o esclusa la prima occorrenza sono selezionati e contemporaneamente appare una finestra di dialogo che mostra quante celle sono state selezionate. Vedi screenshot sopra.

Supponiamo di voler confrontare due elenchi di nomi e contare il numero di duplicati tra di essi. Come risolvere rapidamente questo problema? Possiamo farlo anche con una formula in Excel.

1. Seleziona una cella vuota.

2. Inserisci la formula =SOMMA.PRODOTTO(--(VAL.NUMERO(CONFRONTA(B3:B12;D3:D18;0)))).

3. Premi il tasto Invio .

Note: Nella formula sopra,
(1) B3:B12 è la prima colonna di nomi in cui vuoi contare i duplicati.
(2) D3:D18 è la seconda colonna di nomi in base alla quale vuoi contare i duplicati.

In alternativa, possiamo utilizzare un componente aggiuntivo di terze parti, Kutools per Excel, per contare rapidamente il numero totale di celle duplicate tra due colonne.

1. Fai clic su Kutools > Seleziona > Seleziona celle uguali e diverse.

2. Nella finestra di dialogo Seleziona celle uguali e diverse,
(1) Specifica le due colonne nella Trova valori in e Secondo caselle separatamente.
(2) Seleziona l'opzione Per cella opzione.
(3) Seleziona l'opzione Stesso valore opzione.

4. Fai clic sul pulsante Ok.

Ora tutte le celle duplicate nella prima colonna sono selezionate e contemporaneamente appare una finestra di dialogo che mostra quante celle duplicate sono state selezionate. Vedi screenshot:

Note: Questa funzione conterà il numero totale di valori duplicati nella colonna che hai specificato nella casella Trova valori in nella finestra di dialogo Seleziona celle uguali e diverse. Se vuoi contare il numero totale di valori duplicati nella seconda colonna, applica nuovamente la funzione Seleziona celle uguali e diverse specificando la seconda colonna nella casella Trova valori in.

Puoi contare rapidamente ogni valore duplicato una sola volta con una formula come segue:

1. Seleziona una cella vuota.

2. Inserisci la formula =SOMMA.PRODOTTO((C3:C19<>"")/CONTA.SE(C3:C19;C3:C19&""))

3. Premi il tasto Invio per ottenere il risultato del conteggio.

Note: Nella formula sopra, C3:C19 è la colonna specificata in cui vuoi contare ogni valore duplicato una sola volta.

Quando conti un elenco, puoi contare ogni valore duplicato una sola volta distinguendo tra maiuscole e minuscole applicando una formula array in Excel.

1. Seleziona una cella vuota.

2. Inserisci la formula array =SOMMA(SE.ERRORE(1/SE(C3:C19<>""; FREQUENZA(SE(ESATTO(C3:C19; TRASPOSTA(C3:C19)); CONFRONTA(RIGA(C3:C19); RIGA(C3:C19)); ""); CONFRONTA(RIGA(C3:C19); RIGA(C3:C19)));0);0)).

3. Premi contemporaneamente i tasti Ctrl + Shift + Invio per ottenere il risultato del conteggio.

Note: Nella formula array sopra, C3:C19 è la colonna specificata in cui vuoi contare ogni serie di duplicati una sola volta distinguendo tra maiuscole e minuscole.

Se hai installato Kutools per Excel, puoi anche utilizzare la funzione Estrai le celle univoche in un intervallo (incluso il primo duplicato) per contare rapidamente ogni serie di valori duplicati una sola volta in Excel.

1. Seleziona una cella vuota.

2. Fai clic su Kutools > Assistente formula > Conteggio > Estrai le celle univoche in un intervallo (incluso il primo duplicato).

3. Nella finestra di dialogo Assistente formula, specifica la colonna in cui vuoi contare i duplicati una sola volta nella casella Intervallo e fai clic su Ok.

Il risultato del conteggio viene immediatamente inserito nella cella selezionata.

Possiamo utilizzare la funzione Subtotale per contare ogni serie di valori duplicati in una colonna in Excel.

1. Seleziona la colonna in cui vuoi contare ogni serie di valori duplicati e fai clic su Dati > Ordina dalla A alla Z o Ordina dalla Z alla A.

2. Nella finestra di dialogo Avviso di ordinamento che si apre, seleziona l'opzione Espandi selezione e fai clic sul pulsante Ordina.

Ora vedrai che la selezione è stata ordinata in base ai valori duplicati della colonna specificata.

3. Mantieni la selezione selezionata e fai clic su Dati > Subtotale.

4. Nella finestra di dialogo Subtotale,
(1) Seleziona la colonna specificata dal menu a discesa Ad ogni cambiamento in ;
(2) Seleziona Conteggio dal menu a discesa Usa funzione ;
(3) Seleziona solo la colonna specificata nella casella Aggiungi subtotale a ;
(4) Fai clic sul OK pulsante.

Ora vedrai che ogni serie di valori duplicati è stata conteggiata e il risultato del conteggio è aggiunto sotto ogni serie di valori duplicati, vedi screenshot sopra.

Possiamo anche creare una tabella pivot per contare rapidamente ogni serie di valori duplicati in Excel.

1. Seleziona l'intervallo che contiene la colonna specificata e fai clic su Inserisci > Tabella pivot.

2. Nella finestra di dialogo Crea tabella pivot, specifica la posizione in cui inserire la nuova tabella pivot e fai clic su OK.

3. Nel riquadro Campi tabella pivot, trascina la colonna specificata sia nella sezione Righe che nella sezione Valori . Ora vedrai che ogni serie di valori duplicati nella colonna specificata è conteggiata in blocco. Vedi screenshot:

Se hai già installato Kutools per Excel, puoi utilizzare la sua funzione Unione avanzata righe per contare rapidamente ogni serie di valori duplicati nella colonna specificata.

Note: La funzione Unione avanzata righe modificherà l'intervallo selezionato e rimuoverà le righe in base ai valori duplicati nella colonna chiave specificata. Per salvaguardare i tuoi dati, si consiglia di eseguire un backup o copiare i dati in un'altra posizione prima di procedere.

1. Aggiungi una colonna vuota a destra dell'intervallo dati originale e nomina la nuova colonna come Conteggio.

2. Seleziona l'intervallo dati originale e la nuova colonna insieme, quindi fai clic su Kutools > Unisci e dividi > Unione avanzata righe.

3. Nella finestra di dialogo Unione avanzata righe,
(1) Fai clic per selezionare la colonna specificata in cui vuoi contare ogni serie di valori duplicati e fai clic su Colonna chiave.
(2) Fai clic per selezionare la nuova colonna (Conteggio), quindi fai clic su Calcola > Conteggio.
(3) Specifica tipi di combinazione o calcolo per le altre colonne se necessario.
(4) Fai clic su Ok pulsante.

Ora vedrai che ogni serie di valori duplicati nella colonna specificata è conteggiata in blocco. Vedi screenshot:

Supponiamo che ci sia un elenco di frutti in una colonna. All'interno dell'elenco, alcuni frutti appaiono più volte. Ora devi contrassegnare ogni frutto duplicato nell'ordine in cui appare, come puoi risolverlo? In questa sezione viene presentata una formula per ottenere facilmente il risultato in Excel.

1. Aggiungi una colonna vuota a destra dei dati originali.

2. Inserisci la formula =IF(COUNTIF($C$3:$C$14,C3)>1,COUNTIF(C$3:C3,C3),"") nella prima cella della colonna aggiunta.

3. Trascina il quadratino di riempimento automatico di questa cella per copiare la formula nelle altre celle.

Note: Nella formula sopra,
(1) $C$3:$C$14 è la colonna specificata in cui vuoi contare i valori duplicati in ordine.
(2) C3 è la prima cella della colonna specificata.
(3) Se il valore corrispondente è duplicato, questa formula restituirà il numero di sequenza1,2,3… in base all'ordine di apparizione; se il valore è unico, la formula restituirà vuoto.

Puoi utilizzare la funzione Rimuovi duplicati per rimuovere direttamente tutti i valori duplicati eccetto la prima occorrenza.

1. Seleziona la colonna in cui vuoi rimuovere tutti i valori duplicati eccetto la prima occorrenza.

2. Fai clic su Dati > Rimuovi duplicati.

3. Nella finestra di dialogo Avviso Rimuovi duplicati, seleziona l'opzione Continua con la selezione corrente e fai clic sul pulsante Rimuovi duplicati.

Suggerimenti: Per rimuovere le righe in base ai valori duplicati nella selezione, seleziona l'opzione Espandi selezione .

4. Nella finestra di dialogo Rimuovi duplicati, seleziona solo la colonna specificata e fai clic su OK.

Suggerimenti: Se hai selezionato l'opzione Espandi selezione nel passaggio precedente, tutte le colonne verranno elencate qui. Tuttavia, devi selezionare solo la colonna specificata.

5. Verrà visualizzata una finestra di dialogo che mostra quanti valori duplicati sono stati rimossi. Fai clic su OK per chiuderla.

Puoi anche utilizzare la funzione Filtro avanzato per rimuovere facilmente tutti i valori duplicati dalla colonna specificata.

1. Fai clic su Dati > Avanzate.

2. Nella finestra di dialogo Filtro avanzato,
(1) Seleziona l'opzione Copia in un'altra posizione ;
(2) Nella casella Intervallo elenco , seleziona la colonna specificata da cui vuoi rimuovere i valori duplicati;
(3) Nella casella Copia in , specifica l'intervallo in cui incollare la colonna;
(4) Seleziona Solo record univoci opzione.
(5) Fai clic su OK pulsante.

Ora vedrai che la colonna specificata è stata incollata nell'intervallo specificato con tutti i valori duplicati rimossi eccetto la prima occorrenza. Vedi screenshot:

Puoi anche utilizzare una macro VBA per rimuovere rapidamente i valori duplicati eccetto la prima occorrenza da una colonna in Excel.

1. Premi i tasti Alt + F11 per aprire la finestra Microsoft Visual Basic for Application.

2. Fai clic su Inserisci > Modulo, quindi incolla il codice VBA qui sotto nella nuova finestra del modulo.

VBA: Rimuovi valori duplicati eccetto la prima occorrenza

Sub ExtendOffice_RemoveAllDeplicate()
Dim xRg As Range
Dim xURg, xFRg, xFFRg As Range
Dim xI, xFNum, xFFNum As Integer
Dim xDc As Object
Dim xDc_keys
Dim xBol As Boolean
Dim xStr As String
Dim xWs As Worksheet
Dim xURgAddress As String
On Error Resume Next
Set xRg = Application.InputBox("Select range:", "Kutools for Excel", "", , , , , 8)
If xRg Is Nothing Then Exit Sub
Set xURg = Intersect(xRg.Worksheet.UsedRange, xRg)
Set xWs = xURg.Worksheet
Set xDc = CreateObject("scripting.dictionary")
xURgAddress = xURg.Address
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
For xFNum = 1 To xURg.Count
Set xFRg = xURg.Item(xFNum)
If (Not IsError(xFRg)) Then
If xFRg.Value <> "" And (Not IsError(xFRg)) Then
For xFFNum = xFNum + 1 To xURg.Count
Set xFFRg = xURg.Item(xFFNum)
If Not IsError(xFFRg) Then
If xFFRg.Value = xFRg.Value Then
xDc(xFFRg.Address) = ""
End If
End If
Next
End If
End If
Next
xStr = ""
xDc_keys = xDc.Keys

For xI = 1 To UBound(xDc_keys)
If xStr = "" Then
xStr = xDc_keys(xI)
Set xURg = xWs.Range(xStr)
Else
xStr = xStr & "," & xDc_keys(xI)
Set xURg = Application.Union(xWs.Range(xDc_keys(xI)), xURg)
End If
Next
Debug.Print xStr
xWs.Activate
xURg.Select
Selection.Delete Shift:=xlUp
xWs.Range(xURgAddress).Select
Application.ScreenUpdating = xBol
End Sub

3. Premi il tasto F5 o fai clic sul pulsante Esegui per avviare questa macro VBA.

4. Nella finestra di dialogo che si apre, specifica l'intervallo da cui vuoi rimuovere i valori duplicati e fai clic su OK.

Tutti i valori duplicati, eccetto la prima occorrenza, vengono rimossi immediatamente dall'intervallo specificato.

Nota: Questo codice VBA è sensibile alle maiuscole/minuscole.

Possiamo evidenziare tutti i valori duplicati inclusa la prima occorrenza in una colonna o elenco tramite una regola di formattazione condizionale, quindi filtrare tutti i valori duplicati tramite il colore di evidenziazione. Successivamente, possiamo selezionare tutte le celle duplicate filtrate e rimuoverle in blocco.

1. Applica la formattazione condizionale per evidenziare i valori duplicati. (Clicca per vedere come)

2. Seleziona la colonna da cui vuoi rimuovere i valori duplicati (inclusa la prima occorrenza) e fai clic su Dati > Filtro.

3. Fai clic sull'icona Filtro nell'intestazione della colonna specificata. Nel menu a discesa, seleziona Filtra per colore, quindi specifica il colore di evidenziazione dal sottomenu.

Ora tutti i valori duplicati sono filtrati.

4. Seleziona tutte le celle filtrate, fai clic con il tasto destro e seleziona Elimina riga dal menu contestuale. Nella finestra di dialogo di conferma che si apre, fai clic su OK per procedere.

5. Ora tutti i valori duplicati sono stati rimossi in blocco. Mantieni selezionato l'elenco filtrato e fai clic su Filtro > Dati di nuovo per annullare il filtro.

A questo punto, vedrai che tutte le celle duplicate inclusa la prima occorrenza sono state rimosse in blocco e sono rimasti solo i valori unici.

Note: Questo metodo rimuoverà le righe in base ai valori duplicati nella colonna specificata.

Possiamo anche utilizzare una formula per identificare i valori duplicati inclusa la prima occorrenza in una colonna di supporto, quindi filtrare i valori duplicati tramite i risultati della formula e infine rimuovere questi valori duplicati filtrati in blocco.

1. Aggiungi una colonna di supporto accanto alla colonna specificata, inserisci la formula =COUNTIF($B$3:$B$11,B3) nella prima cella della colonna di supporto e trascina il quadratino di riempimento automatico verso il basso per copiare la formula nelle altre celle. Vedi screenshot:

Nota: Nella formula sopra, $B$3:$B$11 è la colonna specificata da cui vuoi rimuovere i valori duplicati e B3 è la prima cella della colonna specificata.

2. Seleziona la colonna di supporto e fai clic su Dati > Filtro.

3. Fai clic sull'icona del filtro nell'intestazione della colonna di supporto, quindi nel menu a discesa seleziona tutti i valori tranne 1, e fai clic su OK pulsante. Vedi screenshot:

4. Ora tutti i valori duplicati sono filtrati. Seleziona le celle filtrate nella colonna di supporto, fai clic con il tasto destro e seleziona Elimina riga dal menu contestuale.

5. Nella finestra di dialogo di conferma che si apre, fai clic su OK per procedere.

6. Ora tutti i valori duplicati e le relative righe sono stati rimossi in blocco. Procedi facendo clic su Dati > Filtro di nuovo per annullare il filtro.

Ora vedrai che tutti i valori duplicati inclusa la prima occorrenza sono stati eliminati in blocco.

Se hai installato Kutools per Excel, puoi anche utilizzare la funzione Seleziona celle duplicate e uniche per selezionare e eliminare rapidamente i valori duplicati inclusa o esclusa la prima occorrenza in Excel.

1. Seleziona la colonna da cui vuoi rimuovere i duplicati.

2. Fai clic su Kutools > Seleziona > Seleziona celle duplicate e uniche.

3. Nella finestra di dialogo Seleziona celle duplicate e uniche, seleziona l'opzione Valori duplicati (Includi la prima corrispondenza) e fai clic su Ok.

Note:
(1) Per selezionare ed eliminare i valori duplicati esclusa la prima occorrenza, seleziona l'opzione Valori duplicati (Escludi la prima corrispondenza) opzione.
(2) Per selezionare ed eliminare le righe in base ai valori duplicati nella colonna specificata, seleziona l'opzione Seleziona intera riga opzione.
(3) Per selezionare ed eliminare i valori duplicati distinguendo tra maiuscole e minuscole, seleziona l'opzione Sensibile alle maiuscole/minuscole opzione.
(4) Per selezionare, evidenziare ed eliminare celle o righe duplicate, seleziona le opzioni Colore di sfondo di riempimento o Colore del testo di riempimento e specifica i colori di riempimento o testo secondo necessità.

4. Verrà visualizzata una finestra di dialogo che mostra quante celle sono state selezionate, fai clic su OK per chiuderla.

5. Fai clic con il tasto destro sulle celle selezionate e seleziona Elimina dal menu contestuale.

6. Nella finestra di dialogo Elimina che si apre, seleziona l'opzione Sposta celle in alto e fai clic su OK.

A questo punto, tutti i valori duplicati inclusa la prima occorrenza sono stati rimossi in blocco.

Nella maggior parte dei casi, identifichiamo i valori duplicati in una colonna e poi rimuoviamo l'intera riga in base ai valori duplicati. In realtà, questa operazione è molto simile alla rimozione dei valori duplicati da una singola colonna. Di conseguenza, possiamo utilizzare soluzioni simili per eliminare le righe in base ai duplicati nella colonna specificata.

Il primo metodo consiste nell'utilizzare la funzione integrata Rimuovi duplicati per eliminare le righe in base ai duplicati nella colonna specificata. Basta selezionare l'intervallo da cui vuoi rimuovere le righe, fare clic su Dati > Rimuovi duplicati per abilitare la funzione, selezionare solo la colonna specificata nella finestra di dialogo Rimuovi duplicati e poi fare clic su OK per completare l'operazione.

Possiamo anche utilizzare la Formattazione condizionale e la funzione Filtro per eliminare le righe in base ai valori duplicati nella colonna specificata. Prima di tutto, evidenzia le righe in base ai valori duplicati in una determinata colonna tramite una regola di formattazione condizionale (clicca per vedere come). In secondo luogo, filtra l'intervallo per colore. In terzo luogo, elimina facilmente tutte le righe filtrate. Infine, cancella o annulla il filtro e vedrai che sono rimaste solo le righe con valori unici nella colonna specificata.

In alternativa, puoi aggiungere una colonna di supporto e applicare la formula =COUNTIF($C$3:$C$21,C3) per identificare i duplicati nella colonna specificata. Poi filtra i numeri maggiori di1 nella colonna di supporto ed elimina facilmente tutte le righe filtrate. Dopo aver cancellato il filtro, vedrai che sono rimaste solo le righe con valori unici nella colonna specificata.

Il componente aggiuntivo di terze parti Kutools per Excel offre anche una funzione straordinaria, Seleziona celle duplicate e uniche, per selezionare rapidamente le righe in base ai valori duplicati nella colonna specificata e poi rimuovere facilmente queste righe selezionate tramite il menu contestuale con il tasto destro.

La funzione Unione avanzata righe di Kutools per Excel può anche rimuovere rapidamente le righe in base ai valori duplicati nella colonna chiave specificata.

Possiamo aggiungere una colonna di supporto e applicare una formula per identificare i valori duplicati tra due colonne, quindi filtrare ed eliminare facilmente i valori duplicati.

1. Aggiungi una colonna vuota accanto alla colonna specificata da cui vuoi rimuovere i valori duplicati.

2. Nella prima cella della colonna di supporto (esclusa l'intestazione), digita la formula =IF(ISERROR(MATCH(C2,$A$2:$A$13,0)),"Unico","Duplicato") e trascina il quadratino di riempimento automatico verso il basso per copiare la formula nelle altre celle.

Note: Nella formula sopra,
(1) C2 è la prima cella della colonna specificata da cui vuoi rimuovere i valori duplicati;
(2) $A$2:$A$13 è l'altra colonna con cui dobbiamo confrontare.
(3) Questa formula restituirà Duplicato se il valore corrispondente è duplicato rispetto ai valori dell'altra colonna e restituirà Unico se diverso dai valori dell'altra colonna.

3. Seleziona la colonna di supporto e fai clic su Dati > Filtro.

4. Fai clic sull'icona del filtro nell'intestazione della colonna di supporto, quindi nel menu a discesa seleziona solo Duplicato, e fai clic su OK pulsante.

5. Ora tutti i valori duplicati sono filtrati. Seleziona le celle filtrate, fai clic con il tasto destro e seleziona Elimina riga dal menu contestuale. Quindi fai clic su OK nella finestra di dialogo di conferma che si apre.

6. Ora tutti i valori duplicati sono stati rimossi dalla colonna specificata. Procedi facendo clic su Dati > Filtro di nuovo per annullare il filtro.

Ora vedrai che sono rimasti solo i valori unici nella colonna specificata. Puoi rimuovere la colonna di supporto secondo necessità.

Note: Questo metodo rimuoverà l'intera riga in base ai valori duplicati nella colonna specificata.

Se hai installato Kutools per Excel, puoi utilizzare la funzione straordinaria Seleziona celle uguali e diverse per selezionare rapidamente i valori duplicati tra due colonne e poi rimuoverli facilmente.

1. Fai clic su Kutools > Seleziona > Seleziona celle uguali e diverse per abilitare questa funzione.

2. Nella finestra di dialogo Seleziona celle uguali e diverse, specifica entrambe le colonne nelle caselle Trova valori in e Secondo separatamente, seleziona le opzioni Per cella e Stesso valore e fai clic su Ok. Vedi screenshot:

3. Ora tutti i valori duplicati tra le due colonne sono selezionati nella prima colonna (quella specificata nella casella Trova valore in). E fai clic su OK nella finestra di dialogo che si apre.

4. Puoi premere il tasto Canc per rimuovere direttamente questi valori duplicati oppure fare clic con il tasto destro e selezionare Elimina dal menu contestuale.