Comment maîtriser la fonction INDIRECT INDIRECT sur Excel ?

Comprendre la fonction INDIRECT sur Excel

La fonction INDIRECT est une fonction de recherche et de référence dans Excel. Elle prend une chaîne de texte comme argument et la convertit en une référence de cellule. Sa syntaxe de base est la suivante:

=INDIRECT(réf_texte; [a1])

• réf_texte : C'est la chaîne de texte qui représente l'adresse de la cellule à laquelle vous voulez faire référence. Cette chaîne peut être une adresse de cellule simple (par exemple, "A1"), une plage de cellules (par exemple, "A1:B10"), ou même un nom défini. L'astuce est que cette chaîne peut être construite dynamiquement à partir d'autres cellules ou formules.
• [a1] : Cet argument optionnel indique le style de référence utilisé. Si VRAI (ou omis), Excel interprète réf_texte comme une référence de style A1 (par exemple, "A1", "B2", etc.). Si FAUX, Excel interprète réf_texte comme une référence de style L1C1 (Ligne 1, Colonne 1). Par exemple, "L1C1" ferait référence à la cellule A1. Dans la plupart des cas, vous voudrez laisser cet argument omis ou le définir sur VRAI.

Exemples simples d'utilisation de INDIRECT

1. Référence directe à une cellule :

   =INDIRECT("A1") Retourne la valeur de la cellule A1.

2. Référence à une cellule dont l'adresse est dans une autre cellule :

   Si la cellule B1 contient le texte "A1", alors =INDIRECT(B1) retourne la valeur de la cellule A1. Cela peut sembler inutile dans cet exemple, mais imaginez que la valeur de B1 change dynamiquement en fonction d'autres calculs.

3. Référence à une plage de cellules :

   =SOMME(INDIRECT("A1:A10")) Calcule la somme des valeurs des cellules A1 à A10.

Les avantages de l'utilisation de INDIRECT

• Flexibilité : Permet de créer des références dynamiques qui s'adaptent aux changements dans votre feuille de calcul.
• Lisibilité : Peut rendre les formules complexes plus faciles à comprendre en utilisant des noms définis ou des références textuelles descriptives.
• Automatisation : Facilite l'automatisation des tâches répétitives en construisant des références de cellules de manière programmatique.

Le pouvoir de INDIRECT INDIRECT : une double dose de dynamisme

Maintenant, passons à la partie la plus intéressante : l'utilisation de INDIRECT à l'intérieur d'une autre fonction INDIRECT. L'expression INDIRECT INDIRECT peut sembler intimidante, mais elle est simplement une application imbriquée de la fonction INDIRECT. En d'autres termes, la première fonction INDIRECT renvoie une chaîne de texte, et la deuxième fonction INDIRECT interprète cette chaîne comme une référence de cellule.

La syntaxe est donc : =INDIRECT(INDIRECT(réf_texte))

L'intérêt principal de cette imbrication est d'obtenir une référence de cellule qui dépend non pas d'une seule, mais de deux étapes d'interprétation textuelle. C'est particulièrement utile dans les cas suivants:

• Référence indirecte à un nom défini : Vous avez un nom défini qui contient une autre référence, et vous voulez accéder à la cellule pointée par cette deuxième référence.
• Construction de références complexes basées sur des données variables : Vous devez construire une référence de cellule en fonction de plusieurs critères ou conditions.
• Accéder à des feuilles de calcul dont le nom est déterminé dynamiquement : Vous pouvez utiliser INDIRECT INDIRECT pour construire une référence à une cellule dans une feuille dont le nom est stocké dans une autre cellule ou calculé par une formule.

Exemples pratiques de INDIRECT INDIRECT

Exemple 1: Référence indirecte à un nom défini

1. Définir un nom : Allez dans l'onglet Formules > Gestionnaire de noms. Créez un nom appelé NomCellule. Définissez sa valeur comme étant la cellule A1.
2. Définir un deuxième nom : Créez un autre nom appelé NomIndirect. Définissez sa valeur comme étant le texte "=NomCellule". Notez bien les guillemets, qui transforment la référence en texte.
3. Utiliser la formule : Dans une cellule quelconque, entrez la formule =INDIRECT(INDIRECT(NomIndirect)). Cette formule va d'abord évaluer INDIRECT(NomIndirect) ce qui va retourner le texte =NomCellule. Ensuite, la fonction INDIRECT extérieure va évaluer le texte =NomCellule, qui va donc retourner la valeur de la cellule A1.

Exemple 2: Construction de références complexes

Imaginez un scénario où vous avez plusieurs tableaux de données sur différentes feuilles de calcul, et vous voulez extraire une valeur spécifique en fonction du nom du tableau et du nom de la colonne.

• Feuille "Paramètres" :
  • Cellule A1 : Nom du tableau (par exemple, "TableauVentes")
  • Cellule A2 : Nom de la colonne (par exemple, "ChiffreAffaires")
• Feuille "TableauVentes" :
  • Contient un tableau nommé "TableauVentes" avec des colonnes telles que "Produit", "Date", "ChiffreAffaires", etc.

La formule suivante utilise INDIRECT INDIRECT pour extraire la valeur correspondante :

=INDIRECT(INDIRECT("'"&Paramètres!A1&"'!["&Paramètres!A2&"]"))

Explication étape par étape :

