Utilisation de la fonction INDIRECTE dans Microsoft Excel

■ Mes formules de feuille de calcul contiennent souvent des références à des cellules, des plages ou les deux. Plutôt que de changer ces références dans mes formules, puis-je placer les références dans leurs propres cellules afin de pouvoir facilement changer mes références de cellule ou de plage sans changer mes formules sous-jacentes?

■ Chaque feuille de calcul d’un classeur répertorie les ventes mensuelles d’un produit dans la cellule D1. Existe-t-il un moyen simple d’écrire et de copier une formule qui répertorie les ventes de produits de chaque mois dans une seule feuille de calcul?

■ Supposons que je totalise les valeurs dans la plage A5: A10 avec la formule SOMME (A5: A10). Si j’insère une ligne vide quelque part entre les lignes 5 et 10, ma formule se met automatiquement à jour en SOMME (A5: A11). Comment puis-je écrire une formule de sorte que lorsque j’insère une ligne vide entre les lignes 5 et 10, ma formule totalise toujours les valeurs de la plage d’origine, A5: A10?

■ Comment puis-je utiliser la fonction INDIRECTE dans une formule pour lire la partie du nom de plage d’une formule dans une feuille de calcul?

■ Mon classeur contient les ventes dans chaque pays pour chacun des produits de mon entreprise. Comment puis-je facilement regrouper ces données dans une seule feuille de calcul récapitulative?

■ Existe-t-il un moyen simple de répertorier toutes les feuilles de calcul dans un classeur?

La fonction INDIRECTE est probablement l’une des fonctions Microsoft Excel les plus difficiles à maîtriser. Cependant, savoir utiliser la fonction INDIRECTE vous permet de résoudre de nombreux problèmes apparemment insolubles. Essentiellement, toute référence à une cellule dans la partie INDIRECTE d’une formule entraîne l’évaluation immédiate de la référence de cellule pour égaler le contenu de la cellule. Pour illustrer l’utilisation d’INDIRECT, consultez le fichier Indirectsimpleex.xlsx, qui est illustré à la figure ci-dessous.

FIGURE : Voici un exemple simple de la fonction INDIRECTE.

Dans la cellule C4, entrez la formule = INDIRECT (A4). Excel renvoie la valeur 6 car la référence à A4 est immédiatement remplacée par la chaîne de texte “B4” et la formule est évaluée comme = B4, ce qui donne la valeur 6. De même, la saisie de la formule = INDIRECT (A5) dans la cellule C5 renvoie la valeur dans la cellule B5, qui est 9.

Réponses aux questions de cet article

Cette section fournit les réponses aux questions répertoriées au début de cet article.

Mes formules de feuille de calcul contiennent souvent des références à des cellules, des plages ou les deux. Plutôt que de changer ces références dans mes formules, puis-je placer les références dans leurs propres cellules afin de pouvoir facilement changer mes références de cellule ou de plage sans changer mes formules sous-jacentes?

Dans cet exemple, les données que vous utilisez sont contenues dans le fichier Sumindirect.xlsx, illustré à la figure  ci-dessous. La gamme de cellules B4: H16 répertorie les données de ventes mensuelles de six produits sur une période de 12 mois.

Vous calculez actuellement les ventes totales de chaque produit pendant les mois 2 à 12. Un moyen simple de faire ce calcul consiste à copier la formule SOMME (C6: C16) de C18 à D18: H18. Supposons cependant que vous souhaitiez modifier les mois totalisés. Par exemple, vous pouvez souhaiter des ventes totales pour les mois 3 à 12. Vous pouvez modifier la formule de la cellule C18 en SOMME (C7: C16), puis copier cette formule dans D18: H18, mais l’utilisation de cette approche est problématique car vous devez copier la formule de C18 vers D18: H18 et, sans regarder le formules, personne ne sait quelles lignes sont ajoutées.

FIGURE 1 :Vous pouvez utiliser la fonction INDIRECT pour modifier les références de cellule dans les formules sans modifier la formules elles-mêmes.

La fonction INDIRECT offre une autre approche. Placez les lignes de début et de fin de votre

sommation dans les cellules D2 et E2. Ensuite, en utilisant la fonction INDIRECTE, il vous suffit de changer les références de ligne de début et de fin dans D2 et E2, et les sommes sont mises à jour pour inclure les lignes que vous vouloir. De plus, en regardant les valeurs en D2 et E2, vous pouvez voir d’un coup d’œil quelles lignes (mois) sont ajouté. Il vous suffit de copier la formule SOMME (INDIRECT (C $ 3 & $ D $ 2 & ’’: ’’ & C $ 3 & $ E $ 2)) de C18 à J18: H18.

