Comment analyser une série temporelle sur Excel et faire une prévision

Tu ouvres un fichier Excel avec un export de ventes, tu regardes les mois, et tout a l'air propre. Puis quelqu'un trie les libellés par ordre alphabétique, Mars passe devant Janvier, et ton pilotage part dans le décor. C'est exactement le genre de situation où une excel time series mal préparée te fait raconter n'importe quoi au comité de direction, surtout en reporting mensuel, en trésorerie ou en suivi RH.

Dans la pratique, on construit ces analyses chronologiques dans un outil de tous les jours, pas dans un environnement statistique lourd. Le vrai sujet n'est donc pas de “faire un graphique”, mais de fiabiliser la série avant de la lire, puis de la prévisionner sans se faire piéger par les trous, les doublons ou les mauvais axes.

Règle de terrain. Si la colonne des dates n'est pas propre, toute moyenne mobile, toute prévision et même toute lecture visuelle deviennent suspectes.

Tu vas avancer en cinq gestes simples. D'abord nettoyer la structure, ensuite visualiser correctement, puis décomposer la série, tester plusieurs méthodes de prévision, et enfin automatiser le tout pour éviter de refaire le même travail chaque mois. Garde cette logique en tête, parce qu'avec Excel, le problème n'est presque jamais la formule finale, c'est le passage entre les données brutes et la série chronologique exploitable.

Pourquoi tes séries temporelles Excel racontent souvent n'importe quoi

Le piège le plus fréquent, c'est le tableau qui a l'air propre alors qu'il ne l'est pas. Tu vois une colonne avec “Janvier, Février, Mars”, une autre avec les montants, puis quelqu'un trie les mois comme du texte. À l'écran, la série semble correcte. Dans un reporting, elle raconte autre chose, parce qu'Excel ne comprend pas spontanément que les dates doivent être l'axe horizontal et que les valeurs doivent rester sur l'axe vertical, avec un graphique en lignes ou un nuage de points XY selon le niveau de précision recherché, comme le rappellent les guides techniques sur les séries temporelles dans Excel carleton.edu.

Le mauvais réflexe qui fausse tout

Imagine un suivi de trésorerie où “octobre” semble creuser plus fort que “décembre”. Le problème ne vient pas de l'entreprise, mais du tri. Une série temporelle n'est fiable que si les données sont strictement chronologiques, avec une colonne de dates ou d'horodatages, une colonne de valeurs, puis un tri explicite par date avant tout calcul.

Un tableau chronologique n'est pas un tableau classé. C'est une suite ordonnée dans le temps, pas une liste alphabétique.

Le bon flux mental

Le flux tient en cinq étapes. Tu vérifies d'abord la qualité du fichier, tu mets la série en ordre, tu traces le brut, tu lisses ou tu prévois, puis tu mesures l'erreur. Ce séquençage évite l'erreur classique, celle qui consiste à lancer directement une courbe de tendance sur un export CRM ou ERP hétérogène, alors que les dates sont mélangées, incomplètes ou irrégulières.

Dans la pratique métier, la granularité mensuelle ou trimestrielle reste souvent la plus lisible en finance et en pilotage, parce qu'elle capte les cycles sans noyer le modèle. Le vrai sujet n'est pas l'effet visuel, c'est la qualité du calendrier. Une date non contiguë, un trou mal traité ou un horodatage saisi en texte peuvent dégrader la lecture des tendances et fausser la logique de prévision automatique.

Mini-scénario finance

Tu veux expliquer à ton DAF pourquoi la marge paraît se dégrader. Si les mois sont mal ordonnés, si les valeurs manquent sur un trimestre, ou si une saisie a été faite en texte au lieu d'une vraie date, ton graphique peut suggérer une rupture qui n'existe pas. Avant de parler de tendance, il faut donc prouver que la série est chronologique, sinon la conclusion est fragile par construction.

Une autre source de dérive, plus sournoise, vient des erreurs de saisie. Un libellé mal orthographié, une date inversée, un montant collé dans la mauvaise colonne, et toute l'analyse part de travers.

Préparer et nettoyer tes données sans rien casser

La première vraie compétence, c'est de transformer un export sale en tableau exploitable sans modifier les faits. En pratique, pars toujours d'une structure à deux colonnes, une colonne date et une colonne valeur, puis vérifie que la date est une vraie date Excel, pas du texte déguisé. Ensuite seulement, trie par date, parce qu'une moyenne mobile calculée sur une série désordonnée produit une lecture trompeuse.

Structurer proprement le fichier

