Trova, evidenzia, filtra, conta ed elimina i duplicati in Excel

Quando si cercano celle duplicate in una singola colonna o elenco, è possibile utilizzare la funzione CONTA.SE per individuare e contare rapidamente i dati duplicati in modo semplice.
1. Seleziona una cella vuota accanto alla colonna in cui desideri trovare i duplicati.

2. Digita la formula =CONTA.SE($C$3:$C$12; C3)>1, quindi premi il tasto Invio.

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

Nota: Nella formula =COUNTIF($C$3:$C$12, C3)>1,
(1) $C$3:$C$12 è la colonna o l'elenco in cui cercherai valori duplicati. Poiché la colonna rimane fissa quando copi la formula in altre celle, si tratta generalmente di un riferimento assoluto, indicato dal simbolo «$».
(2) C3 è la prima cella della colonna specificata. È un riferimento relativo, perché deve aggiornarsi automaticamente quando copi la formula in altre celle.
(3) Questa formula restituisce VERO o FALSO. VERO indica che il valore corrispondente è duplicato, mentre FALSO indica che è unico nella colonna.
(4) A volte, i risultati VERO o FALSO potrebbero non essere immediatamente chiari. Puoi combinare la formula originale con la funzione SE per ottenere direttamente Duplicati:
=IF(COUNTIF($C$3:$C$12, C3)>1,«Duplicates»,«»)

In alcuni casi, devi confrontare due colonne per individuare i valori duplicati. Ad esempio, hai due elenchi di nomi e desideri trovare i nomi presenti nel secondo elenco che sono già contenuti nel primo. Puoi ottenere facilmente il risultato desiderato combinando le funzioni CERCA.VERT e SE.ERRORE.

1. Seleziona una cella vuota accanto al secondo elenco 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 con la formula per copiarla nelle altre celle, secondo necessità.

Nota: Nella formula precedente,
(1) D3 è la prima cella del secondo elenco nomi. Il riferimento deve cambiare automaticamente quando copiamo la formula in altre celle; pertanto, è relativo.
(2) $B$3:$B$18 è l’elenco dei nomi. Si tratta di un riferimento assoluto perché l’intervallo deve rimanere fisso quando copiamo la formula in altre celle.
(3) Quando un nome è duplicato rispetto a quelli del primo elenco, la formula restituisce il nome stesso; altrimenti restituisce una cella vuota.
(4) Puoi anche utilizzare la formula =IF(COUNTIF($B$3:$B$18,D3)>0,«Duplicates»,«») per individuare i nomi duplicati nel secondo elenco rispetto al primo. Questa formula restituirà «Duplicati» se il nome corrispondente è duplicato.

(5) Se devi confrontare due colonne presenti in due fogli diversi, ti basta aggiungere il nome del foglio prima del riferimento alla colonna da confrontare. Nel nostro esempio, basta sostituire $B$3:$B$18 con Foglio1!$B$3:$B$18 nella formula.

Le formule illustrate in precedenza non distinguono tra maiuscole e minuscole durante la ricerca dei duplicati: ad esempio, «mela» viene considerata un duplicato di «MELA». Per individuare i valori duplicati in una singola colonna tenendo conto delle differenze tra maiuscole e minuscole, puoi utilizzare una formula matriciale.

1. Seleziona una cella vuota accanto alla colonna in cui eseguirai la ricerca dei duplicati.

2. Digita la formula matriciale =IF(SUM((--EXACT($C$3:$C$12,C3)))<=1,«»,«Duplicate») e premi contemporaneamente i tasti Ctrl + Maiusc + Invio.

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

Nota: Nella formula matriciale precedente,
(1) $C$3:$C$12 è la colonna in cui devi individuare i valori duplicati. Il riferimento è assoluto perché deve rimanere fisso quando copi la formula matriciale in altre celle.
(2) C4 è la prima cella della colonna. Il riferimento è relativo perché deve aggiornarsi automaticamente quando copi la formula matriciale in altre celle.
(3)Se il valore corrispondente è duplicato, la formula matriciale restituisce «Duplicato»; in caso contrario, restituisce una cella vuota.

Puoi utilizzare la funzionalità Utilizza la 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 > Utilizza la formattazione condizionale > Evidenzia regole celle > Valori duplicati. Vedi lo screenshot seguente:

3. Nella finestra di dialogo Valori duplicati visualizzata, seleziona Duplicati dal primo elenco a discesa, scegli uno stile di evidenziazione dal secondo elenco a discesa e fai clic sul pulsante OK.

