Contact: 514-605-7112 / info@lecfomasque.com

Excel – Calculer une somme et une moyenne mobiles

Publié le : 24 janvier 2019

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 lInterpré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?.

 


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

Laisser un commentaire