On reçoit un fichier de contacts, on lance un tri rapide, et la colonne « e-mail » affiche trois fois la même adresse, mais avec des espaces en trop ou des cellules vides intercalées. La mise en forme conditionnelle standard colore tout en rouge sans distinguer ces cas. Pour trouver les doublons dans Excel de façon fiable, on a besoin d’une formule personnalisée qui gère ces exceptions, et qui se copie d’une feuille à l’autre sans retouche.
Pourquoi NB.SI seul ne suffit pas pour détecter les doublons Excel
La fonction NB.SI est le réflexe classique. On écrit =NB.SI(A:A;A2)>1 et chaque cellule présente plus d’une fois se retrouve repérée. Le problème apparaît dès que les données sont un peu sales.
Lire également : Analyse de données Excel : Conseils et astuces pratiques pour débutants
Une cellule vide compte comme une valeur. Si la colonne contient dix lignes vides, NB.SI les considère toutes comme des doublons entre elles, ce qui fausse le résultat. Un espace invisible en fin de texte transforme « Dupont » et « Dupont » en deux valeurs distinctes, et le doublon passe inaperçu.
On se retrouve avec deux problèmes opposés : des faux positifs sur les cellules vides et des faux négatifs sur les textes mal saisis. La formule personnalisée qu’on va construire règle les deux cas d’un coup.
A lire aussi : Comment calculer pourcentage sur Excel et éviter les erreurs de formule ?
Formule personnalisée Excel pour repérer les doublons avec exceptions
L’idée est de combiner trois fonctions dans une seule cellule : SUPPRESPACE pour nettoyer les espaces superflus, NB.SI.ENS pour compter avec critère, et un test sur la cellule vide. Voici la formule, en supposant que les données sont en colonne A à partir de A2 :
=SI(A2="";"";SI(NB.SI.ENS(A:A;SUPPRESPACE(A2))>1;"Doublon";"Unique"))
On décompose ce qui se passe à chaque étape :
- Le premier SI vérifie si la cellule est vide. Si oui, la formule renvoie une chaîne vide au lieu de marquer un faux doublon.
- SUPPRESPACE(A2) retire les espaces en début, en fin et les doubles espaces internes avant la comparaison.
- NB.SI.ENS compare la valeur nettoyée à toute la colonne, et renvoie « Doublon » dès que le compte dépasse 1.
Cette formule se copie vers le bas sans modification. On peut aussi la coller dans une autre feuille : il suffit de remplacer A:A par le nom de la colonne cible.
Adapter la formule pour ignorer la casse
NB.SI.ENS ne distingue pas les majuscules des minuscules par défaut, ce qui convient dans la plupart des cas. Si on travaille avec des codes produits où « abc-12 » et « ABC-12 » doivent rester distincts, on remplace NB.SI.ENS par SOMMEPROD :
=SI(A2="";"";SI(SOMMEPROD((EXACT(SUPPRESPACE(A2);SUPPRESPACE(A$2:A$1000)))*1)>1;"Doublon";"Unique"))
La fonction EXACT force la comparaison stricte caractère par caractère. SOMMEPROD additionne les correspondances exactes sur la plage, ce qui permet de détecter les vrais doublons sensibles à la casse.
Doublons autorisés par catégorie dans un tableau Excel
Situation fréquente : on gère un fichier de commandes où le même client peut apparaître plusieurs fois, et c’est normal. On veut repérer les doublons uniquement quand le même nom revient dans la même catégorie (même ville, même service, même mois).
NB.SI.ENS accepte plusieurs paires critère/plage. On ajoute la colonne catégorie comme second critère :
=SI(A2="";"";SI(NB.SI.ENS(A:A;SUPPRESPACE(A2);B:B;B2)>1;"Doublon catégorie";"OK"))
Ici, la colonne B contient la catégorie (ville, département, service). La formule ne signale un doublon que si le nom ET la catégorie correspondent. Un même client dans deux villes différentes ne sera pas marqué.
Exclure des valeurs spécifiques de la détection
On peut aller plus loin. Certaines valeurs reviennent par conception : « Non renseigné », « N/A », « Divers ». Pour les exclure, on imbrique un test supplémentaire :
=SI(OU(A2="";A2="N/A";A2="Non renseigné");"";SI(NB.SI.ENS(A:A;SUPPRESPACE(A2))>1;"Doublon";"Unique"))
La fonction OU liste toutes les valeurs qu’on considère comme des exceptions. Ajouter une exception revient à insérer un terme dans la liste OU, sans toucher au reste de la formule.
Rendre la formule réutilisable sur plusieurs feuilles Excel
Copier une formule dans un nouveau fichier et devoir tout réajuster, c’est exactement ce qu’on veut éviter. Deux approches rendent la formule portable.
Plages nommées pour figer la référence
On sélectionne la colonne de données, puis on lui attribue un nom via Formules > Définir un nom (par exemple « ListeNoms »). La formule devient :
=SI(A2="";"";SI(NB.SI.ENS(ListeNoms;SUPPRESPACE(A2))>1;"Doublon";"Unique"))
Quand on duplique la feuille, la plage nommée suit. On évite les erreurs de référence liées aux colonnes décalées.
Mise en forme conditionnelle avec la formule personnalisée
Plutôt que d’afficher « Doublon » dans une colonne auxiliaire, on peut colorer directement les cellules. On sélectionne la plage A2:A1000, puis Accueil > Mise en forme conditionnelle > Nouvelle règle > Utiliser une formule. On entre :
=ET(A2<>"";NB.SI.ENS(A:A;SUPPRESPACE(A2))>1)
On choisit un remplissage rouge clair, on valide. Chaque cellule en doublon (hors cellules vides) se colore automatiquement. Cette règle se copie avec la feuille et reste active sans intervention.
Pièges fréquents et limites à connaître
SUPPRESPACE ne supprime que les espaces classiques (caractère 32). Les données copiées depuis un site web contiennent parfois des espaces insécables (caractère 160). Pour les traiter, on remplace SUPPRESPACE(A2) par SUBSTITUE(SUPPRESPACE(A2);CAR(160); » »).
NB.SI.ENS sur une colonne entière (A:A) peut ralentir le calcul sur des fichiers volumineux. Si le tableau dépasse plusieurs milliers de lignes, on restreint la plage à la zone réellement utilisée (A$2:A$5000 par exemple).
- Les cellules contenant des formules qui renvoient « » (chaîne vide) ne sont pas identiques aux cellules réellement vides. Le test A2= » » couvre les deux cas, mais attention aux fonctions qui renvoient 0 au lieu de « ».
- Si on utilise NB.SI.ENS avec SUPPRESPACE dans le critère mais pas dans la plage, la comparaison peut échouer. Les retours varient sur ce point selon la version d’Excel.
- Sur Excel pour le web, certaines fonctions imbriquées se comportent différemment. On teste toujours la formule sur quelques lignes avant de l’appliquer à tout le tableau.
La formule construite ici couvre la majorité des cas terrain : cellules vides, espaces parasites, doublons conditionnels par catégorie et valeurs à exclure. Elle tient en une seule cellule, se copie sans retouche et s’intègre aussi bien en colonne auxiliaire qu’en mise en forme conditionnelle. Le plus efficace reste de la stocker dans un classeur modèle qu’on réutilise à chaque nouveau projet de nettoyage de données.
