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

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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.

Excel formation - Référence dynamique - 01

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) 

Excel formation - Référence dynamique - 02

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

Excel formation - Référence dynamique - 03

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 ! :

Excel formation - Référence dynamique - 04

 

 

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 :

Excel formation - Référence dynamique - 05

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 :

Excel formation - Référence dynamique - 06

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

Excel formation - Référence dynamique - 07

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 :

Excel formation - Référence dynamique - 08

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 :

Excel formation - Référence dynamique - 09

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 :

Excel formation - Référence dynamique - 10

Ou sur les colonnes :

Excel formation - Référence dynamique - 11

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 :

Excel formation - Référence dynamique - 12

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

Excel formation - Référence dynamique - 13

 

 



Articles qui pourraient vous intéresser

Comment créer une référence automatiquement sur Excel ? La fonction DECALER()
Comment extraire les plus petites et plus grandes valeurs avec Excel ? Les fonctions PETITE.VALEUR() et GRANDE.VALEUR()
Comment mettre en place une référence dynamique ? La fonction INDIRECT() d’Excel
Comment calculer le PGCD et le PPCM (« Plus Grand Commun Diviseur » et « Plus Petit Commun Multiple ») sur Excel ?
Comment utiliser la fonction SOMMEPROD d’Excel
Comment nettoyer une chaîne de caractères - Supprimer les espaces indésirables et les caractères illisibles sur Excel ?
Comment compter le nombre de feuilles, de lignes ou de colonnes Excel ?
Comment extraire des parties de texte avec les fonctions DROITE() - GAUCHE() - STXT() d'Excel
Comment effectuer un test sur Excel : la fonction SI() (ainsi que ET() et OU())
Comment convertir des unités de mesure sur Excel ? La fonction CONVERT()
Comment modifier le résultat d’une formule renvoyant une erreur : la fonction SIERREUR() d’Excel ?
Comment obtenir des informations sur le classeur (nom et répertoire d’enregistrement) et sur les cellules (nom de l’onglet,…) sur Excel ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2021 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.