Fonctions Excel : RANGMEMBRECUBE (CUBERANKEDMEMBER) et VALEURCUBE (CUBEVALUE)

Lorsque vous travaillez avec des données qui logent dans un cube OLAP ou dans un modèle de données Power Pivot (considéré comme un cube OLAP par Excel), vous pouvez aisément utiliser les tableaux croisés dynamiques. Toutefois, vous observerez que certaines options de base des tableaux croisés dynamiques ne sont alors pas disponibles, comme par exemple les champs et les items calculés. Ils sont alors remplacées par d’autres options comme le langage MDX ou le langage DAX. Parrallèlement, il est possible que vous souhaitiez travailler avec les données de votre cube ou de votre modèle de données, directement dans Excel, sans passer par l’entremise d’un tableau croisé dynamique. Dans ce cas, les fonctions cube vous seront très utiles, notamment la fonction Excel VALEURCUBE et la fonction EXCEL RANGMEMBRECUBE.

Rappel concernant les fonctions cubes dans Excel

Dans un article précédent, nous avons vu comment transformer vos tableaux croisés dynamiques en formules cube. Pour relire cet article, rendez-vous sur la page Découvrez les outils OLAP disponibles pour vos tableaux croisés dynamiques. Dans un autre article, nous avons déjà exploré une façon d’utiliser la fonction Rangmembrecube (Cuberankedmember) ainsi que la fonction Nbjeucube (Cubesetcount) afin de récupérer les valeurs sélectionnées dans un segment (slicer). Vous pouvez retrouver cet article à la page suivante Récupérer la valeur d’un segment (slicer) dans Excel.

Table de données de départ

L’article suivant est basé sur la table de données ci-dessous. Celle-ci a été ajoutée au modèle de données dans Excel et loge donc dans Power Pivot. Elle porte le nom de “Ventes”.

Vous avez des sources de données volumineuses à rassembler et analyser dans Excel ? Apprenez à automatiser tout le processus d’importation, de transformation et de modélisation  des données avec nos formations sur Power Query et Power Pivot dans Excel.

Fonction Excel RANGMEMBRECUBE (CUBERANKEDMEMBER)

Pour utiliser la fonction Excel RANGMEMBRECUBE (CUBERANKEDMEMBER), vous devez d’abord sélectionner la connexion, en guise de premier paramètre. Dès que vous ouvrirez les guillemets, Excel vous proposera les choix disponibles. Dans l’exemple ci-bas, mes données logent dans un modèle de données Power Pivot. Je dois donc choisir “ThisWorkbookDataModel”..

Comme deuxième paramètre, Excel vous demande l’expression du jeu. Vous pouvez choisir une mesure ou une dimension. Ci-dessous, “[Ventes]” représente l’unique table de données de mon modèle de données. Cette table comprend une colonne avec la dimension produits et trois colonnes de mesures: ventes, coûts et marges par produit. Ici, je choisis ma table de ventes car mon objectif est de présenter une liste des produits de mon modèle dans Excel.

Du moment que vous insérez un point, Excel continue de vous faire des propositions. Ici, je choisis “Produits”.

Comme je désire voir la liste de tous les produits, je vais choisir “All” ou “Tous” en français.

À cette étape, Excel vous propose de choisir l’un ou l’autre des produits disponibles.

Mais comme je désire obtenir la liste entière, je ne vais pas sélectionner de produit. Je vais plutôt insérer le suffixe “Children”.

Ensuite, je dois simplement préciser un rang. Dans mon exemple, je souhaite copier ma formule dans Excel afin d’obtenir la liste complète des produits. Je dois donc choisir un rang qui sera dynamique et qui me fournira dans l’ordre : 1, 2, 3 et 4. Je vais donc utiliser la fonction Ligne ou Row en anglais et choisir la cellule A1. En copiant ma formule vers le bas, j’obtiendrai donc un incrément de 1 à chaque ligne.

Comme je ne suis pas supposée connaître à l’avance le nombre de produits qui composeront ma liste et comme cette liste est susceptible de croître au fil du temps, je vais imbriquer ma fonction à l’intérieur d’une fonction Sierreur ou Iferror. De cette façon, ma liste ne présentera que les produits du modèle de données et ensuite, des cellules vides.

Vous voyez le résultat dans les cellules C16 à C24 ci-bas.

Fonction Excel VALEURCUBE (CUBEVALUE) 

Par la suite, je désire insérer les ventes correspondantes à chacun des produits. Pour ce faire, je vais utiliser la fonction Excel VALEURCUBE (CUBEVALUE). Pour utiliser cette fonction, je dois également choisir une connexion et par la suite, je dois simplement spécifier les membres de l’expression désirés. Voici plus précisément les paramètres de cette fonction.

Dans l’exemple ci-dessous, je demande à la fonction cube d’aller dans mon modèle de données et de récupérer la somme des ventes pour la dimension de produits spécifié dans la cellule C16 (Produit A, qui deviendra Produit B et ainsi de suite en copiant la formule). De plus, j’ai imbriqué cette fonction à l’intérieur d’une fonction Si (If) afin de ne pas afficher de valeur pour les lignes où il n’y a pas de produit.

Ce faisant, j’obtiens le résultat illustré dans les cellules D16 à D24.

Conclusion sur la fonction Excel VALEURCUBE et la fonction Excel RANGMEMBRECUBE

Bien sûr, cet exemple est minimaliste. Ces deux fonctions cube seront utiles dans un contexte où le cube OLAP ou le modèle de données contiendra davantage de données et de dimensions et où l’on devra récupérer la liste des éléments d’une dimension dans Excel et possiblement, les valeurs correspondantes. L’utilisation du Sierreur (Iferror) permet d’insérer la fonction sur une grande quantité de lignes afin de s’assurer d’obtenir la liste complète. Toutefois, cette méthode n’est pas infaillible. Si la liste grandit au fil du temps, vous pourriez échapper des données. Nous traiterons donc d’une façon de contrôler davantage cet état de fait dans un prochain article.

Fichier d’accompagnement VIP à télécharger

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

Formation complémentaire

Afin d’approfondir vos connaissances avec Power Pivot, nous vous recommandons notre formation Excel – Introduction à Power Pivot et aux modèles de données.

Voici quelques commentaires d’apprenants ayant suivi cette formation :

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