Eliminer automatiquement les doublons par calculs Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Eliminer les doublons par calcul Excel

La fonctionnalité Excel permettant de supprimer les doublons est à la fois efficace et intéressante. Elle procure néanmoins l'inconvénient de livrer un résultat détaché de la source. Si cette dernière évolue, implémentant de nouvelles saisies, l'extraction purgée de doublons ne se met pas à jour. Nous proposons de créer une petite application Excel, sans code VBA, en trois volets.



Dans ce premier volet, nous proposons de présenter la solution permettant d'extraire les données de bases Excel sans doublons, grâce aux calculs. Nous appliquerons ces résultats pour produire des listes déroulantes permettant de choisir parmi ces valeurs uniques. Dans un deuxième volet, illustré par une formation suivante, nous apporterons la solution pour que ces extractions soient articulées les unes en fonction des autres, afin de produire des listes déroulantes reliées entre elles. Dans une troisième formation, nous proposerons les calculs permettant d'extraire les données dans une zone d'extraction, selon les choix réalisés dans ces listes reliées et purgées de redondances. Au final, nous aurons donc bâti une application Excel permettant d'extraire les informations de base de données, purgées de doublons, grâce à des choix dynamiquement recoupés par des listes déroulantes en cascade. L'application finalisée sera articulée essentiellement par les formules Excel. A aucun moment le code Visual Basic ne doit intervenir.

Sources et présentation du concept
Pour réaliser une telle application, nous avons besoin de matière. C'est pourquoi nous proposons de récupérer un classeur proposant notamment une base de données.

• Télécharger le classeur eliminer-doublons-calculs.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous affichons ainsi le classeur Excel constitué de deux feuilles. La première (bd_sorties) correspond à la base de données à partir de laquelle nous souhaitons extraire des listes purgées des doublons. Ces listes devront conduire à des zones de choix permettant à l'utilisateur de désigner une valeur unique parmi d'autres. Cette base de données énumère des idées de sorties recensées par département, activité et ville.

Dans la seconde feuille (listes), la structure du tableau de bord en trois étapes est prévue en conséquence. En ligne 5 de l'étape 1, nous devrons restituer des listes déroulantes proposant de choisir respectivement un département unique, une activité unique et une ville unique.



En ligne 5 de l'étape 2, nous allons reconstruire les sources de données de ces listes, purgées des doublons. Dans un premier temps, nous ne chercherons pas à les relier entre elles. Concrètement, le choix d'un département depuis la première liste déroulante ne restreindra pas le choix des activités et des villes, en fonction de cette première sélection. Comme indiqué plus haut, c'est dans un deuxième volet, que nous nous occuperons d'établir le lien entre les listes déroulantes par les calculs Excel. En décomposant la construction de l'application par étapes, nous gagnerons en clarté.

Extraire les données sans doublons par calculs
Avant de mettre en place les formules permettant de reconstruire des listes de données purgées des valeurs redondantes, nous proposons de réaliser un tri sur le tableau de la base de données. Cette réorganisation n'est pas importante pour l'extraction des valeurs sans doublons. Mais elle permettra de restituer les éléments ainsi recomposés dans l'ordre alphabétique croissant. Nous gagnerons en ergonomie pour l'utilisateur.

• Cliquer sur l'onglet bd_sorties en bas de la fenêtre Excel pour activer sa feuille,
• Puis, sélectionner l'une des cellules du tableau, par exemple B2,
• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Trier et filtrer du ruban, cliquer sur le bouton Trier,
• Avec la première liste déroulante de la boîte de dialogue Tri qui apparaît, sélectionner le champ Département,
• Cliquer alors sur le bouton Ajouter un niveau en haut de la boîte de dialogue,
• Dans la nouvelle liste qui apparaît, choisir le champ Activité,
• Cliquer de nouveau sur le bouton Ajouter un niveau,
• Dans la nouvelle liste déroulante, choisir le champ Ville,
• Cliquer sur le bouton Ok pour valider ces réglages,

Nous réalisons ainsi une organisation croissante des données sur trois niveaux hiérarchiques de tris. Les enregistrements de la base sont tout d'abord triés dans l'ordre alphabétique croissant des départements. Pour chaque département, ils sont ensuite triés dans l'ordre croissant des activités. Enfin, pour chaque activité du département, les données sont triées dans l'ordre croissant des villes. Ainsi, lorsque nous aurons réussi à extraire les sources sans doublons, les valeurs se proposeront naturellement dans l'ordre alphabétique.



