Un graphe élastique (a)

Chères lectrices, chers lecteurs,

Je vous prie de m'excuser de vous avoir laissé tomber quelques jours, j'étais assez pris ces derniers temps...

Rassurez-vous, j'essaierai de rattraper ce retard.

Construction d'un graphe élastique

En entreprise, nous avons souvent des modèles Excel où, chaque nouveau mois, il convient de saisir les données relatives à ce mois. Si ces données sont représentées dans un graphe, il est souhaitable que ce graphe s’actualise en temps réel pour intégrer la nouvelle donnée.

La solution qui semble la plus simple aujourd’hui est représentée par l’image reproduite plus bas. Nous avons sélectionné le bloc A1:B11 et nous l’avons mis en forme de tableau, en sélectionnant la commande Mettre sous forme de tableau du bloc Styles de l’onglet Accueil. Excel a aussitôt mis le tableau en forme selon le format que vous avez sélectionné et ajouté des menus déroulants dans la première ligne. Nous avons alors sélectionné A2 :B11 et inséré un histogramme.

Quand nous cliquons dans une barre de l’histogramme, nous voyons apparaître dans la barre de formule =SERIE(Dynamic!$B$1;Dynamic!$A$2:$A$11;Dynamic!$B$2:$B$11;1). Rien ne laisse deviner là que nous avons affaire là à un tableau.

Mais pourtant cela marche ! Entrons 11/19 en A12 et nous constatons aussitôt que le tableau s’est agrandi d’une ligne et que le graphe a aussitôt fait de la place pour la nouvelle barre de l’histogramme.

Y a-t-il un problème ?

La question que l’on peut se poser est « Cette solution est-elle toujours meilleure que l’ancienne solution qui consistait à créer deux noms dynamiques et à les utiliser pour modifier la description des X et des Y dans la formule =SERIE(…) ? ». 

Vous aurez la réponse dans le prochain article…

Ventes vs. objectif (b)

J’ai vu récemment un article dans le blog de ma collègue MVP australienne Mynda Treacy dans lequel elle s’intéressait aux graphes incluant aussi un objectif « Idéal ». Voici donc la façon dont on peut intégrer ce second objectif.

On copie une nouvelle colonne avec les objectifs de ce type, on sélectionne le précédent graphe et on fait un collage spécial comme nouvelle série. On obtient alors une nouvelle barre qui masque les deux barres antérieures. Faisons un clic droit sur une de ces nouvelles barres et modifions le type de graphique comme on peut le voir dans la copie d’écran ci-dessous, en affichant la troisième série comme une courbe.

Il ne reste plus qu’à sélectionner cette nouvelle série, et à la mettre en forme en prenant l’option Aucun trait pour la Courbe, et en sélectionnant une marque en trait plein rouge avec une largeur de 1,5. Pour finir, recadrons le titre et la légende de ce graphe.

Et voilà le résultat :

Ventes vs. objectif (a)

Nous nous attaquons aujourd’hui à un type de graphe que l’on rencontre facilement en entreprise. Il s’agit de comparer des chiffres d’affaires par rapport aux objectifs qui avaient été fixés auparavant.

Ce que l’on voit souvent en entreprise, c’est le premier des deux graphes représentés dans la copie d’écran en bas de l'article, avec par exemple le chiffre d’affaires en bleu et l’objectif en vert.

Ce que nous souhaitons obtenir, c’est le second graphe.

Etape 1 : Superposition des séries

La première étape consiste à superpose les séries. Pour cela, faisons un clic droit dans une des barres de la seconde série, prenons la commande Mettre en forme une série de données, et entrons 100% dans Superposition de séries et dans Largeur de l’intervalle puis validons.

Etape 2 : Modification des séries

La seconde étape consiste à sélectionner la série des objectifs et à ôter son remplissage pour ne garder qu’un cadre vert. Puis on sélectionne la série des ventes et on enlève sa bordure.

Et nous obtenons ainsi le résultat voulu. Bien entendu, vous pouvez personnaliser ce graphe à votre façon, avec d’autres couleurs et/ou cadres et éventuellement divers effets graphiques.

Création d’un graphe générique (c)

Créons à présent un troisième onglet et entrons-y les éléments suivants. Dans le bloc A1:D5, nous entrons la description des quatre séries, l’adresse des abscisses, celle des ordonnées et le type de donnée : monnaie (€) ou nombre (0).

Pour la définition de B2, par exemple, nous entrons « = », puis nous allons chercher les abscisses de la première série, nous validons, puis nous remplaçons le « = » de la formule par une apostrophe. Le contenu de la cellule débute alors par deux apostrophes côte à côte… Nous faisons alors de même pour le bloc B2:C5 afin d’identifier les 4 séries de coordonnées en X et en Y. Nous créons alors le bloc G1:K2 en utilisant les formules affichées en commentaire.

En D2, nous définissons une liste de validation par =$A$2:$A$5. On peut ainsi sélectionner la série voulue.

Nous définissons alors Mes_X par =indirect(Chart!$H$2) et Mes_Y par =indirect(Chart!$I$2).

Il ne reste alors plus qu’à rendre le graphe dynamique en sélectionnant la série graphique et – dans la barre de formules – en remplaçant ce qui suit le « ! » dans la formule des X par Mes_X et ce qui suit le « ! » dans la formule des Y par Mes_Y.

Et voilà, le tour est joué !