Si vous souhaitez voir comment Excel évalue une référence à la fonction INDIRECT, utilisez ce qui suit . Cliquez sur une partie de la formule (par exemple, 3 $ C), puis appuyez sur F9. Excel vous montre la valeur de la partie sélectionnée de la formule. Par exemple, C $ 3 correspond à C. Assurez-vous d’appuyer sur Echap pour annuler le changement. Chaque référence de cellule dans la partie INDIRECTE de cette formule est évaluée à égal le contenu de la cellule. C $ 3 est évalué comme C, $ D $ 2 est évalué comme 6 et $ E $ 2 est évalué comme 16. Avec l’esperluette (&) comme symbole de concaténation, Excel évalue cette formule comme SOMME (C6: C16), c’est exactement ce que vous voulez. La formule dans C18 renvoie la valeur 38 + 91 +. . . 69  = 607. Dans la cellule D18, la formule est évaluée comme SUM (D6: D16), ce qui est le résultat souhaité. Si tu veux additionnez les ventes pendant les mois 4 à 6, vous entrez simplement 8 dans D2 et 10 dans E2, puis la formule dans C18 renvoie 33 + 82 + 75 = 190. (Pour plus d’informations sur l’utilisation de l’esperluette pour concaténer des valeurs, voir l’article 5, “Texte les fonctions.”)

Chaque feuille de calcul d’un classeur répertorie les ventes mensuelles d’un produit dans la cellule D1. Existe-t-il un moyen simple écrire et copier une formule qui répertorie les ventes de produits de chaque mois dans une seule feuille de calcul? Le fichier Indirectmultisheet.xlsx (voir figure 22-3) contient sept feuilles de calcul. Dans chaque feuille de calcul, cellule D1 contient des données sur les ventes d’un produit au cours d’un mois donné. Supposons que la feuille 1 contienne Ventes du mois 1, la feuille 2 contient les ventes du mois 2, etc. Par exemple, les ventes du mois 1 sont égales à 1, en

Le mois 2, il est égal à 4, et ainsi de suite.

FIGURE 3 Cette figure montre les ventes mensuelles (mois 1 à 7) d’un produit répertorié à l’aide de la fonction INDIRECT.

Supposons maintenant que vous souhaitiez compiler une liste des ventes de chaque mois dans une seule feuille de calcul. Un fastidieux serait de lister les ventes du mois 1 avec la formule = Sheet1! D1, les ventes du mois 2 avec la = Formule Feuille2! D1, et ainsi de suite jusqu’à ce que vous répertoriez les ventes du mois 7 avec la formule = Feuille7! D1. Si vous avoir 100 mois de données, cette approche prend du temps et est sujette aux erreurs. Un beaucoup plus élégant L’approche consiste à répertorier les ventes du mois 1 dans la cellule E10 de la feuille Sheet1 avec la formule INDIRECTE (10 $ C & D10 & ’’! D1 ’’). Excel évalue $ 10 $ comme «feuille», D10 comme «1» et «»! D1 »» comme chaîne de texte «! D1». La formule entière est évalué comme = Sheet1! D1, ce qui donne les ventes du mois 1, situé dans la cellule D1 de Sheet1. Copier ceci formule à la plage E11: E16 répertorie les entrées dans la cellule D1 de la feuille 2 à la feuille 7. Lorsque la formule dans la cellule E10 est copiée dans la cellule E11, la référence à D10 passe à D11 et la cellule E11 renvoie la valeur situé à Feuille2! D1.

Supposons que je totalise les valeurs dans la plage A5: A10 avec la formule SUM (A5: A10). Si j’insère

une ligne vide quelque part entre les lignes 5 et 10, ma formule se met automatiquement à jour

SOMME (A5: A11). Comment puis-je écrire une formule pour que lorsque j’insère une ligne vide entre les lignes 5 et 10, ma formule totalise toujours les valeurs de la plage d’origine, A5: A10?

La feuille de calcul SOMME (A5: A10) du fichier Indirectinsertrow.xlsx (illustrée à la figure 22-4) illustre plusieurs façons de totaliser les nombres dans la plage de cellules A5: A10. Dans la cellule A12, vous pouvez entrer le SOMME traditionnel (A5: A10) formule, qui donne 6 + 7 + 8 + 9 + 1 + 2 = 33.

De même, la formule SOMME ($ A $ 5: $ A $ 10) dans la cellule E9 donne une valeur de 33. Cependant, si vous insérez une ligne entre les lignes 5 et 10, les deux formules tentent de totaliser les cellules dans la plage A5: A11.

FIGURE 4 :Cette figure montre plusieurs façons de additionner les valeurs dans la plage de cellules A5: A10.

