Fonction INDIRECT dans Excel
La fonction indirecte dans Excel est une fonction intégrée qui est utilisée pour référencer et obtenir des valeurs de cellule à partir d'une chaîne de texte, cette formule prend référence à la cellule référencée, elle a deux arguments le premier argument n'est pas facultatif tandis que le second argument est facultatif qui fait référence à le type de correspondance, cette fonction peut également être utilisée avec une autre formule.
La fonction INDIRECT dans Excel a de multiples usages. Il renvoie la référence de cellule spécifiée par une chaîne de texte. Par exemple, si la cellule B1 contient une chaîne, disons que les plages c4 et c4 ont la valeur «Jessica». Maintenant, si nous allons utiliser la fonction INDIRECT et transmettre le texte de référence comme B1, il renverra la valeur contenue par la plage de cellules c4 qui est «Jessica».
La fonction INDIRECTE d'Excel vous permet de spécifier une adresse de cellule INDIRECTEMENT. Comme vous pouvez dans l'exemple Excel INDIRECT ci-dessus, si la cellule B1 contient le texte C4, cette formule Excel indirecte renvoie le contenu de la cellule C4 ( Jessica ). Cette fonction est une fonction extrêmement utile. Vous pouvez utiliser la fonction INDIRECT chaque fois que vous souhaitez modifier la référence à une cellule dans une formule Excel indirecte sans modifier la formule elle-même.
Formule Excel INDIRECTE
Explication
Ref_text est une référence à une cellule qui contient une référence de style A1, une référence de style R1C1, un nom défini comme référence ou une référence à une cellule sous forme de chaîne de texte.
l'argument a1 est facultatif
| a1 | Faux ou 0, lorsque ref_text est dans le style R1C1 |
| a1 | Omis ou True (1) lorsque ref_text est de style A1 |
Comment utiliser la fonction INDIRECT dans Excel?
Prenons un exemple Excel INDIRECT avant d'utiliser la fonction d'adresse dans le classeur Excel:
Supposons que, pour un site Web www.xyz.com, nous ayons les données Google Analytics pour les visiteurs du site Web provenant de différents canaux d'audience pour cinq pays différents, comme indiqué ci-dessous dans le tableau:
Nous pouvons utiliser la fonction INDIRECT dans Excel dans le but de convertir une chaîne de texte en référence, en faisant référence à la cellule H4 et en utilisant ce nom comme référence. L'utilisation de la fonction INDIRECT dans Excel, dans ce cas, consiste à référencer des plages de noms en utilisant les valeurs d'une cellule, donc dans la cellule I4, nous pourrions simplement utiliser la fonction de somme pour calculer le nombre total de visiteurs d'un pays Canada.
Désormais, nous pouvons calculer directement la somme des visiteurs de chaque pays à l'aide de la fonction somme, mais en utilisant la fonction INDIRECT d'Excel, nous pouvons calculer la somme des visiteurs de différents pays en modifiant la référence du nom plutôt que de changer la formule Excel indirecte elle-même.
Nous avons créé la gamme de noms pour chaque visiteur du pays; cela peut être fait facilement en tapant la plage de noms dans l'espace juste en dessous du presse-papiers, comme indiqué ci-dessous.
Une autre méthode consiste à accéder aux formules-> gestionnaire de noms -> sélectionner la plage que vous souhaitez nommer -> cliquer sur le gestionnaire de noms -> saisir la plage de noms.
De même, il a créé une plage nommée dans Excel pour les visiteurs d'autres pays également.
Maintenant, dans I4, en calculant la somme des visiteurs du pays Canada en utilisant la fonction INDIRECT et en utilisant la plage de noms dans H4 (Canada, un nom ci-dessus pour F3: F7), nous obtenons la somme totale d'un visiteur.
Si nous créons une liste de données en utilisant la validation des données pour les pays et appliquons cette liste à H4
Et en changeant les différents noms de pays du Canada à l'Inde, aux États-Unis, en Australie ou à Singapour, nous obtenons la somme totale du visiteur dans I4 à l'aide de la fonction INDIRECT d'Excel.
Donc, pour chaque cas, nous ne changeons pas la formule Excel indirecte, qui se trouve dans la cellule I4; nous modifions la référence du nom dans H4 et calculons la somme des visiteurs pour chaque pays.
Nous pouvons aller plus loin et référencer les noms au niveau de la feuille de calcul en utilisant également la fonction INDIRECT dans Excel. Par exemple, nous allons maintenant utiliser la fonction INDIRECT en référence à différentes feuilles du classeur.
Nous avons créé différentes feuilles avec les noms de pays pour le nombre total de ventes réalisées avec chaque canal de recherche pour différents pays.
Pour l'Inde,
Pour les USA,
De même, pour d'autres pays.
Encore une fois, nous calculerons les ventes totales effectuées via chaque canal en utilisant la fonction INDIRECT d'Excel pour différentes feuilles avec les noms de pays comme référence.
Dans ce cas, puisque nous avons des données de vente dans différentes feuilles, nous devrons concaténer différents éléments qui sont la plage de données, donc le ref_text commencera par le nom de la feuille, qui est l'Inde dans ce cas en B4, alors nous allons utilisez l'opérateur & appelé esperluette qui est utilisé pour la concaténation.
Ainsi, nous obtenons la somme totale des ventes pour l'Inde; maintenant, si nous changeons le nom du pays de la feuille dans B4 de l'Inde vers d'autres pays comme les États-Unis, Singapour ou le Canada, nous obtiendrons la valeur de vente totale de ces pays.
Encore une fois, dans ce cas, nous avons simplement changé les valeurs de ref_text dans la cellule B4 sans changer la principale formule Excel indirecte, qui est en C4, pour obtenir la somme des ventes pour différents pays qui se trouvent dans différentes feuilles à l'aide de la fonction INDIRECT.
Maintenant, nous allons utiliser la fonction INDIRECT en utilisant la référence R1C1 pour obtenir la valeur de vente du mois dernier. Nous avons des ventes pour différents mois dans les colonnes B, C et D pendant trois mois de janvier, février et mars qui peuvent atteindre des mois supplémentaires comme avril, mai, juin, etc. Ainsi, la table de vente augmentera toujours en fonction de l'ajout de la vente du mois prochain. Donc, dans cet exemple Excel INDIRECT, nous voulons calculer la vente du mois dernier.
Donc, ici le dernier mois est mars, et nous voulons calculer la vente totale du mois de mars, qui est de 249 344 $, en utilisant la fonction INDIRECT.
Dans cet exemple Excel INDIRECT, nous trouverons l'utilisation du deuxième argument de la fonction INDIRECT
INDIRECT (ref_text, a1)
a1: qui est un argument optionnel, c'est une valeur logique (une valeur booléenne) qui spécifie quel type de référence (ref_text) est contenu dans la cellule
si a1 est vrai (1) ou omis, ref_text est considéré comme une référence de style A1
si a1 est faux (0), ref_text est interprété comme un style R1C1
En quoi le style A1 est-il différent du style R1C1?
Normalement, la fonction INDIRECT d'Excel utilise la notation A1. Chaque adresse de cellule se compose d'une lettre de colonne et d'un numéro de ligne. Cependant, la fonction INDIRECT d'Excel prend également en charge la notation R1C1. Dans ce système, la cellule A1 est appelée cellule R1C1, la cellule A2 comme R2C1, etc.
Pour passer à la notation R1C1, choisissez l'option Fichier-> pour ouvrir la boîte de dialogue des options Excel, cliquez sur l'onglet de formule et cochez l'option de style de référence R1C1. Maintenant, vous remarquerez que les lettres des colonnes se transforment toutes en chiffres. Et toutes les références de cellule dans vos formules s'ajustent également.
Ainsi, lorsque nous utilisons le style R1C1 dans la fonction INDIRECT d'Excel, nous passons la valeur a1 comme false, et si nous utilisons le style A1, nous passons la valeur true à a1.
Maintenant, dans cet exemple Excel INDIRECT, nous utiliserons le style R1C1 en raison de l'exigence car les colonnes avec mois augmenteraient.
En utilisant le style R1C1, la valeur de vente totale est dans la ligne 10, et la dernière valeur de colonne, qui est dans la colonne D, sera calculée à l'aide de la fonction COUNTA , qui donnera le dernier numéro de colonne contenant une valeur. Donc, count (10:10) dans ce cas donnera 4, dont le numéro de colonne dans le style R1C1. Nous avons donc contacté cette valeur pour obtenir R10C4 qui contient la valeur souhaitée qui est la valeur de vente totale du mois dernier (249 344 $).
Et, puisque nous utilisons le style R1C1 ici, nous passerons une valeur fausse pour l'argument a1.
Maintenant, ajoutons une colonne de plus avec la vente du mois d'avril.
Alors, obtenez de nouvelles valeurs de ventes du mois dernier correspondant à celles du dernier mois d'avril. De même, si nous ajoutons plus de mois, nous obtiendrons toujours la nouvelle valeur de vente du mois dernier sans changer la formule Excel indirecte pour chaque mois. C'est la spécialité de la fonction INDIRECTE dans Excel.
Comment personnaliser le tableau de table dans la fonction INDIRECT Excel VLOOKUP?
Nous avons cinq pays et un canal de recherche et la vente que le site Web a effectuée via ces différents canaux de différents pays. Maintenant, dans la cellule B3, nous voulons obtenir la vente qui a été effectuée par le biais de recherches biologiques à partir du pays États-Unis.
Pour chaque table pour les différents pays, nous avons nommé chaque table avec un nom court pour le pays, Ind pour l' Inde, les États pour les États - Unis, Can pour le Canada, et ainsi de suite .
Si nous utilisons directement la fonction vlookup Excel INDIRECT pour obtenir la valeur, comme ci-dessous
Nous obtenons une erreur car la fonction INDIRECT Excel vlookup n'est pas capable de reconnaître le tableau de table passé comme valeur de la cellule B2, nous allons donc utiliser la fonction INDIRECT dans Excel pour que le vlookup obtienne le tableau_table reconnu comme un argument valide.
Si nous modifions les valeurs de B1 et B2, nous obtiendrons des valeurs de vente différentes dans B3, selon les besoins.
Remarque: les plages de noms ne sont pas sensibles à la casse. Par conséquent - Ind, IND et InD sont tous identiques dans Excel.
Dans le prochain exemple Excel INDIRECT, nous utiliserons la fonction INDIRECT d'Excel pour créer une liste dépendante. Nous avons une liste de la position d'un employé dans une entreprise; puis, pour chaque poste, nous avons les noms des employés.
Maintenant, dans la cellule I2, nous allons créer une liste pour les différents grades d'employés en tant que leurs postes, et dans I3, nous afficherons une liste qui dépendra de la valeur de I2. Si I2 est un administrateur, alors I3 devrait refléter les noms des administrateurs qu'Andrew et Micheal; si I2 est Manager, alors I3 doit refléter les noms du manager qui est Camille, David, Davis et William et de la même manière pour le superviseur.
Dans ce cas, encore une fois, nous utiliserons la fonction INDIRECT dans Excel pour créer la liste dépendante. Validation des données, dans les paramètres sélectionnant les critères de validation sous forme de liste et dans le champ source, nous utiliserons la fonction INDIRECT dans Excel comme suit.
= INDIRECT ($ I $ 2)
Lorsque nous sélectionnons les noms des administrateurs dans I3 se présente comme une liste avec les noms Andrew et Micheal, lorsque I2 est Manager, I3 reflète les noms des managers Camille, David, Davis et William et de même pour le superviseur, comme indiqué ci-dessous dans la figure.
Choses dont il faut se rappeler
- L'argument ref_text doit être une référence de cellule valide, sinon INDIRECT renverra le #REF! Erreur. L'argument ref_text peut faire référence à un autre classeur.
- Si ref_text fait référence à un autre classeur (une référence externe), l'autre classeur doit être ouvert. Si le classeur source n'est pas ouvert, INDIRECT renvoie le #REF! Valeur d'erreur.
- Si ref_text fait référence à une plage de cellules en dehors de la limite de ligne de 1 048 576 ou de la limite de colonne de 16 384 (XFD), INDIRECT renvoie un #REF! Erreur.