Nota: se gli stili di evidenziazione predefiniti non soddisfano le tue esigenze, puoi selezionare Formato personalizzato dal secondo elenco a discesa e quindi scegliere il colore di evidenziazione, il carattere e i bordi delle celle desiderati nella finestra di dialogo Imposta formato cella visualizzata.

Vedrai quindi tutte le celle duplicate evidenziate nella colonna selezionata, come mostrato nello screenshot seguente.

Note:
(1) Dopo aver evidenziato le celle duplicate, puoi filtrarle in un attimo. ()Fai clic per visualizzare come)
(2) Dopo aver evidenziato le celle duplicate, puoi anche eliminarle in blocco con facilità. ()Fai clic per visualizzare come)

Alcuni utenti preferiscono Intervallo riga evidenziata in base alle celle duplicate presenti in una determinata colonna. In questo caso, possiamo creare una regola Utilizza la formattazione condizionale personalizzata per ottenere il risultato desiderato.

1. Seleziona l’intervallo (escludendo la riga di intestazione) su cui applicherai l’Intervallo riga evidenziata in base alle celle duplicate.

2. Fai clic su Home > Utilizza la formattazione condizionale > Nuova regola.

3. Nella finestra di dialogo Nuova regola di formattazione,
(1) seleziona l’opzione Utilizza una formula per determinare le celle da formattare;
(2) digita la formula =COUNTIF($C$3:$C$12,$C3)>1 nella casella Formatta i valori per cui questa formula è vera;
Suggerimento: nella formula, $C$3:$C$12 è la colonna che contiene le celle duplicate e $C3 è la prima cella della colonna.
(3) fai clic sul pulsante Formato.

4. Nella finestra di dialogo Imposta formato cella, imposta il colore di riempimento, il carattere e i bordi desiderati, quindi fai clic sui pulsanti OK per salvare le impostazioni.

A questo punto, nell’Seleziona intervallo, le righe vengono evidenziate in base alle celle duplicate presenti nella colonna specificata. Vedi lo screenshot:

Per evidenziare le righe duplicate in un determinato intervallo, puoi utilizzare la funzionalità Utilizza la formattazione condizionale.

1. Seleziona l’intervallo, escludendo la riga di intestazione.

2. Fare clic su Home > Utilizza la formattazione condizionale > Nuova regola.

3. Nella finestra di dialogo Nuova regola di formattazione:
(1) Fare clic per selezionare l’opzione Utilizza una formula per determinare le celle da formattare;
(2) Nella casella Formatta i valori per cui la formula è vera, digitare la formula =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1;
(3) Fare clic sul pulsante Formato.

Nota: 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 di questa colonna;
(3) $D$3:$D$12 è la terza colonna dell'intervallo e $D3 è la prima cella di questa colonna;
(4) Se nel proprio intervallo sono presenti ulteriori colonne, è possibile aggiungere successivamente alla formula i riferimenti alle colonne e le relative prime celle.

4. Nella finestra di dialogo Imposta formato cella, specifica il colore evidenziatore, il carattere, i bordi delle celle e così via in base alle tue esigenze, quindi fai clic in successione sui pulsanti OK per salvare l’impostazione.

Finora, i Riga duplicata sono stati identificati ed evidenziati in Seleziona intervallo. Vedere lo screenshot:

Avrete probabilmente notato che tutti i duplicati vengono identificati o evidenziati con i metodi descritti sopra. A volte, però, potreste voler visualizzare solo gli elementi duplicati, escludendo la prima occorrenza. È possibile ottenere questo risultato anche con la funzionalità Utilizza la formattazione condizionale e una formula diversa.

1. Seleziona la colonna con l'intestazione.
Suggerimento: se vuoi evidenziare le righe duplicate in base ai valori di una singola colonna, escludendo la prima occorrenza, seleziona l'intervallo senza la riga di intestazione.

2. Fai clic su Home > Utilizza la formattazione condizionale > Nuova regola.

3. Nella finestra di dialogo Nuova regola di formattazione:
(1) Fare clic per selezionare l'opzione Utilizza una formula per determinare le celle da formattare;
(2) Nella casella Formatta i valori per cui la formula è vera, inserire la formula =COUNTIF($C$3:$C3, C3)>1;
Suggerimenti: per evidenziare l’intera riga in base ai duplicati presenti in una singola colonna, utilizzare la formula =COUNTIF($C$3:$C3, $C3)>1.
(3) Fare clic sul pulsante Formato.

4. Nella finestra di dialogo Imposta formato cella visualizzata, imposta il colore evidenziatore, il carattere, i bordi delle celle e così via in base alle tue esigenze, quindi fai clic sui pulsanti OK per salvare le impostazioni.