Avec la fonction INDIRECTE, vous avez au moins deux façons de totaliser les valeurs dans la plage A5: A10.

Entrez la formule SUM (INDIRECT (‘’ A5: A10 ’’)) dans la cellule F9. Parce qu’Excel traite INDIRECT («» A5: A10 »») comme

la chaîne de texte ‘’ A5: A10 ’’, si vous insérez une ligne dans la feuille de calcul, cette formule totalise toujours les entrées

A5: plage de cellules A10.

Une autre façon d’utiliser la fonction INDIRECT pour totaliser les entrées dans la plage A5: A10 est

 SOMME (INDIRECT (‘’ A ’’ & C4 & ’’: A ’’ & D4)), qui est la formule entrée dans la cellule C6. Excel traite

la référence à C4 comme 5 et la référence à D4 comme 10, donc cette formule devient SUM (A5: A10). L’insertion d’une ligne vide entre la ligne 5 et la ligne 10 n’a aucun effet sur cette formule car la référence à C4 est toujours traité comme un 5, et la référence à D4 est toujours traitée comme un 10. Dans la figure 22-5, vous pouvez voir les sommes calculées par les quatre formules après qu’une ligne vierge est insérée sous la ligne 7. Vous pouvez trouver ceci données de la feuille de calcul Ligne insérée dans le fichier Indirectinsertrow.xlsx. Comme vous pouvez le voir, les formules SUM classiques, qui n’utilisent pas la fonction INDIRECT, ont changé pour additionner les entrées dans la plage A5: A11, de sorte que ces formules donnent toujours une valeur de 33. Les deux SOMME les formules qui utilisent la fonction INDIRECTE continuent d’ajouter les entrées dans la plage A5: A10, donc la valeur de 2 (maintenant dans la cellule A11) n’est plus incluse dans la somme calculée. Les formules SOMME qui utilisez la fonction INDIRECT donne une valeur de 31.

FIGURE 5: Cette figure montre les résultats des formules SOMME après avoir inséré une ligne vierge dans la plage d’origine.

Comment puis-je utiliser la fonction INDIRECT dans une formule pour lire la partie du nom de plage d’une formule dans une feuille de calcul?

Supposons que vous ayez nommé plusieurs plages dans une feuille de calcul pour correspondre aux ventes de produits trimestrielles.

(Voir la figure  ci-dessous et le fichier .) Par exemple, la plage D4: E6 (nommée Quarter1) contient fictif premier quart Ventes de divers Microsoft des produits.

FIGURE 6 Utilisez la fonction INDIRECT pour créer une référence à un nom de plage dans une formule.

Ce serait formidable d’écrire une formule qui peut facilement être copiée et qui rapporte ensuite les ventes de chaque produit dans chaque trimestre dans une seule plage rectangulaire de la feuille de calcul, comme indiqué dans H17: J20. Vous penseriez pouvoir entrer la formule = RECHERCHEV (H $ 16, $ G17,2, FAUX) dans la cellule H17, puis copiez cette formule dans la plage H17: J20. Malheureusement, Excel ne reconnaît pas G17 $ en référence au nom de la plage Quarter1. Au contraire, Excel pense simplement que $ G17 est la chaîne de texte

«Quarter1». La formule renvoie donc une erreur #NA. Pour remédier à ce problème, entrez simplement le  = RECHERCHE(H $ 16, INDIRECT ($ G17), 2, FALSE) formule dans la cellule H17, puis copiez cette formule dans le H17: gamme J20. Cela fonctionne parfaitement! INDIRECT (G17 $) est évalué comme Trimestre1 et est maintenant reconnu comme nom de plage. Vous avez maintenant généré facilement les données de vente de tous les produits au cours des quatre trimestres.

Mon classeur contient les ventes dans chaque pays pour chacun des produits de mon entreprise. Comment puis-je regrouper facilement ces données dans une seule feuille de calcul récapitulative?

Les cellules E7: E9 du fichier  contiennent les ventes de voitures, de camions et d’avions sur chaque continent. Chaque continent a Ses posséder feuille de travail. Comment Pouvez vous résumer cette Les données en un seul feuille? Dans la feuille de calcul Résumé, créez le résumé illustré à la figure  ci-dessous.

FIGURE 7 Utilisez la fonction INDIRECT pour créer un résumé des ventes de produits.

Copiez la formule INDIRECTE (E $ 6 & “!” & $ C7) de la cellule E7 dans la plage E7: I9. Cette formule crée le

= Formule LA! E7, qui calcule les ventes de voitures en Amérique latine. La copie de cette formule retire les ventes

pour chaque type de produit sur chaque continent.