Si ton export CRM mélange plusieurs périodes, garde une table brute intacte et crée une zone de travail à part. Dans cette zone, tu peux utiliser une colonne de dates normalisées, puis une colonne de valeurs nettoyées. C'est le bon endroit pour appliquer TRIER sur la date, pas sur un libellé métier.

Exemple simple, si tes dates sont en A2:A100 et les montants en B2:B100, tu peux créer une version triée dans une plage de travail avec une formule de ce type, selon ta version d'Excel.
=TRIER(A2:B100;1;1)

Gérer les trous sans trafiquer la réalité

Quand il manque un mois, ne le masque pas. Crée d'abord la série complète des périodes attendues, puis aligne les valeurs dessus. Si tu as des cellules vides, deux logiques reviennent souvent, l'interpolation linéaire quand tu veux une transition douce, ou le report de la dernière valeur connue quand le phénomène métier le justifie. Si tu travailles sur des exports hétérogènes, la logique consiste à reconstituer le calendrier, pas à supprimer les absences.

Pour les vides, SI.NON.DISP peut t'aider à sécuriser l'affichage quand une recherche ne trouve rien. Exemple de base.
=SI.NON.DISP(RECHERCHEX(A2;PlageDates;PlageValeurs);"")

Harmoniser les fréquences

Le vrai sujet dans un fichier RH ou finance, c'est souvent le mélange mensuel, hebdomadaire et parfois quotidien. Tu dois choisir une fréquence cible, puis ramener tout le monde au même pas de temps. Si le reporting est mensuel, chaque donnée hebdomadaire doit être agrégée ou réconciliée proprement, sinon tu compares des unités qui ne vivent pas au même rythme.

Astuce terrain. Trace toujours un graphique brut avant de remplir les trous. Un point isolé ou un saut anormal se voit plus vite à l'œil qu'en cellule.

Pour approfondir la logique de préparation des flux, la lecture d'un fichier à plusieurs sources gagne à être centralisée, et comment utiliser Power Query est souvent la première brique à mettre en place quand tu veux arrêter de faire le nettoyage à la main.

Visualiser et décomposer la série comme un pro

Le choix du graphique change la manière dont tu lis le temps. Un graphique en lignes paraît naturel, mais un nuage de points XY respecte mieux les écarts réels entre dates, surtout quand les intervalles sont irréguliers. Le XY est souvent plus fiable pour un véritable axe temporel, tandis que la ligne reste commode pour des dates régulières.

Choisir le bon visuel

Critère	Graphique en lignes	Nuage de points XY
Lecture rapide	Très bon	Bon
Respect des intervalles irréguliers	Moyen	Très bon
Série avec dates espacées de façon inégale	Risqué	Adapté
Usage métier courant	Reporting simple	Analyse plus précise

Le graphique en lignes marche bien quand la série est régulière et que tu veux montrer une évolution lisible. Le XY devient préférable dès qu'un mois manque, qu'un fichier contient des horodatages, ou qu'un export mélange plusieurs rythmes. Pour des données temporelles exigeantes, c'est lui qui protège le mieux la logique des écarts.

Décomposer sans sortir d'Excel

Tu peux construire une décomposition simple avec quatre colonnes, pour la tendance, l'écart, la saisonnalité et le résidu. Pour la tendance, une moyenne mobile centrée fait souvent le travail. Pour la saisonnalité, tu calcules l'écart entre la valeur observée et la tendance, puis tu fais la moyenne de ces écarts mois par mois. Pour le résidu, tu soustrais la tendance et la saisonnalité à la valeur observée.

Un schéma pratique ressemble à ça.
Tendance = MOYENNE(valeurs sur la fenêtre choisie)
Écart = valeur observée - tendance
Saisonnalité = MOYENNE.SI(colonne des mois;mois de la ligne;colonne des écarts)
Résidu = valeur observée - tendance - saisonnalité

Dans un fichier de suivi de ventes, cette lecture t'aide à séparer ce qui relève du fond de ce qui relève du cycle. Si décembre monte chaque année, ce n'est pas une rupture. Si la tendance remonte malgré un mois creux, tu tiens peut-être un signal utile. Pour aller plus loin sur le lissage, la logique de la moyenne mobile reste une base solide avant toute extrapolation.

Petit repère métier

Le bon visuel n'est pas celui qui “fait joli”. C'est celui qui respecte le rythme des données. Si ton graphe cache les intervalles, ta décision peut être bonne mais mal justifiée. Si au contraire le visuel révèle les écarts réels, tu peux expliquer une anomalie au lieu de la subir.