A questo punto verranno evidenziate le celle duplicate nella colonna selezionata (o le righe in base ai duplicati nella colonna specificata), escluse le prime occorrenze. Vedere lo screenshot:

Quando si evidenziano celle o righe duplicate con la funzionalità Utilizza la formattazione condizionale, tutti i duplicati vengono contrassegnati con lo stesso colore. Tuttavia, assegnare colori diversi a ciascun gruppo di valori duplicati rende molto più semplice leggere i dati e individuare rapidamente le differenze. In questi casi, una macro VBA può aiutarti a ottenere questo risultato direttamente in Excel.

1. Premere contemporaneamente i tasti Alt+F11 per aprire la finestra Microsoft Visual Basic, Applications Edition.

2. Nella finestra, fai clic su Inserisci > Modulo, quindi incolla il codice seguente 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. Premere il tasto F5 oppure fare clic sull'icona Esegui per eseguire questa macro VBA.

4. Nella finestra di dialogo di Kutools per Excel visualizzata, seleziona la colonna in cui desideri evidenziare le celle duplicate con colori diversi, quindi fai clic sul pulsante OK.

Ogni serie di Valori duplicati verrà ora evidenziata con un colore distinto.

Il primo strumento che ti presentiamo è la funzionalità Seleziona celle duplicate/uniche, offerta da Kutools per Excel. Grazie a questa funzione, individuare rapidamente e facilmente le celle uniche o duplicate non è mai stato così semplice!

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

2. Fare clic su Kutools > Seleziona > Seleziona celle duplicate/uniche.

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

Nota: Nella finestra di dialogo Seleziona celle duplicate/uniche,
(1) Per selezionare o evidenziare tutti i duplicati escludendo la prima occorrenza, scegliere l'opzione Valori duplicati (Escludi la prima corrispondenza). In caso contrario, selezionare l'opzione Valori duplicati (Includi la prima corrispondenza).
(2) Per evidenziare i duplicati, selezionare l'opzione Colore di sfondo e specificare un colore a piacere.
(3) Per selezionare o evidenziare l’intera riga in base ai duplicati nella colonna selezionata, scegliere l'opzione Seleziona Intera Riga.
(4) Per selezionare o evidenziare valori duplicati distinguendo tra maiuscole e minuscole, attivare l'opzione Maiuscole/minuscole.

Kutools per Excel offre anche uno strumento eccezionale – Confronta Celle – per aiutarti a individuare ed evidenziare facilmente le celle duplicate in due colonne.

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

2. Nella finestra di dialogo Seleziona celle uguali/diverse, specifica le due colonne nelle caselle Trova valori in e Secondo, seleziona l'opzione Valori uguali e imposta eventuali altre opzioni in base alle tue esigenze.

Note:
(1) Se è necessario trovare Riga duplicata, selezionare l'opzione Ogni riga; per trovare celle duplicate, selezionare l'opzione Singola cellanella sezione In base a;
(2) Selezionare l'opzione Colore di sfondoe specificare un colore di evidenziazione se si desidera evidenziare le Riga duplicata o le celle;
(3) Selezionare l'opzione Seleziona Intera Rigase si desidera selezionare o evidenziare le Riga intera in base ai duplicati;
(4) Selezionare l'opzione Maiuscole/minuscolese si desidera trovare o evidenziare i duplicati distinguendo tra maiuscole e minuscole.

3. Fare clic successivamente sui pulsanti OK per completare le impostazioni.

A questo punto, i duplicati nella colonna specificata nella casella Trova valori in verranno identificati ed evidenziati.

Questo metodo consente di identificare ed evidenziare le celle duplicate mediante una regola Utilizza la formattazione condizionale, per poi filtrarle facilmente in Excel in base al colore di evidenziazione.

1. Applica la formattazione condizionale per trovare ed evidenziare i duplicati nella colonna specificata. ()Fare clic per visualizzare come)

2. Fare clic sull'intestazione della colonna per selezionarne tutte le celle, quindi su Dati > Filtro.

3. Fai clic sull'icona del filtro nell'intestazione della colonna, seleziona Filtra per colore, quindi scegli il colore con la formattazione condizionale specificata nel menu a discesa. Guarda lo screenshot:

A questo punto verranno visualizzate soltanto le righe contenenti celle duplicate. Vedere lo screenshot:

In alternativa, è possibile utilizzare una formula in una colonna di appoggio per identificare i duplicati e filtrarli facilmente in Excel.

1. Oltre ai dati originali, aggiungi una colonna di appoggio e digita Duplicati come intestazione di colonna.

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

