Recherche Références des cellules

Fonction INDIRECT

Temps de lecture 3 minutes

La fonction INDIRECT va nous permettre de créer des références dynamiques. Ainsi vous allez pouvoir concevoir des tableaux de bords ultra-professionnels 👍

Présentation de la fonction INDIRECT

Si vous allez sur le site de Microsoft, les explications à propos de la fonction INDIRECT ne sont pas très éclairantes (il faut l'avouer 😉)

En fait, la fonction INDIRECT permet d'interpréter une chaîne de caractères en tant que référence d'une cellule 🤔

=INDIRECT("Référence d'une cellule")

Appliquer dans l'exemple suivant, si j'écris la référence A1 entre guillemet, la fonction INDIRECT retourne le contenu de la cellule A1

=INDIRECT("A1")

Comment ecrire la fonction INDIRECT

Comme vous le voyez, quand la référence est entre guillemet, la fonction INDIRECT "interprète" correctement la cellule et donc le résultat à afficher. Si la référence est sans guillemet, il y a une erreur

Exemple : Comment remplir un tableau de synthèse ?

Prenons comme exemple un classeur qui contient la feuille des temps pour 5 salariés (une feuille par salarié)

Feuille des temps sur plusieurs onglets

Une chose très importante à noter c'est que toutes les feuilles de calcul ont la même structure. Les informations sur le lundi sont en ligne 4, pour les mardis sont en ligne 5 et ainsi de suite.

Cette remarque n'est pas anodine car c'est cette homogénéité qui va être utilisée.

Maintenant, la question c'est de savoir comment nous allons pourvoir remplir la feuille de synthèse à partir des 5 autres feuilles

Comment remplir toutes les cellules de la feuille des temps

Etape 1 : Ecrire la première liaison

Nous allons écrire la première liaison entre la feuille synthèse et la feuille du salarié Pierre pour la journée du Lundi

  1. Dans la cellule B3, saisissez le signe =
  2. Puis sélectionner la cellule qui contient le temps de Pierre (cellule B4)

=Pierre!B4

Liaison avec la feuille Pierre pour la journee du lundi

Etape 2 : Analysons la formule

La formule se décompose en 4 catégories

  • Le nom de la feuille (Pierre)
  • Le séparateur entre le nom de la feuille et la référence de la cellule (le point d'exclamation)
  • La référence de la colonne (B)
  • La référence de la ligne (4)

Etape 3 : Ecrire les liaisons du lundi pour les autres salariés

Ensuite, nous allons refaire la même opération pour les autres salariés

Liaison pour tous les salaries pour la journee du lundi

En en affichant les formules, nous avons le résultat suivant

Formule des 5 liaisons

Entre toutes ces formules, la seule chose qui changent, c'est le nom de feuille de calcul. Donc, l'idée c'est d'utiliser le nom des cellules B2, C2, D2, ... pour l'utiliser dans la référence 😲😲😲

Etape 4 : Intégrer la référence dans la fonction INDIRECT

Cette étape va vous sembler anodine mais elle est fondamentale. Elle va nous assurer que la fonction INDIRECT retourne la valeur que nous attendons.

Comme nous l'avons vu en introduction, dans une fonction INDIRECT, la référence doit être écrite entre guillemet

=INDIRECT("Pierre!B4")

Englober la reference dans la fonction INDIRECT

Etape 5 : Transformer la référence

C'EST LA QUE TOUT SE JOUE !

Nous allons transformer la référence pour mettre le nom de la feuille comme variable. Et nous allons utiliser le contenue de la cellule B2 dans la référence.

=INDIRECT(B2&"!B4")

Utiliser le nom dune cellule dans la reference

Etape 6 : Analysons l'écriture 🧐

Premier constat CA MARCHE 😀😎

Oui, dans la cellule B3, nous avons bien le résultat attendu 👍

  • Le nom du salarié est contenu dans la cellule B2
  • La référence à B2 n'est pas entre guillemet
  • Le symbole & permet de faire la liaison entre la cellule B2 et le reste de la référence
  • Entre les guillemets, nous avons le reste de la référence Y COMPRIS LE SYMBOLE ❗

Si l'écriture n'est pas parfaite, la fonction renverra une erreur.

Etape 7 : Recopie de la formule

En recopiant la formule pour les autres salariés pour le lundi, ça marche toujours car la référence de la colonne change (principe de la référence mixte)

=INDIRECT(B$2&"!B4")

Recopie de la fonction INDIRECT

Etape 8 : Et pour les autres jours ?

Ici, il faut transformer le numéro de ligne en variable. Et pour ça il y a la fonction LIGNE 😉 Et là encore, comme c'est notre variable, nous devons l'écrire en dehors des guillemets.

=INDIRECT(B$2&"!B"&LIGNE()+1)

Reference de la ligne variable

L'écriture LIGNE()+1 s'explique par la différence de ligne entre la feuille "Recap" et les feuilles des salariés 😉

Le fichier de cet exemple se trouve ici.

Related posts

Faire une RECHERCHEV décalée

Frédéric LE GUEN

RECHERCHEV sur 2 colonnes

Frédéric LE GUEN

Référence absolue et relative

Frédéric LE GUEN

37 commentaires

JeanNoel 24/06/2021 at 16:52

Bonjour,

Merci pour votre tuto très clair. Je rencontre cependant un problème lorsque je veux faire la somme d'un même bloc de cellules de plusieurs feuilles.
En cellule A20 le nom d'une personne, par exemple DUPONT, en A22 le nom d'une autre personne, par exemple Martin
Je dispose d'une feuille Dupont et d'une feuille Martin

Si je fait : =SOMME(Dupont!A6:A10;'Martin'!A6:A10)
Ce qui en indirect donne =SOMME(INDIRECT("'"&A20&"'"&"!A6:A10");INDIRECT("'"&A22&"'"&"!A6:A10"))

Par contre si je fait =SOMME('Dupont:Martin'!A6:A10)
Ce que j'ai traduit en indirect par =somme(indirect("'"&A21&":"&A23&"'"&"!A6:A10"))
j'obtiens un message d'erreur #REF!

S'il n'y a que deux feuilles, la première méthode fonctionne mais s'il y a plusieurs feuilles, cette formule devient bien plus longue et complexe, alors que l'autre formule fait la somme de la 1ère feuille à la dernière feuille et donc ne se rallonge pas si on ajout des feuilles.

Pouvez-vous me dire quelle erreur j'ai commis dans ma formule ? Je ne parviens pas à la déceler.

Merci de m'avoir lu

Jean-Noël

Répondre
Frédéric LE GUEN 24/06/2021 at 17:01

Je ne pense pas que la fonction INDIRECT a été développée pour comprendre les références sur plusieurs feuilles de calcul (les références 3D). Le plus simple serait de combiner toutes vos feuilles en 1 seule et là vous faites tous vos calculs depuis cette feuille. C'est le mieux

Répondre
Ella 18/06/2021 at 17:14

Bonjour,

Je travaille sur une feuille excel d'environ 60 000 lignes. Les lignes sont renseignées de façon homogène sauf pour les lignes ayant du contenu aux colonnes G et H, donc pour ces lignes les autres cellules sont vides sauf en G et H. Donc après un certain nombre de ligne (pas toujours le même nombre) totalement renseignées il y a une ligne avec juste les cellules G et H remplies et de nouveaux des lignes entières remplies et ainsi de suite. Et donc je dois reproduire les contenus de G et H dans dux cellules devant toutes les lignes suivantes jusqu'a la prochaine lignes où le contenu de G et H change et ainsi de suite.
Y aurait il un moyen a tout hasard d'effectuer cette tâche automatiquement pour le gain de temps et le risque d'erreur.

Répondre
E-MUR 05/05/2021 at 08:20

Bonjour,
Nous sommes en train de constituer un rapport d'auto évaluation pour notre établissement.
Le premier onglet permet la saisie des commentaires (critères / services / Points positifs / points à améliorer)
Le second nous permet d'enregistrer les résultats par critères et services.
Les onglets correspondants aux services permettent d'éditer un rapport individualisé.
=> Résultats par critères et services = OK (mise en forme graphique radar)
=> Synthèse des commentaires par critères = pas encore en place...
Chose que je n'arrive pas à faire, c'est le report des données de l'onglet "saisie" par critères et par services.
1) Report N° critère (exemple: ServiceA = 9 critères / ServiceB = 11 critères)
2) Report commentaire en lien n° critère, service pour les points positifs
3) Report commentaire en lien n°critère, service pour les points à améliorer

