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 ».
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 »..
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 objecti 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 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.
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.
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.
NOTRE OFFRE DE FORMATIONS
Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.
Consulter la liste des formations offertes
Pour info: 514-605-7112 ou [email protected]
Jeu de briques
Snake
Pacman
Bubble