Comment obtenir le détail d’une valeur d’un tableau croisé dynamique en 1 clic sur Excel

Dans ce tutoriel, je vais vous montrer comment modifier le comportement par défaut d'un tableau croisé dynamique, afin qu’Excel puisse nous afficher directement le détail qui correspond à une valeur d'un tableau croisé dynamique dans la base de données sources, sans avoir à créer une nouvelle feuille.

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. Obtenir le détail d’une valeur d’un tableau croisé dynamique

Ce tutoriel est une réponse à la question posée par Ahmed B., qui a suivi la formation « Prenez le contrôle des données avec les tableaux croisés dynamiques ».

Si vous aussi vous souhaitez que je crée un tutoriel pour répondre à vos questions concernant les tableaux croisés dynamiques, n'hésitez pas à commander votre formation avant vendredi minuit, je me ferai un plaisir d'y répondre au travers d’un tutoriel et d’une vidéo.

La question posée par Ahmed B. est la suivante : « Comment puis-je afficher directement le détail des valeurs correspondantes à une donnée affichée dans le tableau croisé dynamique en cliquant sur celle-ci . ».

Pour répondre à cette question, nous allons utiliser une base de données qui reprend le montant des ventes réalisées par des commerciaux d’une entreprise dans différents pays :

À partir de cette base de données, nous retrouvons un tableau croisé dynamique qui résume le montant de ces ventes, regroupées par commercial, donc sans avoir la distinction par pays :

Ensuite, il faut savoir que l'une des fonctionnalités du tableau croisé dynamique nous permet d'obtenir rapidement le détail d’une ligne en double cliquant sur celle-ci.

Par exemple, si nous voulons connaître le montant de la composition des ventes réalisées par Alain, il suffit de double cliquer sur le montant du chiffre d'affaires pour qu’Excel génère une nouvelle feuille de calcul afin de nous présenter un tableau avec le détail des ventes correspondantes :

Ici, l'objectif est d'aller un peu plus loin que cette fonctionnalité.

Nous ne souhaitons pas qu’Excel crée cette nouvelle feuille de calcul, mais qu’il nous présente les informations directement dans la base de données d'origine.

2. Création d’un filtre personnalisé

Nous allons commencer par mettre en place un filtre automatique sur la base de données.

Pour cela, nous allons évidemment de créer une petite macro en VBA, mais pour que cela soit le plus simple possible, nous allons automatiser son développement.

Nous allons commencer par insérer un bouton directement sur la feuille de calcul dans laquelle se trouve le tableau croisé dynamique en insérant une zone de texte directement depuis le menu Insertion.

Si nous le souhaitons, nous pouvons mettre en forme très rapidement cette zone de texte et saisir le texte « Afficher le détail » :

Ensuite, nous effectuons un clic droit sur ce bouton afin de cliquer sur « Affecter une macro ».

Sur la boite de dialogue qui s’affiche, nous allons nous donner un nom à la macro « afficherDetail » et ensuite nous allons cliquer sur le bouton « Enregistrer » :

Cela va permettre de lancer l'enregistreur de macros, lequel va reprendre le nom de la macro que nous avons défini juste avant.

Lorsque nous allons valider le lancement de l'enregistrement en appuyant sur [Ok], toutes les opérations que nous allons mettre en place au sein d'Excel vont être enregistrées directement en tant que macro-commandes en VBA.

Nous allons effectuer deux opérations :

• Afficher la feuille dans laquelle se trouve la base,
• Filtrer celle-ci pour ne faire apparaître que les ventes d’Alain

Une fois ces opérations effectuées, nous allons pouvoir arrêter l'enregistrement la macro en appuyant sur le carré que nous retrouvons en bas à gauche :

Nous allons maintenant pouvoir tester la macro pour cela nous réaffichons tous les commerciaux et ensuite, nous revenons sur l’onglet du TCD afin de cliquer sur le bouton que nous venons de créer.

Comme vous pouvez le constater, Excel va maintenant nous afficher la feuille sur laquelle se trouve la base de données et va filtrer les données afin de nous afficher uniquement les ventes d’Alain.

3. Personnalisation du filtre

Cela étant fait, nous allons maintenant pouvoir aller consulter la macro mise en place par Excel automatiquement.

