M365Con 2027 – conférence en ligne gratuite · 11–22 Jan 2027 · 120 sessions→ m365con.net

Blog · Mirko Peters

Rechercher les doublons dans Excel : mettre en surbrillance, compter et lister

Par · · Mis à jour

Rechercher les doublons dans Excel avec la mise en forme conditionnelle, les indicateurs COUNTIF, UNIQUE, Power Query et les contrôles entre feuilles, ainsi que la manière de repérer les correspondances approximatives avant toute suppression.

Traduit automatiquement par IA à partir de l'article original. Lire l'original en anglais

Pour rechercher les doublons dans Excel sans rien supprimer, sélectionnez vos données puis utilisez Accueil > Mise en forme conditionnelle > Règles de mise en surbrillance des cellules > Valeurs en double. Excel colore chaque valeur qui apparaît plusieurs fois. Si vous préférez des nombres plutôt que des couleurs, ajoutez une colonne d’aide avec une formule COUNTIF. Cet article s’adresse aux utilisateurs d’Excel qui héritent de listes désordonnées et qui doivent voir le problème avant de le corriger. Vous y trouverez la méthode de mise en surbrillance, les formules de comptage et de signalement, un moyen de ne lister que les valeurs répétées, une option Power Query, des contrôles entre feuilles et une démarche pour les correspondances approximatives.

Points clés

  • Le moyen le plus rapide de rechercher les doublons dans Excel consiste à passer par Accueil, Mise en forme conditionnelle, Règles de mise en surbrillance des cellules, Valeurs en double. Les valeurs répétées sont colorées, et rien n’est supprimé.
  • Une colonne d’aide COUNTIF indique combien de fois chaque valeur apparaît, et COUNTIFS permet de vérifier plusieurs colonnes à la fois.
  • COUNTIF ne tient pas compte de la casse et traite ? et * comme des caractères génériques : le nombre obtenu peut donc différer de ce que vous attendez.
  • Pour comparer deux feuilles, comptez chaque valeur dans l’autre liste avec COUNTIF, et ouvrez les deux classeurs si la seconde liste se trouve dans un autre fichier.
  • Les correspondances approximatives, comme Jon et John, ne sont pas détectées comme des doublons. Commencez par nettoyer les espaces et la casse, puis examinez le reste à l’œil ou avec le complément Fuzzy Lookup.

La recherche précède la suppression. Si vous savez déjà quelles lignes supprimer, notre guide complémentaire sur la suppression des doublons dans Excel traite de cette étape. Ici, l’objectif est d’examiner d’abord, dans Excel, afin de décider vous-même ce qui constitue un doublon et ce que vous conservez.

Mettre en surbrillance les doublons avec la mise en forme conditionnelle

C’est la vérification la plus rapide, et Microsoft la recommande comme étape de contrôle avant toute suppression. Elle suit l’article de Microsoft sur le filtrage des valeurs uniques ou la suppression des valeurs en double.

  1. Sélectionnez les cellules, la colonne ou la plage à contrôler. Une seule colonne suffit généralement.
  2. Sous l’onglet Accueil, ouvrez Mise en forme conditionnelle.
  3. Choisissez Règles de mise en surbrillance des cellules, puis Valeurs en double.
  4. Sélectionnez un format, par exemple un remplissage rouge clair, puis cliquez sur OK.

Toutes les cellules dont la valeur apparaît plusieurs fois sont désormais colorées. Faites défiler la feuille, ou filtrez la colonne par couleur pour n’afficher que les cellules surlignées. Pour annuler, revenez à la Mise en forme conditionnelle et effacez les règles de la plage.

Une seconde méthode offre davantage de contrôle. Ouvrez Mise en forme conditionnelle > Gérer les règles, créez une Nouvelle règle et choisissez Mettre en forme uniquement les valeurs uniques ou en double. Dans la liste Mettre en forme tout, vous pouvez choisir uniques ou en double, et définir le format des nombres, la police, la bordure ou le remplissage. C’est pratique lorsque vous souhaitez colorer les valeurs qui n’apparaissent qu’une fois et laisser les valeurs répétées telles quelles.

Deux limites à connaître. Microsoft indique qu'Excel ne peut pas mettre en évidence les doublons dans la zone Valeurs d'un tableau croisé dynamique. Par ailleurs, lorsque vous sélectionnez une seule colonne, la règle n'examine qu'une colonne à la fois. Une ligne qui ne se répète que lorsque l'on considère plusieurs colonnes ensemble ne sera donc pas mise en évidence. Pour ce cas, il faut une formule.

