Comment utiliser la fonction INDIRECT dans Google Sheets ?

Les INDIRECT dans Google Sheets prend l'adresse de la cellule sous forme de texte et renvoie une référence de cellule. Elle fonctionne à l'inverse de la fonction ADRESSE qui renvoie une adresse au format texte.

La différence entre la fonction INDIRECT et une fonction directe classique est qu'une fonction classique fait directement référence à une cellule (ou à une plage de cellules) dans la formule. En revanche, la fonction INDIRECT utilise l'adresse de la cellule (sous la forme d'une chaîne de texte) pour trouver la cellule concernée.

Note : Si vous souhaitez en savoir plus sur les autres fonctions de google sheet, nous avons un article avec 100+ fonctions de Google Sheet

L'utilisation d'une chaîne de texte pour référencer une cellule présente plusieurs avantages qui sont décrits ci-dessous.

Syntaxe

INDIRECT(cell_reference_as_string, [is_A1_notation])

référence_de_cellule_comme_chaîne - est la forme textuelle de l'adresse d'une cellule. Notez qu'il ne s'agit pas de la référence de la cellule elle-même. Comme il s'agit d'une chaîne de texte, elle doit être mise entre guillemets, sauf si elle fait référence à une cellule contenant une chaîne de texte (voir les exemples ci-dessous).

is_a1_notation - il s'agit d'une entrée facultative qui indique à la fonction le type de notation de l'adresse de la cellule. Il existe deux types de représentations pour l'adresse d'une cellule. La notation A1 spécifie la cellule par la lettre de la colonne (A, B, C, etc.) et le numéro de la ligne (1, 2, 3, etc.), identifiant les cellules sous la forme A1, B3, F14, etc. Une autre façon de représenter une cellule est R1C1, qui donne le numéro de ligne (R1) et le numéro de colonne (C1). Dans ce format, la cellule A1 serait R1C1 et la cellule B3 serait R3C2 (parce que B3 est la troisième ligne de la deuxième colonne). VRAI est la valeur par défaut, et vous obtenez la notation A1 en retour. Sinon, si vous avez besoin de la notation R1C1, vous devez spécifier FALSE.

Comment utiliser la fonction INDIRECT

La façon la plus simple de comprendre ce concept est de le voir à l'œuvre, c'est pourquoi j'ai fourni quelques exemples ci-dessous, chacun utilisant une combinaison différente.

Comme vous pouvez le constater, le premier paramètre peut être une chaîne directe entre guillemets (lignes 2 et 3), une référence de cellule contenant la chaîne d'adresse (ligne # 7) ou même une chaîne concaténée (lignes 4 et 5).

Pour vous aider à mieux comprendre la fonction, je vais passer en revue la liste des différentes variations de la formule et les résultats que chacune d'entre elles génère.

Exemple 1

Dans la première formule =INDIRECT(“B3”), je spécifie simplement que je veux que le résultat de la formule soit ce qui se trouve dans la cellule B3, c'est-à-dire Brian.

Exemple 2

Dans la deuxième formule =INDIRECT(“B2”, TRUE), je fais la même chose, mais cette fois-ci je demande que le résultat provienne de la cellule B2, qui est Alex.

J'indique également VRAI, ce qui signifie que je veux la notation A1.

Notez qu'il n'est pas nécessaire de le spécifier. La fonction suppose que vous utilisez la notation A1 à moins que vous n'indiquiez le contraire (en utilisant FALSE qui indique la notation R1C1).

Exemple 3

Dans la troisième formule =INDIRECT(“Sheet1 !”&A4,True), je concatène une chaîne de texte qui indiquera à la formule la cellule à laquelle je souhaite faire référence.

Pour ce faire, vous commencez par dire “Feuille 1 !”, suivi de &A4, qui indique à la formule de regarder la cellule A4 de la feuille 1.

Le texte de la cellule A4 est “B7”, ce qui indique à la formule de donner la sortie de la cellule B7, qui est Fabio.

Dans cette formule, j'indique également VRAI pour la notation A1, qui est elle aussi facultative.

Exemple 4

La quatrième formule =INDIRECT(A5&”!B8″) fait la même chose mais d'une manière différente.

Il écrit “Sheet1!B8” en référençant la cellule A5 (qui contient “Sheet1”) et en ajoutant le “!B8”.

Par conséquent, la formule indirecte regarde la cellule B8 et renvoie Greg.

Exemple 5

La cinquième formule =INDIRECT(“R5C2”,FALSE) utilise le format R1C1 pour identifier la cellule B5 en disant R5C2 (la cinquième ligne de la deuxième colonne).

Dans ce cas, je dois indiquer à la formule que j'utilise la notation R1C1 en ajoutant FAUX à la fin de la formule.

Exemple 6

La sixième formule =INDIRECT(A7,FALSE) fait référence à une autre cellule, A7, qui renvoie la formule à la cellule B6 au format R1C1.

Notez que dans cet exemple, A7 n'est pas entre guillemets car il ne s'agit pas d'une chaîne de texte mais d'une référence à la cellule A7 qui contient la chaîne de texte (R6C2).

La dernière formule =INDIRECT(SUBSTITUTE(SUBSTITUTE(A8,”[“,””),”]”,””),FALSE) utilise la formule SUBSTITUTE pour changer le format d'adresse de la cellule R[1]C[1] en R1C1, puisqu'il s'agit de la notation que la formule peut gérer.

Gestion d'une plage de cellules dans une formule

L'un des avantages de la fonction INDIRECT est qu'en spécifiant la ou les cellules auxquelles la formule doit faire référence, ces cellules restent inchangées même si vous ajoutez de nouvelles lignes ou colonnes à la feuille.

L'exemple ci-dessous montre la différence entre l'utilisation d'une formule SUM normale et l'utilisation de la formule SUM combinée à une formule INDIRECT, =SUM(INDIRECT(“B2:B6”)).

Ici, vous pouvez voir que pour additionner les revenus du lundi au vendredi, SUM peut être utilisé avec ou sans la fonction INDIRECT.

La fonction Sum fait directement référence aux cellules B2:B6, tandis que la formule INDIRECT fait référence aux cellules sous la forme d'une chaîne de texte “B2:B6”.

La différence intervient lorsque vous apportez des modifications à la feuille, par exemple en ajoutant une ligne supplémentaire.

Dans la capture d'écran ci-dessus, vous pouvez voir qu'une ligne supplémentaire a été insérée au-dessus du lundi afin d'ajouter les recettes du dimanche. Dans ce cas, la fonction SOMME modifie automatiquement ses cellules en B3:B7.

La formule qui utilise la fonction INDIRECT conserve toutefois la plage initiale de B2:B6.

Dans certaines situations, le fait que SUM (et d'autres formules) puisse changer de plage en fonction des modifications apportées aux lignes et aux colonnes d'une feuille peut s'avérer utile. D'un autre côté, cela peut s'avérer frustrant lorsque vous souhaitez conserver la même plage.

Dans ce cas, la fonction INDIRECT est un moyen utile d'y parvenir.

Utilisation de INDIRECT avec des plages nommées

Plages nommées peut être créée pour faire référence à un groupe de cellules. La formule INDIRECT peut utiliser une plage nommée pour divers calculs.

Dans l'exemple ci-dessous, j'ai créé une plage nommée pour les cellules B3:B7 appelée Monday_to_Friday.

Pour ce faire, il suffit de sélectionner les cellules et de cliquer sur Données > Plages nommées.

Une fois la plage nommée créée, elle peut être référencée à l'aide de la fonction INDIRECT.

Dans l'exemple ci-dessus, j'utilise =SUM(INDIRECT(“Monday_to_Friday”)) pour appeler la plage Monday_to_Friday, puis j'affiche la somme de cette plage.

Comment utiliser INDIRECT pour faire référence à d'autres feuilles ?

La fonction INDIRECT permet également de référencer des données dans d'autres feuilles.

Dans l'exemple simple ci-dessous, vous pouvez voir comment la fonction peut être utilisée pour extraire des données d'autres feuilles.

J'ai créé un deuxième onglet intitulé Feuille2, et dans la cellule B4 de cette feuille, j'ai saisi “Données de la feuille 2”.

Ensuite, sur la feuille 1, je peux utiliser la formule INDIRECT pour faire référence à cette cellule et sortir les données de cette cellule en disant =INDIRECT(“Sheet2 !”&”B4”)

Cela peut s'avérer utile lorsque vous disposez d'une feuille comportant plusieurs onglets à partir desquels vous extrayez des informations. Au lieu d'inclure Sheet2 dans la formule, vous pouvez écrire “Sheet2” dans une cellule, puis demander à la formule INDIRECT de faire référence à cette cellule.

Si vous souhaitez ensuite la modifier pour obtenir des données d'un autre onglet, au lieu de modifier la formule, vous pouvez simplement changer la cellule pour qu'elle indique “Feuil3” au lieu de Feuil2, par exemple.

Vous pouvez faire de même avec la cellule spécifique à laquelle vous faites référence sur la feuille (B4 dans cet exemple).

La fonction INDIRECT permet également d'utiliser la mise en forme conditionnelle avec plusieurs onglets.

Comment utiliser la fonction INDIRECT avec MATCH ?

La fonction INDIRECT peut être combinée avec la fonction MATCH pour renvoyer une gamme dynamique de cellules.

Dans l'exemple suivant, je combine les fonctions INDIRECT et MATCH pour obtenir les revenus pour les jours que je spécifie, en utilisant la formule =INDIRECT(“B” & MATCH(B10,A:A,FALSE) & “:B” & MATCH(B11,A:A,FALSE), TRUE)

Comme vous pouvez le voir sur la capture d'écran, j'ai saisi le lundi et le jeudi comme jours de début et de fin pour lesquels je souhaite afficher les recettes.

La formule MATCH trouve ces jours dans ma plage de données et identifie les lignes dans lesquelles ils se trouvent. Ces lignes sont introduites dans la formule INDIRECT en tant que lignes entre lesquelles je souhaite renvoyer les données. Le résultat est que la formule donne les revenus du lundi au jeudi.

En procédant de la sorte, je peux obtenir des données pour différentes plages de jours en modifiant simplement les cellules de début et de fin de journée.

Je peux également combiner cette formule avec d'autres fonctions, telles que SUM, afin d'obtenir le revenu global pour la plage de jours spécifiée.

C'est ce que j'ai fait dans l'exemple ci-dessous, en tapant =SUM(INDIRECT(“R” & MATCH(B10,A:A,FALSE) & “C2” & “:R” & MATCH(B11,A:A,FALSE) & “C2”, false)).

Comme vous pouvez le constater, j'ai utilisé la notation R1C1 dans cet exemple au lieu de la notation A1 de l'exemple précédent.

Cependant, la logique est la même : utilisez la fonction MATCH pour identifier les données que vous souhaitez obtenir et utilisez la fonction INDIRECT pour appeler ces données.

Utilisation de la formule INDIRECTE avec des onglets

La formule INDIRECTE peut être utilisée pour consolider des informations provenant de plusieurs onglets d'une feuille de calcul.

Dans l'exemple suivant, j'ai une feuille de calcul contenant des données de vente pour l'année. Chaque mois fait l'objet d'un onglet distinct, qui contient les données des ventes hebdomadaires et la valeur totale des ventes pour le mois, comme le montre la capture d'écran suivante.

Dans le cadre de la Résumé je souhaite intégrer tous les totaux mensuels des ventes.

Au lieu de copier et coller chaque valeur, je peux utiliser la formule INDIRECT pour accéder aux données de chaque onglet en utilisant la formule suivante : =INDIRECT(A2&”!B6″)

Comme vous pouvez le voir, cette formule indique à la fonction indirecte de regarder dans chaque onglet et de renvoyer le total des ventes pour ce mois, qui se trouve dans la cellule B6 de chaque onglet.

Vous pouvez ainsi consolider rapidement les données de plusieurs onglets. Le plus beau, c'est que lorsque les chiffres changent dans l'un des onglets, ce changement est automatiquement répercuté dans l'onglet "Résumé" !

Erreurs - Formule INDIRECTE

La formule INDIRECT peut-elle entraîner des erreurs ? Oui, bien sûr !

La capture d'écran suivante illustre le résultat lorsque vous essayez de saisir une notation d'adresse de style A1 pour cell_reference_as_string et FALSE pour le paramètre is_A1_notation.

En faisant cela, la formule INDIRECT cherchera évidemment une notation de type R1C1, et elle n'en trouve pas. D'où l'erreur !

L'inverse est également vrai, comme le montre l'image ci-dessous.

Et maintenant, le pire des scénarios.

Vous essayez de saisir un formulaire de texte qui n'est ni la représentation A1, ni la représentation R1C1, mais simplement un texte fictif. Et, bien sûr, il devrait y avoir une erreur !

Merci d'avoir lu ce guide détaillé de Sheetgo sur l'utilisation de la fonction INDIRECT dans Google Sheets. Consultez nos autres articles pour obtenir d'autres conseils sur les feuilles de calcul !

Vous avez aimé cet article ?

Partagez-le avec vos amis et collègues via les boutons de médias sociaux à gauche.