Dans Microsoft Excel, plusieurs fonctions peuvent vous aider à créer des prévisions de lissage linéaires et exponentielles basées sur des données historiques telles que les ventes, les budgets, les flux de trésorerie, les cours des actions, etc.

Cette section se concentrera principalement sur les deux principales fonctions de prévision, mais nous aborderons également brièvement d’autres fonctions pour vous aider à comprendre leur objectif et leurs utilisations de base.

1 Les types de fonctions de prévision

Dans les versions récentes d’Excel, il existe six fonctions de prévision différentes.

Les deux fonctions font des prévisions linéaires :

■ PRÉVISION – prédit les valeurs futures en utilisant la régression linéaire ; une fonction héritée pour la rétrocompatibilité avec Excel 2013 et versions antérieures.

■ PREVISION.LINEAIRE – identique à la fonction PREVISION; partie de la nouvelle suite de fonctions de prévision dans Excel 2016 et Excel 2019.

Les quatre fonctions ETS sont destinées aux prévisions de lissage exponentiel. Ces fonctions sont uniquement disponibles dans Excel pour Office 365, Excel 2019 et Excel 2016.

■ PREVISION.ETS – prédit les valeurs futures sur la base de l’algorithme de lissage exponentiel.

■ PREVISION.ETS.CONFINT – calcule l’intervalle de confiance.

■ PREVISION.ETS.CARACTERESAISONNIER – calcule la durée d’un motif récurrent saisonnier ou autre.

■ PREVISION.ETS.STAT – renvoie des valeurs statistiques pour la prévision de séries chronologiques.

2 Fonction PRÉVISION

La fonction PREVISION dans Excel est utilisée pour prédire une valeur future en utilisant la régression linéaire. En d’autres termes, la fonction PREVISION projette une valeur future le long d’une ligne de meilleur ajustement basée sur des données historiques.

La syntaxe de la fonction PREVISION est la suivante :

PREVISION(x ; y_connus ; x_connus)

Où:

■ x (obligatoire) – une valeur x numérique pour laquelle vous souhaitez prédire une nouvelle valeur y.

■ y_connus  (requis) – un tableau de valeurs y dépendantes connues.

■ x_connus (obligatoire) – un tableau de valeurs x indépendantes connues.

La fonction PREVISION fonctionne dans toutes les versions d’Excel pour Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP et Excel 2000.

Dans Excel 2016 et 2019, cette fonction a été remplacée par la fonction PREVISION.LINEAIRE, mais est toujours disponible pour la compatibilité descendante.

3 Fonction Excel PREVISION.LINEAIRE

La fonction PREVISION.LINEAIRE est l’équivalent moderne de la fonction PREVISION. Il a le même but et la même syntaxe :

PREVISION.LINEAR(x ; y_connus ; x_connus)

Cette fonction est disponible dans Excel pour Office 365, Excel 2019 et Excel 2016.

Les deux fonctions PREVISION et PREVISION.LINEAIRE calculent une valeur future y en utilisant l’équation de régression linéaire :

y = a + bx

Où la constante (ordonnée à l’origine) est :

Et le coefficient b (pente de la droite) est :

Les valeurs de x̄ et ȳ sont les moyennes d’échantillon (moyennes) des valeurs x et y connues.

Si votre formule PREVISION.LINEAIRE renvoie une erreur, cela est probablement dû aux raisons suivantes :

1. Si les plages des x_connus et des y_connus sont de longueurs différentes ou vides, l’erreur #N/A! se produit.
2. Si la valeur x n’est pas numérique, la formule renvoie l’erreur #VALEUR !.
3. Si la variance des x_connus est nulle, l’erreur #DIV/0! se produit.

4 Comment utiliser la fonction PREVISION – exemple de formule

Comme déjà mentionné, les fonctions Excel PREVISION et PREVISION.LINEAIRE sont destinées à la prévision de tendance linéaire. Ils fonctionnent mieux pour les ensembles de données linéaires et dans les situations où vous souhaitez prévoir une tendance générale en ignorant les fluctuations de données insignifiantes.

A titre d’exemple, nous essaierons de prédire le trafic d’un site Web pour les 7 prochains jours sur la base des données des 3 semaines précédentes.

Avec les valeurs y connues (nombre de visiteurs) dans B2 : B22 et les valeurs x connues (dates) dans A2 : A22, la formule de prévision est la suivante.

Excel 2019 – Excel 2000:

= PREVISION(A23; $B$2:$B$22; $A$2:$A$22)

Excel 2016 et Excel 2019:

= PREVISION.LINEAIRE (A23; $B$2:$B$22; $A$2:$A$22)

Où A23 est une nouvelle valeur x pour laquelle vous souhaitez prédire une future valeur y.

Selon votre version d’Excel, insérez l’une des formules ci-dessus dans n’importe quelle cellule vide de la ligne 23, copiez-la dans autant de cellules que nécessaire et vous obtiendrez ce résultat :

Veuillez faire attention à ce que nous verrouillions les plages avec des références de cellules absolues (comme $A$2:$A$2) pour les empêcher de changer lors de la copie de la formule dans d’autres cellules.

Tracée sur un graphique, notre prévision linéaire se présente comme suit :

Si vous souhaitez prédire les valeurs futures en fonction du modèle récurrent observé dans vos données historiques, utilisez PREVISION.ETS au lieu de la fonction Excel PREVISION.LINEAIRE. Et la section suivante de notre tutoriel montre comment faire cela.

5 Fonction PREVISION.ETS

La fonction PREVISION.LINEAIRE est utilisée pour faire des prévisions de lissage exponentiel basées sur une série de valeurs existantes.

Plus précisément, il prédit une valeur future basée sur la version AAA de l’ algorithme Exponential Triple Smoothing (ETS), d’où le nom de la fonction. Cet algorithme lisse les écarts insignifiants dans les tendances des données en détectant les modèles de saisonnalité et les intervalles de confiance. “AAA” signifie erreur additive, tendance additive et saisonnalité additive.

La fonction PREVISION.ETS est disponible dans Excel pour Office 365, Excel 2019 et Excel 2016.

= PREVISION.ETS(date_cible ; valeurs ; chronologie ; [caractère_saisonnier] ; [saisie_semiautomatique_données] ; [agrégation])

La syntaxe de la fonction PREVISION.ETS contient les arguments suivants :

■ date_cible Obligatoire. Représente le point de données dont vous voulez prévoir la valeur. La date cible peut être date/heure ou numérique. Si la date cible est chronologiquement avant la fin de la chronologie historique, la prévision. ETS renvoie la #NUM! peut avoir les autres causes suivantes.

■ Valeurs Obligatoire. Valeurs historiques pour lesquelles vous voulez prévoir les points suivants.

■ Chronologie Obligatoire. Matrice indépendante ou plage de données numériques. Les dates de la chronologie doivent avoir un incrément constant entre elles, celui-ci ne pouvant pas être zéro. La chronologie n’est pas nécessaire pour être triée, comme la prévision. ETS le trie implicitement pour les calculs. Si aucun insérez constant ne peut être identifié dans la chronologie fournie, la fonction PREVISION.ETS retourne la #NUM! erreur. Si la chronologie contient des valeurs en double, la prévision. ETS vous #VALUE ! erreur. Si les plages de la chronologie et les valeurs ne sont pas de la même taille, la prévision. ETS retourne l’#N/A.

■ Caractère saisonnier Facultatif. Une valeur numérique. La valeur par défaut 1 signifie qu’Excel détecte le caractère saisonnier automatiquement pour la prévision et utilise des nombres entiers positifs pour la durée du modèle saisonnier. 0 indique l’absence de caractère saisonnier, ce qui signifie que la prévision sera linéaire. Des nombres entiers positifs indiquent à l’algorithme d’utiliser des modèles de cette durée comme caractère saisonnier. Pour toutes les autres valeurs, prévision. ETS vous #NUM ! erreur.

Le caractère saisonnier maximal pris en charge est 8 760 (nombre d’heures dans une année). Tout caractère saisonnier supérieur à ce nombre entraîne l’erreur #NOMBRE!.

■ Saisie semi-automatique des données Facultatif. Bien que la chronologie nécessite un in step constant entre les points de données, la prévision. ETS prend en charge jusqu’à 30 % de données manquantes et effectue un ajustement automatique pour en prendre en charge. 0 indique à l’algorithme de prendre en compte les points manquants en tant que zéros. La valeur par défaut de 1 rend compte des points manquants en les remplaçant par la moyenne des points adjacents.

■ Agrégation Facultatif. Bien que la chronologie nécessite un in step constant entre les points de données, la prévision. ETS regroupe les points dont l’heure est identique. Le paramètre d’agrégation est une valeur numérique indiquant la méthode utilisée pour regrouper plusieurs valeurs avec les mêmes date et heure. La valeur par défaut de 0 utilise la MOYENNE. Les autres options sont SOMME, NB, NBVAL, MIN, MAX et MEDIANE.

6 Cinq choses que vous devez savoir sur PREVISION.ETS

