Excel INDIRECT() : Comment créer des références dynamiques dans vos formules

La fonction INDIRECT() est l'un des outils les plus puissants d'Excel, mais souvent mal compris. Contrairement aux fonctions qui effectuent directement des calculs, INDIRECT() convertit les chaînes de texte en références de cellules vivantes. Cela permet aux utilisateurs de créer des formules dynamiques et flexibles qui s'adaptent à des entrées variables. Que vous construisiez un tableau de bord récapitulatif, que vous fassiez référence à différentes feuilles en fonction des sélections de l'utilisateur ou que vous changiez dynamiquement les références des cellules, INDIRECT() peut changer la donne.

Dans ce guide, je vous montrerai comment utiliser la fonction INDIRECT() pour référencer des cellules et résoudre les problèmes courants, y compris quand éviter d'utiliser la fonction. Si vous n'êtes pas familiarisé avec certaines bases, je vous recommande de suivre notre cours d'introduction à Excel pour acquérir des bases solides. 

Ce que fait la fonction INDIRECT()

La fonction INDIRECT() d'Excel permet de récupérer la valeur d'une cellule ou d'une plage en fonction d'une référence fournie sous la forme d'une chaîne de texte. Au lieu de coder en dur une adresse de cellule comme "A1" dans une formule, INDIRECT() vous permet de définir dynamiquement cette adresse à l'aide de texte, qu'Excel interprète comme une référence vivante.

La syntaxe de la fonction INDIRECT() est présentée ci-dessous :

INDIRECT(ref_text, [a1])

Où ?

• ref_text (obligatoire) : Une chaîne de texte représentant une référence de cellule ou de plage valide. Il peut s'agir d'une cellule comme "A1", d'une plage nommée ou même d'une référence à une autre feuille comme "Sheet2!B5".

• [a1]: Il s'agit d'un argument facultatif qui spécifie le type de référence dans ref_text. Si a1 est TRUE ou omis, ref_text est interprété comme une référence de style A1. Si a1 est FALSE, ref_text est traité comme une référence de type R1C1.

Comment utiliser la fonction INDIRECT()

L'exemple suivant montre comment la fonction INDIRECT() convertit dynamiquement du texte en références de cellules.

Supposons que la cellule C2 contienne le texte "A2" et que la cellule A2 contienne la valeur 100. La fonction ci-dessous renverra également 100 car Excel lit le contenu de C2 et INDIRECT() le traite comme une référence.

=INDIRECT(C2)

Utiliser INDIRECT() pour faire référence à une autre cellule dans Excel. Image par l'auteur.

Si la cellule A5 contient le nombre 5, vous pouvez créer une référence dynamique à l'aide de la formule ci-dessous :

=INDIRECT("B"&A5)

Cette formule combine "B" avec la valeur de la cellule A5 (6), formant ainsi "B6". INDIRECT() renvoie alors la valeur de la cellule B6, qui est "Product A".

Utilisation de INDIRECT() avec & pour le référencement dynamique dans Excel. Image par l'auteur.

Utilisations dynamiques de INDIRECT() dans Excel

La véritable force de la fonction INDIRECT() réside dans sa capacité à construire des références dynamiques. Il est donc idéal pour la modélisation avancée, les tableaux de bord et les rapports pilotés par l'utilisateur. Vous trouverez ci-dessous trois cas d'utilisation clés qui illustrent sa flexibilité.

Référence à d'autres feuilles de travail

Vous pouvez utiliser INDIRECT() pour extraire dynamiquement des données de différentes feuilles en fonction de la saisie d'une cellule. Par exemple, si la cellule A1 contient le nom d'une feuille, comme "Sales". Vous voulez extraire la valeur de la cellule B2 de cette feuille.

La formule ci-dessous construit une référence comme 'Sales'!B2 et renvoie la valeur de cette cellule.

=INDIRECT("'" & A1 & "'!B2")

Utiliser INDIRECT() pour référencer indirectement d'autres feuilles de calcul dans Excel. Image par l'auteur.

Mettez toujours les noms de feuilles entre guillemets simples en utilisant ' s'il est possible qu'ils contiennent des espaces ou des caractères spéciaux, comme 'Sales 2023'!A1.

Plages nommées de référence