Nota: Nella formula indicata sopra, $C$3:$C$12 è la colonna contenente i dati duplicati e C3 è la prima cella (esclusa quella dell'intestazione) della colonna.

3. Fai clic sull'intestazione – Duplicati, quindi su Dati > Filtro.

4. Fare quindi clic sull'icona del filtro nell'intestazione della colonna, selezionare solo Duplicati e fare clic sul pulsante OK. Vedere lo screenshot:

A questo punto vengono visualizzate soltanto le righe contenenti valori duplicati. Vedere lo screenshot:

In generale, puoi utilizzare la funzione =CONTA.SE(intervallo; criterio)per contare quante volte un determinato valore appare in un intervallo specifico. Ad esempio, per sapere quante volte compare «Mela» nell'elenco A2:A10, basta usare la formula =COUNTIF(A2:A10, «Apple») e ottenere immediatamente il numero di occorrenze di quel valore.

Tuttavia, la formula =CONTA.SE(intervallo; criterio) conta soltanto un valore duplicato specifico. Come si fa invece a contare i valori duplicati con due o più criteri? E se fosse necessario contare i duplicati con distinzione tra maiuscole e minuscole applicando dei criteri? I metodi seguenti consentono di risolvere questi problemi.

È possibile applicare una formula matriciale per contare i Valori duplicati con distinzione tra maiuscole e minuscole e con criteri in Excel. Ad esempio, per contare quante volte compare il valore «Mela» nell'elenco B2:B21 con Maiuscole/minuscole, procedere come segue:

1. Seleziona una cella vuota.

2. Immettere la formula =SOMMA(--IDENTICO(B2:B20;E2)).

3. Premere contemporaneamente i tasti Ctrl + Maiusc + Invio per ottenere il risultato del conteggio.

Note: Nella formula matriciale,
(1)B2:B20è la colonna in cui si desidera contare i duplicati.
(2)E2è la cella contenente il valore specifico di cui si vuole contare il numero di occorrenze. È possibile sostituire il riferimento di cella con il valore racchiuso tra virgolette, ad esempio «Mela».

A volte potrebbe essere necessario contare i duplicati in base a due o più criteri. In questi casi, puoi utilizzare la funzione CONTA.PIÙ.SE per ottenere il risultato desiderato.
Ad esempio, considera la tabella delle vendite di frutta mostrata nello screenshot seguente: vuoi contare quante volte compare la mela venduta il 7/5/2020 con un importo superiore a 300. Ecco come contare i duplicati applicando questi criteri:

1. Seleziona una cella vuota.

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

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

Nota: Nella formula indicata sopra,
(1) B3:B20 è la (prima) colonna delle date e G4 è il criterio relativo alla data;
(2) C3:C20 è la (seconda) colonna della frutta e G3 è il criterio relativo alla frutta;
(3) D3:D20 è la (terza) colonna degli importi e «>300» è il criterio relativo all'importo.
(4) Se nella tabella sono presenti ulteriori colonne e criteri, puoi aggiungere i relativi riferimenti e criteri.

Per contare tutti i duplicati in una colonna escludendo la prima occorrenza, procedere come segue:

1. Seleziona una cella vuota accanto alla colonna.

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

Nota: Nella formula indicata sopra,
(1) $B$3:B3 è l'intervallo in cui vengono contati i duplicati. In $B$3:B3, B3 si aggiorna automaticamente quando si copia la formula nelle altre celle.
(2) B3 è la prima cella della colonna specificata.
(3) Questa formula restituisce «SÌ» o una cella vuota: «SÌ» indica che il valore corrispondente è un duplicato, mentre una cella vuota indica un valore univoco.

A questo punto tutti i duplicati nella colonna specificata sono stati identificati. È possibile contare i risultati della formula per ottenere il numero totale di duplicati.

3. Seleziona una cella vuota.

4. Inserisci la formula =COUNTIF(C3:C16,«YES»), quindi premi il tasto Invio.

Nota: Nella formula precedente,
(1) C3:C16 è l'intervallo a cui abbiamo applicato la formula per Contrassegna Duplicati nel passaggio precedente.
(2) YES è il valore restituito dall'ultima formula.

Otteniamo così il numero totale di Valori duplicati all'interno della colonna specificata. Il numero totale di duplicati non include le prime occorrenze.

Per contare il numero di tutti i duplicati, incluse le prime occorrenze in Excel, è possibile applicare una formula matriciale per ottenere il risultato.

1. Seleziona una cella vuota.

2. Inserisci la formula =ROWS(B3:B16)-SUM(IF(COUNTIF(B3:B16,B3:B16)=1,1,0)).

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

Nota: Nella formula precedente, B3:B16 è la colonna specificata in cui si desidera contare i duplicati, incluse le prime occorrenze.

Per semplificarti il lavoro ed evitare di dover memorizzare lunghe e noiose formule, prova la funzionalità Seleziona celle duplicate/uniche di Kutools per Excel: ti permette di contare rapidamente i valori duplicati nell’elenco o nella colonna specificata, includendo o escludendo le prime occorrenze.

1. Seleziona la colonna in cui desideri contare i valori duplicati e fai clic su Kutools > Seleziona > Seleziona celle duplicate/uniche.

2. Nella finestra di dialogo Seleziona celle duplicate/uniche, seleziona l'opzione Valori duplicati (Escludi la prima corrispondenza) oppure Valori duplicati (Includi la prima corrispondenza) in base alle tue esigenze e fai clic sul pulsante Ok.

3. Verranno selezionati tutti i valori duplicati—sia includendo che escludendo le prime occorrenze—e contemporaneamente apparirà una finestra di dialogo con il numero di celle selezionate. Vedere lo screenshot sopra.

Supponiamo di voler confrontare due Elenco 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 =SUMPRODUCT(--(ISNUMBER(MATCH(B3:B12,D3:D18,0)))).

3. Premere il tasto Invio.

Nota: Nella formula precedente,
(1) B3:B12 è la prima colonna di nomi in cui si desidera contare i duplicati.
(2) D3:D18 è la seconda colonna di nomi su cui basare il conteggio dei duplicati.

In alternativa, puoi usare un componente aggiuntivo di terze parti, Kutools per Excel, per contare in modo facile e veloce il numero totale di celle duplicate tra due colonne.

1. Fare clic su Kutools > Seleziona > Seleziona celle uguali/diverse.

2. Nella finestra di dialogo Seleziona celle uguali e diverse,
(1) Specifica le due colonne nei campi Trova valori in e In base a separatamente.
(2) Seleziona l'opzione Singola cella.
(3) Seleziona l'opzione Valori uguali.

4. Fai clic sul pulsante Ok.

Verranno quindi selezionate tutte le celle duplicate nella prima colonna, e contemporaneamente apparirà una finestra di dialogo indicante il numero di celle duplicate selezionate. Vedere lo screenshot:

Nota: Questa funzionalità conta il numero totale di valori duplicati nella colonna specificata nel campo Trova valori in della finestra di dialogo Seleziona celle uguali/diverse. Per contare il numero totale di valori duplicati nella seconda colonna, riapplica la funzionalità Seleziona celle uguali/diverse, indicando la seconda colonna nel campo Trova valori in.

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

1. Seleziona una cella vuota.

2. Inserisci la formula =SUMPRODUCT((C3:C19<>«»)/COUNTIF(C3:C19,C3:C19&«»)).

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

Nota: Nella formula precedente, C3:C19 è la colonna specificata in cui si desidera contare ogni valore duplicato una sola volta.

Quando si conta un elenco, è possibile contare ogni valore duplicato una sola volta con Maiuscole/minuscole applicando una formula matriciale in Excel.

1. Seleziona una cella vuota.

2. Inserisci la formula matriciale =SUM(IFERROR(1/IF(C3:C19<>«», FREQUENCY(IF(EXACT(C3:C19, TRANSPOSE(C3:C19)), MATCH(ROW(C3:C19), ROW(C3:C19)), «»), MATCH(ROW(C3:C19), ROW(C3:C19))), 0), 0)).

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

Nota: Nella formula matriciale precedente, C3:C19 è la colonna specificata in cui ogni serie di duplicati viene conteggiata una sola volta, distinguendo tra maiuscole e minuscole.

Se hai installato Kutools per Excel, puoi sfruttare la sua funzionalità Conta celle con valori univoci per contare rapidamente ogni serie di valori duplicati una sola volta in Excel.

1. Seleziona una cella vuota.

2. Fare clic su Kutools > Assistente formula > Statistica > Conta il numero di valori univoci in un intervallo (incluso il primo duplicato).

3. Nella finestra di dialogo Assistente formula, nel campo Intervallo, specifica la colonna in cui desideri contare i duplicati una sola volta e fai clic sul pulsante Ok.

Il risultato del conteggio viene immediatamente inserito nella cella selezionata.

Puoi utilizzare la funzionalità Subtotale per contare ogni serie di valori duplicati in una colonna di Excel.

1. Seleziona la colonna in cui vuoi contare ogni serie di valori duplicati e fai clic su Dati > Crescente oppure Ordina Z → A.

2. Nella finestra di avviso Ordina visualizzata, seleziona l'opzione Espandi la selezione, quindi fai clic sul pulsante Ordina.

Vedrai quindi che la selezione è stata ordinata in base al Valori duplicati della colonna specificata.

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

4. Nella finestra di dialogo Subtotale,
(1) seleziona la colonna desiderata dal menu a discesa A ogni variazione in;
(2) scegli Conteggio dal menu a discesa Usa funzione;
(3) seleziona esclusivamente la colonna specificata nella casella Aggiungi subtotale a;
(4) fai clic sul pulsante OK.

Vedrai quindi che ogni serie di valori duplicati è stata conteggiata e il risultato del conteggio è stato aggiunto sotto ciascuna serie di valori duplicati, come mostrato nello screenshot precedente.

È possibile creare anche una Tabella pivot per contare rapidamente ogni serie di valori duplicati in modo semplice in Excel.

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

2. Nella finestra di dialogo Crea Tabella pivot, indica la posizione in cui vuoi inserire la nuova tabella pivot e fai clic sul pulsante OK.

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

Se hai già installato Kutools per Excel, puoi sfruttare la sua funzionalità facile da usare Unione avanzata righe per contare rapidamente ogni serie di valori duplicati nella colonna specificata.

Nota: La funzionalità Unisci righe avanzate modificherà l’intervallo selezionato e rimuoverà le righe in base ai valori duplicati nella colonna chiave specificata. Per salvaguardare i tuoi dati, ti consigliamo di eseguire un backup o di copiare i dati altrove prima di procedere con le operazioni seguenti.

1. Aggiungi una colonna vuota immediatamente a destra dell’intervallo dati originale e assegnale il nome Conteggio.

2. Seleziona contemporaneamente l’Intervallo Dati Originale e la nuova colonna, 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 specifica i cui valori duplicati desideri contare, quindi fai clic su Chiave primaria.
(2) fai clic per selezionare la nuova colonna (Conteggio), quindi fai clic su Calcola > Conteggio.
(3) specifica, se necessario, i tipi di combinazione o calcolo per le altre colonne.
(4) fai clic sul pulsante Ok.

Vedrai quindi che ogni serie di valori duplicati nella colonna specificata viene conteggiata in blocco. Vedi screenshot:

Si supponga di avere un elenco di frutti in una colonna. All'interno dell'elenco, alcuni frutti compaiono più volte. Ora devi contrassegnare ogni frutto duplicato nell'ordine in cui appare: come puoi risolvere il problema? In questa sezione verrà presentata una formula per ottenere facilmente il risultato desiderato in Excel.

1. Aggiungi una colonna vuota immediatamente 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 vuota aggiunta.

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

Nota: Nella formula indicata sopra,
(1) $C$3:$C$14 è la colonna specificata in cui desideri contare i valori duplicati in ordine.
(2) C3 è la prima cella della colonna specificata.
(3) Se il valore corrispondente è duplicato, la formula restituirà un numero di sequenza (1, 2, 3…) in base all’ordine di apparizione; se il valore è unico, la formula restituirà una cella vuota.

Puoi utilizzare la funzionalità Rimuovi duplicati per eliminare direttamente tutti i valori duplicati, tranne la prima occorrenza.

1. Seleziona la colonna da cui vuoi rimuovere tutti i valori duplicati, tranne la prima occorrenza.

2. Fai clic su Dati > Rimuovi duplicati.

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

Suggerimento: per rimuovere le righe in base ai valori duplicati nella selezione, seleziona l’opzione Espandi la selezione.

4. Nella finestra di dialogo Rimuovi duplicati, seleziona esclusivamente la colonna specificata e fai clic sul pulsante OK.

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

5. Verrà visualizzata una finestra di dialogo con il numero di valori duplicati rimossi. Fai clic sul pulsante OK per chiuderla.

Puoi anche utilizzare la funzionalità 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 da cui desideri rimuovere i valori duplicati;
(3) nella casella Copia in, specifica l’intervallo in cui incollare la colonna;
(4) seleziona l’opzione Solo record univoci;
(5) fai clic sul pulsante OK.

Vedrai quindi che la colonna specificata è stata incollata nell’intervallo limitato, con tutti i valori duplicati rimossi tranne la prima occorrenza. Vedi screenshot:

Puoi anche utilizzare una macro VBA per rimuovere rapidamente tutti i Valori duplicati tranne la prima occorrenza da una colonna in Excel.

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

2. Fare clic su Inserisci > Modulo, quindi incollare il seguente codice VBA nella nuova finestra del modulo.

VBA: Rimuovi i valori duplicati tranne 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. Premere il tasto F5 oppure fare clic sul pulsante Esegui per eseguire questo codice VBA.

4. Nella finestra di dialogo visualizzata, specifica l'intervallo da cui desideri rimuovere i valori duplicati e fai clic sul pulsante OK.

Tutti Valori duplicati, tranne le prime occorrenze, vengono immediatamente rimossi da Intervallo limitato.

Nota: questo codice VBA è Maiuscole/minuscole.

È possibile evidenziare tutti Valori duplicati, comprese le prime occorrenze, in una colonna o in un elenco con una regola Utilizza la formattazione condizionale, quindi filtrare tutti Valori duplicati in base al colore di evidenziazione. Successivamente, è possibile selezionare tutte le celle duplicate filtrate e rimuoverle in blocco.

1. Applica la formattazione condizionale per evidenziare i valori duplicati. ()Fare clic per visualizzare come)