Méthodes de prévision qui marchent vraiment dans Excel

Une prévision utile n'a pas besoin d'être sophistiquée. Elle doit rester lisible, recalculable et défendable face à un DAF ou à un contrôleur de gestion. Dans Excel, tu reviens presque toujours aux mêmes familles de méthodes, la moyenne mobile, la droite de tendance linéaire, puis PREVISION.LINEAIRE et PREVISION.ETS (FORECAST.LINEAR et FORECAST.ETS dans Excel en anglais) quand la série demande un cran de plus.

Commencer par la moyenne mobile

La moyenne mobile sert à lisser le bruit sans faire disparaître le signal. Si ta série est mensuelle et saisonnière, une fenêtre de 12 a souvent du sens, à condition d'avoir assez d'historique pour qu'elle raconte quelque chose. Si le cycle est plus court, une fenêtre de 3 peut déjà aider. L'erreur classique, c'est d'appliquer un lissage de 12 sur une série trop courte, puis de croire que le résultat est précis parce qu'il est propre visuellement.

Formule simple sur une fenêtre de trois points.
=MOYENNE(B2:B4)

Sur un suivi de trésorerie, cette méthode fait ressortir la tendance sous les à-coups de fin de mois. Sur un effectif, elle évite de surinterpréter un mois de recrutement ou un pic de départs.

Lire la droite de tendance

Sur un graphique, tu peux ajouter une droite de tendance linéaire, puis afficher l'équation et le R². L'équation te donne la forme de la tendance, et le R² t'aide à juger si la droite suit vraiment la série ou si elle force le trait. Le réglage se fait vite, clic droit sur la série, ajout de la tendance, puis affichage des deux indicateurs.

Cette méthode est pratique quand la trajectoire est assez régulière pour être résumée par une pente. Elle devient moins convaincante dès que la saisonnalité prend de la place ou que les ruptures sont fréquentes.

Utiliser PREVISION.LINEAIRE et PREVISION.ETS

PREVISION.LINEAIRE convient quand la tendance reste simple et que tu veux une projection rapide à expliquer. PREVISION.ETS devient plus utile quand la série mélange tendance et saisonnalité, à condition d'avoir une chronologie propre et un pas de temps cohérent. Dans Excel, la logique passe souvent par la Feuille de prévision de l'onglet Données, qui s'appuie sur une paire temps, valeur et affiche une sortie graphique immédiate.

Formules prêtes à coller.
=PREVISION.LINEAIRE(DATE(2026;1;1);B2:B24;A2:A24)
=PREVISION.ETS(DATE(2026;1;1);B2:B24;A2:A24)

Le point à surveiller, c'est l'argument temps. Il ne doit pas servir de décor, il doit porter la série. Sur une prévision d'effectif mensuel, la date cible et la plage chronologique doivent rester alignées, sinon Excel produit une réponse propre en apparence mais bancale sur le fond.

Si tes dates sont irrégulières ou mélangées, la prévision automatique peut sortir un graphique propre et une courbe rassurante, tout en donnant une interprétation trompeuse.

La fiche de la fonction PREVISION.ETS aide à mettre cette logique en place sans repartir de zéro à chaque mise à jour.

Mesurer les erreurs et comparer tes modèles

Une prévision sans mesure d'erreur, c'est une opinion. Si tu veux parler sérieusement à un DAF, tu dois comparer les modèles sur les mêmes périodes et regarder ce qu'ils ratent, pas seulement ce qu'ils affichent. Les trois métriques les plus pratiques restent MAPE, RMSE et MAE, parce qu'elles te donnent trois lectures différentes de l'écart entre réel et prévu.

Lire les métriques sans te perdre

Le MAPE te parle en pourcentage, donc il est très lisible en réunion. Le RMSE pénalise davantage les gros écarts, ce qui est utile quand une erreur ponctuelle coûte cher. Le MAE te donne une lecture moyenne en valeur absolue, souvent plus stable et plus facile à expliquer qu'un indicateur trop sensible.

Tu peux poser les trois en Excel avec des formules simples.
MAPE = SOMMEPROD(ABS((Réel-Prévu)/Réel))/NB(Réel)
RMSE = RACINE(SOMMEPROD((Réel-Prévu)^2)/NB(Réel))
MAE = SOMMEPROD(ABS(Réel-Prévu))/NB(Réel)

Comparer plusieurs modèles sur le même historique