Vous pouvez également utiliser la fonction INDIRECT() pour référencer dynamiquement des plages nommées, ce qui est très utile lorsque vous les combinez à des listes déroulantes ou à des entrées utilisateur.

Supposons que vous ayez des plages nommées Sales_2023 et Sales_2023, où la cellule "A1" contient "Sales_2023"

Nommer des plages de cellules dans Excel. Image par l'auteur.

La formule ci-dessous additionne les valeurs de la plage nommée Sales_2023, même si elle n'y fait pas directement référence. Cette méthode est idéale pour les tableaux de bord pilotés par l'utilisateur ou les modèles comportant plusieurs scénarios.

=SUM(INDIRECT(A1))

Utiliser INDIRECT() pour référencer des plages nommées dans Excel. Image par l'auteur.

Références fixes qui ne changent pas

Normalement, si vous insérez ou supprimez des lignes ou des colonnes, les références aux cellules dans les formules s'adaptent automatiquement. Mais la fonction INDIRECT() crée des références absolues qui ne se déplacent pas.

Par exemple, si la cellule "A1" contient 100, les formules =A1 et =INDIRECT("A1") renverront toutes deux 100. Toutefois, si vous déplacez la valeur

Référence directe à une cellule dans Excel. Image par l'auteur.

Utilisation de INDIRECT() pour le référencement des cellules dans Excel. Image par l'auteur.

Toutefois, si vous insérez une nouvelle ligne au-dessus de la ligne 1, D3 (qui était B2) affiche maintenant =A3 puisque la valeur se déplace avec la ligne. La cellule E3 avec la fonction INDIRECT() affiche toujours l'original A2, qui peut maintenant être vide ou avoir une valeur différente (UID).

Utilisation de INDIRECT() pour les références fixes dans Excel. Image par l'auteur.

Exemples avancés et cas d'utilisation

Une fois que vous maîtrisez les bases, la fonction INDIRECT() s'avère très utile dans les scénarios Excel plus avancés, en particulier lorsqu'elle est associée à des fonctions telles que VLOOKUP(), ROW(), et les tableaux dynamiques. Ces combinaisons permettent de créer des modèles encore plus dynamiques et flexibles.

Utiliser INDIRECT() avec VLOOKUP()

Vous pouvez combiner INDIRECT() avec VLOOKUP() pour modifier dynamiquement le tableau de consultation en fonction des données saisies par l'utilisateur.