Pourriez-vous m'aider à consolider mon tableau?
Merci par avance de votre aide

Répondre
Frédéric LE GUEN 05/05/2021 at 08:34

Faites votre formulaire avec Forms https://www.youtube.com/watch?v=wYWNhiJJmTs ça sera plus simple

Répondre
E-MUR 05/05/2021 at 09:18

Je vous remercie pour votre retour rapide. Ce point nous servira pour déployer d'autres outils en interne.

Néanmoins, cela ne réponds pas à mon besoin. Nous avons déjà collecté des données pour 9 services.
Chose que me demande ma hiérarchie, c'est que les éléments de réponses de l'onglet "Saisie" soient reportés automatiquement dans les onglets "Service" et par critères.
Sur les deux premières saisies, je n'ai fais que du =cellule de la feuille de "Saisie"

Pour faciliter les choses, il faudrait que l'onglet "Saisie" incrémente les onglets services au fur et à mesure.
La fonction indirect me semblait adaptée, je peux me tromper... à moins que ce ne soit une autre formule ou pire, impossible à faire.

Répondre
Frédéric LE GUEN 05/05/2021 at 10:29

Ben là c'est un vrai projet à réaliser. Si vous voulez que je m'en charge, il faut que j'ai accès aux données pour évaluer le temps à y consacrer et du prix que cela va couter. Voulez-vous que je prenne en charge ce projet ?

