Comment créer une fonction personnalisée pour les Moyennes Mobiles sur Excel (et sans VBA !) ?
Dans ce tutoriel, je vais vous montrer comment créer votre propre fonction Excel personnalisée pour calculer automatiquement des moyennes mobiles, des totaux glissants et d'autres agrégations dynamiques.
Nous allons construire ensemble cette fonction étape par étape, en utilisant les nouvelles fonctions incroyables d'Excel 365 comme LET, LAMBDA ou MAP.
Une fois créée, cette fonction sera réutilisable dans tous vos classeurs Excel, aussi simplement qu'une fonction SOMME ou MOYENNE.
Plus besoin de réinventer la roue à chaque fois que vous avez besoin d'un calcul glissant !
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous trouvons les relevés de température quotidienne au sein d’une cave d'affinage d’une fromagerie artisanale.
Cette donnée est importante car elle influence directement la qualité de maturation de nos fromages, il est donc important de surveiller la température moyenne sur plusieurs jours pour détecter rapidement toute dérive qui pourrait affecter la production.
En cellule D8, nous retrouvons également la valeur « 5 ».
Ce paramètre nous permet de définir sur combien de jours nous voulons calculer notre moyenne mobile.
Avant de commencer, il est important de comprendre que les calculs glissants sont basés sur la position des valeurs dans notre liste. Par exemple, pour calculer la moyenne mobile du 8ème jour sur 5 jours, nous devons faire la moyenne des jours 4, 5, 6, 7 et 8.
C'est un calcul répétitif qui devient complexe avec les formules classiques, d'où l'intérêt de créer une fonction réutilisable.
2. Construction de la formule avec LET
Nous allons commencer par construire notre formule directement dans une cellule avant de la transformer en fonction personnalisée, ce qui nous permet de tester et de comprendre chaque étape avant de finaliser notre création.
Nous nous rendons dans la cellule E10 où nous allons construire notre formule.
Nous allons utiliser la fonction LET, qui est une fonction avancée qui permet de créer des variables temporaires dans notre formule.
Cela rend notre formule plus lisible, plus performante et plus facile à maintenir puisque nous n'avons pas à répéter les mêmes calculs plusieurs fois.
=LET(
Nous allons maintenant définir deux variables qui représentent les données d'entrée :
- La variable « tableau », qui contient toutes nos températures que nous voulons analyser,
- Une deuxième variable « nbJours » qui correspond au nombre de jours sur lesquels nous voulons calculer notre moyenne mobile, c'est-à-dire 5 jours dans notre exemple.
=LET(tableau;B9:B23;nbJours;E8
=LET(tableau;B9:B23;nbJours;E8
Un calcul glissant est fondamentalement basé sur la position de chaque valeur. Nous devons donc créer une liste de numéros qui représentent la position de chaque température.
Pour nos 15 températures, nous voulons une liste de 1 à 15, pour cela, nous allons nous reposer sur la fonction SEQUENCE, qui génère automatiquement une suite de nombres.
=LET(tableau;B9:B23;nbJours;E8;indexPosition;SEQUENCE(NBVAL(tableau))
La fonction NBVAL compte combien de valeurs non vides il y a dans notre tableau, ce qui nous donne 15, et SEQUENCE crée donc la liste 1, 2, 3... jusqu'à 15.
C'est maintenant que nous entrons dans la partie la plus technique de notre formule.
Nous allons en effet utiliser la fonction MAP qui nous permet d'appliquer un calcul personnalisé à chaque élément d'une liste :
=LET(tableau;B9:B23;nbJours;E8;indexPosition;SEQUENCE(nbval(tableau));calcul;MAP(indexPosition;
Ensuite, pour chacun de ces index, nous allons appliquer un calcul personnalisé, grâce à la fonction LAMBDA, qui permet de créer des fonctions personnalisées et réutilisables.
Cette fonction LAMBDA prend notre liste de positions en utilisant le paramètre « v » pour représenter chaque valeur de position individuellement.
=LET(tableau;B9:B23;nbJours;E8;indexPosition;SEQUENCE(nbval(tableau));calcul;MAP(indexPosition;LAMBDA(v
Nous devons maintenant gérer un problème important des calculs glissants.
Au début de notre série de données, nous n'avons pas encore assez de valeurs pour calculer une moyenne sur 5 jours.
Pour la position 1, nous n'avons qu'une seule température, pour la position 2, nous n'en avons que deux, etc.
Ce n'est qu'à partir de la position 5 que nous avons nos 5 températures nécessaires.
Nous commençons par vérifier si la position actuelle est inférieure à la longueur de période requise. Si c'est le cas, nous renvoyons une cellule vide avec les deux guillemets :
=LET(tableau;B9:B23;nbJours;E8;indexPosition;SEQUENCE(nbval(tableau));calcul;MAP(indexPosition;LAMBDA(v;SI(v<nbJours;"";
Maintenant, nous allons construire le calcul qui s'applique quand nous avons assez de données.
Pour cela, nous allons calculer la SOMME des valeurs situées sur les 5 lignes.
Les valeurs pour lesquelles nous souhaitons obtenir la somme seront donc obtenues grâce à la fonction CHOISIRLIGNES, qui permet de sélectionner les lignes correspondantes de notre tableau.
Cette fonction attend deux arguments : la plage correspondante au tableau sur le lequel nous souhaitons effectuer l’extration (notre variable « tableau ») et les numéros de lignes.
Enfin, pour obtenir ces numéros de lignes, nous allons appeler une nouvelle fois la fonction SEQUENCE pour créer une liste de positions.
Pour cela, nous allons utiliser les quatre arguments :
- Le premier « nbJours » indique combien de nombres nous voulons, c'est-à-dire 5.
- Le deuxième « 1 » indique que nous voulons une seule colonne.
- Le troisième « v » indique le point de départ, c'est-à-dire la position actuelle.
- Le quatrième « -1 » indique le pas, c'est-à-dire que nous reculons d'une position à chaque fois. Pour la position 8, cela crée la liste 8, 7, 6, 5, 4.
=LET(tableau;B9:B23;nbJours;E8;indexPosition;SEQUENCE(nbval(tableau));calcul;MAP(indexPosition;LAMBDA(v;SI(v<nbJours;"";SOMME(CHOISIRLIGNES(tableau;SEQUENCE(nbJours;1;v;-1 ))))))
Et voilà, le plus dur est derrière nous, pour finir notre fonction LET, il ne reste plus qu’à renvoyer une dernière variable « calcul » pour indiquer à Excel de retourner le résultat obtenu par ce calcul, en faisant bien attention de bien fermer toutes les parenthèses ;) :
=LET(tableau;B9:B23;nbJours;E8;indexPosition;SEQUENCE(NBVAL(tableau));calcul;MAP(indexPosition;LAMBDA(v;SI(v<nbJours;"";SOMME(CHOISIRLIGNES(tableau;SEQUENCE(nbJours;1;v;-1))))));calcul)
Maintenant, si nous appuyons sur [Entrée], notre formule nous renverra le total glissant sur 5 jours pour chaque température. Les quatre premières cellules restent vides car nous n'avons pas encore 5 valeurs, puis à partir de la cinquième position, nous obtenons la somme des 5 dernières températures.
Attention, il s’agit d’une fonction de propagation, il ne doit donc rien y avoir de saisie dans les cellules de la colonne.
3. Transformation en fonction personnalisée
Maintenant que notre formule fonctionne parfaitement, nous allons la transformer en fonction réutilisable avec LAMBDA. Cette étape va nous permettre de créer une fois et d'utiliser partout, dans tous nos classeurs Excel.
Nous nous repositionnons dans la cellule C2 et nous éditons notre formule en appuyant sur [F2]. Nous nous plaçons tout au début de la formule, juste avant le LET.
Nous tapons :
=LAMBDA(tableau;nbJours;fonction;
Nous définissons trois paramètres pour notre fonction personnalisée :
- Le premier « tableau » représente la plage de données à analyser.
- Le deuxième « nbJours » représente le nombre de périodes pour le calcul glissant.
- Le troisième « fonction » représente le type de calcul à appliquer, comme SOMME, MOYENNE, MAX ou MIN.
Puisque « tableau » et « nbJours » sont maintenant des paramètres de LAMBDA, nous devons les supprimer du début de notre fonction LET. Nous effaçons les portions « tableau;B2:B16; » et « nbJours;D3; » de notre formule. Ces valeurs seront maintenant passées en argument quand nous appellerons notre fonction.
Puis, nous pour rendre notre fonction encore plus puissante, nous allons souhaiter que la fonction à utiliser soit saisie directement en tant qu’argument. Dans notre calcul MAP, nous devons donc remplacer SOMME par notre variable « fonction ».
Cela nous permet d'utiliser n'importe quelle fonction d'agrégation selon nos besoins.
Nous ajoutons une parenthèse fermante tout à la fin de notre formule pour fermer le LAMBDA.
=LAMBDA(tableau;nbJours;fonction;LET(indexPosition;SEQUENCE(NBVAL(tableau));calcul;MAP(indexPosition;LAMBDA(v;SI(v<nbJours;"";fonction(CHOISIRLIGNES(tableau;SEQUENCE(nbJours;1;v;-1))))));calcul))
Attention, si nous validons maintenant la formule, Excel nous renverra une belle erreur #CALC!.
C’est tout à fait normal, car la fonction LAMBDA ne peut pas être utilisée directement dans une cellule Excel, car nous devons lui spécifier la valeurs des arguments utilisés.
Nous sélectionnons maintenant toute notre formule en appuyant sur [Ctrl]+[A] dans la barre de formule, puis nous copions avec [Ctrl]+[C].
Nous allons dans l'onglet « Formules » du ruban Excel.
Nous cliquons sur le bouton « Gestionnaire de noms » puis sur le bouton « Nouveau ».
Dans la boîte de dialogue qui s'ouvre, nous cliquons dans le champ « Nom » et nous tapons « CALCUL_GLISSANT ».
Dans le champ « Étendue », nous laissons « Classeur » pour que notre fonction soit disponible partout dans ce fichier.
Nous cliquons dans le champ « Fait référence à » et nous collons notre formule avec [Ctrl]+[V].
Nous cliquons sur le bouton « OK » pour valider.
Notre fonction personnalisée est maintenant créée et enregistrée dans Excel !
4. Utilisation pratique de la fonction
Pour utiliser la fonction, nous pouvons supprimer la formule complète, et saisir à la place la formule :
=CALCUL_GLISSANT(B9:B23;E8;SOMME)
Excel calcule automatiquement le total glissant sur 5 jours pour chaque température.
Nous observons que les quatre premières cellules restent vides car il n'y a pas encore 5 valeurs disponibles.
À partir de la cinquième position, nous obtenons la somme des 5 dernières températures.
Pour le jour 5, par exemple, nous obtenons la somme des températures des jours 1, 2, 3, 4 et 5.
Ces résultats sont strictement identiques à ceux obtenus un peu plus tôt, mais la fonction utilisée est nettement plus simple et lisible !
Pour obtenir une moyenne mobile au lieu d'un total, nous modifions simplement le troisième paramètre :
=CALCUL_GLISSANT(B9:B23;E8;MOYENNE)
C'est exactement ce que nous cherchions pour surveiller la température de notre cave d'affinage.
Cette moyenne lisse les variations quotidiennes et nous permet de mieux identifier les tendances.
L'avantage de notre fonction est sa flexibilité totale. Si nous changeons la valeur dans la cellule E8, par exemple de 5 à 3, tous nos calculs se mettent automatiquement à jour pour calculer sur 3 jours au lieu de 5.
Nous pouvons tester différentes périodes pour trouver celle qui correspond le mieux à nos besoins d'analyse. Une période plus courte réagit plus rapidement aux changements, tandis qu'une période plus longue lisse davantage les fluctuations.
Pour utiliser cette fonction dans un nouveau classeur, la procédure est simple : il suffit en effet de copier n'importe quelle cellule contenant notre fonction CALCUL_GLISSANT avec [Ctrl]+[C], puis de la coller dans le classeur de destination avec [Ctrl]+[V].
Excel importe automatiquement la définition de notre fonction personnalisée.
Nous pouvons maintenant adapter les paramètres selon nos nouveaux besoins. C'est le principe du « créer une fois, utiliser partout » qui fait toute la puissance de cette approche.
Attention toutefois, si vous utilisez le classeur sur plusieurs poste de travail, assurez-vous que celui-ci est compatible avec les fonctions utilisées. En effet, les fonctions LET, LAMBDA, MAP, SEQUENCE ou encore CHOISIRLIGNES sont disponibles uniquement dans les versions récentes d’Excel 365.
Si le fichier est ouvert sur une version plus ancienne, la fonction personnalisée ne sera pas reconnue et renverra une erreur.
En dehors de ce point de vigilance, vous disposez désormais d’une fonction puissante, élégante et entièrement native, qui vous permet de réaliser des moyennes mobiles, totaux glissants ou tout autre calcul dynamique sans VBA, sans colonnes intermédiaires et avec une lisibilité maximale. Une excellente base pour professionnaliser vos analyses Excel et gagner un temps précieux au quotidien.