Extraire les valeurs uniques groupées par calcul matriciel

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


Sujets que vous pourriez aussi aimer :


Extraire les valeurs uniques

Dans une précédente formation, nous avons appris à purger les doublons d'une liste pour extraire et regrouper les valeurs uniques dans une plage de cellules de destination. Et pour cela, nous avions exploité des calculs intermédiaires de repérage. Dans cette formation, nous proposons d'apporter une autre solution. Il s'agit de mettre en oeuvre une formule matricielle. Ce type de calcul est particulier. Comme son nom l'indique, il raisonne sur le tableau de données et non pas sur l'une des cellules de ce dernier.

Source et présentation de la problématique
Pour débuter les travaux, nous proposons tout d'abord de récupérer une source offrant des redondances à purger.

• Télécharger le classeur doublons-calcul-matriciel.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é,

L'unique feuille de ce classeur Excel est nommée bd. Son tableau illustre une extraction d'activités de loisirs référencées par département. Et comme de nombreuses activités sont recensées dans les mêmes départements, la colonne B offre un grand nombre de répétitions. A partir de cette dernière, nous souhaitons extraire les valeurs sans redondances, donc les départements uniques. Une application dédiée consisterait à bâtir une liste déroulante de choix, sur la base de cette extraction purgée de ses répétitions.

Identifier les valeurs uniques
La procédure consistant à n'extraire que les départements n'ayant pas déjà été extraits est relativement simple. Elle consiste à rechercher dans la plage en cours d'extraction, si le département de la colonne B n'a pas déjà été restitué. Il s'agit donc d'exploiter la fonction Excel RechercheV, sur un tableau de recherche qui grandit en même temps que le calcul est répliqué sur les lignes du dessous. Nous allons donc procéder à l'extraction des valeurs uniques. Et pour bien comprendre le fonctionnement final, nous proposons de débuter à partir d'une cellule arbitraire.

• Cliquer sur la cellule F5 pour la sélectionner,
• Taper le symbole égal (=) pour débuter la formule,
• Taper la fonction conditionnelle suivie d'une parenthèse, soit Si(,
• Taper la fonction de test des erreurs suivie d'une parenthèse, soit EstErreur(,
• Saisir la fonction de recherche suivie d'une parenthèse, soit RechercheV(,
• Désigner le premier département à chercher en cliquant sur la cellule B2,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Sélectionner la cellule au-dessus du calcul, soit F4,
• Enfoncer la touche des deux points (:) du clavier,

Cette action a pour effet de créer la plage de cellules F4:F4. Pour l'instant elle désigne une seule et même cellule.

• Cliquer entre le F et le 4 de la première des deux références,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : $F$4:F4,

Ainsi, nous figeons la borne supérieure de la plage de recherche, mais pas la borne inférieure. De fait, le tableau de recherche va progresser en même temps que nous répliquerons le calcul vers le bas. C'est ainsi, au fur et à mesure des extractions, que nous pourrons scruter la plage pour savoir si le département a déjà été extrait.

• Cliquer à la fin de la syntaxe dans la barre de formule pour replacer le point d'insertion,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de retour,
• Saisir le chiffre 1,

En effet, la recherche et l'extraction ont lieu dans la même colonne. D'ailleurs en deuxième argument, nous avons bien défini un tableau constitué d'une seule rangée. Le numéro de colonne de l'information à retourner correspond donc à la première colonne.

• Taper un point-virgule et saisir le texte Faux, soit : ;faux,

Ce dernier paramètre est booléen. Avec cette valeur, nous demandons à la fonction de réaliser une recherche stricte des départements, et non une recherche approximative.

• Fermer la parenthèse de la fonction RechercheV,
• Puis, fermer la parenthèse de la fonction EstErreur,

Souvenez-vous, nous sommes dans le critère de la fonction Si. En comparant la valeur cherchée à celles de la plage en cours d'extraction, nous voulons savoir si elle a déjà été restituée. Si la fonction RechercheV retourne une erreur, cela signifie que le département n'est pas trouvé. Dans ce cas, nous devons l'extraire.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Cliquer sur le département à extraire, soit la cellule B2,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets, soit '',

