Excel – Calculer une somme et une moyenne mobiles

Publié le 24 janvier 2019
par Sophie Marchand M.Sc.
Image somme mobile

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.

Sommes et moyennes mobiles

 

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:

DECALER

 

Pour effectuer la somme des 4 trimestres pour chacune des 6 activités, nous avons proposé la fonction suivante:

SOMME MOBILE

 

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:

MOYENNE MOBILE

 

Pour faire la moyenne des 6 activités, nous avons utilisé la fonction suivante:

MOYENNE MOBILE TOTALE

 

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.

FONCTIONNE AVEC 2

 

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:

SOMME MOBILE V2

 

Pour calculer la moyenne des x derniers mois, nous proposons:

MOYENNE MOBILE V2

 

Pour calculer la moyenne des x derniers mois des 6 activités, nous proposons:

MOYENNE TOTALE MOBILE v2

 

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:

 

Voici quelques commentaires d’apprenants ayant suivi la formation Excel – Mise à niveau :


CFO-Masque_Formations-en-ligne_FBLa 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é.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut