Le lissage de données graphiques Excel avec une moyenne mobile fixe fonctionne lorsque vous avez de l’expérience avec les données et leur degré de variabilité. Mais, si les données changent ou si elles sont nouvelles, vous ou la direction pouvez vouloir expérimenter avec un nombre différent de périodes dans la moyenne de lissage.

Dans cet exemple, une formule OFFSET est utilisée pour créer une moyenne sur une plage dynamique. Vous saisissez le nombre de mois que vous souhaitez utiliser pour une période de lissage et la MOYENNE et OFFSET calculent le nombre de cellules sur lesquelles établir la moyenne.

Avant de rendre la MOYENNE dynamique, commençons par comprendre le fonctionnement de OFFSET. Dans la figure suivante, la cellule G26 contient la formule de lissage des données de la ligne 20,

=AVERAGE(OFFSET(G20,0,0,1,-3))

Cette formule est une étape intermédiaire pour vous aider à comprendre le fonctionnement de la formule dynamique. La fonction OFFSET renvoie une plage sur laquelle AVERAGE va agir. Les attributs de OFFSET sont,

OFFSET(référence,lignes,colonnes,,,)

Dans cet exemple, la plage que OFFSET calcule commence à G20 et elle a un décalage de ligne ou de colonne nul. En d’autres termes, le coin supérieur gauche de la plage calculée par OFFSET est juste G20. Il ne sera pas déplacé ou décalé d’une ligne ou d’une colonne. La plage calculée sera d’une rangée de haut et comprendra la cellule actuelle G26 et deux autres à gauche (c’est un -3). Donc, cette plage calculée sera G20:E20.

Copiez cette formule dans G26 vers la droite et vous verrez qu’elle fait la moyenne de la cellule de données au-dessus d’elle dans la ligne 20 et des deux cellules précédentes à gauche.

Donc, voici l’astuce qui rendra cela dynamique et fera la moyenne sur une plage que vous choisissez. Que se passe-t-il si vous substituez une référence de cellule à ce -3 dans la formule ?

Dans la figure ci-dessous, la formule de la cellule G26 a été modifiée de sorte que le est spécifié par le nombre dans la cellule E24,

AVERAGE(OFFSET(G20,0,0,1,-$E$24))

$E$24 est la cellule ombrée en vert. Le nombre que vous tapez dans la cellule E24 déterminera la largeur des cellules utilisées pour faire la moyenne à travers.

À ce stade, vous pourriez utiliser un spinner ou un menu déroulant pour donner à votre utilisateur le choix de la largeur de la moyenne de lissage. Le résultat de sa sélection devrait aller dans la cellule E24.

Télécharger le lissage Données de graphique Excel avec lissage fixe ou dynamique

Il y a un problème avec cet arrangement. Si l’utilisateur entre un nombre plus grand dans E24 qu’il n’y a de cellules à gauche, alors OFFSET inclura les cellules vides et l’étiquette. Cela donnera une moyenne incorrecte. Si E24 est si grand que OFFSET sort de la feuille, AVERAGE produit une erreur. Que faire ? Nous avons besoin d’une solution qui n’arrête pas seulement une erreur, comme ISERROR, nous avons besoin d’une solution qui arrête les réponses incorrectes.

Dans cette prochaine figure, la formule est la cellule G26 a été ajustée. La rangée 25 comporte une série de chiffres qui sont les limites des plages valides à gauche. La nouvelle formule dans la cellule G26 regarde la ligne 25 et si le nombre dans la cellule E24 est plus grand que la limite dans la ligne 25, alors NA() est produit. Cette nouvelle formule de correction d’erreur dans G26 est,

=IF($E$24>G25,NA(),AVERAGE(OFFSET(G20,0,0,1,-$E$24)))

Lorsqu’un graphique Excel fait référence à NA() dans une cellule, il ne trace rien. Cela empêche la ligne de lissage de tomber à zéro.

Problèmes avec le lissage des données et comment les contourner

Maintenant, vous pouvez laisser votre utilisateur essayer la période de lissage qu’il veut. Cependant, (on dirait qu’il y a toujours un « cependant » ou un « mais, dans ce cas ») il y a un problème avec le lissage des données. Le lissage supprime la volatilité, mais il signifie également que vous ne verrez pas les changements de direction, les points d’inflexion ou les nouvelles tendances avant la moitié de la période de lissage. Vous êtes en retard sur la courbe.

Le meilleur des deux mondes est d’utiliser une fonction de lissage moyenne pondérée. De cette façon, vous pouvez donner plus de poids aux données à court terme afin de voir les tendances, mais vous pouvez lisser sur la base de données plus anciennes pour éliminer une partie de la volatilité. Cela semble être un bon cas pour un autre article, Lissage de données de graphiques Excel en utilisant une moyenne pondérée.

Téléchargez le fichier d’exemple pour le lissage de données de graphiques Excel avec un lissage mobile ou dynamique

Articles connexes,

• Lissage de données de graphiques Excel avec une moyenne mobile
• Lissage de données de graphiques Excel en utilisant une moyenne pondérée

.