Pourquoi rechercher les doublons est si important ?
Rechercher les doublons dans une colonne est un travail assez courant dans Excel. Si vous travaillez avec des listes de fournisseurs, de clients, de pièces, .... la duplicité de ces données est une source d'erreur très impactante.
Par exemple, si la référence d'un client est comptabilisée 2 fois dans votre table de références, vos formules comptabiliseront 2 fois les résultats pour ce client ??
Mise à jour
ATTENTION ! Les nouvelles fonctions d'Excel 365, Excel 2019 et Excel Online propose des nouvelles fonctions plus facile à utiliser dans le cas de la recherche des valeurs en double par formule.
Utiliser l'un ou l'autre des liens sur vous travaillez avec avec une version récente d'Excel.
Formule pour rechercher les doublons
Si vous travaillez avec les autres versions d'Excel, la formule pour trouver les doublons utilise la fonction NB.SI ou encore NB.SI.ENS
Trouver les doublons revient à compter le nombre de fois que le contenu d'une cellule est présent dans une colonne.
- Nous commençons d'abord par placer notre cellule active à coté de la colonne à analyser
- Ensuite, nous écrivons le début de notre formule
- Puis, vous sélectionnez la plage de cellules à analyser
- Enfin, sélectionnez comme critère la première cellule de la liste
=NB.SI.ENS($C$2:$C$11;C2)
Recopiez cette formule pour toutes les autres cellules afin de comptabiliser le nombre de fois que les valeurs ont été trouvées dans la liste.
Analyser les résultats
De façon assez évidente, à chaque fois que la fonction NB.SI.ENS retourne 1 signifie que la valeur est unique
Si le résultat est 2 signifie que la valeur est présente 2 fois dans la colonne. Si le résultat est 3, nous sommes en présence d'un triplet et ainsi de suite...
Présentation des résultats
Pour une meilleure lisibilité, vous pouvez trier vos données
- Sur la colonne des résultats en ordre décroissant (première clé de tri)
- Puis la colonne analysée (deuxième clé)
Le résultat montre les valeurs les plus présentes au début de votre liste.
Trouver les doublons sur plusieurs colonnes
Ici, seule la fonction NB.SI.ENS peut être utilisée car elle seule permet d'effectuer un dénombrement sur plusieurs colonnes.Nous voulons maintenant le nombre de fois que le Prénom + Nom est présent dans notre liste.
Dans notre formule initiale, nous allons rajouter comme nouveau critère de sélection, la colonne des Noms et la valeur du Nom à rechercher.
=NB.SI.ENS($C$2:$C$11;C2;$D$2:$D$11;D2)
Le résultat est maintenant plus affiner que le simple test sur la colonne des prénoms. Vous notez cette fois que le prénom Paul est toujours présent 3 fois mais pour 2 personnes différentes. Et de la même façon si nous souhaitons trouver les doublons entre les colonnes des Prénom, Nom et Ville, nous écrirons la formule suivante :
=NB.SI.ENS($C$2:$C$11;C2;$D$2:$D$11;D2;$E$2:$E$11;E2)
Maintenant, seule Clara MULLER de Strasbourg est présente 2 fois dans la liste. Paul SMITH sont en fait 2 personnes différentes qui habitent dans 2 villes distinctes.
Erreurs à éviter
Comme vous le constatez, la formule pour trouver les doublons est simple et courte. Mais ce n'est pas pour autant qu'il faut négliger certains points de son écriture.
Ordre des paramètres
En langage naturel on dit : "Je cherche la valeur x dans la colonne A". De fait, on aurait tendance à écrire =NB.SI.ENS("x";Plage de données) alors qu'en fait c'est l'inverse qu'il faut écrire
- Toujours écrire la plage de données dans laquelle se trouve la valeur à chercher en premier paramètre
- Puis en 2ème paramètre la valeur recherchée.
Ne pas sélectionner toute une colonne
- Beaucoup d'utilisateurs sélectionnent la colonne entière en cliquant sur l'entête de colonne. La formule s'écrit alors =NB.SI.ENS(A:A;"x") et ce n'est vraiment pas bon ⛔
- En effet, si vous travaillez de cette façon, la sélection est très rapide
- Mais, vous sélectionnez 1 048 576 cellules. Donc la fonction va "lire" chacune de ces cellules (performances très mauvaises).
Astuce ! Pour vous assurer de sélectionner uniquement les cellules à analyser et en un seul clic, il faut que vous mettiez vos données dans une Table. Consultez cet article pour connaître toutes les astuces liées aux Tables.
The Plouc
Frédéric LE GUEN
Alexandre Pitton
Aurélie
GRANDCHAMP Alain
Chapi
Anonyme
willy
Benyagoub
Guy LE STRUJON
10/12/2023 @ 12:44
Bonjour,
Je cherche une formule pour savoir combien de fois je trouve "on" dans deux colonnes à la fois (et donc en même temps). Ceci sur EXCEL 2007.
Cordialement.
10/12/2023 @ 13:39
Excel 2007 est obsolète aujourd'hui, utilisez Excel Online plutôt, c'est gratuit
14/07/2023 @ 22:28
Bonjour à tous, je cherche la formule dans un tableau avec 2 fois ou plus le même numéro de facture afin d'aditionner le nombre présent.
Je vous remercie de votre aide.
11/05/2023 @ 14:35
Bonjour,
je souhaite comparer 2 colonnes texte pour mettre en relief les doublons
Ces 2 colonnes, dans le tableau représentent :
- L’une un listing exhibitors pour un salon
- La seconde est un extract d’une partie de notre fichier de nos Clients
Mon but étant de voir si un de nos clients de notre base serait présent au salon.
Le problème est que par exemple prenons le cas de STAUBLI : la 1ere colonne Staübli puis dans la seconde STÄUBLI WFT GmbH
Donc problème avec les accents, les majuscules (au pire je mets tout en majuscule pas un soucis), et plusieurs mots surtout dans les cellules
J’avais pensé à la RechercheV et la Fonction Si associée mais sans succès.
Pourriez-vous m'eclairer, je m'arrache les cheveux !
Merci beaucoup
12/05/2023 @ 00:52
Il faut utiliser Power Query pour ça. Il y a l'option fuzzy match dans les options de fusion.
J'en parle dans ma formation Power Query sur Udemy. Au vu de votre besoin, c'est la seule façon de procéder
16/02/2023 @ 14:49
Dans votre exemple, on connait la valeur à rechercher. Je cherche une solution pour un tableau qui établit un classement d'équipes. je cherche à repérer les équipes ayant le même nombre de points (que je ne connais pas à l'avance) et à appliquer un complément de traitement (meilleure attaque, etc...)
16/02/2023 @ 07:30
Bonjour je cherche une fonction qui après avoir ordone mes données en fonction des dates vas me permettre d'attribuer la valeur 1 au premier doublon trouve 2 au second 3 au 3eme ainsi de suite pour le même doublon
24/01/2022 @ 17:59
Bonjour,
merci pour votre article.
Autres formules que j'ai testée :
détecter doublon de deux colonnes par rapport à la ligne du dessus ou du dessous
=OU(ET(ET($A2=$A1; $B2=$B1); $B2""); ET(ET($A2=$A3; $B2=$B3); $B2""))
J'ai renoncé à la "SOMMEPROD", car Excel devenait ingérable (temps de calcul très long).
=SOMMEPROD(($A$2:$A2=$A3)*($B$2:$B2=$B3)*($B3""))
Cordialement.
21/06/2019 @ 15:17
Bonjour,
je souhaite que les doublons commence à partir du deuxième doublons et que le premier reste unique
merci d'avance
07/06/2019 @ 18:01
Bonjour. Comment exclure une valeur (dans mon cas le chiffre 0) présent plusieur fois dans des cellules et colonnes ?
Car le 0 apparait alors comme doublon.
Je vous remercie par avance.
Cordialement.
25/03/2019 @ 10:27
5 6 5 8 1 8 8 8
11 7 3 3 6 8 7 6
Voici deux lignes , il y a des chiffres en double et plus . Comment enlever les chiffres qui sont en trop ?
Merci plusieurs fois car je n'ai pas trouvé .
Cordialement
Guy Le Strujon
25/03/2019 @ 20:40
Si vous avez Excel avec Office 365, la fonction UNIQUE va vous permettre de faire ce que vous souhaitez.
Enfin, il faut que vos données soient en colonne.