Pouvoir référencer des cellules et des plages est ce qui fait fonctionner n'importe quel tableur. Et Excel est le meilleur et le plus puissant qui soit.
Dans ce didacticiel, je couvrirai tout ce que vous devez savoir sur la façon de référencer des cellules et des plages dans Excel. Hormis le référencement de base sur la même fiche, la majeure partie de ce tutoriel porterait sur comment référencer une autre feuille ou un classeur dans Excel.
Bien qu'il n'y ait pas beaucoup de différence dans son fonctionnement, lorsque vous référencez une autre feuille dans le même fichier ou référencez un fichier Excel complètement séparé, le format de cette référence change un peu.
En outre, vous devez garder à l'esprit certaines choses importantes lorsque vous référencez une autre feuille ou d'autres fichiers externes.
Mais rassurez-vous… rien de bien fou !
Lorsque vous aurez terminé ce didacticiel, vous saurez tout sur le référencement des cellules et des plages dans Excel (être dans le même classeur ou dans un autre classeur).
Commençons!
Référencer une cellule dans la même feuille
Il s'agit du niveau de référencement le plus élémentaire où vous faites référence à une cellule sur la même feuille.
Par exemple, si je suis dans la cellule B1 et que je veux faire référence à la cellule A1, le format serait :
=A1
Lorsque vous faites cela, la valeur de la cellule où vous utilisez cette référence sera la même que celle de la cellule A1. Et si vous apportez des modifications à la cellule A1, celles-ci seront reflétées dans la cellule où vous avez utilisé cette référence.
Référencer une cellule dans une autre feuille
Si vous devez référencer une autre feuille dans le même classeur, vous devez utiliser le format ci-dessous :
Sheet_name!Cell_address
Tout d'abord, vous avez le nom de la feuille suivi d'un signe d'exclamation suivi de la référence de la cellule.
Donc, si vous devez vous référer à la cellule A1 de la feuille 1, vous devez utiliser la référence suivante :
=Feuille1!A1
Et si vous souhaitez faire référence à une plage de cellules dans une autre feuille, vous devez utiliser le format suivant :
Sheet_name!First_cell:Last_cell
Ainsi, si vous souhaitez faire référence à la plage A1:C10 dans une autre feuille du même classeur, vous devez utiliser la référence ci-dessous :
=Feuille1!A1:C10
Notez que je ne vous ai montré que la référence à la cellule ou à la plage. En réalité, vous les utiliseriez dans des formules. Mais le format des références mentionnées ci-dessus va rester le même
Dans de nombreux cas, la feuille de calcul à laquelle vous faites référence aurait plusieurs mots dans le nom. Par exemple, il pourrait être Données du projet ou Données de vente.
Si vous avez des espaces ou des caractères non alphabétiques (tels que @, !, #, -, etc.), vous devez utiliser le nom entre guillemets simples.
Par exemple, si vous souhaitez faire référence à la cellule A1 de la feuille intitulée Sales Data, vous utiliserez la référence ci-dessous :
='Données de ventes'!A1
Et si le nom de la feuille est Sales-Data, alors pour faire référence à la cellule A1 de cette feuille, vous devez utiliser la référence ci-dessous :
='Ventes-Données'!A1
Lorsque vous faites référence à une feuille dans le même classeur, puis modifiez ultérieurement le nom de la feuille de calcul, vous n'avez pas à vous soucier de la décomposition de la référence. Excel mettra automatiquement à jour ces références pour vous.
Bien qu'il soit bon de connaître le format de ces références, dans la pratique, ce n'est pas une si bonne idée de les saisir manuellement à chaque fois. Cela prendrait du temps et serait très sujet aux erreurs.
Laissez-moi vous montrer une meilleure façon de créer des références de cellule dans Excel.
Création automatique d'une référence à une autre feuille dans le même classeur
Une bien meilleure façon de créer une référence de cellule vers une autre feuille consiste simplement à pointer Excel vers la cellule/plage vers laquelle vous souhaitez créer la référence et à laisser Excel la créer lui-même.
Cela garantira que vous n'avez pas à vous soucier du point d'exclamation ou des guillemets manquants ou de tout autre problème de format survenant. Excel créera automatiquement la référence correcte pour vous.
Voici les étapes pour créer automatiquement une référence à une autre feuille :
- Sélectionnez la cellule dans le classeur actuel où vous avez besoin de la référence
- Tapez la formule jusqu'à ce que vous ayez besoin de la référence (ou d'un signe égal si vous voulez juste la référence)
- Sélectionnez la feuille à laquelle vous devez vous référer
- Sélectionnez la cellule/plage à laquelle vous souhaitez vous référer
- Appuyez sur Entrée pour obtenir le résultat de la formule (ou continuez à travailler sur la formule)
Les étapes ci-dessus créeraient automatiquement une référence à la cellule/plage dans une autre feuille. Vous pourrez également voir ces références dans la barre de formule. Une fois que vous avez terminé, vous pouvez simplement appuyer sur la touche Entrée et cela vous donnera le résultat.
Par exemple, si vous avez des données dans la cellule A1:A10 dans une feuille nommée Sales Data et que vous souhaitez obtenir la somme de ces valeurs dans la feuille actuelle, voici les étapes :
- Tapez la formule suivante dans la feuille actuelle (où vous avez besoin du résultat) : =Sum(
- Sélectionnez la feuille « Données de vente ».
- Sélectionnez la plage que vous souhaitez ajouter (A1:A10). Dès que vous faites cela, Excel créera automatiquement une référence à cette plage (vous pouvez le voir dans la barre de formule)
- Appuyez sur la touche Entrée.
Lorsque vous créez une formule longue, vous devrez peut-être faire référence à une cellule ou à une plage dans une autre feuille, puis à revenir à la feuille d'origine et à y faire référence à une cellule/plage.
Lorsque vous faites cela, vous remarquerez qu'Excel insère automatiquement une référence de feuille à la feuille où vous avez la formule. Bien que ce soit bien et ne fasse pas de mal, ce n'est pas nécessaire. Dans un tel cas, vous pouvez choisir de conserver la référence ou de la supprimer manuellement.
Une autre chose que vous devez savoir lors de la création de références en sélectionnant la feuille puis la cellule/plage est qu'Excel créera toujours un référence relative (c'est-à-dire des références avec n0 signe $). Cela signifie que si je copie et colle la formule (une en référence à une autre feuille) dans une autre cellule, cela ajustera automatiquement la référence.
Voici un exemple qui explique les références relatives.
Supposons que j'utilise la formule suivante dans la cellule A1 de la feuille actuelle (pour faire référence à la cellule A1 dans un nom de feuille SalesData)
=Données Ventes!A1
Maintenant, si je copie cette formule et que je la colle dans la cellule A2, la formule deviendra :
=Données Ventes!A1
Cela se produit parce que la formule est relative et lorsque je la copie et la colle, les références s'ajustent automatiquement.
Si je souhaite que cette référence fasse toujours référence à la cellule A1 de la feuille SalesData, je devrai utiliser la formule ci-dessous :
=Données Ventes!$A$1
Le signe dollar avant le numéro de ligne et de colonne verrouille ces références afin qu'elles ne changent pas.
Voici un didacticiel détaillé où vous pouvez en savoir plus sur les références absolues, mixtes et relatives.
Maintenant que nous avons vu comment référencer une autre feuille dans le même classeur, voyons comment nous pouvons nous référer à un autre classeur.
Comment référencer un autre classeur dans Excel
Lorsque vous faites référence à une cellule ou à une plage d'un autre classeur Excel, le format de cette référence dépend de si ce classeur est ouvert ou fermé.
Et bien sûr, le nom du classeur et des feuilles de calcul jouent également un rôle dans la détermination du format (selon que vous ayez ou non des caractères espacés ou non alphabétiques dans le nom).
Voyons donc les différents formats de références externes à un autre classeur dans différents scénarios.
Référence externe à un classeur ouvert
Lorsqu'il s'agit de faire référence à un classeur ouvert externe, vous devez spécifier le nom du classeur, le nom de la feuille de calcul et l'adresse de cellule/plage.
Vous trouverez ci-dessous le format que vous devez utiliser lorsque vous vous référez à un classeur ouvert externe
='[FileName]SheetName!CellAddress
Supposons que vous ayez un classeur « ExampleFile.xlsx » et que vous vouliez faire référence à la cellule A1 dans Sheet1 de ce classeur.
Ci-dessous la référence pour cela:
=[ExampleFile.xlsx]SalesData!A1
S'il y a des espaces dans le nom du classeur externe ou le nom de la feuille (ou les deux), vous devez ajouter le nom du fichier (entre crochets) et le nom de la feuille entre guillemets simples.
Voici les exemples où vous auriez besoin d'avoir les noms entre guillemets simples :
='[Example File.xlsx]SalesData'!A1 ='[ExampleFile.xlsx]Sales Data'!A1 ='[Example File.xlsx]Sales Data'!A1
Comment créer une référence à un autre classeur (automatiquement)
Encore une fois, s'il est bon de connaître le format, il est préférable de ne pas le saisir manuellement.
Au lieu de cela, pointez simplement Excel dans la bonne direction et il créera ces références pour vous. C'est beaucoup plus rapide avec beaucoup moins de risques d'erreurs.
Par exemple, si vous avez des données dans la cellule A1:A10 dans un classeur nommé « Exemple de fichier » dans la feuille nommée « Données de vente », et que vous souhaitez obtenir la somme de ces valeurs dans la feuille actuelle, voici les étapes :
- Tapez la formule suivante dans la feuille actuelle (où vous avez besoin du résultat) : =Sum(
- Accédez au classeur « Exemple de fichier »
- Sélectionnez la feuille « Données de vente ».
- Sélectionnez la plage que vous souhaitez ajouter (A1:A10). Dès que vous faites cela, Excel créera automatiquement une référence à cette plage (vous pouvez le voir dans la barre de formule)
- Appuyez sur la touche Entrée.
Cela créerait instantanément la formule avec les références correctes.
Une chose que vous remarquerez lors de la création d'une référence à un classeur externe est qu'il créera toujours des références absolues. Cela signifie qu'il y a un signe $ avant les numéros de ligne et de colonne. Cela signifie que si vous copiez et collez cette formule dans d'autres cellules, elle continuera à faire référence à la même plage en raison d'une référence absolue.
Si vous souhaitez que cela change, vous devez modifier les références manuellement.
Référence externe à un classeur fermé
Lorsqu'un classeur externe est ouvert et que vous vous référez à ce classeur, il vous suffit de spécifier le nom du fichier, le nom de la feuille et l'adresse de cellule/plage.
Mais lorsque cela est fermé, Excel n'a aucune idée de l'endroit où vous recherchez les cellules/plage auxquelles vous vous êtes référé.
C'est pourquoi lorsque vous créez une référence à un classeur fermé, vous devez également spécifier le chemin du fichier.
Vous trouverez ci-dessous une référence qui fait référence à la cellule A1 de la feuille de calcul Sheet1 du classeur de fichier d'exemple. Étant donné que ce fichier n'est pas ouvert, il fait également référence à l'emplacement où le fichier est enregistré.
='C:\Users\sumit\Desktop\[Example File.xlsx]Sheet1'!$A$1
La référence ci-dessus comprend les parties suivantes :
- Chemin du fichier - l'emplacement sur votre système ou réseau où se trouve le fichier externe
- Nom de fichier - le nom du classeur externe. Cela inclurait également l'extension de fichier.
- Nom de la feuille - le nom de la feuille dans laquelle vous faites référence aux cellules/plages
- Adresse de cellule/plage - l'adresse exacte de la cellule/plage à laquelle vous faites référence
Lorsque vous créez une référence externe à un classeur ouvert, puis que vous fermez le classeur, vous remarquerez que la référence change automatiquement. Une fois le classeur externe fermé, Excel insère également automatiquement une référence au chemin du fichier.
Impact du changement d'emplacement de fichier sur les références
Lorsque vous créez une référence à une cellule/plage dans un fichier Excel externe, puis que vous la fermez, la référence utilise désormais également le chemin du fichier.
Mais ensuite, si vous modifiez l'emplacement du fichier, rien ne changera dans votre classeur (dans lequel vous créez la référence). Mais depuis que vous avez changé l'emplacement, le lien est maintenant rompu.
Donc, si vous fermez et ouvrez ce classeur, il vous dira que le lien est rompu et que vous devez soit mettre à jour le lien, soit le rompre complètement. Il vous montrera une invite comme indiqué ci-dessous:
Lorsque vous cliquez sur Mettre à jour, il vous montrera une autre invite où vous pourrez choisir les options pour modifier les liens (qui vous montrera la boîte de dialogue ci-dessous)
Si vous devez conserver ces fichiers liés, vous pouvez spécifier le nouvel emplacement du fichier en cliquant sur Mettre à jour les valeurs. Excel ouvre une boîte de dialogue pour vous dans laquelle vous pouvez spécifier le nouvel emplacement du fichier en y naviguant et en le sélectionnant.
Référence à un nom défini (dans le même classeur ou un classeur externe)
Lorsque vous devez faire référence à des cellules et à des plages, un meilleur moyen consiste à créer des noms définis pour les plages.
Ceci est utile car cela vous permet de vous référer facilement à ces plages en utilisant un nom au lieu d'une adresse de référence longue et compliquée.
Par exemple, il est plus facile d'utiliser =SalesData au lieu de =[Example File.xlsx]Sheet1′!$A$1:$A$10
Et si vous avez utilisé ce nom défini dans plusieurs formules et que vous devez modifier la référence, vous ne devez le faire qu'une seule fois.
Voici les étapes pour créer une plage nommée pour une plage de cellules :
- Sélectionnez toutes les cellules que vous souhaitez inclure dans la plage nommée
- Cliquez sur l'onglet Formules
- Cliquez sur l'option Définir le nom (c'est dans le groupe Noms définis)
- Dans la boîte de dialogue Nouveau nom, donnez un nom à cette plage (j'utilise le nom SalesData dans cet exemple). N'oubliez pas que vous ne pouvez pas avoir d'espaces dans le nom
- Conservez la portée en tant que classeur (à moins que vous n'ayez une bonne raison de la rendre au niveau de la feuille)
- Assurez-vous que la référence à la plage est correcte.
- Cliquez sur OK.
Votre plage nommée a maintenant été créée et vous pouvez l'utiliser à la place des références de cellule avec des adresses de cellule.
Par exemple, si je veux obtenir la somme de toutes ces cellules dans la plage SalesData, vous pouvez utiliser la formule ci-dessous :
=SOMME(DonnéesVentes)
Et si vous souhaitez utiliser cette plage nommée pour d'autres feuilles de calcul ou même d'autres classeurs ?
Vous pouvez!
Il vous suffit de suivre le même format que nous avons discuté dans la section ci-dessus.
Inutile de revenir au début de cet article. Permettez-moi de vous donner tous les exemples ici lui-même afin que vous ayez l'idée.
Plages nommées au niveau du classeur contre la feuille de calcul: lorsque vous créez une plage nommée au niveau du classeur, il ne peut y avoir qu'une seule plage portant ce nom. Ainsi, si vous vous y référez à partir de classeurs externes ou de feuilles de calcul dans le même classeur, vous n'avez pas besoin de spécifier le nom de la feuille de calcul (car il est disponible pour une utilisation dans l'ensemble du classeur). Si vous utilisez la plage nommée au niveau de la feuille de calcul, vous ne pourrez l'utiliser que dans la feuille de calcul et, tout en vous y référant, vous devrez également spécifier le nom de la feuille de calcul.Référencement du nom défini dans la même feuille de calcul ou le même classeur
Si vous avez créé le nom défini pour le niveau du classeur, vous pouvez l'utiliser n'importe où dans le classeur en utilisant simplement le nom défini lui-même.
Par exemple, si je veux obtenir la somme de toutes les cellules de la plage nommée que nous avons créée (SaledData), je peux utiliser la formule ci-dessous :
=SUM(DonnéesVentes)
Si vous avez créé une plage nommée au niveau de la feuille de calcul, vous ne pouvez utiliser cette formule que si la plage nommée est créée dans la même feuille où vous utilisez la formule.
Si vous souhaitez l'utiliser sur une autre feuille (disons Sheet2), vous devez utiliser la formule suivante :
=SOMME(Feuille1!$A$1:$A$10)
Et au cas où il y aurait des espaces ou des caractères alphanumériques dans le nom de la feuille, vous devrez mettre le nom de la feuille entre guillemets simples.
=SUM('Feuille 1'!$A$1:$A$10)
Référencement du nom défini dans un autre classeur (ouvert ou fermé)
Lorsque vous souhaitez référencer une plage nommée dans un autre classeur, vous devrez spécifier le nom du classeur, puis le nom de la plage.
Par exemple, si vous avez un classeur Excel avec le nom ExampleFile.xlsx et une plage nommée avec le nom SalesData, vous pouvez utiliser la formule ci-dessous pour obtenir la somme de cette plage à partir d'un autre classeur :
=SUM(FichierExemple.xlsx!DonnéesVentes)
S'il y a des espaces dans le nom du fichier, vous devez les utiliser entre guillemets simples.
=SUM('Example File.xlsx'!SalesData)
Si vous avez des plages nommées au niveau de la feuille, vous devez spécifier le nom du classeur ainsi que la feuille de calcul lorsque vous la référencez à partir d'un classeur externe.
Vous trouverez ci-dessous un exemple de référencement d'une plage nommée au niveau de la feuille :
=SUM('[Example File.xlsx]Sheet1'!SalesData)
Comme je l'ai également mentionné ci-dessus, il est toujours préférable de créer des plages nommées au niveau du classeur, à moins que vous n'ayez une bonne raison de créer un niveau de feuille de calcul un.
Si vous faites référence à une plage nommée dans un classeur fermé, vous devrez également spécifier le chemin du fichier. Ci-dessous un exemple de ceci :
=SUM('C:\Users\sumit\Desktop\Example File.xlsx'!SalesData)
Lorsque vous créez une référence à une plage nommée dans un classeur ouvert, puis fermez le classeur, Excel modifie automatiquement la référence et ajoute le chemin d'accès au fichier.
Comment créer une référence à une plage nommée
Si vous créez et travaillez avec de nombreuses plages nommées, il n'est pas possible de se souvenir du nom de chacune.
Excel vous aide en vous montrant une liste de toutes les plages nommées que vous avez créées et vous permet de les insérer dans des formules en un seul clic.
Supposons que vous ayez créé une plage nommée SalesData que vous souhaitez utiliser dans une formule pour additionner toutes les valeurs de la plage nommée.
Voici les étapes à suivre :
- Sélectionnez la cellule dans laquelle vous souhaitez saisir la formule.
- Entrez la formule au point où vous avez besoin de la plage nommée à insérer
- Appuyez sur la touche F3 de votre clavier. Cela ouvrira la boîte de dialogue Coller le nom avec la liste de tous les noms que vous avez créés
- Double-cliquez sur le nom que vous souhaitez insérer.
Les étapes ci-dessus insèrent le nom dans la formule et vous pouvez continuer à travailler sur la formule.
Remarque : cette technique ne fonctionne que pour les plages de noms qui se trouvent dans le même classeur. Si vous souhaitez obtenir les noms dans un classeur externe, vous devrez vous y rendre, vérifier le nom à l'aide de la boîte de dialogue Coller le nom, puis revenir et l'utiliser dans votre formule (au format correct en utilisant le nom de fichier).C'est tout ce que vous devez savoir sur la façon de référencer d'autres feuilles ou classeurs et comment créer une référence externe dans Excel.
J'espère que vous avez trouvé ce tutoriel utile.
