Filtrer les valeurs uniques par catégories 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 :


Filtrer les valeurs uniques par catégories

Dans le volet précédent, pour construire des listes déroulantes liées et conditionnelles, nous avons déjà recomposé la ligne d'entêtes des plages devant servir de sources de données aux listes déroulantes dépendantes. Sous chacun de ces entêtes, nous devons maintenant extraire les valeurs uniques et triées correspondant aux catégories représentées par ces entêtes.



C'est ce qu'illustre le rendu final de la capture. A partir d'une liste dans laquelle toutes les informations sont mélangées et souvent répétées, nous obtenons la classification des modèles par marques, sans doublons et triés dans l'ordre croissant. Et pour cela, une seule formule est nécessaire. Nous le verrons, elle exploite des fonctions matricielles et rend une syntaxe très simple.

Classeur Excel à télécharger
Pour construire cette formule de classement, nous proposons d'agir à partir d'un classeur reprenant les travaux du volet précédent.

• Télécharger le classeur classer-trier-par-categories.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ébouchons sur la première feuille de ce classeur. A l'issue du projet, une liste déroulante doit permettre à l'utilisateur de choisir parmi l'une des marques de véhicules, dans n'importe quelle cellule de la colonne C. En fonction de ces choix antécédents, des listes déroulantes doivent proposer uniquement les modèles de ces marques dans toutes les cellules de la colonne D.

• En bas de la fenêtre Excel, cliquer sur l'onglet references pour activer sa feuille,

Nous découvrons le tableau qui doit nourrir ces listes dépendantes. En ligne 2, la formule du volet précédent a permis de recomposer la liste des marques triées dans l'ordre croissant et sans doublons. Cette ligne devra servir de source de données pour toutes les listes déroulantes de la colonne C sur la feuille liaisons. Sous chacun de ces entêtes, nous devons maintenant lister tous les modèles par marque, dans l'ordre croissant et une fois encore bien sûr, sans doublons.

Filtrer les données par catégorie
Comme nous l'avons annoncé, c'est une formule unique qui doit permettre ces extractions organisées par catégorie. Mais pour la bonne compréhension de la syntaxe finale, nous proposons de décomposer sa construction en trois étapes. Il est tout d'abord question de filtrer les modèles issus de la feuille liaisons en fonction de la marque inscrite dans la ligne de titre du tableau de la feuille reference. Et pour réaliser ce type de filtre conditionnel, depuis la version 2019, Excel offre la puissante fonction matricielle nommée Filtre.

• Sur la feuille references, cliquer sur la cellule B3 pour la sélectionner,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction pour filtrer, suivie d'une parenthèse, soit : Filtre(,
• En bas de la fenêtre Excel, cliquer sur l'onglet liaisons pour activer sa feuille,
• Sélectionner alors tous les modèles dans la colonne D, ce qui donne : liaisons!D4:D91,
• Puis, enfoncer la touche F4 du clavier pour la figer, soit : liaisons!$D$4:$D$91,

En effet, nous allons reproduire la formule sur la droite pour filtrer tous les modèles dans chaque catégorie. Le critère devra lui évoluer vers la droite avec la formule répliquée pour considérer les noms des marques. Les modèles quant à eux, doivent toujours être prélevés dans cette colonne D qui ne doit pas bouger. C'est la raison pour laquelle nous la figeons.

• Taper un point-virgule (;) pour passer dans l'argument du critère, nommé inclure,
• Sur la feuille liaisons, désigner cette fois toutes les marques, ce qui donne : liaisons!C4:C91,
• Puis la figer pour les mêmes raisons que précédemment, ce qui donne : liaisons!$C$4:$C$91,
• Taper alors le symbole égal (=) pour annoncer la condition à satisfaire,
• En bas de la fenêtre Excel, cliquer sur l'onglet references pour revenir sur sa feuille,
• Dès lors, désigner la première marque en cliquant sur sa cellule B2,

Rappelons-le, la fonction Filtre est une fonction matricielle. Elle va donc raisonner de façon récursive pour analyser toutes les cellules de la matrice des marques concordant avec celle qui est ainsi mentionnée. Et pour chaque concordance avérée, ce sont tous les modèles de la colonne D qui seront extraits.

