Fonctions Excel: Rangmembrecube (Cuberankedmember) et Valeurcube (Cubevalue)

Publié le 02 mars 2015
par Sophie Marchand M.Sc., CPA, CGA, MVP
Excel - Formules Cube

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.

 

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 départ

L’article suivant est basé sur la table de données ci-bas. 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”.

Modèle de données

 

.

Fonction Rangmembrecube (Cuberankedmember)

Pour utiliser la fonction 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”..

Excel - Formules Cube

 

Comme deuxième paramètre, Excel vous demande l’expression du jeu. Vous pouvez choisir une mesure ou une dimension. Ci-bas, “[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.

Excel - Formules Cube

 

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

Excel - Formules Cube

 

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

Excel - Formules Cube

 

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

Excel - Formules Cube

 

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

Excel - Formules Cube

 

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.

Excel - Formules Cube

 

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.

Excel - Formules Cube

 

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

Excel - Formules Cube

Fonction Valeurcube (Cubevalue) 

Par la suite, je désire insérer les ventes correspondantes à chacun des produits. Pour ce faire, je vais utiliser la fonction 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.

Valeurcube

 

Dans l’exemple ci-bas, 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.

Excel - Formules Cube

 

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

 

Conclusion

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


CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

6 réflexions sur “Fonctions Excel: Rangmembrecube (Cuberankedmember) et Valeurcube (Cubevalue)”

  1. Bonjour,

    Avant tout merci pour toutes ces explications fournit car les sites expliquant OLAP pour excel ne sont pas nombreux !

    Je cherche à faire tout comme une “recherchev” mais vers ma base sous powerpivot.
    Je m’explique :
    – j’ai sous powerpivot une table avec 3 colonnes : référence / nom / ventes.
    – j’ai sous excel une sélection de références correspondant aux références dans powerpivot.
    – je souhaiterais récupérer les noms stockés dans powerpivot à côté de mes références sur excel.

    J’ai beau m’essayé avec toute les formules cube, je n’arrive pas à mes fin. Pourriez vous m’aider s’il vous plait ?

    Par avance merci !

  2. Bonjour Sophie,

    J’utilise des formules VALEURCUBE pour présenter en synthèse mes mouvements de CA d’une journée à l’autre. Mon fichier est mis à jour quotidiennement à l’aide du vba à heure constante.
    Les tables construites sous power query se mettent bien à jour mais les formules sur le cube OLAP s’affichent en #N/A. Une idée ? Pour que le calcul s’effectue je suis obligé chaque jour d’activer la cellule et d’en sortir. C’est à ce moment là qu’Excel affiche #CHARGEMENT_ DONNEES
    J’ai essayé des wait, des loop pour attendre Application.CalculationState = xlDone mais rien n’y fait…
    Merci pour tous vos articles et vos astuces

    Fred

    1. Francis Paquet, M.Sc., ing., EEE

      Bonjour Fred,

      Je crois comprendre que VBA fait la mise à jour de la requête Power Query. Avez-vous modifié les propriétés de cette connexion afin d’empêcher le l’Actualisation en arrière-plan? En effet, l’Actualisation en arrière-plan est l’option par défaut. Cela veut dire que le rafraîchissement de la connexion se fait en parallèle des autres traitements. Cela vient perturber complètement l’exécution du VBA. Il faut donc décocher cette option… Menu Données, Connexions, choisir votre requête, aller dans les Propriétés et décocher l’option Activer l’actualisation en arrière-plan.

      Francis

  3. Je suis instituteur et pour faciliter mon travail,j’ai créé des formules me permettant de procéder aux calculs de total,de moyennes,de rang.
    Mon seul bémol est que je ne parviens pas à faire un tri automatique du 1er au dernier.
    Je vous prie de bien vouloir m’aider.
    Nb: je suis un autodidacte en Excel

Laisser un commentaire

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

Scroll to Top