Récemment, lors d’une de mes formations en modélisation financière dans Excel, une question intéressante m’a été posée par un apprenant. Il souhaitait savoir si on pouvait, à partir d’un tableau de données à deux dimensions (mois et années), calculer une période de temps dont le calcul dépendrait de 3 paramètres, spécifiés par l’utilisateur: mois de départ, mois de fin et année. La réponse est oui! Cet article vise à vous enseigner la technique sous-jacente, basée sur la fonction INDEX imbriquée dans une SOMME.
Données de départ pour notre fonction INDEX imbriquée dans une SOMME
Pour illustrer mon exemple, je vais utiliser les données ci-dessous. À noter que l’usager peut choisir un mois de départ (en E2), un mois de fin (en E3) et une année (en E4).
La fonction SOMME de base
D’abord, si les paramètres ne devaient pas être flexibles, pour trouver la somme de mars en juillet de l’année 2014, on utiliserait simplement la fonction suivante:
= SOMME(G10:G14)
À partir de cette formule, c’est maintenant à nous de remplacer le G10 et le G14 par des fonctions dynamiques, qui vont réagir aux paramètres spécifiés par l’utilisateur.
La fonction INDEX pour plus de flexibilité
On peut donc remplacer le G10 et le G14 par des fonctions INDEX. Le premier INDEX va se positionner sur le mois de départ de l’année sélectionnée et le deuxième INDEX va se positionner sur le mois de fin de l’année sélectionnée. Cette formule plutôt simple va nous permettre d’arriver à nos fins. Il fallait simplement y penser!
Dorénavant, grâce à cette Fonction SOMME(INDEX()), l’utilisateur pourra changer le mois de départ, le mois de fin et l’année et la formule en H2 rapportera toujours la somme des valeurs entre les deux mois de l’année sélectionnée.
La fonction DECALER plutôt que la fonction INDEX imbriquée dans une SOMME?
La fonction DECALER nous permettrait également d’arriver au résultat mais rappelons-nous que la fonction DECALER est une fonction volatile et peut entraîner de l’instabilité dans le fichier. Donc, lorsqu’on peut s’en passer, mieux vaut le faire.
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Pour une introduction aux principes de base et aux meilleures pratiques d’affaires en modélisation financière dans Excel et vous familiariser avec les fonctions et les fonctionnalités qui permettent de faire des prévisions financières, des analyses de sensibilité et des analyses de scénarios, suivez notre formation Excel – Modélisation financière niveau 1.
Voici quelques commentaires d’apprenants ayant suivi la formation Excel – Modélisation financière niveau 1 :
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é.
Bonjour Sophie,
Il aurait également été possible d’utiliser la “technique” d’intersection. Après avoir créé des zones nommées pour les années (en mettant un “_” devant le numéro d’année) et pour les mois. Une formule de type =SOMME(INDIRECT(E2):INDIRECT(E3) INDIRECT(E4)) fonctionne correctement me semble-t-il.
Merci pour ce site.
Laurent
Bonjour Laurent,
Tel que précisé dans cet article, j’ai utilisé la fonction INDEX pour éviter d’utiliser une fonction volatile, comme notamment la fonction DECALER. La fonction INDIRECT est aussi une fonction volatile. Donc, oui, il aurait été possible d’utiliser ces fonctions (DECALER ou INDIRECT) mais lorsque l’on peut éviter les fonctions volatiles dans Excel, mieux vaut le faire.
Au plaisir,
Sophie
Bonjour Sophie
Merci pour le partage, est-il possible de faire la somme de octobre 2013 jusqu’au fevriet 2014
Merci
Bonjour,
Oui, bien sûr. Il s’agit d’ajuster la fonction Index qui ramène le numéro de colonne, en conséquence. Il faudrait donc une cellule d’Input pour entrer l’année de départ et une cellule d’Input pour entrer l’année de fin et utiliser cette deux cellules dans les deux EQUIV qui retournent les numéros de colonnes.
Au plaisir,
Sophie
Très clair comme explication, bravo encore pour vos posts
Bonjour,
Cà peut être utile dans un tableau que l’on utilise tous les jours si l’on change les mois, mais avec des listes déroulantes au niveau des 2 cellules mois et de l’année.
Mais c’est plus simple de recouvrir les zones manuellement avec le pointeur, la barre d’état tout en bas vous donnant le total des cellules.
merci
Franck