«

»

Graphique en cascade

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.

Graphique_Cascade_1

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.

Par exemple, entre Mai et Juin, nous constatons une baisse mais nous ne sommes pas capables de dire quelles sont les actions responsables de cette perte.

C’est là qu’un graphique en cascade va nous permettre de compléter le diagramme en barre par un détail plus fin expliquant la variation entre 2 barres.

 Graphique_Cascade_2

Comme vous le constatez, seules 2 des 5 actions que nous possédons en portefeuille engendrent une baisse globale de notre portefeuille actions.

Carrefour, EDF et Total ont augmenté mais L’Oréal et Orange ont quant à elles, baissé.  Et la somme des baisses ne compense pas la hausse des 3 autres valeurs.

Comment a été construit ce graphique en cascade ?

Construction

Si vous travaillez avec Office 2016 ou Office 365, la création d’un graphique en cascade est directement accessible depuis le menu Insertion>Graphique. Si c’est le cas, allez sur cette page pour connaître la méthodologie qui est beaucoup facile à mettre en oeuvre que celle présentée ici.

Pour construire un graphique en cascade dans toutes autres versions d’Excel, il faut respecter scrupuleusement les étapes qui suivent.

La structure du tableau de données



Nous allons commencer par construire un tableau avec les données suivantes :

  • 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)

Notre tableau se présente alors de la façon suivante

Graphique_Cascade_4

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)

Graphique_Cascade_5

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 :

=SI(C3>B3;C3-B3;0)

Graphique_Cascade_6



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.

=SI(B3>=C3;B3-C3;0)

Graphique_Cascade_7

Pour une même ligne, nous avons toujours une colonne avec une valeur positive et l’autre avec la valeur 0



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).

=D2+H2+E2-F3

Graphique_Cascade_8

Recopier cette formule pour toutes les autres cellules de la colonne H.

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).

 Graphique_Cascade_9



Création du graphique

Maintenant que nous avons construit notre tableau de données, il ne nous reste plus qu’à créer notre graphique.



Choix du graphique

Pour concevoir un graphique en cascade, vous devez choisir le graphique histogramme (ou barre empilée).

Graphique_Cascade_10

Et vous obtenez un graphique qui ressemble à ceci

Graphique avec mauvaise interprétation des axes

Graphique avec mauvaise interprétation des axes

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>Intervertir les lignes et colonnes.

Graphique_Cascade_12

Votre graphique ressemble déjà davantage au résultat final. Mais il reste encore quelques étapes pour terminer le graphique.

Histogramme empilé avec axes correctement positionnés

Histogramme empilé avec axes correctement positionnés



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 vous choisissez l’option Sélectionner des données.

Graphique_Cascade_14

Dans la fenêtre qui s’ouvre, vous décochez les cases avant M et M-1

Graphique_Cascade_15

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 :

Graphique_Cascade_16



Permuter les colonnes

Graphique_Cascade_17

Encore 2 étapes et le travail est terminé

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

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.

Avec Excel 2013, double-cliquez sur l’une des séries (cela n’a pas d’importance) pour faire apparaître le volet d’option de la série.

Réduisez la largeur de l’intervalle à 0%.

Avec les versions antérieures, vous devez afficher la fenêtre des options des séries en effectuant un clic-droit sur l’une des séries et vous changez la même valeur dans cette fenêtre.

 

Et c’est terminé (enfin presque). Il ne reste plus qu’à changer les couleurs et l’échelle pour mieux visualiser les écarts entre chaque série.

Graphique_Cascade_19



Couleur et échelle

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 :

Graphique_Cascade_20a

Le résultat final correspond maintenant au graphique ci-dessus, qui nous permet de mieux visualiser la hausse de notre portefeuille boursier, tout en constatant que 2 valeurs ont eu un rendement négatif sur la période.




Lien Permanent pour cet article : https://www.excel-exercice.com/graphique-en-cascade/

(13 commentaires)

1 ping

Passer au formulaire de commentaire

  1. Van

    Un grand merci, ça m’a été très utile !!

  2. vincent rm

    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

  3. Anonyme

    Et comment on sélectionne les données ? Si vous les prenez toutes, vous avez plein de barres !!

  4. Anonyme

    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

  5. Pierre-Hugues

    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).

    1. Frédéric LE GUEN

      Pour afficher les valeurs négatives, il suffit de changer les paramètres des axes pour inclure les valeurs négatives

      1. greg

        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

  6. Sebastien

    Super, merci pour ce tuto. Excellente démonstration.

  7. Tode

    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

  8. Dom

    Pédagogique, pratique et efficace ! Merci

  9. Jean-Jacques

    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 !!!

    1. Anonyme

      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.

  10. latifasioud

    les resultas de l’exercice sont rentables

  1. Créer un graphique en cascade - Excel Exercice

    […] Graphique en cascade […]

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Vous pouvez utiliser ces balises et attributs HTML : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Advertisment ad adsense adlogger