Rechercher, mettre en évidence, filtrer, compter et supprimer les doublons sous Excel

Lorsqu’on recherche des cellules en double dans une seule colonne ou liste, on peut appliquer la fonction NB.SI pour identifier et compter facilement les données en double.
1. Sélectionnez une cellule vide à côté de la colonne dans laquelle vous souhaitez repérer les doublons.

2. Saisissez la formule =NB.SI($C$3:$C$12; C3)>1, puis appuyez sur la touche Entrée.

3. Faites glisser la poignée de recopie depuis la cellule contenant la formule pour l’appliquer aux autres cellules.

Remarques : Dans la formule =COUNTIF($C$3:$C$12, C3)>1,
(1) $C$3:$C$12 est la colonne ou la liste dans laquelle vous recherchez des valeurs en double. Étant donné que cette plage reste fixe lorsque vous copiez la formule vers d’autres cellules, il s’agit généralement d’une référence absolue, indiquée par le symbole « $ ».
(2) C3 est la première cellule de la colonne concernée. Il s’agit d’une référence relative, car elle doit s’ajuster automatiquement lorsque vous copiez la formule vers d’autres cellules.
(3) Cette formule renvoie VRAI ou FAUX. VRAI signifie que la valeur correspondante est en double, tandis que FAUX indique qu’elle est unique dans la colonne.
(4) Parfois, les résultats VRAI ou FAUX ne sont pas immédiatement clairs. Vous pouvez combiner la formule initiale avec la fonction SI pour afficher directement Doublons.
=IF(COUNTIF($C$3:$C$12, C3)>1,«Duplicates»,«»)

Dans certains cas, vous devez comparer deux colonnes pour identifier les valeurs en double. Par exemple, vous disposez de deux listes de noms et souhaitez supprimer de la deuxième liste les noms déjà présents dans la première. Vous pouvez facilement y parvenir à l’aide des fonctions RECHERCHEV et SIERREUR.

1. Sélectionnez une cellule vide située à côté de la deuxième liste des noms.

2. Saisissez la formule =IFERROR(VLOOKUP(D3,$B$3:$B$18,1,0),«»), puis appuyez sur la touche Entrée.

3. Faites glisser la poignée de recopie de la cellule contenant la formule pour la copier vers les autres cellules, selon vos besoins.

Remarques : Dans la formule ci-dessus,
(1) D3 est la première cellule de la deuxième liste des noms. La référence doit changer automatiquement lorsque nous copions la formule vers d’autres cellules ; il s’agit donc d’une référence relative.
(2) $B$3:$B$18 est la liste des prénoms. Il s’agit d’une référence absolue, car la plage doit rester fixe lors de la copie de la formule vers d’autres cellules.
(3) Lorsqu’un nom figure déjà dans la première liste, la formule renvoie ce nom ; sinon, elle renvoie une cellule vide.
(4) Vous pouvez également utiliser la formule =IF(COUNTIF($B$3:$B$18,D3)>0,«Duplicates»,«») pour identifier et supprimer les noms en double dans la deuxième liste par rapport à la première. Cette formule renvoie « Doublons » si le nom correspondant est en double.

(5) Si vous devez comparer deux colonnes situées sur deux feuilles différentes, il vous suffit d’ajouter le nom de la feuille devant la référence de la colonne à comparer. Dans notre exemple, remplacez simplement $B$3:$B$18 par Feuil1!$B$3:$B$18 dans la formule.

Les formules présentées ci-dessus ne tiennent pas compte de la casse lors de la détection des doublons, ce qui signifie que « apple » est considéré comme un doublon de « APPLE ». Vous pouvez utiliser une formule matricielle pour détecter les valeurs en double dans une seule colonne tout en respectant la casse.

1. Sélectionnez une cellule vide adjacente à la colonne dans laquelle vous allez effectuer la recherche pour trouver les doublons.

2. Saisissez la formule matricielle =IF(SUM((--EXACT($C$3:$C$12,C3)))<=1,«»,«Duplicate»), puis appuyez sur Ctrl + Maj + Entrée.

3. Faites glisser la poignée de recopie depuis la cellule contenant la formule matricielle pour l’appliquer aux autres cellules.

Remarques : Dans la formule matricielle ci-dessus,
(1) $C$3:$C$12 est la colonne dans laquelle vous recherchez les valeurs en double. La référence est absolue, car elle reste fixe lorsque vous copiez la formule matricielle vers d’autres cellules.
(2) C4 est la première cellule de la colonne. La référence est relative, car elle doit s’ajuster automatiquement lorsque vous copiez la formule matricielle vers d’autres cellules.
(3) Si la cellule correspondante est un doublon, la formule matricielle renvoie « Doublon » ; sinon, elle renvoie une cellule vide.

Vous pouvez utiliser la fonctionnalité Utiliser la mise en forme conditionnelle pour mettre en évidence rapidement les cellules en double dans une colonne ou une plage.

1. Sélectionnez la colonne dans laquelle vous souhaitez mettre en évidence les cellules en double.

2. Cliquez sur Accueil > Utiliser la mise en forme conditionnelle > Règles de mise en évidence des cellules > Valeurs en double. Voir la capture d’écran ci-dessous :

3. Dans la boîte de dialogue Valeurs en double qui s’ouvre, sélectionnez Doublons dans la première liste déroulante, choisissez un style de mise en évidence dans la deuxième liste déroulante, puis cliquez sur le bouton OK.

