Découvrez la puissance de la fonction INDIRECT Excel : Un guide complet
La fonction INDIRECT Excel est l’une des fonctionnalités les plus puissantes et polyvalentes d’Excel, offrant des possibilités infinies pour manipuler et référencer des données. Dans cet article, nous vous guiderons à travers les concepts fondamentaux de la fonction INDIRECT, ses différentes utilisations pratiques, et comment l’utiliser efficacement pour améliorer votre productivité et vos analyses de données.
Syntaxe de la fonction Indirect
Généralement, une formule Excel pointe vers une cellule précise ou une plage de cellules. Dans certains cas, il est nécessaire que la formule pointe vers une cellule différente en fonction du résultat du calcul. Il est possible d’effectuer cette opération à l’aide de la fonction INDIRECT.
Cette fonction envoie la référence spécifiée par une chaîne de caractères. Les références sont immédiatement évaluées afin d’afficher leur contenu. Utilisez la fonction INDIRECT lorsque vous voulez modifier la référence à une cellule à l’intérieur d’une formule sans modifier la formule à proprement parler.
La syntaxe est la suivante =INDIRECT(adresse_de_la_plage)
adresse_de_la_plage est l’adresse de la cellule ou de la plage dont vous voulez obtenir la référence, données sous la forme d’une chaîne de caractères, par exemple :
Pour le premier exemple =INDIRECT(« A2 ») renvoie le contenu de la cellule A2.
Pour le second exemple =INDIRECT(« A3 ») renvoie le contenu de la cellule A3.
Pour le troisième exemple =INDIRECT(« A » & A5) renvoie le contenu de la cellule A2. En effet, « A » & A5 est la concaténation de A et du contenu de la cellule A5, c’est à dire A2.
Pour concaténer du texte sur Excel, il est possible de le faire de différentes manières :
Utilisation de la fonction CONCAT
La fonction =CONCAT(« A »;A5) qui renvoie la chaine de caractère : « A » et le contenu de la cellule A5 qui est 2, ce qui donne : « A2 ».
Utilisation de l’opérateur &
Pour aller plus vite, il est possible de se passer de la fonction CONCAT et d’utiliser l’opérateur & : « A » & A5 renvoie également « A2 »
Intérêt de la fonction INDIRECT
Une fois que l’on possède une formule qui crée un texte qui ressemble à la référence d’une cellule, on utilise cette formule comme argument de la fonction INDIRECT. Excel renvoie la valeur pointée par la formule texte.
La fonction INDIRECT offre énormément de possibilités. La plus simple consiste à rendre les références de plage « variables » à l’intérieur des formules. Par exemple, il est possible d’utiliser cette fonction dans la définition des zones de liste et ainsi de rendre variable le contenu d’une zone de liste en fonction du choix d’une première sélection.
Utilisation de la formule INDIRECT en utilisant la référence des feuilles de calcul
La fonction INDIRECT permet également de créer des références de feuilles de calcul dynamiques. Vous pouvez utiliser cette fonction pour accéder à des données provenant de feuilles de calcul différentes ou pour construire des formules qui se réfèrent à des feuilles de calcul spécifiques en fonction de certaines conditions. .
Nous cherchons à réaliser une synthèse dans un tableau à partir d’un fichier Excel qui est monté avec les ventes des produits par mois. Chaque onglet représente un mois.
Tous les onglets ont la même structure, c’est un point primordial pour la suite de l’explication.
Le chiffre d’affaire de Janvier du produit 1 se trouve à l’onglet Janvier dans la cellule D2.
Le chiffre d’affaire de Février du produit 3 se trouve à l’onglet Février dans la cellule D4 etc …
L’objectif est de construire ce tableau de synthèse sans pointer sur chaque cellule des onglets mois par mois
Pour avoir la donnée du Produit 1 pour Janvier, il est possible de saisir : =INDIRECT(« Janvier!D2 »).
Janvier!D2 signifie la cellule D2 sur l’onglet Janvier. Il faut bien noter le ! entre Janvier et D2.
L’idée est d’utiliser l’en-tête de colonne du tableau pour faire référence au bon mois.
La formule sera =INDIRECT(B1 & « !D2 »).
Un petit ajustement doit avoir lieu à ce stade car l’idée est de mettre en place une seule formule et de la copier pour tous les produits et tous les mois. Donc il va falloir transformer B1 en B$1. je vous renvoie au tuto sur les références absolues pour l’explication.
A ce stade la formule utilisée est =INDIRECT(B$1 & « !D2 »)
Pour pouvoir copier la formule sur la hauteur, c’est à dire pour tous les produits, il faut faire en sorte que la ligne change lorsque la copie est réalisée. Il faut utiliser la fonction LIGNE() qui renvoie la ligne de chaque cellule.
La formule définitive sera =INDIRECT(B$1 & « !D » & LIGNE())
Cette formule peut être copiée verticalement et horizontalement pour renvoyer l’ensemble des données pour tous les produits et tous les mois.
Utilisation de la fonction INDIRECT avec d’autres fonctions Excel
La fonction INDIRECT peut être combinée avec d’autres fonctions Excel pour réaliser des tâches avancées. Par exemple, vous pouvez utiliser INDIRECT avec des fonctions telles que SOMME, MOYENNE, SI, et bien d’autres encore, pour effectuer des calculs dynamiques et obtenir des résultats précis.
L’exemple ci-dessous est un tableau avec des résultats par mois. La sélection de la plage sur laquelle doit porter la somme est commandée par les mois indiqués en E4 et E6.
La formule qui est utilisée =SOMME(INDIRECT(« B » & E4+1 & »:B » & E6+1)) permet de sélectionner la plage de cellules sur laquelle se fait la somme.
Dans l’exemple précédent, la somme sera réalisée en fonction des valeur saisie en E4 (premier mois de l’analyse) et E6 (dernier mois de l’analyse).
Il suffit de changer les valeurs dans ces cases pour que la somme porte sur un plage différente.
INDIRECT(« B » & E4+1 & »:B » & E6+1) renvoie INDIRECT(« B3:B6 »)
La fonction somme sera appliquée à la plage : B3:B6
Utiliser la fonction INDIRECT pour rendre des listes dépendantes
En utilisant le principe de la fonction INDIRECT, il est possible de rendre des listes dépendantes.
Par exemple dans une litse contenue dans une cellule, le choix d’une région est fait.
Dans la liste suivante qui permet de sélectionner un département, il faut que seuls les départements appartenants à la région sélectionnée apparaissent.
Un exemple est présenté dans l’article : listes déroulantes dépendantes
Conclusion sur la fonction INDIRECT
La fonction INDIRECT est un outil puissant qui ouvre de nombreuses possibilités dans Excel. Dans cet article, nous avons exploré différentes utilisations pratiques de la fonction INDIRECT, en vous fournissant des exemples concrets pour chaque type d’utilisation. Nous espérons que ces exemples vous ont aidé à mieux comprendre comment exploiter pleinement la fonction INDIRECT et à découvrir son potentiel pour automatiser des tâches, réaliser des calculs avancés et améliorer votre productivité dans Excel. N’hésitez pas à expérimenter et à appliquer ces concepts dans vos propres projets pour tirer le meilleur parti de la fonction INDIRECT. Cette fonctionnalité peut être traité lors de nos accompagnement sur mesure en tête en tête (en ligne ou en présentiel) : coaching Excel personnalisé.