En effet, dans le cas où aucune erreur n'est générée, cela signifie que le département a été trouvé. Il ne doit donc pas être extrait une nouvelle fois. Nous sommes en train d'éliminer les doublons. Les guillemets servent à encadrer du texte. Si aucune information n'est transmise, la cellule demeure vide.

• Fermer la parenthèse de la fonction Si,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

De cette manière, nous conservons la cellule du résultat active.

Le premier département est fort naturellement extrait. Il n'existait pas dans cette colonne de calcul.

• Tirer la poignée du résultat sur quelques dizaines de lignes vers le bas,

Seuls les départements n'ayant pas déjà été extraits sont effectivement restitués. La formule que nous avons bâtie est la suivante :

=SI(ESTERREUR(RECHERCHEV(B2;$F$4:F4;1;FAUX));B2;'')

Cependant et comme vous le remarquez, le résultat n'est pas pleinement satisfaisant. De nombreuses lignes vierges sont intercalées.

Regrouper les valeurs par calcul matriciel
Ce phénomène s'explique par la nature du calcul construit. Il raisonne ligne à ligne, donc cellule à cellule. La solution pour regrouper les valeurs uniques, consiste à exploiter un calcul matriciel. Une matrice est un tableau. Donc, ce type d'opération raisonne sur l'ensemble des cellules contenues dans la matrice. La syntaxe est particulière et relativement complexe. Cependant, nous allons tâcher de la démystifier et de l'expliquer au mieux. Il s'agit de regrouper les valeurs uniques, précédemment extraites. Donc, nous proposons de bâtir la formule dans la colonne suivante.

Comme il s'agit d'extraire les valeurs situées sur des indices de lignes n'ayant pas déjà été restitués, nous proposons d'exploiter la fonction Excel Index. Sur une plage de cellules désignée en premier paramètre, elle permet d'extraire l'information située au croisement d'une ligne à renseigner en deuxième paramètre, et d'une colonne à renseigner en troisième paramètre. Ce troisième paramètre est facultatif et c'est tant mieux. Notre recherche doit être réalisée dans la colonne d'extraction précédente. Pour le prélèvement de l'information, seul l'indice de ligne importe donc.

