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

Lorsque votre objectif est de comparer des séries de données à travers plusieurs feuilles de calcul ayant la même structure, comme les ventes par région sur quatre mois différents situés dans des onglets distincts, créer un histogramme consolidé avec plusieurs séries de données offre une comparaison visuelle efficace.

Cette méthode nécessite une sélection manuelle mais est simple et ne nécessite pas de formules complexes ou de macros. Elle fonctionne mieux lorsque vos feuilles de calcul ont une disposition identique et que vous devez composer un graphique directement à partir des feuilles sources.

Suivez ces étapes pour configurer votre graphique :

1. Cliquez sur Insérer > Insérer un graphique en colonnes (ou Colonne) > Histogramme groupé. Cela ouvre un graphique vide dans la feuille de calcul.

2. Faites un clic droit sur le graphique vierge nouvellement inséré et sélectionnez Sélectionner les données dans le menu contextuel.

3. Dans la boîte de dialogue Sélectionner la source de données, cliquez sur le bouton Ajouter pour commencer à ajouter une nouvelle série de données.

4. Dans la boîte de dialogue Modifier la série, entrez le nom de la série et spécifiez les valeurs de la série en naviguant vers la feuille de calcul concernée et en sélectionnant la plage de données requise. Vérifiez bien que les références sont exactes, car les erreurs de référencement peuvent entraîner l'affichage de données incorrectes ou des erreurs comme #REF. Cliquez sur OK pour confirmer.

Astuce : Pour référencer des données provenant d'une autre feuille de calcul dans la zone des valeurs de la série, basculez vers la feuille cible, puis sélectionnez la plage souhaitée. Excel inclura automatiquement le nom de la feuille de calcul dans la référence.

5. Répétez les étapes 3 et 4 pour chaque feuille de calcul que vous souhaitez inclure dans le graphique. Après avoir ajouté toutes les séries, vous les verrez listées sous Entrées de légende (Séries) dans la boîte de dialogue.

Remarque : Assurez-vous toujours de choisir la même structure de plage de cellules sur chaque feuille ; des incohérences peuvent rendre le graphique trompeur ou causer des problèmes d'alignement.

6. Pour affiner votre graphique, cliquez sur Modifier sous les Étiquettes de l'axe horizontal (catégorie) dans la fenêtre Sélectionner la source de données. Dans la boîte de dialogue Étiquettes de l'axe, sélectionnez les étiquettes pertinentes pour qu'elles s'alignent correctement avec vos données. Cliquez sur OK une fois terminé.

7. Fermez la boîte de dialogue Sélectionner la source de données en cliquant sur OK. Votre graphique combine maintenant des séries de données provenant de plusieurs feuilles de calcul.

8. (Facultatif) Pour améliorer la clarté visuelle, sélectionnez le graphique, puis allez dans Conception > Ajouter un élément de graphique > Légende, et choisissez une option (comme Légende > Bas) pour afficher des légendes qui identifient chaque série.

Cette approche est idéale pour les jeux de données statiques. Cependant, si la mise en page de vos feuilles de calcul varie, ou si la plage de données diffère entre les feuilles, il faudra d'abord résoudre ces différences. Si vos données sont fréquemment mises à jour ou nécessitent une consolidation automatisée, envisagez les autres solutions ci-dessous pour plus d'efficacité.

Voici l'histogramme groupé résultant, qui compare visuellement les séries de données provenant de quatre feuilles de calcul différentes :

Lorsque vous souhaitez construire un graphique en sélectionnant des points de données individuels provenant de plusieurs feuilles de calcul, plutôt que des séries entières, vous pouvez d'abord rassembler les cellules cibles dans une feuille de synthèse, puis les représenter ensemble. C'est courant lorsque vous voulez comparer une seule métrique, comme la valeur « Total », provenant de plusieurs feuilles départementales.

Kutools for Excel’s Incrément automatique des références de feuille de calcul fournit un moyen efficace d'extraire des valeurs spécifiques de plusieurs feuilles dans une seule feuille de synthèse, et est particulièrement utile pour les données dynamiques où les valeurs sources peuvent changer au fil du temps.

Voici comment rassembler les points de données et créer le graphique :

1. Sur la barre d'onglet Feuille, cliquez sur le bouton Nouveau bouton ou pour créer une nouvelle feuille de travail pour la consolidation.

2. Dans cette nouvelle feuille de calcul, sélectionnez la cellule où vous souhaitez extraire des données provenant d'autres feuilles. Ensuite, accédez à Kutools > Plus (sous le groupe Formule ) > Incrément automatique des références de feuille de calcul.

3. Dans la boîte de dialogue Remplir les références de feuille de calcul, procédez comme suit :

• Sélectionnez Remplir par colonne, puis par ligne dans le menu déroulant Ordre de remplissage. Cela organisera les valeurs renvoyées dans une liste verticale.
• Cochez les feuilles de calcul contenant les cellules que vous souhaitez référencer, en veillant à n'inclure que les onglets sources pertinents.
• Cliquez sur Plage de remplissage pour extraire les valeurs, puis Fermer une fois terminé.
  

Astuce : Vous pouvez effectuer cette étape à plusieurs reprises si vous devez collecter différents types de données, en les organisant en colonnes ou lignes distinctes dans votre feuille de synthèse selon les besoins.

