Créer des listes sans doublons avec une seule formule

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 avec une seule formule

Nous avons déjà appris à extraire les références uniques d'une liste de données. Mais pour cela, nous avions exploité des calculs intermédiaires de repérage.



Ici, il est question de relever le même défi mais à l'aide d'une seule formule. Et ce sont bien évidemment les calculs matriciels, capables de raisonner sur l'ensemble des matrices, qui permettent cette prouesse.

Source et présentation de la problématique
Pour réaliser cette étude, nous avons besoin de données redondantes que nous proposons de récupérer.

• Télécharger le classeur purger-doublons-formule-matricielle.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons une base de données des idées de sorties. Elle s'étend entre les colonnes B et F et sur plusieurs centaines de lignes. Pour chaque enregistrement, le détail est apporté sur le département, l'activité et la ville notamment. De nombreuses idées sont situées dans le même département, voire la même ville et proposent le même type d'activité. En conséquence, ces trois champs présentent de nombreuses redondances. C'est la raison de la présence du tableau de synthèse sur la droite, entre les colonnes H et J. Nous souhaitons connaître tous les départements, toutes les activités et villes, purgés de leurs doublons.



De plus, en déployant la zone Nom en haut à gauche de la feuille Excel, vous notez que chaque colonne est intitulée en fonction de son titre. Nous exploiterons ces noms dans la construction des formules matricielles.

Extraire les données uniques par formule matricielle
Les fonctions d'extraction nous les connaissons bien désormais. Il s'agit des fonctions Index et Equiv. Mais nous ne pouvons pas les exploiter classiquement. Sans un raisonnement matriciel, engageant des analyses sur les lignes respectives, elles ne sont pas capables d'extraire les valeurs jugées uniques.

L'idée consiste à chercher chaque valeur à extraire dans sa colonne d'appartenance pour savoir si elle a déjà été rencontrée. Pour cela, nous allons exploiter la fonction de dénombrement Nb.Si, sur la plage du calcul qui progresse en même temps que la formule est répliquée sur les lignes du dessous. Cette condition matricielle devra être testée par la fonction Equiv. En guise de valeur cherchée, nous lui fournirons un indicateur booléen. Le chiffre 0 signifie Faux en effet. Si ce test n'est pas concluant, nous saurons que la donnée n'a pas encore été extraite. Donc, nous connaitrons sa ligne grâce à la nature de la fonction Equiv. Ce test devra être réalisé dans la fonction logique EstNA. Une fonction d'extraction retourne une anomalie lorsque la recherche est infructueuse. L'objectif de cette fonction logique est donc de ne pas saborder le calcul pour permettre l'analyse sur l'intégralité des matrices. A chaque succès, nous extrairons la donnée unique grâce à la fonction Index.

• Sélectionner la première information à extraire, soit la cellule H6,
• Taper le symbole égal (=) pour initier la formule matricielle,
• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,

Nous avons en effet un test logique à réaliser, notamment sur la génération d'erreurs.