Compter et signaler les doublons avec COUNTIF

Une colonne auxiliaire vous donne un nombre que vous pouvez trier et filtrer. C'est aussi l'outil adapté lorsque deux lignes ne sont des doublons que si deux ou trois colonnes correspondent.

Signaler toutes les répétitions

Supposons que vos valeurs se trouvent dans A2:A100. Saisissez en B2 :

=COUNTIF($A$2:$A$100,A2)

Recopiez la formule vers le bas. Un résultat de 1 signifie que la valeur est unique. Un 2 signifie qu'elle apparaît deux fois, et ainsi de suite. Chaque exemplaire d'une valeur répétée reçoit le même nombre, y compris le premier. Pour transformer ce nombre en VRAI ou FAUX, ajoutez une comparaison : =COUNTIF($A$2:$A$100,A2)>1.

Ne signaler que les exemplaires suivants

Lorsque vous prévoyez de supprimer des lignes, vous souhaitez généralement conserver la première occurrence. Utilisez alors une plage qui s'agrandit :

=COUNTIF(A$2:A2,A2)>1

La plage A$2:A2 s'agrandit à mesure que vous recopiez la formule vers le bas. La première fois qu'une valeur apparaît, vous obtenez FAUX. Chaque apparition suivante donne VRAI. Cela correspond au fonctionnement de la commande Supprimer les doublons d'Excel, qui conserve la première occurrence.

Vérifier plusieurs colonnes avec COUNTIFS

Pour un enregistrement qui n'est un doublon que lorsque, par exemple, le prénom et le nom correspondent tous deux, utilisez COUNTIFS avec une paire plage-critère par colonne :

=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)

Un résultat supérieur à 1 signifie que la combinaison apparaît plus d'une fois. Ajoutez d'autres paires pour davantage de colonnes. N'essayez pas de reproduire le même contrôle en concaténant les plages dans COUNTIF, comme dans COUNTIF($A$2:$A$100&$B$2:$B$100,A2&B2). COUNTIF attend une véritable plage comme premier argument, de sorte que cette formule ne fait pas ce qu'elle semble faire. COUNTIFS est la solution propre.

Le comportement de COUNTIF qui surprend

La page COUNTIF de Microsoft décrit certains comportements importants lorsque vous recherchez des doublons :

  • La correspondance de texte ne tient pas compte de la casse. « pommes » et « POMMES » sont comptés comme la même valeur. Excel les traite ici comme des doublons.
  • Les caractères ? et * agissent comme des caractères génériques. Si vos données en contiennent, par exemple dans des codes produit, COUNTIF peut trouver davantage de correspondances que prévu. Placez un tilde devant le caractère pour le rechercher littéralement.
  • COUNTIF renvoie des résultats incorrects pour les chaînes de plus de 255 caractères.
  • Les espaces en début ou en fin de cellule, les guillemets droits et typographiques mélangés et les caractères non imprimables peuvent tous fausser le comptage. Microsoft suggère d'utiliser les fonctions SUPPRESPACE et EPURER pour nettoyer les données au préalable.

Ce dernier point est à l'origine de la plupart des plaintes du type « Excel ne trouve pas mes doublons ». Deux cellules d'apparence identique peuvent différer par une espace finale. Nettoyez d'abord, comptez ensuite.

Filtrer et vérifier

Une fois la colonne auxiliaire en place, filtrez-la sur VRAI, ou sur les valeurs supérieures à 1, puis relisez les lignes. Vous pouvez trier par la colonne auxiliaire pour regrouper toutes les lignes répétées. Examinez un échantillon avant de supprimer quoi que ce soit. Il arrive souvent qu'un « doublon » se révèle être deux enregistrements réels qui partagent un même nom.

Afficher uniquement les valeurs en double

Il arrive que vous souhaitiez une courte liste de ce qui se répète, plutôt qu'un indicateur à côté de chaque ligne.

Dans Excel pour Microsoft 365, 2024 et 2021, la fonction UNIQUE peut faire le travail. Selon la page UNIQUE de Microsoft, =UNIQUE(array,[by_col],[exactly_once]) renvoie les valeurs distinctes d'une plage, et en définissant exactly_once sur VRAI, seules les valeurs qui n'apparaissent qu'une fois sont renvoyées. Ainsi, =UNIQUE(A2:A100) donne chaque valeur une seule fois, et =UNIQUE(A2:A100,,TRUE) donne les valeurs qui ne sont pas du tout dupliquées. Pour ne obtenir que les valeurs répétées, combinez UNIQUE avec FILTER et COUNTIF :