2. Seleziona la colonna da cui vuoi rimuovere i valori duplicati (incluse le prime occorrenze) e fai clic su Dati > Filtro.

3. Fai clic sull'icona Filtro nell'intestazione della colonna desiderata. Nel menu a discesa, seleziona Filtra per colore e poi scegli il colore di evidenziazione dal sottomenu.

In questo modo, tutti Valori duplicati vengono filtrati.

4. Seleziona tutte le celle filtrate, fai clic con il tasto destro e scegli Elimina Riga dal menu contestuale. Nella finestra di conferma visualizzata, fai clic sul pulsante OK per procedere.

5. In questo modo, tutti i valori duplicati vengono rimossi in blocco. Mantieni selezionato l'elenco filtrato e fai nuovamente clic su Filtro > Dati per annullare il filtro.

A questo punto, si noterà che tutte le celle duplicate, incluse le prime occorrenze, sono state rimosse in blocco e sono rimasti soltanto i valori univoci.

Nota: questo metodo elimina le righe in base ai valori duplicati presenti nella colonna specificata.

È possibile applicare anche una formula per identificare Valori duplicati, incluse le prime occorrenze, in una colonna di appoggio, filtrare successivamente Valori duplicati in base ai risultati della formula e infine rimuovere in blocco queste Valori duplicati filtrate.

