Parmi les différentes méthodes de prévision, il est impossible de ne pas allouer l'approximation. Avec cela, vous pouvez produire des calculs approximatifs et calculer les indicateurs planifiés en remplaçant les objets source sur le plus simple. Dans Excel, il est également possible d'utiliser cette méthode pour prédire et analyser. Examinons comment cette méthode peut être appliquée dans le programme spécifié avec des outils intégrés.
Exécution de l'approximation
Le nom de cette méthode provient du mot latin Proxima - le "le plus proche" est approximativement en simplifiant et en lissant les indicateurs connus, les construisant dans la tendance et est sa base. Mais cette méthode peut être utilisée non seulement pour la prévision, mais également pour étudier les résultats déjà existants. Après tout, l'approximation est en fait simplifiée les données source et la version simplifiée est plus facile à explorer.L'outil principal avec lequel le lissage est effectué dans Excel construit une ligne de tendance. L'essence est que sur la base d'indicateurs déjà existants, un calendrier d'une fonction est attiré par les périodes futures. L'objectif principal de la ligne de tendance, comment il n'est pas difficile de deviner, c'est la préparation des prévisions ou de la détection d'une tendance générale.
Mais il peut être construit en utilisant l'un des cinq types d'approximation:
- Linéaire;
- Exponentiel;
- Logarithmique;
- Polynôme;
- Puissance.
Considérez chacune des options plus en détail séparément.
Leçon: Comment construire une ligne de tendance dans Excel
Méthode 1: lissage linéaire
Tout d'abord, considérons l'option la plus facile d'approximation, à savoir utiliser une fonction linéaire. Nous nous concentrerons plus en détail plus en détail, car nous présenterons les moments généraux caractéristiques et pour d'autres méthodes, à savoir la construction de l'horaire et d'autres nuances, dans laquelle, lors de l'examen des options ultérieures, nous ne nous arrêterons pas.
Tout d'abord, nous construirons un horaire, sur la base desquels nous allons effectuer une procédure de lissage. Pour construire un graphique, nous prenons la table dans laquelle le coût de l'unité produite par l'entreprise et le bénéfice correspondant de cette période est estimé. La fonction graphique que nous construisons affichera la dépendance des bénéfices croissants de réduire le coût des produits.
- Pour construire un graphique, tout d'abord, nous mettons en évidence les colonnes "Coût d'une unité de produits" et "profit". Après cela, nous passons à l'onglet "Insérer". Ensuite, sur la bande dans le bloc d'outil "Diagramme", cliquez sur le bouton "Spot". Dans la liste qui s'ouvre, sélectionnez le nom "Spot avec des courbes et des marqueurs lisses". Il s'agit de ce type de diagrammes qui conviennent le mieux à travailler avec une ligne de tendance, ce qui signifie que pour appliquer la méthode d'approximation pour exceller.
- L'horaire est construit.
- Pour ajouter une ligne de tendance, sélectionnez-la avec le bouton droit de la souris. Le menu contextuel apparaît. Choisissez-y l'article "Ajouter une ligne de tendance ...".
Il y a une autre option pour l'ajouter. Dans un groupe supplémentaire d'onglets sur le ruban "Travail avec graphiques", nous passons à l'onglet "Disposition". Ensuite, dans la barre d'outils "Analyse", cliquez sur le bouton "TREND LINE". La liste s'ouvre. Puisque nous devons appliquer une approximation linéaire, alors nous choisissons l'approximation linéaire des positions présentées.
- Si vous avez toujours choisi la première option d'action avec l'ajout dans le menu contextuel, la fenêtre de format ouvre.
Dans le bloc de paramètres "Créez une ligne de tendance (approximation et lissage)", nous définissons le commutateur sur la position "linéaire".
Si vous le souhaitez, vous pouvez installer une tique près de la position "Afficher l'équation sur le diagramme". Après cela, le diagramme affichera l'équation de fonction de lissage.
En outre, dans notre cas, il est important de comparer différentes options d'approximation pour établir une coche sur l'élément "Placez la valeur d'une approximation valide sur le diagramme (R ^ 2)". Cet indicateur peut varier de 0 à 1. Ce qu'il est plus élevé, l'approximation est meilleure (significative). On pense qu'avec la magnitude de cet indicateur 0,85 et ce qui précède, le lissage peut être considéré comme fiable et si l'indicateur est inférieur, non.
Après avoir passé tous les paramètres ci-dessus. Cliquez sur le bouton "Fermer", placé au bas de la fenêtre.
- Comme vous pouvez le constater, la ligne de tendance est construite sur le graphique. Avec une approximation linéaire, il est indiqué par une bande droite noire. La forme spécifiée de lissage peut être utilisée dans les cas les plus simples lorsque les données varient plutôt rapidement et la dépendance de la valeur de la fonction de l'argument est évidente.
Le lissage, utilisé dans ce cas, est décrit par la formule suivante:
Y = hache + b
Spécifiquement, notre formule prend ce genre:
Y = -0,1156x + 72.255
La valeur de la précision de l'approximation est égale à 0,9418, qui est un résultat assez acceptable, caractérisant le lissage, aussi fiable.
Méthode 2: approximation exponentielle
Considérons maintenant le type d'approximation exponentielle dans Excel.
- Afin de modifier le type de ligne de tendance, sélectionnez-le avec le bouton droit de la souris et sélectionnez le "format de ligne de tendance ..." dans le menu d'ouverture.
- Après cela, la fenêtre de format déjà familière est lancée. Dans une unité de sélection de type approximatif, nous définissons le commutateur sur la position "exponentielle". Les réglages restants laisseront la même chose que dans le premier cas. Cliquez sur le bouton "Fermer".
- Après cela, la ligne de tendance sera construite sur le graphique. Comme nous le voyons, lors de l'utilisation de cette méthode, il a une forme quelque peu courbe. Dans ce cas, le niveau de fiabilité est égal à 0,9592, ce qui est plus élevé que lors de l'approximation linéaire. La méthode exponentielle est mieux utilisée dans le cas où les premières valeurs sont rapidement modifiées, puis prennent une forme équilibrée.
La vision générale de la fonction de lissage est comme celle-ci:
y = soyez ^ x
Où E est la base d'un logarithme naturel.
Spécifiquement, notre formule a pris la forme suivante:
Y = 6282.7 * E ^ (- 0,012 * x)
Méthode 3: lissage logarithmique
Maintenant, c'est le tour de considérer la méthode d'approximation logarithmique.
- De la même manière, l'heure précédente dans le menu contextuel, nous démarquons la fenêtre Format de ligne de tendance. Nous établissons un passage à la position "logarithmic" et cliquez sur le bouton "Fermer".
- La procédure de construction d'une ligne de tendance avec approximation logarithmique se produit. Comme dans le cas précédent, il est préférable d'utiliser cette option lorsque les données sont rapidement modifiées, puis prenez une vue équilibrée. Comme vous pouvez le constater, le niveau de fiabilité est de 0,946. Ceci est plus élevé que lors de l'utilisation d'une méthode linéaire, mais inférieure à la qualité de la ligne de tendance lors du lissage exponentiel.
En général, la formule de lissage ressemble à ceci:
y = a * ln (x) + b
Où est la magnitude d'un logarithme naturel. D'où le nom de la méthode.
Dans notre cas, la formule prend le formulaire suivant:
y = -62,81ln (x) +404.96
Méthode 4: lissage polynomial
Il est venu de considérer la méthode de lissage polynomial.
- Aller à la fenêtre de format de la ligne de tendance, comme cela a déjà fait plus d'une fois. Dans le bloc « Trend Line Build », nous avons mis l'interrupteur en position « polynôme ». A droite de ce point est le champ « degré ». Au moment de choisir la valeur « polynôme » devient actif. Ici, vous pouvez spécifier une valeur de puissance de 2 (installé par défaut) à 6. Cet indicateur détermine le nombre de maxima et minima fonction. Lors de l'installation du polynôme du second degré, un seul maximum est décrit, et lorsque le sixième polynôme est installé, jusqu'à cinq maxima peut être décrit. Pour commencer, laissez les paramètres par défaut, qui est, nous précisons le second degré. Les autres paramètres laissent les mêmes que nous les mettons dans les voies précédentes. Cliquez sur le bouton "Fermer".
- La ligne de tendance en utilisant cette méthode est construite. Comme vous pouvez le voir, il est encore plus courbé que d'utiliser approximation exponentielle. Le niveau de fiabilité est supérieure à l'une quelconque des méthodes utilisées précédemment, et est 0,9724.
Cette méthode est la plus utilisée avec succès dans le cas où les données sont en constante évolution. La fonction décrivant ce type de lissage ressemble à:
y = a1 + a1 + a2 * x * x ^ 2 + ... + an * x ^ n
Dans notre cas, la formule a ce genre:
y = 0,0015 * x ^ 2-1,7202 * x + 507,01
- Maintenant, nous allons changer le degré de polynômes pour voir si le résultat sera différent. Retour à la fenêtre de format. Le type d'approximation est laissée par polynomiale, mais en face dans la fenêtre de mesure, nous la plus haute valeur possible - 6.
- Comme on le voit, après cela, notre ligne de tendance a pris la forme d'une courbe prononcée, dans laquelle le nombre de sommets est égal à six. Le niveau de fiabilité a augmenté encore plus, pour atteindre 0,9844.
La formule qui décrit ce type de lissage, prend la forme suivante:
Y = 8E-08x ^ 6-0.0003x ^ 5 + 0.3725x ^ 4-269,33x ^ 3 + 109525x ^ 2-2E + 07X + 2E + 09
Méthode 5: Puissance Lissage
En fin, tenir compte de la méthode d'approximation de la puissance dans Excel.
- Nous passons maintenant à la fenêtre « Format ligne de tendance ». Installer le commutateur de type de lissage à la position « de puissance ». Le spectacle de l'équation et le niveau de fiabilité, comme toujours, laissez le inclus. Cliquez sur le bouton "Fermer".
- Le programme constitue une ligne de tendance. Comme vous pouvez le voir, dans notre cas, il est une ligne avec une légère courbure. Le niveau de fiabilité est égal à 0,9618, qui est un indicateur assez élevé. De toutes les façons décrites ci-dessus, le niveau de fiabilité était plus élevé que lors de l'utilisation de la méthode polynomiale.
Cette méthode est efficace en cas de changement intensif de ces fonctions. Il est important de considérer que cette option ne s'applique que si la fonction et l'argument ne prennent pas de valeurs négatives ou nulles.
La formule générale décrivant cette méthode a ce type:
y = bx ^ n
Spécialement, pour notre cas, cela ressemble à ceci:
Y = 6e + 18x ^ (- 6,512)
Comme nous pouvons le constater, en utilisant des données spécifiques que nous avons utilisées par exemple, le niveau de fiabilité le plus important a montré la méthode d'approximation polynomiale avec un polynôme dans le sixième degré (0,9844), le plus petit niveau de fiabilité dans la méthode linéaire (0,9418). Mais cela ne signifie pas que la même tendance utilisera d'autres exemples. Non, le niveau d'efficacité dans les méthodes ci-dessus peut différer de manière significative, en fonction du type de fonction spécifique pour lequel la ligne de tendance sera construite. Par conséquent, si pour cette fonction, la méthode sélectionnée est la plus efficace, cela ne signifie pas du tout qu'il sera également optimal dans une autre situation.
Si vous ne pouvez pas déterminer immédiatement à la fois, sur la base des recommandations ci-dessus, quel type d'approximation convient spécifiquement dans votre cas, c'est-à-dire qu'il est logique d'essayer toutes les méthodes. Après avoir construit une ligne de tendance et visualisant son niveau de fiabilité, vous pouvez choisir la meilleure option.