1. Paramètres!A1 : Récupère le nom du tableau (par exemple, "TableauVentes") depuis la cellule A1 de la feuille "Paramètres".
2. Paramètres!A2 : Récupère le nom de la colonne (par exemple, "ChiffreAffaires") depuis la cellule A2 de la feuille "Paramètres".
3. "'"&Paramètres!A1&"'!["&Paramètres!A2&"]" : Concatène les chaînes de texte pour former une référence de cellule valide au format Excel. Dans notre exemple, cela donnera : 'TableauVentes'![ChiffreAffaires]
4. INDIRECT(...) (la fonction INDIRECT intérieure) : Interprète la chaîne de texte créée à l'étape 3 comme une référence à une colonne nommée dans un tableau structuré. Cela retourne une référence à la colonne "ChiffreAffaires" du tableau "TableauVentes".
5. INDIRECT(...) (la fonction INDIRECT extérieure) : Interprète la référence retournée par la fonction INDIRECT intérieure et renvoie la première valeur de cette colonne (en supposant que vous voulez la première ligne). Pour retourner une ligne spécifique, il faudrait ajouter des indices (par exemple INDEX(INDIRECT(...), 2) pour la deuxième ligne).

Cet exemple montre comment INDIRECT INDIRECT peut être utilisé pour construire des références très complexes basées sur des données variables.

Exemple 3: Accéder à des feuilles de calcul dynamiquement

Supposons que vous ayez plusieurs feuilles de calcul nommées "Janvier", "Février", "Mars", etc., et que vous vouliez extraire une valeur de la cellule A1 de la feuille correspondant au mois actuel.

1. Obtenir le nom du mois actuel : Vous pouvez utiliser la fonction TEXTE pour obtenir le nom du mois actuel à partir de la fonction AUJOURDHUI():

   =TEXTE(AUJOURDHUI(); "MMMM") Cette formule renvoie le nom du mois actuel (par exemple, "Mai").

2. Construire la référence : Utilisez INDIRECT pour construire la référence à la cellule A1 de la feuille correspondant au mois actuel :

   =INDIRECT("'"&TEXTE(AUJOURDHUI(); "MMMM")&"'!A1")

3. Ajouter une deuxième couche d'INDIRECT (bien que pas strictement nécessaire ici) : On peut encapsuler la formule précédente dans une deuxième fonction INDIRECT:

=INDIRECT(INDIRECT("'"&TEXTE(AUJOURDHUI(); "MMMM")&"'!A1"))

Dans cet exemple, la deuxième fonction INDIRECT ne fait qu'ajouter une couche d'abstraction supplémentaire. Elle n'est pas essentielle pour que la formule fonctionne, mais elle peut être utile dans des scénarios plus complexes où la première fonction INDIRECT renvoie une chaîne de texte qui doit être encore interprétée.

Bonnes pratiques lors de l'utilisation de INDIRECT INDIRECT

• Utiliser des noms définis : Les noms définis rendent les formules plus lisibles et plus faciles à maintenir. Au lieu d'utiliser des références de cellules directes dans vos formules INDIRECT, utilisez des noms définis pour représenter ces références.
• Tester vos formules : Les formules INDIRECT peuvent être difficiles à déboguer. Testez vos formules soigneusement pour vous assurer qu'elles renvoient les résultats attendus.
• Documenter vos formules : Ajoutez des commentaires à vos formules pour expliquer leur fonctionnement. Cela facilitera la compréhension et la maintenance de vos feuilles de calcul.
• Éviter les références circulaires : Assurez-vous que vos formules INDIRECT ne créent pas de références circulaires, car cela peut entraîner des erreurs de calcul.

Erreurs courantes à éviter avec INDIRECT INDIRECT

• Oublier les guillemets : N'oubliez pas d'encadrer les références de cellules textuelles avec des guillemets doubles (").
• Erreurs de syntaxe : Vérifiez attentivement la syntaxe de vos formules INDIRECT. Une erreur de syntaxe peut entraîner des erreurs de calcul ou des messages d'erreur.
• Noms de feuilles incorrects : Assurez-vous que les noms de feuilles que vous utilisez dans vos formules INDIRECT sont corrects et qu'ils existent réellement.
• Références non valides : Vérifiez que les références de cellules que vous utilisez dans vos formules INDIRECT sont valides et qu'elles ne contiennent pas d'erreurs.

Alternatives à la fonction INDIRECT

Bien que INDIRECT soit une fonction puissante, elle peut aussi rendre les feuilles de calcul plus complexes et plus difficiles à comprendre. Dans certains cas, il existe des alternatives plus simples et plus efficaces pour atteindre le même objectif.

• INDEX et EQUIV : La combinaison des fonctions INDEX et EQUIV est souvent une alternative plus performante et plus facile à comprendre que INDIRECT. INDEX permet de récupérer une valeur à partir d'une plage en spécifiant un numéro de ligne et de colonne, tandis que EQUIV permet de trouver la position d'une valeur dans une plage.
• DECALER : La fonction DECALER permet de renvoyer une référence à une plage qui est décalée d'un certain nombre de lignes et de colonnes par rapport à une référence de départ. Elle peut être utile pour créer des références dynamiques qui s'adaptent aux changements dans votre feuille de calcul.
• TABLEAUX STRUCTURÉS : Les tableaux structurés (introduits dans Excel 2007) offrent une manière plus intuitive et plus robuste de faire référence à des données dans un tableau. Vous pouvez utiliser les noms de colonnes et les noms de tableaux directement dans vos formules, sans avoir besoin de recourir à INDIRECT.

L'avantage des tableaux structurés est qu'ils s'ajustent automatiquement lorsque vous ajoutez ou supprimez des lignes et des colonnes. Cela évite d'avoir à modifier manuellement vos formules.

Choisir la bonne alternative dépendra de la complexité de votre feuille de calcul et de vos besoins spécifiques. Dans de nombreux cas, l'utilisation de INDEX et EQUIV ou de tableaux structurés sera préférable à INDIRECT pour une meilleure performance et une plus grande clarté.