1. Pour le bon fonctionnement de la fonction PREVISION.ETS, la chronologie doit avoir un intervalle régulier- horaire, quotidien, mensuel, trimestriel, annuel, etc.
2. La fonction est mieux adaptée aux ensembles de données non linéaires avec un modèle saisonnier ou répétitif.
3. Lorsqu’Excel ne peut pas détecter un modèle, la fonction revient à une prévision linéaire.
4. La fonction peut fonctionner avec des ensembles de données incompletsoù jusqu’à 30 % de points de données sont manquants. Les points manquants sont traités en fonction de la valeur de l’argument de complétion des données.
5. Bien qu’une chronologie avec une étape cohérente soit requise, il peut y avoir des doublonsdans la série date/heure. Les valeurs avec le même horodatage sont agrégées comme défini par l’argument d’agrégation.

La fonction PREVISION.ETS ne fonctionne pas :

Si votre formule génère une erreur, il s’agit probablement de l’une des suivantes :

1. L’erreur #N/A se produit si les valeurs et les tableaux de chronologie ont une longueur différente.
2. L’erreur VALEUR!  est renvoyée si l’argument de saisonnalité , d’achèvement des données ou d’ agrégation n’est pas numérique.
3. L’erreur #NUM !  peut être générée pour les raisons suivantes :

• Une taille de pas cohérente ne peut pas être détectée dans la chronologie.
• La valeur de saisonnalité est en dehors de la plage prise en charge (0 – 8,7600).
• La valeur d’achèvement des données est différente de 0 ou 1.
• La valeur d’agrégation est en dehors de la plage valide (1 – 7).

7 Comment utiliser la fonction PREVISION.ETS – exemple de formule

Pour voir en quoi les valeurs futures calculées avec un lissage exponentiel sont différentes d’une prévision de régression linéaire, créons une formule PREVISION.ETS pour le même ensemble de données que celui que nous avons utilisé dans l’exemple précédent :

=PRÉVISION.ETS(A23 ; $B$2 :$B$22 ; $A$2 :$A$22)

Où:

• A23 est la date cible
• $B$2 :$B$22 sont les données historiques ( valeurs )
• $A$2 :$A$22 sont les dates ( chronologie )

En omettant les trois derniers arguments ( saisonnalité , achèvement des données ou agrégation ), nous nous appuyons sur les valeurs par défaut d’Excel. Et Excel prévoit parfaitement la tendance :

8 Fonction PREVISION.ETS.CONFINT

La fonction PREVISION.ETS.CONFINT est utilisée pour calculer l’intervalle de confiance pour une valeur prévue.

L’intervalle de confiance est en quelque sorte une mesure de la précision de la prédiction. Plus l’intervalle est petit, plus la prédiction est fiable pour un point de données spécifique.

La fonction PREVISION.ETS.CONFINT est disponible dans Excel pour Office 365, Excel 2019 et Excel 2016.

La fonction a les arguments suivants :

= PREVISION.ETS.CONFINT (date_cible ; valeurs ; chronologie ; [niveau_confiance] ; [saisonnalité] ; [achèvement des données] ; [agrégation])

Comme vous le voyez, la syntaxe de PREVISION.ETS.CONFINT est très similaire à celle de la fonction PREVISION.ETS, à l’exception de cet argument supplémentaire :

Niveau_confidence (facultatif) – un nombre compris entre 0 et 1 qui spécifie un niveau de confiance pour l’intervalle calculé. En règle générale, il est fourni sous forme de nombre décimal, bien que les pourcentages soient également acceptés. Par exemple, pour définir un niveau de confiance de 90 %, vous entrez 0,9 ou 90 %.

■ Si elle est omise, la valeur par défaut de 95 % est utilisée, ce qui signifie que 95 % du temps, un point de données prévu devrait se situer dans ce rayon par rapport à la valeur renvoyée par PREVISION.ETS.

■ Si le niveau de confiance est en dehors de la plage prise en charge (0 – 1), la formule renvoie le #NUM ! Erreur.

9 Exemple de formule PREVISION.ETS.CONFINT

Pour voir comment cela fonctionne dans la pratique, calculons l’intervalle de confiance pour notre exemple d’ensemble de données :

= PREVISION.ETS.CONFINT (A23; $B$2:$B$22; $A$2:$A$22)

Où:

• A23 est la date cible
• $B$2 :$B$22 sont les données historiques
• $A$2 :$A$22 sont les dates

Les 4 derniers arguments sont omis, indiquant à Excel d’utiliser les options par défaut :

