Comment créer un graphique à partir de plusieurs feuilles de calcul dans Excel ?

Dans ce tutoriel, je vais vous montrer comment créer un graphique à partir de plusieurs feuilles de calcul dans Excel.

Imaginons que vous disposiez de quatre tableaux avec la même structure sur quatre feuilles de calcul différentes, et que vous souhaitiez extraire des données d’une colonne de chaque feuille pour créer un graphique.

Nous vous présenterons deux solutions de contournement pour résoudre ce défi. Suivez-nous pas à pas pour découvrir ces méthodes pratiques et efficaces.

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éer un graphique à partir de plusieurs feuilles de calcul

Pour illustrer ce tutoriel, nous allons pouvoir utiliser le classeur Excel, dans lequel nous retrouvons les ventes réalisées par une entreprise au cours des années 2016 à 2022 dans quatre pays.

Chacune de ces ventes est synthétisée dans un tableau situé dans une feuille de calcul différente.

L’objectif sera donc d’insérer un histogramme dans la feuille de synthèse qui permettra de comparer ces ventes entre les différents pays.

Pour commencer, nous allons donc insérer un histogramme.

Pour cela, nous nous rendons dans l'onglet « Insertion » de la barre de menus, puis nous cliquons sur l'option « Histogramme » et sélectionnons « Histogramme groupé ».

Cela insérera un graphique vierge dans notre feuille de calcul.

Ensuite, nous allons insérer des données dans ce graphique vierge.

Pour cela, nous effectuons un clic droit sur celui-ci, afin de sélectionner l'option « Sélectionner des données » dans le menu contextuel.

Une boîte de dialogue va alors s’ouvrir nommée « Sélectionner la source de données ».

Nous cliquons sur le bouton « Ajouter » pour faire apparaître une nouvelle boîte de dialogue intitulée « Modifier la série" :

Dans cette boîte de dialogue, nous spécifions le nom de la série et les valeurs de la série en sélectionnant la plage de cellules correspondantes au montant des CA par année dans la première feuille de calcul (les ventes réalisées en France) :

Ensuite, nous cliquons sur le bouton « OK » pour ajouter cette série de données.

Nous répétons ces étapes pour ajouter d'autres séries de données à partir des autres feuilles de calcul. Finalement, toutes les séries de données ajoutées seront répertoriées dans la boîte « Entrées de légende (série)" :

Pour personnaliser les étiquettes de l'axe horizontal (les années d’étude), nous cliquons sur l'icône « Modifier » dans la section « Étiquettes d'axe horizontal (catégorie) » pour faire apparaître la boîte de dialogue intitulée « Étiquettes d'axe ».

Dans cette boîte de dialogue, nous spécifions les étiquettes d'axe selon nos besoins en sélectionnant la plage de cellules correspondantes sur l’un des tableaux de notre choix, par exemple ici celui de la feuille de calcul « France » :

Ensuite, nous cliquons sur le bouton « OK ».

Lorsque nous validons la boîte de dialogue « Sélectionner la source de données » en appuyant sur le bouton « OK », Excel va bien insérer le graphique demandé, lequel permet de comparer rapidement le montant des ventes réalisées pays par pays pour la période donnée.

Si nous souhaitons ajouter une légende à notre graphique, nous pouvons sélectionner à nouveau le graphique, et cliquer sur l'onglet « Création de graphiques » dans le menu Ruban, puis choisir l'option « Ajouter un élément de graphique » et sélectionner le sous-menu « Légende ». Ensuite, nous choisissons l'option de légende souhaitée dans le sous-menu (par exemple « en bas ») :

Et voilà, notre graphique est maintenant terminé !

En revanche, l’inconvénient principal de cette méthode, c’est que celle-ci n’est pas véritablement dynamique.

Il serait effectivement possible d’ajouter une année à cette étude, mais cela nécessiterait d’être extrêmement vigilent pour ne pas obtenir d’erreur dans l’ajout de ces données.

2. Créer un tableau structuré

Maintenant, nous allons souhaiter simplifier la récupération des données, et pour cela, nous allons mettre en place un tableau de synthèse sur la feuille principale qui ira ensuite sélectionner automatiquement les données sur les différentes feuilles.

Pour commencer, nous allons revenir sur la feuille principale afin de sélectionner la cellule A6.

Nous allons ensuite y saisir le nom de la première cellule du tableau, « Année ».

