Comment mettre en place une référence dynamique ? La fonction INDIRECT() d’Excel

Pour ce nouveau tutoriel, nous allons découvrir comment utiliser des références dynamiques directement au sein d’une cellule Excel. C’est-à-dire que nous allons utiliser le résultat contenu dans une cellule pour déterminer rapidement la feuille de calculs du classeur Excel dans laquelle se trouvent les données à extraire dans un tableau Excel.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Rappels sur la notion de référence dans Excel

Au cours des chapitres précédents, nous avons déjà abordé à plusieurs reprises les références de cellules dans Excel.

Pour rappel, nous utilisons le terme de référence pour désigner le fait que le résultat d’une cellule est utilisé directement pour effectuer un calcul ou un traitement dans une autre cellule.

Pour simplifier, il suffit alors de saisir les coordonnées de cette cellule cible dans la cellule qui a besoin de son résultat.

Nous avions ainsi pu découvrir qu’il existe trois grands types de références :

• Tout d’abord les références relatives : il s’agit du type de référence par défaut utilisé par Excel. La particularité de cette référence, c’est qui si nous déplaçons le calcul dans une autre cellule, par déplacement ou par duplication, alors la formule va automatiquement s’adapter à la nouvelle cellule de destination (A1).
• Il existe ensuite les références absolues, identifiables rapidement par la présence des symboles dollars devant la lettre de colonne et le numéro de ligne ($A$1).Dans ce cas-là, si nous déplaçons la formule dans une autre cellule, alors la référence restera figée et la cellule cible sera inchangée.
• Pour profiter des propriétés de ces deux types de cellules, nous pouvons encore utiliser les références mixtes, dans lesquelles il n’y aura qu’un seul symbole dollar pour ne fixer que la colonne ou la ligne ($A1 ou A$1)

En plus de ces trois types de références, il faut également savoir qu’une référence peut être interne, c’est-à-dire pointer vers une cellule du même classeur, ou externe si la cellule cible se trouve sur un autre classeur.

Pour en savoir plus sur ce dernier point, vous pouvez consulter le tutoriel dédié en cliquant ici.

2. La référence dynamique

Après ce bref rappel sur la notion de référence, découvrons ce qui nous intéresse ici, à savoir les références dynamiques.

Il est en effet possible de générer une référence intelligente en utilisant une fonction Excel.

Cette fonction, nous l’avions déjà abordée rapidement dans un tutoriel précédent, il s’agit de la fonction INDIRECT(), qui permet de récupérer une référence de cellule à partir d’une simple chaîne de caractères que nous lui passons en argument.

Pour récupérer la valeur d’une cellule, il suffit de saisir ses coordonnées en tant qu’argument :

 =INDIRECT("B6") 

Ce qui nous permet de récupérer la valeur de la cellule B6.

Attention toutefois, il ne s’agit pas de saisir la référence de la cellule, mais ses coordonnées, en tant que chaîne de caractère.

L’argument est donc à saisir entre guillemets.

À partir de là, nous pouvons donc comprendre comment il est possible de récupérer la valeur d’une cellule dynamiquement en venant saisir les coordonnées de la cellule en question dans une autre cellule :

 =INDIRECT(D6) 

Il suffira ensuite de modifier la valeur de cette cellule D6 pour que le résultat soit adapté :

La fonction INDIRECT() peut également recevoir un second argument facultatif : « A1 ».

Celui-ci permet de spécifier le type de référence utilisée.

Lorsque cet argument est omis, ou s’il a pour valeur VRAI, alors nous utiliserons une référence classique sous la forme A1.

 =INDIRECT("B10") 

En revanche, cet argument a pour valeur FAUX, alors nous utiliserons une référence sous la forme « L1C1 » :

 =INDIRECT("L10C2";FAUX) 

Cette seconde possibilité sera pratique si nous connaissons la position de la colonne dans laquelle se trouve l’information, sans nécessairement devoir déterminer la lettre qui correspond.

3. Comment récupérer une valeur contenue sur une autre feuille.