Après ces étapes, vous verrez les données que vous avez sélectionnées à partir de chaque feuille de calcul soigneusement organisées dans votre nouvelle feuille.

4. Mettez en surbrillance les points de données consolidés, puis procédez à la création du graphique comme d'habitude : Insérer > Insérer un graphique en colonnes (ou Colonne) > Histogramme groupé.

Maintenant, vous avez créé un histogramme groupé qui compare visuellement les points de données sélectionnés, chacun provenant d'une feuille de calcul différente.

Conseils :

• Cette méthode fonctionne mieux pour les graphiques à mise à jour dynamique, car les liens peuvent se rafraîchir automatiquement lorsque les données sources changent (à condition d'utiliser des références directes ou des formules).
• Vérifiez les noms des feuilles de calcul sources si vous rencontrez des erreurs #REF!, car les feuilles renommées/supprimées peuvent briser les références.

Démo : créer un graphique à partir de plusieurs feuilles de calcul dans Excel

Pour les utilisateurs recherchant une solution plus automatisée et évolutive - en particulier lorsqu'il s'agit d'un grand nombre de feuilles de calcul ou nécessitant des mises à jour régulières - une macro VBA peut collecter efficacement des données de plusieurs feuilles et générer instantanément un graphique. VBA vous permet de personnaliser le processus de consolidation des données, ce qui le rend très adapté aux utilisateurs avancés ou à toute personne qui met fréquemment à jour ses rapports.

Avantages : Automatisation, grande flexibilité pour les besoins personnalisés, fonctionne bien avec de nombreuses feuilles de calcul.
Inconvénients potentiels : Requiert l'autorisation d'exécuter des macros, et certains utilisateurs peuvent ne pas être familiers avec la syntaxe VBA ou son dépannage.

Vous pouvez utiliser l'exemple suivant pour combiner une cellule spécifique (par exemple, B2) de toutes les feuilles de calcul dans une nouvelle feuille de synthèse, puis créer un histogramme groupé à partir des données combinées :

1. Cliquez sur Outils de développement > Visual Basic pour ouvrir la fenêtre Microsoft Visual Basic pour Applications. Cliquez sur Insérer > Module, puis collez le code ci-dessous dans le module :

Sub CombineDataAndChart()
    Dim ws As Worksheet
    Dim summarySheet As Worksheet
    Dim lastRow As Long
    Dim destRow As Long
    Dim wsCount As Integer
    Dim i As Integer
    Dim rng As Range
    
    On Error Resume Next
    
    ' Create summary sheet or clear previous one
    Application.DisplayAlerts = False
    For Each ws In Worksheets
        If ws.Name = "SummaryChartData" Then
            ws.Delete
            Exit For
        End If
    Next
    Application.DisplayAlerts = True
    
    Set summarySheet = Worksheets.Add
    summarySheet.Name = "SummaryChartData"
    
    destRow = 1
    
    ' Set header
    summarySheet.Cells(destRow, 1).Value = "Sheet"
    summarySheet.Cells(destRow, 2).Value = "Value"
    destRow = destRow + 1
    
    ' Collect data from all sheets (change range as needed)
    For Each ws In Worksheets
        If ws.Name <> "SummaryChartData" Then
            summarySheet.Cells(destRow, 1).Value = ws.Name
            summarySheet.Cells(destRow, 2).Value = ws.Range("B2").Value ' Modify "B2" as needed
            destRow = destRow + 1
        End If
    Next
    
    ' Create chart
    Dim chartObj As ChartObject
    Set chartObj = summarySheet.ChartObjects.Add(Left:=250, Width:=350, Top:=20, Height:=250)
    
    chartObj.Chart.ChartType = xlColumnClustered
    chartObj.Chart.SetSourceData Source:=summarySheet.Range("A1:B" & destRow - 1)
    chartObj.Chart.HasTitle = True
    chartObj.Chart.ChartTitle.Text = "Combined Data from All Sheets"
    
    xTitleId = "KutoolsforExcel"
End Sub

2. Cliquez sur le Bouton Exécuter dans l'éditeur VBA pour exécuter le code. La macro créera automatiquement une feuille de synthèse ("SummaryChartData"), collectera des données (dans cet exemple, la valeur de la cellule B2) de toutes les feuilles de calcul sauf celle de synthèse, et créera un graphique basé sur les données compilées.

Remarque :

• Si vous souhaitez extraire une cellule différente de chaque feuille de calcul, ajustez la référence ws.Range("B2") en conséquence.
• Pour inclure plus de colonnes ou des plages flexibles, vous pouvez développer la logique du code ou parcourir les indices de colonnes.
• En cas de conflits de noms de feuilles de calcul, la macro recréera ou écrasera automatiquement la feuille de synthèse si nécessaire.
• Avant d'exécuter des macros, assurez-vous que vos paramètres Excel permettent l'exécution des macros.

Si vous rencontrez des erreurs, revérifiez les noms des feuilles de calcul, les références de cellules spécifiées et vérifiez qu'aucune feuille n'est protégée ou masquée.

Miroir/liens entre cellules à travers les feuilles de calcul dans Excel

Définir une plage nommée à travers les feuilles de calcul dans Excel

Copier des données depuis/vers plusieurs feuilles de calcul dans Excel ?

Appliquer la mise en forme conditionnelle à travers les feuilles de calcul/classeurs