Dans la feuille de calcul Another Summary (voir la figure 22-7), vous pouvez créer le même tableau dans une manière différente; vous pouvez automatiser la génération des adresses de cellule à partir desquelles extraire voiture, camion,

et les ventes d’avions en copiant la formule ADRESSE (LIGNE (), COLUMN () + 2) de C7 à C8: C9. Cela génère colonne E dans l’adresse de la cellule, puis lignes 8 et 9. La fonction LIGNE () entrée dans une cellule renvoie la ligne de la cellule nombre,et le COLONNE() une fonction entré par lui-même dans une cellule Retour le colonne nombre du courant cellule (dans cette Cas, 3). Quand entré comme partie du troisième argument de le ADRESSE une fonction, le terme COLONNE () + 2 effectivement Retour le colonne étiquette pour le cellule deux Colonnes A la droite de le courant cellule (dans Cette Cas, colonne E). Alors vous pouvez copie le INDIRECTE (E $ 6 & “!” & $ C7) formule de cellule E7 à E7: I9 gamme. Existe-t-il un moyen simple de répertorier toutes les feuilles de calcul dans un classeur? Dans l’exemple précédent, il était facile de répertorier les noms des feuilles de calcul. Supposons que vous ayez un classeur avec 100 feuilles de calcul ou plus. Si vous souhaitez libérer toute la puissance de la fonction INDIRECT pour extraire des données à partir d’un sous-ensemble de ces feuilles de calcul, vous avez besoin d’une méthode efficace pour répertorier le nom de chaque feuille de calcul dans un classeur. À liste tout feuille de travail noms dans un classeur Efficacement, procédez comme suit. (Voir classeur  et Figure 8 et Figure 9.)

  1. 1. Comme le montre la figure 8, dans l’onglet Formules, sélectionnez Définir les noms; au nouveau nom

boîte de dialogue, créez un nom de plage. Appelez la gamme Worksheets avec le = GET.WORKBOOK (1) formule.

GET.WORKBOOK (1) est une macro, donc le correctif du fichier suf doit être .xlsm.

  1. 2. Sélectionnez un tableau horizontal contenant autant de lignes que votre classeur a de feuilles de calcul, bien que

dans cet exemple, il est facile de voir qu’il n’y a que trois feuilles de calcul. Si vous ne connaissez pas

nombre de feuilles de calcul dans un classeur, vous pouvez utiliser la fonction Excel 2013 SHEETS (). En cellule H13, la fonction SHEETS () renvoie le nombre de feuilles de calcul dans un classeur (dans ce cas, 3). Par ailleurs, la nouvelle formule Excel 2013 SHEET () entre le numéro de la feuille de calcul actuelle dans le classeur. Si vous avez entré = SHEET () n’importe où dans la feuille de calcul Bob, la formule serait entrez 1; n’importe où dans la feuille de calcul Allen, 2; n’importe où dans la feuille de calcul Jill, 3.

  1. 3. Dans le coin gauche de la plage sélectionnée (dans ce cas, H15), tapez = Feuille de calcul et appuyez sur

Ctrl + Maj + Entrée.

Le nom de la plage de la feuille de calcul agit comme une formule de tableau (voir Chapitre 87, «Utilisation des fonctions de tableau», pour plus de détails sur les formules matricielles), vous devez donc appuyer sur Ctrl + Maj + Entrée pour que la formule travail. Dans la plage de cellules H15: J15, vous voyez maintenant le nom du classeur entre crochets suivi des noms des feuilles de calcul, répertoriés dans l’ordre des feuilles de calcul. Pour renvoyer chaque nom de feuille de calcul dans une cellule distincte, utilisez simplement les fonctions DROITE et TROUVER à partir de article 5 pour extraire chaque nom de feuille de calcul en retirant tous les caractères à droite du carré support. Copiez la formule = DROITE (I15, NBCAR (I15) -TROUVE (“]”, I15,1)) de H16 à I16: J16. Vous voyez maintenant noms de feuille de calcul répertoriés dans la plage H16: J16. Cependant, si les noms des feuilles de calcul changent, le H15: J15

la gamme reste inchangée. Si vous souhaitez que les noms des feuilles de calcul soient mis à jour après leur modification, vous doit supprimer le contenu de H15: J15 et ressaisir la formule du tableau Feuille de calcul.

FIGURE 8 : Créez un nom de plage qui peut être utilisé pour extraire les noms de feuille de calcul.

FIGURE 9: ci-dessus Les noms des feuilles de calcul sont extraits dans la ligne 16 et le nombre de feuilles est extrait, en utilisant la FEUILLE () une fonction.

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Initiation à Excel

Fonctions Excel

Excel VBA

Macros VBA Utiles

Plus d'outils

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x
()
x