Comment obtenir des informations sur le classeur (nom et répertoire d’enregistrement) et sur les cellules (nom de l’onglet,…) sur Excel ?
La semaine dernière, nous avions découvert la fonction INFORMATION() qui permet d’obtenir des informations sur l’utilisateur d’un fichier Excel et sur son environnement de travail, sans avoir besoin d’utiliser VBA. Aujourd’hui, nous allons voir comment extraire des informations sur une cellule donnée (comme ses coordonnées, son contenu, etc…). Nous verrons également comment utiliser ces informations pour récupérer facilement le nom du classeur ou de l’onglet.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. La fonction CELLULE()
La fonction CELLULE() permet comme son nom l’indique d’obtenir des informations sur une cellule Excel, comme sa mise en forme, son adresse ou encore son contenu.
La fonction CELLULE() attend les deux arguments suivants :
=CELLULE(type_info;[référence])
Où
- Type_info : il s’agit de renseigner à la fonction CELLULE le type d’information que nous souhaitons extraire. C’est un texte à saisir entre guillemets, dont nous verrons juste après les différentes valeurs que nous pouvons lui affecter
- [Référence] : cet argument facultatif permet de renseigner les coordonnées de la cellule pour laquelle nous souhaitons obtenir des informations. Si cet argument est omis, Excel nous renverra alors l’information voulue par rapport à la cellule active. Nous reviendrons plus tard sur ce que cela engendre dans le fonctionnement de la formule.
2. Les valeurs de l’argument type_info
L’argument type_info permet de sélectionner l’information à extraire de la cellule voulue, parmi une liste très importante d’informations que vous pouvez retrouver dans ce tableau :
Valeur de type_info |
Information obtenue… |
"adresse" |
les coordonnées de la cellule passée en référence, en référence absolue |
"col" |
le numéro de colonne de la cellule passée en référence |
"couleur" |
est-ce qu'une couleur spécifique est définie dans le format de nombre de la cellule passée en référence ? Si oui la fonction CELLULE() retourne 1, dans le cas contraire, elle retourne 0 |
"contenu" |
la valeur contenue dans la cellule passée en référence, ou son résultat s'il s'agit d'une formule |
"nomfichier" |
le chemin d'enregistrement complet du fichier (chemin et nom de fichier) du fichier qui contient une référence. Si le fichier n'est pas enregistré, le résultat retourné est une chaîne vide |
"format" |
le format de nombre appliqué à la cellule passée en référence. Celui-ci est retourné sous la forme d'un code de correspondance ("D" pour une date, "S" pour Général, …") |
"parentheses" |
est-ce que la mise en forme des nombres de la cellule passée en référence repose sur l'utilisation de parenthèses ? Si oui, la fonction renverra 1, dans le cas contraire 0 |
"prefixe" |
est-ce que la valeur saisie dans la cellule passée en référence est préfixée par une apostrophe ? Comme nous l'avons vu dans un tutoriel précédent, l'apostrophe permet de forcer la reconnaissance de la cellule en tant que texte |
"protege" |
si la cellule passée en référence est protégée, alors la fonction renverra la valeur 1. Dans le cas contraire, elle renverra la valeur 0 |
"ligne" |
le numéro de ligne de la cellule passée en référence |
"type" |
le type de contenu de la cellule passée en argument : "i" la cellule est vide, "l" si la cellule contient du texte et "v" pour tout autre type de valeur (nombre, date,…) |
"largeur" |
informations sur la largeur de la cellule passée en argument. Cette matrice est composée de deux éléments : la taille de la cellule, et une valeur booléenne permettant de savoir s'il s'agit de la largeur par défaut de la cellule. Seul le premier élément de la matrice est directement affiché dans la cellule. Nous pouvons appuyer sur la touche [F9] pour en visionner les deux éléments, |
3. Informations complémentaires
3.1. Obtenir l’adresse relative de la cellule
Comme nous venons de le voir, la valeur adresse permet de récupérer l’adresse de la cellule passée en argument sous la forme d’une référence absolue.
Étant donné qu’il s’agit d’une chaîne de caractère, nous pouvons facilement la convertir en référence relative en utilisant une fonction de manipulation de chaîne de caractères, comme par exemple la fonction SUBSTITUE() :
=SUBSTITUE(CELLULE(A7;$B$1);"$";"")
Nous remplaçons ici les symboles dollar par une chaîne de caractère vide :
3.2. Mettre en forme le résultat
La valeur « contenu » permet de récupérer la valeur brute de la cellule, sans aucune mise en forme :
Pour mettre en forme cette valeur, nous pouvons encore encapsuler ce résultat dans une fonction : la fonction TEXTE() qui permet de mettre en forme une valeur retournée sous la forme d’un texte :
=TEXTE(CELLULE(A10;$D$6);"jj/mm/aaaa")
Ou nous pouvons encore modifier directement le format de nombre de la cellule.
3.3. Récupérer le nom du fichier
Comme nous l’avons juste avant, la valeur nomfichier donnée à l’argument type_info permet de récupérer le chemin entier du fichier (chemin, nom du classeur et même le nom de la feuille de calcul).
Si nous souhaitons extraire une information en particulier, nous allons donc devoir manipuler une fois de plus la chaîne de caractères.
Pour nous y aider, il suffit de regarder le résultat obtenu, pour constater que le nom du classeur est affiché entre crochets :
Nous pouvons donc en déduire que :
- Le chemin d’enregistrement du classeur se trouve à gauche du crochet ouvert, nous le récupèrerons donc avec la fonction GAUCHE(),
- Le nom du classeur se trouve entre les deux crochets, nous le récupèrerons donc avec la fonction STXT(),
- Le nom de la feuille de calcul se trouve à droite du crochet fermé, nous le récupèrerons donc avec la fonction DROITE(),
En plus des fonctions que nous venons d’énumérer, nous allons utiliser la fonction TROUVE() qui permet de récupérer la position d’un caractère donné au sein d’une chaîne de caractères :
La formule qui permet de récupérer le chemin d’enregistrement est la plus simple, il suffit en effet de récupérer la position du crochet ouvrant, et d’extraire le nombre de caractères situés sur la gauche, en ôtant un caractère qui correspond au crochet :
=GAUCHE(B11;TROUVE("[";B11)-1)
La logique qui permet d’obtenir le nom du classeur est un petit peu plus complexe, nous allons en effet utiliser la fonction STXT() qui permet d’extraire une partie d’une chaîne de caractères :
- Dont nous connaissons la position de départ de l’extraction (cette position correspond au caractère qui suit directement le crochet ouvrant : TROUVE("[";B11)+1)
- Dont nous connaissons également le nombre de caractères à extraire (qui correspond à la différence entre les positions des deux crochets, moins un caractère : TROUVE("]";B11)-TROUVE("[";B11)-1
=STXT(B11;TROUVE("[";B11)+1;TROUVE("]";B11)-TROUVE("[";B11)-1)
Enfin, pour récupérer le nom de la feuille de calcul, il suffit de soustraire la position du crochet fermant au nombre de caractères total contenu dans la chaîne de caractère :
=DROITE(B11;NBCAR(B11)-TROUVE("]";B11))
4. L’omission de l’argument facultatif référence
Comme nous l’avons vu un peu plus tôt lors de la présentation de la fonction CELLULE, l’omission de l’argument référence a pour effet de retourner le résultat renseigné par type_info de la fonction active.
Attention, nous ne parlons pas ici de la cellule dans laquelle la fonction est insérée, mais bien de la cellule active au moment du recalcul de la feuille.
Concrètement, cela signifie que le résultat retourné va être modifié lors de chaque recalcul de la feuille, sachant qu’un recalcul est lancé lors de chaque :
- Modification de n’importe quelle cellule du classeur,
- En cliquant sur le bouton Formules > Calculer maintenant du menu ruban,
- En appuyant simplement sur la touche [F9] du clavier
Lorsque cet argument est omis, il suffira alors de sélectionner une cellule, puis d’appuyer sur la touche [F9] pour obtenir les informations correspondantes :