Répondre
E-MUR 05/05/2021 at 11:21

Autant le Forms simplifierai la gestion des données et l'exploitation des résultats, mais nous sommes partis sur tableur Excel avec nos connaissances respectives.
Au vu de l'urgence du besoin (ultimatum de résultat pour le 06/05/2021) et de l'expression du demandeur, le coût d'une étude et d'un développement ne rentre dans aucun budget pour notre établissement de santé.

Votre site et vos partages sont une mine d'or pour les néophytes comme moi. Je vais y travailler en back-office pour trouver la formule qui va bien, qui nous servira pour les années suivantes. C'est aussi un challenge pour moi de créer de nouveaux outils.
Je vous remercie en tout cas pour votre réactivité et votre soutien.

Marguerite BELE 24/03/2021 at 14:59

Bonjour,
Comment adapter la formule pour récupérer les informations de plusieurs lignes simplement en étirant la formule ?
En prenant exemple sur votre fichier, j'aimerais réaliser le tableau ci-dessous dans l'onglet Report sans avoir à modifier dans la formule la colonne et la ligne des cellules dont je veux récupérer l'information.
A B
FR DE
1 Pays =INDIRECT(B1&"!B2") =INDIRECT(C1&"!B2")
2 CAPITALE =INDIRECT(B1&"!B3") =INDIRECT(C1&"!B3")
3 Superficie population =INDIRECT(B1&"!B4") =INDIRECT(C1&"!B4")

Merci d'avance pour votre aide

Répondre
Adrien PELERIN 20/03/2021 at 14:34

Bonjour,
Comment peut on faire pour trouver la somme des chiffres d'une date de naissance ? soit25/06/1965 = 2+5+0+6+1+9+6+5=34 d"ou 3+4 =7 7 etant le chiffre final ? merci de votre aide

Répondre
Frédéric LE GUEN 20/03/2021 at 14:48

Mais quel est l'intérêt de faire un truc pareil ?

Répondre
Nicolito 13/08/2021 at 08:46

Dans une cellule std
=SOMME(1*GAUCHE(D13;2)+1*DROITE(D13;4)+1*STXT(D13;4;2))

La cellule de saisie de la date (D13) doit être au format Txt par contre

Répondre
Ouelaa 07/02/2021 at 11:31

merci bien

Répondre
Dominique 09/06/2020 at 10:38

merci c'était très clair et je suis contente d'avoir deux exemples d'utilisation de la fonction indirect

Répondre
Dida coco 05/07/2019 at 23:09

Bonjour,
Merci chaleureusement, après des heures sur Internet à essayer de comprendre comment faire cette formule, au finale si simple grâce à vous. C'est exactement l'exemple recherché !

Répondre
Charles 18/01/2019 at 14:11

J'ai un classeur avec 4 feuilles correspondants à 4 produits A, B, C et D dont la structure de chacune d est identique. Enfin j'ai une 5e feuille bilan . J'ai mis une liste déroulante dans une cellule du bilan listant les 4 produits. Mon soucis est de trouver une formule me faisant une somme de la colonne C d'une de ces feuilles à chaque fois qu'elle est sélectionnée dans la liste déroulante .... aidez moi a trouver une parade svp

Répondre
fontaine 06/06/2018 at 18:08

