Comment sélectionner le nombre d’éléments à afficher dans un graphique avec les segments sur Excel ?

Dans ce tutoriel, nous allons découvrir comment utiliser des segments pour sélectionner le nombre d'éléments à afficher dans un graphique Excel.

Pour cela, nous allons revenir sur des notions avancées d’Excel que nous avons eu l’occasion d’utiliser au cours de tutoriels précédents : les tableaux croisés dynamiques, les segments, un peu de VBA…

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Création du tableau de données

Pour illustrer ce tutoriel, nous allons partir du tableau reprenant les ventes réalisées par les commerciaux d’une entreprise au cours de l’année 2021 :

Nous y retrouvons la date de réalisation de chacune de ces ventes, le commercial ayant conclu la vente, et le montant de celle-ci.

Pour commencer, nous allons convertir cette base en tableau de données structuré Excel, ce qui nous permettra ensuite de pouvoir travailler de manière plus efficace avec les données du tableau.

En effet, comme nous l’avons déjà vu dans un tutoriel précédent, l'utilisation de ce type de tableau est un moyen efficace de stocker, gérer et analyser les données de manière structurée.

Pour effectuer la conversion, nous commençons par sélectionner l’une des cellules de la base, nous allons cliquer sur le bouton « Tableau » de l’onglet « Données » du ruban. Pour aller encore plus vite, nous pouvons également utiliser le raccourci clavier [Ctrl] + [L].

Excel affiche alors une boîte de dialogue que nous puissions valider la plage des cellules sélectionnée automatiquement par Excel.

Ensuite, nous validons la création du tableau de données en appuyant sur le bouton « Ok » :

Nous pouvons alors constater que l’apparence de la base de données a été modifiée et optimisée pour que les données soient le plus lisibles possible.

La conversion de notre base de données en tableau Excel nous apporte également de nombreux autres avantages, tels que la possibilité de travailler plus facilement avec les données en utilisant les fonctionnalités avancées d'Excel, notamment en proposant d’utiliser une plage dynamique comme source de données du tableau croisé dynamique.

2. Création du segment

Maintenant, nous allons créer une nouvelle feuille de calcul que nous allons appeler « Top ».

Dans celle-ci, nous allons saisir une mini-base de données afin de permettre à l'utilisateur de choisir le nombre d'éléments à afficher sur le graphique en utilisant un segment.

Pour cela, nous créons la mini-base suivante :

Bien entendu, ici libre à vous de sélectionner les possibilités que vous souhaitez laisser à l’utilisateur quant aux choix du nombre d’éléments affichés dans le graphique.

Comme tout à l’heure, nous allons maintenant convertir cette plage en un tableau de données :

Cela va en effet nous permettre d’insérer un segment pour choisir en un clic le nombre d’éléments à afficher.

L'intérêt d'utiliser un segment est qu'il permet d'afficher uniquement les données qui sont nécessaires pour une analyse particulière, ce qui peut faciliter la compréhension et l'analyse des données. Cela peut également aider à économiser du temps en ne présentant que les données qui sont nécessaires.

Pour créer un segment à partir de notre tableau, nous sélectionnons l’une de ces cellules, puis nous nous rendons dans le menu « Création de tableau » afin de cliquer sur « Insérer un segment » :

Sur la boîte de dialogue qui s’affiche, nous sélectionner la colonne « Top », et nous validons en appuyant sur le bouton « Ok ».

Maintenant, lorsque nous cliquerons sur l’un des boutons, le tableau sera filtré automatiquement :

À présent, il va nous rester à trouver la valeur choisie par l’utilisateur dans la cellule A1.

Étant donné qu’il n’existe pas de fonction directement dédiée à la récupération de la valeur sélectionnée dans une boîte de segments, nous allons devoir ruser un petit peu.

En effet, s’il n’est pas possible de lier les données depuis une bonite de segments, il est en revanche évidemment tout à fait possible de lire les données sur la base source elle-même.

Pour cela, nous allons utiliser une formule qui va nous permettre d’extraire la valeur la plus grande (ou la plus petite ? ou la somme ? c’est comme vous le souhaitez !) parmi les cellules visibles de la base.

Pour cela, nous serions tout d’abord tentés d’utiliser la fonction MAX() d’Excel de la manière suivante :

 =MAX(top[Top]) 

Malheureusement, la fonction MAX() va nous retourner la plus grande valeur de toutes les cellules de la colonne, peu importe que celles-ci soient affichées ou non !

À la place, nous allons utiliser la fonction AGREGAT() que nous avions découverte dans un autre tutoriel, laquelle permet d’effectuer un calcul (on parle ici « d’agrégation ») sur une plage de données.

Celle-ci attend en entrée plusieurs paramètres qui définissent la manière dont les données doivent être agrégées :

• Le premier paramètre, permet de choisir quel type de fonction nous souhaitons appliquer. Pour récupérer la valeur la plus grande parmi une plage, nous donnons la valeur « 4 ».
• Le second paramètre, est justement celui-ci qui va nous intéresser ici, étant donné qu’il va nous permettre de spécifier que nous souhaitons ne pas tenir des cellules masquées dans le cadre de ce calcul !
• Enfin, le troisième paramètre, « _top[Top] », indique la plage de données à prendre en compte pour le comptage.

 =AGREGAT(4;5;top[Top]) 