=UNIQUE(FILTER(A2:A100,COUNTIF(A2:A100,A2:A100)>1))

Cette formule conserve les lignes dont le nombre est supérieur à 1 et affiche chacune d'elles une seule fois. Elle nécessite une version d'Excel prenant en charge les matrices dynamiques. Testez-la sur quelques lignes avant de vous y fier sur une longue liste.

Dans les versions antérieures, utilisez une colonne auxiliaire. Saisissez =IF(COUNTIF($A$2:$A$100,A2)>1,A2,"") et recopiez la formule vers le bas. La colonne affiche désormais chaque valeur répétée et laisse des cellules vides pour les autres. Filtrez les cellules vides. Vous verrez chaque valeur répétée autant de fois qu'elle apparaît, ce qui convient pour une vérification.

Avec le filtre avancé, vous pouvez copier uniquement les enregistrements uniques vers un nouvel emplacement. Accédez à Données > Avancé, choisissez Copier vers un autre emplacement, saisissez une cellule dans la zone Copier vers et cochez Enregistrements uniques uniquement. Comparer cette liste à l’original permet de voir ce qui était répété.

Utiliser Power Query pour ne conserver que les doublons

Si vos données sont volumineuses ou arrivent chaque semaine, laissez Power Query repérer les doublons. La documentation Microsoft sur Power Query décrit une commande Conserver les doublons, à l’opposé de Supprimer les doublons. Elle filtre la table pour ne garder que les lignes dont les valeurs, dans les colonnes sélectionnées, apparaissent plus d’une fois. Contrairement à Supprimer les doublons, elle renvoie toutes les lignes correspondantes et non une seule par valeur.

L’article de Microsoft sur la conservation ou la suppression de lignes en double détaille les étapes pour la suppression. Le début est identique dans les deux cas :

  1. Sélectionnez une cellule de vos données et choisissez Requête > Modifier, ou chargez-les avec Données > À partir d’un tableau/d’une plage.
  2. Cliquez sur l’en-tête d’une colonne pour la sélectionner. Maintenez Maj ou Ctrl enfoncé pour sélectionner d’autres colonnes.
  3. Appliquez la commande. Les étapes de Microsoft pour la suppression utilisent Accueil > Supprimer les lignes > Supprimer les doublons. Recherchez la commande Conserver les doublons correspondante parmi les autres commandes de lignes de l’onglet Accueil, puis vérifiez l’aperçu.

Microsoft précise également que Power Query est sensible à la casse : « Apple » et « apple » sont donc deux valeurs différentes, contrairement à NB.SI. C’est une vraie différence entre les deux méthodes. Si votre objectif est un contrôle hebdomadaire reproductible, décidez d’un comportement et tenez-vous-y, par exemple en convertissant d’abord la colonne en majuscules.

Rechercher les doublons entre deux feuilles ou listes

Une tâche courante consiste à vérifier si une liste figurant sur une feuille apparaît aussi sur une autre : une nouvelle liste de clients comparée à l’ancienne, ou les commandes de ce mois-ci comparées à celles du mois dernier.

  1. Placez les deux listes sur des feuilles distinctes, par exemple Feuil1 et Feuil2, avec les valeurs dans la colonne A.
  2. Dans Feuil1, en B2, saisissez =NB.SI(Feuil2!A:A;A2)>0.
  3. Faites glisser la formule vers le bas. VRAI signifie que la valeur figure aussi dans Feuil2.
  4. Filtrez sur VRAI pour voir les éléments communs, ou sur FAUX pour voir ce qui est nouveau.

Pour deux colonnes qui doivent correspondre ensemble, utilisez NB.SI.ENS avec un couple de critères pour chaque colonne et les plages de l’autre feuille.

Si la seconde liste se trouve dans un autre classeur, Microsoft avertit qu’une formule faisant référence à un classeur fermé peut renvoyer une erreur #VALEUR!. Gardez les deux classeurs ouverts pendant le travail, ou importez la seconde liste dans le premier avec Power Query. Après la vérification, gardez à l’esprit que le résultat dépend des données au moment où vous le faites. Si l’une des listes change, les indicateurs changent aussi.

Correspondances approximatives : Jon Smith et John Smith