Le bon réflexe consiste à réserver les 6 derniers mois à la comparaison des modèles, puis à calculer l'erreur de chaque méthode sur cette même fenêtre. Tu peux aligner une moyenne mobile, une prévision linéaire et une prévision ETS dans un petit tableau, puis calculer leur MAPE côte à côte. Ce tableau devient ton arbitre, pas ton intuition.

Décider avec du concret

Dans les fonctions support, un modèle un peu moins élégant mais plus stable vaut souvent mieux qu'un modèle sophistiqué qui se trompe sur les ruptures. L'objectif n'est pas d'annoncer une précision magique, c'est de repérer le modèle qui se comporte le mieux sur ta série réelle. Si ton modèle ETS colle mieux à la saisonnalité, garde-le. Si la moyenne mobile est plus solide sur une petite série, assume-la.

Bon réflexe. Compare toujours les modèles sur une période de contrôle commune. Sinon, tu ne compares pas des méthodes, tu compares des contextes différents.

Pour le suivi de prévision dans Excel, une aide comme la formation Excel du Dojo peut accélérer la prise en main des formules et des tableaux de contrôle, surtout si tu dois standardiser le même test d'un mois sur l'autre.

Automatiser le pipeline avec Power Query et une macro VBA

Quand tu refais le même fichier tous les mois, le vrai gain n'est pas d'aller plus vite à la souris. Le gain, c'est d'enlever la saisie manuelle. Power Query est ta meilleure base pour consolider plusieurs exports CSV ou Excel dans une seule table chronologique, puis réappliquer le nettoyage de façon stable. Si les sources changent de fréquence ou de format, tu centralises la logique au lieu de la réécrire partout.

Construire une table unique

Dans Power Query, tu importes les fichiers, tu harmonises les colonnes, tu gardes la colonne date au bon type, puis tu charges le résultat dans Excel. Si une cellule vide doit reprendre la valeur du dessus, le remplir vers le bas règle souvent le problème sans manipulations répétitives. Le but n'est pas de tout faire dans une seule étape, mais de rendre la chaîne reproductible.

Rafraîchir et publier avec VBA

Une petite macro VBA peut ensuite rafraîchir la feuille de prévision et copier le résultat dans un onglet “Reporting”. Tu n'as pas besoin d'une usine à gaz. Une macro courte suffit souvent à mettre à jour les requêtes, recalculer la feuille, puis figer la sortie pour diffusion.

Exemple de squelette simple.

Sub MajReporting()
ThisWorkbook.RefreshAll
Application.CalculateFull
Sheets("Prévision").Range("A1:H50").Copy
Sheets("Reporting").Range("A1").PasteSpecial xlPasteValues
End Sub

Le bénéfice est surtout opérationnel. Tu passes d'un rituel manuel à un flux stable, et tu limites les risques d'oubli. Pour aller plus loin sur Power Query ou VBA dans un cadre structuré, Le Dojo Club peut servir d'espace d'apprentissage continu pour pratiquer les cas de consolidation et d'automatisation, sans repartir de zéro à chaque fois.

En résumé, FAQ et astuce bonus du coach

En résumé

• Trie toujours par date, pas par libellé.
• Trace le brut avant de lisser.
• Choisis le bon axe, surtout si les intervalles sont irréguliers.
• Compare plusieurs modèles sur la même fenêtre.
• Vérifie l'erreur, pas seulement le joli graphique.
• Automatise le rafraîchissement dès que le fichier revient chaque mois.

Astuce bonus du terrain

Utilise un tableau structuré avec Ctrl+L comme source du graphique et de la prévision. Quand de nouvelles lignes arrivent, la plage s'étend toute seule, le filtre reste propre et les calculs suivent sans recopie manuelle. C'est simple, mais ça évite beaucoup d'accidents silencieux.

FAQ

Comment gérer les jours fériés dans une série quotidienne ?
Tu gardes le calendrier complet, puis tu traites les jours non ouvrés à part. Dans un tableau de caisse ou de production, ça évite de confondre absence d'activité et absence de données.

Comment détecter un changement de régime sans outil statistique dédié ?
Commence par comparer visuellement la tendance avant et après l'événement, puis regarde si l'écart persiste sur plusieurs périodes. Si la rupture est nette dans le tableau brut et dans une moyenne mobile, tu as déjà un signal exploitable.

Pourquoi Power BI reste parfois supérieur ?
Parce qu'il gère mieux les modèles plus riches, les interactions visuelles et les volumes plus importants. Excel reste excellent pour le pilotage de proximité, mais au-delà d'un certain niveau de complexité, Power BI structure mieux la restitution.