Remarques : Si les styles de mise en évidence prédéfinis ne répondent pas à vos besoins, sélectionnez Format personnalisé dans la deuxième liste déroulante, puis choisissez la couleur de mise en évidence, la police et les bordures de cellule souhaitées dans la boîte de dialogue Définir le format des cellules qui s’ouvre.

Vous verrez alors toutes les cellules en double mises en évidence dans la colonne sélectionnée, comme illustré dans la capture d’écran ci-dessous.

Remarques:
(1) Une fois les cellules en double mises en évidence, vous pouvez facilement les filtrer. ()Cliquez pour voir comment)
(2) Une fois les cellules en double mises en évidence, vous pouvez également les supprimer facilement en bloc. ()Cliquez pour voir comment)

Certains utilisateurs préfèrent Plage de ligne en surbrillance en fonction des cellules en double d’une colonne donnée. Dans ce cas, nous pouvons créer une règle de Utiliser la mise en forme conditionnelle personnalisée pour y parvenir.

1. Sélectionnez la plage (en excluant la ligne d’en-tête) sur laquelle vous souhaitez appliquer la mise en surbrillance des lignes en fonction des cellules en double.

2. Cliquez sur Accueil > Utiliser la mise en forme conditionnelle > Nouvelle règle.

3. Dans la boîte de dialogue Nouvelle règle de mise en forme,
(1) cliquez pour sélectionner l’option Utiliser une formule pour déterminer les cellules à mettre en forme ;
(2) saisissez la formule =NB.SI($C$3:$C$12;$C3)>1 dans le champ Mettre en forme les valeurs pour lesquelles cette formule est vraie ;
Conseil : Dans la formule, $C$3:$C$12 est la colonne contenant les cellules en double, et $C3 est la première cellule de la colonne.
(3) Cliquez sur le bouton Format.

4. Dans la boîte de dialogue « Définir le format des cellules », définissez la couleur de remplissage, la police et les bordures de cellule selon vos préférences, puis cliquez successivement sur les boutons OK pour enregistrer les paramètres.

À ce stade, dans le Sélectionner la plage, les lignes sont mises en évidence en fonction des cellules en double de la colonne spécifiée. Voir la capture d’écran :

Pour mettre en évidence les lignes en double dans une plage donnée, vous pouvez également utiliser la fonctionnalité Utiliser la mise en forme conditionnelle.

1. Sélectionnez la plage, à l’exception de la ligne d’en-tête.

2. Cliquez sur Accueil > Utiliser la mise en forme conditionnelle > Nouvelle règle.

3. Dans la boîte de dialogue Nouvelle règle de mise en forme :
(1) Sélectionnez l’option Utiliser une formule pour déterminer les cellules à mettre en forme ;
(2) Dans le champ Mettre en forme les valeurs pour lesquelles cette formule est vraie, saisissez la formule =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1 ;
(3) Cliquez sur le bouton Format.

Remarques : Dans la formule =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1 :
(1) $B$3:$B$12 est la première colonne de la plage, et $B3 est la première cellule de cette colonne ;
(2) $C$3:$C$12 est la deuxième colonne de la plage, et $C3 est la première cellule de cette colonne ;
(3) $D$3:$D$12 est la troisième colonne de la plage, et $D3 est la première cellule de cette colonne ;
(4) Si votre plage contient davantage de colonnes, vous pouvez ajouter successivement les références de colonnes et leurs premières cellules dans la formule.

4. Dans la boîte de dialogue « Définir le format des cellules », définissez la couleur de surlignage, la police, les bordures de cellule, etc., selon vos besoins, puis cliquez successivement sur les boutons OK pour enregistrer les paramètres.

À ce stade, les Ligne en double sont identifiés et mis en évidence dans le Sélectionner la plage. Voir la capture d’écran :

Vous avez peut-être remarqué que toutes les occurrences en double sont identifiées ou mises en évidence avec les méthodes ci-dessus. Parfois, vous souhaitez afficher uniquement les doublons, à l’exception de leur première occurrence. C’est également possible grâce à la fonctionnalité Utiliser la mise en forme conditionnelle et à une formule différente.

1. Sélectionnez la colonne comportant un en-tête.
Conseil : Si vous souhaitez mettre en surbrillance une plage de lignes en fonction des doublons présents dans une seule colonne, à l’exception de la première occurrence, sélectionnez la plage sans inclure la ligne d’en-tête.

2. Cliquez sur Accueil > Utiliser la mise en forme conditionnelle > Nouvelle règle.

3. Dans la boîte de dialogue Nouvelle règle de mise en forme :
(1) Cliquez pour sélectionner l’option Utiliser une formule pour déterminer les cellules à mettre en forme ;
(2) Dans le champ Mettre en forme les valeurs pour lesquelles cette formule est vraie, saisissez la formule =COUNTIF($C$3:$C3, C3)>1 ;
Conseils : pour mettre en surbrillance les lignes en fonction des doublons présents dans une seule colonne, saisissez la formule =COUNTIF($C$3:$C3, $C3)>1.
(3) Cliquez sur le bouton Format.

4. Dans la boîte de dialogue Définir le format des cellules qui s’ouvre, définissez la couleur de surlignage, la police, les bordures de cellule, etc., selon vos besoins, puis cliquez sur le bouton OK pour enregistrer les paramètres.

Vous verrez alors les cellules en double, à l’exception de leur première occurrence dans la colonne sélectionnée (ou les lignes basées sur les doublons dans la colonne spécifiée), mises en évidence. Voir la capture d’écran :