• Sélectionner la cellule G5 et taper le symbole égal (=) pour débuter la formule,
• Taper la fonction d'extraction suivie d'une parenthèse, soit Index(,
• Sélectionner la plage de cellules F1:F50,

Nous indiquons ainsi quelle est la colonne de recherche. Volontairement nous la stoppons à la cinquantième ligne mais rien n'empêche d'aller plus loin. C'est la démonstration qui compte ici. En revanche, il est important de débuter la sélection à partir de la première ligne. Nous souhaitons effectivement retourner à la fonction Index, l'indice de ligne de la valeur à extraire dans la colonne.

• Enfoncer la touche F4 du clavier pour figer cette plage, ce qui donne : $F$1:$F$50,

En effet, la formule est destinée à être répercutée sur les lignes du dessous. La plage de recherche quant à elle ne doit pas varier.

• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne à retourner,
• Saisir la fonction retournant la plus petite valeur suivie d'une parenthèse, soit Min(,

Effectivement, des critères sont à vérifier pour connaître le premier indice de ligne, soit le plus petit dont la valeur n'a pas encore été extraite.

• Taper la fonction conditionnelle suivie d'une parenthèse, soit Si(,
• Sélectionner la plage de la précédente extraction, soit : F5:F50,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : $F$5:$F$50,

La raison est identique à la précédente. Bien que le calcul soit destiné à être répliqué sur les lignes du dessous, la plage d'analyse ne doit pas bouger.

• Taper le symbole inférieur suivi du symbole supérieur, suivi de deux guillemets, soit : <>'',

Ainsi, nous posons l'inégalité du premier critère à satisfaire. Rappelez-vous, nous sommes dans un calcul matriciel. Nous raisonnons sur l'ensemble des cellules contenues dans une plage. Et à ce titre, la condition sera scrutée par le calcul matriciel sur toutes les valeurs concernées. Nous ne devons en effet pas restituer les cellules vides. C'est bien l'enjeu de cette formule qui consiste à regrouper les valeurs uniques les unes sous les autres.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir de nouveau la fonction conditionnelle suivie d'une parenthèse, soit : Si(,

En effet, s'il ne s'agit pas d'une cellule vide, nous devons nous assurer qu'elle n'a pas déjà été extraite. Un critère supplémentaire est nécessaire. Il consiste à dénombrer la cellule en cours d'analyse sur la plage où est bâti le calcul d'extraction. La fonction de dénombrement dédiée, est la fonction Nb.Si. Elle compte la présence d'une valeur à passer en deuxième argument sur une plage de cellules à désigner en premier argument.

• Saisir la fonction de dénombrement suivie d'une parenthèse, soit Nb.Si(,
• Cliquer sur la cellule située au-dessus du calcul, soit G4,

Nous désignons ainsi le point de départ de la plage à analyser pour vérifier si le département a déjà été extrait.

• Taper le symbole deux points (:) pour générer la plage G4:G4,
• Cliquer entre le G et le 4 de la première des deux références,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : $G$4:G4,

L'analyse pour le décompte doit effectivement se faire au fur et à mesure que la formule est répliquée sur les lignes du dessous. Ainsi, à chaque fois qu'un département unique est restitué, pour la prochaine extraction, il est considéré dans le décompte.

• 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,
• Sélectionner de nouveau la plage de cellules des départements, soit F5:F50,

Une fois encore, il s'agit d'un calcul matriciel. Nous raisonnons sur la globalité des éléments à analyser.

• Enfoncer la touche F4 pour figer cette plage, ce qui donne : $F$5:$F$50,
• Fermer la parenthèse de la fonction Nb.Si,
• Puis, taper le symbole égal suivi du chiffre 0, soit : =0,

La condition de cette seconde fonction Si est explicite. Si le département n'a pas encore été comptabilisé sur cette plage en cours d'extraction, alors nous devons le restituer. Et pour cela, il s'agit de retourner à la fonction Index, l'indice de ligne de l'élément identifié à prélever. Et pour continuer de raisonner sur des plages, dans le cadre de ce calcul matriciel, nous devons de nouveau spécifier l'ensemble des départements précédemment extraits.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir la fonction retournant l'indice de ligne suivie d'une parenthèse, soit Ligne(,
• Désigner la plage des départements, soit F5:F50,
• Enfoncer la touche F4 du clavier pour la figer,
• Fermer la parenthèse de la fonction Ligne,
• Fermer la parenthèse de la deuxième fonction Si,
• Fermer la parenthèse de la première fonction Si,
• Fermer la parenthèse de la fonction Min,
• Fermer la parenthèse de la fonction Index,

Au total, nous fermons cinq parenthèses consécutivement. Certes, nous n'avons pas renseigné les branches Sinon des fonctions Si. Mais une formule matricielle est particulière. Tel que nous avons posé le raisonnement, elle n'agit que lorsque les conditions sont satisfaites.

• Ensuite, taper le caractère de concaténation suivi de deux guillemets, soit : &'',

En répliquant la formule vers le bas, en l'absence de données à retourner, le chiffre 0 sera inscrit. Il indique que le traitement est nul. Cette concaténation consiste à forcer le retour en cellule textuelle vide. La dernière manipulation est particulièrement importante pour que le calcul matriciel soit traité en tant que tel.

• Valider la formule par le raccourci clavier CTRL + MAJ + Entrée,

Souvenez-vous, la touche Maj est située juste au-dessus de la touche Ctrl et sa désignation anglaise est Shift.

Comme vous le remarquez, le premier département est restitué. Si vous consultez la barre de formule, une spécificité est venue se greffer. Par ce raccourci clavier de validation, nous l'avons formellement déclaré comme un calcul matriciel. De fait, des crochets viennent encadrer la syntaxe :

{=INDEX($F$1:$F$50;MIN(SI($F$5:$F$50<>'';SI(NB.SI($G$4:G4;$F$5:$F$50)=0;LIGNE($F$5:$F$50)))))&''}

• Double cliquer sur la poignée du résultat.

Comme vous pouvez le voir, grâce à cette formule matricielle ignorant les cellules vides, nous avons réussi à regrouper les valeurs uniques de la précédente extraction.