La méthode de construction d'un graphique en cascade décrite ici s'applique aux versions avant Excel 2016. Depuis la version Excel 2016, le type "Graphique en cascade" est accessible directement dans les options des graphiques.
L'intérêt de créer un graphique en cascade
Un graphique en cascade permet d'afficher le détail d'une variation d'un graphique en barre. Dans l'exemple ci-dessous, vous avez la valeur de votre portefeuille boursier présentant le résultat à la fin de chaque mois boursier.
Cependant, nous n'avons aucune indication sur le détail, valeur par valeur, du gain ou des pertes réalisés au cours du mois.
- Entre Mai et Juin, nous constatons une baisse
- Mais nous ne sommes pas capables de dire quelles sont les valeurs boursières responsables de cette perte.
- C'est dans ce cadre qu'un graphique en cascade va nous permettre de compléter le diagramme en barre.
- Nous allons faire ressortir l'impact de chaque action sur la variation globale.
- Dans l'exemple suivant, 3 actions ont augmentées sur la période
- Mais la baisse de seulement 2 actions a eu un impact négatif sur l'ensemble du mois
Etape 1 : La structure du tableau de données
Pour construire un graphique en cascade dans toutes les versions avant Excel 2016, il faut respecter scrupuleusement les étapes de construction du tableau. Nous allons écrire les données suivantes dans cet ordre :
- Valeur totale de notre portefeuille fin de mois précédent
- Valeur totale de notre portefeuille fin de mois courant
- La valeur de chaque valeur fin de mois précédent
- La valeur de chaque valeur fin de mois courant
- Ecarts positifs constatés
- Ecarts négatifs constatés
- Correction à apporter (ça sera notre hauteur invisible)
Etape 2 : Valeur de la première barre
La première barre représente nécessairement la valeur de notre portefeuille boursier fin de mois. Dans la cellule D2, nous allons effectuer la somme de la colonne B =SOMME(B3:B7)
Etape 3 : Ecart positif
Sachant que notre graphique doit présenter les valeurs positives et négatives des variations, nous devons dissocier les écarts positifs et négatifs en utilisant la fonction SI. Dans notre tableau, nous allons tout d'abord écrire le résultat de la différence entre les 2 valeurs quand l'écart est positif. Si le test est faux, nous écrivons 0. La fonction s'écrit :
Etape 4 : Ecart négatif
Dans cette colonne, nous ne nous intéressons qu'aux valeurs qui ont subi une perte. Mais attention, c'est la valeur absolue qui nous intéresse ici. Le chiffre à inscrire dans la colonne doit être positif. Ce qui implique que nous inversons les valeurs à retrancher comme ceci.
Pour une même ligne, nous avons toujours une colonne avec une valeur positive et l'autre avec la valeur 0
Etape 5 : Calcul de la correction
Le calcul de la correction à apporter est important car c'est ce qui va déterminer la hauteur de la barre invisible. Le calcul mathématique n'est pas très compliqué mais demande juste d'être attentif pour ne pas commettre d'erreurs dans votre résultat final. En cellule H3, vous allez additionner 3 cellules de la ligne précédente à savoir
- le résultat du mois précédent (D2)
- le résultat de la correction précédente (H2)
- la variation positive constatée précédemment (E2)
A ce résultat, vous retranchez la variation négative constatée pour l'action étudiée (F3).
Recopier cette formule pour toutes les autres cellules de la colonne H.
Etape 6 : Vérification
Pour terminer notre calcul, nous devons renseigner la valeur de notre portefeuille fin de mois. (G8). Ce doit être égale à
- La somme de la dernière cellule de la colonne Correction (H7)
- L'ajout du dernier chiffre positif (E7).
Le résultat est nécessairement égal à la somme du mois actuel (C8).
Etape 7 : Choix du graphique
Pour concevoir un graphique en cascade, vous devez choisir le graphique histogramme empilé (ou barre empilée).
Et vous obtenez un graphique qui ressemble à ceci
Etape 8 : Intervertir lignes et colonnes
Il se peut qu'Excel aie bien "compris" ce que vous voulez faire. Auquel cas, cette étape n'est pas nécessaire. Mais si vous êtes dans le cas de figure précédent, avec les données de la ligne 1 en abscisse, alors vous devez cliquer sur le menu Création de graphique > Intervertir les lignes et colonnes.
Votre graphique ressemble déjà davantage au résultat final. Mais il reste encore quelques étapes pour terminer le graphique.
Etape 9 : Suppression des barres inutiles
- Les barres de données M et M-1 sont inutiles dans notre graphique et surtout perturbent le résultat.
- Pour les retirer faites un clic-droit dans votre graphique
- Et choisissez l'option Sélectionner des données.
- Dans la fenêtre qui s'ouvre, vous décochez les cases avant M et M-1
- Pour les versions antérieures à Excel 2013, vous devez sélectionner chaque élément un par un et cliquer sur le bouton Supprimer.
- Vous obtenez maintenant le graphique suivant :
Etape 10 : Permuter l'ordre des séries
Ici, on reste dans la même fenêtre que l'étape précédente. Nous devons mettre la série Correction en première position de l'empilement.
- Rouvrez la fenêtre Source de données
- Sélectionnez la série Correction.
- Positionnez la série en première position avec les flèches de positionnement
Etape 11 : Intervalle entre les séries
Pour faciliter la lecture entre les séries, il est préférable de supprimer l'espace qui existe entre chaque barre, exactement comme cela a été fait pour la pyramide des âges.
- Double-cliquez sur l'une des séries pour faire apparaître le volet d'option de la série.
- Réduisez la largeur de l'intervalle à 0%.
Etape 12 : Modifier les couleurs et échelle minimale
Changer les couleurs d'une série ne devrait pas vous poser de problème, aussi nous ne ferons pas de capture d'écran pour chacune d'elle mais nous allons détailler pour chaque série les couleurs à appliquer
- Mois précédant : couleur unie grise
- Correction : couleur de même couleur que le fond (blanc ici) et laisser une bordure grisée pour mieux visualiser les ruptures.
- Positive : couleur verte
- Négative : couleur rouge
- Mois courant : idem à la couleur Mois précédant
- Pour l'échelle, vous devez réduire la borne inférieure pour qu'elle corresponde à 10% ou 15% de la limite inférieure.
- Retirez la légende
Nous avons alors le graphique suivant :
Le graphique nous permet de connaître les valeurs qui ont montées ou baissées entre les deux périodes.
31/12/2018 @ 11:57
Merci§!!!!
06/04/2017 @ 10:35
Un grand merci, ça m'a été très utile !!
17/02/2016 @ 11:10
Bonjour,
Je suis bloqué moi aussi au niveau des variations qui traversent l'axe des abscisses, tout comme Pierre-Hugues. Ce tuto ne permet pas de résoudre les problèmes de valeurs qui entraînent un passage sous zéro. Quelqu'un a une solution ?
Merci
03/10/2015 @ 18:44
Et comment on sélectionne les données ? Si vous les prenez toutes, vous avez plein de barres !!
27/05/2015 @ 10:40
L'exercice est très intéressant, mais je bloque sur le passage final quand il est question de réduire la borne inférieure afin d' avoir une action sur l'échelle.
C'est frustrant de ne pas aboutir au résultat final
06/01/2015 @ 19:52
Bonne question de @Tode ‼
Ce tutoriel ne permet pas de réaliser ce type de graphe si certaines des valeurs de l'analyse entraînent un passage sous zéro (analyse d'écart de résultats d'une entreprise par exemple).
17/01/2015 @ 21:36
Pour afficher les valeurs négatives, il suffit de changer les paramètres des axes pour inclure les valeurs négatives
04/02/2015 @ 16:28
Désolé mais ça n'est pas clair pour moi. Comment faire si certaines des valeurs de l'axe entraînent un passage sous zéro ou si la première valeur est < 0 ? Merci
20/12/2014 @ 12:36
Super, merci pour ce tuto. Excellente démonstration.
20/06/2014 @ 15:45
Est-il possible de faire ce type de graphique avec une borne postive et une borne négative, des variations qui traversent donc l'axe des abscisses? Merci beaucoup
10/05/2014 @ 15:19
Pédagogique, pratique et efficace ! Merci
06/05/2014 @ 14:00
Bonjour
Merci pour ce tutoriel bien sympathique qui m'a fait me sentir moinsseul après avoir longtemps cherché à réaliser ce fameux graphique en cascade...
Il manque une étape importante que je n'arrive pas à réaliser : comment faire apparaitre les étiquettes de données, sélectivement (soit les étiquettes de la série Positive ou Négative apparaissent, selon les barres), et accompagnées du signe '-' le cas échéant
Encore Merci !!!
12/10/2015 @ 11:32
Il faut agir sur le format des cellules du tableau :
Colonne "Positif" : +# ##0;-# ##0;
Colonne "Négatif" : -# ##0;+# ##0;
Surtout ne rien mettre après le dernier point-virgule.
Seules les étiquettes différentes de zéro apparaîtrons, avec le signe - pour les valeurs négatives.
12/12/2013 @ 15:46
les resultas de l'exercice sont rentables