Lorsque nous mettons en évidence des cellules ou des lignes en double à l’aide de la fonctionnalité Utiliser la mise en forme conditionnelle, tous les doublons sont affichés avec la même couleur. Pourtant, s’il était possible d’attribuer une couleur différente à chaque série de valeurs en double, leur identification visuelle deviendrait bien plus intuitive. Une macro VBA vous permet justement d’obtenir ce résultat dans Excel.

1. Appuyez simultanément sur les touches Alt + F11 pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.

2. Dans cette fenêtre, cliquez sur Insertion > Module, puis collez le code ci-dessous dans la nouvelle fenêtre du module.

VBA : Mettre en évidence les cellules en double avec des couleurs différentes :

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. Appuyez sur la touche F5 ou cliquez sur l’icône Exécuter pour exécuter cette macro VBA.

4. Dans la boîte de dialogue Kutools pour Excel qui s’ouvre, sélectionnez la colonne dans laquelle vous souhaitez mettre en évidence les cellules en double avec des couleurs différentes, puis cliquez sur le bouton OK.

Chaque série de Valeurs en double est désormais mise en évidence avec une couleur distincte.

Le premier outil que je vais vous présenter est la fonctionnalité Sélectionner les cellules en double/uniques, proposée par Kutools pour Excel. Grâce à elle, identifiez en un clin d’œil les cellules uniques ou en double !

1. Sélectionnez la colonne ou la plage dans laquelle vous souhaitez identifier et mettre en évidence les cellules en double.

2. Cliquez sur Kutools > Sélectionner > Sélectionner les cellules en double/uniques.

3. Dans la boîte de dialogue Sélectionner les cellules en double ou uniques, cochez les options souhaitées, puis cliquez sur le bouton OK pour terminer l’opération.

Remarques : Dans la boîte de dialogue Sélectionner les cellules en double/uniques,
(1) Si vous souhaitez sélectionner ou mettre en évidence tous les doublons à l’exception de la première occurrence, cochez l’option Valeurs en double (Exclure la première correspondance). Sinon, cochez l’option Valeurs en double (Inclure la première correspondance).
(2) Si vous souhaitez mettre en évidence les doublons, activez l’option Couleur d'arrière-plan de remplissage et choisissez une couleur de surlignage selon vos préférences.
(3) Si vous souhaitez sélectionner ou mettre en surbrillance toute la ligne en fonction des doublons présents dans la colonne sélectionnée, cochez l’option Sélectionner la ligne entière.
(4) Si vous souhaitez sélectionner ou mettre en évidence les valeurs en double en respectant la casse, cochez l’option Sensible à la casse.

Kutools pour Excel propose également un outil remarquable : Comparer les cellules, qui permet d’identifier et de mettre en évidence facilement les cellules en double présentes dans deux colonnes.

1. Cliquez sur Kutools > Comparer les cellules pour ouvrir la boîte de dialogue « Sélectionner les cellules identiques/différentes ».

2. Dans la boîte de dialogue Sélectionner les cellules identiques/différentes, spécifiez les deux colonnes dans les champs Rechercher les valeurs dans et Selon, cochez l’option Valeurs identiques et activez les autres options souhaitées.

Remarques :
(1) Si vous devez rechercher des Ligne en double, cochez l’option Chaque ligne; et pour trouver des cellules en double, cochez l’option Cellule uniquedans la section Basé sur;
(2) Cochez l’option Couleur d'arrière-plan de remplissageet spécifiez une couleur de mise en évidence si vous souhaitez mettre en évidence les Ligne en double ou les cellules ;
(3) Cochez l’option Sélectionner la ligne entièresi vous souhaitez sélectionner ou mettre en évidence les Ligne entière en fonction des doublons ;
(4) Cochez l’option Sensible à la cassesi vous souhaitez rechercher ou mettre en évidence les doublons sensibles à la casse.

3. Cliquez successivement sur le bouton OK pour terminer la configuration.

Vous verrez alors les doublons dans la colonne que vous avez spécifiée dans la zone Rechercher les valeurs dans identifiés et mis en évidence.

Cette méthode vous permet d’identifier et de mettre en évidence les cellules en double à l’aide d’une règle Utiliser la mise en forme conditionnelle, puis de filtrer facilement par couleur de mise en évidence dans Excel.

1. Utilisez la mise en forme conditionnelle pour rechercher et mettre en évidence les doublons dans la colonne spécifiée. ()Cliquez pour voir comment)

2. Cliquez sur l’en-tête « Cliquez pour sélectionner » de la colonne spécifiée, puis cliquez sur Données > Filtrer.

3. Cliquez ensuite sur l’icône de filtre dans l’en-tête de colonne, sélectionnez Filtrer par couleur, puis choisissez la couleur utilisée dans la mise en forme conditionnelle spécifiée dans la liste déroulante. Voir la capture d’écran :

Seules les lignes contenant des cellules en double seront alors filtrées. Voir la capture d’écran :

Autrement, nous pouvons également utiliser une formule Marquer les doublons dans une colonne auxiliaire, puis filtrer facilement les doublons à l’aide de cette colonne dans Excel.

1. En plus des données d’origine, ajoutez une colonne auxiliaire et saisissez Doublon comme en-tête de colonne.

2. Sélectionnez la première cellule vide sous l’en-tête de colonne, saisissez la formule =IF(COUNTIF($C$3:$C$12,C3)>1,«Duplicate»,«»), puis faites glisser la poignée de recopie vers le bas pour appliquer cette formule aux autres cellules.

Remarques : Dans la formule ci-dessus, $C$3:$C$12 correspond à la colonne contenant les données en double, et C3 est la première cellule (hors en-tête) de cette colonne.

