Tutoriel Exce : comment utiliser la fonction INDIRECT

Ce tutoriel Excel explique la syntaxe de la fonction INDIRECT ainsi que ses utilisations de base. Il fournit également un certain nombre d'exemples de formules qui montrent comment utiliser cette fonction dans Excel.

De nombreuses fonctions existent dans Microsoft Excel, certaines étant faciles à comprendre, d'autres nécessitant une longue courbe d'apprentissage. Pourtant, la fonction INDIRECT est unique en son genre. C’est une fascinante fonction intégrée dans Microsoft Excel, qui renvoie la référence spécifiée par une chaîne de texte. Cette référence peut être une cellule ou une plage de cellules. Il peut même s'agir d'un nom de feuille de calcul.

Eh bien, à quoi sert la fonction INDIRECT dans Excel et dans quels cas elle est utilisée ? Suivez ce tutoriel pour avoir des réponses complètes à ces questions et bien d’autres …

Table des matières

Qu'est-ce que la fonction Excel INDIRECT ?

Syntaxe et arguments de la fonction INDIRECT

Utilisation basique de la fonction INDIRECT

Exemples d'utilisation de la fonction INDIRECT dans Excel

Exemple 1: utiliser la référence de cellule dans une cellule pour récupérer la valeur

Exemple 2: création d'une référence à l'aide d'une valeur dans une cellule

Exemple 3: calculez la somme d'une plage de cellules

Exemple 4: création d'une référence à une feuille à l'aide de la fonction INDIRECT

Exemple 5: verrouiller une référence de cellule à l'aide de la fonction INDIRECT

Qu'est-ce que la fonction Excel INDIRECT ?

La fonction Excel INDIRECT renvoie une référence à une plage. Elle n'évalue pas les tests ou conditions logiques. De plus, elle n'effectuera pas de calculs. Fondamentalement, cette fonction permet de verrouiller la cellule spécifiée dans une formule. Pour cette raison, vous pouvez changer une référence de cellule dans une formule sans changer la formule elle-même.

Un autre avantage de la fonction INDIRECT d’Excel est que les références indirectes ne changeront pas même lorsque de nouvelles lignes ou colonnes sont insérées dans la feuille de calcul. De même, les références ne changent pas lorsque vous supprimez les références existantes.

Tout cela peut être plus facile à comprendre à partir d'un exemple. Toutefois, pour pouvoir écrire une formule, même la plus simple, vous devez connaître les arguments de la fonction, non ? Alors, jetons un coup d'œil sur la syntaxe de la fonction INDIRECT d’abord.

Syntaxe et arguments de la fonction INDIRECT

La fonction INDIRECT n'a que deux arguments, le premier est obligatoire et le second est facultatif :

=INDIRECT (réf_texte ; [a1])

• ·Le premier argument est réf_texteet il représente l'argument requis. Il s'agit de la référence à une cellule contenant l'un des éléments suivants:
• Une référence de style A1.
• Une référence de style L1C1.
• Un nom défini comme référence
• Une référence à une cellule sous forme de chaîne de texte.

L'argument « réf_texte » doit être une référence de cellule valide. Sinon, INDIRECT renverra l’erreur #REF!. L'argument « réf_texte » peut faire référence à un autre classeur. Néanmoins, il doit être ouvert ou la formule renverra encore une fois l’erreur REF!.

• ·Le deuxième argument est A1et il est facultatif. Il peut s'agir de l'une des deux valeurs logiques, « VRAI » ou « FAUX ». Son objectif est de spécifier le type de référence de cellule de l'argument « réf_texte ». Si vous omettez cet argument, la formule suppose une valeur « VRAI ».
• Si VRAI, la fonction interprétera l'argument réf_texte comme la référence de style A1.
• Si FAUX, il interprète l'argument réf_texte comme la référence de style L1C1.

Notes complémentaires :

