Filtrer les données dupliquées avec Excel

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


Sujets que vous pourriez aussi aimer :


Compter les données répétées

Nous avons déjà imbriqué les fonctions Unique et Filtre pour notamment créer des listes purgées de leurs doublons. Nous récidivons ici dans une optique légèrement différente. Il est question de dresser des statistiques sur des valeurs répétées.



Sur l'exemple illustré par la capture, nous travaillons sur une base de données des activités de sorties. Certains départements sont mieux renseignés que d'autres. Pour avoir une idée des proportions et répartitions, nous construisons une formule de filtre, sur la droite du tableau, répondant à un critère numérique saisi par l'utilisateur. C'est ainsi et dans ce cas illustré que nous dressons la liste des départements enregistrant plus de cent activités de sorties.

Classeur Excel à télécharger
Nous suggérons d'axer l'étude sur un classeur Excel hébergeant cette base de données.

• Télécharger le classeur compter-donnees-repetees.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons le tableau des activités de sorties. De nombreuses suggestions sont renseignées dans chaque département. C'est la raison pour laquelle, cette information en colonne D, est répétée à de nombreuses reprises. L'idée ici est d'établir une formule unique capable d'extraire les départements riches en activités, selon une contrainte numérique à dépasser, livrée par l'utilisateur en cellule H4. En déployant la zone Nom en haut à gauche de la feuille Excel, vous constatez que la colonne des départements est reconnue sous le nom dep.

Filtrer au-delà
Pour extraire les départements proposant au moins le nombre d'activités désirées, nous devons déployer plusieurs astuces. Nous devons commencer par compter les répétitions grâce à la fonction Nb.Si. Ce score, nous devons l'exploiter dans la fonction Filtre pour retourner les données répondant favorablement.

• Sélectionner la case du premier département à extraire en cliquant sur sa cellule G4,
• Taper le symbole égal (=) pour débuter la construction de la formule matricielle,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Filtre(,
• Désigner la plage des départements à filtrer par son nom, soit : dep,
• Taper un point-virgule (;) pour passer dans l'argument du critère de la fonction Filtre,
• A gauche de la barre de formule, cliquer sur le bouton de l'assistant fonction (fx),

Grâce à lui, nous verrons comment seront repérées les données concordantes, dans ce raisonnement matriciel.

• Dans la boîte de dialogue, cliquer dans la zone intitulée Inclure,
• Inscrire la fonction de dénombrement conditionnel, suivie d'une parenthèse, soit : Nb.Si(,
• Désigner la plage des départements à analyser par son nom, soit : dep,
• Taper un point-virgule (;) pour passer dans l'argument de la donnée à comptabiliser,
• Désigner de nouveau la plage des départements par son nom, soit : dep,

Dans ce raisonnement matriciel, donc récursif, ce sont tous les départements qui vont être analysés tour à tour dans cette même colonne des départements, pour compter leurs nombres respectifs.



Et d'ailleurs, vous remarquez que l'assistant fonction réagit aussitôt en répondant par une matrice de nombres. Chaque nombre indique la quantité de fois que le département repéré sur cette position, intervient dans la colonne. C'est ainsi, en ajoutant la contrainte numérique définie par l'utilisateur, que nous allons pouvoir exclure tous ceux qui ne dépassent pas ce seuil.

• Fermer la parenthèse de la fonction Nb.Si,
• Puis, ajouter le critère suivant : >H4,

Cette fois, c'est une nouvelle matrice qui apparaît, en bas à droite de l'assistant. Elle retranscrit les noms des départements répondant favorablement à la contrainte numérique. Dans ce court visuel, seul le département de la Drôme ressort car il est répété à de nombreuses reprises en début de base de données.

• Cliquer sur le bouton Ok de l'assistant pour valider la formule matricielle,

Comme vous pouvez le voir, tous les départements qui répondent favorablement au critère numérique sont retranscrits dans la colonne G. Ils apparaissent a priori dans le même ordre que dans celui de la base de données source. Mais il n'en est rien. Vous pouvez le constater en faisant défiler les lignes jusqu'à atteindre celles des départements peu fournis comme c'est le cas du Vaucluse par exemple.

Si vous changez la contrainte numérique, les extractions s'adaptent bien entendu.



Eliminer les répétitions
La formule que nous venons de construire est certes un succès mais elle est peu exploitable dans la mesure où elle répète un département concordant autant de fois qu'il apparaît dans la base de données source. Pour corriger le défaut, il suffit d'intégrer le précédent calcul dans la fonction matricielle Unique.

• Sélectionner de nouveau la cellule G4,
• Cliquer dans la barre de formule après le symbole égal (=) pour y placer le point d'insertion,
• Inscrire la fonction pour éliminer les doublons, suivie d'une parenthèse, soit : Unique(,
• Cliquer alors à la fin de la syntaxe, après la dernière parenthèse fermante,
• Fermer la parenthèse de la fonction Unique,
• Puis, valider la formule par la touche Entrée du clavier,

Le résultat est beaucoup plus épuré et donc beaucoup plus clair à interpréter. Par exemple, seuls quatre départements proposent plus de 140 activités de sorties. La syntaxe de la formule que nous avons construite est la suivante : =Unique(FILTRE(dep; NB.SI(dep;dep)>H4)).

Son mécanisme est intéressant en inversant le critère d'inégalité pour repérer rapidement tous les départements dépourvus d'idées de sorties et donc à travailler en priorité.



C'est ainsi que nous pouvons rapidement faire ressortir la liste des départements offrant moins de 20 activités de sorties.