3. Cliquez sur l’en-tête – Doublons, puis sur Données > Filtrer.

4. Cliquez ensuite sur l’icône de filtre dans l’en-tête de colonne, cochez uniquement Doublon, puis cliquez sur le bouton OK. Voir la capture d’écran :

À ce stade, seules les lignes contenant des Valeurs en double sont filtrées. Voir la capture d’écran :

Généralement, on peut utiliser la fonction =NB.SI(plage; critère) pour compter le nombre total d’occurrences d’une valeur donnée dans une plage limitée. Par exemple, pour compter combien de fois « Pomme » apparaît dans la liste A2:A10, on peut utiliser la formule =NB.SI(A2:A10; «Pomme») afin de dénombrer toutes les occurrences de cette valeur.

Toutefois, la formule =NB.SI(plage; critère) ne compte que la valeur en double spécifiée. Comment procéder pour compter les valeurs en double selon deux critères ou plus ? Et que faire si l’on souhaite compter les doublons sensibles à la casse avec des critères ? Les méthodes ci-dessous vous permettront de résoudre ces problèmes.

Nous pouvons appliquer une formule matricielle pour compter les Valeurs en double sensibles à la casse avec critères dans Excel. Par exemple, pour compter combien de fois la valeur « Apple » apparaît dans la liste B2:B21 avec Respecter la casse, procédez comme suit :

1. Sélectionnez une cellule vide.

2. Saisissez la formule =SOMME(--EXACT(B2:B20;E2)).

3. Appuyez sur Ctrl + Maj + Entrée pour obtenir le résultat du décompte.

Remarques: Dans la formule matricielle,
(1)B2:B20correspond à la colonne dans laquelle vous comptez les doublons.
(2)E2est la cellule contenant la valeur spécifique dont vous souhaitez compter les occurrences. Vous pouvez remplacer la référence de cellule par la valeur entre guillemets, par exemple «Apple».

Parfois, vous souhaitez compter les doublons selon deux critères ou plus. Utilisez la fonction NB.SI.ENS pour y parvenir.
Par exemple, prenons le tableau des ventes de fruits illustré ci-dessous. Nous devons compter combien de fois des pommes ont été vendues le 7/5/2020 avec un montant supérieur à 300. Voici comment procéder pour compter les doublons selon ces critères :

1. Sélectionnez une cellule vide.

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

3. Appuyez sur la touche Entrée pour obtenir le résultat du décompte.

Remarques : Dans la formule ci-dessus,
(1) B3:B20 correspond à la première colonne (celle des dates), et G4 au critère de date ;
(2) C3:C20 correspond à la deuxième colonne (celle des fruits), et G3 au critère de fruit ;
(3) D3:D20 correspond à la troisième colonne (celle des montants), et «>300» au critère de montant.
(4) Si votre tableau comporte davantage de colonnes et de critères, vous pouvez ajouter les références de colonnes et leurs critères correspondants.

Pour compter tous les doublons d’une colonne à l’exception de leur première occurrence, procédez comme suit :

1. Sélectionnez une cellule vide à côté de la colonne.

2. Saisissez la formule =IF(COUNTIF($B$3:B3,B3)>1,«YES»,«»), puis faites glisser la poignée de recopie vers le bas pour l’appliquer aux autres cellules.

Remarques : Dans la formule ci-dessus,
(1) $B$3:B3 correspond à la plage dans laquelle vous comptez les doublons. Dans $B$3:B3, B3 s’ajuste automatiquement lorsque vous copiez la formule vers d’autres cellules.
(2) B3 est la première cellule de la colonne spécifiée.
(3) Cette formule renvoie « OUI » ou une cellule vide. « OUI » indique que la valeur correspondante est un doublon, tandis qu’une cellule vide signifie qu’elle est unique.

Tous les doublons de la colonne spécifiée sont alors identifiés. Nous pouvons compter les résultats de la formule pour obtenir le nombre total de doublons.

3. Sélectionnez une cellule vide.

4. Saisissez la formule =COUNTIF(C3:C16,«YES»), puis appuyez sur la touche Entrée.

Remarques : Dans la formule ci-dessus,
(1) C3:C16 est la plage à laquelle nous avons appliqué la formule pour marquer les doublons à l’étape précédente.
(2) OUI est la valeur renvoyée par la dernière formule.

Nous obtenons ainsi le nombre total de Valeurs en double dans la colonne spécifiée. Le nombre total de doublons n’inclut pas les premières occurrences.

Pour compter le nombre de tous les doublons, y compris les premières occurrences, dans Excel, vous pouvez appliquer une formule matricielle pour y parvenir.

1. Sélectionnez une cellule vide.

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

3. Appuyez sur Ctrl + Maj + Entrée pour obtenir le résultat du décompte.

Remarques : Dans la formule ci-dessus, B3:B16 est la colonne spécifiée dans laquelle vous souhaitez compter les doublons, y compris leurs premières occurrences.

Pour simplifier votre travail et vous éviter de mémoriser de longues formules fastidieuses, utilisez la fonctionnalité Sélectionner les cellules en double/uniques proposée par Kutools pour Excel : elle vous permet de compter rapidement le nombre de valeurs en double dans la liste ou la colonne spécifiée, en incluant ou en excluant les premières occurrences.

1. Sélectionnez la colonne dans laquelle vous souhaitez compter les valeurs en double, puis cliquez sur Kutools > Sélectionner > Sélectionner les cellules en double/uniques.

