Qu'est-ce que la fonction ESTREF ?
Tu sors ESTREF (ISREF en anglais) au moment de blinder un outil qui accepte des entrées imprévisibles, avant qu'une adresse mal formée ne fasse planter toute la chaîne de calcul en #REF!. C'est le garde-fou qui te dit si ce qu'on t'a donné mène vraiment quelque part, avant même d'essayer d'aller y lire une valeur.
C'est elle qui permet de valider une adresse saisie par un utilisateur avant d'en tirer une valeur, de sécuriser les outils Excel complexes contre des entrées incorrectes, ou d'adapter automatiquement le traitement selon que l'argument reçu est une référence réelle ou une valeur brute.
Comprendre chaque paramètre de la fonction ESTREF
valeur
: la valeur à testerIl peut s'agir d'une référence directe comme A1, d'une plage comme B2:D10, d'un nom défini pointant vers des cellules, ou d'une expression qui retourne une référence comme INDIRECT("A1").
ESTREF retourne VRAI uniquement si l'argument est (ou se résout en) une référence de cellule. Ainsi, =ESTREF(A1) retourne VRAI, mais =ESTREF("A1") retourne FAUX car la chaîne entre guillemets est du texte, pas une référence.
Attention : N'entoure pas l'argument de guillemets si tu veux tester une vraie référence. =ESTREF("A1") retourne toujours FAUX car les guillemets transforment A1 en texte.
Exemples pratiques pas à pas
Comment valider une adresse de cellule saisie à la main avec les fonctions ESTREF et INDIRECT
Ton outil demande aux utilisateurs de taper eux-mêmes l'adresse de la cellule à aller lire, et une adresse fantaisiste se transforme en #REF! en plein milieu du fichier. Avant d'exploiter ce lot de saisies, tu veux le passer au contrôle d'un coup plutôt que de tester les adresses une par une. La fonction ESTREF sait dire si une adresse mène vraiment quelque part, à condition de lui présenter une référence et non un bout de texte.
- 1Dans une cellule, écris
=ESTREF(. - 2En 1er argument : écris la conversion de l'adresse saisie en vraie référence et referme-la,
INDIRECT(B2). La celluleB2porte le texteA1, et la fonction INDIRECT en fait une référence exploitable quand cette adresse existe vraiment. Sans elle, tu testeraisB2, qui est une cellule de ton tableau et donc une référence par nature, au lieu de l'adresse qu'elle contient. - 3Ferme la parenthèse de la fonction ESTREF et appuie sur Entrée, puis recopie la formule vers le bas. Celle de la fonction INDIRECT a déjà été refermée à l'étape précédente, et chaque ligne recopiée contrôle sa propre adresse.
=ESTREF(INDIRECT(B2))A1 écrit dans la cellule et le transforme en vraie référence, puis la fonction ESTREF confirme que cette référence existe : VRAI. Recopiée vers le bas, la colonne reste à VRAI sur toute la liste, ce qui est le verdict recherché : aucune des adresses saisies ne pointe dans le vide, le lot passe la validation.
Attention : La formule filmée ne lit que la colonne Référence. Les mentions Feuil1 et Feuil2 de la colonne voisine sont de la donnée de contexte : elles n'entrent pas dans le calcul, et la fonction INDIRECT résout donc chaque adresse sur la feuille active.
Comment remplacer une erreur #REF! par un message clair avec les fonctions SI et ESTREF
Le contrôle de l'exemple précédent renvoyait des VRAI et des FAUX, ce qui te va très bien à toi mais ne dit rien à l'utilisateur qui s'est trompé. Cette fois, une main maladroite a tapé XYZ999, une adresse qui n'existe nulle part, et tu veux que ton outil le lui dise avec des mots plutôt que de laisser un #REF! s'afficher en travers du tableau. La fonction SI se charge de traduire le verdict de la fonction ESTREF.
- 1Dans une cellule, écris
=SI(. - 2En 1er argument : écris le contrôle qui dit si l'adresse mène quelque part et referme-le,
ESTREF(INDIRECT(B2)). La fonction INDIRECT tente de transformer le texte deB2en référence, et la fonction ESTREF renvoie VRAI quand elle y parvient, FAUX quand elle échoue. - 3En 2ᵉ argument : écris le message à afficher quand le test est vrai,
"Référence valide". Les guillemets sont indispensables pour qu'Excel traite ces deux mots comme du texte à afficher et non comme un élément de la feuille à retrouver. - 4En 3ᵉ argument : écris le message à afficher quand le test est faux,
"Erreur #REF!". Ce texte prend la place de l'erreur brute et nomme le problème avec des mots, ce qui laisse à celui qui s'est trompé une phrase lisible. - 5Ferme la parenthèse de la fonction SI et appuie sur Entrée, puis recopie la formule vers le bas. Celles des fonctions ESTREF et INDIRECT ont déjà été refermées dans l'étape du test.
=SI(ESTREF(INDIRECT(B2)); "Référence valide"; "Erreur #REF!")XYZ999 et ZZZZZ99999 ressemblent à des adresses mais n'existent pas : les colonnes d'Excel s'arrêtent à XFD. La fonction INDIRECT échoue donc en #REF!, la fonction ESTREF répond FAUX et ton message prend la place de l'erreur brute. Les adresses réelles, elles, passent et affichent « Référence valide ».
Les erreurs fréquentes avec la fonction ESTREF
Le réflexe qui fait trébucher avec ESTREF, c'est de mettre la référence entre guillemets : =ESTREF("A1") renvoie FAUX parce que tu testes alors la chaîne de texte A1 et non la cellule. Si ton adresse arrive sous forme de texte dans une cellule, passe-la d'abord à INDIRECT.
Les deux autres pièges sont plus subtils : un nom défini limité à une autre feuille qu'ESTREF ne reconnaît pas, et la confusion avec ESTNUM (qui teste si c'est un nombre) ou TYPE (qui renvoie un code de type), qui ne répondent pas du tout à la même question.
ESTREF retourne toujours FAUX même pour des références visibles
L'argument est entouré de guillemets, ce qui le transforme en texte. =ESTREF("A1") teste la chaîne A1, pas la cellule A1. Une chaîne de caractères n'est jamais une référence.
Solution : Retire les guillemets : =ESTREF(A1) retourne VRAI. Si l'adresse est stockée sous forme de texte dans une cellule, passe-la d'abord à INDIRECT : =ESTREF(INDIRECT(A1)).
Confusion entre ESTREF, ESTNUM et TYPE
Ces trois fonctions testent des choses différentes. ESTREF teste uniquement si c'est une référence de cellule. ESTNUM teste si c'est un nombre. TYPE retourne un code (1=nombre, 2=texte, 4=logique, 16=erreur, 64=tableau).
Solution : Utilise ESTREF quand tu veux savoir si ton argument est une cellule ou une plage. Utilise ESTNUM pour vérifier si une valeur est numérique. Utilise TYPE si tu as besoin du type exact de données.
ESTREF retourne FAUX pour un nom défini valide
Le nom défini n'est pas reconnu, soit parce qu'il est mal orthographié, soit parce qu'il n'existe pas dans le classeur courant, soit parce que la portée du nom est limitée à une autre feuille.
Solution : Vérifie le nom dans Formules → Gestionnaire de noms. Assure-toi que la portée (Classeur vs Feuille) est correcte. Les noms définis au niveau feuille ne sont accessibles que depuis cette feuille.
ESTREF vs INDIRECT vs ESTNUM vs TYPE
Sors ESTREF uniquement quand ta question est « est-ce que cet argument est une cellule ou une plage ? », typiquement pour valider une référence dynamique avant de l'exploiter. Si tu veux fabriquer une référence à partir d'un texte, c'est INDIRECT ; pour vérifier qu'une saisie est numérique, c'est ESTNUM.
TYPE va plus loin en renvoyant un code chiffré (1 pour un nombre, 2 pour du texte, 16 pour une erreur…) : prends-la quand le simple VRAI/FAUX d'ESTREF ne suffit pas et que tu as besoin de connaître le type exact.
| Critère | ESTREF | INDIRECT | ESTNUM | TYPE |
|---|---|---|---|---|
| Objectif | Tester si référence | Créer une référence depuis du texte | Tester si nombre | Identifier le type exact |
| Résultat | VRAI / FAUX | Référence ou #REF! | VRAI / FAUX | Code numérique (1, 2, 4…) |
| Cas d'usage typique | Valider une référence dynamique | Construire une référence depuis une cellule | Valider une saisie numérique | Détecter le type de données |
| Fréquence | Rare (cas avancés) | Occasionnelle | Occasionnelle | Rare |
| Compatibilité | Toutes versions | Toutes versions | Toutes versions | Toutes versions |
Astuces avancées avec ESTREF
Valide les références dynamiques avec SIERREUR + ESTREF
Pour créer une validation robuste, combine les deux niveaux de sécurité : =SIERREUR(SI(ESTREF(INDIRECT(A1)); INDIRECT(A1); "Référence invalide"); "Erreur de syntaxe"). ESTREF intercepte les adresses hors limites, SIERREUR gère les erreurs de syntaxe qu'INDIRECT peut générer sur une chaîne vide ou malformée.
Tu couvres ainsi tous les cas d'erreur possibles avec une seule formule.
Combine ESTREF et ADRESSE pour documenter tes dépendances
Pour créer un système de documentation automatique, =SI(ESTREF(A1); "Pointe vers " & ADRESSE(LIGNE(A1); COLONNE(A1)); "Valeur autonome") signale quelles cellules dépendent d'autres cellules et lesquelles portent des valeurs indépendantes.
C'est un outil d'audit léger, sans macro, qui fonctionne dans n'importe quelle version d'Excel.
Questions fréquentes
TYPE retourne un code numérique indiquant le type de données : 1 pour un nombre, 2 pour du texte, 4 pour une valeur logique, 16 pour une erreur, 64 pour un tableau. ESTREF retourne simplement VRAI ou FAUX selon que l'argument est une référence de cellule ou non. Dans la pratique, TYPE teste la valeur contenue dans la cellule, tandis qu'ESTREF teste la nature de l'argument lui-même.
Oui. ESTREF retourne VRAI aussi bien pour une cellule unique (A1) que pour une plage (A1:B5) ou un nom défini pointant vers une plage. Tout ce qui fait référence à des cellules est considéré comme une référence valide.
En revanche, une valeur scalaire comme un nombre ou du texte retourne FAUX, même si cette valeur provient d'une formule.
Parce qu'elle est rarement utile dans les formules du quotidien. La quasi-totalité des cas d'usage d'Excel ne nécessitent jamais de savoir si un argument est une référence : Excel le gère silencieusement. ESTREF devient nécessaire dans les outils complexes : validation des entrées utilisateur, formules dynamiques avec INDIRECT, macros VBA qui acceptent soit des plages soit des valeurs directes. C'est une fonction de développeur Excel.
Oui. ESTREF retourne VRAI pour les références externes comme [Classeur.xlsx]Feuil1!A1 ou les références 3D comme Feuil1:Feuil3!A1, tant que la référence est syntaxiquement valide et que le classeur externe est ouvert.
Si le classeur externe est fermé, INDIRECT ne peut pas résoudre la référence et retourne #REF!, donc ESTREF retourne FAUX.
Combine les deux fonctions : =SI(ESTREF(INDIRECT(A1)); INDIRECT(A1); "Référence invalide"). Si A1 contient l'adresse valide B5, la formule retourne la valeur de B5. Si A1 contient ZZZZZ999 (adresse impossible), INDIRECT retourne #REF!, ESTREF retourne FAUX, et tu affiches ton message personnalisé.
Pour une sécurité maximale, ajoute SIERREUR en wrapper extérieur pour gérer les cas où A1 est vide ou contient du texte non analysable par INDIRECT.
Dans VBA, ESTREF est moins utile car tu peux tester directement si une variable est de type Range avec TypeOf variable Is Range. En revanche, dans une Function VBA qui appelle des formules worksheet, tu peux utiliser Application.WorksheetFunction.IsRef() pour vérifier la nature d'un argument.
Pour des fonctions VBA personnalisées qui acceptent soit une plage soit une valeur, le test TypeOf est plus direct et plus lisible que de passer par ESTREF.
Pour aller plus loin
Continue sur ta lancée après la fonction ESTREF : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
