Pour des courbes impeccables, ajoutez des erreurs !

Courbe Excel trace zero

Vous avez sûrement déjà rencontré le graphique ci-contre. Un indicateur mensuel visualisé à l’aide d’une courbe que l’on voit évoluer au cours de l’année. Malheureusement, pour les mois à venir, Excel trace la ligne à 0. Comment faire en sorte qu’elle soit coupée plutôt que tirer vers le bas ?


 

Dans quel contexte c’est utile ?

Quand on construit un reporting, on cherche à l’automatiser et à le simplifier au maximum. Une fois le format clairement défini, on met en place les tableaux et graphiques pour n’avoir plus que la mise à jour à faire chaque mois, chaque semaine ou autre fréquence d’actualisation.

Pour un tableau de données, généralement, ça ne pose pas de problème.

Dans l’exemple ci-dessous, on calcule la marge mensuelle à partir d’une feuille de données source. Avec des chiffres à fin septembre, on a simplement un zéro affiché dans les trois dernières cellules. C’est peu dérangeant à la lecture et si on veut les masquer, on a déjà vu dans un précédent article comment faire.

Donnees graphiques formules zero

En revanche, pour un graphique, le rendu est moins bon. Les zéros de la feuille de calcul sont interprétés par Excel au même titre que les autres données. Ainsi, la courbe tombe à partir d’octobre.
Et sur une courbe, on ne lit pas chaque point individuellement, c’est d’ailleurs pour cela qu’ils sont reliés entre eux. C’est donc perturbant de présenter un graphique comme celui-ci.

Baisse trace courbe zero

Alors on peut supprimer les formules pour les mois à venir. Le problème est réglé immédiatement sur le graphique.

Cellule vide rupture courbe graphique Excel

Mais on revient au point de départ. A chaque mise à jour du reporting, en plus des nouvelles données, il faut donc rajouter les formules. On perd du temps, on oublie facilement… Trouvons une autre solution.

 

Une astuce pas si intuitive

Masquer la valeur zéro dans la feuille de calcul ne change rien à notre graphique, la valeur n’étant pas modifiée.
On peut être tenté de remplacer cette valeur par un vide. Ci-dessous, la formule a été modifiée pour afficher un vide, représenté par deux guillemets, dès que la valeur est à zéro.
Par simplification, on considère qu’un zéro est synonyme de valeur encore inconnue à ce jour. Pour être précis, on aurait dû placer la condition sur le mois en rajoutant un champ pour le mois de mise à jour.

Texte vide formule courbe

Pourtant, aucun effet sur la courbe. En effet, les doubles guillemets ne sont pas un ‘vrai vide’ comme lorsqu’on supprime la formule. Ici, on affiche un texte vide. Excel la considère donc comme un texte et les textes ont une valeur égale à 0 dans Excel. Pour le graphique, on revient donc sur une valeur nulle. L’opération n’a servi à rien.

Rendu inefficace texte vide courbe

En réalité, on en a déjà parlé, on obtient 0 parce que les données ne sont pas encore disponibles. Dans Excel, une valeur non disponible se matérialise par une erreur, #N/A. Par exemple, on la voit couramment avec une RECHERCHEV sur une valeur non trouvée.

L’idée est donc de donner à Excel une erreur #N/A à la place des zéros pour tracer la courbe. Et pas la peine de chercher une formule complexe pour générer cette erreur, on utilise simplement la fonction NA.
Cette fonction est basique : elle ne prend aucun argument, il suffit d’écrire =NA(), et n’a qu’un seul but, renvoyer une erreur #N/A.

Fonction NA Excel

On peut donc reprendre notre formule avec la condition et remplacer les guillemets par la fonction NA. Cette fois, le résultat dans le graphique est impeccable. La courbe est bien coupée après la dernière valeur disponible.

Erreur NA rupture courbe graphique Excel

Evidemment, l’erreur est inesthétique dans le tableau. Si nous voulons le conserver dans la présentation, on peut créer deux lignes de calcul : une pour le tableau avec des zéros, éventuellement masqués via le format de nombre, et une pour le graphique avec des erreurs. Dans ce cas c’est la ligne entière qu’on masque ou qu’on affiche en dehors.

Maintenant on peut donc préparer nos graphiques et formules à l’avance, sans avoir de souci d’affichage.
Dites-nous en commentaires si vous utilisez déjà cette astuce ou si vous connaissez d’autres combines pour assurer un rendu parfait de vos graphes !

 

Abonnez-vous à la newsletter

Illustration couverture ebook 100 trucs et astuces pour Excel-ler au travail

Et recevez gratuitement l'ebook 100 trucs et astuces pour Excel-ler au travail (5€ sur Amazon)

Votre adresse mail ne sera pas communiquée et vous pourrez vous désabonner à tout moment.

Taggé , , .Mettre en favori le Permaliens.

Une réponse à Pour des courbes impeccables, ajoutez des erreurs !

  1. Charlotte dit :

    Merci ! et Encore merci ! des mois que je cherche à résoudre ce problème !

Laisser un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.