Concept : utiliser des références externes pour faire référence aux données dans d’autres classeurs
Dans un classeur, vous pouvez faire référence à des cellules ou à des plages de données qui existent dans d’autres classeurs. Nous appelons ces références des
références externes
ou des références d’un classeur à l’autre
. Les références à d’autres feuilles du même classeur ont une syntaxe similaire. Nous les décrirons donc également dans cette rubrique. Vous pouvez créer une référence autonome (en utilisant uniquement le signe égal et la référence) ou encore une référence peut être un argument dans une formule.Un classeur qui fait référence à des données (les apporte) d’un autre classeur est un classeur
consommateur
. Un classeur contenant des données référencées dans un autre classeur est un classeur producteur
. Un classeur peut être à la fois consommateur et producteur.
Création de références externes
Pour ajouter des références à un classeur producteur à partir d’un classeur consommateur, vous devez disposer de l’autorisation Peut modifier pour le classeur consommateur et de l’autorisation Peut afficher, ou mieux, pour le classeur producteur.
Lorsque quelqu’un crée ou modifie une formule avec une référence externe, Worksheets se souvient de l’utilisateur qui l’a fait. Lorsque la formule s’exécute, Worksheets vérifie que l’utilisateur est autorisé à accéder au classeur référencé de manière externe.
Vous pouvez copier une référence complète
à partir d'un classeur producteur
, puis la coller dans un classeur consommateur.- Sélectionnez les données que vous souhaitez référencer, puis cliquez avec le bouton droit de la souris et sélectionnezCopier comme référence externe.
- Ouvrez le classeur consommateur. Dans la cellule où vous voulez placer la référence, entrez le signe égal (=) et collez la référence en appuyant sur les touches Ctrl+V (Windows) ou Commande+V (Mac).
Vous pouvez également obtenir l’identifiant Workday du classeur producteur en accédant à et en ajoutant manuellement le reste de la référence externe dans le classeur consommateur.
Vous pouvez copier l'identifiant Workday d'
un classeur consommateur à partir d'
un classeur producteur, puis ajouter manuellement le nom défini ou le nom de la feuille et la référence de cellule ou de plage.- Dans la cellule où vous souhaitez placer la référence, sélectionnez pour afficher tous les classeurs auxquels vous avez accès.
- Accédez au classeur producteur, puis cliquez surCopier l’identifiant Workday.
- Dans le classeur consommateur, entrez le signe égal (=) et collez l’identifiant Workday en appuyant sur les touches Ctrl+V (Windows) ou Commande+V (Mac). Saisissez ensuite le reste de la référence, qui peut être un nom défini ou un nom de feuille et une référence de cellule ou de plage.
Mise à jour des références externes
Les classeurs consommateurs affichent une icône
Références externes
grise dans la barre d’outils du classeur. Si les données du classeur producteur changent, l’icône devient verte et vous pouvez cliquer sur celle-ci pour mettre à jour les données dans le classeur consommateur. L’icône Références externes
est accessible aux utilisateurs disposant de n’importe quel niveau d’autorisation du classeur. La mise à jour qui a lieu est identique à Recalculer tout
. Il peut y avoir un délai maximum de deux minutes, une fois que les données du classeur producteur ont été modifiées, avant que l’icône Références externes
du classeur consommateur ne devienne verte.Lorsque les données du classeur producteur changent, vous pouvez choisir de mettre à jour automatiquement les références externes à l’ouverture du classeur consommateur, ou vous pouvez ouvrir le classeur consommateur et cliquer sur l’icône
Références externes
pour mettre à jour les références externes manuellement. Cliquez sur Fichier
> Paramètres
et cochez ou décochez la case Mettre à jour les données des classeurs externes chaque fois que le classeur est ouvert
. En outre, vous pouvez planifier une actualisation des données dynamiques pour que le classeur consommateur soit recalculé. Vous pouvez définir une actualisation planifiée pour automatiser un nouveau calcul, même si le classeur consommateur ne contient pas de données dynamiques.
Par exemple, si vous avez trois classeurs joints (producteur > consommateur/producteur > consommateur), vous pouvez automatiser les mises à jour de chaque classeur en planifiant des actualisations de données. De plus, l’étape des actualisations planifiées fait en sorte que les données d’un classeur sont à jour avant que les données du classeur suivant soient recalculées.
Cette approche automatise le flux des données entre les classeurs, éliminant ainsi le besoin de mises à jour manuelles.
- Planifiez une actualisation pour le classeur producteur/ consommateur.Dans le classeur producteur/ consommateur, cliquez surDonnées>Planifier l’actualisation des données dynamiqueset définissez une heure d’actualisation, par exemple à 12:00 PM. Cela mettra à jour les données extraites du classeur producteur.
- Planifiez une actualisation pour le classeur consommateur.Dans le classeur du consommateur, planifiez une heure d’actualisation après l’actualisation de la valeur du producteur/du consommateur, par exemple à 12:15 PM. Cela garantit que le classeur consommateur affiche toujours les dernières données recalculées.
L’option
Planifier l’actualisation des données dynamiques
est configurable uniquement par le propriétaire du classeur et les actualisations planifiées ne peuvent s’exécuter qu’une fois par jour.Directives sur la syntaxe
Pour les références externes :
- Entrez l’identifiant Workday du classeur entre crochets, puis le nom de la feuille et la cellule ou la plage de cellules ou le nom défini. N’incluez pas d’information sur le dossier/chemin pour le classeur producteur.
Pour les références multifeuilles ou les références externes :
- Commencez par un signe égal (=) dans la cellule.
- Si le nom de la feuille comporte l’une des propriétés suivantes, placez le nom entre guillemets simples (') :
- Le nom de la feuille ne commence pas par une lettre ou un trait de soulignement.
- Les caractères qui suivent dans le nom de la feuille ne sont pas une lettre, un chiffre, un point ou un trait de soulignement.
- Exemple de références multifeuilles :
- =Sheet1!A1:A4
- ='All Employees'!A1:A4
- ='All Employees'!DataArea1, où DataArea1 est un nom défini
Ce tableau présente des exemples de références externes. Les identifiants Workday sont raccourcis pour une meilleure lisibilité.
=[9f9a1]January!A1 | Obtient les données du classeur avec l’identifiant Workday 9f9a1, dans la feuille January, cell A1. |
=SUM([9f9a1]January!A1:B6) | Affiche la somme des cellules du classeur avec l’identifiant Workday 9f9a1, dans la feuille January, range A1:B6. |
=[9f9a1]DataArea1 | Obtient les données du classeur avec l’identifiant Workday 9f9a1, avec le nom défini DataArea1. |
=SUM([9f9a1]Regions!A1:B6) | Affiche la somme des cellules de la plage A1:B6, dans le classeur portant l’identifiant Workday 9f9a1, dans la feuille Regions. |
=SELECT("SELECT * FROM ?",ARRAYAREA([9f9a1]'Data Sheet'!A1)) | Utilise la fonction SELECT pour obtenir les données d’un tableau à partir du classeur 9f9a1, feuille de données Data Sheet. |
Éléments à prendre en compte pour les références externes
Gardez ces conseils à l’esprit lorsque vous travaillez avec des références externes :
- Si les données changent dans un classeur producteur :
- Lorsque vous cliquez sur l’icône verteRéférences externespour mettre à jour le classeur consommateur, l’action est identique àRecalculer tout.
- Si vous modifiez le positionnement des données dans un classeur producteur, par exemple en supprimant une feuille ou en insérant une colonne dans une plage, Worksheets ne met pas à jour la référence dans le classeur consommateur.
- Si vous perdez l’accès à un classeur producteur ou si un classeur producteur est supprimé, le symbole d’erreur #REF! s’affiche dans les cellules de classeur qui dépendent des données de référence externes.
- Worksheets réaffecte les références externes interrompues dans les classeurs lorsque les références deviennent non valides en raison de la désactivation du compte de l’utilisateur auteur ou lorsque l’utilisateur auteur n’a plus les autorisations pour les classeurs producteurs et consommateurs. Worksheets tente de corriger les références externes chaque fois qu’un utilisateur qui peut modifier ou qui est propriétaire du classeur consommateur et qui a accès en mode affichage ou plus au classeur producteur, clique sur l’icôneRéférence externe. Si Worksheets ne peut pas mettre à jour une référence, une erreur #REF s’affiche et vous devez mettre à jour la référence manuellement.
- Si vous avez une référence externe qui contient une référence structurée (tableau de données dynamiques/nom de colonne) et que vous changez le nom du tableau ou de la colonne, Worksheets ne met pas automatiquement à jour le classeur consommateur; vous devez mettre à jour la référence manuellement.
- La mise en forme dans les références du classeur producteur est appliquée dans le classeur consommateur, sauf si les cellules du classeur consommateur sont déjà mises en forme. Si vous modifiez ultérieurement la mise en forme des cellules référencées du classeur producteur, la nouvelle mise en forme n’est pas appliquée dans le classeur consommateur. (Ce même comportement se produit pour les références dans le même classeur.)
- Si vous passez la souris sur l’identifiant Workday dans une référence externe dans la barre de formule, Worksheets affiche le nom du classeur producteur.
- Vous ne pouvez pas ajouter une référence à un graphique de classeur producteur dans un classeur consommateur; une erreur se produit lorsque vous placez la référence dans le consommateur.
- Si vous êtes le propriétaire du classeur, vous pouvez empêcher les utilisateurs disposant de l’autorisation Peut modifier de modifier le contenu dans le classeur producteur en utilisant la protection par plage. Toutefois, l’utilisation de la protection par plage dans un classeur consommateur n’empêche pas les mises à jour de se transmettre du producteur au consommateur.
- Dans un locataire particulier, jusqu’à 10 000 classeurs consommateurs peuvent faire référence à un seul classeur producteur. Jusqu’à 25 classeurs producteurs peuvent fournir des données dans un seul classeur consommateur.
- Les fonctions NOTIFYIF et NOTIFYIFS ne sont pas mises à jour automatiquement lorsque les données du classeur producteur changent; vous devez mettre à jour les références manuellement ou définir une actualisation planifiée des données dynamiques. (Le classeur n’a pas besoin de contenir des données dynamiques; l’actualisation planifiée effectue une action de recalcul, qui met à jour les données du classeur consommateur.)