Avez-vous pu trouver le type de graphique à colonnes empilées en cluster Excel?
Eh bien, ce n’est pas là, malheureusement. Mais Excel est un outil puissant et nous pouvons utiliser d’autres options pour créer le graphique que nous recherchons avec cette astuce simple pour créer votre propre graphique à colonnes empilées en cluster Excel. Ce didacticiel est axé sur Microsoft Excel 2013, Excel 2016 et Excel 2019. Mais il fonctionnera très probablement pour les versions après 2019. Si vous voulez voir les étapes requises pour Excel 20010 et Excel 2007, consultez ce didacticiel: comment faire facilement-créer-un-histogramme-cluster-empilé-dans-Excel
La répartition
Voici les étapes de base que nous allons parcourir dans ce tutoriel. L’exemple de fichier à télécharger et la démonstration vidéo se trouvent à la fin de l’article.
1) Données de configuration
2) Créer un histogramme empilé
3) Changer les lignes / colonnes du graphique (si nécessaire)
4) Diminuer la largeur de l’écart des séries (comme souhaité)
Étapes facultatives pour les fonctionnalités du graphique étendu:
5) Ajouter une série de remplissage pour la séparation
5a) Insérer des lignes selon les besoins dans le graphique précédemment créé
6) Ajouter Série d’étiquettes verticales du 2e axe
6a) Ajouter une série
6b) Répétez les étapes 2 et 3
6c) Déplacer la nouvelle série d’étiquettes Veritical 2e axe vers le 2e axe
6d) Remplacez le nouveau remplissage vertical de la série d’étiquettes du 2e axe par Aucun remplissage
6e) Diminuer l’écart de série Largeur (comme souhaité)
6f) Supprimer l’entrée de légende d’étiquette du 2ème axe
Pas à pas
1) Données de configuration
Afin de créer un type de graphique à colonnes empilées en cluster simulé, nous devons simplement effectuer 4 étapes. Cette astuce utilise désormais l’option de graphique Excel « Libellés de catégories à plusieurs niveaux » pour les histogrammes. Pour utiliser cette option, nous devons configurer nos données d’une certaine manière. C’est donc notre première étape.
Notre scénario est que nous construisons un graphique pour l’équipe marketing sur le montant dépensé et le montant budgété pour 2 produits sur 4 types de supports (imprimé, Internet, TV, radio). Les données que nous avons reçues étaient dans ce format.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Budget des ventes par rapport au budget réel | ||||||||
| 2 | Radio | Radio | Imprimer | Imprimer | TV | TV | Internet | Internet | |
| 3 | Budget | Réel | Budget | Réel | Budget | Réel | Budget | Réel | |
| 4 | Produit 1 | 123 | 166 | 128 | 91 | 163 | 181 | 171 | |
| 5 | Produit 2 | 63 | 149 | 157 | 101 | 53 | 140 | 198 | 153 |
Données d’origine
Pour utiliser l’option « Libellés de catégorie à plusieurs niveaux » dans un histogramme, nous devons configurer nos données avec catégories à plusieurs niveaux. Cela signifie que nous avons une catégorie principale dans une colonne et de nombreuses catégories dans la colonne de droite. Par conséquent, nous devons modifier nos données au format suivant.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Budget des ventes par rapport au budget réel | |||||
| 2 | Radio | Imprimer | TV | Internet | ||
| 3 | Produit 1 | Budget | 123 | 155 | 91 | 181 |
| 4 | Réel | 166 | 128 | 163 | 171 | |
| 5 | Produit 2 | Budget | 63 | 157 | 53 | 198 |
| 6 | Réel | 149 | 101 | 140 | 153 |
Configuration de base des graphiques empilés en cluster
Vous pouvez donc voir que nos données a été réorganisé pour que Le produit 1 est associé à 2 lignes. Un pour les valeurs budgétaires et un pour les valeurs réelles des 4 types de publicité médiatique. Nous sommes maintenant prêts à créer le graphique à colonnes empilées en cluster Excel.
2) Créer un graphique à colonnes empilées
Pour créer notre graphique, mettez en surbrillance la plage de données dans l’ensemble de données ci-dessus à partir de A2: F6. Ensuite, allez dans le ruban d’insertion et cliquez sur le graphique en colonnes empilées dans le bouton de graphique comme vous le voyez ici.
Votre graphique devrait maintenant ressembler à ceci:
3 ) Changer les lignes / colonnes du graphique (si nécessaire)
Notre graphique ne semble pas tout à fait correct. Dans la plupart des cas, vous devez changer de ligne / colonne sur votre graphique. Pourquoi ai-je besoin de faire cela? Regardez cette vidéo ici
Pour corriger votre graphique, cliquez simplement sur le graphique, puis allez dans le ruban de conception et cliquez sur le bouton Changer de ligne / colonne.
Votre graphique est maintenant complet et devrait ressembler à ceci:
Si vous souhaitez rapprocher les colonnes, suivez l’étape suivante, mais elle est facultative.
4) Diminuez la largeur de l’intervalle de série (comme souhaité)
Si vous le souhaitez, et c’est mon look préféré, vous pouvez fermer la largeur de l’écart des colonnes. Pour ce faire, sélectionnez le graphique, puis sélectionnez une série de données et appuyez sur CTRL + F1 (ou cliquez avec le bouton droit et sélectionnez Formater la série de données). Ensuite, à partir de la boîte de dialogue Format de la série de données, modifiez la largeur de l’espace à 15% ou à ce qui vous semble le mieux.
Votre graphique final devrait maintenant ressembler à ce que vous voyez ci-dessus. Mais nous n’avons pas fini si vous souhaitez ajouter des modifications supplémentaires. Ci-dessous est complètement facultatif et comme vous le souhaitez, mais je pense que cela ajoute de la clarté et des détails supplémentaires qui aideront le lecteur.
Étapes facultatives du graphique à colonnes empilées en cluster
5) Ajouter une série de remplissage pour la séparation
Si vous souhaitez séparer les groupements, suivez cette étape.
5a) Insérez des lignes selon les besoins dans le graphique précédemment créé
Faites-le comme bon vous semble , insérez simplement une ligne dans vos données et cela séparera les groupes.
Après avoir inséré votre ligne, vous devez suivre les étapes 1 à 3 ci-dessus pour créer votre graphique depuis le début.Lorsque vous aurez terminé les étapes 1 à 3, vous verrez que votre graphique ne semble pas tout à fait correct car votre groupe de produits 1 comprend ce nouvel espace comme vous le voyez ici:
Pour résoudre ce problème, vous devez tromper Excel en créant un autre groupe de niveau un qui n’apparaît pas sur le graphique. C’est très simple en mettant simplement un espace dans l’ensemble de données dans la cellule juste au-dessus de l’entrée du produit 2.
Votre graphique final avec l’écart ajouté devrait ressemble à ceci avec le regroupement corrigé:
6) Ajouter une série d’étiquettes de 2e axe vertical
L’étape facultative 5 ci-dessus me semble bonne, mais les données du produit 2 sont plus éloignées des chiffres, alors j’aime parfois ajouter un série de graphiques supplémentaires sur l’axe secondaire pour que les numéros de l’axe vertical s’affichent plus près de cette série. Il s’agit d’une étape facultative, donc ne l’utilisez que si vous aimez également l’aspect.
6a) Ajouter une série
Pour que la valeur limite maximale de l’axe secondaire corresponde à la limite maximale de l’axe principal, nous devons créer une formule pour nos séries d’axes secondaires supplémentaires. Pour ce faire, créez une formule sur la première ligne de données avec une formule qui calcule la colonne empilée maximale de toutes les colonnes empilées qui seront dans le graphique. Par conséquent, dans la cellule G3, nous créons une formule MAX () avec une somme de chaque ligne. Pour plus de détails, consultez la formule Max et Sum ci-dessous.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Budget des ventes par rapport au budget réel | ||||||
| 2 | Radio | Imprimer | TV | Internet | Étiquettes du deuxième axe | ||
| 3 | Produit 1 | Budget | 123 | 155 | 91 | 181 | 628 |
| 4 | Réel | 166 | 128 | 163 | 171 | ||
| 5 | |||||||
| 6 | Produit 2 | Budget | 63 | 157 | 53 | 198 | |
| 7 | Réel | 149 | 101 | 140 | 153 |
Graphique du 2e axe facultatif
Formules de feuille de travail
|
6b) Répétez les étapes 2 et 3
Maintenant que vous avez créé votre ensemble de données, vous allez simplement suivre toutes les étapes de base 1 à 3 ci-dessus pour créer votre histogramme empilé en cluster mais changez votre plage de graphique en A2: G6 ou A2: G7 si vous avez ajouté une ligne supplémentaire pour un écart entre les regroupements. Dans mon cas, j’ai ajouté la ligne d’espacement, donc ma plage de graphique est A2: G7.Allez-y et créez le graphique initial comme vous le voyez ici:
L’initiale de votre graphique devrait maintenant ressembler à ceci si vous avez la ligne d’espacement dans la série de données après l’étape 2.
Après avoir changé de ligne / colonne, votre graphique ressemblera à ceci après l’étape 3:
6c) Déplacer la nouvelle série d’étiquettes Veritical 2nd Axis vers 2nd Axis
Notre graphique ressemble beaucoup à notre graphique de base, mais il existe une série supplémentaire que nous utiliserons pour notre axe secondaire. Sélectionnez donc le graphique, puis sélectionnez la série « Étiquettes du 2e axe » et appuyez sur CTRL + F1 ou cliquez dessus avec le bouton droit de la souris et sélectionnez l’option Format de la série de données…. Cela affichera l’option permettant de le déplacer vers l’axe secondaire. Faites-le maintenant .
6d) Remplacer le nouveau remplissage vertical de la série d’étiquettes du 2e axe par Aucun remplissage
Lorsque nous avons déplacé la série vers l’axe secondaire, elle recouvre l’une de nos colonnes empilées. Donc, pour contourner ce problème, nous devons changer le remplissage de la série sur Aucun remplissage. Si vous êtes toujours dans la boîte de dialogue Format de la série de données, cliquez sur les options de remplissage (on dirait qu’une peinture peut couler) et changez le remplissage en Aucun remplissage.
6d) Diminuer la largeur de l’écart de série (comme Désiré)
C’est la même chose que l’étape facultative 4 ci-dessus. J’ai changé la largeur de l’espace des colonnes empilées sur l’axe principal à 15% comme vous le voyez ici:
6f) Supprimer l’entrée de légende d’étiquette du 2e axe
La dernière chose que nous devons faire est de supprimer l’entrée de légende« Étiquettes du 2e axe »sur la légende. Pour ce faire, sélectionnez le graphique, puis sélectionnez la légende, puis sélectionnez la légende, puis sélectionnez l’entrée de légende « 2nd Axis Labels » et appuyez sur la touche Suppr.
Votre graphique final ressemblera maintenant à ceci:
C’est une excellente technique à faire un graphique combiné fréquemment utilisé pour les ensembles de données complexes. Il fonctionnera même pour d’autres ensembles de données produit. Voici un exemple de ce à quoi une autre série ressemblerait.
Démonstration vidéo
Découvrez ce didacticiel vidéo sur les techniques présenté ci-dessus.
Téléchargement de fichier d’exemple
Cliquez ici pour télécharger l’exemple de fichier de modèle Excel gratuit:
Clustered-Stacked -Column-Charts
