Utiliser la fonction de prévision d'Excel pour convertir les données historiques en prévisions

Microsoft ne peut prédire l'avenir, mais il peut certainement essayer. Les capacités de prévision de séries chronologiques d'Excel prennent les données que vous avez fournies et extrapolent des données futures potentielles. Ce n'est pas parfait, mais cela peut vous donner une idée de ce que vos données vous disent vraiment. Procédure d'analyse élémentaire des données dans Excel Procédure d'analyse élémentaire des données dans Excel Excel n'est pas conçu pour l'analyse de données, mais il peut néanmoins gérer des statistiques . Nous allons vous montrer comment utiliser le complément Data Analysis Toolpak pour générer des statistiques Excel. Lire la suite . Un petit aperçu de l'avenir, si vous voulez.

Il existe deux manières différentes de prévoir dans Excel. Tout d'abord, vous pouvez utiliser plusieurs fonctions de prévision directement dans les cellules de votre feuille de calcul. La deuxième méthode, qui utilise une interface graphique, est plus facile à utiliser, nous allons donc nous concentrer sur cela..

Si vous voulez vraiment savoir comment utiliser les fonctions, vous pouvez consulter le guide de Microsoft sur les fonctions de prévision Excel. Mais nous vous recommandons de vous en tenir au bouton Prévisions.

Comment les données Excel sont-elles prévues??

Avant de commencer, il est important de comprendre ce que vous obtenez lorsque vous utilisez Excel pour prévoir des données..

La fonction de prévision utilise un processus appelé lissage triple exponentiel (ETS) pour prendre vos données existantes et faire des prédictions basées sur les modèles présents. ETS pondère les valeurs précédentes de votre série afin que les valeurs les plus récentes soient plus fortement pondérées et que les valeurs antérieures soient moins pondérées.

L'explication de Gregory Trubetskoy sur le lissage exponentiel est l'une des lectures les plus lisibles par les mathématiciens. C'est un processus compliqué, mais ce qui en découle est que les valeurs récentes de votre série ont un impact important sur les valeurs prédites..

Excel propose un certain nombre d'options pour peaufiner le fonctionnement d'ETS, et nous les passerons en revue dans un instant..

Mise en forme de vos données pour la prévision

Les fonctions de prévision Excel nécessitent que vos données soient dans un format spécifique pour fonctionner correctement. Vous aurez besoin d'au moins deux colonnes.

Le premier est une chronologie. Cela doit contenir des dates, des heures ou une série de chiffres spécifiant un changement temporel. Comment utiliser Excel pour calculer entre dates Comment utiliser Excel pour calculer entre dates Combien de jours y a-t-il entre le 9 janvier 2015 et le 12 juin 2017? Arrêtez de vous battre lorsque Excel peut faire le travail difficile pour vous! Lire la suite .

La chose importante ici est que les intervalles doivent être cohérents. Vous ne pouvez pas avoir de vente à partir du premier janvier, du premier février, du premier mars, du premier avril, du quinze avril et du premier mai. Bien que vous puissiez remplacer ces dates par des chiffres, les prévisions risquent de ne pas être aussi précises si vous les réalisez ainsi..

La deuxième colonne est vos données. C'est ce que vous prévoyez dans le futur.

Pour fournir un exemple de prévision des données, j'ai créé un tableur avec des chiffres de vente mensuels fictifs..

Prévision simple

Pour commencer votre prévision Excel, mettez en surbrillance vos données, puis allez à Données> Feuille de prévision.

Une fois que vous avez cliqué sur le bouton Feuille de prévision, vous verrez un graphique bien formaté contenant vos données en bleu et des données prédictives en orange..

Frappé Créer dans la nouvelle fenêtre, vous obtiendrez une version plus permanente de ce graphique à côté d'un tableau des valeurs prédites. Ce tableau comprend également des intervalles de confiance de 95% (qui sont également représentés sur le graphique)..

C'est déjà très précieux. Mais en ajustant certains paramètres, vous pouvez personnaliser les données que vous obtenez pour des prévisions encore meilleures..

Personnaliser les prévisions Excel

dans le Créer une feuille de prévision Dans cette fenêtre, vous ne voyez qu'un seul menu déroulant par défaut: Fin de prévision.

La date de fin de prévision vous permet de choisir la distance à prévoir dans le futur. Gardez à l'esprit que plus vous allez loin, plus vous risquez de voir des erreurs.

Dans l'image ci-dessus, vous pouvez voir une prévision étendue, l'intervalle de confiance s'étendant avec le temps..

Pour accéder aux autres options de prévision, cliquez sur Les options au bas de la fenêtre.

Nous allons examiner chaque option individuellement.