Bonjour
peut-on mettre un INDIRECT dans une formule ?
je m'explique
j'ai une feuille de synthèse dans laquelle je récupère automatiquement les noms de mes onglets (par une macro)
j'aimerai pour chaque onglet savoir combien il y a de colonnes
Mes noms d'onglets n'ont pas d'espace dans leur nom
Les noms d'onglet sont en colonne A
et j'ai écrit =NBVAL(INDIRECT(A5)&"!1:1") puis =NBVAL(INDIRECT(A4)&"!1:1)")
qui me ramène 1 et pas du tout le nombre de colonnes dans les onglets
Quand j'évalue la formule l'INDIRECT est résolu en premier et ramène bien le nom de l'onglet
Puis à l'étape suivante il met un #ref et termine avec un résultat à 1

Répondre
toche 31/01/2018 at 14:57

bonjour, j'ai besoins d'aide sur un tableau est il possible d'avoir de l'aide
merci

Répondre
LAWRENCE Paul 14/10/2015 at 09:27

Bonjour.
Vos explications sont très claires. Une petite remarque cependant.
Vous écrivez: "Attention, il n’est possible d’attribuer une source autre que votre feuille active que depuis Excel 2010."
Voici une méthode qui fonctionne qu'elle que soit la version d'Excel:
Exemple dans Excel 2007.
Dans "Validation des données", si la Source se trouve dans une autre feuille, il faut qu'elle soit identifiée par un Nom que l'on détermine en utilisant le Menu Données puis Gestionnaire des noms.
Dans le Gestionnaire des noms, on crée un Nom qui fera référence à une cellule (ou à une plage de cellules) située dans une autre feuille que la feuille active.
En face de Nom:, écrire le nom choisi
En face de Zone: choisir classeur
En face de Fait référence à: cliquer sur la petite grille à droite et sélectionner la feuille puis la cellule ou la plage de cellules qui servira de ultérieurement de Source dans Données, Validation des données.
Pour que le Nom choisi soit valable, ne pas utiliser d'espaces, ni d'accents.
Pour éviter tout conflit, ne pas employer un Nom déjà existant dans Excel.
L'emploi de majuscules permet de le repérer comme nom créé.
Ce Nom pourra être utilisé dans toutes les formules de toutes les feuilles du classeur.
Cordialement

Répondre
Anonyme 16/02/2015 at 21:59

Bonjour
sous MO 2010 je souhaiterais appliquer la formule INDIRECT au 2ème mois de cette formule (ici Mars) =SOMME(Janvier:Mars!AB35) Mais je n'y arrive pas ! =SOMME(INDIRECT("Janvier:Mars!AB35")) Excel m'indique l'erreur #REF!
Merci de m'éclairer

Répondre
loubet 22/01/2015 at 11:35

