«

»

Extraire sans doublon avec formule

Il est très facile d’extraire sans doublon les données d’un tableau grâce à l’option du menu Données. Il fonctionne très bien sur une ou plusieurs colonne mais dans certain cas de figure, c’est outil est inopérant. J’ai récemment travaillé sur un classeur d’adresse de clients en ne conservant que les données les plus récentes (adresse, téléphone, …). L’outil standard d’extraction sans doublon ne pouvait pas fonctionner. Pour solutionner le problème, je me suis servi d’une formule, d’un tri et d’un filtre pour trouver et éliminer les doublons.

Pour les experts d’Excel, il est possible de créer une formule matricielle qui peut extraire d’une colonne toutes les données uniques. Cette formule (très complexe) est dynamique. C’est à dire qu’à chaque modification de la liste source, la formule va instantanément réactualiser notre liste de valeurs uniques.

Outils Extraire sans doublon

En utilisant l’outil Données>Supprimer les doublons le résultat est inopérant car pour chaque ligne, il y a des différences (numéro de téléphone notamment)

Suppression_Doublons_Formule_1

 

Suppression_Doublons_Formule_2

Pour autant, nous savons que nous devons nettoyer notre liste de ses doublons. Pour y arriver, nous allons nous baser sur une formule.

Formule NB.SI.ENS




Toute l’astuce consiste à trouver un identifiant unique pour chaque individu (ici l’email), puis de comptabiliser le nombre de fois que nous retrouvons cet identifiant dans la liste. Nous allons donc nous baser sur la fonction NB.SI.ENS pour dénombrer le nombre de fois que nous trouvons le même email dans la liste MAIS en choisissant une référence mixte.

Donc, dans une nouvelle colonne, nous allons écrire la formule suivante et la recopier sur toutes les cellules de cette nouvelle colonne

=NB.SI.ENS($H$2:H2,H2)

Le résultat est le suivant et vous pouvez voir dans l’image qui suit que pour Richard SAVARD, nous retrouvons 3 fois son mails dans notre liste avec les valeurs 1, 2 et 3.

Suppression_Doublons_Formule_3

L’explication de cette formule est assez simple. Comme nous avons dans notre formule, la première référence de notre plage de données fixées ($H$2) et la seconde libre (H2) le fait de recopier cette formule avec la poignée de recopie vers le bas va changer uniquement la seconde référence.

Ainsi, la formule en I4 ne prendra en compte que les mails compris entre H2 et H4 (et donc là, l’email de Richard SAVARD n’est présent qu’une seule fois). Quand la formule atteint la cellule I11, cette fois-ci la plage de sélection va de H2 à H11. A ce moment, le mail de Richard SAVARD est présent 2 fois et ainsi de suite pour tout le document.

Regardez les formules dans l’image suivante pour voir comment évolue la formule quand elle est recopiée.

Suppression_Doublons_Formule_4

Tri des données

Notre formule fonctionne parfaitement mais il ne faut pas oublier de trier notre tableau. Il faut impérativement trier les dates par ordre décroissant pour avoir les dates de mises à jour les plus récentes en premier. Nous simplifier le résultat, nous pouvons trier sur le mail en premier critère de tri puis les dates de mises à jour.

Allez dans le menu Données>Trier et créer l’ordre de tri suivant

Suppression_Doublons_Formule_5

Après validation, les mails en doubles ou triples, se retrouvent les uns sous les autres.

Suppression_Doublons_Formule_6


Filtrer les données

Le travail est presque terminé. Il nous suffit maintenant d’utiliser le filtre d’Excel (Données>Filtre) sur la colonne que nous avons ajouté précédemment et de décocher la valeur 1.

Suppression_Doublons_Formule_7

De cette façon, nous n’affichons que les éléments que nous voulons supprimer.

Suppression_Doublons_Formule_8

Sélectionnez toutes les lignes visibles et supprimez les avec un Clic-droit>Supprimer la ligne ou le raccourci clavier Ctrl + (moins ou 6)

Formule matricielle

Il est également possible d’extraire par une formule matricielle une liste de valeur unique. Cette formule est l’oeuvre de notre maître à tous en matière de formule matricielle à savoir, Mike “ExcelIsFun” Girvin. Si les formules matricielles vous intéresse, je vous renvoie vers son livre (en anglais) pour vous familiariser avec cet univers étrange que sont les formules matricielles dans Excel.

La formule pour extraire toutes les valeurs uniques d’une liste est la suivante :

=INDEX(Ma_Colonne;PETITE.VALEUR(SI(FREQUENCE(SI(Ma_Colonne<>””;EQUIV(Ma_Colonne;Ma_Colonne;0)); LIGNE(Ma_Colonne)-LIGNE($B$2)+1);LIGNE(Ma_Colonne)-LIGNE($B$2)+1);LIGNES(I$2:I2)))

Pour valider une formule matricielle vous devez appuyer simultanément sur les touches Ctrl + Shift + Entrée




Pour adapter cette formule à votre besoin, vous devez remplacer :

  • Ma_Colonne par la plage de cellule qui contient les données que vous voulez extraire
  • B2 par la première cellule de votre colonne où il y a les doublons
  • I2 par la cellule dans laquelle vous voulez voir le résultat (normalement écrire la référence de la cellule  du résultat dans la formule de calcul de cette même formule entraîne une référence circulaire mais pas dans le cas d’une formule matricielle)

Tout le coeur de la formule, c’est la partie PETITE.VALEUR. Cette partie récupère la position de la prochaine valeur différente des précédentes. Et comme cette formule est incluse dans la fonction INDEX, nous ramenons toutes les valeurs uniques de notre colonne.



Lien Permanent pour cet article : https://www.excel-exercice.com/extraire-sans-doublon-avec-formule/

(1 commentaire)

  1. bimbo

    Bonjour, svp y a t-il une fonction qui nous permet de visualisé les doublons et de les transféré dans une nouvel feuille
    Merci

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Vous pouvez utiliser ces balises et attributs HTML : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Advertisment ad adsense adlogger