Excel – Calculer une somme et une moyenne mobiles

Publié le 24 janvier 2019
par Sophie Marchand M.Sc., CPA, CGA, MVP
Image somme mobile

Excel – Calculer une somme et une moyenne mobiles

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.

 

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

 

Pour faire la somme des 6 activités, nous avons simplement fait la somme des 6 résultats obtenus avec la formule ci-dessus.

 

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 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 :

François Le May
Écrit il y a 5 mois
Il était grandement temps que je la suive!

Mon efficacité au travail et ma capacité à faire des analyses rapidement vont être grandement améliorées.

Mélanie Bonneau
Écrit il y a 6 mois
Adaptée pour le monde des CPA

Je trouve que cette formation est la plus adaptée pour le monde des CPA. En d'autres mots, j'apprends davantage ici que dans une formation générale de type "débutant - intermédaire - avancé" et je trouve que c'est plus concret pour mes activités quotidiennes. Merci!

Isabelle Madé
Écrit il y a 8 mois
Excellente formation!

Le découpage des sections est parfait, on peut prendre le tout à petites bouchées à notre rythme. J'utilise Excel depuis très longtemps et comme bien des gens j'ai appris sur le tas. Cette mise à niveau m'a permis d'apprendre ou mieux comprendre certaines formules et fonctionnalités que j'aurais dû maîtriser bien avant! Je suis prête pour la suite...

Melissa Forgues
Écrit il y a 9 mois
J'ai adoré!

Je croyais faire cette formation pour rien puisque je travaille avec Excel et prit quelques cours dans le passé. Je croyais que je maîtrisais toute la base! Ayayaye! Je me suis trompé! La formatrice donne énormément de trucs et des informations jusque là inconnue pour moi, jamais montré en salle de formation avec d'Autres compagnies... Déjà là, je vais sauvé beaucoup de temps. OMG! Bravo!

Sylvie Giroux
Écrit il y a 2 ans
Captivant

La formatrice expliquait très clairement chaque exemple et avait un ton de voix dynamique. En suivant une formation en ligne, je craignais de m'endormir, mais non! J'ai été captivée et intéressée tout au long de ma formation.


CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

Laisser un commentaire

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

Scroll to Top