Comment utiliser la formule INDIRECT sur Excel pour dynamiser vos références ?

Comprendre la formule INDIRECT sur Excel

La formule INDIRECT est une fonction Excel qui renvoie la référence spécifiée par une chaîne de texte. En d'autres termes, au lieu d'écrire =A1 pour faire référence à la cellule A1, vous pouvez écrire =INDIRECT("A1"). Cela semble inutile au premier abord, mais la puissance de INDIRECT réside dans sa capacité à utiliser des chaînes de texte dynamiques pour construire la référence.

Syntaxe de la formule INDIRECT

La syntaxe de la formule INDIRECT est la suivante :

=INDIRECT(ref_text; [a1])

• ref_text : C'est l'argument obligatoire. Il s'agit d'une chaîne de texte qui représente une référence de cellule. Cette chaîne peut être:
  • Une référence de cellule simple (par exemple, "A1").
  • Une référence à une plage de cellules (par exemple, "A1:B10").
  • Le nom d'une plage nommée (par exemple, "MaPlage").
  • Une formule qui génère une référence de cellule sous forme de texte.
• [a1] : C'est l'argument facultatif. Il spécifie le style de référence utilisé dans ref_text. Il peut prendre deux valeurs :
  • VRAI (ou omis) : ref_text est interprété comme une référence de style A1 (par exemple, "A1", "B2:C5").
  • FAUX : ref_text est interprété comme une référence de style L1C1 (ligne 1, colonne 1) (par exemple, "R1C1", "R2C3:R5C6"). Le style L1C1 est moins courant.

Pourquoi utiliser la formule INDIRECT ?

INDIRECT est utile dans plusieurs situations :

• Références dynamiques : La principale utilité de INDIRECT est de créer des références qui s'ajustent automatiquement lorsque la structure de votre feuille de calcul change (par exemple, l'ajout ou la suppression de lignes ou de colonnes).
• Travailler avec des plages nommées : INDIRECT permet de référencer des plages nommées de manière dynamique, en utilisant le nom de la plage stocké dans une cellule.
• Construire des références à partir d'autres cellules : Vous pouvez utiliser des formules pour construire la chaîne de texte qui représente la référence, ce qui permet de créer des références complexes basées sur des conditions.
• Accéder à des données dans d'autres feuilles de calcul : Bien que d'autres méthodes soient souvent préférables, INDIRECT peut être utilisé pour accéder à des cellules dans d'autres feuilles de calcul en construisant la chaîne de texte appropriée.

Exemples pratiques de la formule INDIRECT

Voici quelques exemples concrets pour illustrer l'utilisation de la formule INDIRECT.

Exemple 1 : Référence simple à une cellule

Supposons que la cellule A1 contienne le texte "B2". La formule suivante :

=INDIRECT(A1)

renverra la valeur de la cellule B2. Si vous changez le contenu de A1 en "C3", la formule renverra la valeur de la cellule C3.

Exemple 2 : Référence dynamique à une plage de cellules

Imaginez que vous ayez une liste de chiffres dans les cellules A1 à A10 et que vous souhaitiez calculer la somme de ces chiffres. Vous pourriez utiliser la formule SOMME(A1:A10). Cependant, si vous insérez une nouvelle ligne au-dessus de la ligne 1, la plage de la formule ne s'ajustera pas automatiquement. Vous pouvez utiliser INDIRECT pour créer une référence dynamique :

1. Dans la cellule B1, entrez "A1".
2. Dans la cellule B2, entrez "A10".
3. Dans une autre cellule, entrez la formule suivante :

=SOMME(INDIRECT(B1):INDIRECT(B2))

Cette formule calcule la somme des cellules de A1 à A10. Si vous insérez une nouvelle ligne au-dessus de la ligne 1, la formule s'ajustera automatiquement car les cellules B1 et B2 contiennent toujours les références "A1" et "A10". Vous pouvez également modifier les valeurs dans B1 et B2 pour modifier la plage de la somme de manière dynamique.

Exemple 3 : Utilisation de la formule INDIRECT avec des plages nommées

Créez une plage nommée "Chiffres" qui comprend les cellules C1 à C5. Vous pouvez ensuite utiliser la formule suivante pour calculer la somme de cette plage nommée :

=SOMME(INDIRECT("Chiffres"))

Vous pouvez également stocker le nom de la plage dans une cellule (par exemple, la cellule D1 contient le texte "Chiffres") et utiliser la formule suivante :

=SOMME(INDIRECT(D1))

Cela permet de modifier dynamiquement la plage à sommer en changeant simplement le contenu de la cellule D1.

Exemple 4 : Accéder à des données dans une autre feuille de calcul

Supposons que vous ayez une feuille de calcul nommée "Données" et que vous souhaitiez accéder à la cellule A1 de cette feuille. Vous pouvez utiliser la formule suivante :

=INDIRECT("'Données'!A1")

Important : L'utilisation de INDIRECT pour référencer d'autres feuilles de calcul peut rendre votre feuille de calcul plus lente et plus difficile à comprendre. Il est généralement préférable d'utiliser des références directes entre les feuilles de calcul (par exemple, 'Données'!A1) si possible.

