Téléchargez l'exemple de classeur
Ce didacticiel montre comment utiliser le Fonction INDIRECT d'Excel dans Excel pour créer une référence de cellule à partir de texte.
Présentation de la fonction INDIRECT
La fonction INDIRECT Crée une référence de cellule à partir d'une chaîne de texte.
(Remarquez comment les entrées de formule apparaissent)
Fonction INDIRECT Syntaxe et entrées :
| 1 | =INDIRECT(ref_texte,C1) |
ref_text - Une chaîne représentant une référence de cellule ou une référence de plage. La chaîne peut être au format R1C1 ou A1, ou peut être une plage nommée.
a1 - OPTIONNEL : Indique si la référence est au format R1C1 ou A1. FALSE pour R1C1 ou TRUE / Omis pour A1.
Qu'est-ce que la fonction INDIRECT ?
La fonction INDIRECT vous permet de donner une chaîne de texte et de laisser l'ordinateur interpréter cette chaîne comme une référence réelle. Cela peut être utilisé pour référencer une plage sur la même feuille, une feuille différente ou même un classeur différent.
ATTENTION : La fonction INDIRECT fait partie des fonctions volatiles. La plupart du temps, lorsque vous travaillez dans votre feuille de calcul, l'ordinateur ne recalculera une formule que si les entrées ont changé de valeur. Une fonction volatile, cependant, recalcule tous fois que vous apportez une modification à n'importe quelle cellule. Des précautions doivent être prises pour s'assurer que vous ne causez pas un temps de recalcul important en raison d'une utilisation excessive de la fonction volatile ou du fait que de nombreuses cellules dépendent du résultat d'une fonction volatile.
Créer une référence de cellule
Supposons que vous vouliez récupérer la valeur de A2, mais que vous vouliez vous assurer que votre formule séjours sur A2 indépendamment des nouvelles lignes insérées/supprimées. Vous pourriez écrire une formule de
| 1 | =INDIRECT("A2") |
Notez que l'argument à l'intérieur de notre fonction est la chaîne de texte "A2", et non une référence de cellule. De plus, puisqu'il s'agit d'une chaîne de texte, il n'est pas nécessaire d'indiquer une référence absolue comme $A$2. Le texte ne changera jamais, et donc cette formule pointera toujours vers A2, peu importe où elle est déplacée.
Numéro de ligne INDIRECT
Vous pouvez concaténer des chaînes de texte et des valeurs de cellules. Plutôt que d'écrire "A2" comme nous l'avons fait précédemment, nous pouvons saisir une valeur numérique dans la cellule B2 et l'utiliser dans notre formule. Nous écririons la formule comme
| 1 | =INDIRECT("A" & B2) |
Le symbole « & » est utilisé ici pour concaténer la chaîne de texte « A » avec la valeur de la cellule B2. Donc, si la valeur de B2 était actuellement de 10, alors notre formule se lirait comme suit
| 123 | =INDIRECT("A" & 10)=INDIRECT("A10")=A10 |
Valeur de colonne INDIRECT
Vous pouvez également concaténer dans la référence de colonne. Cette fois, disons que nous savons que nous voulons récupérer une valeur de la ligne 10, mais que nous voulons pouvoir changer la colonne à partir de laquelle extraire. Nous mettrons la lettre de colonne que nous voulons dans la cellule B2. Notre formule pourrait ressembler à
| 1 | =INDIRECT(B2 & "10") |
Si la valeur de B2 est « G », alors notre formule évalue ainsi
| 123 | =INDIRECT("G" & 10)=INDIRECT("G10")=G10 |
Style INDIRECT r1c1
Dans notre exemple précédent, nous devions utiliser une lettre pour indiquer la référence de la colonne. C'est parce que nous utilisions ce que l'on appelle le référencement de style A1. Dans le style A1, les colonnes sont indiquées par une lettre et les lignes sont données par des nombres. Les références absolues sont indiquées en utilisant le « $ » avant l'élément que nous voulons rester absolu.
Dans r1c1, les lignes et les colonnes commencent à l'aide de nombre. La référence absolue à a1 s'écrirait
| 1 | =R1C1 |
Vous pouvez lire ceci comme « Ligne 1, colonne 1 ». Les références relatives sont données à l'aide de parenthèses, mais le nombre indique la position par rapport à la cellule avec la formule. Donc, si nous écrivions une formule dans la cellule A10 et que nous devions nous référer à A1, nous écririons la formule
| 1 | =R[-9]C |
Vous pouvez lire ceci comme "La cellule 9 lignes vers le haut, mais dans la même colonne.
La raison pour laquelle cela pourrait être utile est que INDIRECT peut prendre en charge l'utilisation de la notation r1c1. Considérons l'exemple précédent où nous récupérons une valeur de la ligne 10 mais voulions pouvoir modifier la colonne. Plutôt que de donner une lettre, disons que nous mettons un nombre dans la cellule B2. Notre formule pourrait alors ressembler à
| 1 | =INDIRECT("R10C" & B2, FAUX) |
Nous avons omis le 2sd argument jusqu'à maintenant. Si cet argument est omis ou True, la fonction évaluera en utilisant le style A1. Comme c'est False, il va être évalué dans r1c1. Supposons que la valeur de B2 est 5. Notre formule évaluera cela comme si
| 12 | =INDIRECT("R10C5", FAUX)=$E$10 |
Différences INDIRECTES avec A1 vs r1c1
Rappelez-vous que nous avons déjà montré que le contenu de cette formule étant une chaîne de texte, il n'a jamais changé ?
| 1 | =INDIRECT("A2") |
Cette formule examinera toujours la cellule A2, peu importe où vous déplacez la formule. Dans r1c1, puisque vous pouvez indiquer la position relative à l'aide de crochets, cette règle ne reste pas cohérente. Si vous placez cette formule dans la cellule B2
| 1 | =INDIRECT("RC[-1]") |
Il examinera la cellule A2 (puisque la colonne A se trouve à gauche de la colonne B). Si vous copiez cette formule dans la cellule B3, le texte à l'intérieur restera le même, mais INDIRECT examinera maintenant la cellule A3.
INDIRECT avec le nom de la feuille
Vous pouvez également combiner un nom de feuille dans vos références INDIRECTES. Une règle importante à retenir est que vous devez placer des guillemets simples autour des noms et que vous devez séparer le nom de la feuille de la référence de cellule par un point d'exclamation.
Disons que nous avons eu cette configuration, où nous indiquons le nom, la ligne et la colonne de notre feuille.
Notre formule pour combiner tous ces éléments dans une référence ressemblerait à ceci :
| 1 | =INDIRECT("'" & A2 & "'!" & B2 & C2) |
Notre formule sera alors évaluée comme suit :
| 123 | =INDIRECT("'" & "Feuille2" & "'!" & "B" & "5")=INDIRECT("'"Feuille2'!B5")='Feuille2'!B5 |
Techniquement, étant donné que le mot « Sheet2 » ne contient aucun espace, nous ne avoir besoin les guillemets simples. Il est parfaitement valable d'écrire quelque chose comme
| 1 | =Feuille2!A2 |
Cependant, cela ne fait pas de mal de placer les guillemets lorsque vous n'en avez pas besoin. Il est recommandé de les inclure afin que votre formule puisse gérer les cas où ils pourraient être nécessaires.
INDIRECT vers un autre classeur
Nous mentionnerons également qu'INDIRECT peut créer une référence à un autre classeur. La limitation est que INDIRECT ne récupère pas les valeurs d'un classeur fermé, donc cette utilisation particulière a une praticité limitée. Si le classeur vers lequel INDIRECT pointe n'est pas ouvert, la fonction lancera un "#REF!" Erreur.
La syntaxe lors de l'écriture du nom du classeur est qu'il doit être entre crochets. Utilisons cette configuration et essayons d'extraire une valeur de la cellule C7.
Notre formule serait
| 1 | =INDIRECT("'[" & A2 & "]" & B2 & "'!C7") |
Encore une fois, faites attention à l'emplacement des guillemets simples, des crochets et du point d'exclamation. Notre formule sera alors évaluée comme suit :
| 123 | =INDIRECT("'[" & "Sample.xlsx" & "]" & "Résumé" & "'!C7")=INDIRECT("'[Exemple.xslx]Résumé'!C7")='[Sample.xlsx]Résumé'!C7 |
INDIRECT pour construire une plage dynamique
Lorsque vous disposez d'un ensemble de données volumineux, il est important d'essayer d'optimiser les formules afin qu'elles ne fassent pas plus de travail que nécessaire. Par exemple, plutôt que de référencer toute la colonne A, nous pourrions vouloir simplement référencer le nombre exact de cellules dans notre liste. Considérez la disposition suivante :
Dans la cellule B2, nous avons placé la formule
| 1 | =COUNTA(A:A) |
La fonction COUNTA est très facile à calculer pour l'ordinateur, car elle vérifie simplement combien de cellules dans la colonne A ont une certaine valeur, au lieu d'avoir à effectuer des vérifications logiques ou des opérations mathématiques.
Maintenant, construisons notre formule qui additionnera les valeurs de la colonne A, mais nous voulons nous assurer qu'elle ne regarde que la plage exacte avec des valeurs (A2:A5). Nous écrirons notre formule sous la forme
| 1 | =SOMME(INDIRECT("A2:A" & B2)) |
Notre INDIRECT va saisir le numéro 5 de la cellule B2 et créera une référence à la plage A2:A5. La SOMME peut alors utiliser cette plage pour son calcul. Si nous ajoutons une autre valeur dans la cellule A6, le nombre dans B2 sera mis à jour et notre formule SUM sera également mise à jour automatiquement pour inclure cette nouvelle valeur.
ATTENTION : avec l'introduction des tables dans Office 2007, il est beaucoup plus efficace de stocker vos données dans une table et d'utiliser une référence structurelle plutôt que de créer la formule que nous avons utilisée dans cet exemple en raison de la nature volatile d'INDIRECT. Cependant, il peut s'agir de cas où vous devez créer une liste d'éléments et ne pouvez pas utiliser une table.
Cartographie dynamique avec INDIRECT
Reprenons l'exemple précédent et faisons un pas de plus. Plutôt que d'écrire une formule pour nous donner la somme des valeurs, nous allons créer une plage nommée. Nous pourrions appeler cette plage « MyData » et la faire référencer à
| 1 | =INDIRECT("A2:A" & COUNTA($A:$A)) |
Notez que puisque nous mettons cela dans une plage nommée, nous avons échangé la référence à B2 et avons plutôt mis la fonction COUNTA directement là-dedans.
Maintenant que nous avons cette plage nommée, nous pouvons l'utiliser dans un graphique. Nous allons créer un graphique en courbes vierge, puis ajouter une série de données. Pour les valeurs de la série, vous pouvez écrire quelque chose comme
| 1 | =Feuille1!MesDonnées |
Le graphique va maintenant utiliser cette référence pour tracer les valeurs. Au fur et à mesure que plus de valeurs sont ajoutées à la colonne A, INDIRECT fera référence à une plage de plus en plus large, et notre graphique continuera à rester à jour avec toutes les valeurs nouvellement ajoutées.
Validation dynamique des données avec INDIRECT
Lors de la collecte des commentaires des utilisateurs, il est parfois nécessaire de choisir parmi les options d'un choix en fonction d'un choix précédent. Considérez cette disposition, où notre première colonne permet à l'utilisateur de choisir entre les fruits, les légumes et les viandes.
Dans le 2sd colonne, nous ne voulons pas avoir une grande liste montrant tous les choix possibles, car nous avons déjà un peu réduit les choses. Nous avons donc créé 3 autres listes qui ressemblent à ceci :
Ensuite, nous attribuerons chacun de ces listes à une plage nommée. C'est-à-dire que tous les fruits seront dans une gamme appelée « Fruits », et les légumes dans « Légumes », etc.
De retour dans notre tableau, nous sommes prêts à configurer la validation des données dans le 2sd colonne. Nous allons créer une validation de type Liste, avec une entrée de :
| 1 | =INDIRECT(A2) |
L'INDIRECT va lire dans le choix fait en col A et voir le nom d'une catégorie. Nous avons défini des plages avec ces noms, donc INDIRECT prendra alors ce nom et créera une référence à la plage souhaitée.
Notes complémentaires
Utilisez la fonction INDIRECT pour créer une référence de cellule à partir de texte.
Créez d'abord la chaîne de texte qui représente une référence de cellule. La chaîne doit être soit dans la lettre de colonne et le numéro de ligne habituels de style A1 (M37), soit dans le style R1C1 (R37C13). Vous pouvez taper la référence directement, mais généralement vous référencerez des cellules qui définissent les lignes et les colonnes. Enfin, entrez le format de référence de cellule que vous choisissez. VRAI ou omis pour la référence de style A1 ou FAUX pour le style R1C1.
Lorsque vous travaillez avec des formules INDIRECTES, vous souhaiterez peut-être utiliser le Fonction LIGNE pour obtenir le numéro de ligne d'une référence ou le Fonction COLONNE pour obtenir le numéro de colonne (pas la lettre) d'une référence.
Revenir à la liste de toutes les fonctions dans Excel
INDIRECT dans Google Sheets
La fonction INDIRECT fonctionne exactement de la même manière dans Google Sheets que dans Excel :