Bonjour, desolée je suis un peu perdue: je souhaite mettre un champs multiple ( en fait deux choix "oui" ou" non)" et seulement dans le cas ou "oui " est chosi j aimerai que cela renvoi a une cas a remplir et que cela soit blocant si cette case n est pas completé..je ne sais pas si je suis claire mais merci par avance de votre aide

Répondre
Majérus Paul 22/12/2014 at 11:08

Bonjour,

Je voudrais ne pas bloquer la case de référence afin de pouvoir étirer ma formule tout en faisant toujours référence à une page. Est-ce possible ? Comment dois-je écrire cette formule ? D'avance, merci pour votre aide !

Répondre
Anonyme 03/03/2015 at 14:37

Bonjour

$A va bloquer la colonne A
$1 va bloquer la ligne 1

Donc si tu veux rester toujours dans la meme cellule : $A$1

Répondre
Lili 06/11/2014 at 22:55

Bonjour,

J'ai créer un fichier excel dans lequel on encode régulièrement des données.

Dans ma premier feuille, toutes les données sont encodées mais dans ma 2ème feuille, j'aimerais que dans celle-ci, seule certaines données soient repris automatique par rapport à ma 1ère feuille. (le tableau croisé ne me correspond pas car dans cette 2ème feuille, d'autres colonnes s'y ajouterons pour encoder d'autres données.

Pourriez-vous m'aider ?

Merci !

Répondre
christ 18/08/2014 at 07:34

bonjour a vous
j'ai vu votre professionnalisme...
et je voudrais savoir si vous pouvez m'aider pour la création d'un devis tres particulier en vba ou autre....
car je n'y connais rien et je suis une petite entreprise et j'aimerais que mes devis soient automatiques et personnel a mon entreprise...
j'ai essayé des logiciels tout fait mais cela ne me convient pas
donc je demande votre aide svp
merci
a+

Répondre
Pajude 25/06/2014 at 08:41

Bonjour,
J'ai suivi vos explications très claires pour cette fonction Indirect. Je suis sur Excel 2007, j'ai fait l'exercice et j'ai un souci. Mes cellules sont en format standard, dans ma feuille Global, la TVA remonte au format 0,196 pour 19.60%. J'ai modifié le format en %, le PB c'est que le PIB remonte en %
Merci de votre aide

Répondre
marc1325 04/06/2014 at 13:48

Bonjour,
merci pour ces précieux tutos.

Un petit détail qui ne fonctionne pas avec Excel 2010, la formule FormulaText permettant normalement d'afficher la définition d'une formule dans une cellule.
Toutefois, cette fonction est aisément redéfinissable par le biais d'un module et de la fonction personnalisée suivante :
Function FT(MyCell As Range)
FT = MyCell.Formula
End Function

Pour les détails d'implémentation de cette méthode, se référer à ce tutorial : https://www.youtube.com/watch?v=wpRGTvhp1cY

Répondre
Frédéric LE GUEN 01/09/2014 at 20:24

Dans Excel 2013, il y a la fonction FORMULETEXTE(référence) qui vous permet d'écrire dans une cellule la formule contenu dans une autre cellule.

Répondre
WIMI 11/05/2014 at 11:41

Bonjour,

Merci infiniment pour vos explications..Tout semble clair à présent pour moi.. Juste une question, est il possible de changer le No_index de la colonne de manière automatique pour des formules déjà établies?

Merci d'avance

Répondre
atoch 30/01/2014 at 21:08

bonjour,
je suis à la recherche d'une solution pour que les liens ne changent pas dans un sous dossier contenant ma feuille excel.
je m'explique : j'ai à la base un dossier "modèle" qui contient 6 sous-dossiers. Ce dossier "modèle sera copié et son nom va changer en fonction du client.
par contre aucun changement dans les sous-dossiers.est il possible de garder les liens dans les inters sous-dossiers ?

merci d'avance pour votre aide

atoch

Répondre
Laurence 18/11/2013 at 21:06

Bonjour,

J'essaie d'utiliser la formule indirect avec le format de cellule «date» (jj-mmm), mais je n'y parviens pas... Je dois avouer que la formule avec les espaces m'a quelque peu aidé, mais j'aimerais savoir si c'est possible avec des dates (c'est pour un fichier de feuille de temps hebdomadaires avec un onglet contenant le sommaire annuel).

Merci d'avance!

Répondre
said 30/10/2013 at 18:37

bjr,

c'est vraiment intéréssant merci pour ces explicatio,; toute fois je veux créer un fichier de plusieure onglets, et sur l'onglet recap je veux mettre deux menus déroulent ce qui fait deux choix conditionnelle pour recupéré les données des autres onglets, pouvez vous m'aider SVP? :)

merci :)

Répondre
desfetes 24/09/2013 at 19:04

Bonjour,

Pouvez-vous me dire sous qu'elle version excel travaillez vous car elle semble vraiment très sympa??

Répondre
Frédéric LE GUEN 25/09/2013 at 01:15

Excel 2013
Téléchargeable en haut à droite de cette page

Répondre
Zorba 24/08/2013 at 21:00

Bonjour,
Tout fonctionne parfaitement, merci ! Mais je souhaiterais savoir comment récupérer de façon automatique la liste des onglets ? Celle que vous mettez en colonne A... Et ce, sans macro s'il vous plaît !

Répondre
Frédéric LE GUEN 24/08/2013 at 23:39

Bonsoir,
Ce n'est malheureusement pas possible d'extraire les noms de vos feuilles sans passer par une macro. Mais c'est pas si dur de faire des macros ;) Pour votre besoin, juste 4 lignes de code suffisent et j'ai justement utlisé cet exemple dans le livre que j'ai écrit (cf référence en haut à droite des pages)

Répondre
Anonyme 08/07/2015 at 09:30

J'avais eu ce problème et j'ai trouvé une solution sur le web
(c'est un peu tiré par les cheveux mais ça marche (au moins en Excel 2010)):

1-Définir un nouveau nom/plage nommée (onglet Formules) (typiquement nom_feuilles) dans la zone "Fait référence à" entrez la formule
=LIRE.CLASSEUR(1)

2- Dans toutes les cellules du classeur, la formule suivante appliquée sur les lignes de 3 à n de n'importe quel onglet permet de récupérer le nom de la feuille
=SI(LIGNE()-1 <= COLONNES(nom_feuilles); DROITE(INDEX(nom_feuilles;LIGNE()-1);NBCAR(INDEX(nom_feuilles;LIGNE()-1))-TROUVE("]";INDEX(nom_feuilles;LIGNE()-1))); "")

Répondre

Laissez un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.