L’article suivant a pour but de répondre à une question poser sur notre forum récemment par Pascal. Il nous demandait notamment comment calculer la moyenne et la somme des 4 derniers trimestres sans passer par un tableau croisé dynamique. Cet article vise donc à vous expliquer comment calculer une somme et moyenne mobiles dans Excel.
Mise en contexte
Pascal nous a fourni le tableau ci-dessous. L’objectif est de présenter dans la colonne C et dans la colonne D, la somme et la moyenne des 4 derniers trimestres, par activité et au total. Les informations de chaque trimestre sont inscrites au fur et à mesure dans ce tableau.
Solution proposée avec une fonction DECALER
Nous avons proposé une solution avec une solution DECALER. Sachant toutefois que la fonction DECALER est une fonction volatile, nous avons également proposé une solution avec une fonction INDEX.
D’abord, rappelons que la fonction DECALER permet de partir d’une cellule spécifique et de se déplacer vers le bas ou vers le haut puis vers la droite ou vers la gauche et de sélectionner ensuite une plage de données en hauteur et/ou en largeur. Voici les paramètres spécifiques:
Pour effectuer la somme des 4 trimestres pour chacune des 6 activités, nous avons proposé la fonction suivante:
Voici comment Interpréter:
- On demande à Excel de se positionner sur la cellule F8 (valeur du 1er trimestre)
- On lui demande ensuite de ne pas se déplacer ni vers le haut ni vers le bas (déplacement de 0 lignes)
- On lui demande ensuite de se déplacer vers la droite de NBVAL(F8:Q8)-$C$5.
- La fonction NBVAL(F8:Q8) permet de déterminer à quel trimestre on est rendu en renvoyant le nombre de cellules non vides dans la sélection de trimestres (dans notre exemple, 8)
- On retranche de ce nombre le nombre de trimestres à consolider (dans notre exemple 4)
- On demande donc à Excel de se déplacer de 4 cellules vers la droite
- Excel aboutit à la colonne du trimestre 5
- On demande ensuite à Excel de prendre une sélection de cellules de hauteur 1 et de largeur 4
- Donc Excel sélectionne les cellules des trimestres 5, 6, 7 et 8
- La fonction SOMME permet de faire la somme de ces 4 cellules
Somme mobile dans Excel
Pour faire la somme des 6 activités, nous avons simplement fait la somme des 6 résultats obtenus avec la formule ci-dessus.
Moyenne mobile dans Excel
Nous avons procédé de la même façon pour déterminer la moyenne:
Pour faire la moyenne des 6 activités, nous avons utilisé la fonction suivante:
En effet, ici, il faut faire la moyenne des données des 4 derniers trimestres mais pour les 6 activités. Il nous faut donc une plage équivalent à une hauteur de 6.
Variation du nombre de trimestres
La beauté de ces formules, c’est qu’on peut aisément changer le nombre de trimestres dans la cellule C5 et le tout se recalcule en conséquence. Ici on voit le résultat de ces formules appliquées aux 2 derniers trimestres.
Solution proposée pour une somme mobile dans Excel avec une fonction INDEX
Tel que décrit dans l’article Excel: Vous pensez tout connaître de la fonction INDEX?, il est préférable, quand c’est possible, de remplacer la fonction DECALER par la fonction INDEX. Cela dit, dans un contexte où il y a aussi peu de lignes dans le fichier INDEX, la fonction DECALER devrait fonctionner sans problème. Voici néanmoins une approche alternative avec la fonction INDEX.
Pour calculer la somme des X derniers mois, nous proposons:
Pour calculer la moyenne des x derniers mois, nous proposons:
Pour calculer la moyenne des x derniers mois des 6 activités, nous proposons:
Note: Cette approche alternative est bien expliquée dans l’article Excel: Vous pensez tout connaître de la fonction INDEX?.
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Apprendre les meilleures pratiques d’affaires dans Excel
Apprendre à travailler selon les meilleures pratiques d’affaires dans Excel peut vous faire économiser un temps fou, en plus d’enrayer vos risques d’erreurs. On croit parfois à tort qu’en cherchant sur internet, on va trouver les réponses à nos questions mais on sous-estime largement le temps investi pour chercher les réponses à nos questions et les adapter à notre situation. Par surcroît, en travaillant de cette façon, on ne fait que boucher des trous. Il est impossible d’apprendre les meilleures méthodes d’élaboration d’un fichier Excel en allant piger des trucs à gauche et à droite. Pour cela, il faut passer à travers une formation structurée, qui enseigne les concepts de base. À ce sujet, je vous recommande les 3 formations suivantes:
- Excel – Mise à niveau
- Excel – Modélisation financière (niveau 1)
- Excel – Introduction à Power Query et au langage M
Voici quelques commentaires d’apprenants ayant suivi la formation Excel – Mise à niveau :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
