Accéder au contenu principal

Kutools for Office — Une Suite. Cinq Outils. Accomplissez Plus.

Comment créer un histogramme en colonnes groupées et empilées dans Excel ?

Author Xiaoyang Last modified

Dans l'analyse de données professionnelle, visualiser plusieurs catégories et groupes de données dans un seul graphique peut offrir une comparaison plus claire et une interprétation des données plus efficace. Une exigence courante est d'afficher une combinaison de données empilées et groupées, comme afficher les valeurs de vente par région et par trimestre, regroupées côte à côte. Cet article vous guidera à travers les étapes pour créer un histogramme en colonnes groupées et empilées dans Excel, ce qui vous permet de comparer les valeurs individuelles des catégories sous forme de barres groupées, tandis que chaque groupe est composé de segments empilés. Cette approche permet une analyse comparative puissante entre les groupes et à travers plusieurs variables, comme le montre la capture d'écran ci-dessous.

a screenshot showing a stacked clustered column chart in worksheet

Créer un histogramme en colonnes groupées et empilées dans Excel

Pour créer un histogramme en colonnes groupées et empilées dans Excel, il est important de comprendre d'abord qu'Excel ne prend pas en charge nativement ce type de graphique. Cependant, vous pouvez simuler cet effet en préparant soigneusement vos données et en personnalisant la disposition du graphique.

✅ Ce qu'il faut savoir d'abord :

  • Excel n'offre pas de type "histogramme en colonnes groupées et empilées" intégré. Le résultat est obtenu grâce à des astuces de disposition des données.
  • Vous devez restructurer vos données sources pour simuler des groupements en clusters.
  • Des lignes vides sont ajoutées entre les groupes de catégories pour séparer visuellement chaque cluster.

Passons en revue le processus étape par étape en utilisant des données de ventes types pour des produits sur plusieurs trimestres.

1. Organisez vos données brutes : Dans cet exemple, nous avons les noms des produits dans la colonne A et les données de vente (par exemple, Valeur réelle vs Valeur cible pour T1 et T2) dans les colonnes adjacentes. L'objectif est de regrouper les données de chaque produit côte à côte et d'afficher les valeurs réelles/cibles empilées dans chaque cluster.

2. Restructurez les données : Vous devez copier chaque groupe de données (par exemple, chaque ligne de produit) vers une nouvelle disposition et insérer une ligne vide entre chaque groupe. Cela aide Excel à interpréter chaque groupe comme un cluster distinct dans l'histogramme en colonnes empilées.

a screenshot of inserting blank row after each group of data and header row

3. Créez le graphique : Sélectionnez vos données nouvellement structurées. Ensuite, allez dans Insérer > Histogramme ou Graphique à barres > Colonne empilée.

a screenshot of selecting a Stacked Column under the Insert tab

4. Formater la série : Cliquez avec le bouton droit sur n'importe quelle colonne du graphique, sélectionnez Formater la série de données.

a screenshot of opening the Format Data Series dialog box

5. Réduisez la largeur de l'écart : Dans le volet Formater la série de données, allez dans Options de la série, et définissez Largeur de l'écart = 0% pour compresser visuellement chaque groupe en un cluster empilé.

a screenshot of changing the Gap Width to 0 in the Format Data Series pane

6. Ajustez la légende et la mise en page : Cliquez avec le bouton droit sur la légende > Formater la légende.

a screenshot showing how to open the Format Legend pane in Excel

7. Sélectionnez la position de la légende : Dans le volet Formater la légende, sous les Options de la légende, sélectionnez la position de la légende préférée (Droite, Haut, Gauche ou Bas) pour mieux s'adapter à votre mise en page de graphique et éviter tout chevauchement avec les données.

a screenshot of selecting the position for the legend

✅ Résultat : Vous avez maintenant un histogramme en colonnes groupées et empilées où les données réelles/cibles de chaque produit sont groupées et empilées côte à côte pour une comparaison rapide.