Vous remarquez la présence de colonnes vides respectivement en C, E et G. Elles doivent servir à des calculs intermédiaires permettant de repérer les valeurs uniques pour chacun des champs correspondants : Département, Activité et Ville. Ces repères doivent consister en des numéros incrémentés qu'une recherche depuis la feuille listes, permettra de restituer dans la zone des sources de données, pour les listes déroulantes. La fonction Excel Nb.Si permet de compter les cellules répondant à un critère sur une plage de données. Par exemple, en colonne C, il s'agit de vérifier si le département (D) de la ligne en cours a déjà été recensé. Si c'est le cas, il ne doit plus l'être pour ne conserver que les valeurs uniques. Dans le cas contraire, il doit être repéré par un numéro incrémenté qui permettra de l'extraire. Ces conditions peuvent se vérifier grâce la fonction Excel conditionnelle Si.

• Dans la feuille bd_sorties, sélectionner la cellule C2,
• Taper le symbole = pour débuter le calcul,
• Taper le nom de la fonction conditionnelle suivi d'une parenthèse, soit Si(,
• Saisir alors le nom de la fonction de dénombrement suivi d'une parenthèse, soit Nb.Si(,
• Cliquer sur la première cellule de la colonne des départements, soit D1,
• Taper le symbole des deux points (:), pour générer une plage, soit D1:D1,
• Sélectionner la première référence des deux et enfoncer deux fois la touche F4 du clavier, soit D$1:D1,

Ainsi, nous ne figeons que la borne supérieure de la plage de cellules en ligne par les références absolues.

• A la suite de la formule, taper un point-virgule (;),
• Sélectionner alors le premier département, soit la cellule D2,

Nous désignons ainsi le premier des départements à analyser pour l'extraction sans doublons.

• Fermer la parenthèse de la fonction Nb.Si,
• Puis, taper le critère : >0,
• Taper alors un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir deux guillemets ('') pour indiquer de conserver la cellule vide,

En effet, si le critère est vérifié, nous savons que le département en cours d'analyse (D2) a déjà été comptabilisé (>0).

• Taper un point-virgule pour passer dans la branche Sinon de la fonction Si,
• Saisir le nom de la fonction retournant la plus grande valeur dans une plage de cellules, suivi d'une parenthèse, soit max(,
• Sélectionner alors la première cellule de la colonne en cours, soit C1,
• Comme précédemment, taper le symbole deux points (:), pour générer une plage,
• Puis, figer la première des deux en ligne seulement, soit C$1:C1,
• Fermer la parenthèse de la fonction Max,
• Taper : +1 pour l'incrémentation,
• Fermer la parenthèse de la fonction Si,
• Valider la formule par le raccourci clavier CTRL + Entrée,

Ce raccourci permet de conserver la cellule active en vue de l'exploiter dans la foulée. La formule que nous avons bâtie est la suivante :

=SI(NB.SI(D$1:D1;D2)>0; ''; MAX(C$1:C1) + 1)

Nous raisonnons sur des plages de cellules mouvantes. Elles doivent progresser en même temps que la calcul est répliqué sur les cellules du dessous (D$1:D1). Ainsi, le département en cours (D2) est comparé aux résultats précédents et non suivants, pour savoir s'il a déjà été comptabilisé (Nb.Si > 0). Lorsque ce n'est pas le cas, sur cette plage de cellule en progression (C$1:C1), nous lui attribuons un numéro supérieur au précédent, pour faciliter sa reconnaissance.

• Double cliquer sur la poignée du calcul en C2 pour le répliquer sur la hauteur du tableau,

Comme l'illustre la capture ci-dessus, à chaque fois qu'un nouveau département est découvert, un numéro incrémenté par rapport au précédent, lui est attribué. Nous plaçons ainsi des repères en regard de chaque donnée unique à extraire dans la feuille listes.

Mais avant cela, comme nous avons judicieusement figé les plages de cellules, nous devons répliquer la logique du calcul pour répertorier les activités et villes uniques. Souvenez-vous, nous avions figé la première cellule des plages en ligne seulement (D$1:D1). Donc, en copiant le calcul, sur les autres rangées, l'indice de colonne doit s'adapter.