Début de prévision vous permet de choisir quand Excel commence à prévoir les données. Dans presque tous les cas, vous voudrez que cela soit à partir du tout dernier point de votre chronologie..

Vous voudrez peut-être le définir plus tôt pour voir si les prédictions d'Excel correspondent aux données réelles, juste pour vérifier. Mais en général, vous pouvez ignorer cette option en toute sécurité.

Intervalle de confiance vous permet d'activer ou de désactiver les lignes d'intervalle de confiance et de choisir de le calculer ou non. La plupart du temps, 95% est la valeur standard pour l'intervalle de confiance..

Cela signifie qu'Excel est à 95% confiant que la valeur prévue se situera entre ces deux lignes..

Saisonnalité définit la nature répétitive de votre chronologie. La plupart du temps, Excel calculera cela automatiquement. Dans d'autres cas, vous voudrez le changer vous-même.

Dans notre exemple de feuille de calcul, par exemple, si vous modifiez la saisonnalité sur 4, vous obtenez des données bien meilleures (nous en avons utilisé 4 car il y a quatre points dans chaque cycle de répétition). Voici à quoi ressemblait la prévision avec la détection automatique de la saisonnalité:

Et voici à quoi ça ressemble après avoir changé le caractère saisonnier en 4:

Cela reflète mieux le ralentissement typique des ventes vers octobre. Il est utile d'essayer dans votre propre tableur pour vous assurer d'obtenir les meilleures données possibles..

Assurez-vous simplement que votre numéro de saisonnalité est exact. Si vous utilisez des données mensuelles, la saisonnalité est de 12. Les données hebdomadaires sur une année seront de 52. Si c'est hebdomadaire sur six mois, utilisez 26.

Si vous vérifiez Inclure les statistiques prévisionnelles, Excel vous donnera des statistiques de base supplémentaires dans votre dernière feuille. Voici les statistiques que vous obtiendrez:

Si vous ne savez pas ce que signifient ces statistiques, ne vous en faites pas..

Plage chronologique et Plage de valeurs sont les colonnes où vous avez entré vos données. Vous pouvez les ajuster si Excel récupère les mauvaises données, mais vous n’aurez probablement pas besoin de le faire..

Avant de parler de Remplir les points manquants avec, nous devons parler des données manquantes dans les prévisions. La fonction de prévision fonctionnera même si tous les points de données ne figurent pas dans la chronologie. Même s'il vous manque des informations, Excel continue d'exécuter les prévisions..

La méthode standard pour combler les points manquants est l'interpolation, qui utilise des moyennes pondérées des valeurs voisines du point manquant. C'est une bonne option, d'autant plus que c'est votre seule option.

Si vous ne voulez pas que Excel interpole, vous pouvez choisir Zéros dans le menu déroulant pour insérer des zéros dans les espaces de données manquants. Mais si ces valeurs manquantes ne sont pas susceptibles de constituer des zéros, vous rejetterez vos prévisions..

le Agréger les valeurs en double la liste déroulante concerne un cas spécifique dans vos données. Lorsqu'il existe deux entrées pour un même point dans vos données, Excel les calcule en moyenne pour déterminer la valeur finale. Vous pouvez changer cela en nombre, médiane, somme ou une variété d’autres opérations..

Une fois que vous avez défini toutes les options comme vous le souhaitez, appuyez sur Créer et vous obtiendrez la meilleure estimation d'Excel quant à l'apparence future de vos données, avec un graphique et un tableau plein de valeurs prédites.

Prédire l'avenir avec la fonction de prévision d'Excel

Maintenant que vous savez comment prévoir dans Excel à partir de données historiques, vous pouvez commencer à utiliser le vénérable tableur pour prédire l’avenir! Ou du moins pour avoir une idée de ce à quoi cela pourrait ressembler à partir du passé.

Les prévisions d'Excel ne sont pas parfaites, mais c'est un excellent outil à intégrer dans votre arsenal Excel. Optimisez Excel avec 10 compléments pour traiter, analyser et visualiser des données comme un pro Optimisez Excel avec 10 compléments pour traiter, analyser et visualiser Data Like a Pro Vanilla Excel est incroyable, mais vous pouvez le rendre encore plus puissant avec des compléments. Quelles que soient les données que vous devez traiter, il est probable que quelqu'un a créé une application Excel pour cela. Voici une sélection. Lire la suite . Construire un modèle de prévision dans Excel est simple et vous aidera beaucoup lorsque vous exécutez des statistiques commerciales..

Utilisez-vous les prévisions d'Excel? Quels autres conseils avez-vous pour les nouveaux arrivants? Partagez votre opinion dans les commentaires ci-dessous!

En savoir plus sur: Microsoft Excel.