On ouvre un fichier client exporté d’un CRM, on lance un tri rapide, et les premières lignes semblent propres. Puis on filtre par adresse e-mail et on découvre trois « [email protected] » avec des variantes de prénom.
Le problème n’est pas de repérer les doublons évidents, c’est de coincer ceux qui passent entre les mailles : un espace insécable Unicode collé en fin de cellule, une majuscule en trop, un retour chariot invisible. Voici comment on ratisse large dans Excel sans laisser filer ces doublons furtifs.
Lire également : Excel formule soustraction avec condition SI, ET et OU
Doublons furtifs dans Excel : nettoyer avant de chercher
La plupart des méthodes de détection (mise en forme conditionnelle, NB.SI, Power Query) comparent des chaînes de caractères brutes. Si deux cellules contiennent le même nom mais que l’une cache un espace non-breaking Unicode (U+00A0) en fin de texte, Excel les considère comme deux valeurs distinctes. On passe alors à côté du doublon.
Avant toute recherche, on applique un pré-nettoyage systématique. Dans une colonne auxiliaire, on combine deux fonctions :
A lire aussi : Convertir Libre en Excel : comment faire étape par étape ?
=SUPPRESPACE(SUBSTITUE(A2;CARACTERE(160);" "))
SUBSTITUE remplace l’espace insécable (caractère 160) par un espace standard. SUPPRESPACE supprime ensuite les espaces superflus en début, fin et milieu de chaîne. On travaille ensuite sur cette colonne nettoyée pour lancer la détection.
On peut aller plus loin en ajoutant MINUSCULE() autour de la formule pour neutraliser les différences de casse. Un « DUPONT » et un « Dupont » deviennent identiques. Ce nettoyage prend deux minutes, mais il évite de valider un fichier « sans doublons » qui en contient encore des dizaines.
Formule NB.SI pour trouver les doublons sur une colonne Excel
Une fois les données nettoyées, NB.SI reste la formule la plus directe. Elle compte combien de fois une valeur apparaît dans une plage. Si le résultat dépasse 1, on a un doublon.
La formule dans une colonne auxiliaire (par exemple B2) :
=NB.SI($A$2:$A$500;A2)
Toute cellule renvoyant 2 ou plus signale un doublon. On filtre ensuite la colonne B pour afficher uniquement les valeurs supérieures à 1.
Repérer les valeurs dupliquées plus de deux fois
NB.SI ne se limite pas à la paire. Si une référence produit apparaît cinq fois, la formule renvoie 5. On peut trier par ordre décroissant pour identifier d’abord les doublons les plus fréquents, souvent symptômes d’un problème d’import récurrent.
NB.SI fonctionne sur une seule colonne à la fois. Pour croiser plusieurs critères (même nom ET même code postal, par exemple), il faut passer à NB.SI.ENS.
NB.SI.ENS et doublons multi-colonnes dans Excel
En gestion de fichiers clients ou de catalogues produits, un doublon se définit rarement par une seule cellule. On cherche les lignes où plusieurs champs coïncident. NB.SI.ENS gère ce cas :
=NB.SI.ENS($A$2:$A$500;A2;$C$2:$C$500;C2)
Cette formule compte les lignes où la colonne A (nom) ET la colonne C (code postal) correspondent simultanément. On peut empiler jusqu’à 127 paires critère/plage.
- Deux colonnes (nom + e-mail) suffisent souvent pour un fichier prospect classique
- Trois colonnes (référence + fournisseur + date) couvrent la majorité des audits de commandes
- Au-delà de quatre critères, on gagne du temps en passant à Power Query qui gère la déduplication visuelle par glisser-déposer
Les retours varient sur le seuil optimal de colonnes à croiser. Trop peu, et on remonte des faux positifs (deux homonymes dans la même ville). Trop, et on rate des doublons partiels. Commencer par deux critères puis affiner reste l’approche la plus fiable en pratique.
Mise en forme conditionnelle Excel pour visualiser les doublons
Quand on veut un repérage visuel sans formule auxiliaire, la mise en forme conditionnelle fait le travail. On sélectionne la plage, puis :
- Onglet Accueil, cliquer sur Mise en forme conditionnelle
- Choisir Règles de mise en surbrillance des cellules, puis Valeurs en double
- Sélectionner la couleur de surbrillance et valider
Toutes les cellules contenant une valeur présente au moins deux fois dans la plage sont colorées instantanément. Cette méthode est rapide pour un contrôle visuel sur quelques centaines de lignes.
Limites à connaître
La mise en forme conditionnelle compare cellule par cellule, pas ligne par ligne. Si la colonne A contient deux fois « Martin » mais avec des prénoms différents en colonne B, les deux cellules A seront surlignées alors qu’il ne s’agit pas de vrais doublons. Pour un contrôle multi-colonnes, on revient à NB.SI.ENS ou à une règle personnalisée basée sur CONCATENER.
Autre point : sur des classeurs volumineux (plusieurs dizaines de milliers de lignes), la mise en forme conditionnelle peut ralentir sensiblement le fichier. On la désactive après vérification en allant dans Mise en forme conditionnelle, puis Effacer les règles.
Power Query : dédupliquer un tableau Excel sans formule
Power Query, intégré à Excel depuis la version 2016, permet de supprimer les doublons sans écrire de formule et sans toucher aux données sources. On charge le tableau, on sélectionne les colonnes servant de critères, et on clique sur Supprimer les doublons dans l’onglet Accueil de l’éditeur.
Power Query conserve chaque étape dans un journal de transformations, ce qui facilite l’audit et la traçabilité. On peut revenir en arrière, modifier un critère ou ajouter une colonne de contrôle sans reprendre de zéro.
Pour les fichiers dépassant quelques centaines de milliers de lignes, Power Query offre des performances nettement supérieures à NB.SI appliqué sur une plage entière. La requête se rafraîchit à la demande, sans recalculer en continu comme une formule matricielle.
Vérification finale : comparer le total avant et après déduplication
Une fois les doublons traités, on vérifie le résultat avec un contrôle simple. On compare le nombre de lignes du tableau d’origine avec le nombre de valeurs uniques. La formule SOMMEPROD peut servir :
=SOMMEPROD(1/NB.SI(A2:A500;A2:A500))
Elle renvoie le nombre de valeurs distinctes dans la plage. Si ce chiffre correspond au nombre de lignes du tableau nettoyé, la déduplication est complète. Un écart signale des doublons résiduels, souvent causés par les fameux caractères invisibles évoqués plus haut.
Le réflexe à garder : toujours travailler sur une copie du fichier avant de supprimer quoi que ce soit. Un doublon apparent peut être une donnée légitime (deux commandes identiques passées à des dates différentes). La suppression aveugle coûte plus cher que la détection elle-même.
