Créer des listes dépendantes avec les fonctions matricielles dynamiques dans Excel

Publié le 21 septembre 2021
par Sophie Marchand M.Sc., CPA, CGA, MVP
Copier formule Excel

Créer des listes dépendantes avec les fonctions matricielles dynamiques dans Excel

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.

Tableau de données Excel

 

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…

Validation de données

 

… ainsi qu’une seconde liste, qui présente les produits de ce département, par ordre alphabétique également.

Validation de données

 

É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 :

Fonction Excel UNIQUE

 

É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 :

Fonction Excel TRIER

 

 

É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.

Fonction Excel TRANSPOSE

 

É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 :

Fonction Excel FILTRE

 

É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 :

Fonction Excel TRIER

 

É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.

Copier formule Excel

 

On aura au final un résultat sous la forme suivante :

Table Excel

 

É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.

Liste dans Excel

 

É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.

Liste déroulante

 

 


Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

 


CFO-Masque_Formations-en-ligne_FBLa 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é.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top