Pourquoi rechercher les doublons est 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.

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

Si vous travaillez avec Office 2016 ou Office 365, la solution proposée avec Power Query est plus rapide.

Formule pour rechercher les doublons

La formule pour trouver les doublons dans une colonne est très simple. Vous pouvez indifféremment utiliser NB.SI ou NB.SI.ENS car toutes les 2 permettent un dénombrement sur une seule colonne.

Trouver les doublons revient à compter le nombre de fois que le contenu d'une cellule est présent dans une colonne.

1. Nous commençons d'abord par placer notre cellule active à coté de la colonne à analyser
2. Ensuite, nous écrivons le début de notre formule

=NB.SI.ENS(

3. Puis vous sélectionner la plage de cellule à analyser

=NB.SI.ENS($C$2:$C$11;

4. 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 somme en présence de 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 constater, 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, c'est très rapide à sélectionner mais pas efficace du tout en terme de calcul.

Quand, vous sélectionnez les 1 048 576 cellules de la colonne dans votre formule, la fonction NB.SI.ENS va devoir analyser chacune de ces cellules pour y trouver les doublons.

Ne vous étonnez pas ensuite que votre classeur soit lent ????????

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.

Utilisation des $

L'ajout des dollars dans les références de la plage de données est nécessaire pour verrouiller la colonne à analyser.

Si vous les omettez, lors de la recopie de la formule, les références de la colonne à analyser seront modifiées. Alors, vous ne serez plus focalisés exactement sur votre plage de références.