2. Dans la boîte de dialogue Sélectionner les cellules en double/uniques, cochez l’option Valeurs en double (Exclure la première correspondance) ou Valeurs en double (Inclure la première correspondance) selon vos besoins, puis cliquez sur le bouton OK.

3. Toutes les valeurs en double — premières occurrences incluses ou non — sont alors sélectionnées, et une boîte de dialogue s’affiche simultanément pour indiquer le nombre de cellules sélectionnées. Voir la capture d’écran ci-dessus.

Supposons que vous souhaitiez comparer deux Liste des noms et compter le nombre de doublons entre eux. Comment résoudre ce problème rapidement ? Nous pouvons également y parvenir à l’aide d’une formule dans Excel.

1. Sélectionnez une cellule vide.

2. Saisissez la formule =SUMPRODUCT(--(ISNUMBER(MATCH(B3:B12,D3:D18,0)))).

3. Appuyez sur la touche Entrée.

Remarques : Dans la formule ci-dessus,
(1) B3:B12 est la première colonne de noms dans laquelle vous allez compter les doublons.
(2) D3:D18 est la deuxième colonne de noms sur laquelle vous basez le décompte des doublons.

Vous pouvez également utiliser une extension tierce, Kutools pour Excel, pour compter facilement et rapidement le nombre total de cellules en double entre deux colonnes.

1. Cliquez sur Kutools > Sélectionner > Sélectionner les cellules identiques/différentes.

2. Dans la boîte de dialogue « Sélectionner les cellules identiques et différentes »,
(1) Spécifiez les deux colonnes dans les zones Rechercher les valeurs dans et Selon séparément.
(2) Cochez l’option Cellule unique.
(3) Cochez l’option Valeurs identiques.

4. Cliquez sur le bouton OK.

Toutes les cellules en double de la première colonne sont alors sélectionnées, et une boîte de dialogue s’affiche simultanément pour indiquer le nombre de cellules en double sélectionnées. Voir la capture d’écran :

Remarques : Cette fonctionnalité compte le nombre total de valeurs en double dans la colonne que vous avez spécifiée dans la zone Rechercher les valeurs dans de la boîte de dialogue Sélectionner les cellules identiques/différentes. Si vous souhaitez compter le nombre total de valeurs en double dans une deuxième colonne, appliquez à nouveau la fonctionnalité Sélectionner les cellules identiques/différentes en spécifiant cette deuxième colonne dans la zone Rechercher les valeurs dans.

Vous pouvez compter rapidement chaque valeur en double une seule fois à l’aide d’une formule comme suit :

1. Sélectionnez une cellule vide.

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

3. Appuyez sur la touche Entrée pour obtenir le résultat du décompte.

Remarques : Dans la formule ci-dessus, C3:C19 est la colonne spécifiée dans laquelle vous souhaitez compter chaque valeur en double une seule fois.

Lorsque vous comptez une liste, vous pouvez compter chaque valeur en double une seule fois avec Respecter la casse en appliquant une formule matricielle dans Excel.

1. Sélectionnez une cellule vide.

2. Saisissez la formule matricielle =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. Appuyez simultanément sur les touches Ctrl + Maj + Entrée pour obtenir le résultat du décompte.

Remarques : Dans la formule matricielle ci-dessus, C3:C19 correspond à la colonne spécifiée dans laquelle chaque série de doublons n’est comptée qu’une seule fois, en respectant la casse.

Si vous avez installé Kutools pour Excel, vous pouvez également utiliser sa fonctionnalité Compter les cellules contenant des valeurs uniques pour compter rapidement chaque série de valeurs en double une seule fois dans Excel.

1. Sélectionnez une cellule vide.

2. Cliquez sur Kutools > Assistant de formule > Statistiques > Compter le nombre de valeurs uniques dans une plage (y compris le premier doublon).

3. Dans la boîte de dialogue Assistant de formule, indiquez dans la zone Plage la colonne dans laquelle vous souhaitez compter les doublons une seule fois, puis cliquez sur le bouton OK.

Le résultat du décompte est alors immédiatement inséré dans la cellule sélectionnée.

Nous pouvons utiliser la fonctionnalité Sous-total pour compter chaque série de valeurs en double dans une colonne sous Excel.

1. Sélectionnez la colonne dans laquelle vous souhaitez compter chaque série de valeurs en double, puis cliquez sur Données > Ordre croissant ou Trier de Z à A.

2. Dans la boîte de dialogue d’alerte de tri qui s’affiche, cochez l’option Étendre la sélection, puis cliquez sur le bouton Trier.

Vous verrez alors que la sélection a été triée selon les Valeurs en double de la colonne spécifiée.

3. Gardez la sélection active, puis cliquez sur Données > Sous-total.

4. Dans la boîte de dialogue Sous-total,
(1) sélectionnez la colonne souhaitée dans la liste déroulante À chaque changement dans ;
(2) choisissez Compter dans la liste déroulante Fonction à utiliser ;
(3) cochez uniquement la colonne concernée dans la zone Ajouter un sous-total à ;
(4) cliquez sur le bouton OK.

Vous verrez alors que chaque série de valeurs dupliquées est comptabilisée, et que le résultat du décompte est ajouté sous chaque série de valeurs dupliquées. Voir la capture d’écran ci-dessus.

Nous pouvons également créer un Tableau croisé dynamique afin de compter facilement et rapidement chaque série de valeurs dupliquées dans Excel.

1. Sélectionnez la plage contenant la colonne spécifiée, puis cliquez sur Insertion > Tableau croisé dynamique.