INDIRECT est une fonction volatile. Cela signifie qu'elle recalcule chaque fois que le classeur Excel est ouvert ou chaque fois qu'un calcul est déclenché dans la feuille de calcul. Cela augmente le temps de traitement et ralentit la vitesse de votre classeur. Bien que vous puissiez utiliser cette fonction avec de petits ensembles de données avec peu ou pas d'impact sur la vitesse, vous pouvez la voir ralentir votre classeur lorsque vous l'utilisez avec de grands ensembles de données.

Utilisation basique de la fonction INDIRECT

Prenons l’exemple d’une formule simple qui montre comment utiliser la fonction INDIRECT dans Excel.

Supposons que la cellule B1 contient une chaîne (C2), et la cellule C2 prend la valeur « Amina ». Maintenant, si vous allez utiliser la fonction INDIRECT et passer le texte de référence comme B1, elle renverra la valeur contenue dans la plage de cellules C2 qui est « Amina ».

La fonction INDIRECT d'Excel vous permet de spécifier une adresse de cellule INDIRECTEMENT. Comme vous pouvez le voir dans l'exemple ci-dessus, si la cellule B1 contient le texte C2, cette formule Excel indirecte renvoie le contenu de la cellule C2 (Amina). C’est une fonction extrêmement utile. Vous pouvez alors l’utiliser chaque fois que vous souhaitez modifier la référence à une cellule dans une formule Excel indirecte sans modifier la formule elle-même.

Si vous pensez que cette méthode n'a encore que très peu de sens pratique, suivez le reste de ce tutoriel afin de découvrir d’autres formules qui révèlent la puissance réelle de la fonction INDIRECT d'Excel.

Exemples d'utilisation de la fonction INDIRECT dans Excel

Comme illustré dans l'exemple ci-dessus, vous pouvez utiliser la fonction Excel INDIRECT pour mettre l'adresse d'une cellule dans une autre en tant que chaîne de texte habituelle et obtenir la valeur de la première cellule en référençant la deuxième. Cependant, cet exemple trivial n'est rien d'autre qu'un indice des capacités de cette fonction.

Lorsque vous travaillez avec des données réelles, la fonction INDIRECT peut transformer n'importe quelle chaîne de texte en référence, y compris des chaînes très complexes que vous créez à l'aide des valeurs d'autres cellules et des résultats renvoyés par d'autres formules Excel. Parcourons des exemples de formules Excel indirectes !.

Exemple 1: utiliser la référence de cellule dans une cellule pour récupérer la valeur

Vous pouvez utiliser cette fonction pour extraire la valeur d'une cellule dont la référence est stockée dans la cellule elle-même.

Dans l'exemple ci-dessus, la cellule A1 a la valeur 123.

La cellule C1 a référence à la cellule A1 (sous forme de chaîne de texte).

Maintenant, lorsque vous utilisez la fonction INDIRECT et utilisez C1 comme argument (qui à son tour a une adresse de cellule comme chaîne de texte), cela convertit la valeur de la cellule A1 en une référence de cellule valide.

Et cela signifie également que la fonction fera référence à la cellule A1 et y renverra la valeur.

Notez que vous n'avez pas besoin d'utiliser de guillemets doubles ici car le C1 a la référence de cellule stockée dans le format de chaîne de texte uniquement. De plus, dans le cas où la chaîne de texte dans la cellule C1n'est pas une référence de cellule valide, la fonction INDIRECT renvoie l’erreur #REF!.

Comme vous vous en souvenez, la fonction INDIRECT d’Excel permet les styles de référence A1 et L1C1. Habituellement, vous ne pouvez pas utiliser les deux styles dans une seule feuille à la fois. Vous pouvez uniquement basculer entre les deux types de référence via la case à cocher Fichier > Options > Formules > L1C1. C'est la raison pour laquelle les utilisateurs d'Excel considèrent rarement l'utilisation de L1C1 comme approche de référencement alternative.

Le style A1 est le type de référence par défaut dans Excel qui fait référence à une colonne suivie d'un numéro de ligne. Par exemple, B2 fait référence à la cellule à l'intersection de la colonne B et la ligne 2.