Maintenant, si nous souhaitons récupérer la valeur d’une cellule positionnée sur une autre feuille de calculs, il suffit de saisir la référence correspondante en utilisant les règles énoncées dans le tutoriel dédié aux références internes et externes :

• Tout d’abord, nous commençons par saisir le nom de la feuille,
• Puis nous saisissons un point d’exclamation suivi des coordonnées de la cellule : « 2020!B10 »
• Si le nom de la feuille de calcul contient une espace ou un autre caractère spécial, nous saisissons le nom de la feuille entre des guillemets simples : « 'Année 2020'!B10 »
• S’il s’agit d’une référence externe, nous saisissons le nom du fichier juste avant entre crochets : « '[Autre fichier.xlsx]Année 2020'!B10 »

Par contre, contrairement à une référence externe, la fonction INDIRECT() nécessite que le fichier externe soit ouvert pour retourner le résultat de la cellule cible.

Dans le cas contraire, Excel nous retournera une erreur #REF ! :

4. Mettre en place une référence dynamique

Maintenant que la fonction INDIRECT() n’a plus de secret pour nous, nous allons pouvoir l’utiliser pour récupérer dynamiquement une information contenue dans un onglet dont le nom se retrouve dans une cellule.

Pour cela, nous disposons d’un classeur dont les CA réalisés par une entreprise au cours des dernières années est inséré dans des onglets différents :

Dans chacun de ces onglets, les tableaux sont organisés exactement de la même manière : le y retrouvons le montant des ventes mensuelles pour trois produits donnés :

L’objectif sera alors de récupérer rapidement les ventes qui correspondent à une année donnée dans une feuille de synthèse :

Et pour cela, nous allons bien entendu vouloir utiliser la fonction INDIRECT().

Pour commencer, nous allons nous placer sur la cellule B10 et souhaiter obtenir le résultat de la même cellule, située sur la feuille 2020.

Conformément à ce que nous avons vu un peu plus tôt, la formule est donc la suivante :

 =INDIRECT("2020!B10") 

Ici, il n’y a aucune difficulté.

Ensuite, pour rendre cette formule dynamique, il suffira simplement de remplacer le terme « 2020 » par les coordonnées de la cellule sur laquelle se trouve l’année en cours d’analyse, à savoir la cellule B6.

Pour concaténer le résultat de cette cellule avec la suite de l’argument, nous utilisons une simple esperluette :

 =INDIRECT($B$6&"!B10") 

Bien entendu, nous utilisons ici une référence absolue afin de pouvoir étendre le résultat de la formule sur les cellules adjacentes.

Justement, maintenant essayons d’étendre cette formule sur les cellules situées sur la même ligne, afin de déterminer le CA du mois de janvier des deux autres produits, à l’aide de la poignée de recopie :

Comme vous pouvez le constater, le résultat retourné est erroné, car les coordonnées saisies dans l’argument sont insérées sous la forme d’une chaîne de caractères qui ne va donc pas s’adapter.

Pour régler ce problème, nous allons donc plutôt utiliser la seconde forme proposée pour saisir les coordonnées.

Étant donné que la cellule B10 se trouve sur la dixième ligne et sur la seconde colonne de la feuille de calculs, nous pourrions alors utiliser la formule :

 =INDIRECT($B$6&"!L10C2";FAUX) 

Bien entendu, le résultat obtenu sera identique :

Sauf que maintenant, il est facile de rendre cette formule dynamique, en utilisant les fonctions LIGNE() et COLONNE(), qui permet de récupérer le numéro de ligne et de colonne de la cellule appelante :

  =INDIRECT($B$6&"!L"&LIGNE()&"C"&COLONNE();FAUX)  

Nous pouvons maintenant étendre cette formule sans aucune difficulté, que ce soit sur la ligne :

Ou sur les colonnes :

Et bien entendu, si nous saisissons une autre année, donc une des feuilles du classeur porte le nom, Excel adaptera évidemment le résultat :

Pour finir, il faut également savoir que la fonction INDIRECT() peut être utilisée en tant qu’argument d’une autre fonction :

 =SOMME(INDIRECT(B6&"!B10:D21"))