Nous avons déjà présenté différentes approches pour créer des listes dépendantes dans Excel par le passé. Nous avons présenté l’approche avec des listes 100% dynamiques dans Excel (mais un peu complexe à mettre en place) et l’approche via Power Query. Dans notre formation Excel – Mise à niveau, nous présentons une approche basée sur les formules traditionnelles d’Excel et dans cet article, nous vous proposons une quatrième approche, basée sur les nouvelles fonctions matricielles dynamiques. L’avantage ici, c’est que l’on construit les listes à partir d’une table Excel dynamique.
Créer des listes dépendantes dans Excel basées sur les fonctions matricielles
Voici donc une approche en 8 étapes pour construire des listes dépendantes dans Excel, via la validation de données et les nouvelles fonctions matricielles dynamiques.
Données de départ
Avant toutes choses, voici la table de données de départ, qui comprend notamment la liste des produits et le département associé à chaque produit.
Résultat recherché
Nous cherchons ici à créer une liste déroulante qui présente en tout temps la liste des départements, en ordre croissant…
… ainsi qu’une seconde liste, qui présente les produits de ce département, par ordre alphabétique également.
Étape 1 : Utiliser la fonction matricielle dynamique UNIQUE
Pour débuter, nous pouvons utiliser la fonction UNIQUE pour créer la liste des départements sans doublons, tel que présenté ci-dessous :
Étape 2 : Utiliser la fonction matricielle dynamique TRIER
Pour présenter la liste en ordre croissant, nous pouvons ensuite simplement imbriquer notre fonction UNIQUE dans une fonction TRIER, tel que présenté ci-dessous :
Étape 3 : Utiliser la fonction matricielle dynamique TRANSPOSE
Finalement, nous pouvons utiliser la fonction TRANSPOSE pour présenter la liste des départements à l’horizontal. Cette plage de données sera utilisée dans notre première liste dépendante dans Excel. On la place à l’horizontal uniquement pour mieux préparer la suite, i.e. la liste des produits par départements.
Étape 4 : Utiliser la fonction matricielle dynamique FILTRE
Pour dresser la liste des produits par département, nous pouvons utiliser la fonction FILTRE et utiliser le département comme paramètre de filtre, tel qu’illustré ci-dessous :
Étape 5 : Utiliser la fonction matricielle dynamique TRIER
Ici, encore une fois, nous pouvons trier la liste résultante en ordre alphabétique grâce à la fonction TRIER, tel qu’illustré ci-dessous :
Étape 6 : Copier la formule pour tous les départements
En copiant la formule pour tous les départements, nous obtiendrons le tableau complet des produits par département et ce tableau sera dynamique. C’est à partir de ce tableau que nous construirons nos liste dépendantes dans Excel.
On aura au final un résultat sous la forme suivante :
Étape 7 : Construire la première des 2 listes dépendantes dans Excel
Pour construire la liste des départements uniques, nous utiliserons simplement la validation de données avec en référence la cellule dans laquelle se trouve notre premier département. Comme on ne veut pas uniquement la valeur de cette cellule mais bien la liste complète qui débute dans cette cellule, nous ajouterons le # à la fin.
Étape 8 : Construire la 2ième des 2 listes dépendantes dans Excel
Pour la deuxième liste de validation, on pourra utiliser une fonction RECHERCHEX, pour s’arrêter dans la bonne “colonne”, soit celle présentant le département sélectionner par l’usager. Encore une fois, on utilisera le # à la fin pour présenter la liste complète.
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