1. Aggiungi una colonna di appoggio accanto a quella specificata, inserisci la formula =CONTA.SE($B$3:$B$11;B3) nella prima cella della colonna di appoggio e trascina il quadratino di riempimento automatico verso il basso per copiare la formula nelle altre celle. Guarda lo screenshot:

Nota: nella formula indicata sopra, $B$3:$B$11 è la colonna specificata da cui si desidera rimuovere i valori duplicati, mentre B3 è la prima cella di tale colonna.

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

3. Fare clic sull'icona Filtro nell'intestazione della colonna di riferimento, quindi nel menu a discesa deselezionare tutti i valori tranne 1 e fare clic sul pulsante OK. Vedere lo screenshot:

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

5. Nella finestra di conferma visualizzata, fai clic sul pulsante OK per procedere.

6. Ora tutti i valori duplicati e le relative righe vengono rimossi in blocco. Per annullare il filtro, fai nuovamente clic su Dati > Filtro.

A questo punto, si noterà che tutti Valori duplicati, incluse le prime occorrenze, sono stati eliminati in blocco.

Se hai installato Kutools per Excel, puoi sfruttare la sua funzione Seleziona celle duplicate/uniche per individuare ed eliminare rapidamente i valori duplicati in Excel, includendo o escludendo le prime occorrenze, in modo semplice e intuitivo.

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