Supposons que vous ayez deux tableaux, Prices_2022 et Prices_2023. Dans des cellules séparées, G1 : Prices_2022 et G2 : Pomme`. La formule ci-dessous recherche dynamiquement "Apple" dans le tableau de l'année sélectionnée en fonction de la valeur de A1 et renvoie son prix.

=VLOOKUP(G2, INDIRECT(G1), 2, FALSE)

Utilisation de INDIRECT() avec VLOOKUP() dans Excel. Image par l'auteur.

Consultez notre site VLOOKUP() from Another Sheet : Un tutoriel sur Excel pour en savoir plus sur la recherche et l'extraction de données dans différentes feuilles Excel.

INDIRECT() avec référencement R1C1

INDIRECT() peut également utiliser la notation R1C1, ce qui est particulièrement utile pour construire des formules de manière dynamique dans les macros ou pour référencer des positions de manière programmatique.

Par exemple, la formule ci-dessous renvoie la valeur de la ligne 2, colonne 3 (cellule C2). Le deuxième argument (FALSE) indique à Excel que vous utilisez le style R1C1, et non le style A1.

=INDIRECT("R2C3", FALSE)

Utilisation de INDIRECT() avec R1C1 dans Excel. Image par l'auteur.

La formule suivante comprend l'utilisation dynamique de ROW() et COLUMN(). La formule renverra toujours la valeur de la colonne 3 sur la même ligne que celle où la formule est écrite.

=INDIRECT("R" & ROW() & "C3", FALSE)

Utilisation de INDIRECT() avec ROW() et COLUMN() dans Excel. Image par l'auteur.

INDIRECT() et les tableaux dynamiques

Avant qu'Excel n'introduise les tableaux dynamiques, INDIRECT() était une méthode courante pour générer un comportement semblable à celui d'un tableau.

Par exemple, la formule ci-dessous renvoie un tableau : {1; 2; 3; 4; 5}. Cela simule un tableau de 1 à 5 en référençant les lignes indirectement.

=ROW(INDIRECT("1:5"))

Utilisation de INDIRECT() avec ROW() pour les tableaux dynamiques dans Excel. Image par l'auteur.

La méthode alternative moderne consiste à utiliser la fonction SEQUENCE().

=SEQUENCE(5)

Utilisation de SEQUENCE() pour les tableaux dynamiques dans Excel. Image par l'auteur.

L'utilisation de INDIRECT() pour les tableaux dynamiques fonctionne toujours, mais elle est considérée comme moins efficace et moins lisible que SEQUENCE(), FILTER(), et d'autres fonctions de tableaux dynamiques disponibles dans Excel moderne (Office 365 et Excel 2021+).

Précautions et problèmes de performance

La fonction INDIRECT() présente certaines limites importantes, en particulier lorsqu'elle est utilisée dans des classeurs Excel volumineux ou complexes. Ces questions sont notamment les suivantes :

• Fonction volatile (surcharge de calcul): INDIRECT() est une fonction volatile, ce qui signifie qu'elle recalcule chaque fois qu'une modification est apportée dans le classeur, même si cette modification n'a rien à voir avec la formule. Cela peut entraîner un ralentissement des performances.

• Le débogage peut s'avérer difficile: Comme INDIRECT() construit des références à partir du texte, Excel ne peut pas facilement les tracer comme il peut le faire avec des références de cellules standard. Il est donc difficile d'auditer les formules avec la trace des précédents/dépendants, ou de repérer les références erronées lorsque quelque chose change.

• Envisagez des alternatives modernes: Lorsque cela est possible, il est souvent préférable d'utiliser des fonctions non volatiles qui offrent de meilleures performances et une plus grande clarté. Ces méthodes comprennent INDEX() pour la recherche dynamique de cellules, CHOOSE() pour la sélection dynamique de plages ou d'options prédéfinies et SEQUENCE() pour la génération de tableaux numériques dynamiques.

Quand éviter d'utiliser INDIRECT()

Bien que INDIRECT() offre une grande flexibilité, ce n'est pas toujours le meilleur choix. Elle introduit souvent une complexité inutile, des problèmes de performance ou de maintenabilité. Vous trouverez ci-dessous quelques situations à éviter et les meilleures méthodes alternatives.

• Références dynamiques de cellules au sein d'une même feuille: Utilisez INDEX() au lieu de INDIRECT() pour de meilleures performances et une meilleure traçabilité.

• Tableaux dynamiques ou séquences de nombres: Évitez =ROW(INDIRECT("1:10")). Utilisez plutôt =SEQUENCE(10), qui est plus rapide, plus propre et non volatile.

• Quand la traçabilité est importante: Les formules utilisant INDIRECT() ne peuvent pas être tracées avec "Trace Precedents" ou "Evaluate Formula" de manière efficace.

• Référencement d'une cellule ou d'une plage statique: N'utilisez pas INDIRECT("A1") si vous pouvez vous contenter d'écrire =A1.

• Utilisation intensive dans les grandes feuilles de calcul: Comme INDIRECT() est volatile, il se recalcule constamment et peut ralentir considérablement les performances.

INDIRECT() alternatives

Le tableau suivant résume les meilleures alternatives à INDIRECT().

Principaux enseignements

INDIRECT() transforme les chaînes de texte en références vivantes, ce qui en fait un outil puissant pour créer des feuilles de calcul dynamiques et flexibles. Elle est utile pour référencer des feuilles différentes, des plages nommées ou des cellules fixes qui ne doivent pas être modifiées en fonction des changements. Cependant, il s'agit d'une fonction volatile qui recalcule fréquemment et qui peut ralentir les classeurs volumineux. La fonction est également plus difficile à tracer et à déboguer que les fonctions traditionnelles. Utilisez INDIRECT() de manière sélective et envisagez des alternatives plus sûres et plus efficaces.

Je vous recommande de suivre notre cours Analyse de données dans Excel pour découvrir les insights dans les données B2B et le cursus de compétences Fondamentaux d'Excel pour en savoir plus sur les fonctions disponibles dans les dernières versions d'Excel. Notre cours Fonctions Excel avancées est parfait si vous souhaitez explorer les possibilités de référencement et de recherche avancées.