Créer une liste déroulante dynamique liée à des fonctions cube

Publié le 06 mars 2015
par Sophie Marchand M.Sc.
Liste dynamique

Dans l’article Fonctions Excel: Rangmembrecube (Cuberankedmember) et Valeurcube (Cubevalue), nous avons vu comment utiliser les fonctions cube pour créer une liste d’items (membres) dans Excel. Nous avons notamment spécifié qu’il fallait être prudent avec cette méthode, pour ne pas omettre de nouveaux éléments dans la liste. Cet article vous propose une démarche pour créer une liste dynamique liée à des fonctions cube d’Excel.

 

Utiliser les fonctions cube d’Excel

Repartons donc de notre liste d’items initiale, créée à partir de fonctions cube.

Excel - Formules Cube

 

D’abord, nous devons créer une formule qui fournira le nombre d’items dans la liste. La formule suivante pourrait être utilisée. À noter que le 9 est le nombre de lignes qui comprennent la fonction cube qui permet d’aller chercher les items dans le cube. Une formule cube, présentée plus loin dans cet article, pourrait également être utilisée (note: la fonction Countif se traduit par Nb.si en français).

Liste dynamique

 

Définir une plage de données

Par la suite, nous devons définir une plage de données (que j’ai nommée Liste dans l’exemple ci-bas), en utilisant la fonction Offset (ou Decaler en français) et en y imbriquant la formule développée ci-haut.

Liste dynamique

 

Créer une liste dynamique

Finalement, nous pouvons créer une liste via l’option de validation de données.

Liste dynamique

 

Le résultat final est une liste déroulante qui ne comprend aucune ligne vide et qui s’ajuste donc au nombre d’items présents dans la liste créée à partir de fonctions cube et donc au nombre d’items présents dans la portion sous-jacente du cube.

Liste dynamique

Ajouter un contrôle 

Pour s’assurer que la liste d’items comprend toujours tous les membres de la liste d’items du cube, nous pouvons simplement ajouter un contrôle. Nous pouvons d’abord calculer l’écart entre le nombre de données du cube et le nombre de cellules dans la liste. Le nombre de données dans la liste est connu et correspond au nombre de lignes sur lesquels nous avons appliqué notre fonction cube (c’est notre fameux 9). Le nombre de données du cube peut être déterminé par la fonction cube ci-bas (note: Cubeset se traduit par Jeucube en français et Cubesetcount se traduit par Nbjeucube).

Cubeset et Cubesetcount

 

Si l’écart est négatif, nous pouvons faire apparaître un message d’alerte afin que l’utilisateur soit avisé qu’il manque des items dans sa liste et qu’il devrait ainsi copier la fonction cube ayant servi à créer la liste dans Excel sur davantage de lignes. Au risque de me répéter, ce n’est pas une approche infaillible. C’est pour cette raison qu’il est souvent préférable, pour créer une liste d’items d’un cube dans Excel, de le faire à partir d’une liste qui ne sera pas appelée à évoluer. Dans la vrai vie, ce n’est malheureusement pas toujours possible. Aussi, nous pourrions ajouter de la mise en forme conditionnelle pour faire ressortir notre message de contrôle.

 

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 *

Retour en haut