2. Fare clic su Kutools > Seleziona > Seleziona celle duplicate/uniche.

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

Nota:
(1) Per selezionare ed eliminare i valori duplicati escludendo la prima occorrenza, seleziona l’opzione Valori duplicati (escludi la prima corrispondenza).
(2) Per selezionare ed eliminare intere righe in base ai valori duplicati nella colonna specificata, seleziona l’opzione Seleziona intera riga.
(3) Per selezionare ed eliminare i valori duplicati distinguendo tra maiuscole e minuscole, seleziona l’opzione Maiuscole/minuscole.
(4) Per selezionare, evidenziare ed eliminare celle o righe duplicate, seleziona le opzioni Colore di sfondo o Colore del testo e imposta il colore di riempimento o del carattere in base alle tue esigenze.

4. Verrà visualizzata una finestra di dialogo che indica quante celle sono state selezionate; fai clic sul pulsante OK per chiuderla.

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

6. Nella finestra Elimina visualizzata, seleziona l'opzione Sposta celle in alto e fai clic sul pulsante OK.

Finora, tutti Valori duplicati, incluse le prime occorrenze, sono stati rimossi in blocco.

Nella maggior parte dei casi, si identificano Valori duplicati in una colonna e successivamente si rimuovono Riga intera in base a Valori duplicati. In realtà, questa operazione è molto simile alla rimozione di Valori duplicati da una singola colonna. Di conseguenza, è possibile utilizzare soluzioni analoghe per Elimina righe in base ai duplicati presenti nella colonna specificata.