Le style L1C1 est le type de référence opposé - lignes suivies de colonnes, ce qui prend un certain temps pour s’en habituer. Par exemple, L4C1 fait référence à la cellule A4 qui se trouve à la ligne 4, colonne 1 dans une feuille. Si aucun chiffre ne vient après la lettre, alors vous faites référence à toute la ligne ou colonne.

Et maintenant, voyons comment la fonction INDIRECT gère les références A1 et L1C1 :

Comme vous le voyez dans la capture d'écran ci-dessous, trois formules indirectes différentes renvoient le même résultat.

• ·Formule dans la cellule C1: =INDIRECT(B1)

C'est la méthode la plus simple. La formule fait référence à la cellule B1, récupère sa valeur - la chaîne de texte A2, la convertit en référence de cellule, se dirige vers la cellule A2 et renvoie sa valeur, qui est 222.

• ·Formule dans la cellule C3: =INDIRECT(B3; FAUX)

FAUX dans le 2e argument indique que la valeur référencée (B3) doit être traitée comme une référence de cellule L1C1, c'est-à-dire un numéro de ligne suivi d'un numéro de colonne. Par conséquent, notre formule INDIRECT interprète la valeur de la cellule B3 (L2C1) comme une référence à la cellule à la conjonction de la ligne 2 et la colonne 1, qui est la cellule A2.

• ·Formule dans la cellule C5: =INDIRECT(B5; FAUX)

Comme la précédente, cette formule INDIRECT comporte également FAUX dans le deuxième argument, ce qui signifie qu'elle interprète la valeur du premier argument comme une référence L1C1. Ainsi, la formule saisit la valeur dans la cellule B5 (L3C) et la transforme en l'adresse de la cellule B3 (ligne 3 dans la même colonne), puis elle est transmise à l'itinéraire connu.

Exemple 2: création d'une référence à l'aide d'une valeur dans une cellule

De la même façon de création des références à partir de valeurs de cellules, vous pouvez combiner une chaîne de texte et une référence de cellule dans votre formule INDIRECT, liée avec l'opérateur de concaténation (&).

Par exemple, si la cellule B1 contient le nombre 2 et que vous utilisez la formule =INDIRECT("A" & B1), elle se réfère alors à la cellule A2.

Une application pratique de cette méthode pourrait s’imposer lorsque vous souhaitez créer une référence dynamique aux cellules en fonction de la valeur dans une autre cellule.

