Excel: Vous pensez tout connaître de la fonction INDEX?

Publié le 01 novembre 2018
par Sophie Marchand M.Sc.
Somme INDEX

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).

Somme INDEX

 

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.

 

Vous aimeriez créer des états financiers prévisionnels complets et dynamiques dans Excel, capables de se moduler à vos hypothèses, afin d’éclairer vos décisions d’affaires ? Apprenez les meilleures pratiques avec nos formations en modélisation financière.

 

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!

INDEX imbriquée dans une SOMME

 

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 :


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

6 réflexions sur “Excel: Vous pensez tout connaître de la fonction INDEX?”

  1. 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

    1. 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

    1. 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

  2. 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

Laisser un commentaire

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

Retour en haut