• Réglez le niveau de confiance sur 95 %.
• Détecter automatiquement la saisonnalité.
• Compléter les points manquants comme la moyenne des points voisins.
• Agrégez plusieurs valeurs de données avec le même horodatage à l’aide de la fonction MOYENNE.

Pour comprendre ce que signifient réellement les valeurs renvoyées, veuillez consulter la capture d’écran ci-dessous (certaines lignes avec des données historiques sont masquées pour des raisons d’espace).

La formule dans D23 donne le résultat 6441,22 (arrondi à 2 décimales). Cela signifie que 95 % du temps, la prévision pour le 11 mars devrait se situer à moins de 6 441,22 de la valeur prévue de 61 075 (C3). C’est 61 075 ± 6441,22.

 Pour connaître la plage dans laquelle les valeurs prévues sont susceptibles de se situer, vous pouvez calculer les limites de l’intervalle de confiance pour chaque point de données.

Pour obtenir la limite inférieure, soustrayez l’intervalle de confiance de la valeur prévue :

=C23-D23

Pour obtenir la limite supérieure, ajoutez l’intervalle de confiance à la valeur prévue :

=C23+D23

Où C23 est la valeur prédite renvoyée par PREVISION.ETS et D23 est l’intervalle de confiance renvoyé par PREVISION.ETS.CONFINT.

Copiez les formules ci-dessus, tracez les résultats sur un graphique et vous aurez une représentation visuelle claire des valeurs prédites et de l’intervalle de confiance :

Fonction Excel PREVISION.ETS.CARACTERESAISONNIER

La fonction PREVISION.ETS.CONFINT est utilisée pour calculer la longueur d’un modèle récurrent dans la chronologie spécifiée. Il est étroitement lié à PREVISION.ETS car les deux fonctions utilisent le même algorithme pour détecter la saisonnalité.

Cette fonction est disponible dans Excel pour Office 365, Excel 2019 et Excel 2016.

La syntaxe de PREVISION.ETS.CARACTERESAISONNIER est la suivante :

= PREVISION.ETS.CARACTERESAISONNIER (valeurs, chronologie, [data_completion], [aggregation])

Pour notre ensemble de données, la formule prend la forme suivante :

= PREVISION.ETS.CARACTERESAISONNIER (B2:B22; A2:A22)

Et renvoie la saisonnalité 7, qui est parfaitement en accord avec le schéma hebdomadaire de nos données historiques :

10 Fonction PREVISION.ETS.STAT

La fonction PREVISION.ETS.STAT renvoie une valeur statistique spécifiée relative à une prévision de lissage exponentiel de série temporelle.

Comme les autres fonctions ETS, il est disponible dans Excel pour Office 365, Excel 2019 et Excel 2016.

La fonction a la syntaxe suivante :

PREVISION.ETS.STAT(valeurs, chronologie, type_statistique, [caractère_saisonnier], [saisie_semiautomatique_données], [agrégation])

L’ argument type_statistique indique la valeur statistique à renvoyer :

■ Paramètre alpha de l’algorithme ETS    Renvoie le paramètre de valeur de base: une valeur plus élevée donne plus de poids aux points de données récents.

■ Paramètre bêta de l’algorithme ETS    Renvoie le paramètre de valeur de tendance: une valeur plus élevée donne plus de poids à la tendance récente.

■ Paramètre gamma de l’algorithme ETS    Renvoie le paramètre de valeur de caractère saisonnier : une valeur plus élevée donne plus de poids à la période saisonnière récente.

■ Métrique MASE    Renvoie la valeur d’erreur moyenne absolue mise à l’échelle, une mesure de la précision des prévisions.

■ Métrique SMAPE    Renvoie la valeur d’erreur de pourcentage moyenne absolue symétrique, une mesure de la précision basée sur les erreurs de pourcentage.

■ Métrique MAE    Renvoie la valeur d’erreur de pourcentage moyenne absolue symétrique, une mesure de la précision basée sur les erreurs de pourcentage.

■ Métrique RMSE    Renvoie la valeur d’erreur du carré moyen racine, une mesure des différences entre les valeurs prévues et les valeurs observées.

■ Taille d’incrément détecté    Renvoie la taille de l’incrément détecté dans la chronologie historique.

Par exemple, pour renvoyer le paramètre Alpha pour notre exemple d’ensemble de données, nous utilisons cette formule :

= PREVISION.ETS.STAT (B2:B22; A2:A22; 1)

La capture d’écran ci-dessous montre les formules pour d’autres valeurs statistiques :

C’est ainsi que vous faites des prévisions de séries chronologiques dans Excel.