• Sélectionner de nouveau la cellule C2,
• Copier l'intégralité de son contenu (CTRL + C),
• Puis, le coller (CTRL + V) en cellule E2,
• Double cliquer alors sur la poignée de la cellule E2,
• Coller de nouveau le calcul en cellule G2,
• Puis, double cliquer sur la poignée de la cellule G2 pour reproduire son calcul,

Comme vous le constatez, le calcul s'adapte parfaitement. Chaque nouvelle activité pas encore recensée est marquée d'un numéro incrémenté. Il en va de même pour les villes.

Construire les listes de valeurs uniques
Nous devons exploiter ces résultats de calculs intermédiaires, pour reconstruire les listes de données sans doublons, dans la feuille liste. Il s'agit des colonnes I, J et K pour respectivement fournir la liste des départements uniques, celle des activités sans doublons et celle des villes uniques. Pour chacune d'elles, il faut être en mesure de chercher le numéro incrémenté dans la source de données. Ainsi, toutes les cellules vides seront ignorées et toutes les autres seront extraites. La fonction permettant de rechercher une valeur pour extraire une correspondance, est la fonction RechercheV selon la syntaxe suivante :

=RechercheV(Valeur_cherchée ; Tableau_de_recherche ; num_colonne_retour ; Faux)

La valeur cherchée est le numéro incrémenté dans la colonne du calcul de la feuille bd_sorties. Nous proposons pour cela d'exploiter la fonction Excel Ligne, depuis la première cellule, afin de retourner les incréments recherchés de ligne en ligne. Sa syntaxe est la suivante :

=Ligne(Référence_cellule)

Le tableau de recherche correspond pour le département par exemple, aux deux colonnes C et D. La colonne C est celle de la recherche, la colonne D est celle de la valeur correspondante à extraire. Il s'agit donc de la deuxième (Valeur 2 en troisième argument de la fonction de recherche). Le dernier argument de la fonction RechercheV est un booléen. Réglé à False, il indique que la recherche doit se faire selon une correspondance exacte.

