Excel : Construire des courbes de tendance

Cette semaine, j’ai reçu la question suivante d’un lecteur, qui se questionnait comment construire une telle courbe de tendance dans un graphique Excel :

J’ai un tableau classique: 3 colonnes (mois, données 2013, données 2014). J’ai construit un graphique de type “histogramme”, qui compare les données par mois de chaque année. A ce graphique, j’aimerais ajouter une courbe pour chacune des années, donnant la tendance (mois1=mois1/2; mois2=(mois1+mois2)/4;  mois3=(mois1+mois2+mois3)/6, etc.), soit la moyenne cumulée à 50%.

Le présent article vise donc à répondre à cette question.

Étape 1: Construire le tableau de données

Dans un premier temps, il faut construire notre tableau de données. Celui-ci doit donc comprendre 5 colonnes: les mois, les données de 2013, les tendances calculées pour 2013, les données de 2014 et les tendances calculées pour 2014. Pour calculer les tendances, tel que demandé par mon lecteur, j’ai utilisé la fonction Somme (Sum en anglais) et la fonction Equiv (Match en anglais), tel qu’illustré sur l’image ci-bas. La première partie de la formule fait la somme des données jusqu’où nous sommes rendus et divise ensuite le tout selon la position de la ligne où nous sommes rendus dans le tableau et multiplie le tout par 2 (c’est ce qui permet de calculer les moyennes cumulées demandées et de les diviser par 2).

Bien sûr, nous aurions pu utiliser simplement la fonction MOYENNE (ou AVERAGE en anglais) et diviser ensuite le tout par deux. Par contre, nous avons préféré insérer la fonction ci-haut, juste au cas où certains d’entre vous cherchaient de l’inspiration pour calculer d’autres types de courbes de tendance, comme une moyenne mobile par exemple. Dans ce contexte, il est plus facile de déterminer un calcul pour le numérateur et un calcul pour le dénominateur.

Étape 2: Tracer le graphique

Ensuite, nous devons tracer un graphique en colonnes (un histogramme groupé).Éta

Étape 3: Ajouter les courbes de tendance

Pour distinguer les courbes de tendance des données, nous allons changer de type de graphique pour les deux séries de données qui représentent les tendances, 2013 et 2014. Pour ce faire, nous allons aller dans le menu Création (Design en anglais) et nous allons cliquer sur Changer le type de graphique (Change Chart Type en anglais).

Nous allons choisir les graphiques en lignes pour les deux courbes de tendance.

Nous allons obtenir le graphique suivant:

Vous devez consolider des données, les analyser, les interpréter et présenter des conclusions utiles pour des fins de prise de décision ? Apprenez à développer des solutions robustes et automatisées avec nos formations en Tableaux de bord avec Excel.

Étape 4 : Embellir le graphique

Finalement, nous allons mettre le graphique un peu plus à notre goût en modifiant notamment le titre, la légende et le fond.

Vous aussi vous avez des questions?

Si vous avez vous aussi des questions, nous vous invitons à les poser dans l’un ou l’autre de nos forums.

Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

Formation complémentaire

Pour une introduction à l’élaboration d’un tableau de bord dans Excel, et ainsi apprendre à alimenter, structurer et présenter vos données, suivez notre formation Excel – Tableaux de bord (niveau 1).

Voici quelques commentaires d’apprenants ayant suivi la formation Excel – Tableaux de bord (niveau 1) :

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.