Je sais que les experts d'Excel détestent cette fonction, mais c'est toujours mon « arme secrète » préférée.

Mentionnez la fonction INDIRECT dans un forum Excel et vous déclencherez une bagarre. Il est volatile, ce qui signifie qu'il est toujours éveillé et en train de recalculer, ce qui peut transformer une feuille de calcul rapide en un désordre lent. Mais utilisé correctement, c'est l'arme secrète d'un utilisateur expérimenté pour créer des tableaux de bord dynamiques et réactifs.

Bien que sa réputation de cause de décalage soit bien méritée, le véritable danger n'est pas la fonction elle-même : elle l'utilise pour le mauvais travail alors que de meilleures alternatives sont disponibles. Pour maîtriser INDIRECT, vous devez savoir quand adopter sa flexibilité et quand passer à une alternative plus stable.

Comment fonctionne la fonction INDIRECT

La plupart des fonctions Excel sont directes, mais INDIRECT est une carte d'une carte. Considérez-le comme une note sur une porte : au lieu d'entrer dans une maison (une formule standard), la note vous indique de vous rendre dans une maison entièrement différente pour retrouver vos données.

La syntaxe de la fonction INDIRECT d'Excel est simple :

=INDIRECT(ref_text,[a1])

où:

• ref_text est une référence de cellule normale, une référence de style R1C1, une cellule nommée ou une référence à une cellule sous forme de chaîne de texte.
• a1 (facultatif) est une valeur logique où VRAI (ou omis) interprète ref_text comme référence de style A1, et FALSE interprète ref_text comme référence de style R1C1.

INDIRECT est idéal pour créer des listes déroulantes dépendantes

Supposons que vous ayez deux tables nommées dans votre feuille de calcul Excel : T_France et T_USA. Vous souhaitez que les options de votre cellule Ville (F2) changent automatiquement lorsque vous sélectionnez dans la cellule Pays (E2).

Tout d’abord, créez une liste déroulante de pays dans la cellule E2. Sélectionnez cette cellule et dans l'onglet Données, cliquez sur « Validation des données ».

Ensuite, sélectionnez « Liste » dans le champ Autoriser, tapez France,États-Unis dans le champ Source, puis cliquez sur « OK ».

Enfin, sélectionnez la cellule F2 (où iront vos villes) et sélectionnez à nouveau « Liste » dans la boîte de dialogue de validation des données. Cette fois, cependant, vous allez utiliser la fonction INDIRECT dans le champ Source :

=INDIRECT("T_"&E2)

Désormais, lorsque vous sélectionnez « USA » dans la cellule E2, la liste déroulante de la cellule F2 produit une liste de toutes les villes de votre table T_USA. En revanche, sélectionner « France » dans la cellule E2 vous donne une liste des villes françaises dans la cellule F2.

Cela fonctionne grâce à l'esperluette (&) dans votre formule, qui prend le texte statique « T_ » et le colle au mot actuellement dans la cellule E2. INDIRECT prend ensuite cette chaîne concaténée finale et demande à Excel de trouver la table portant ce nom.

En rapport

Tout ce que vous devez savoir sur la validation des données dans Microsoft Excel

Définissez des règles pour contrôler les entrées de données dans Excel.

INDIRECT est la meilleure fonction pour assembler un tableau de bord multi-tables

Imaginez que vous ayez des tableaux de ventes distincts pour différentes années : T_2024 (à gauche) et T_2025 (à droite). Vous souhaitez qu'une cellule récapitulative (J2) additionne automatiquement les ventes en fonction de l'année sélectionnée dans un menu déroulant (I2).

Tout d'abord, créez un troisième tableau nommé T_Years et, dans la seule colonne, répertoriez toutes les années que vous souhaitez voir apparaître dans la liste déroulante de la cellule I2.

Ensuite, vérifiez que les deux tables de données sont nommées de manière cohérente (T_[Year]) et que la colonne à partir de laquelle vous allez extraire les données porte exactement le même libellé (Ventes).

Sélectionnez la cellule I2 et dans l'onglet Données, cliquez sur « Validation des données ».

Dans le champ Autoriser, choisissez « Liste » et dans le champ Source, insérez des références de cellule directes aux années de votre tableau T_Years en les saisissant manuellement comme références absolues ou en les sélectionnant avec votre souris. Ensuite, cliquez sur « OK ».

Maintenant, dans la cellule J2, entrez la formule suivante :

=SUM(INDIRECT("T_"&I2&"[Sales]"))

Cette formule indique à Excel de construire une référence de tableau en utilisant des esperluettes pour combiner le préfixe « T_ », l'année sélectionnée dans la cellule I2 et le nom spécifique. [Sales] colonne. INDIRECT évalue ensuite cette chaîne de texte comme une plage en direct que la fonction SUM peut calculer.

En rapport

Votre feuille de calcul Excel a besoin d'un tableau de bord : voici comment en créer un

Ayez vos KPI au même endroit.

INDIRECT n'est pas toujours la meilleure solution : la fenêtre déroulante

Bien qu'INDIRECT soit idéal pour basculer entre des tables distinctes, comme dans les scénarios ci-dessus, il a un point de rupture. Lorsque vous commencez à l’utiliser pour effectuer des calculs approfondis sur un seul ensemble de données en croissance, la taxe sur la volatilité devient un prix trop élevé à payer.

Supposons que vous ayez une table nommée T_Stocks qui s'agrandit quotidiennement. Vous souhaitez calculer le prix moyen des X dernières entrées, où X est un nombre variable que vous saisissez dans la cellule D3.

Ici, tu pourrait utilisez INDIRECT pour créer manuellement une chaîne de texte comme T_Stocks[Price] et calculez les décalages de ligne pour trouver votre point de départ. Cependant, pour ce type de calcul, j'utilise l'INDEX non volatile, qui ne recalcule que lorsque les données qu'il examine réellement changent.

Voici la formule de la cellule E3 :

=AVERAGE(INDEX(T_Stocks[Price],ROWS(T_Stocks)-(D3-1)):INDEX(T_Stocks[Price],ROWS(T_Stocks)))

Dans ce cas, les deux points (:) entre les deux fonctions INDEX créent la plage dynamique, il n'est donc pas nécessaire d'INDIRECT. Le premier INDEX compte à rebours à partir du bas du tableau et le second INDEX utilise ROWS pour trouver la toute dernière entrée du tableau.

Ensuite, si vous remplacez 5 dans la cellule D3 par 10, la fenêtre s'agrandit instantanément.

En rapport

Pourquoi devriez-vous éviter de coder en dur les valeurs dans les formules Microsoft Excel

Faire référence à des cellules ou à des plages nommées est la voie à suivre.

INDIRECT reste l’une des fonctionnalités d’Excel dont on parle le plus car elle peut être un cauchemar lent et volatile. Mais lorsque vous avez besoin de transformer du texte en une carte fonctionnelle pour des listes déroulantes ou un changement de table, c'est le meilleur outil pour ce travail. Respecter la volatilité est l'un des nombreux moyens essentiels d'accélérer vos feuilles de calcul Excel et de maintenir un classeur réactif.

Système d'exploitation

Windows, macOS, iPhone, iPad, Android

Essai gratuit

1 mois

Microsoft 365 inclut l'accès aux applications Office telles que Word, Excel et PowerPoint sur jusqu'à cinq appareils, 1 To de stockage OneDrive et bien plus encore.