Si la chaîne de texte que vous utilisez dans la formule donne une référence qu'Excel ne comprend pas, elle renverra l'erreur de référence (#REF!).

Exemple 3: calculez la somme d'une plage de cellules

Vous pouvez également faire référence à une plage de cellules de la même manière avec une seule cellule à l'aide de la fonction INDIRECT dans Excel.

Par exemple, =INDIRECT(“A1:A5”) fait référence à la plage A1: A5.

Vous pouvez ensuite utiliser la fonction SOMME pour trouver le total ou la fonction MOYENNE / MIN / MAX pour effectuer d'autres calculs.

Tout comme la fonction SOMME, vous pouvez également utiliser la même syntaxe pour les autres fonctions :

• =MOYENNE(INDIRECT(“A1:A5”))
• =MAX(INDIRECT(“A1:A5”))
• =MIN(INDIRECT(“A1:A5”))

Maintenant que vous avez une idée générale de l'utilisation de la fonction INDIRECT dans Excel, nous pouvons avancer avec des formules plus puissantes.

Exemple 4: création d'une référence à une feuille à l'aide de la fonction INDIRECT

Les exemples ci-dessus expliquent comment référencer une cellule dans la même feuille de calcul. Toutefois, vous pouvez également utiliser la formule INDIRECT pour faire référence à une cellule dans une autre feuille de calcul ou même un autre classeur.

Cette solution a des implications intéressantes lorsque vous devez configurer des références de cellule à partir de différentes feuilles de calcul dans un classeur. Voici quelques notes que vous devez savoir pour vous référer à d'autres feuilles :

• Supposons que vous avez une feuille de calcul avec le nom Feuil1 et dans la cellule A1 dela feuille vous avez la valeur 123. Si vous allez dans une autre feuille (par ex, Feuil2) et que vous vous référez à la cellule A1 dans Feuil1, la formule sera : =Feuille1!A1

Mais :

• Si vous avez une feuille de calcul qui contient deux ou plus de deux mots (avec un espace entre les deux), et que vous faites référence à la cellule A1 dans cette feuille à partir d'une autre feuille, la formule sera : =’Nom de feuille’!A1. Dans le cas de plusieurs mots, Excel insère automatiquement des guillemets simples au début et à la fin du nom de la feuille.

Voyons maintenant comment créer une fonction INDIRECT pour faire référence à une cellule dans une autre feuille de calcul.

Supposons que vous avez des données importantes dans la feuille 1 et souhaitez extraire ces données dans la feuille 2. La capture d'écran suivante montre comment une formule Excel indirecte peut gérer cette tâche :

Séparons la formule que vous voyez dans la capture d'écran pour la comprendre :

Comme déjà mentionné, la façon habituelle de référencer une autre feuille dans Excel consiste à écrire le nom de la feuille suivi du point d'exclamation et d'une référence de cellule / plage, comme NomFeuille!Plage. Étant donné que le nom de la deuxième feuille dans cet exemple contient un espace, vous feriez mieux de le placer (le nom, pas un espace) entre guillemets simples pour éviter une erreur, par exemple ‘Nouvelle feuille’!$A$1.

Et maintenant, tout ce qu’il vous reste à faire est de saisir le nom de la feuille dans une cellule (A1), l'adresse de la cellule dans une autre (A2), de les concaténer dans une chaîne de texte et de transmettre cette chaîne à la fonction INDIRECT. N'oubliez pas que dans une chaîne de texte, vous devez mettre chaque élément autre qu'une adresse ou un numéro de cellule entre guillemets et puis lier tous les éléments ensemble à l'aide de l'opérateur de concaténation (&).

Compte tenu de ce qui précède, vous obtenez le modèle suivant :

INDIRECT("'" & Nom de la feuille & "'!" & Cellule pour extraire les données)

Pour revenir à notre exemple, mettez le nom de la feuille « Nouvelle feuille » dans la cellule A1 et tapez l’adresse de la cellule dans la cellule A2, comme illustré dans la capture d'écran ci-dessus. En conséquence, vous obtenez la formule suivante :

INDIRECT("'" & $A$1 & "'!" & A2)

Notez également que si vous copiez la formule dans plusieurs cellules, vous devez verrouiller la référence au nom de la feuille en utilisant les références de cellule absolues comme $A$1.

Exemple 5: verrouiller une référence de cellule à l'aide de la fonction INDIRECT

Généralement, lorsque vous ajoutez ou supprimez des lignes ou des colonnes dans Excel, les références de cellule changent automatiquement. Si vous souhaitez empêcher ce désagrément, la fonction INDIRECT vous sera très utile.

Dans la capture d'écran ci-dessous, j'ai utilisé la fonction INDIRECT pour A2 et donné une référence simple pour A3.

Lorsque j'insère une nouvelle colonne, la cellule égale à l'opérateur logique renvoie toujours 20, car sa formule a été automatiquement modifiée en =B3.

Cependant, la cellule avec la formule INDIRECT renvoie maintenant 0, car la formule n'a pas été modifiée lorsqu'une nouvelle ligne a été insérée et elle fait toujours référence à la cellule A1, qui est actuellement vide :

La fonction Excel INDIRECT est donc utile dans un scénario où nous ne voulons pas que la formule soit modifiée automatiquement.

Maintenant que vous connaissez les forces et les limites de la fonction INDIRECT d’Excel, il est temps de commencer à l’appliquer et de voir comment elle peut simplifier vos tâches Excel.

Télécharger le fichier excel de ce tutoriel