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

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

Publié le : 1 novembre 2018

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.

 

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.

 


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.

  • réponse Laurent ,

    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

    • réponse Sophie Marchand ,

      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

    • réponse Mohamed Slim Ben Mosbeh ,

      Bonjour Sophie
      Merci pour le partage, est-il possible de faire la somme de octobre 2013 jusqu’au fevriet 2014
      Merci

      • réponse Sophie Marchand ,

        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

      • réponse Laurent Kaddouch ,

        Très clair comme explication, bravo encore pour vos posts

        • réponse franck ,

          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

          Répondre à Laurent Annuler