• Cliquer sur l'onglet listes en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la cellule I5 et taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction gérant les erreurs suivi d'une parenthèse, soit SiErreur(,
• Taper le nom de la fonction de recherche suivi d'une parenthèse, soit RechercheV(,
• Saisir le nom de la fonction pour l'indice de ligne suivi d'une parenthèse, soit Ligne(,
• Cliquer sur la toute première cellule de la rangée pour la désigner, soit I1,
• Fermer la parenthèse de la fonction Ligne,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Cliquer sur l'onglet bd_sorties en bas de la fenêtre Excel pour désigner sa feuille,
• Sélectionner les colonnes C et D par leurs étiquettes, ce qui donne : bd_sorties!C:D,
• Taper un point-virgule pour passer dans l'argument du numéro de colonne de retour,
• Saisir le chiffre 2 suivi d'un point-virgule,
• Saisir le booléen False et fermer la parenthèse de la fonction RechercheV,
• Taper un point-virgule pour passer dans l'argument de la gestion d'erreur,
• Saisir deux guillemets ('') pour conserver la cellule vide en cas de souci,
• Fermer la parenthèse de la fonction SiErreur,
• Valider la formule par le raccourci CTRL + Entrée pour conserver la cellule active,
• Tirer sa poignée sur une trentaine de cellules vers le bas pour reproduire le calcul,

La formule que nous avons bâtie est la suivante :

=SIERREUR(RECHERCHEV(LIGNE(I1); bd_sorties!C:D; 2; FAUX); '')



Les départements sont effectivement répliqués dans la colonne du calcul. Et malgré leurs redondances dans la source de données, ils sont restitués sans doublons dans la feuille Liste. C'est l'astuce de la recherche sur le numéro de ligne qui a permis de les trouver et de les extraire. En partant de la première cellule de la colonne (LIGNE(I1)), nous savions qu'en reproduisant le calcul, tous les numéros seraient incrémentés, donc cherchés.

Vous remarquez que des bordures sont apparues instantanément. C'est une mise en forme conditionnelle, similaire à celles que nous avions exploitées pour bâtir les calendriers et plannings automatiquement, qui est prédéfinie. Comme l'illustre la capture ci-dessous, le critère consiste à dessiner une bordure, lorsque la cellule n'est pas vide.



Il s'agit maintenant de reproduire la même logique de calcul, pour restituer les activités et villes uniques, en adaptant les colonnes de recherches.

• En cellule J5, adapter le calcul comme suit :

=SIERREUR(RECHERCHEV(LIGNE(J1); bd_sorties!E:F; 2; FAUX); '')

• Puis tirer la poignée sur une trentaine de cellules vers le vas pour répliquer la formule,
• En cellule K5, adapter le calcul comme suit :

=SIERREUR(RECHERCHEV(LIGNE(K1); bd_sorties!G:H; 2; FAUX); '')

• Puis tirer la poignée vers le bas pour reproduire la logique du calcul,

Nous obtenons bien l'énumération des activités et villes sans doublons. Comme ces listes ne sont pas reliées entre elles, l'ordre alphabétique est rompu à chaque changement de département pour les activités et les villes.

Dans la formation suivante, nous améliorerons ces extractions pour ne produire que les activités uniques du département choisi en amont et les villes uniques dépendant du département et de l'activité.



Là encore, vous remarquez l'apparition automatique des bordures d'encadrement, dès lors qu'un contenu est détecté.

Listes déroulantes dynamiques
Pour préparer le travail de la formation à suivre, nous souhaitons créer des listes déroulantes sur la base de ces extractions sans doublons. Mais comme ces plages sont susceptibles de varier en hauteur, nous devons créer ces listes déroulantes sur des plages de cellules dynamiques. Et pour ce faire, nous devons exploiter la fonction Excel Decaler.

=Decaler(cellule_départ ; decalage_ligne ; decalage_colonne ; [hauteur] ; [largeur])

Pour les départements, la cellule de départ est la cellule I5. Aucun décalage en colonne ni en ligne n'est nécessaire. Nous fixerons donc ces deux arguments sur la valeur 0. La hauteur est l'argument important. Il dépend du nombre de valeurs dans la colonne. Nous exploiterons la fonction Nb (nombre de cellules numériques) pour la déterminer. En effet, nous allons préparer le terrain dans les colonnes L, M et N afin d'afficher un numéro de ligne lorsqu'une valeur correspond. Nous les masquerons à l'issue. L'argument de la largeur étant facultatif, nous l'omettrons.

• En cellule L5, taper la formule suivante :

=SI(I5<>''; LIGNE(I5);'')

• Tirer la poignée du calcul vers la droite sur les colonnes M et N,
• Puis tirer la poignée de l'ensemble des colonnes vers le bas,

Nous obtenons ainsi des numéros incrémentés, dont l'énumération se stoppe lorsque la colonne correspondante ne propose plus de valeurs. Le nombre de ces numéros définit la hauteur à prélever pour chaque colonne respective.

C'est ce qu'illustre la capture ci-dessous. Il est temps désormais de construire ces listes déroulantes sur la base de ces données évolutives.

• Sélectionner la cellule B5 de la feuille listes,
• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Outils de données du ruban, cliquer sur le bouton Validation de données,
• Dans la zone Autoriser de l'onglet Options de la boîte de dialogue qui suit, choisir Liste,
• Puis, cliquer dans la zone Source pour la définir,
• Taper la formule suivante : =DECALER($I$5; 0; 0; NB($L:$L)) et valider,

Vous constatez l'apparition d'une liste déroulante en B5, parfaitement bornée au nombre d'éléments contenus dans la colonne des départements. Si nous ajoutions une valeur à la suite de leur énumération, la liste déroulante s'adapterait pour l'inclure. De même, si l'offre des départements était moins généreuse, la liste se restreindrait aux nouvelles bornes.

• De même en C5, créer la liste déroulante dynamique des activités selon la formule suivante :

=DECALER($J$5; 0; 0; NB($M:$M))

• En D5, créer la liste déroulante évolutive pour les villes, selon la formule suivante :

=DECALER($K$5; 0; 0; NB($N:$N))



Nos listes déroulantes sont donc prêtes à s'adapter au contenu des sources de données évolutives et purgées de doublons, lorsqu'elles seront reliées entre elles. Nous avons déjà franchi un pas important puisque nous avons réussi à extraire les données de bases Excel, sans aucune redondance.

• Sélectionner les colonnes L, M et N par leurs étiquettes,
• Réaliser un clic droit sur la sélection,
• Dans le menu contextuel, choisir Masquer,

Les sources nécessaires à la construction des plages dynamiques sont toujours présentes mais ne sont plus visibles. La présentation est plus sobre. De fait, l'application en cours de développement s'en trouve plus ergonomique.

Rendez-vous donc dans le prochain numéro afin de relier ces sources purgées, pour construire ces fameuses listes déroulantes Excel en cascade, uniquement par le calcul.