Ensuite, nous saisissons les noms des quatre pays sur les cellules situées à droite.

Pour alimenter le tableau, nous sélectionnons la première cellule (A7) et nous y insérons la référence « =France!A4 » pour récupérer la première année.

Puis, pour récupérer le montant des ventes sur la cellule juste à droite, nous insérons la référence « =France!D4 » dans laquelle se trouve le montant du chiffre d'affaires réalisé en France en 2019 :

Nous répétons cette opération pour les autres pays en utilisant les références à la cellule D4 des différentes feuilles de calcul :

Maintenant, pour exploiter facilement ces données, nous allons transformer ce petit tableau en tableau structuré Excel.

Comme nous l’avons déjà vu dans plusieurs tutoriels précédents, il s’agit en effet d’un outil très puissant, qui permet de gérer efficacement les données an ajoutant de nombreuses fonctionnalités très utiles.

Cela permettra par exemple de profiter des avantages suivants :

• Excel nous offre des fonctionnalités intégrées pour trier et filtrer les données dans un tableau structuré. Il suffit de cliquer sur les flèches à côté des en-têtes de colonne pour trier les données par ordre croissant ou décroissant, ou pour filtrer les données en fonction de critères spécifiques.
• Lorsque nous utilisons des formules ou des fonctions dans un tableau structuré, Excel étend automatiquement les calculs aux nouvelles lignes ajoutées, ce qui sera très pratique ici pour ajouter automatiquement de nouvelles années dans notre base.
• Les références dans les formules d'un tableau structuré utilisent des noms de colonnes plutôt que des références de cellules spécifiques. Cela facilite la lecture des formules et rend le tableau plus flexible.
• Et surtout, ce qui va nous intéresser ici, c’est que la plage des cellules du tableau va maintenant devenir dynamique. Il s’agira d’étendre celle-ci pour que tous les appels à cette plage soient tiennent de ces nouvelles lignes, ce qui va nous permettre d’actualiser automatiquement notre graphique, comme nous le verrons juste après.

Pour transformer la base en un tableau structuré, nous sélectionnons l’une des cellules de celui-ci, puis nous appuyons sur les touches [Ctrl]+[L].

Excel nous affiche alors une boîte de dialogue qui va nous permettre de sélectionner la plage des cellules du tableau.

Normalement, lors de cette étape de transformation, Excel reconnaît automatiquement les données et les sélectionne à notre place.

Nous vérifions malgré toutes les cellules sélectionnées, et nous activons l’option « Mon tableau comporte des en-têtes ».

Ensuite, nous validons la création du tableau structuré en appuyant sur le bouton « OK ».

Notre tableau est maintenant transformé.

Pour récupérer les années suivantes, il ne reste plus qu’à étendre la plage de celui-ci en utilisant la petite poignée bleue située en bas à droite de la dernière cellule :

3. Créer le graphique à partir des données du tableau de synthèse

Maintenant que nous avons récupéré les données dans notre tableau de synthèse, nous pouvons insérer le nouveau graphique.

Nous sélectionnons les données du tableau, puis nous allons dans l'onglet « Insertion » de la barre de menus et choisissons l'option « Courbe » pour insérer un graphique de type courbe.

Attention, comme nous pouvons le constater, ici Excel utilise la première colonne « Année » en tant que données pour créer une courbe, et non pas comme axe horizontal.

Pour rectifier cela, nous devons sélectionner le graphique, cliquer avec le bouton droit et choisir « Sélectionner les données » dans le menu contextuel.

Dans la boîte de dialogue qui s'ouvre, nous cliquons sur le bouton « Modifier » de la zone « Étiquettes de l’axe horizontal » pour utiliser la première colonne comme l'axe horizontal.

Ensuite, nous supprimons la série « Année » de la zone « Entrées de légende », en la sélectionnant, puis en appuyant sur le bouton « Supprimer » :

Ensuite, nous validons la modification en appuyant sur le bouton « OK ».

Maintenant que notre tableau de synthèse est configuré, il est facile d'ajouter de nouvelles données.

Nous pouvons simplement saisir les données à la suite de chaque base de données (France, Allemagne, Espagne, Italie) dans le tableau.

Lorsque nous agrandissons l'étendue du tableau en étirant la poignée, les nouvelles données seront automatiquement ajoutées sur le graphique.

En revanche, le graphique créé avec la première méthode reste quant à lui identique, sans ces nouvelles données :