• Saisir la fonction de test d'erreur suivie d'une parenthèse, soit : EstNa(,
• Taper la fonction trouvant la ligne d'une information, suivie d'une parenthèse, soit : Equiv(,
• Saisir le chiffre 0 pour indiquer la non correspondance booléenne à établir,
• Taper un point-virgule (;) pour passer dans l'argument de la matrice conditionnelle,
• Inscrire la fonction de dénombrement suivie d'une parenthèse, soit : Nb.Si(,
• Cliquer sur la cellule située au-dessus du calcul, soit H5,
• Ajouter le symbole deux points (:) pour générer la plage H5:H5,
• Cliquer sur la première des références pour y placer le point d'insertion,
• Enfoncer deux fois la touche F4 du clavier, ce qui donne : H$5:H5,

L'intérêt de cette manoeuvre est double. La borne inférieure est laissée totalement libre. Au gré de la réplication du calcul, elle suivra le mouvement pour se déplacer. C'est ainsi que chaque précédente valeur extraite pourra être intégrée pour savoir si elle a déjà été comptée. La borne supérieure est figée en ligne et libérée en colonne. Ainsi, pour une même colonne, toutes les données précédemment extraites seront considérées. Pour les autres, situées sur la droite, nous déplacerons naturellement l'analyse sur les champs associés.

• Cliquer à la fin de la syntaxe pour y replacer le point d'insertion,
• Taper un point-virgule (;) pour passer dans l'argument du critère à dénombrer,

Cette condition doit considérer l'ensemble des informations présentes dans la colonne. C'est ainsi que nous saurons si cette donnée est unique. Pour désigner la colonne des départements, nous suggérons d'exploiter le titre juste au-dessus du calcul. Mais pour qu'il soit interprété comme une plage reconnue par son nom, nous devons employer la fonction Indirect.

• Saisir la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Désigner le titre du tableau de synthèse en cliquant sur sa cellule H5,
• Enfoncer deux fois la touche F4 du clavier, ce qui donne : H$5,

Les raisons sont similaires aux précédentes. Pour l'extraction des départements, le nom de la plage à prélever est indiqué dans cette ligne fixe. Pour les autres données à extraire sur la droite, l'information doit évoluer en se déplaçant avec la formule répliquée. C'est ainsi que nous proposerons une unique formule parfaitement dynamique.

• Fermer la parenthèse de la fonction Indirect,
• Fermer la parenthèse de la fonction Nb.Si,
• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, fermer la parenthèse de la fonction EstNa,

Ainsi, nous sommes de retour dans les arguments de la fonction Si. Si cette condition est vérifiée, cela signifie qu'une erreur est générée. Ce critère consiste à savoir si l'information en cours d'analyse n'a pas déjà été extraite. Dans ce cas, nous devons ignorer la valeur.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Inscrire deux guillemets ('') pour ignorer l'enregistrement en cas d'échec,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,

C'est précisément dans ce cas que l'extraction doit être faite. Nous devons employer la fonction Excel Index, capable d'extraire une donnée située au croisement d'une ligne et d'une colonne. La ligne, nous la connaissons implicitement. Si la fonction Equiv n'échoue pas, c'est qu'elle a isolé la position de l'information unique à retourner. La colonne reste à préciser. Elle dépend de l'entête de colonne du tableau de synthèse.

• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Saisir la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Cliquer sur la cellule de titre du tableau de synthèse, soit la cellule H5,
• Enfoncer deux fois la touche F4 du clavier, ce qui donne : H$5,

De cette manière, nous désignons indirectement la plage des départements uniques à extraire. Nous libérons la colonne et figeons la ligne pour les mêmes raisons que celles évoquées précédemment.

• Fermer la parenthèse de la fonction Indirect,
• Taper un point-virgule (;) pour passer dans l'argument de la ligne de la donnée à extraire,
• Copier et coller strictement à l'identique la précédente recherche de la fonction Equiv :

EQUIV(0; NB.SI(H$5:H5; INDIRECT(H$5)); 0)

Dans la mesure en effet où cette fonction ne génère pas d'erreur, nous savons qu'elle identifie la ligne d'un enregistrement qui n'a pas encore été extrait. Il n'est pas nécessaire de renseigner la colonne. Dans la mesure où nous avons indiqué une unique rangée pour l'extraction par la fonction Index, cette dernière pointera dessus de façon implicite.

• Fermer la parenthèse de la fonction Index,
• Puis, fermer la parenthèse de la fonction Si,
• Enfin, valider nécessairement la formule matricielle par le raccourci CTRL + MAJ + Entrée,

Le premier département considéré comme unique est instantanément extrait.

• Tirer la poignée du résultat sur quelques dizaines de lignes vers le bas,
• Puis, tirer la poignée de la sélection sur les deux colonnes situées à droite,

Grâce à la formule matricielle capable de comparer la valeur extraite avec toutes celles contenues dans la source de données et grâce aux références mixtes, nous avons réussi à purger trois listes de leurs doublons à l'aide d'une seule formule.

La syntaxe de ce calcul matriciel est la suivante :

{=SI(ESTNA(EQUIV(0; NB.SI(H$5:H5; INDIRECT(H$5)); 0)); ''; INDEX(INDIRECT(H$5); EQUIV(0; NB.SI(H$5:H5; INDIRECT(H$5)); 0)))}