• Dès lors, enfoncer deux fois la touche F4 du clavier, ce qui donne : references!B$2,

La ligne est ainsi figée. La colonne reste libre de se déplacer quant à elle. Effectivement, lors de la réplication de la formule vers la droite, ce sont les marques suivantes qui doivent être considérées dans le critère à appliquer sur la matrice figée des marques.

• Fermer la parenthèse de la fonction Filtre,
• Puis, valider la formule avec la touche Entrée du clavier,

Comme vous pouvez le voir, seul un résultat tombe. Mais à ce stade, rien n'est plus normal. Pour l'instant, seul le modèle A6 est référencé pour la marque Audi.

• Cliquer et glisser la poignée du résultat à l'horizontale jusqu'en cellule N3,

La formule matricielle répand naturellement toutes ses extractions conditionnelles en hauteur, en fonction des réponses positives au test. Il s'agit bien des modèles respectifs appartenant aux marques désignées dans la ligne d'entête.



Cependant et cela ne vous a pas échappé, d'une part les modèles ne sont pas triés dans l'ordre croissant et d'autre part, certains d'entre eux sont répétés. En effet, plusieurs mêmes modèles d'une même marque peuvent figurer dans ce parc automobile. Il n'y a rien de plus logique.

Supprimer les doublons des données filtrées
Donc, comme nous l'avons fait dans le volet précédent, nous devons dégainer la fonction matricielle Unique. Elle aussi est sortie en 2019. En imbriquant la fonction Filtre dans cette fonction Unique, nous allons éliminer les répétitions des données ainsi extraites conditionnellement.

• Cliquer de nouveau sur la première cellule de l'extraction, soit B3,
• Dans la barre de formule, cliquer 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(,
• Dès lors, cliquer à la fin de la syntaxe, après la parenthèse fermante de la fonction Filtre,
• Fermer la parenthèse de la fonction Unique,
• Valider la formule matricielle avec la touche Entrée du clavier,
• Puis, cliquer et glisser la poignée du résultat à l'horizontale jusqu'en cellule N3,

Cette fois les résultats se lissent et entrent dans les cases qui avaient été prévues à cet effet. Les doublons ont disparu, et les extractions sont toujours filtrées par catégories.

Trier les données uniques filtrées
En revanche, elles sont toujours désorganisées dans la mesure où elles ne sont pas triées dans l'ordre alphabétique croissant. Il n'est pas concevable d'utiliser ces sources pour remplir les contenus des listes dépendantes. L'utilisateur y perdrait en confort et en ergonomie. Là encore et comme dans le volet précédent, nous allons armer la fonction matricielle nommée Trier. En lui imbriquant les deux précédentes fonctions, elle doit restituer les modèles de chaque marque dans l'ordre croissant et sans répétitions.

• Cliquer de nouveau sur la cellule B3 du premier calcul pour la sélectionner,
• Dans la barre de formule, cliquer une fois encore après le symbole égal,
• Inscrire la fonction de tri suivie d'une parenthèse, soit : Trier(,
• Dès lors, cliquer à la fin de la syntaxe pour y placer le point d'insertion,
• Fermer la parenthèse de la fonction Trier et valider avec Entrée,
• Resélectionner la cellule B3,
• Retirer la poignée du résultat à l'horizontale jusqu'en cellule N3,

Cette fois et comme vous pouvez l'apprécier, nous obtenons bien des extractions de valeurs uniques triées dans l'ordre croissant et filtrées en fonction du nom de la marque et ce, avec une seule formule. Nous exploiterons ces sources de données dynamiques et conditionnelles pour remplir toutes les listes déroulantes des modèles de véhicules dépendant de la marque choisie par l'utilisateur.

La syntaxe complète de la formule matricielle d'extraction conditionnelle que nous avons construite, est la suivante :

=TRIER(UNIQUE(FILTRE(liaisons!$D$4:$D$91; liaisons!$C$4:$C$91 = references!B$2)))