Récupérer la valeur d’un segment (slicer) dans Excel

Publié le 20 avril 2021
par Sophie Marchand M.Sc., CPA, CGA, MVP
Valeur des segments (slicers)

Récupérer la valeur d’un segment (slicer) dans Excel

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

 

NBJEUCUBE

 

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.

 

RANGMEMBRECUBE

 

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.

 

Fonction SI

 

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.

 

Joindre texte

 

 


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 :

Le CFO masqué - Commentaires formation - Excel – Introduction à Power Pivot et à la modélisation de données
CFO-Masque_Formations-en-ligne_FBLa 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é.

13 réflexions sur “Récupérer la valeur d’un segment (slicer) dans Excel”

  1. Ben Mosbeh Mohamed Slim

    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

  2. Ben Mosbeh Mohamed Slim

    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.

  3. 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?

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

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

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

  7. Stéphane Lorin

    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

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

Laisser un commentaire

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

Scroll to Top