Vous êtes plusieurs à m’avoir posé la question: “Comment récupérer la valeur d’un segment (slicer) dans Excel?” et afficher l’information dans le haut d’un rapport, par exemple. Cet article vise donc à répondre à cette question en vous proposant deux avenues, soit une pour les segments (slicers) liés à des tableaux croisés dynamiques dont les données sources sont dans Power Pivot et une autre pour les segments (slicers) liés à des tableaux croisés dynamiques dont les données sources sont diverses. Pour la méthode permettant de récupérer les segments via Power Pivot, nous devrons utiliser des fonctions cube.
Segments via Power Pivot
Pour utiliser cette technique, les données ayant servi à construire le tableau croisé dynamique doivent être logées dans Power Pivot.
Utilisation de la fonction NBJEUCUBE (CUBESETCOUNT)
Pour récupérer la valeur d’un segment dans Excel via Power Pivot, vous pouvez utiliser les formules cube. Par exemple, ci-dessous, j’ai d’abord utilisé la fonction NBJEUCUBE(Segment_Date_mois) ou CUBESETCOUNT(Slicer_Date_mois) pour connaître le nombre d’items sélectionnés dans mon segment “mois”. Pour connaître le nom exact de votre segment, cliquez sur votre segment avec le bouton droit de votre souris et rendez-vous dans les paramètres (settings) de votre segment. Vous y trouverez son nom. Dans mon exemple, mon segment s’appelle “Segment_Date_mois”.
Voyez comment afficher la valeur de vos segments Power Pivot
🎞️ Abonnez-vous à notre chaîne YouTube
Bon visionnement !
Utilisation de la fonction RANGMEMBRECUBE (CUBERANKMEMBER)
Ensuite, j’ai à nouveau utilisé une fonction cube, soit RANGMEMBRECUBE ou CUBERANKMEMBER en anglais, combinée à une fonction SI (IF) pour obtenir les valeurs sélectionnées dans le segment via Power Pivot. À noter que cette fonction utilise le résultat de la première fonction cube. En gros, on demande à Excel d’afficher le premier mois de la liste si le nombre d’items sélectionnés est plus grand ou égal à 1, d’afficher le deuxième mois de la liste si le nombre d’items sélectionnés est plus grand ou égal à 2, et ainsi de suite. Si vous vous demandez pourquoi le premier mois de la liste est avril, c’est que Power Pivot présente les valeurs des segments mois en ordre alphabétique. Nous avons d’ailleurs vu un truc, dans un billet de blogue précédent, pour trier les mois en ordre chronologique dans un tel segment.
Voyez la fonction RANGMEMBRECUBE en action
🎞️ Abonnez-vous à notre chaîne YouTube
Bon visionnement !
Le point concernant les segments via Power Pivot
Pour utiliser les fonctions cube, cela suppose que vos données sources soient logées dans une source de donnés OLAP ou dans Power Pivot. Vous pouvez consulter un référentiel de Microsoft pour en apprendre davantage sur les fonctions cube.
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. |
|---|
Segments via autres sources de données
Préparation du tableau croisé dynamique
Voici une façon alternative de récupérer les valeurs de vos segments et cela, peu importe où logent vos données sources. Notez que vous devez d’abord créer un tableau croisé dynamique et mettre en étiquettes de lignes le même champ que celui qui se retrouve dans votre segment.
Mise en garde
Les deux méthodes précédentes doivent être utilisées avec beaucoup de précaution. En effet, dans l’exemple des segments via Power Pivot, nous connaissons d’avance le nombre d’éléments dans le segment, soit 12 pour 12 mois. Nous pouvons donc réserver 12 lignes à la récupération des éléments du segment. Toutefois, en général, nous ne connaissons pas d’avance le nombre d’items qui feront partie du segment. Cela signifie donc que nous devrons répliquer nos formules sur de nombreuses lignes pour s’assurer de comprendre tous les items. Si plusieurs usagers sont susceptibles d’accéder à ces pages, je vous suggère alors de mettre de la protection sur ces cellules.
Liste des éléments récupérés du segment (slicer)
Pour créer une liste des items sélectionnés par les segments, je vous propose d’utiliser la fonction JOINDRE.TEXTE, tel qu’illustré ci-dessous.
Voyez le tout en action dans ce tutoriel
🎞️ Abonnez-vous à notre chaîne YouTube
Bon visionnement !
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é.
Merci Sophie, ça va être encore une fois très utile!
Merci pour ce post. y a-t-il un moyen de filtrer une “page de filtre” d’un autre tableau croisé dynamique en fonction des mois sélectionnes dans le segment_mois (avec ou sans vba).
Merci encore une fois
Bonjour Ben,
Pourriez-vous être plus précis? Je ne comprends pas bien votre question.
Merci,
Sophie
Merci Sophie pour votre réponse expresse. Dans ce Post vous avez un segment Mois issue d’une table ( disant Ventes) et vous récupérer la valeur des éléments sélectionnées dans ce segment. Supposons que l’on dispose d’une deuxième table (table2), ayant entres autres une colonne mois, indépendante de la table “Ventes”. Est-il possible de créer une pivottable basée sur la deuxième table, ayant comme page de filtre “Mois” et qui soit filtrée en fonction des éléments sélectionnes dans le slicer mois de la première table.
j’espère que je me suis fais comprendre
Merci encore une fois.
Merci beaucoup Sophie, c’été suuuuuuuuuper, je les refais et ça super bien marché.
Bonjour,
Merci beaucoup pour ce tutoriel, mais je rencontre des difficulté à comprendre comment vous parvenez à obtenir “Avr” en cellule E7.
Est-ce que cette donnée est récupérée directement du segment ou bien du tableau croisé dynamique?
Bonjour,
Je viens de voir votre article ci-dessus. Je me posais la question si l’inverse est aussi faisable en Excel?
Si je reprends votre exemple: je voudrais taper manuellement “février” et “avril” dans des cellules et faire en sorte que le slicer (segment) s’adapte automatiquement (ou via VBA) en fonction de ces valeurs.
Merci beaucoup
Bonjour,
Je vous invite à jeter un coup d’oeil à l’article suivant, qui pourra vous inspirer dans ce sens https://www.lecfomasque.com/controler-plusieurs-tableaux-croises-dynamiques-avec-un-seul-menu-deroulant/.
Au plaisir,
Sophie
Bonjour
Petite question: si le segment est basé sur une hierarchie définie dans les relations entre tables dans power Pivot, comment paramétrer la récupération des valeurs selectionnées dans les segements pour chaque niveau de la hierarchie (la hierarchie comporte 2 niveaux, j’ai placé un segment pour chaque niveau)?
Merci d’avance pour votre aide.
julien
Bonjour,
J’ai un peu de mal à saisir votre question. Je vous invite à la poser dans un de nos forums https://www.lecfomasque.com/forums avec un fichier en exemple.
Merci.
Sophie
Bonjour Sophie
Pour contourner la limite de la méthode indiquée dans votre “Mise en Garde”,
Avec Pivot, la liste des éléments sélectionnés peut être obtenue sans limite d’éléments avec
=JOINDRE.TEXTE(“,”;VRAI;RANGMEMBRECUBE(“ThisWorkbookDataModel”;Segment;SEQUENCE(NBJEUCUBE(Segment))))
la fonction SEQUENCE générant la liste de 1 à N éléments sélectionnés dans le segment.
Avec une version Excel sans la fonction SEQUENCE : LIGNE(INDIRECT(“1:”&NBJEUCUBE(Segment)))
Sinon avec une mesure DAX du type Liste_concaténée:=CONCATENATEX(DISTINCT(Tableau1[Colonne1]);Tableau1[Colonne1];”,”)
puis une formule cube =VALEURCUBE(“ThisWorkbookDataModel”;”[Measures].[Liste_concaténée]”;Segment)
Enfin pour les tableaux croisés sans Pivot, l’utilisation d’un TCD sans les en-têtes ni valeur ni mise en forme mais uniquement l’élément choisi permet d’obtenir la liste des éléments sélectionnés sans que l’utilisateur ait l’impression d’avoir un TCD. Si vous mettez ce TCD dans une colonne disponible à droite, colonne vide par ailleurs ! , un JOINDRE.TEXTE(“,”;VRAI;Z:Z) vous donnera la liste des éléments sélectionnés.
Stéphane
Sophie
Je complète mon commentaire précédent par une fonctionnalité qui me semble intéressante à partager.
Soit en D7 de l’exemple la fonction RANGMEMBRECUBE(“ThisWorkbookDataModel”;Segment;SEQUENCE(NBJEUCUBE(Segment))) pour obtenir la liste de l’ensemble des éléments sélectionnés du segment
Comme vous l’avez expliqué dans un précédent article, cette plage peut être récupérée avec le caractère # donc D7#
La question que je me suis posée est : comment récupérer ces infos dans Power Query ?
= Excel.CurrentWorkbook(){[Name=”D7#”]}[Content] ne fonctionne pas
La solution trouvée est de passer par un nom “Liste_filtrée” = Feuil1!$D$7#
et dans Power Query = Excel.CurrentWorkbook(){[Name=”Liste_filtrée”]}[Content]
Je récupère ainsi dans Power Query soit “All” si aucun filtre dans le segment, soit la liste des éléments filtrés.
Stéphane
Bonjour Stéphane,
Merci pour vos excellents commentaires. Comme vous pouvez le constater d’après la date de publication des autres commentaires, cet article a été rédigé en 2015, avant l’apparition des techniques que vous mentionnez. Ça vaudrait vraiment la peine que je mette à jour l’article avec une section supplémentaire qui comprend vos autres trucs et conseils. Je m’engage à le faire sous peu. Merci encore pour votre contribution :).
Au plaisir,
Sophie