Le graphe du « waterfall »

Nous construisons aujourd’hui le graphe du waterfall à partir des valeurs obenues grâce aux formules présentées dans l’article publié il y a cinq jours.

La première étape consiste à sélectionner A2:B7 puis, en maintenant la touche [Ctrl] enfoncée – afin d’obtenir une sélection multiple –, le bloc C2:F7.

Puis nous utilisons la commande « Insertion Colonne », en sélectionnant la seconde option, c’est-à-dire l’histogramme cumulé en 2D. Nous obtenons alors le premier graphe ci-dessous.

Pour obtenir le second graphe, nous avons suivi plusieurs étapes.

1 – Destruction de la légende, par clic droit et « Supprimer ».

2 – Modification des couleurs pour les séries en bleu, rouge et vert.

3 – Pour la dernière série, « Aucun trait » et « Aucun remplissage ».

4 – Amélioration de la présentation pour les textes de l’axe des X.

En ce qui concerne le quatrième point, quand la légende a disparu, la partie utile du graphe s’est trouvée agrandie et les titres « Effet prix » et  « Effet coûts » se sont retrouvés sur la première ligne, alors que « Effet volume » s’affichait sur deux lignes. Ce n’était pas beau…

Je suis donc revenu sur les cellules B3 et B5 correspondant à ces titres et j’ai créé un saut de ligne en utilisant la combinaison [Alt]-[Entrée] pour provoquer un retour à la ligne entre « Effet » et « prix » ou « coûts ».

Les formules du « waterfall »

Les graphes en waterfall – aussi appelés bridges – sont régulièrement utilisés dans certains domaines, mais plus particulièrement en finance. Ils y servent à mettre en relief l’impact d’un certain nombre de recettes et de dépenses sur une situation donnée, les dépenses étant représentées en rouge et les recettes en vert.

J’avais déjà publié un article sur ce sujet en 2011 mais, comme c’est un sujet qui intéresse beaucoup de monde, je reprends ce sujet en y mettant un peu plus de détails.

Dans l’exemple ci-dessous, nous avons l’EBITDA 2012 et les divers éléments qui permettent d’en tirer l’EBITDA 2013. Les valeurs d’origine sont en colonne B.

Pour construire le graphe, il faut créer les colonnes C à F qui respectivement correspondront aux barres relatives à chaque série, chacune ayant sa couleur distinctive..

Dans l’encadré bleu, vous trouverez toutes les formules des colonnes C à F. En fait, la formule de la ligne 3 est tout simplement recopiée vers le bas jusqu’à la ligne 6.

Dans le prochain article, nous utiliserons ces données pour construire notre graphe en waterfall.

Liste dynamique sans répétition

Le problème de ce jour représente un véritable casse-tête. Vous disposez en colonne C d’une liste de noms dans laquelle il y a des répétitions. Vous souhaitez obtenir en colonne A, de façon dynamique, la liste de ces noms sans aucune répétition.

Comment faites-vous ? Essayez donc de trouver une solution par vous- même : il s’agit là d’un véritable challenge pour pro d’Excel …

La solution que je vais vous présenter provient de Roberto Mensa, qui a déjà été notre inspirateur à plusieurs reprises. Voici donc la formule entrée en A2 et recopiée ensuite vers le bas :

=index(Noms;petite.valeur(si(frequence(equiv(Noms;Noms;);equiv(Noms;Noms;));equiv(Noms;Noms;));ligne(A1)))

Cette formule est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].

Pour mieux la comprendre, nous allons la décortiquer…

equiv(Noms;Noms;) engendre le résultat {1;2;1;4;5;6;2;8} : c’est la liste des positions dans la liste des noms de la première occurrence de chaque nom rencontré.

Remarque 1 – Quand le troisième argument de l’equiv() est absent, sa valeur par défaut est « 0 ». Si l’on avait omis le dernier « ; », la valeur par défaut de ce troisième argument aurait été « 1 ». Le fait de ne pas saisir le « 0 » ne sert qu’à économiser un caractère !

