Comment obtenir le résultat d’une cellule située sur un autre classeur Excel ? Découvrons les références externes

Guide « 83 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchageant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

 

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 

Excel formation - references externes - 01

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 :

Excel formation - references externes - 02

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 formation - references externes - 03

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 

Excel formation - references externes - 04

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.

Excel formation - references externes - 05

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  

Excel formation - references externes - 06

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 :

Excel formation - references externes - 07

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 :

Excel formation - references externes - 08

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 :

Excel formation - references externes - 09

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 :

Excel formation - references externes - 10

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 :

Excel formation - references externes - 11

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 :

Excel formation - references externes - 12

 

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 :

Excel formation - references externes - 13

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 formation - references externes - 14

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.

Excel formation - references externes - 15

 

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] :

Excel formation - references externes - 16

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 :

Excel formation - references externes - 17

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 :

Excel formation - references externes - 18

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 formation - references externes - 19

Excel a maintenant remplacé les références externes dans les cellules par les valeurs stockées en mémoire.

 



Articles qui pourraient vous intéresser

Comment se protéger des macros infectées par un virus sur Excel ?
Comment obtenir le résultat d’une cellule située sur un autre classeur Excel ? Découvrons les références externes
Comment établir le classement des équipes de la Ligue 1 sur Excel ?
Comment faire une pause en VBA sur Excel ?
Comment savoir si un fichier existe dans un dossier en VBA avec Excel ?
Comment protéger ses macros VBA (limiter la consultation du code et le lancement) sur Excel ?
Les 6 bonnes habitudes qui facilitent le développement en VBA sur Excel
Les tableaux de données d'Excel
Comment créer nos propres objets VBA avec les classes personnalisées [#30 FORMATION EXCEL VBA COMPLETE]
Comment lancer des macros automatiquement avec les évènements VBA : Les objets Workbook [#31 FORMATION EXCEL VBA COMPLETE]
Comprendre et corriger les erreurs de VBA [#29 FORMATION EXCEL VBA COMPLETE]
Comment créer des commentaires de pro sur Excel
Guide « 83 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchageant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2021 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.