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.
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).
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.
Créer une liste dynamique
Finalement, nous pouvons créer une liste via l’option de validation de données.
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.
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).
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.
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é.
