Comment créer des graphiques imbriqués dans Excel sans utiliser Power BI ?

Dans ce tutoriel, je vais vous expliquer comment créer un graphique imbriqué dans un autre graphique en utilisant Excel. Nous y verrons les étapes permettant de créer un sous-graphique à l'intérieur d'un graphique principal en utilisant un tableau croisé dynamique (TCD) et enfin, nous verrons comment filtrer les données affichées en fonction d'un critère spécifique que nous sélectionnerons d’un simple clic. Pour ce tutoriel, nous n’aurons pas à utiliser Power BI.

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. Comment créer un graphique imbriqué dans un autre graphique

Comme je vous l'expliquais dans l’introduction de ce tutoriel, nous allons maintenant découvrir comment faire en sorte de créer un sous-graphique à l'intérieur d’un graphique principal.

Cela nous permettra d'identifier rapidement la part représentée en fonction d'un critère que nous allons définir par rapport aux données globales de notre base de données.

Pour cela, nous allons partir d’une base de données présentée sous la forme d’un tableau de données Excel :

Pour rappel, comme nous l'avons découvert dans un tutoriel précédent, un tableau Excel permet d'enrichir les données d’une base de données, afin tout d'abord d’en modifier l'apparence et la rendre plus agréable à consulter, mais cela va également permettre d'ajouter un certain nombre de fonctionnalités telles que l'aide à la rédaction des formules ou encore (et c'est ce qui va particulièrement nous intéresser ici) le fait que la plage des données qui correspond à ce tableau sera dynamique.

Ainsi, si nous faisons reposer un tableau croisé dynamique sur ce tableau de données, alors la plage va s'adapter automatiquement en fonction des différentes lignes que nous pourrons ajouter à la suite de la base.

2. Créer un tableau croisé dynamique de sélection de données

Il est maintenant temps de créer notre premier tableau croisé dynamique., lequel va reposer directement sur ce tableau de données Excel.

Pour cela, nous sélectionnons l'une des cellules de la base, puis nous allons nous rendre dans le menu « Insertion », afin de cliquer sur « Tableau croisé dynamique ».

Excel nous demande si nous voulons créer le tableau croisé dynamique sur une nouvelle feuille ou alors sur la feuille de calcul sur laquelle nous sommes en train de travailler :

Nous allons choisir d’insérer ce tableau croisé dynamique juste à droite de notre base de données ici par exemple en cellule F8.

Nous pouvons valider la création du tableau croisé dynamique en appuyant sur le bouton [OK].

Maintenant que le TCD est créé, nous allons vouloir faire apparaître le montant des ventes totales en fonction de chaque commercial.

Pour cela il suffit de cocher la case « Commercial » sur le panneau latéral pour qu’Excel l’ajoute donc au niveau de chacune des lignes :

Nous allons également cliquer sur « Montant » pour qu’Excel insère le montant des ventes qui correspond cette fois-ci dans la zone des valeurs.

Maintenant filtrer rapidement les ventes réalisées par un commercial en particulier, nous allons insérer un segment. Pour cela, nous allons nous rendre dans le menu « Analyse du tableau croisé dynamique » du menu ruban et cliquer tout simplement sur « Insérer un segment ».

Sur la boîte de dialogue qui s’affiche, nous choisissons d’insérer un segment en fonction du nom des commerciaux :

Si nous cliquons sur l’un des boutons de cette boîte de segments, Excel va filtrer automatiquement les données de notre tableau croisé dynamique pour ne faire apparaître que les ventes du commercial que nous avons sélectionné :

Et donc, en réalité, le tableau croisé dynamique que nous venons créer ici ne nous servira qu'à pouvoir utiliser ce filtre afin de sélectionner très rapidement l’un des commerciaux de l’entreprise.

3. Comment n'afficher que les ventes du commercial sélectionné dans les données sources ?

Ensuite, nous allons vouloir faire apparaître le montant des ventes réalisées uniquement par le commercial qui est sélectionné dans le segment dans une nouvelle colonne du tableau que nous appelons « Montant sélectionné » :

Pour cela, il suffit d'insérer un nouveau titre dans la cellule située juste à droite du tableau pour qu’Excel modifie automatiquement la plage des cellules qui composent ce tableau de données.

Pour ne récupérer que les ventes du ou des commerciaux qui ont été sélectionnés dans le tableau croisé dynamique, nous allons tout d'abord devoir regarder si effectivement le nom du commercial que nous retrouvons sur la même ligne se trouve dans la colonne G de la feuille, sur laquelle se trouve le nom des commerciaux du tableau croisé dynamique.

Pour cela, nous allons pouvoir utiliser la fonction Excel EQUIV(), qui permet de récupérer la position d'une valeur que nous saisissons en tant que paramètre au sein d'une plage de cellules.

Pour cela, donc nous allons commencer par récupérer le nom du commercial en cliquant tout simplement sur celui-ci au sein de la ligne sélectionnée pour qu’Excel récupère automatiquement la cellule qui correspond en fonction du nom de la colonne.

En effet, étant donné que nous travaillons ici sur un tableau structuré, Excel interprète les coordonnées des cellules par les noms de colonnes, ce qui est bien plus pratique !

Ensuite, nous passons au deuxième paramètre qui permet de spécifier la plage des cellules dans laquelle nous allons vouloir effectuer cette recherche.

Ici, nous allons faire au plus simple en sélectionnant directement toute la colonne G.

Pour finir, nous allons vouloir réaliser une recherche exacte afin qu’Excel identifie exactement le nom que nous avons passé en paramètre :

 =EQUIV([@Commercial];G:G;0) 

Nous pouvons maintenant constater qu’Excel va nous retourner soit des chiffres « 9 » lorsque le nom du commercial correspond à celui qui est sélectionné, soit des erreurs #N/A pour tous les commerciaux.

Ainsi, nous allons pouvoir effectuer un test pour vérifier si effectivement ce résultat est une erreur :

• si c'est le cas, nous n’afficherons rien du tout dans la cellule,
• en revanche dans le cas contraire nous pourrons reprendre le montant qui est situé juste dans la colonne « Montant »

  =SI(ESTERREUR(EQUIV([@Commercial];G:G;0));"";[@Montant]) 

Ici, nous utilisons deux fonctions Excel supplémentaires que nous avons déjà découvertes dans des tutoriels précédents :

• La fonction SI(), qui permet d’effectuer un test afin de retourner une valeur différente en fonction de ce test,
• La fonction ESTERREUR() qui permet quant à elle de tester si un argument retourne une erreur Excel, comme la valeur #N/A que nous avions relevée un peu plus tôt afin de retourner la valeur booléenne VRAI si c’est le cas, ou FAUX dans le cas contraire.

Nous allons maintenant pouvoir masquer les colonnes G et H du tableau croisé dynamique qui ne va pas nous intéresser pour le moment.

4. Personnaliser la boîte des segments

Pour découvrir comment personnaliser l’apparence de la boîte des segments, je vous invite à visionner la vidéo d’accompagnement de ce tutoriel :

5. Combiner les colonnes dans un nouveau TCD

Ensuite, nous allons voir créer un deuxième tableau croisé dynamique dans lequel nous allons faire apparaître à la fois la colonne « Montant » et la colonne « Montant sélectionné », pour chaque produit vendu.

Pour cela, nous répétons les opérations que nous avons vu un peu plus tôt.

Si la colonne « Montant sélectionné » n’apparaît pas dans la liste des champs disponibles, pensez à actualiser le cache du TCD en effectuant un clic droit sur celui-ci afin de sélectionner « Actualiser » :

Ici, lorsque nous sélectionnons l'une des valeurs au sein de notre boîte de segments, cela n'aura aucune répercussion sur notre tableau croisé dynamique. La raison est très simple : c'est que même si nous avons effectivement modifié les valeurs que nous retrouvons dans la colonne « Montant sélectionné », cela n'impactera pas le tableau croisé dynamique tant que nous n'aurons pas demandé la mise à jour de celui-ci.

Ainsi, nous effectuons un clic droit sur le tableau croisé dynamique et nous cliquons sur « Actualiser » :

Cette fois-ci, les données auront bien été modifiées, afin de ne présenter que les ventes réalisées par le commercial sélectionné.

6. Comment actualiser en temps réel les données du TCD ?

Et donc maintenant, nous allons vouloir automatiser cette opération.

Pour cela, nous allons tout simplement mettre en place une mini-macro que nous allons générer de manière automatique. Pour cela, nous allons nous rendre tout en bas à gauche de la fenêtre Excel et cliquer sur le bouton d'enregistrement des macros Excel :

Excel affiche alors une boîte de dialogue dans laquelle nous allons pouvoir paramétrer cette nouvelle macro en lui donnant tout simplement un nom, ici « actualiserTCD » :

Après avoir appuyé sur [OK], toutes les opérations que nous allons mettre en place sur notre feuille de calcul seront enregistrées et traduites automatiquement en lignes de code VBA.

Ici, nous allons uniquement vouloir actualiser le tableau croisé dynamique, comme nous venons de le faire à l’instant, en effectuant un clic droit et en cliquant sur « Actualiser ».

Pour arrêter l'enregistrement de la macro, nous pouvons cliquer à nouveau sur le bouton d’enregistrement des macros.

Ensuite, nous allons vouloir Excel lance la macro « actualiserTCD » lors de chaque changement effectué sur la feuille de calcul. Pour cela, nous allons effectuer un clic droit sur l'onglet de la feuille de calcul et nous allons choisir « Visualiser le code » :

Cela va nous permettre de créer ce que nous appelons une macro événementielle.

Il s’agit d’un type de macros particulier qui va se lancer automatiquement lorsque nous allons effectuer une opération, que ce soit sur une feuille de calcul ou sur le classeur dans son intégralité.

Ici, nous allons aborder rapidement cette notion, mais je vous invite à consulter le tutoriel complet en cliquant ici si vous souhaitez en savoir plus sur les macros événementielles.

Ici, nous allons choisir respectivement sur les listes déroulantes situées au-dessus de la feuille de code les valeurs suivantes :

• « Worksheet » sur la première liste permet de lister tous les évènements de la feuille de calcul
• « Change » sur la deuxième ligne permet d’insérer l’évènement « Worksheet_Change » sur la feuille de code pour exécuter une portion de code chaque fois qu’un changement est effectué sur la feuille de code

Ensuite, nous allons pouvoir appeler la macro « actualiserTCD » à l’intérieur de cet évènement :

Private Sub Worksheet_Change(ByVal Target As Range)
    actualiserTCD
End Sub

Attention, chaque fois qu’il y aura un changement, Excel va appeler la macro « actualiserTCD » via cet événement. Sauf que cette macro va elle aussi effectuer un changement sur la feuille (mais si les données du TCD restent inchangées…), ce qui aura pour effet d’appeler l’évènement « Worksheet_Change » et donc la macro « actualiserTCD »… nous sommes donc ici confrontés à une boucle infinie, ce qui aura pour conséquence de planter Excel.

Pour contourner ce mécanisme, nous allons tout simplement stopper la gestion des événements le temps d’exécuter la macro « actualiserTCD » en utilisant la propriété « Application.EnableEvents » à laquelle nous allons donner la valeur de « False » :

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
        actualiserTCD
    Application.EnableEvents = True
End Sub

Nous n’oublions pas de lui redonner la valeur de « True » après l’exécution de la macro.

Maintenant, si nous revenons dans la feuille de calcul nous allons pouvoir sélectionner un autre commercial et comme vous pouvez le voir, instantanément les valeurs que nous trouvons dans le tableau croisé dynamique seront actualisées en fonction du commercial que nous avons sélectionné.

7. Créer le graphique embarqué sur Excel

À présent il va nous rester à insérer un graphique sur notre feuille de calcul qui va reposer sur les données de ce tableau croisé dynamique. Pour cela nous sélectionnons l'une des cellules de ce tableau croisé dynamique puis nous allons nous rendre dans le menu « Insertion », afin de sélectionner le type de graphique nous voulons mettre en forme :

Une fois que le graphique est inséré, il ne reste plus qu’à le mettre en forme pour qu’il corresponde au résultat que nous souhaitons lui donner :

Pour arriver à ce résultat, vous pouvez consulter la vidéo d’illustration de ce tutoriel :

• Nous allons commencer par masquer les boutons de champs, qui permettent de sélectionner des données du tableau croisé dynamique directement depuis le graphique
• Nous allons également supprimer la légende du graphique
• Puis, nous allons pouvoir appliquer l’un des styles prédéfinis depuis le menu « Création »
• Ensuite, nous allons personnaliser la manière dont les barres sont affichées afin de les afficher l'une par-dessus l'autre. Pour cela, nous sélectionnons l'une de ces barres en effectuant un clic droit pour choisir de mettre en forme une série de données. Nous allons pouvoir choisir une superposition des barres à cent pour cent et une largeur d’intervalle beaucoup plus basse (par exemple à 10%), ce qui va nous permettre d'agrandir la hauteur de chacune de ces barres.
• Il ne reste plus qu’à afficher les étiquettes des barres, et à mettre ces dernières en forme

Cela étant fait, il nous suffira de sélectionner n'importe quel commercial pour obtenir automatiquement la part des ventes de chacun des appareils vendus par le magasin par rapport à la part globale des ventes réalisées sur ces mêmes appareils