2. Dans la boîte de dialogue « Créer un tableau croisé dynamique », indiquez l’emplacement où vous souhaitez insérer le nouveau tableau croisé dynamique, puis cliquez sur le bouton OK.

3. Dans le volet Champs du tableau croisé dynamique, faites glisser la colonne spécifiée vers les zones Lignes et Valeurs. Vous verrez alors que chaque série de valeurs dupliquées dans la colonne spécifiée est comptabilisée automatiquement. Voir la capture d’écran :

Si vous avez déjà installé Kutools pour Excel, vous pouvez utiliser sa fonctionnalité intuitive Fusion avancée des lignes pour compter rapidement chaque série de valeurs dupliquées dans la colonne spécifiée.

Remarques : La fonctionnalité Fusionner des lignes avancée modifiera la plage sélectionnée et supprimera des lignes en fonction des valeurs en double dans la colonne clé spécifiée. Pour préserver vos données, il est recommandé d’en faire une sauvegarde ou de les copier ailleurs avant d’effectuer les opérations suivantes.

1. Ajoutez une colonne vide juste à droite de la plage de données d’origine et nommez cette nouvelle colonne Compte.

2. Sélectionnez simultanément la plage de données d’origine et la nouvelle colonne, puis cliquez sur Kutools > Fusionner et scinder > Fusion avancée des lignes.

3. Dans la boîte de dialogue Fusion avancée des lignes,
(1) cliquez pour sélectionner la colonne spécifiée dont vous souhaitez compter chaque série de valeurs dupliquées, puis cliquez sur Clé primaire.
(2) Cliquez pour sélectionner la nouvelle colonne (Compte), puis cliquez sur Calculer > Compter.
(3) Spécifiez les types de combinaison ou de calcul pour les autres colonnes si nécessaire.
(4) Cliquez sur le bouton OK.

Vous verrez alors que chaque série de valeurs dupliquées dans la colonne spécifiée est comptabilisée en masse. Voir la capture d’écran :

Supposons qu’une liste de fruits figure dans une colonne. Certains fruits y apparaissent plusieurs fois. Vous devez maintenant marquer chaque fruit dupliqué dans l’ordre où il apparaît. Comment résoudre ce problème ? Cette section présente une formule permettant de le faire facilement dans Excel.

1. Ajoutez une colonne vide juste à droite des données d’origine.

2. Saisissez la formule =IF(COUNTIF($C$3:$C$14,C3)>1,COUNTIF(C$3:C3,C3),«») dans la première cellule de la colonne vide ajoutée.

3. Faites glisser la poignée de recopie de la cellule contenant la formule pour l’appliquer aux autres cellules.

Remarques : Dans la formule ci-dessus,
(1) $C$3:$C$14 correspond à la colonne spécifiée dans laquelle vous souhaitez compter les valeurs en double dans l’ordre.
(2) C3 est la première cellule de la colonne spécifiée.
(3) Si la valeur correspondante est dupliquée, cette formule renvoie le numéro de séquence 1, 2, 3… selon l’ordre d’apparition ; si la valeur est unique, la formule renvoie une cellule vide.

Vous pouvez utiliser la fonctionnalité Supprimer les doublons pour supprimer directement toutes les valeurs en double, à l’exception de leur première occurrence.

1. Sélectionnez la colonne dans laquelle vous souhaitez supprimer toutes les valeurs en double, à l’exception de leur première occurrence.

2. Cliquez sur Données > Supprimer les doublons.

3. Dans la boîte de dialogue d’avertissement « Supprimer les doublons », cochez l’option Continuer avec la sélection actuelle, puis cliquez sur le bouton Supprimer les doublons.

Conseil : pour supprimer des lignes en fonction des valeurs en double dans la sélection, cochez l’option Étendre la sélection.

4. Dans la boîte de dialogue « Supprimer les doublons », cochez uniquement la colonne spécifiée, puis cliquez sur le bouton OK.

Conseils : Si vous avez coché l’option Étendre la sélection à l’étape précédente, toutes les colonnes apparaîtront ici. Même dans ce cas, veillez à ne cocher que la colonne spécifiée.

5. Une boîte de dialogue s’affiche alors, indiquant combien de valeurs en double ont été supprimées. Cliquez sur le bouton OK pour la fermer.

Vous pouvez également utiliser la fonctionnalité Filtre avancé pour supprimer facilement toutes les valeurs en double de la colonne spécifiée.

1. Cliquez sur Données > Avancé.

2. Dans la boîte de dialogue Filtre avancé,
(1) cochez l’option Copier vers un autre emplacement ;
(2) dans la zone Plage de la liste, sélectionnez la colonne concernée dont vous souhaitez supprimer les valeurs en double ;
(3) dans la zone Copier vers, indiquez l’emplacement où vous souhaitez coller la colonne ;
(4) cochez l’option Uniques uniquement ;
(5) cliquez sur le bouton OK.

Vous verrez alors que la colonne spécifiée a été collée à l’Plage limitée avec suppression de toutes les Valeurs en double sauf les premières occurrences. Voir la capture d’écran :

Vous pouvez également utiliser une macro VBA pour supprimer rapidement toutes les valeurs en double d’une colonne dans Excel, à l’exception de leur première occurrence.

1. Appuyez sur les touches Alt + F11 pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.

2. Cliquez sur Insertion > Module, puis collez le code VBA ci-dessous dans la nouvelle fenêtre du module.

VBA : Supprimer les valeurs en double sauf les premières occurrences

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. Appuyez sur la touche F5 ou cliquez sur le bouton Exécuter pour exécuter ce code VBA.

