Analyse de la fonction INDIRECT par des exemples

A première vue (surtout à la lecture de l'aide), la fonction INDIRECT (INDIRECT) semble simple et même inutile. Son essence est de transformer un texte qui ressemble à un lien en un lien à part entière. Ceux. si nous devons nous référer à la cellule A1, nous pouvons soit faire un lien direct habituel (entrez un signe égal dans D1, cliquez sur A1 et appuyez sur Entrée), soit nous pouvons utiliser INDIRECT dans le même but :

Veuillez noter que l'argument de la fonction - une référence à A1 - est entré entre guillemets, de sorte qu'il s'agit en fait de texte ici.

"Eh bien, d'accord", dites-vous. "Et quel est l'avantage?" 

Mais ne jugez pas par la première impression - c'est trompeur. Cette fonctionnalité peut vous aider dans de nombreuses situations.

Exemple 1. Transposer

Un classique du genre : il faut tourner le diamètre vertical

rainure à l'horizontale (transposition). Bien sûr, vous pouvez utiliser un insert ou une fonction spéciale TRANSP (TRANSPOSER) dans une formule matricielle, mais vous pouvez vous débrouiller avec notre INDIRECT:

La logique est simple : pour obtenir l'adresse de la cellule suivante, on colle la lettre « A » avec le caractère spécial « & » et le numéro de colonne de la cellule courante, que la fonction nous donne COLONNE (COLONNE).

La procédure inverse est mieux faite un peu différemment. Puisque cette fois, nous devons former un lien vers les cellules B2, C2, D2, etc., il est plus pratique d'utiliser le mode de liaison R1C1 au lieu du classique «bataille navale». Dans ce mode, nos cellules ne différeront que par le numéro de colonne : B2=R1C2, C2=R1C3, D2=R1C4 et ainsi de suite

C'est là qu'intervient le deuxième argument de fonction facultatif. INDIRECT. S'il est égal COUCHÉ (FAUX), vous pouvez définir l'adresse de liaison en mode R1C1. Nous pouvons donc facilement transposer la plage horizontale à la verticale :

Exemple 2. Somme par intervalle

Nous avons déjà analysé une façon de faire la sommation sur une fenêtre (plage) d'une taille donnée sur une feuille en utilisant la fonction ÉLIMINATION (DÉCALAGE). Un problème similaire peut également être résolu en utilisant INDIRECT. Si nous devons résumer les données uniquement à partir d'une certaine période, nous pouvons les coller à partir de morceaux, puis les transformer en un lien à part entière, que nous pouvons insérer dans la fonction. SUM (SOMME):

Exemple 3. Liste déroulante de tableau intelligent

Parfois, Microsoft Excel ne traite pas les noms de tableaux intelligents et les colonnes comme des liens complets. Ainsi, par exemple, lorsque vous essayez de créer une liste déroulante (onglet Données – Validation des données) basé sur la colonne Employés de la table intelligente Personnes nous aurons une erreur :

Si nous « enveloppons » le lien avec notre fonction INDIRECT, alors Excel l'acceptera facilement et notre liste déroulante sera dynamiquement mise à jour lors de l'ajout de nouveaux employés à la fin de la table intelligente :

Exemple 4. Liens incassables

Comme vous le savez, Excel corrige automatiquement les adresses de référence dans les formules lors de l'insertion ou de la suppression de lignes-colonnes sur une feuille. Dans la plupart des cas, c'est correct et pratique, mais pas toujours. Disons que nous devons transférer les noms du répertoire des employés vers le rapport :

Si vous mettez des liens réguliers (entrez =B2 dans la première cellule verte et copiez-le), alors lorsque vous supprimerez, par exemple, Dasha, nous obtiendrons le #LINK ! erreur dans la cellule verte qui lui correspond. (#REF!). En cas d'utilisation de la fonction de création de liens INDIRECT il n'y aura pas un tel problème.

Exemple 5 : Collecte de données à partir de plusieurs feuilles

Supposons que nous ayons 5 feuilles avec des rapports du même type de différents employés (Mikhail, Elena, Ivan, Sergey, Dmitry) :

Supposons que la forme, la taille, la position et la séquence des biens et des mois dans tous les tableaux sont les mêmes - seuls les chiffres diffèrent.

Vous pouvez collecter les données de toutes les feuilles (ne les résumez pas, mais placez-les les unes sous les autres dans une « pile ») avec une seule formule :

Comme vous pouvez le voir, l'idée est la même : nous collons le lien à la cellule souhaitée de la feuille donnée, et INDIRECT le transforme en "live". Pour plus de commodité, au-dessus du tableau, j'ai ajouté les lettres des colonnes (B,C,D) et à droite - les numéros de ligne à prendre sur chaque feuille.

Pièges

Si vous utilisez INDIRECT (INDIRECT) vous devez vous rappeler de ses faiblesses:

• Si vous créez un lien vers un autre fichier (en collant le nom du fichier entre crochets, le nom de la feuille et l'adresse de la cellule), cela ne fonctionne que lorsque le fichier d'origine est ouvert. Si nous le fermons, nous obtenons l'erreur #LINK !
• INDIRECT ne peut pas faire référence à une plage nommée dynamique. Sur statique - pas de problème.
• INDIRECT est une fonction volatile ou « volatile », c'est-à-dire qu'elle est recalculée pour tout changement dans n'importe quelle cellule de la feuille, et pas seulement dans les cellules d'influence, comme dans les fonctions normales. Cela a un effet néfaste sur les performances et il vaut mieux ne pas se laisser emporter par de grandes tables INDIRECT.

• Comment créer une plage dynamique avec le dimensionnement automatique
• Sommation sur une fenêtre de plage sur une feuille avec la fonction OFFSET