=INDIRECT(E2) lit la cellule dont l'adresse est écrite sous forme de texte en E2. Si E2 contient C4, la formule renvoie la valeur de C4. L'adresse peut aussi être construite à partir de morceaux : =INDIRECT("C"&E3) lit la colonne C au numéro de ligne indiqué en E3. INDIRECT porte le même nom en français et en anglais ; le tableau affiche les formules en anglais, mais vous pouvez aussi les y taper en français, avec des points-virgules : =SOMME(INDIRECT("B2:B"&(1+E2))).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Address | Value | |
| 2 | Apple | Fruit | $1.20 | C4 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | 6 | $1.10 | |
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
=INDIRECT(E2)F2 lit C4, le prix de Carrot, $0.80. Remplacez E2 par C3 ou B5 et F2 suit. F3 assemble "C" et le 6 de E3 en l'adresse C6 et renvoie $1.10. Passez E3 à 2 pour le prix de Apple.
Syntaxe de INDIRECT
=INDIRECT(ref_text, [a1])
ref_text(réf_texte) : un texte qui écrit une référence :"C4","B2:B6","Prices!A2","'Price list'!A2:B9".a1:VRAIou omis pour des adresses de style A1.FAUXlit le style R1C1 (appelé L1C1 dans un Excel français), où"R4C3"signifie ligne 4, colonne 3, ce qui convient quand la ligne et la colonne sont toutes deux des nombres.
Si le texte n'est pas une adresse valide, le résultat est #REF!. INDIRECT renvoie une vraie référence, elle fonctionne donc dans SOMME, NB.SI, RECHERCHEV et toutes les fonctions qui prennent une plage.
Construire une plage à partir de nombres
L'adresse peut être une plage entière. Y insérer un nombre donne une plage dont la taille vient d'une cellule.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Rows | Total | ||
| 2 | Jan | 4,200 | 3 | 12,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 |
=SOMME(INDIRECT("B2:B"&(1+E2)))Avec 3 en E2, le texte devient B2:B4, et F2 additionne de Jan à Mar : 12,900. Mettez E2 à 6 pour le semestre, 27,900. Le 1+E2 est là parce que les données commencent à la ligne 2. Le même total peut s'écrire sans INDIRECT, =SOMME(B2:INDEX(B2:B7;E2)), qui n'est pas volatile ; la page DECALER compare les options.
Faire référence à une feuille nommée dans une cellule
Le nom de la feuille peut aussi venir d'une cellule. Une seule formule de synthèse devient ainsi une recherche entre feuilles : chaque ligne lit la feuille nommée dans la colonne A. Les apostrophes autour du nom permettent de gérer les noms avec des espaces.
| A | B | |
|---|---|---|
| 1 | Month | Total |
| 2 | Jan | 12,500 |
| 3 | Feb | 12,200 |
| 4 | Mar | 13,700 |
=SOMME(INDIRECT("'"&A2&"'!B2:B4"))B2 construit le texte 'Jan'!B2:B4 et en fait le total : 12,500. B3 et B4 sont la même formule recopiée vers le bas, elles lisent donc Feb (12,200) et Mar (13,700). Ouvrez l'onglet Feb et modifiez un nombre : la synthèse suit. Tapez Feb à la place de Jan en A2 et B2 fait maintenant le total de Feb. Le B2:B4 entre guillemets est du texte, il ne change donc pas quand la formule est recopiée ; seule la référence A2 change.
Listes déroulantes dépendantes
Une seconde liste déroulante dont les éléments dépendent de la première est l'usage classique d'INDIRECT. Dans Excel, la mise en place habituelle est la suivante :
- Placez les éléments de chaque catégorie dans une colonne et nommez chaque plage d'après sa catégorie : sélectionnez les colonnes avec leurs en-têtes et utilisez Formules > Créer à partir de la sélection > Ligne du haut. Cela crée les noms
Fruit,VegetableetDairy. - Donnez à A2 une liste des catégories : Données > Validation des données > Autoriser : Liste, Source
Fruit,Vegetable,Dairy(dans un Excel français, les éléments d'une source tapée à la main sont séparés par des points-virgules). - Donnez à B2 une liste dont la Source est
=INDIRECT(A2). Quand A2 contient Fruit, la liste lit la plage nommée Fruit.
Le tableau ci-dessous construit la même chose avec une feuille par catégorie au lieu d'une plage nommée. D2 utilise INDIRECT pour propager les éléments de la feuille nommée en A2, et la liste de B2 lit D2:D4.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Item | Items for the category | |
| 2 | Fruit | Apple | Apple | |
| 3 | Pear | |||
| 4 | Plum |
=INDIRECT("'"&A2&"'!A2:A4")Choisissez Dairy en A2 : D2:D4 passe à Milk, Butter, Cheese, et les choix de B2 aussi. B2 garde son ancienne valeur jusqu'à ce que vous en choisissiez une nouvelle ; Excel se comporte de la même façon, c'est pourquoi les formulaires ajoutent souvent un contrôle comme =NB.SI(D2:D4;B2)>0 à côté de l'article. Dans Excel 365, vous pouvez vous passer des plages nommées et faire pointer la seconde liste vers une formule propagée, par exemple =INDIRECT("'"&A2&"'!A2:A4") dans une cellule d'aide et =D2# comme Source. La page liste déroulante détaille le reste de la mise en place.
INDIRECT est volatile et ignore les lignes insérées
Deux effets secondaires viennent de ce qu'INDIRECT lit du texte au lieu d'une référence :
- Elle se recalcule à chaque modification. Excel ne peut pas savoir vers quelles cellules pointera un texte, il recalcule donc chaque INDIRECT après toute modification n'importe où dans le classeur. Quelques dizaines ne posent aucun problème ; des dizaines de milliers ralentissent chaque frappe. INDEX avec un numéro de ligne (
=INDEX(C:C;E3)) donne le même résultat que=INDIRECT("C"&E3)et ne se recalcule que lorsque ses entrées changent. - L'adresse ne bouge pas. Insérez une ligne au-dessus de la ligne 4 et
=C4devient=C5, mais=INDIRECT("C4")lit toujours C4, qui est maintenant une autre ligne. C'est parfois le but, une référence qui doit rester sur une cellule fixe quoi qu'il arrive à la feuille. Le plus souvent, c'est un bug qui attend qu'on insère une ligne.
INDIRECT vers un autre classeur ne fonctionne que tant que ce classeur est ouvert ; fermé, elle renvoie #REF!.
Exercice : un prix à partir d'un numéro de ligne
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Row | Price | |
| 2 | Apple | Fruit | $1.20 | 5 | ||
| 3 | Pear | Fruit | $1.50 | |||
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
À vous : En F2, utilisez INDIRECT pour renvoyer le prix de la colonne C à la ligne dont le numéro est écrit en E2.
Questions fréquentes
Que fait INDIRECT dans Excel ?
Elle transforme du texte en référence. =INDIRECT("C4") renvoie la valeur de C4, et =INDIRECT(E2) renvoie la valeur de la cellule dont l'adresse est écrite en E2. L'adresse peut être construite avec &, donc =INDIRECT("C"&E2) lit la colonne C au numéro de ligne indiqué en E2.
Comment faire référence à une autre feuille dont le nom est dans une cellule ?
Construisez l'adresse avec le nom de la feuille entre apostrophes : =INDIRECT("'"&A2&"'!B2"). Les apostrophes permettent de gérer les noms avec des espaces. =SOMME(INDIRECT("'"&A2&"'!B2:B4")) fait le total d'une plage de cette feuille.
Pourquoi INDIRECT renvoie-t-elle #REF! ?
Le texte n'est pas une adresse valide, ou il désigne une feuille qui n'existe pas, ou il pointe vers un autre classeur fermé. Vérifiez le texte que construit la formule en mettant la même expression seule dans une cellule, sans INDIRECT.
INDIRECT est-elle volatile ?
Oui. Excel recalcule chaque INDIRECT à chaque modification n'importe où dans le classeur, car il ne peut pas savoir à l'avance vers quelles cellules pointera le texte. Quelques-unes sont sans conséquence ; des milliers ralentissent un classeur. INDEX peut souvent faire le même travail sans être volatile.