⚠️ Limitation : Cette technique fonctionne bien pour les petits jeux de données. Mais pour les grands ensembles de données ou les données changeant fréquemment, une restructuration manuelle peut être sujette aux erreurs. Consultez les sections suivantes pour des solutions VBA et formules pour automatiser ce processus.

Code VBA – Automatiser la restructuration des données et la génération du graphique

Lorsque vous traitez de grands ensembles de données ou des rapports fréquemment mis à jour, réorganiser manuellement les données pour créer des graphiques en colonnes groupées et empilées peut être fastidieux et sujet à erreurs. L'utilisation de VBA (Visual Basic for Applications) vous permet d'automatiser l'ensemble du processus - de la restructuration du tableau source à l'insertion du graphique - vous faisant gagner du temps et des efforts.

Cette méthode convient le mieux aux utilisateurs familiers avec les macros Excel ou dans les environnements où VBA est autorisé. Elle est particulièrement efficace si la structure de votre graphique reste la même mais que les données sont régulièrement mises à jour. Voici comment l'implémenter :

Étape 1 : Appuyez sur Alt + F11 pour ouvrir l'éditeur VBA. Dans l'éditeur, cliquez sur Insérer > Module.

Étape 2 : Collez le code VBA suivant dans la fenêtre du module :

Sub CreateStackedClusteredChart()
    Dim ws As Worksheet
    Dim rngData As Range
    Dim chartObj As ChartObject
    Dim chartRange As Range
    Dim xTitleId As String

    On Error Resume Next
    Set ws = ActiveSheet
    xTitleId = "KutoolsforExcel"

    ' Prompt user to select original data
    Set rngData = Application.InputBox("Select the original grouped data (including all headers):", xTitleId, Selection.Address, Type:=8)
    If rngData Is Nothing Then Exit Sub

    ' Create new worksheet for reshaped data
    Dim wsChartData As Worksheet
    Set wsChartData = Worksheets.Add
    wsChartData.Name = "ChartData_" & Format(Now(), "hhmmss")

    Dim numRows As Long, numCols As Long, i As Long, j As Long, outRow As Long
    numRows = rngData.Rows.Count
    numCols = rngData.Columns.Count
    outRow = 1

    ' Add headers
    wsChartData.Cells(outRow, 1).Value = "Category"
    For j = 2 To numCols
        wsChartData.Cells(outRow, j).Value = rngData.Cells(1, j).Value
    Next j
    outRow = outRow + 1

    ' Copy data and insert blank rows
    For i = 2 To numRows
        For j = 1 To numCols
            wsChartData.Cells(outRow, j).Value = rngData.Cells(i, j).Value
        Next j
        outRow = outRow + 1
        If i < numRows Then
            wsChartData.Cells(outRow, 1).Value = ""
            outRow = outRow + 1
        End If
    Next i

    ' Define chart data range
    Set chartRange = wsChartData.Range(wsChartData.Cells(1, 1), wsChartData.Cells(outRow - 1, numCols))

    ' Insert chart
    Set chartObj = wsChartData.ChartObjects.Add(Left:=100, Top:=30, Width:=500, Height:=350)
    With chartObj.Chart
        .SetSourceData Source:=chartRange
        .ChartType = xlColumnStacked
        .HasTitle = True
        .ChartTitle.Text = "Stacked Clustered Column Chart"
        .Legend.Position = xlLegendPositionRight
        .ChartGroups(1).GapWidth = 0
    End With

    MsgBox "Chart generated successfully.", vbInformation, "KutoolsforExcel"
End Sub

Étape 3 : Appuyez sur Alt + F8 pour ouvrir la boîte de dialogue Macro. Sélectionnez CreateStackedClusteredChart et cliquez sur Exécuter.

