Table des matières
Toutes nos félicitations! Vous avez atteint le dernier jour du marathon 30 fonctions Excel en 30 jours. Ce fut un voyage long et intéressant au cours duquel vous avez appris beaucoup de choses utiles sur les fonctions Excel.
Au 30ème jour du marathon, nous consacrerons l'étude de la fonction INDIRECT (INDIRECT), qui renvoie le lien spécifié par la chaîne de texte. Avec cette fonction, vous pouvez créer des listes déroulantes dépendantes. Par exemple, lors de la sélection d'un pays dans une liste déroulante, détermine les options qui apparaîtront dans la liste déroulante des villes.
Alors, regardons de plus près la partie théorique de la fonction INDIRECT (INDIRECT) et explorez des exemples pratiques de son application. Si vous avez des informations supplémentaires ou des exemples, merci de les partager dans les commentaires.
Fonction 30 : INDIRECT
Fonction INDIRECT (INDIRECT) renvoie le lien spécifié par la chaîne de texte.
Comment utiliser la fonction INDIRECT ?
Depuis la fonction INDIRECT (INDIRECT) renvoie un lien donné par une chaîne de texte, vous pouvez l'utiliser pour :
- Créez un lien initial non décalé.
- Créez une référence à une plage nommée statique.
- Créez un lien à l'aide des informations de feuille, de ligne et de colonne.
- Créez un tableau de nombres non décalé.
Syntaxe INDIRECT (INDIRECT)
Fonction INDIRECT (INDIRECT) a la syntaxe suivante :
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_to_cell) est le texte du lien.
- a1 – si égal à TRUE (TRUE) ou non spécifié, alors le style du lien sera utilisé A1; et si FALSE (FALSE), alors le style R1C1.
Pièges INDIRECT (INDIRECT)
- Fonction INDIRECT (INDIRECT) est recalculé chaque fois que les valeurs de la feuille de calcul Excel changent. Cela peut considérablement ralentir votre classeur si la fonction est utilisée dans de nombreuses formules.
- Si la fonction INDIRECT (INDIRECT) crée un lien vers un autre classeur Excel, ce classeur doit être ouvert ou la formule signalera une erreur #REF! (#LIEN!).
- Si la fonction INDIRECT (INDIRECT) fait référence à une plage qui dépasse la limite de ligne et de colonne, la formule signalera une erreur #REF! (#LIEN!).
- Fonction INDIRECT (INDIRECT) ne peut pas faire référence à une plage nommée dynamique.
Exemple 1 : créer un lien initial non décalé
Dans le premier exemple, les colonnes C et E contiennent les mêmes nombres, leurs sommes calculées à l'aide de la fonction SUM (SOMME) sont également les mêmes. Cependant, les formules sont légèrement différentes. Dans la cellule C8, la formule est :
=SUM(C2:C7)
=СУММ(C2:C7)
Dans la cellule E8, la fonction INDIRECT (INDIRECT) crée un lien vers la cellule de départ E2 :
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Si vous insérez une ligne en haut de la feuille et ajoutez la valeur de janvier (Jan), le montant dans la colonne C ne changera pas. La formule va changer, réagissant à l'ajout d'une ligne :
=SUM(C3:C8)
=СУММ(C3:C8)
Cependant, la fonction INDIRECT (INDIRECT) fixe E2 comme cellule de départ, donc janvier est automatiquement inclus dans le calcul des totaux de la colonne E. La cellule de fin a changé, mais la cellule de début n'a pas été affectée.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Exemple 2 : Lien vers une plage nommée statique
Fonction INDIRECT (INDIRECT) peut créer une référence à une plage nommée. Dans cet exemple, les cellules bleues constituent la plage NumListe. De plus, une plage dynamique est également créée à partir des valeurs de la colonne B NumListeDyn, selon le nombre de nombres dans cette colonne.
La somme des deux plages peut être calculée en donnant simplement son nom comme argument à la fonction SUM (SOMME), comme vous pouvez le voir dans les cellules E3 et E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Au lieu de taper un nom de plage dans une fonction SUM (SOMME), Vous pouvez vous référer au nom écrit dans l'une des cellules de la feuille de calcul. Par exemple, si le nom NumListe est écrit dans la cellule D7, alors la formule dans la cellule E7 ressemblera à ceci :
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Malheureusement la fonction INDIRECT (INDIRECT) ne peut pas créer une référence de plage dynamique, donc lorsque vous copiez cette formule dans la cellule E8, vous obtiendrez une erreur #REF! (#LIEN!).
Exemple 3 : créer un lien à l'aide des informations de feuille, de ligne et de colonne
Vous pouvez facilement créer un lien basé sur les numéros de ligne et de colonne, ainsi qu'utiliser la valeur FALSE (FALSE) pour le deuxième argument de la fonction INDIRECT (INDIRECT). Voici comment le lien de style est créé R1C1. Dans cet exemple, nous avons en outre ajouté le nom de la feuille au lien - 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Exemple 4 : créer un tableau de nombres non décalé
Parfois, vous devez utiliser un tableau de nombres dans les formules Excel. Dans l'exemple suivant, nous voulons faire la moyenne des 3 plus grands nombres de la colonne B. Les nombres peuvent être entrés dans une formule, comme cela se fait dans la cellule D4 :
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Si vous avez besoin d'un tableau plus grand, il est peu probable que vous souhaitiez entrer tous les nombres dans la formule. La deuxième option consiste à utiliser la fonction RANGÉE (ROW), comme dans la formule matricielle entrée dans la cellule D5 :
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
La troisième option consiste à utiliser la fonction RANGÉE (STRING) avec INDIRECT (INDIRECT), comme fait avec la formule matricielle dans la cellule D6 :
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Le résultat pour les 3 formules sera le même :
Cependant, si des lignes sont insérées en haut de la feuille, la deuxième formule renverra un résultat incorrect car les références dans la formule changeront avec le décalage de ligne. Désormais, au lieu de la moyenne des trois plus grands nombres, la formule renvoie la moyenne des 3e, 4e et 5e plus grands nombres.
Utilisation des fonctions INDIRECT (INDIRECT), la troisième formule conserve les références de ligne correctes et continue à afficher le résultat correct.