Pour cela, nous allons lancer VBE (pour Visual Basic Editor) l'outil de développement des macro-commandes en utilisant le raccourci clavier [Alt]+[F11].

Nous arrivons ainsi sur le projet sur lequel nous sommes en train de travailler. Pour retrouver la macro nous allons double cliquer sur le répertoire « modules », puis sur le « module1 » :

Dans celui-ci, nous allons retrouver la macro afficherDetail que nous venons de mettre en place avec les deux commandes vues un peu plus tôt.

Nous y retrouvons notamment le filtre mis en place avec l’instruction VBA « AutoFilter », laquelle applique le filtre sur la première colonne dans laquelle se trouve le nom des commerciaux.

Maintenant, nous allons très légèrement modifier la macro commande que nous venons de mettre en place, afin de pouvoir sélectionner le nom du commercial de manière dynamique afin de pouvoir appliquer un filtre automatique.

Pour cela, l’utilisateur devra tout d’abord sélectionner la valeur à détailler, puis il pourra ensuite cliquer sur le bouton.

Nous pourrons ainsi récupérer le nom du commercial sur la cellule située juste à gauche.

Pour cela, nous allons stocker le nom du commercial dans une variable que nous pourrons ensuite réutiliser pour effectuer le filtre.

En VBA, pour créer une variable nous utilisons le mot-clé Dim, suivi du nom que nous voulons donner à la variable : « nom » :

Dim nom As String 

Ensuite, nous pouvons la typer en tant que String pour pouvoir y stocker du texte.

Une fois que notre variable est créée, nous allons pouvoir lui affecter le nom du commercial.

Ce nom étant stocké sur la cellule située à gauche de la cellule active, nous allons le récupérer avec l’instruction Offset qui permet d'effectuer un décalage en nombre de lignes et de colonnes à partir d'une cellule de départ, un peu comme la fonction Excel DECALER().

Ici, nous souhaitons rester sur la même ligne (0) et nous placer une cellule vers la gauche (-1) :

    Dim nom As String
    nom = ActiveCell.Offset(0, -1)

Une fois le nom du commercial enregistré dans la variable nom, nous allons pouvoir l’utiliser à la place de la chaîne de filtre.

4. Obtenir le détail d’un double clic

Maintenant que notre macro et bien fonctionnelle, nous allons souhaiter lancer celle-ci en double cliquant sur le montant pour lequel nous souhaitons obtenir le détail :

Pour cela, nous allons pouvoir créer un événement, qui permet comme nous l’avons déjà vu précédemment d’effectuer une action automatiquement lorsque quelque chose se passe sur le classeur ou la feuille de calcul.

Ici, nous allons souhaiter appeler la macro afficherDetail lors d’un double clic.

Pour cela, nous revenons dans notre projet et pour créer un événement il suffit de se placer sur la feuille de code de la feuille concernée (ici la feuille « TCD ») afin d’utiliser les deux listes déroulantes que nous retrouvons au-dessus.

Dans la première liste, nous choisissons Worksheet afin d'obtenir la liste de tous les événements de la feuille de calcul :

Et dans la seconde, nous choisissons l’évènement BeforeDoubleClic. :

Excel l’ajoute alors automatiquement l’évènement Worksheet_BeforeDoubleClick sur la feuille de code :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target  As Range, Cancel As Boolean)
 
End Sub

À l'intérieur de cet événement, nous allons pouvoir saisir une première ligne va nous permettre d'appeler la macro que nous avons créée juste avant « afficherDetail » Worksheet_BeforeDoubleClick :

    afficherDetail

La deuxième commande que nous allons mettre en place va nous permettre de ne pas générer automatiquement la feuille de calcul avec le détail, laquelle ne nous sera plus utile, étant donné que nous allons obtenir ces informations au niveau de la base source.

Et donc pour empêcher cette création nous allons utiliser l'argument Cancel de l’évènement auquel nous allons tout simplement donner la valeur de False :

    Cancel = False

Et voilà, notre macro est maintenant terminée, nous pouvons nous rendre sur le tableau croisé dynamique et double cliquer sur le montant pour lequel nous souhaitons obtenir le détail pour constater qu’Excel va bien nous afficher le détail des ventes réalisées par le commercial directement sur la feuille source sans avoir besoin de créer une nouvelle feuille !