Étape 4 : Lorsque vous êtes invité, sélectionnez votre jeu de données original (avec en-têtes). La macro générera une nouvelle feuille de calcul avec des lignes vides insérées et créera automatiquement l'histogramme en colonnes groupées et empilées.

📝 Conseils :

  • Assurez-vous que votre tableau original a des en-têtes de colonnes et une mise en forme cohérente.
  • Vous pouvez réexécuter la macro à tout moment lorsque votre jeu de données est mis à jour - pas besoin de répéter les étapes manuelles.

✅ Avantages : Gagne du temps, mise en page précise, parfait pour les rapports récurrents.
⚠️ Inconvénients : Requiert Excel avec macros activées et une connaissance de base de VBA.

Formule Excel – Restructurer dynamiquement les données pour les graphiques en colonnes groupées et empilées

Si vous préférez ne pas utiliser VBA ou avez besoin d'une solution qui permet des mises à jour dynamiques du graphique lorsque vos données brutes changent, des formules peuvent être utilisées pour restructurer vos données sources dans le bon format pour les graphiques en colonnes groupées et empilées. En tirant parti des fonctions intégrées telles que INDEX, TRANSPOSE et des colonnes auxiliaires, vous pouvez configurer une zone de transformation de données qui fournit toujours la structure correcte pour votre graphique avec un minimum d'effort.

Cette approche est particulièrement pratique lorsque vos données brutes sont régulièrement mises à jour (nouvelles périodes, catégories, etc.), et que vous souhaitez que votre graphique s'ajuste automatiquement sans restructuration manuelle. L'exigence principale est de construire une section "auxiliaire" qui extrait et organise des blocs de données et des lignes vides à l'aide de formules, afin que la source de votre graphique reste toujours à jour.

Voici un exemple de la façon dont vous pourriez configurer cela :

  • Supposons que vos données originales soient dans A1:D7 (avec A1 comme en-tête en haut à gauche), structurées comme région/catégorie dans la colonne A et les valeurs des sous-catégories (par exemple, T1, T2, T3) dans les colonnes B, C, D.
  • Vous souhaitez afficher chaque catégorie comme un cluster avec les valeurs Q empilées, en utilisant des lignes vides pour séparer les clusters.

1. Dans votre nouvelle feuille ou zone adjacente, créez une structure auxiliaire pour extraire chaque groupe et insérer des lignes vides. Par exemple, pour copier la première ligne de données vers E2:G2 :

=INDEX($A$2:$D$7,INT((ROW()-2)/2)+1,COLUMN()-4+1)

Faites glisser cette formule vers le bas selon vos besoins. Pour insérer des lignes vides entre les groupes, configurez une formule SI pour retourner une chaîne vide ("") sur des lignes alternées :

=IF(ISODD(ROW()), "", INDEX($A$2:$D$7,ROW()/2,COLUMN()-4+1))

Utilisez une combinaison de ces formules avec des références soigneusement structurées pour générer vos données restructurées, y compris des lignes vides à intervalles réguliers.

2. Une fois que votre plage de transformation est complète (avec des piles et des clusters), sélectionnez cette nouvelle plage et créez votre histogramme en colonnes empilées en suivant la méthode originale donnée précédemment (Insérer > Colonne empilée). Le graphique reflétera désormais automatiquement toutes les modifications que vous apportez au tableau de données original.

Pour les grands ensembles de données, il est souvent utile d'utiliser la fonction DÉCALAGE pour rendre le processus d'extraction plus flexible ou d'appliquer des plages nommées dynamiques pour définir la source du graphique.

Avantages : Pas de VBA ni de macros nécessaires, idéal pour les environnements avec restrictions de scripts.
Inconvénients : Configuration complexe de formules pour les grandes données, éventuel ralentissement des performances avec des plages dynamiques très grandes.

Dépannage : Si votre graphique ne se met pas à jour correctement, vérifiez les erreurs de référence ou les incohérences dans les formules auxiliaires. Assurez-vous que les lignes vides sont correctement insérées, car elles sont essentielles pour obtenir l'apparence "groupée".