Il primo metodo consiste nell'utilizzare la funzione integrata Rimuovi duplicati per eliminare le righe in base ai valori duplicati presenti nella colonna specificata. Basta selezionare l'intervallo da cui si desidera rimuovere i duplicati, fare clic su Dati > Rimuovi duplicati per attivare la funzione, selezionare esclusivamente la colonna desiderata nella finestra di dialogo Rimuovi duplicati e infine cliccare su OKper completare l'operazione.

Possiamo anche utilizzare la formattazione condizionale e il filtro per rimuovere le righe in base ai valori duplicati nella colonna specificata. Innanzitutto, evidenzia l’intervallo di righe in base ai valori duplicati in una determinata colonna tramite una regola di formattazione condizionale ()fare clic per visualizzare come). In secondo luogo, filtra l’intervallo per colore. In terzo luogo, elimina facilmente tutte le righe filtrate. Infine, rimuovi o annulla il filtro e vedrai soltanto le righe con valori univoci nella colonna specificata.

In alternativa, puoi aggiungere una colonna di appoggio e applicare la formula =COUNTIF($C$3:$C$21;C3) per identificare i duplicati nella colonna specificata. Filtra poi i valori maggiori di 1 nella colonna di appoggio ed elimina facilmente tutte le righe filtrate. Una volta rimosso il filtro, rimarranno visibili soltanto le righe con valori univoci nella colonna indicata.

L’estensione di terze parti Kutools per Excel offre una funzionalità straordinariamente pratica: Seleziona celle duplicate/uniche. Consente di selezionare rapidamente le righe in base ai valori duplicati nella colonna specificata, dopodiché potrai eliminarle facilmente tramite il menu contestuale.

La funzionalità Kutools per Excel Unione avanzata righe consente inoltre di rimuovere rapidamente le righe in base ai valori duplicati nella colonna chiave specificata.

Possiamo aggiungere una colonna di appoggio e applicare una formula per identificare i Valori duplicati tra due colonne, per poi filtrarli ed eliminarli facilmente.

1. Aggiungi una colonna vuota accanto a quella da cui vuoi rimuovere i valori duplicati.

2. Nella prima cella della colonna di appoggio (esclusa l’intestazione), digita la formula =IF(ISERROR(MATCH(C2,$A$2:$A$13,0)),«Unique»,«Duplicate»), quindi trascina il quadratino di riempimento automatico verso il basso per copiare la formula nelle altre celle.

Nota: Nella formula indicata sopra,
(1) C2 è la prima cella della colonna specificata da cui si desidera rimuovere i valori duplicati;
(2) $A$2:$A$13 è l’altra colonna con cui effettuare il confronto;
(3) questa formula restituisce Duplicato se il valore corrispondente è duplicato rispetto a quelli presenti nell’altra colonna e Univoco se è diverso.

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

4. Fai clic sull’icona del filtro nell’intestazione della colonna di appoggio, quindi, nel menu a discesa, seleziona esclusivamente Duplicato e fai clic sul pulsante OK.

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

6. A questo punto, tutti i valori duplicati sono stati rimossi dalla colonna specificata. Per annullare il filtro, fai nuovamente clic su Dati > Filtro.

Si vedranno quindi soltanto i valori univoci nella colonna specificata. È possibile rimuovere la colonna di appoggio se necessario.

Nota: Questo metodo rimuoverà l’intera riga in base ai valori duplicati presenti nella colonna specificata.

Se hai installato Kutools per Excel, puoi sfruttare la sua straordinaria funzionalità Seleziona celle uguali/diverse per individuare rapidamente i valori duplicati tra due colonne e rimuoverli con facilità.

1. Fare clic su Kutools > Seleziona > Seleziona celle uguali/diverse per attivare questa funzionalità.

2. Nella finestra di dialogo Seleziona celle uguali/diverse, specifica entrambe le colonne nei campi Trova valori in e Secondo, seleziona le opzioni Singola cella e Stessi valori, quindi fai clic sul pulsante Ok. Vedere lo screenshot:

3. Verranno quindi selezionati tutti i valori duplicati corrispondenti tra le due colonne nella prima colonna (quella specificata nel campo)Trova valori in). Fare clic sul pulsante OK nella finestra di dialogo che compare.

4. È possibile premere il tasto Canc per rimuovere direttamente questi valori duplicati oppure fare clic con il tasto destro su di essi e selezionare Elimina dal menu contestuale.