frequence({1;2;1;4;5;6;2;8};{1;2;1;4;5;6;2;8}) est évalué en {2;2;0;1;1;1;0;1;0}. On obtient donc à chaque fois la fréquence du nom si le nom est original, et « 0 » autrement.

si({2;2;0;1;1;1;0;1;0};{1;2;1;4;5;6;2;8}) devient {1;2;FAUX;4;5;6;FAUX;8;FAUX}. On a maintenant la liste des positions des noms originaux.

Le reste est élémentaire. La fonction petite.valeur() sert à évacuer les résultats faux et ne garde que ceux avec un nombre. La fonction index reprend le nom indexé par la énième petite valeur.

Remarque 2 – Bien entendu, tout cela est encore mieux quand la définition de « Noms » est élastique. Mais cela ne devrait vous poser aucun problème si vous lisez ce blog de façon régulière…

Brillant, n’est-ce pas ?

Construire une table de fréquence

Supposons que vous souhaitiez construire dans la colonne D une table de fréquence des 20 valeurs (de 0 à 999) de la colonne A, c’est-à-dire savoir combien de valeurs sont inférieures ou égales à 250, comprises entre 250 et 500, entre 500 et 600,…

Pour obtenir les fréquences individuelles, il faut sélectionner le bloc D1:D6, saisir la formule fatidique =frequence($A$1:$A$20;C1:C6), puis la valider comme une formule matricielle en utilisant à cet effet la combinaison de touches [Ctrl]-[Maj]-[Entrée] !

Si en revanche vous souhaitez obtenir des fréquences cumulées, il faut sélectionner E1:E6, saisir la même formule, mais la valider en saisie multiple avec [Ctrl]-[Entrée]…

Une petite astuce enfin… Comment ai-je obtenu les libellés de l’axe des abscisses ? Tout simplement en sélectionnant cet axe, et en lui attribuant le format numérique "<="0 !

Remarque – Nous avons repris ici un article publié au début de ce blog, le 5 janvier 2006. En effet, non seulement la fonction frequence() est-elle intéressante, et cela fait plus de sept ans que nous n’en avions pas parlé, mais elle va aussi nous servir dans le prochain article…

Synchronisation des divers TCDs

Avec le tableau de bord final obtenu dans l’article précédent, chacun des segments est indépendant des autres segments.

Ainsi, quand on sélectionne cinq clients dans le premier segment, cela ne change en rien l’affichage des graphes liés aux autres segments. De la même façon, la modification des paramètres d’un autre segment que le premier n’a aucune influence sur le premier graphe.

Or, on peut tout à fait imaginer des tableaux de bord dans lesquels tous les segments seraient liés. Ainsi, le premier graphe ne représenterait que les CAs des clients sélectionnés dans le premier segment et effectués par l’intermédiaire des représentants sélectionnés dans le second segment et pour les dates correspondant aux sélections du troisième et du quatrième segment et enfin pour les durées d’appel actives dans le dernier segment !

Pour atteindre cet objectif, il vous suffit de sélectionner l’un quelconque des quatre segments et, dans le ruban « Options » de la commande « Outils Segment », d’utiliser la commande « Connexions de tableau croisé dynamique ».

Il ne vous reste plus qu’à cocher les quatre tableaux pour que les segments associés à ces TCDs soient enfin liés :

Remarque – Pour déconnecter les TCDs, il suffit de réaliser l’opération inverse en décochant les TCDs que vous souhaitez maintenir indépendants. L’impact de cette modification est immédiat.

Le tableau de bord final

Voici le tableau de bord auquel nous étions arrivés à la fin de l'article publié le 22 mai dernier :

Après avoir personnalisé les autres graphes et segments de la même façon que nous avons personnalisé le premier graphe et le premier segment, nous avons enfin obtenu le tableau de bord final.

Dans ce tableau de bord, les couleurs sélectionnées nous permettent de voir rapidement quel est le segment associé à chaque graphe :