Plus d'articles relatifs aux graphiques :

  • Créer un graphique à barres superposé dans Excel
  • Lorsque nous créons un graphique à barres ou en colonnes groupées avec deux séries de données, les barres des deux séries seront affichées côte à côte. Mais parfois, nous devons utiliser un graphique à barres superposées ou superposées pour comparer les deux séries de données plus clairement. Dans cet article, je vais parler de la façon de créer un graphique à barres superposées dans Excel.
  • Créer un graphique en escalier dans Excel
  • Un graphique en escalier est utilisé pour montrer les changements survenus à des intervalles irréguliers ; il s'agit d'une version étendue d'un graphique linéaire. Mais il n'y a pas de moyen direct de le créer dans Excel. Cet article vous expliquera comment créer un graphique en escalier étape par étape dans une feuille de calcul Excel.
  • Mettre en évidence les points de données Max et Min dans un graphique
  • Si vous avez un graphique en colonnes que vous souhaitez mettre en évidence avec différentes couleurs les valeurs les plus élevées ou les plus faibles pour les faire ressortir, comme le montre la capture d'écran suivante. Comment pourriez-vous identifier les valeurs les plus élevées et les plus faibles, puis mettre en évidence les points de données dans le graphique rapidement ?
  • Créer un modèle de graphique en courbe de cloche dans Excel
  • Le graphique en courbe de cloche, appelé distributions de probabilité normales en statistique, est généralement utilisé pour montrer les événements probables, et le sommet de la courbe de cloche indique l'événement le plus probable. Dans cet article, je vais vous guider pour créer un graphique en courbe de cloche avec vos propres données et enregistrer le classeur comme modèle dans Excel.
  • Créer un graphique à bulles avec plusieurs séries dans Excel
  • Comme nous le savons, pour créer rapidement un graphique à bulles, vous créerez toutes les séries comme une seule série, comme le montre la capture d'écran 1, mais maintenant je vais vous dire comment créer un graphique à bulles avec plusieurs séries comme le montre la capture d'écran 2 dans Excel.
  • Barre de formule avancée (éditez facilement plusieurs lignes de texte et de formules); Mode Lecture (lisez et éditez facilement un grand nombre de cellules); Coller dans une plage filtrée...
  • Fusionner des cellules/lignes/colonnes tout en conservant les données; Diviser le contenu des cellules; Consolider des lignes en double et faire la somme/moyenne... Empêcher les doublons dans les cellules; Comparer des plages...
  • Sélectionner des lignes en double ou uniques; Sélectionner des lignes vides (toutes les cellules sont vides); Super Recherche et recherche floue dans de nombreux classeurs; Sélection aléatoire...
  • Copie exacte de plusieurs cellules sans changer les références de formule; Créer automatiquement des références vers plusieurs feuilles; Insérer des puces, des cases à cocher et plus encore...
  • Ajouter et insérer rapidement des formules, des plages, des graphiques et des images; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails...
  • Extraire du texte, ajouter du texte, supprimer par position, supprimer des espaces; Créer et imprimer des statistiques de page; Convertir entre le contenu des cellules et les commentaires...
  • Super Filtre (enregistrer et appliquer des schémas de filtre à d'autres feuilles); Tri avancé par mois/semaine/jour, fréquence et plus; Filtre spécial par gras, italique...
  • Consolider des classeurs et feuilles de calcul; Fusionner des tables basées sur des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots de fichiers xls, xlsx et PDF...
  • Regroupement dans les tableaux croisés dynamiques par numéro de semaine, jour de la semaine et plus... Afficher les cellules verrouillées/déverrouillées avec différentes couleurs; Mettre en évidence les cellules contenant des formules/noms...
kte tab 201905
  • Activer l'édition et la lecture à onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrir et créer plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50 % et réduit des centaines de clics de souris chaque jour !
officetab bottom