Exemple 5 : Combiner INDIRECT avec d'autres fonctions

La puissance de INDIRECT est amplifiée lorsqu'elle est combinée avec d'autres fonctions Excel. Par exemple, vous pouvez l'utiliser avec ADRESSE pour construire dynamiquement des références de cellules.

La fonction ADRESSE renvoie l'adresse d'une cellule sous forme de texte, en fonction des numéros de ligne et de colonne. La syntaxe est : ADRESSE(ligne; colonne; [type_ref]; [a1]; [nom_feuille])

• ligne : Numéro de la ligne.
• colonne : Numéro de la colonne.
• [type_ref] : (Optionnel) Type de référence : 1 = Absolue ($A$1), 2 = Ligne absolue, colonne relative ($A1), 3 = Ligne relative, colonne absolue (A$1), 4 = Relative (A1).
• [a1] : (Optionnel) Style de référence : VRAI ou omis = A1, FAUX = L1C1.
• [nom_feuille] : (Optionnel) Nom de la feuille.

Supposons que vous ayez le numéro de ligne dans la cellule A1 (par exemple, 5) et le numéro de colonne dans la cellule B1 (par exemple, 3). La formule suivante :

=INDIRECT(ADRESSE(A1; B1))

renverra la valeur de la cellule C5 (car la colonne 3 est la colonne C). Si vous changez les valeurs dans A1 et B1, la formule renverra la valeur de la cellule correspondante.

Bonnes pratiques et erreurs à éviter avec la formule INDIRECT

Bonnes pratiques

• Utilisez INDIRECT avec parcimonie : Bien que puissante, INDIRECT peut rendre vos feuilles de calcul plus lentes et plus difficiles à comprendre. Utilisez-la uniquement lorsque cela est nécessaire pour créer des références dynamiques que vous ne pouvez pas obtenir autrement.
• Documentez vos formules : Si vous utilisez INDIRECT, ajoutez des commentaires à vos formules pour expliquer ce qu'elles font. Cela facilitera la compréhension et la maintenance de vos feuilles de calcul.
• Utilisez des noms de plages descriptifs : Lorsque vous utilisez INDIRECT avec des plages nommées, choisissez des noms de plages clairs et descriptifs pour faciliter la compréhension de vos formules.
• Préférez INDEX et EQUIV : Dans de nombreux cas, les fonctions INDEX et EQUIV peuvent remplacer INDIRECT tout en offrant de meilleures performances et une plus grande lisibilité. Explorez ces alternatives avant d'opter pour INDIRECT.

Erreurs à éviter

• Erreurs de syntaxe : Vérifiez attentivement la syntaxe de votre formule INDIRECT. Assurez-vous que la chaîne de texte ref_text est correctement formatée et qu'elle représente une référence de cellule valide.
• Références circulaires : Évitez de créer des références circulaires avec INDIRECT. Une référence circulaire se produit lorsque une formule fait référence à elle-même, directement ou indirectement. Cela peut entraîner des erreurs de calcul et des performances médiocres.
• Utilisation excessive de INDIRECT : Comme mentionné précédemment, l'utilisation excessive de INDIRECT peut ralentir vos feuilles de calcul. Évitez de l'utiliser lorsque d'autres alternatives sont disponibles.
• Oublier les guillemets : N'oubliez pas d'encadrer les références textuelles (comme "A1" ou "MaPlage") avec des guillemets. Si vous faites référence à une cellule contenant le texte de la référence, n'utilisez pas de guillemets.

Alternatives à la formule INDIRECT

Comme mentionné précédemment, les fonctions INDEX et EQUIV sont souvent de meilleures alternatives à INDIRECT, en particulier pour créer des références dynamiques dans des tableaux de données.

INDEX

La fonction INDEX renvoie la valeur d'une cellule dans une plage, en fonction de son numéro de ligne et de son numéro de colonne. Elle est plus performante et plus facile à comprendre que INDIRECT dans de nombreux cas.

EQUIV

La fonction EQUIV renvoie la position d'une valeur dans une plage. Elle peut être utilisée en combinaison avec INDEX pour créer des références dynamiques basées sur la valeur d'une autre cellule.

Par exemple, au lieu d'utiliser INDIRECT pour référencer une colonne en fonction de son nom, vous pouvez utiliser EQUIV pour trouver la position de la colonne, puis utiliser INDEX pour récupérer les données de cette colonne. Cela est généralement plus performant et plus robuste que d'utiliser INDIRECT.

Conclusion

La formule INDIRECT est un outil puissant pour créer des références dynamiques dans Excel. Elle peut être utilisée pour automatiser et flexibiliser vos feuilles de calcul, mais elle doit être utilisée avec parcimonie en raison de son impact potentiel sur les performances. Comprendre la syntaxe de INDIRECT, ses cas d'utilisation et ses alternatives vous permettra de l'utiliser efficacement et d'optimiser vos feuilles de calcul.