Comment obtenir le résultat d’une cellule située sur un autre classeur Excel ? Découvrons les références externes
Lorsque nous construisons des formules sur Excel, il peut arriver régulièrement que nous ayons besoin de faire appel au résultat d’autres cellules.
La plupart du temps, la cellule en question se trouvera directement placée au sein du même classeur, ce qui ne posera pas de problème particulier.
En revanche, les choses sont légèrement plus complexes à appréhender lorsque nous souhaitons utiliser le résultat d’une cellule située sur un autre classeur.
Dans ce cas, là nous parlons alors de référence externe, et c’est ce que nous allons découvrir dans ce tutoriel.
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. Présentation
Comme nous venons de le voir dans l’introduction, une référence externe est donc une référence qui fait appel au contenu d’une cellule située sur un autre classeur.
Utiliser ainsi une référence externe permet donc de maintenir à jour une valeur à partir d’éléments contenus dans un autre classeur, de manière automatique, ou tout du moins presque automatique.
Dans ce tutoriel, nous allons revenir sur les principales différences que nous pouvons retrouver entre une référence classique et une référence externe.
2. Les références internes
Avant de voir comment fonctionne une référence externe, découvrons le cas le plus simple : la référence interne.
Pour insérer une référence à une cellule dans une formule, il suffit de saisir ces coordonnées, directement en cours de saisie :
=c7
En principe, les coordonnées des cellules doivent être saisies en majuscules.
Mais Excel est décidément une application vraiment bien faite, c’est pourquoi la casse sera automatiquement ajustée comme il se doit dès lors que la référence a été détectée.
Pour gagner du temps, nous pouvons également saisir les coordonnées de la cellule en cliquant directement sur la cellule pour laquelle nous souhaitons faire référence :
Bien entendu, pour que l’insertion de la référence puisse fonctionner correctement, il est nécessaire d’utiliser le symbole « = » pour informer Excel que nous sommes en cours de rédaction d’une formule.
Dans le cas contraire, Excel se contentera de sélectionner la cellule pointée.
Si la cellule cible se trouve sur une autre feuille du même classeur, il s’agira toujours d’une référence interne à ce classeur, mais la présentation sera légèrement différente, afin de tenir compte du changement de feuille de calcul :
='Autre feuille'!B1
Excel enregistre en effet le nom de la feuille de calcul directement dans la référence.
Le nom de la feuille étant alors séparé des coordonnées de la feuille de calcul par un point d’exclamation.
Vous noterez également que si le nom de la feuille de calculs contient au moins une espace ou tout autre caractère non alphanumérique, alors il sera entouré par des apostrophes simples.
=Autrefeuille!B1
Comme vous pouvez le constater, dans un cas comme dans l’autre, les références insérées automatiquement par Excel sont des références relatives, sans le symbole dollar.
Il faut donc être vigilant si nous envisageons d’être la formule sur d’autres cellules d’utiliser une référence absolue, en insérant les symboles dollars, ou en appuyant sur la touche [F4] pour automatiser cette insertion.
3. Les références externes d’un classeur ouvert
Maintenant que nous avons pu nous rafraichir les idées concernant les références, je vous propose de découvrir les références externes.
Pour cela, nous allons chercher à obtenir la valeur d’une cellule située sur un autre classeur, en procédant de la même manière :
- Nous saisissons le symbole « = »
- Puis, nous nous plaçons sur le classeur cible
- Ensuite, nous cliquons sur la cellule souhaitée
- Et enfin, nous validons en appuyant sur la touche [Entrée] pour valider la formule
='[Excelformation.fr - Les références externes d''Excel - Autre classeur.xlsx]excelformation.fr'!$A$7
Ici, nous pouvons constater deux choses :
- Le nom du classeur cible est renseigné entre crochets, juste avant le nom de la feuille de calcul (entre les guillemets)
- La référence insérée est cette fois-ci une référence absolue, avec les symboles « $ ». Une fois encore, il faut être vigilant à ce que ce soit bien le type de référence que souhaitons mettre en place. Dans le cas contraire, nous devrons les supprimer ou appuyer sur la touche [F4]
Évidemment, nous pourrions tout à fait saisir directement la référence externe au clavier, mais pour éviter tout risque d’erreur, et pour gagner le plus de temps possible, je vous conseille d’utiliser la souris, comme nous venons de le faire à l’instant.
Comme pour les références classiques, nous pouvons modifier la cellule cible, pour que le résultat de la formule faisant appel à cette cellule s’actualise instantanément.
En effet, lorsque le classeur cible est ouvert, la référence externe va pratiquement s’utiliser comme une référence interne au classeur.
En revanche, les choses vont quelque peu se compliquer à partir du moment où le classeur sera fermé, et c’est ce que nous allons voir dans la partie suivante.
4. Les références externes d’un classeur fermé
En effet, une référence externe ne fonctionne pas de la même manière si le classeur cible est ouvert, ou s’il est fermé.
Nous pouvons tout d’abord constater une différence au niveau de l’appel de la référence dans la cellule.
En effet, il suffit de revenir dans celle-ci pour constater le premier changement :
='C:\Fichiers Excel\[Excelformation.fr - Les références externes d''Excel - Autre classeur.xlsx]excelformation.fr'!$A$7
Avant le nom du classeur, la référence renvoie maintenant le chemin d’enregistrement de celui-ci (ici dans le répertoire « C:\Fichiers Excel\ »).
Nous retrouvons alors, dans l’ordre :
- C:\Fichiers Excel\ : le chemin du répertoire d’enregistrement du classeur
- [Excelformation.fr - Les références externes d''Excel - Autre classeur.xlsx] : le nom d’enregistrement du classeur, avec son extension
- excelformation.fr! : le nom de la feuille de calcul, avec un point d’exclamation
- $A$7 : et enfin les coordonnées de la cellule cible
5. Utiliser une référence sous la forme d’un nom
Dans Excel, nommer une cellule, ou une plage de plusieurs cellules permet de s’y référer très simplement, en faisant abstraction des coordonnées de la cellule cible.
Pour créer un nom, il suffit de sélectionner la cellule, puis de le saisir dans la zone des noms, située à gauche de la barre de formule :
Pour appeler cette cellule, il suffit ensuite de saisir ce nom dans la formule d’appel, ou d’appuyer sur la touche [F3] lors de la rédaction de la formule :
Dans cet exemple, nous avons pu appeler une cellule nommée située sur une autre feuille de calcul, sans aucun problème, nous n’avons même eu besoin de nommer la feuille en question.
Pour information, une cellule nommée est reconnue au niveau d’une zone, que nous pouvons retrouver depuis le menu Formule > Gestionnaire de nom :
Nous voyons ici que le nom est étendu au niveau de la zone « Classeur ».
Si nous avions défini un nom au niveau d’une zone « Feuille », alors nous pourrions avoir plusieurs noms identiques sur un même classeur :
En revanche, si nous utilisons cellule nommée depuis un autre classeur, comme vous pouvez l’imaginer, nous allons devoir spécifier le nom de ce dernier que nous pas qu’Excel commence à s’emmêler les pinceaux :
='Excelformation.fr - Les références externes d''Excel - Autre classeur.xlsx'!cible
Nous pouvons même envisager d’insérer directement le chemin d’accès à la cellule dans le nom créé dans le classeur.
Pour cela, nous revenons dans le gestionnaire de noms depuis le menu formule, et nous cliquons sur le bouton Nouveau :
Dans la zone « Fait référence à », nous cliquons sur la flèche pour aller sélectionner la cellule cible.
Ensuite, il suffira d’appeler ce nom pour qu’Excel comprenne où aller chercher la valeur à insérer dans la référence :
6. La gestion des références externes
Nous avons déjà vu un peu plus tôt que dès lors qu’une référence externe pointe vers un classeur actuellement ouvert, Excel va automatiquement mettre à jour les données lorsque celles-ci seront modifiées.
En revanche, si le classeur est fermé, nous devrons explicitement demander que la mise à jour se fasse ponctuellement.
Et pour cela, plusieurs cas de figure peuvent se présenter :
Tout d’abord lorsque nous ouvrons un classeur contenant une référence externe, Excel va automatiquement détecter sa présence et nous afficher une notification :
Nous pourrons alors choisir de mettre à jour les cellules contenant ces références externes.
Excel se chargera alors d’aller consulter le contenu de ces classeurs, de manière totalement transparente, sans que nous n’ayons à ouvrir les fichiers.
Ensuite, en cours de travail, si nous souhaitons actualiser le contenu des cellules, nous devrons nous rendre dans le menu Données > Modifier les données :
Excel nous affiche alors une fenêtre, sur laquelle il suffira de cliquer sur le bouton « Mettre à jour les valeurs » pour actualiser le contenu des cellules.
7. Détecter des liaisons externes
Parfois, nous pouvons souhaiter que des références externes ne soient plus insérées dans le classeur.
Ce sera par exemple le cas si nous souhaitons partager le fichier à quelqu’un qui n’aurait pas accès au classeur cible.
Dans ce cas-là, il sera donc inutile d’afficher le message à l’ouverture du classeur, d’autant plus que la mise à jour des valeurs ne pourra jamais aboutir.
Pour identifier rapidement une référence externe contenue dans une formule, il suffit tout simplement d’effectuer une recherche Excel sur le nom du fichier cible, que nous pouvons retrouver depuis le menu Données > Modifier les liaisons.
Pour effectuer une recherche, nous appuyons sur les touches [Ctrl]+[F] :
Ici, nous saisissons le terme « autre classeur.xlsx » dans le champ Rechercher, puis nous appuyons sur le bouton Rechercher tout.
Nous aurions également pu limiter la recherche au terme « xslx ».
Attention, avec cette méthode Excel est capable de détecter les références externes insérées dans les cellules, mais pas celles intégrées dans une plage nommée.
Pour cela, nous allons nous rendre dans le gestionnaire des noms depuis le menu Formules :
Nous pouvons alors identifier ces cellules d’un simple coup d’œil, car nous retrouvons le nom du fichier entre crochets (et éventuellement le chemin d’accès si le fichier est fermé).
Au besoin, nous pouvons supprimer le nom en cliquant sur le bouton « Supprimer ».
Ensuite, si nous souhaitons supprimer les liaisons pour qu’Excel insère directement le résultat à la place, nous pouvons revenir dans la fenêtre « Modifier les liaisons » et cliquer sur le bouton « Rompre la liaison ».
Excel nous affiche alors un avertissement pour nous prévenir que cette action est définitive et qu’il ne sera donc plus possible de revenir en arrière :
Si nous sommes certains de vouloir supprimer la liaison, et que nous disposons de copies de sauvegarde du fichier de travail, nous pouvons confirmer la suppression en cliquant sur le bouton « Rompre les liaisons » :
Excel a maintenant remplacé les références externes dans les cellules par les valeurs stockées en mémoire.