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

Publié le 01 novembre 2018
par Sophie Marchand M.Sc., CPA, CGA, MVP
Somme INDEX

Excel: Vous pensez tout connaître de la fonction 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.

 

Données de départ

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!

SOMME INDEX EXCEL

 

Dorénavant, 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.

 

Et la fonction DECALER?

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 :

 

Jean-Philippe Provost
Écrit il y a 2 semaines
Super formation !

Me permettra sans aucun doute de relevé la qualité de mes modélisations financières même si je modélise depuis plus de 10 ans.

Antoine Champagne
Écrit il y a 5 mois
Excellents outils

Des outils excellents pour bien organiser et rendre clair nos fichiers Excel. Je travaille à tous les jours avec excel et c'est bon de pouvoir bonifier et améliorer mon travail. Vraiment, Mme Marchand, vous êtes excellente; j'ai hâte à la formation suivante. Ça fait plaisir d'encourager une entreprise québécoise qui "excel" dans son domaine.

Paul Chainé
Écrit il y a 1 an
Vraiment très agréable à suivre

La narration et les explications se font avec un débit qui laisse le temps de voir et comprendre ce qui est expliqué, dans un style amical et décontracté, la simplicité des exercices est très appréciée ce qui fait qu'on les fait et c'est justement ce qui nous permet d'assimiler la matière.

Mélanie Hébert
Écrit il y a 2 ans
Très utile

J'aime que nous avons des excel que nous pouvons revoir par la suite j'aime que la formation soit faite avec des sections définies et claires et que chaque sujet ait son propre document de présentation ce qui permet de le consulter facilement au besoin


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

 

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 de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top