Cela étant fait, nous pouvons renommer cette cellule A1, laquelle contient le résultat de la fonction AGREGAT, en « _nombre », en sélectionnant cette cellule, puis en saisissant le nom correspondant dans la zone des noms située en haut à gauche de la feuille de calcul :

Cela nous permettra ensuite de pouvoir appeler cette valeur facilement dans d'autres parties du classeur Excel afin d’obtenir la valeur sélectionnée dans le segment partout, y compris dans les macros commandes VBA.

3. Synthèse des données

Notre segment étant maintenant créé, nous allons pouvoir synthétiser les données de la base en utilisant un tableau croisé dynamique.

Pour cela, nous sélectionnons une cellule de la base des données, puis nous nous rendons dans le menu Insertion > Tableau croisé dynamique.

Ici, nous passons rapidement sur la notion et l’utilisation des tableaux croisés dynamiques dans Excel, mais si vous souhaitez en savoir davantage, n’hésitez pas à aller consulter ma formation dédiée à cet outil extrêmement puissant d’Excel en cliquant ici.

Une fois le TCD créé sur une nouvelle feuille de calcul, nous pouvons représenter le montant des ventes totales réalisées par chaque commercial en sélectionnant les colonnes « Commercial » et « Ventes » depuis le panneau latéral :

4. Actualisation du nombre de lignes du TCD

Une fois le TCD inséré, nous allons pouvoir effectuer deux traitements sur ce TCD.

Tout d’abord, nous allons trier les données en fonction du montant des ventes réalisées par chaque commercial.

Pour cela, nous déployons le bouton des filtres « Étiquettes de lignes » afin de sélectionner « Options de tri supplémentaires » :

Dans la boîte de dialogue qui s’affiche, nous pouvons choisir de trier les données en suivant un ordre décroissant, à partir de la colonne « Somme de Ventes » :

Puis, nous allons ensuite souhaiter filtrer le nombre de données présentées dans le tableau, en fonction de la valeur de la cellule « nombre ».

Pour cela, nous allons commencer par renommer la feuille de calcul dans laquelle est inséré le tableau croisé dynamique en « tcd » :

Pour renommer une feuille, il suffit de double-cliquer sur l’onglet correspondant.

Nous allons également renommer le TCD, en sélectionnant l’une des cellules qui le composent puis en nous rendant dans le menu « Analyse du tableau croisé dynamique ». Nous le nommons également « tcd » :

Ensuite, nous allons devoir lancer l’éditeur de code VBE (Visual Basic Editor), en appuyant sur les touches [Alt]+[F11].

Une fois VBE lancé, nous allons insérer un nouveau module, à partir du menu Insertion.

Un module est feuille de saisie de code dans laquelle nous pouvons venir saisir des lignes de code en VBA.

Cela étant fait, nous allons insérer la macro VBA suivante directement dans ce module :

Sub nombreCommerciaux()
     Sheets("tcd").PivotTables("tcd").PivotFields("Commercial").ClearAllFilters
     Sheets("tcd").PivotTables("tcd").PivotFields("Commercial").PivotFilters.Add2  _
        Type:=xlTopCount,  DataField:=Sheets("tcd").PivotTables("tcd").PivotFields( _
        "Somme de Ventes"), Value1:=[nombre]
End Sub

Si vous êtes curieux et que vous souhaitez comprendre le fonctionnement de cette macro VBA, vous pouvez consulter la vidéo d’illustration de ce tutoriel dans laquelle nous revenons sur chacune de ces lignes.

Nous y abordons également une méthode de création de la macro différente et plus intuitive que ce que nous venons de voir ici.

5. Modification automatique du TCD

La macro que nous venons de créer dans la partie précédente est bien fonctionnelle, mais pour pouvoir l’utiliser, nous devrons la lancer manuellement, en affichant la boîte de dialogue « Lancement de macro » en appuyant sur les touches [Alt]+[F8].

Pour automatiser son exécution, nous allons devoir créer une macro évènementielle.

La particularité de ce type de macro, c’est que celle-ci s’exécute automatiquement lorsque quelque chose se passe sur une feuille ou un classeur.

Appeler la macro « nombreCommerciaux » que nous venons de créer dès qu’une cellule est modifiée sur la feuille « segment », nous revenons dans VBE et nous double cliquons sur le nom de celle-ci dans le navigateur de projet, afin d’y coller la macro suivante :

Private Sub Worksheet_Calculate()
    nombreCommerciaux
End Sub

Maintenant, nous pouvons sélectionner un bouton sur la boîte de segments pour acualiser automatiquement le nombre de lignes du TCD.

Il ne reste plus qu’à générer un graphique à partir de celui-ci en nous rendant dans le menu Insertion à afin de sélectionner le type de graphique à insérer dans le groupe « Graphiques » :

Puis, nous sélectionnons le graphique afin de le couper en appuyant sur [Ctrl]+[X].

Cela va nous permettre de le coller sur la feuille principale :

Nous faisons de même avec la boîte de segments, ce qui permet de modifier rapidement le graphique juxtaposé :