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.
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 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:
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 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?.
NOTRE OFFRE DE FORMATIONS
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, Analytique d’affaires et Finance corporative. 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. De plus, nos formations peuvent mener à l’obtention d’une accréditation.
Découvrez quelles formations vous conviennent
Pour info: 514-605-7112 ou [email protected]
Jeu de briques
Snake
Pacman
Bubble