Tout ce qui précède ne détecte que les correspondances exactes, à la casse près. Les listes réelles contiennent aussi des fautes de frappe et des orthographes différentes. Excel ne dispose d’aucune fonction intégrée qui mesure la similarité entre deux textes ; il faut donc procéder en deux temps.

Première étape : supprimer les fausses différences. Utilisez SUPPRESPACE pour retirer les espaces superflus et EPURAGE pour supprimer les caractères non imprimables, puis construisez le contrôle sur les valeurs nettoyées. Dans une colonne auxiliaire, =SUPPRESPACE(A2) affiche le texte nettoyé. Comptez les doublons sur cette colonne et non sur l’original. Comme NB.SI ignore la casse, il n’est pas nécessaire de modifier la casse à ce niveau. Dans Power Query, où la casse compte, convertissez la colonne en majuscules ou en minuscules.

Étape deux : examinez ce qui reste. Triez la liste nettoyée par ordre alphabétique et parcourez les lignes voisines. Les correspondances approchantes, comme « Jon Smith » et « John Smith », se retrouvent côte à côte, ce qui permet à un humain de les repérer. Pour les listes plus volumineuses, Microsoft Research a publié le complément Fuzzy Lookup pour Excel. Il peut repérer des lignes en double approximatives dans un même tableau ou faire correspondre des lignes similaires entre deux tableaux, et il attribue à chaque correspondance un score de similarité. Vérifiez la configuration requise avant de prévoir son utilisation : la page de téléchargement mentionne Windows, avec Excel 2007, 2010, 2013 et 2016, ainsi qu’une version de 2020. Il risque de ne pas s’installer sur une configuration Microsoft 365 actuelle, et il n’est pas disponible sur Mac.

Quel que soit l’outil utilisé, vérifiez les résultats approximatifs à la main. Deux personnes aux noms similaires ne sont pas forcément la même personne.

Erreurs courantes

  • Vérifier une seule colonne alors que le doublon est défini par deux colonnes. Un nom de famille répété ne suffit pas à faire d’un enregistrement un doublon.
  • Faire confiance aux comptages COUNTIF sur des données contenant des espaces parasites ou des caractères inhabituels. Nettoyez d’abord les données avec TRIM et CLEAN.
  • Oublier que COUNTIF ne tient pas compte de la casse alors que Power Query la prend en compte, puis obtenir des résultats différents avec les deux.
  • Utiliser COUNTIF($A$2:$A$100,A2)>1 et supprimer toutes les lignes marquées VRAI. Cette formule signale aussi la première occurrence, si bien que vous supprimeriez toutes les copies. Utilisez la plage étendue pour ne signaler que les occurrences suivantes.
  • Rechercher des doublons dans des colonnes entières avec des chaînes de plus de 255 caractères, cas où COUNTIF donne des résultats erronés.
  • Supprimer immédiatement après la mise en surbrillance. Copiez d’abord la feuille, car Microsoft indique que les doublons supprimés le sont définitivement.

Sujets connexes à explorer

  • Supprimez les doublons trouvés. La commande Supprimer les doublons de l’onglet Données conserve la première occurrence et supprime les autres. Triez d’abord les données pour contrôler la ligne qui restera.
  • Conservez le meilleur enregistrement, pas le premier. Triez par date ou par valeur pour placer en premier la ligne souhaitée, puis signalez les copies suivantes avec le COUNTIF à plage étendue.
  • Comparez des listes entre plusieurs fichiers. Power Query peut combiner plusieurs classeurs en un seul tableau afin de les vérifier ensemble.
  • Empêchez la création de nouveaux doublons. La validation des données avec une formule personnalisée peut bloquer une valeur déjà existante. Essayez-la sur une feuille de test.
  • Conservez une piste d’audit. Gardez la colonne d’aide dans une copie de la feuille afin de pouvoir montrer ultérieurement ce qui a été signalé et pourquoi.

Prochaine étape

Choisissez une colonne de votre fichier actuel, mettez en évidence ses doublons avec la mise en forme conditionnelle et comptez le nombre de cellules colorées. Ajoutez ensuite une colonne d’aide avec COUNTIF et comparez les deux résultats. S’ils diffèrent, vous venez de détecter des espaces cachés ou des différences de casse, et vous savez désormais comment repérer des doublons dans Excel avant de supprimer quoi que ce soit.

Sources

Entités dans cet article

Microsoft Excel Power Query

Articles associés

RSS