4. Dans la boîte de dialogue qui s’ouvre, indiquez la plage à partir de laquelle vous souhaitez supprimer les valeurs en double, puis cliquez sur le bouton OK.

Toutes les Valeurs en double, à l’exception des premières occurrences, sont immédiatement supprimées de la Plage limitée.

Remarque : Ce code VBA est Sensible à la casse.

Nous pouvons mettre en évidence toutes les Valeurs en double, y compris les premières occurrences, dans une colonne ou une liste à l’aide d’une règle de Utiliser la mise en forme conditionnelle, puis filtrer toutes les Valeurs en double selon cette couleur de mise en évidence. Ensuite, nous pouvons sélectionner toutes les cellules en double filtrées et les supprimer par lot.

1. Utilisez la mise en forme conditionnelle pour mettre en évidence les valeurs en double. ()Cliquez pour voir comment)

2. Sélectionnez la colonne à partir de laquelle vous souhaitez supprimer toutes les valeurs en double (y compris les premières occurrences), puis cliquez sur Données > Filtrer.

3. Cliquez sur l’icône de filtre dans l’en-tête de la colonne concernée. Dans le menu déroulant, sélectionnez Filtrer par couleur, puis choisissez la couleur de mise en évidence dans le sous-menu.

Toutes les Valeurs en double sont alors filtrées.

4. Sélectionnez toutes les cellules filtrées, cliquez avec le bouton droit, puis choisissez Supprimer une ligne dans le menu contextuel. Dans la boîte de dialogue de confirmation qui s’ouvre, cliquez sur le bouton OK pour continuer.

5. Toutes les valeurs en double sont alors supprimées par lot. Gardez la liste filtrée sélectionnée et cliquez à nouveau sur Filtrer > Données pour annuler le filtre.

À ce stade, vous constaterez que toutes les cellules en double, y compris les premières occurrences, ont été supprimées par lot, ne laissant que les valeurs uniques.

Remarque : cette méthode supprime les lignes contenant des valeurs en double dans la colonne spécifiée.

Nous pouvons également appliquer une formule pour identifier les Valeurs en double, y compris les premières occurrences, dans une colonne auxiliaire, puis filtrer les Valeurs en double selon les résultats de la formule, et enfin supprimer ces Valeurs en double filtrées par lot.

1. Ajoutez une colonne auxiliaire juste à côté de la colonne spécifiée, saisissez la formule =NB.SI($B$3:$B$11;B3) dans la première cellule de cette colonne, puis faites glisser la poignée de recopie vers le bas pour l’appliquer aux autres cellules. Voir la capture d’écran :

Remarque : Dans la formule ci-dessus, $B$3:$B$11 correspond à la colonne spécifiée à partir de laquelle vous souhaitez supprimer les valeurs en double, et B3 est la première cellule de cette colonne.

2. Sélectionnez la colonne auxiliaire, puis cliquez sur Données > Filtrer.

3. Cliquez sur l’icône de filtre dans l’en-tête de la colonne auxiliaire, puis cochez, dans le menu déroulant, toutes les valeurs sauf 1, et cliquez sur le bouton OK. Voir la capture d’écran :

4. Toutes les valeurs en double sont désormais filtrées. Sélectionnez les cellules filtrées dans la colonne auxiliaire, cliquez avec le bouton droit, puis choisissez Supprimer une ligne dans le menu contextuel.

5. Dans la boîte de dialogue de confirmation qui s’ouvre, cliquez sur le bouton OK pour continuer.

6. Toutes les valeurs en double et leurs lignes associées ont désormais été supprimées par lot. Cliquez à nouveau sur Données > Filtrer pour annuler le filtre.

Vous constaterez alors que toutes les Valeurs en double, y compris les premières occurrences, ont été supprimées par lot.

Si vous avez installé Kutools pour Excel, vous pouvez également utiliser sa fonctionnalité Sélectionner les cellules en double/uniques pour sélectionner et supprimer facilement les valeurs en double, en incluant ou en excluant les premières occurrences, directement dans Excel.

1. Sélectionnez la colonne à partir de laquelle vous souhaitez supprimer les doublons.

2. Cliquez sur Kutools > Sélectionner > Sélectionner les cellules en double/uniques.

3. Dans la boîte de dialogue Sélectionner les cellules en double/uniques, cochez l’option Valeurs en double (inclure la première correspondance), puis cliquez sur le bouton OK.

Remarques :
(1) Pour sélectionner et supprimer les valeurs en double tout en conservant la première occurrence, cochez l’option Valeurs en double (Exclure la première correspondance).
(2) Pour sélectionner et supprimer des lignes entières en fonction des valeurs en double présentes dans une colonne spécifique, cochez l’option Sélectionner la ligne entière.
(3) Pour sélectionner et supprimer les valeurs en double en tenant compte de la casse, cochez l’option Sensible à la casse.
(4) Pour sélectionner, mettre en évidence puis supprimer les cellules ou lignes en double, cochez l’une des options suivantes : Couleur d'arrière-plan de remplissage ou Couleur de police de remplissage, puis définissez la couleur souhaitée.

4. Une boîte de dialogue s’affiche alors, indiquant le nombre de cellules sélectionnées ; cliquez sur le bouton OK pour la fermer.

5. Cliquez avec le bouton droit sur les cellules sélectionnées, puis choisissez Supprimer dans le menu contextuel.

6. Dans la boîte de dialogue Supprimer qui s’ouvre, cochez l’option Décaler les cellules vers le haut, puis cliquez sur le bouton OK.

