Comment construire un graphique en cascade

De plus en plus souvent je rencontre dans le reporting de différentes entreprises et j'entends des demandes de stagiaires pour expliquer comment se construit un diagramme en cascade des déviations - c'est aussi une "cascade", c'est aussi une "cascade", c'est aussi un "pont », c'est aussi un « pont », etc. Cela ressemble à ceci :

De loin, cela ressemble vraiment à une cascade de cascades sur une rivière de montagne ou à un pont suspendu - qui voit quoi 🙂

La particularité d'un tel diagramme est que :

• On voit clairement la valeur initiale et finale du paramètre (les première et dernière colonnes).
• Les changements positifs (croissance) sont affichés en une seule couleur (généralement et une transition qui soit juste.) et négatives (déclin) aux autres (habituellement rouge).
  
• Parfois, le graphique peut également contenir des colonnes de sous-totaux (grisatterri sur les colonnes de l'axe des x).
  

Dans la vie de tous les jours, de tels schémas sont généralement utilisés dans les cas suivants :

• Visuel affichage dynamique tout processus dans le temps: flux de trésorerie (cash-flow), investissements (nous investissons dans un projet et en tirons profit).
• Visualisation mise en œuvre du plan (la colonne la plus à gauche du diagramme est un fait, la colonne la plus à droite est un plan, le diagramme entier reflète notre processus d'évolution vers le résultat souhaité)
• Quand vous avez besoin de visuel afficher les facteursqui affectent notre paramètre (analyse factorielle du profit – en quoi il consiste).
  

Il existe plusieurs façons de créer un tel graphique - tout dépend de votre version de Microsoft Excel.

Méthode 1 : la plus simple : type intégré dans Excel 2016 et versions ultérieures

Si vous avez Excel 2016, 2019 ou version ultérieure (ou Office 365), la création d'un tel graphique n'est pas difficile - ces versions d'Excel ont déjà ce type intégré par défaut. Il suffira de sélectionner un tableau avec des données et de sélectionner dans l'onglet insérer (Insérer) Command en cascade (Cascade):

En conséquence, nous obtiendrons un diagramme presque prêt à l'emploi:

Vous pouvez immédiatement définir les couleurs de remplissage souhaitées pour les colonnes positives et négatives. La façon la plus simple de le faire est de sélectionner les lignes appropriées Améliore и diminuer directement dans la légende et en faisant un clic droit dessus, sélectionnez la commande Niveau de remplissage (Remplir):

Si vous devez ajouter des colonnes avec des sous-totaux ou un total de colonne final au graphique, il est plus pratique de le faire en utilisant des fonctions SOUS-TOTALS (SOUS-TOTALS) or UNITÉ (AGRÉGAT). Ils calculeront le montant cumulé depuis le début du tableau, en en excluant les totaux similaires situés au-dessus :

Dans ce cas, le premier argument (9) est le code de l'opération de sommation mathématique, et le second (0) amène la fonction à ignorer les totaux déjà calculés pour les trimestres précédents dans les résultats.

Après avoir ajouté des lignes avec des totaux, il reste à sélectionner les colonnes de totaux qui sont apparues sur le diagramme (faire deux clics simples consécutifs sur la colonne) et, en faisant un clic droit sur la souris, sélectionner la commande Définir comme total (Définir comme total):

La colonne sélectionnée atterrira sur l'axe des x et changera automatiquement de couleur en gris.

En fait, c'est tout - le diagramme en cascade est prêt :

Méthode 2. Universel : colonnes invisibles

Si vous avez Excel 2013 ou des versions antérieures (2010, 2007, etc.), la méthode décrite ci-dessus ne fonctionnera pas pour vous. Vous devrez faire le tour et découper le graphique en cascade manquant à partir d'un histogramme empilé régulier (en additionnant les barres les unes sur les autres).

L'astuce ici consiste à utiliser des colonnes prop transparentes pour élever nos lignes de données rouges et vertes à la bonne hauteur :

Pour construire un tel graphique, nous devons ajouter quelques colonnes auxiliaires supplémentaires avec des formules aux données source :

• Tout d'abord, nous devons diviser notre colonne d'origine en séparant les valeurs positives et négatives dans des colonnes distinctes à l'aide de la fonction IF (SI).  
• Deuxièmement, vous devrez ajouter une colonne devant les colonnes sucettes, où la première valeur sera 0, et à partir de la deuxième cellule, la formule calculera la hauteur de ces colonnes de support très transparentes.
  

Après cela, il reste à sélectionner l'ensemble du tableau à l'exception de la colonne d'origine Flow et créer un histogramme empilé régulier à travers Encart — Histogramme (Insérer — Diagramme à colonnes):

Si vous sélectionnez maintenant les colonnes bleues et les rendez invisibles (clic droit dessus - Format de ligne – Remplir – Pas de remplissage), alors nous obtenons juste ce dont nous avons besoin. 

L'avantage de cette méthode est la simplicité. Dans les inconvénients – la nécessité de compter les colonnes auxiliaires.

Méthode 3. Si on rentre dans le rouge, tout est plus difficile

Malheureusement, la méthode précédente ne fonctionne correctement que pour des valeurs positives. Si au moins dans certaines zones, notre cascade entre dans une zone négative, la complexité de la tâche augmente considérablement. Dans ce cas, il faudra calculer chaque ligne (fictive, verte et rouge) séparément pour les parties négatives et positives avec des formules :

Afin de ne pas trop souffrir et de ne pas réinventer la roue, un modèle prêt à l'emploi pour un tel cas peut être téléchargé dans le titre de cet article.

Méthode 4. Exotique : bandes haut-bas

Cette méthode est basée sur l'utilisation d'un élément spécial peu connu des tableaux plats (histogrammes et graphiques) - Bandes haut-bas (Barres haut-bas). Ces bandes relient les points de deux graphiques par paires pour montrer clairement lequel des deux points est supérieur ou inférieur, ce qui est activement utilisé lors de la visualisation du plan-fait :

Il est facile de comprendre que si nous supprimons les lignes des graphiques et ne laissons que les bandes haut-bas sur le graphique, nous obtiendrons la même "cascade".

Pour une telle construction, nous devons ajouter deux autres colonnes supplémentaires à notre tableau avec des formules simples qui calculeront la position des deux graphiques invisibles requis :

Pour créer une "cascade", vous devez sélectionner une colonne avec des mois (pour les signatures le long de l'axe X) et deux colonnes supplémentaires calendrier 1 и calendrier 2 et construire un graphique régulier pour les débutants en utilisant Insertion – Graphique (Insérer - Graphique linéaire):

Ajoutons maintenant des bandes haut-bas à notre graphique :

• Dans Excel 2013 et plus récent, cela doit être sélectionné dans l'onglet Constructeur Command Ajouter un élément de graphique — Bandes d'augmentation-diminution (Conception - Ajouter un élément de graphique - Barres haut-bas)
• Dans Excel 2007-2010 - allez à l'onglet Mise en page - Barres d'avance-décrémentation (Mise en page — Barres haut-bas)

Le graphique ressemblera alors à ceci :

Il reste à sélectionner les graphiques et à les rendre transparents en cliquant successivement dessus avec le bouton droit de la souris et en sélectionnant la commande Format des séries de données (Série de formats). De même, vous pouvez changer les couleurs de rayures noires et blanches standard, plutôt minables, en vert et rouge pour obtenir une image plus agréable à la fin :

Dans les dernières versions de Microsoft Excel, la largeur des barres peut être modifiée en cliquant sur l'un des graphiques transparents (pas les barres !) avec le bouton droit de la souris et en sélectionnant la commande Format des séries de données – Dégagement latéral (Série de formats — Largeur d'espacement).

Dans les anciennes versions d'Excel, vous deviez utiliser la commande Visual Basic pour résoudre ce problème :

1. Mettez en surbrillance le diagramme construit
2. Appuyez sur le raccourci clavier autre+F11pour accéder à Visual Basic Editor
3. Appuyez sur le raccourci clavier Ctrl+Gpour ouvrir le panneau d'entrée de commande directe et de débogage Immédiat (généralement situé en bas).

4. Copiez et collez-y la commande suivante : ActiveChart.ChartGroups(1).GapWidth = 30 et appuyez sur Entrer:

Vous pouvez, bien sûr, jouer avec la valeur du paramètre si vous le souhaitez. ÉcartLargeurpour obtenir le dégagement souhaité :

• Comment créer un tableau à puces dans Excel pour visualiser les KPI  
• Quoi de neuf dans les graphiques dans Excel 2013
• Comment créer un graphique interactif "en direct" dans Excel