Excel est un outil puissant qui regorge de fonctionnalités parfois méconnues, qui peuvent grandement simplifier votre travail. L’une de ces fonctionnalités est la fonction INDIRECT, qui peut vous sembler intimidante au premier abord, mais qui en réalité est assez simple. Dans cet article, nous allons vous expliquer comment utiliser cette fonction efficacement.
Qu’est-ce que la fonction INDIRECT dans Excel ?
Le fonction INDIRECT dans Excel, c’est comme un GPS qui vous guide vers la cellule ou la plage exacte dont vous avez besoin dans votre formule, et ce, même si elle change de place. En utilisant INDIRECT, vous donnez à Excel une « adresse » sous forme de texte, pour lui permettre de trouver la cellule correspondante pour vous. C’est idéal pour les feuilles de calcul où les références doivent être flexibles et facilement ajustables.
Comment utiliser la fonction INDIRECT dans Excel ?
La fonction INDIRECT comporte 2 paramètres, dont un seul est obligatoire.
Voici la structure de la fonction INDIRECT :
= INDIRECT(ref_texte, [a1])
Ref_texte est un paramètre obligatoire qui indique à Excel à quel endroit dans le fichier il doit aller récupérer la donnée.
A1 est un paramètre facultatif, qui devra être renseigné dans le cas où la référence est de style R1C1 ou L1C1. Pour en savoir plus sur les styles de référence, veuillez consulter cet article : Des chiffres ou des lettres dans vos entêtes de colonnes Excel?
Pour illustrer le tout, prenons l’exemple suivant :
Admettons que je souhaite aller récupérer, via la fonction INDIRECT, les ventes de janvier. Que pourrais-je indiquer à Excel comme « coordonnées GPS » (si on continue avec notre métaphore) pour lui indiquer où aller chercher cette donnée ?
Je pourrais lui dire d’aller regarder dans la cellule C3, ou encore je pourrais lui dire d’aller regarder dans la cellule que j’aurais nommée, par exemple, Janvier. Ce sont des variables comprises dans ces indications que nous avons inscrites textuellement dans la colonne E et qui seront récupérées par la fonction INDIRECT pour afficher le résultat dans la colonne G.
Ainsi,
=INDIRECT(E4) permettra d’afficher la donnée inscrite dans la cellule C3 puisque nous avons inscrit C3 comme valeur dans la cellule E4.
=INDIRECT(“C”&E5) permettra d’arriver au même résultat puisque nous avons inscrit 3 comme valeur dans E5, et donc la fonction ira récupérer la donnée dans la colonne C, à la ligne 3.
=INDIRECT(E6) permettra également d’arriver au même résultat, à condition que nous ayons nommé la cellule qui contient la donnée à afficher exactement de la même manière que nous l’avons écrite dans la cellule E6.
À ce stade-ci, vous vous dites probablement que, dans le cadre de cet exemple, il aurait été plus simple de faire une référence directe à la cellule C3. Vous avez bien raison !
Alors, maintenant que cet exemple simple vous a permis de mieux comprendre le fonctionnement de la fonction INDIRECT, voyons, concrètement, de quelles façons cette fonction est couramment utilisée dans la pratique.
Quand utiliser la fonction INDIRECT dans Excel ?
Voici quelques cas d’utilisation concrets pour lesquels INDIRECT peut être une fonction particulièrement utile.
-
Les listes déroulantes dynamiques
Imaginons que plusieurs personnes collaborent sur un même fichier et que vous souhaitez standardiser la manière dont les pays et les villes seront renseignées dans votre fichier. Vous pourriez vouloir utiliser des listes déroulantes pour obliger les collaborateurs à sélectionner une donnée, plutôt qu’à l’entrer manuellement dans la cellule. Cependant, vous souhaitez éviter que, par inadvertance, quelqu’un sélectionne par exemple Canada comme pays et France comme ville puisque cela ne ferait aucun sens.
Voici les données avec lesquelles nous allons travailler :
Vous souhaitez donc que la liste déroulante des villes affiche seulement celles qui sont dans le pays sélectionné. Voici une manière d’y arriver :
- Bâtir la première liste déroulante (dans ce cas-ci la liste déroulante des pays).
- Nommer les 3 plages de cellules qui contiennent les villes. (La plage I6:I9 doit se nommer USA, la plage J6:J8 doit se nommer Canada et la plage K6:K8 doit se nommer France).
- Bâtir la deuxième liste déroulante, qui sera dépendante de la valeur sélectionnée dans la première liste déroulante, en utilisant la fonction INDIRECT. Dans ce cas-ci, la formule serait =INDIRECT($B$6) ou =INDIRECT(B6) si on souhaite copier la formule pour créer une table de données et que celle-ci soit relative à la ligne sur laquelle on se trouve.
-
Les calculs à partir de références dynamiques
Supposons que vous avez, dans des tableaux Excel, les données de ventes par mois des 3 dernières années. Vous souhaitez permettre aux utilisateurs de ce fichier de choisir l’année à analyser à partir d’une liste déroulante. Vous souhaitez également leur offrir la possibilité d’analyser les données à la fois sous la forme d’une somme et d’une moyenne.
Voici une manière d’y arriver :
- Nommer les tableaux qui contiennent les données de chaque année.
- Créer une liste déroulante qui contient les noms des différents tableaux.
- Dans une cellule, indiquer la formule suivante, qui utilise la fonction SOMME en combinaison avec la fonction INDIRECT : =SOMME((INDIRECT($C$20 & “[Ventes]”)))
Ici, la fonction SOMME calcule la somme des valeurs de la plage spécifiée par la fonction INDIRECT. La fonction INDIRECT, quant à elle, renvoie une chaîne de texte qui fait référence à un des tableaux du fichier. Par exemple, lorsque la valeur sélectionnée dans la liste déroulante qui se trouve dans la cellule C20 est Ventes2023, la fonction INDIRECT renvoie “Ventes2023[Ventes]”, ce qui indique à la fonction SOMME d’aller additionner les valeurs de la colonne Ventes du tableau nommé Ventes2023.
- Dans une autre cellule, indiquer la formule suivante, qui utilise la fonction MOYENNE en combinaison avec la fonction INDIRECT : =MOYENNE((INDIRECT($C$20 & “[Ventes]”)))
Suivant le même principe que pour la formule à l’étape précédente, cette formule calculera la moyenne des valeurs de la plage spécifiée par la fonction INDIRECT, qui variera en fonction de la valeur sélectionnée dans la liste déroulante.
Cet exemple démontre la puissance de la fonction INDIRECT dans un contexte où vous voulez faire une opération sur différentes colonnes ou tables de données en fonction d’une valeur dynamique (ici le contenu de la cellule C20) sans avoir à modifier manuellement la formule. Ainsi, l’utilisateur n’a qu’à sélectionner l’année souhaitée dans une liste déroulante pour voir instantanément les sommes et moyennes correspondantes, rendant l’analyse des données rapide et efficace.
-
Les sommaires exécutifs faisant référence à des feuilles multiples
Si vous avez un fichier Excel avec plusieurs feuilles représentant différentes périodes ou départements, et que vous souhaitez créer un sommaire exécutif qui compile des données de toutes ces feuilles, INDIRECT peut simplifier cette tâche. Pour en apprendre plus, consulter cet article qui détaille la procédure à suivre : Excel: La fonction INDIRECT pour créer des sommaires exécutifs en un clic de souris
Les inconvénients de la fonction INDIRECT
Bien que la fonction INDIRECT puisse être très utile dans certaines situations, il faut rester vigilants et l’utiliser avec parcimonie, puisque cette fonction comporte également son lot d’inconvénients.
D’abord, il faut noter que cette fonction est volatile, ce qui signifie qu’elle est recalculée chaque fois qu’une modification est apportée au classeur. Cela peut donc ralentir les performances, surtout lorsque le fichier est volumineux et comporte plusieurs fonctions INDIRECT.
De plus, pour les utilisateurs d’Excel moins expérimentés, les formules complexes utilisant la fonction INDIRECT peuvent être difficiles à comprendre, ce qui peut rendre la maintenance plus ardue. En conséquence, il est important de bien comprendre et de bien documenter l’utilisation que vous faites de cette fonction dans vos fichiers.
Conclusion
La fonction INDIRECT est un outil très puissant pour ceux qui veulent plus de flexibilité dans leurs feuilles de calcul Excel. Que ce soit pour créer des références dynamiques, gérer des listes déroulantes dépendantes, ou compiler des données sur plusieurs feuilles, cette fonction peut grandement simplifier votre travail tout en réduisant les risques d’erreurs. Toutefois, il est important de l’utiliser avec discernement, en tenant compte de son impact potentiel sur la performance de vos fichiers.
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
Si vous souhaitez en apprendre davantage sur la fonction INDIRECT et les manières de l’utiliser efficacement, nous vous invitons à suivre la formation Excel Modélisation financière (niveau 1), qui traite de la fonction INDIRECT, ainsi que de plusieurs autres fonctions Excel nécessaires à l’élaboration de modèles financiers dynamiques et performants.
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