À ce stade, toutes les Valeurs en double, y compris les premières occurrences, ont été supprimées par lot.

Dans la plupart des cas, nous identifions les Valeurs en double dans une colonne, puis supprimons les Ligne entière en fonction des Valeurs en double. Honnêtement, cette opération ressemble beaucoup à la suppression des Valeurs en double d’une seule colonne. Par conséquent, nous pouvons utiliser des solutions similaires pour Supprimer des lignes en fonction des doublons présents dans la colonne spécifiée.

La première méthode consiste à utiliser la fonctionnalité intégrée Supprimer les doublons pour supprimer des lignes en fonction des doublons présents dans la colonne spécifiée. Il suffit de sélectionner la plage concernée, de cliquer sur Données > Supprimer les doublons, de cocher uniquement la colonne souhaitée dans la boîte de dialogue Supprimer les doublons, puis de cliquer sur OKpour finaliser l’opération.

Vous pouvez également utiliser la mise en forme conditionnelle associée au filtre pour supprimer les lignes contenant des valeurs en double dans une colonne spécifiée. Commencez par mettre en surbrillance ces doublons à l’aide d’une règle de mise en forme conditionnelle ()cliquez pour voir comment). Ensuite, appliquez un filtre par couleur, puis supprimez facilement toutes les lignes filtrées. Enfin, effacez ou désactivez le filtre : seules les lignes avec des valeurs uniques dans la colonne concernée resteront.

Vous pouvez également ajouter une colonne auxiliaire et appliquer la formule =NB.SI($C$3:$C$21;C3) pour identifier et marquer les doublons dans la colonne spécifiée. Ensuite, filtrez les valeurs supérieures à 1 dans cette colonne auxiliaire, puis supprimez facilement toutes les lignes filtrées. Une fois le filtre effacé, seules les lignes contenant des valeurs uniques dans la colonne spécifiée subsisteront.

Le complément tiers Kutools pour Excel propose également une fonctionnalité extrêmement pratique : Sélectionner les cellules en double/uniques. Elle vous permet de sélectionner rapidement des lignes contenant des valeurs en double dans la colonne spécifiée, après quoi vous pouvez facilement supprimer ces lignes via le menu contextuel obtenu par un clic droit.

La fonction Fusion avancée des lignes de Kutools pour Excel permet également de supprimer rapidement des lignes en fonction des valeurs en double dans la colonne clé spécifiée.

Nous pouvons ajouter une colonne auxiliaire et appliquer une formule pour identifier les Valeurs en double entre deux colonnes, puis filtrer et supprimer facilement les Valeurs en double.

1. Ajoutez une colonne vide à côté de la colonne spécifiée dont vous souhaitez supprimer les valeurs en double.

2. Dans la première cellule de la colonne auxiliaire (hors en-tête), saisissez la formule =IF(ISERROR(MATCH(C2,$A$2:$A$13,0)),«Unique»,«Duplicate»), puis faites glisser la poignée de recopie vers le bas pour l’appliquer aux autres cellules.

Remarques : Dans la formule ci-dessus,
(1) C2 est la première cellule de la colonne spécifiée dont vous allez supprimer les valeurs en double ;
(2) $A$2:$A$13 est l’autre colonne avec laquelle nous devons effectuer la comparaison.
(3) Cette formule renvoie Doublon si la valeur correspondante est en double par rapport aux valeurs de l’autre colonne, et renvoie Unique si elle diffère des valeurs de l’autre colonne.

3. Sélectionnez la colonne auxiliaire, puis cliquez sur Données > Filtrer.

4. Cliquez sur l’icône de filtre dans l’en-tête de la colonne auxiliaire, puis cochez uniquement Doublon dans le menu déroulant et cliquez sur le bouton OK.

5. Toutes les valeurs en double sont désormais filtrées. Sélectionnez les cellules filtrées, cliquez avec le bouton droit, puis choisissez Supprimer une ligne dans le menu contextuel. Cliquez ensuite sur OK dans la boîte de dialogue de confirmation qui s’affiche.

6. Toutes les valeurs en double ont été supprimées de la colonne spécifiée. Cliquez à nouveau sur Données > Filtrer pour annuler le filtre.

Vous constaterez alors que seules les valeurs uniques subsistent dans la colonne spécifiée. Vous pouvez supprimer la colonne auxiliaire si nécessaire.

Remarques : Cette méthode supprime les lignes entières en fonction des valeurs en double présentes dans la colonne spécifiée.

Si vous avez installé Kutools pour Excel, vous pouvez utiliser sa remarquable fonctionnalité Sélectionner les cellules identiques/différentes pour sélectionner rapidement les valeurs en double entre deux colonnes, puis les supprimer facilement.

1. Cliquez sur Kutools > Sélectionner > Sélectionner les cellules identiques/différentes pour activer cette fonctionnalité.

2. Dans la boîte de dialogue Sélectionner les cellules identiques/différentes, indiquez les deux colonnes respectivement dans les champs Rechercher les valeurs dans et Selon, cochez les options Cellule unique et Valeurs identiques, puis cliquez sur le bouton Ok. Voir la capture d’écran :

3. Toutes les valeurs en double communes aux deux colonnes sont alors sélectionnées dans la première colonne (celle que vous avez spécifiée dans le champ)Rechercher les valeurs dans). Cliquez ensuite sur le bouton OK dans la boîte de dialogue qui s’affiche.

4. Vous pouvez appuyer sur la touche Suppr pour supprimer directement ces valeurs en double, ou effectuer un clic droit dessus et choisir Supprimer dans le menu contextuel.